Dion Cho – Oracle Performance Storyteller

We are natural born scientists

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

6 Responses

Subscribe to comments with RSS.

  1. Congratulations for your English blog open! I’m very surprised to see your plan this year! So I’m very respect you and very proud together with you, sir.
    I’m also deeply felt that foreign language is very important. Espetially, Englis is basic of basic.
    So I made up my mind study English very hard like you.
    I wish you a happy new year!
    I will visit your site often. See you later.^^*

    Lee Myeongjin

    January 25, 2009 at 1:42 pm

  2. I found out that the maximum count of sql criteria is 14.

    Elapsed Time (ms)
    CPU Time (ms)
    Buffer Gets
    Disk Reads
    Parse Calls
    User I/O Wait Time (ms)
    Cluster Wait Time (ms)
    Application Wait Time (ms)
    Concurrency Wait Time (ms)
    Version Count
    Sharable Mem(KB)

    Hm… What a document bug!

    Dion Cho

    January 28, 2009 at 5:20 am

  3. […] Filed under: Optimizer — Dion Cho @ 6:04 am Tags: colored sql, dbms_workoad_repository In previous post,  I discussed how Oracle 10g captures the top […]

  4. […] stores the sql plan and statistics for top SQLs as discussed at this post. Even with the limitation of the number of top SQLs(30 per criteria), parallel queries are more […]

  5. Hello Dion

    is any way to capture the sql which are generated and executed by dynamic sampling method?

    Best Regards,

    Kostas Hairopoulos

    September 24, 2009 at 10:01 am

    • Kostas.

      Would SQL trace(10046 event) meet your requirement? SQL trace would capture all the recursive queries including the queries executed by dynamic sampling.


      Dion Cho

      September 24, 2009 at 11:50 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: