Home » RDBMS Server » Performance Tuning » understanding execution plans
understanding execution plans [message #64718] Thu, 08 January 2004 10:59 Go to next message
woodchuck
Messages: 12
Registered: May 2003
Junior Member
hihi,

does anyone know a good resource for understanding execution plans?

for example, i want to be able to tell of the following plans, which one is better? (faster):

Execution Plan #1
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=24 Card=16 Bytes=430
)

1 0 INTERSECTION
2 1 SORT (UNIQUE) (Cost=8 Card=10 Bytes=340)
3 2 TABLE ACCESS (FULL) OF 'REPORTABLE_SUBJECT' (Cost=1 Ca
rd=10 Bytes=340)

4 1 SORT (UNIQUE) (Cost=9 Card=6 Bytes=90)
5 4 NESTED LOOPS (Cost=2 Card=6 Bytes=90)
6 5 TABLE ACCESS (FULL) OF 'APC_PROGRAM' (Cost=1 Card=1
Bytes=7)

7 5 INDEX (RANGE SCAN) OF 'PK_CURRICULUM_SUBJECT_RULE' (
UNIQUE) (Cost=1 Card=95 Bytes=760)

Execution Plan #2
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=1 Bytes=49)
1 0 SORT (UNIQUE) (Cost=10 Card=1 Bytes=49)
2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=49)
3 2 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=41)
4 3 TABLE ACCESS (FULL) OF 'APC_PROGRAM' (Cost=1 Card=1
Bytes=7)

5 3 SORT (JOIN) (Cost=1 Card=1 Bytes=34)
6 5 TABLE ACCESS (FULL) OF 'REPORTABLE_SUBJECT' (Cost=
1 Card=1 Bytes=34)

7 2 INDEX (RANGE SCAN) OF 'PK_CURRICULUM_SUBJECT_RULE' (UN
IQUE) (Cost=1 Card=95 Bytes=760)

these execution plans are generated by two different select statements that return the exact same results

thx in advance for any help!
Re: understanding execution plans [message #64719 is a reply to message #64718] Thu, 08 January 2004 14:15 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
not trying to be smart, but the one that executes faster(hope you timed it) consistently in a given environement,is the better !

The 2nd one has lesser cardinality and Bytes, but involves a sort join and a merge join and so you have to test it for larger result sets too .It also depends on if you are looking for faster 1st row(response) or all the rows(throughput).
Oracle Performance tuning guide is a good resource for understanding execution plans.

-Thiru
Re: understanding execution plans [message #64722 is a reply to message #64719] Fri, 09 January 2004 05:49 Go to previous messageGo to next message
woodchuck
Messages: 12
Registered: May 2003
Junior Member
thx Thiru.

i use toad, and everytime i execute the select statements the bottom left corner displays the time in msecs.

i noticed the execution times are not consistent. each time i run the query i can get a different time, but i consistently see several identical execution times per query. (ie. if i execute the query many times over and over, i see the same different execution times over and over... why i do not know)

here are the results:

Execution Plan #1 has execution times of:
- 1 msecs
- 15 msecs
- 16 msecs

Execution Plan #2 has execution times of:
- 15 msecs
- 16 msecs
- 31 msecs
- 32 msecs

so based on that, i think it's safe to say that Execution Plan #1 is faster than Execution Plan #2.

these 2 queries give the exact same results, but are written differently.

the only difference between the 2 queries is that query #1 is using the INTERSECT keyword to get the results, while query #2 is joining the two tables normally.

i knew that my query #1 is faster, but i wanted to be able to know this from looking at the execution plans.

is it possible? (to tell which execution plan is faster by looking at them?) or does it always depend on the tables/data involved as well? (given that the two queries give the exact same result and work on the exact same tables, but just written differently)

thx in advance again!
Re: understanding execution plans [message #64723 is a reply to message #64722] Fri, 09 January 2004 08:24 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Quote "(ie. if i execute the query many times over and over, i see the same different execution times over and over... why i do not know)"

is becos you will be reading from the memory(if the result set is small) and hence no physical reads. The consistent gets will almost remain the same(for a given array length) .You can confirm this using sqlplus autotrace

SQL> set autotrace on
SQL> set timing on
SQL> select count(*) from t where owner='THIRU';

  COUNT(*)
----------
         0

<B>Elapsed: 00:00:00.63</B>

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1 Bytes=5)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=40 Card=1079 Bytes=5395
          )

Statistics
----------------------------------------------------------
         37  recursive calls
          0  db block gets
     <B>407  consistent gets</B>
     <B>398  physical reads</B>
          0  redo size
        378  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from t where owner='THIRU';

  COUNT(*)
----------
         0

<B>Elapsed: 00:00:00.22</B>

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1 Bytes=5)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=40 Card=1079 Bytes=5395
          )

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     <B> 401  consistent gets</B>
 <B>      0  physical reads</B>
          0  redo size
        378  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

-- Now you are reading only from the cache.No physical reads.

SQL> select count(*) from t where owner='THIRU';

  COUNT(*)
----------
         0

<B>Elapsed: 00:00:00.22</B>
-- The execution time remains almost the same hereafter

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1 Bytes=5)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=40 Card=1079 Bytes=5395
          )

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    <B>  401  consistent gets</B>
<B>       0  physical reads</B>
          0  redo size
        378  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


We can only guess which execution plan 'might' be faster..it depends on so many things ( your optimizer settings,selectivity of the indexes,sort_area_size,hash__area_size(pga_aggregate_target/workarea_size_policy),size of the tables/indexes,db_multiblock_read_count,system statistics etc etc..
Just becos the execution plan says that an index is being used, it does not mean that the query will be executed faster.Some times FTS are much faster. Some times FTS are much much slower...you need a ton of details before you can tell if a particular execution plan is good or bad. The easiest thing is test it out ..:-)

HTH
Thiru
Previous Topic: re: freespace in database(oracle8i)
Next Topic: Reg Partition
Goto Forum:
  


Current Time: Fri Mar 29 02:54:22 CDT 2024