Our monitoring software recently flagged a long-running job on a client database: an Oracle auto-task for statistics gathering that had been running for over three hours. Investigating why a standard maintenance task was consuming so much time led to some surprising discoveries regarding Oracle's internal mechanics.
Investigating Long-Running Statistics Jobs
To understand what was happening during the job run, I queried Active Session History (ASH) to identify the most frequent SQL statements based on the specific module name.
ASH Query and Surprising Results
I used the following SQL to look at the top five statements associated with the auto-task action ORA$AT_OS_OPT_SY%:
[sourcecode lang="sql"]
SQL> select s.sql_id, t.sql_text, s.cnt
from
(select * from
(select sql_id, count() cnt from v$active_session_history where action like 'ORA$AT_OS_OPT_SY%' group by sql_id order by count() desc)
where rownum <= 5) s,
dba_hist_sqltext t
where s.sql_id = t.sql_id;
SQL_ID SQL_TEXT CNT
020t65s3ah2pq select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /+ ... 781 byug0cc5vn416 / SQL Analyze(1) / select /+ full(t) no_parallel(t) ... 43
bkvvr4azs1n6z /* SQL Analyze(1) / select /+ full(t) no_parallel(t) ... 21
46sy4dfg3xbfn /* SQL Analyze(1) / select /+ full(t) no_parallel(t) ... 1559
[/sourcecode]
Surprisingly, the top queries contained the /* SQL Analyze(1) */ comment. This comment is typically associated with the SQL Tuning Advisor, yet it was appearing inside a DBMS_STATS call.
Decoding the DBMS_STATS and SQL Tuning Relationship
Finding SQL Tuning Advisor signatures during a statistics gathering task is confusing. After some research, I found MOS Doc ID 1480132.1, which confirms that DBMS_STATS has an internal dependency on the SQL Tuning package.
Verifying Internal Dependencies
Checking the data dictionary confirms that DBMS_STATS_INTERNAL relies on DBMS_SQLTUNE_INTERNAL:
[sourcecode lang="sql"]
SQL> select * from dba_dependencies
where name = 'DBMS_STATS_INTERNAL'
and referenced_name = 'DBMS_SQLTUNE_INTERNAL';
OWNER NAME TYPE REFERENCED_NAME REFERENCED_TYPE
SYS DBMS_STATS_INTERNAL PACKAGE BODY DBMS_SQLTUNE_INTERNAL PACKAGE
[/sourcecode]
It turns out this is not actually performing an SQL Tuning analysis. Instead, DBMS_STATS simply utilizes specific procedures within the SQL Tuning framework to handle data sampling.
Analyzing the Call Tree and SQL Execution
To verify this behavior, I traced a GATHER_TABLE_STATS call using DBMS_HPROF. The resulting call tree clearly shows the path from statistics gathering to the internal tuning utilities.
The Profiler Call Tree
The trace shows DBMS_STATS_INTERNAL calling GATHER_SQL_STATS, which then hands the work off to DBMS_SQLTUNE_INTERNAL.
[sourcecode]
NAME FUNCTION SUBTREE_ELAPSED_TIME CALLS
SYS.DBMS_STATS_INTERNAL GATHER_SQL_STATS 21131962 1
SYS.DBMS_SQLTUNE_INTERNAL GATHER_SQL_STATS 21118776 1
SYS.DBMS_SQLTUNE_INTERNAL I_PROCESS_SQL 21107094 1
[/sourcecode]
The Resulting SQL
The actual SQL being executed under the /* SQL Analyze(0) */ header looks like this:
[sourcecode lang="sql"]
/* SQL Analyze(0) / select /+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats ... */
to_char(count("ID")),
to_char(substrb(dump(min("ID"),16,0,32),1,120)),
to_char(substrb(dump(max("ID"),16,0,32),1,120)),
...
from "TIM"."T1" t
[/sourcecode]
Conclusion: Understanding Approximate NDV
This process is essentially an Approximate Number of Distinct Values (NDV) calculation. Oracle's code is organized so that DBMS_STATS leverages the API of the SQL Tuning framework specifically when you use DBMS_STATS.AUTO_SAMPLE_SIZE as the ESTIMATE_PERCENT.
Since AUTO_SAMPLE_SIZE is the default and recommended value in Oracle 11g and later, seeing these "SQL Analyze" comments in your ASH reports during maintenance windows is perfectly normal and nothing to be alarmed about.
Oracle Database Consulting Services
Ready to optimize your Oracle Database for the future?
.svg){kind=small}
.svg){kind=small}