Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘dbms_xplan

Interesting case of the restriction of global hints

with one comment

One of my colleagues sent following test case, of which he couldn’t control the join order with hints.

1. Create objects – table t1, t2 and t3

SQL> create table t1(c1 number, c2 number);

Table created.

SQL> create table t2(c1 number, c2 number);

Table created.

SQL> create table t3(c1 number, c2 number);

Table created.

2. Now Let’s set the join order as T1->T2->T3, using global hints convention. But it seems that Oracle does not work as expected.

SQL> explain plan for
  2  select * from
  3  (
  4  select
  5  	     /*+ leading(v.t1 v.t2 t3) */
  6  	     v.c1 as v_c1,
  7  	     v.c2 as v_c2,
  8  	     t3.c2 as t3_c2
  9  from
 10  	     (select
 11  		     t1.c1,
 12  		     t2.c2
 13  	     from
 14  		     t1, t2
 15  	     where
 16  		     t1.c1 = t2.c1) v,
 17  	     t3
 18  where
 19  	     v.c1 = t3.c1
 20  ) x
 21  ;

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN            |      |     1 |    65 |     7  (15)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |    52 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | T1   |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

It really seems that the optimizer does not understand the global hints when it is used with non global hint convention(t3 here). Yes, you can use hints inside the inline view, but what if you want to control the join order only with global hint convention?

3. The cure he tried was using Oracle’s internal global hint convention. You can get how Oracle’s internal hint convention looks using ‘ADVANCED’ format option with DBMS_XPLAN.DISPLAY function.

select * from table(dbms_xplan.display(null, null, 'advanced'));
...

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5C160134
   3 - SEL$5C160134 / T1@SEL$3
   4 - SEL$5C160134 / T2@SEL$3
   5 - SEL$5C160134 / T3@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$5C160134" "T3"@"SEL$2")
      USE_HASH(@"SEL$5C160134" "T2"@"SEL$3")
      LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2")
      FULL(@"SEL$5C160134" "T3"@"SEL$2")
      FULL(@"SEL$5C160134" "T2"@"SEL$3")
      FULL(@"SEL$5C160134" "T1"@"SEL$3")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$335DD26A")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$335DD26A")
      OUTLINE_LEAF(@"SEL$5C160134")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

4. With this ugly global hint convention, the join order is now fully controllable.

SQL> explain plan for
  2  select * from
  3  (
  4  select
  5  	     /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2" ) */
  6  	     v.c1 as v_c1,
  7  	     v.c2 as v_c2,
  8  	     t3.c2 as t3_c2
  9  from
 10  	     (select
 11  		     t1.c1,
 12  		     t2.c2
 13  	     from
 14  		     t1, t2
 15  	     where
 16  		     t1.c1 = t2.c1) v,
 17  	     t3
 18  where
 19  	     v.c1 = t3.c1
 20  ) x
 21  ;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    39 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

5. The better approach would be QB_NAME hint, which provides better readability and flexibility.

SQL> explain plan for
  2  select * from
  3  (
  4  select
  5  	     /*+ leading(t1@inline t2@inline t3) */
  6  	     v.c1 as v_c1,
  7  	     v.c2 as v_c2,
  8  	     t3.c2 as t3_c2
  9  from
 10  	     (select /*+ qb_name(inline) */
 11  		     t1.c1,
 12  		     t2.c2
 13  	     from
 14  		     t1, t2
 15  	     where
 16  		     t1.c1 = t2.c1) v,
 17  	     t3
 18  where
 19  	     v.c1 = t3.c1
 20  ) x
 21  ;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    39 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Let me give my thanks to my colleague, who sent this interesting test case.

PS) Note that QB_NAME hint has also some restrictions, in that case, Oracle’s internal global hint convention would be a good alternative.

Written by Dion Cho

December 17, 2010 at 5:21 am

Posted in Uncategorized

Tagged with , ,

Tracing user specific SQL statements

with 4 comments

Assuming following request:

I would like to trace SQL statements of specific Oracle user, not any other users.

My first choice would be the powerful end to end tracing facility of Oracle 10g.
Next one is dbms_xplan package which is not only powerful but also the most important package in Oracle.

1. End to end tracing to trace specific user.

UKJA@ukja102>  -- 1. Create logon trigger in which I give identifier to user's session                                                   
UKJA@ukja102> connect /as sysdba                                                     
Connected.                                                                           
                                                                                     
SYS@ukja10> create or replace trigger logon_trigger                                  
  2  after logon on database                                                         
  3  begin                                                                           
  4    if ora_login_user = 'UKJA' then                                               
  5      dbms_session.set_identifier('ukja');                                       
  6    end if;                                                                       
  7  end;                                                                            
  8  /                                                                               
                                                                                     
Trigger created.                                                                     
                                                                                     
SYS@ukja10> -- 2. Enable sql*trace for given client identifier                                                                       
SYS@ukja10> exec dbms_monitor.client_id_trace_enable(client_id=>'ukja', -            
>           waits=>true, binds=>true);                                               
                                                                                     
PL/SQL procedure successfully completed.                                             
                                                                                     
SYS@ukja10>  -- 3. Logon as UKJA user and execute some queries                                                                         
SYS@ukja10> connect ukja/ukja@ukja102                                                
Connected.                                                                           
                                                                                     
UKJA@ukja102> select count(*) from t1;                                               
                                                                                     
  COUNT(*)                                                                           
----------                                                                           
         0                                                                           
                                                                                     
UKJA@ukja102> connect ukja/ukja@ukja102                                              
Connected.                                                                           
                                                                                     
UKJA@ukja102> select count(*) from t1;                                               
                                                                                     
  COUNT(*)                                                                           
----------                                                                           
         0                                                                           
                                                                                     
UKJA@ukja102> connect ukja/ukja@ukja102                                              
Connected.                                                                           

UKJA@ukja102> select count(*) from t1;                                               
                                                                                     
  COUNT(*)                                                                           
----------                                                                           
         0                                                                           
                                                                                     
UKJA@ukja102> -- 4. Disable sql*trace for given client identifier                                                                      
UKJA@ukja102> connect sys /as sysdba                                                 
Connected.                                                                           
                                                                                     
SYS@ukja10> exec dbms_monitor.client_id_trace_disable(client_id=>'ukja');            
                                                                                     
PL/SQL procedure successfully completed.                                             
                                                                                     
SYS@ukja10>  -- 5. Now the most difficult part. 
Our 3 trace files are scattered in the user dump directory. We should merge it.
The fantastic trcsess tool does this for me.   
                                                                      
SYS@ukja10> col value new_value dump_dir                                             
SYS@ukja10>                                                                          
SYS@ukja10> select value from v$parameter where name = 'user_dump_dest';             
                                                                                     
VALUE                                                                                
--------------------                                                                 
C:\ORACLE\ADMIN\UKJA                                                                 
10\UDUMP                                                                             
                                                                                     
                                                                                     
SYS@ukja10> -- Merge!                                                                         
SYS@ukja10> ho trcsess output=trc1.out clientid=ukja &dump_dir\*.trc                 
                                                                                     
SYS@ukja10> -- And tkprof                                                                          
SYS@ukja10> ho tkprof trc1.out tk1.out                                               

 -- Tkprof report. The result(3 executions from different sessions) was merged successfully! 
select count(*) 
from
 t1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.00       0.00          0          9          0           3                                                                                     



This end to end tracing is quite flexible and powerful. Love it!

2. DBMS_XPLAN.DISPLAY_CURSOR to trace specific user.

UKJA@ukja102>  -- Following simple query would capture the queries
which start with 'select /*+ gather_plan_statistics */ count(*)%' and
executed by UKJA user
UKJA@ukja102> select                                                                 
  2      cursor(select * from table(                                                 
  3          dbms_xplan.display_cursor(s.sql_id, s.child_number, 'allstats last')))  
  4  from v$sql s                                                                    
  5  where s.parsing_schema_name = 'UKJA'                                            
  6        and s.sql_text like 'select /*+ gather_plan_statistics */ count(*) %'     
  7  ;                                                                               
                                                                                     
CURSOR(SELECT*FROMTA                                                                 
--------------------                                                                 
CURSOR STATEMENT : 1                                                                 
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
PLAN_TABLE_OUTPUT                                                                    
-------------------------------------------------------------------------------------
SQL_ID  fuynj2z4vdjks, child number 0                                                
-------------------------------------                                                
select /*+ gather_plan_statistics */ count(*) from t3                                
                                                                                     
Plan hash value: 463314188                                                           
                                                                                     
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL| T3   |      1 |   1000 |   1000 |00:00:00.01 |       7 |
-------------------------------------------------------------------------------------
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
PLAN_TABLE_OUTPUT                                                                    
-------------------------------------------------------------------------------------
SQL_ID  5cunshnzqa8vb, child number 0                                                
-------------------------------------                                                
select /*+ gather_plan_statistics */ count(*) from t2                                
                                                                                     
Plan hash value: 3321871023                                                          
                                                                                     
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS FULL| T2   |      1 |      1 |      0 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
PLAN_TABLE_OUTPUT                                                                    
-------------------------------------------------------------------------------------
SQL_ID  cz662tawx6sbt, child number 0                                                
-------------------------------------                                                
select /*+ gather_plan_statistics */ count(*) from t1                                
                                                                                     
Plan hash value: 3724264953                                                          
                                                                                     
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------
                                                                                     



Again, very easy and powerful.

Written by Dion Cho

March 10, 2009 at 6:57 am

Posted in Misc.

Tagged with , ,

How was my parallel query executed last night? – AWR!

leave a comment »

In the previous post, I discussed on how to use v$pq_tqstat view to investigate parallel execution.

Now the question is “How was my parallel query executed last night? Well, was it executed in parallel anyway?”.

The answer is AWR – The king of historical data.

Oracle 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 likely to be captured than non-parallel ones.

See following test case.

1. Create objects

UKJA@ukja102> create table t1(c1 int, c2 char(100));
Table created.
UKJA@ukja102>
UKJA@ukja102> insert into t1
  2  select level, 'x'
  3  from dual connect by level <= 10000;
10000 rows created.

2. Create start snapshot and execute parallel query.

UKJA@ukja102>
UKJA@ukja102> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
UKJA@ukja102>
UKJA@ukja102> select /*+ parallel(t1 10) */ count(*)
  2  from t1
  3  ;
  COUNT(*)
----------
     10000
UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  6aubqkg2xfv20, child number 0
-------------------------------------
select /*+ parallel(t1 10) */ count(*) from t1
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |     5 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 11126 |     5   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T1       | 11126 |     5   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
   - dynamic sampling used for this statement
27 rows selected.
UKJA@ukja102>
UKJA@ukja102> select
  2    dfo_number, tq_id, server_type,    process,
  3    num_rows, bytes, avg_latency, waits, timeouts
  4  from v$pq_tqstat
  5  order by 1, 2, 3, 4, 5
  6  ;
DFO_NUMBER      TQ_ID SERVER_TYPE          PROCESS                NUM_ROWS      BYTES AVG_LATENCY      W
---------- ---------- -------------------- -------------------- ---------- ---------- ----------- ------
         1          0 Consumer             QC                           10        320           0
         1          0 Producer             P000                          1         32           0
         1          0 Producer             P001                          1         32           0
         1          0 Producer             P002                          1         32           0
         1          0 Producer             P003                          1         32           0
         1          0 Producer             P004                          1         32           0
         1          0 Producer             P005                          1         32           0
         1          0 Producer             P006                          1         32           0
         1          0 Producer             P007                          1         32           0
         1          0 Producer             P008                          1         32           0
         1          0 Producer             P009                          1         32           0
11 rows selected.

3. Create second snapshot and have an investigation on the parallel query through AWR – dba_hist_sql_plan and dba_hist_sqlstat.

UKJA@ukja102> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_awr('&sql_id'));
Enter value for sql_id: 6aubqkg2xfv20
old   1: select * from table(dbms_xplan.display_awr('&sql_id'))
new   1: select * from table(dbms_xplan.display_awr('6aubqkg2xfv20'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID 6aubqkg2xfv20
--------------------
select /*+ parallel(t1 10) */ count(*) from t1
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |     5 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10183 |     5   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       | 10183 |     5   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
22 rows selected.
UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> select sql_id, px_servers_execs_total, px_servers_execs_delta
  2  from dba_hist_sqlstat    
  3  where sql_id = '&sql_id'
  4         and snap_id = (select max(snap_id) from dba_hist_snapshot)
  5  ;
Enter value for sql_id: 6aubqkg2xfv20
old   3: where sql_id = '&sql_id'
new   3: where sql_id = '6aubqkg2xfv20'
SQL_ID        PX_SERVERS_EXECS_TOTAL PX_SERVERS_EXECS_DELTA
------------- ---------------------- ----------------------
6aubqkg2xfv20                     10                     10

With AWR, we can conclude that the query got executed in parallel with DOP of 10.

Yes, AWR is always fantastic.

The other way would be investigate the dba_hist_active_sess_history view, but it’s not guaranteed to contain all the corresponding sessions. But captured successfully, it would be the most useful historical data.

Written by Dion Cho

February 20, 2009 at 2:37 pm

dbms_stats.display_cursor vs. sql trace

leave a comment »

I’m an enthusiastic evangelist of dbms_xplan.display_cursor function(including other functions of dbms_xplan package) in local Oracle community.

Many thanks given for encouraging them to use dbms_xplan package. Considering the power of the dbms_xplan package, this appreciation is quite natural.

But there is a very frequent question on dbms_xplan.display_cursor fuction:

What’s the deal of dbms_xplan.display_cursor function when we have sql_trace(10046 trace) which seems more powerful?

My answer is:

These two are totally different tools!

Let me make a simple list for the features of each one.

What is so unique with dbms_xplan.display_cursor?

  • Easier to use(we don’t need to lookup local folders and FTP download of trace files)
  • With the gather_plan_statistics hint, we can gather very useful informations like e-rows, a-rows, buffer gets, physical reads, memory usage, and acutal elapsed time.
  • Unless the sql has been flushed out from shared pool, we can extract the plan information with sql_id and child_number whenever we want.
  • Since the result of dbms_xplan.display_cursor is pseudo table, we can use them in the query just like any other tables. This means that we can make customized query to meet our complex requirement.

Why do we still need sql_trace?

  • The real tracing including all recursive SQLs.
  • Tracking the entire steps of parses, executions and fetches.
  • Many essential informations including wait events.

We use dbms_xplan.display_cursor to retrieve the execution plans of the cursors loaded onto shared pool. On the contrary, sql_trace is used to trace every inch of the query execution. With gather_plan_statisitcs hint enabled, dbms_xplan.display_cursor function can be used as an alternative to sql_trace, but only in limited way.

The point is that we have 2 totally different tools which help us to analyze the performance of query execution. It’s up to you to use them in the right situation.

Written by Dion Cho

February 7, 2009 at 3:04 pm

Posted in Optimizer

Tagged with ,

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.

topnsql

  • 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
;

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

Written by Dion Cho

January 23, 2009 at 4:03 pm

Posted in Optimizer

Tagged with

A brief demo of dbms_xplan.display_awr

with 3 comments

Oracle 10g added the functionality to extract execution plans from AWR repository.

dbms_xplan.display_awr

It’s not that fantatisic than you might expect, but it’s still a very important information.

Following is a small test to show how helpful the dbms_xplan.display_awr function is.

First, create test objects.

drop table t1 purge;

create table t1(c1 int, c2 int);

insert into t1
select level, level
from dual
connect by level <= 10000
;

commit;

exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false);

Now I create the spooled result to generate 10,000 same select statement. This would enable the statement being captured by Oracle.

set heading off
set timing off
set feedback off
set serveroutput on size 100000
spool select.sql
select 'select /*+ awr */ * from t1 where c1 = 1;'
from dual
connect by level <= 10000;
spool off

Now I create snapshots and capture the sql statement.

exec dbms_workload_repository.create_snapshot;

@select

exec dbms_workload_repository.create_snapshot;

I create index to change the execution plan dramatically(Might be from FTS to index range scan).

create index t1_n1 on t1(c1);

exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false);

@select

exec dbms_workload_repository.create_snapshot;

We can extract sql_id from dba_hist_sqltext view. Fortunately, the select statement we just executed was successfuly captured.

col sql_id new_value v_sql_id

select sql_id, sql_text from dba_hist_sqltext
where sql_text like 'select /*+ awr */%';
-------------------------------------

aphq7ta15w8y3

Now we can extract the execution plans that Oracle built at runtime by simply calling dbms_xplan.display_awr. As you see, Oracle captured 2 different execution plans. One with FTS, and another with index range scan. This is exactly what we want.

select * from table(dbms_xplan.display_awr('&v_sql_id'));

SQL_ID aphq7ta15w8y3
--------------------
select /*+ awr */ * from t1 where c1 = 1

Plan hash value: 1420382924

---------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:
|   2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:
---------------------------------------------------------------------------------

SQL_ID aphq7ta15w8y3
--------------------
select /*+ awr */ * from t1 where c1 = 1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     6 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |     7 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Looks cool. How about “all” option? Query block and alias information is additionally provided.

select * from table(dbms_xplan.display_awr('&v_sql_id', null, null,  'all'));

SQL_ID aphq7ta15w8y3
--------------------
select /*+ awr */ * from t1 where c1 = 1

Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|   2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1

SQL_ID aphq7ta15w8y3
--------------------
select /*+ awr */ * from t1 where c1 = 1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     6 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |     7 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T1@SEL$1

Now I can extract more meaningful information from dbms_xplan.display_awr by using with other AWR history views.

col snap_id new_value v_snap_id

select max(snap_id) as snap_id from dba_hist_snapshot;

select
  s.elapsed_time_delta,
  s.buffer_gets_delta,
  s.disk_reads_delta,
  cursor(select * from table(dbms_xplan.display_awr(t.sql_id, s.plan_hash_value)))
from
  dba_hist_sqltext t,
  dba_hist_sqlstat s
where
  t.dbid = s.dbid
  and t.sql_id = s.sql_id
  and s.snap_id between &v_snap_id-2 and &v_snap_id
  and t.sql_text like 'select /*+ awr */%'
;

           1542490             40000                0 CURSOR STATEMENT : 4

CURSOR STATEMENT : 4

SQL_ID aphq7ta15w8y3
--------------------
select /*+ awr */ * from t1 where c1 = 1

Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|   2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

           5466666            240018               23 CURSOR STATEMENT : 4

CURSOR STATEMENT : 4

SQL_ID aphq7ta15w8y3
--------------------
select /*+ awr */ * from t1 where c1 = 1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     6 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |     7 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Really cool feature.

Written by Dion Cho

January 22, 2009 at 8:30 am

Posted in Optimizer

Tagged with

Follow

Get every new post delivered to your Inbox.

Join 61 other followers