| Explain Plan - Which is the best? [message #64847] |
Thu, 19 February 2004 01:53  |
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  |
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 |
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 |
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 |
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 #64860 is a reply to message #64857] |
Fri, 20 February 2004 09:24 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
