Execution Plan

SET AUTOTRACE TRACEONLY STATISTICS EXPLAIN
select * from module where module_id=125;
==> compair rows processed with consistant gets

Costs and the Cost-based Optimizer
cost column in execution plan is not a reliable way to judge the real costs of a SQL statements response time. It is supposed to be a guess of number of single block reads required

The most common causes for inaccurate CBO costing estimates may include :

Bad table join order : Sub-optimal table join order can be caused by missing histogram
Inaccurate I/O estimates : Oracle uses the estimate of single block read time which can be inaccurate if system statistics are missing
Buffer caching estimate : If opimizer_index_caching is not set or if the content of the data buffers carry greatly, the optimizer can mis-estimate the I/O costs for a query optimization.

Reading Execution Plan

• Explain plan supplemental information
• Card : number of rows Optimizer guesses it will return, used to determine optimal table join order
• Bytes : This is the number of bytes that might be returned, a rough idea of the total baggage that must be passed to any subsequent steps
• The Nested Loops Join
• Driving table : usually uses table access full or index range scan access method, it is the table specificed first after nested loops table access method
• Second table : normally uses index unique scan method
• More : only one of indexes may be accessed as a range
• Phisical Disk reads : aw_top_tables_phyrd.sql

col c0 heading ‘Begin|Interval|time’ format a8

col c1 heading ‘Table|Name’

col c2 heading ‘Disk|Reads’

col c3 heading ‘Rows|Processed’

select

*

from (

select

format a20

format 99,999,999

format 99,999,999

     to_char(s.begin_interval_time,'mm-dd hh24') c0,

     p.object_name c1,

     sum(t.disk_reads_total) c2,

     sum(t.rows_processed_total) c3,

DENSE_RANK() OVER (PARTITION BY to_char(s.begin_interval_time,'mm-dd hh24') ORDER BY SUM(t.disk_reads_total) desc) AS rnk

from

  dba_hist_sql_plan   p,

  dba_hist_sqlstat t,

  dba_hist_snapshot s

where

   p.sql_id = t.sql_id

and

   t.snap_id = s.snap_id

and

   p.object_type like '%TABLE%'

group by

   to_char(s.begin_interval_time,'mm-dd hh24'),

   p.object_name

order by

c0 desc, rnk

)

where rnk <= 5;

• Index range scan : awr_sql_index_freq.sql

col c1 heading ‘Object|Name’         format a30

col c2 heading ‘Option’              format a15

col c3 heading ‘Index|Usage|Count’   format 999,999

select

p.object_name c1,

  p.options     c2,

  count(1)      c3

from

   dba_hist_sql_plan   p,

   dba_hist_sqlstat  s

where

   p.object_owner <> 'SYS'

and

   p.options like '%RANGE SCAN%'

and

   p.operation like ‘%INDEX%’

and

   p.sql_id = s.sql_id

group by

   p.object_name,

   p.operation,

   p.options

order by

  1,2,3;

• Total counts for each object and table access : aw_sql_object_freq.sql

col c1 heading ‘Object|Name’ format a30

col c2 heading ‘Operation’ format a15

col c3 heading ‘Option’ format a15

col c4 heading ‘Object|Count’ format 999,999

break on c1 skip 2

break on c2 skip 2

select

  p.object_name c1,

  p.operation   c2,

  p.options     c3,

  count(1)      c4

from

   dba_hist_sql_plan p,

   dba_hist_sqlstat  s

where

   p.object_owner <> 'SYS'

and

   p.sql_id = s.sql_id

group by

   p.object_name,

   p.operation,

   p.options

order by

  1,2,3;

•  Count number of access type : TFS, Index range scan, index unique access, index full scan
• plan9i.sql : v$sql_plan
• plan10g.sql : dba_hist_sql_plan
• Use case 1 : candidate for keep pool ( small TFS <2% db_cache_size), for recycle pool ( large TFS)
• Use case 2 :  Indexes that are accessed via ROWID ==> non-range scan access
• Tracing SQL execution history : dba_hist_sql_plan : dbms_xplan.display_awr
• Using SQL profiles : stored persistently in the data dictionary : generate a better execution plan than the normal optimisation because it is tested against a real-world workload in the SQL Tuning Set(STS)
•