Misunderstanding on Top SQLs of AWR Repository
AWR Report contains information about SQL activity like following :
SQL ordered by CPU Time DB/Inst: UKJA10/ukja10 Snaps: 4498-4499 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 CPU Elapsed CPU per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ----------- ------- ------------- 6 6 100 0.06 0.9 3bdy67c0730td Module: SQL*Plus select /*+ top_sql_39 */ count(*) from t1 6 6 100 0.06 1.0 cbhzmzrac7t9y Module: SQL*Plus select /*+ top_sql_86 */ count(*) from t1 ...
There are 2 common misunderstandings on how Oracle captures SQLs into AWR repository.
1. Oracle captures every SQL.
The answer is absolutely NO. Capturing every SQL might be too heavy. Oracle only captures part of the SQLs which are considered to be meaingful workloads. The actual configuration is controlled by dbms_workload_repository.modify_snapshot_settings procedure.
2. Oracle captures 30 SQLs(with typical statistics_level) or 100 SQLs(with all statistics_level)
The answer is NO. These 30/100 numbers are originated from PL/SQL package reference(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm#BACCDGDB). Following is the excerpt.
As the document clearly tells, the 30/100 threshold is not per instance. It’s per SQL criteria. But there is also a document bug.
SQL criteria is not (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). As to real AWR reportthe SQL criteria is Elapsed Time, CPU Time, Buffer Gets, Physical Reads, Executions, Parse Calls, Shareable Memory, Version Count)(it might be version dependent)
So, theoretically maximum 30*8 = 240 or, 100*8 = 800 top SQLs are captureable.
Although we can capture as many SQLs as we want(even 100% is possible!), the overhead would not be negligible.
Following is a small test case to demonstrate how Oracle captures the top SQLs.
-- create objects create table t1(c1 int, c2 char(100)); insert into t1 select level, 'x' from dual connect by level <= 10000 ; commit; -- generate many many TOP sqls. here we generate 100 top sqls which do full scan on table t1 set heading off set timing off set feedback off spool select2.sql select 'select /*+ top_sql_' || mod(level,100) || ' */ count(*) from t1;' from dual connect by level <= 10000; spool off ed select2 -- the result would be like this: select /*+ top_sql_1 */ count(*) from t1; select /*+ top_sql_2 */ count(*) from t1; select /*+ top_sql_3 */ count(*) from t1; select /*+ top_sql_4 */ count(*) from t1; ... select /*+ top_sql_0 */ count(*) from t1; -- Now we capture the SQLs exec dbms_workload_repository.create_snapshot; @select2 exec dbms_workload_repository.create_snapshot; -- AWR Report would show that more than 30 top sqls are captured @?/rdbms/admin/awrrpt