Dion Cho – Oracle Performance Storyteller

We are natural born scientists

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.

About these ads

Written by Dion Cho

January 22, 2009 at 8:30 am

Posted in Optimizer

Tagged with

3 Responses

Subscribe to comments with RSS.

  1. Hi Dion,
    a two little questions.
    1- Why you execute
    exec dbms_stats.gather_table_stats(user, ‘t1′, no_invalidate=>false);
    and not
    exec dbms_stats.gather_table_stats(user, ‘t1′, cascade=true, no_invalidate=>false);
    for statistics estimate after index creation?
    2- is requisite parameter no_invalidate=>false for your testcase?

    Thanks.

    Sandro

    February 9, 2009 at 11:22 am

  2. Hi Sandro.

    As of 10g, Oracle gathers index statistics when index is created or rebuilt(_optimizer_compute_index_stats parameter). So there’s no need to recollect the index statistics except for some special cases.

    As to “no_invalidate=>false” parameter, this is not requisite.

    1. With table t1 creation, all cursors dependent on table t1 would have been invalidated.

    2. With index creation on table t1, all cursors dependent on table t1 would have been invalidated.

    So, no need to invalidate the current cursor. It’s just copied from my common test scripts. Sorry for the confusing.

    Dion Cho

    February 9, 2009 at 12:37 pm

  3. Nice.

    dbametrix

    November 8, 2009 at 5:21 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

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: