Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘dbms_xplan

Misunderstanding on Top SQLs of AWR Repository

with 6 comments

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.


  • If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 100000000. Specifying NULL will keep the current setting.

  • If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. SpecifyingMAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULL for this argument will keep the current setting.

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 report, the 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


-- 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;
exec dbms_workload_repository.create_snapshot;

-- AWR Report would show that more than 30 top sqls are captured

Written by Dion Cho

January 23, 2009 at 4:03 pm

Posted in Optimizer

Tagged with


Get every new post delivered to your Inbox.

Join 58 other followers