Home » RDBMS Server » Performance Tuning » Explain Plan - Which is the best?
Explain Plan - Which is the best? [message #64847] Thu, 19 February 2004 01:53 Go to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi,

I have 2 explain plans on a same query (but using different index)!

EXPLAIN PLAN 1

----------------

SELECT STATEMENT, GOAL = CHOOSE                                                                                                            4              1                              18



NESTED LOOPS                                                                                                                                                     4              1                              18



  TABLE ACCESS BY INDEX ROWID                   SMS_USER              SERVICE_OP_PRICE                              3              1                              9



    INDEX UNIQUE SCAN                                       SMS_USER              CONS_SOP_UK                                       2              1             



  TABLE ACCESS BY INDEX ROWID                   SMS_USER              PRICE_GROUP                                       1              16                            144



    INDEX UNIQUE SCAN                                       SMS_USER              PRGR_PRICE_GROUP_ID_PK                                               16           

EXPLAIN PLAN 2

---------------

SELECT STATEMENT, GOAL = CHOOSE   4 1 20
 NESTED LOOPS   4 1 20
  INDEX RANGE SCAN SMS_USER IDX_SOP_PRICE_GROUP_ID 3 1 9
  TABLE ACCESS BY INDEX ROWID SMS_USER PRICE_GROUP 1 492 5412
   INDEX UNIQUE SCAN SMS_USER PRGR_PRICE_GROUP_ID_PK  492 

Is the EXPLAIN PLAN 2 best??

Thank you for your answers!

Best regards,

Patrick Tahiri.
Re: Explain Plan - Which is the best? [message #64849 is a reply to message #64847] Thu, 19 February 2004 05:30 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Patrick,
Is this a continuation of the previous thread ? or you have different sets of indexes here
Whats the number of consistent gets/timings on these ?
The first one looks cheaper.

-Thiru
Re: Explain Plan - Which is the best? [message #64850 is a reply to message #64847] Thu, 19 February 2004 05:45 Go to previous messageGo to next message
Michel Bartov
Messages: 35
Registered: February 2003
Member
I don’t think it is easy to be able to tell that an explain plan is better than the other just by looking at it (unless it is obvious – full scan of a big table compare to using indexes). I did develop a GUI tool, one of the many functions is to use different scenarios (rule, choose, first rows ...), including hints and each time execute the query (asynchronously so it can be killed if necessary) and based on the true response time, you choose the best tuning for a particular query. It is a free trial and I will appreciate any feedback. The link is http://www.barsoft.net/

Michel.
Re: Explain Plan - Which is the best? [message #64856 is a reply to message #64849] Thu, 19 February 2004 21:58 Go to previous messageGo to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi Thiru,

I red your article yesterday: it's great! I used then SQL+ to set autotrace on and set timing on! Then, I got my statistics!
The timing between my to expalin plain are nearly the same! BUT the consistent get and the recursive sql are clearly very different!
I would have thought that timing and consistent get are very correlated, but it doesn't seems that they are so much!??
Consistent gets: number of data blocks accessed in READ CONSISTENT mode. In order to maintain statement level read consistency, Oracle has to read the blocks in a consistent fashion(as of the snapshot SCN) and hence may fetch from rollback segments , which is also added to this statistic.
Are these read made in memory area or from the datafile (disk)? I would think in memory if the concerned blocks are in memory and on disk if... :-)

Thank you for your help!

As you know I'm new as an Oracle DBA (I used to be a Sybase DBA). I have some Oracle skills but they are quite "schoolar" (I'm OCA, but just 2 monthes experience!), and I really fell the difference between the theory and the real life! It's "fun", but I learn so much each day (and thank you for this!!), that I'm coming home exhausted after work! :-)

Kind regards! You are doing a great job and effort on this forum, I'm amazed! Congratulations!

Patrick Tahiri.
Re: Explain Plan - Which is the best? [message #64857 is a reply to message #64849] Thu, 19 February 2004 22:03 Go to previous messageGo to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Just one thing more about this subject!
The main criterias to determine which EXECUTION PLAN is the best:
1.The one which got less CONSISTENT GETS
2.The one which got less TIMING
3.The one which got less PHYSICAL READ
...

Is it a good ranking?
It seems that they are all important to me and especially closely related/corelated!! Is it possible to have one execution plan with less consistent gets than the other but the other have less timing? Which one is better then for exemple?

Again: thank you for your precious tips!

Patrick Tahiri.
Re: Explain Plan - Which is the best? [message #64859 is a reply to message #64856] Fri, 20 February 2004 08:45 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Patrick,
No problem.
Consistent gets include memory reads and physical reads.

-Thiru
Re: Explain Plan - Which is the best? [message #64860 is a reply to message #64857] Fri, 20 February 2004 09:24 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
You got it! , almost. Just note that when you go for a hash join using full table scans, which might be the best execution plan, in some cases, the physical reads tends to be high ,but its still a good plan especially when your db_file_multiblock_read_count is high and have very fast I/O system.

In general the one with the less logical reads(consistent gets+db block gets) should consume less cpu time and hence likely to return your result set faster.
The sqlplus timing is not as accurate as the cpu/elapsed timings you get from the trace.Eventually its the response/throughput that matters,right?

-Thiru
Previous Topic: SYSTEM STATISTICS
Next Topic: cost based optimizer
Goto Forum:
  


Current Time: Fri Mar 29 10:18:00 CDT 2024