Dion Cho – Oracle Performance Storyteller

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.