| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to compare two different SQL executions plans (question is more deeper then seams from first look)?
Apologies - assertion (b) below is complete rubbish. I crossed over the two sets of results whilst doing the division by execution count - so much for the paperless office :(
It would be quite easy, though, to build an example that matched my assertions - i.e. two 'iso-functional' statement where the one with the LOWER CPU usage did far more logical I/O.
However, the impact of the example comes from the fact that for the same CPU cost we reduce the LIO by a factor of more than 200, but increase the execution count by a factor of only 3.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
The example is demonstrating the relevant of the question that you quoted, but from the opposite direction to normal.
The two SQL statements repeated below
are 'iso-functional' - in fact the performance
tuning guide tells us that the optimizer may
choose to convert from the IN form to the
EXISTS form before optimizing.
The demonstration is showing, though, that for a constant amount of CPU (ca. 45 seconds)
and
b) The statement that can be executed more times
for the same amount of CPU is the one that would be highlighted by many tools as the more expensive statement. (viz. any tool that does a 'top 10' display by LIO - which is one of the commonest performance monitoring strategies around).
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
Are you asking "If, given two iso-functional SQL-s operating on the same dataset, is it usually true that the one which causes fewer LIOs is overall cheaper in systems resources?" then I think I'm not sure what your examples have to do with the question.
In the first case, example one gets 800,000 rows in 3.3 seconds from the full table scan and example two gets 2.4 million rows in 7.5 seconds. Are these the same datasets? A baseline select count(m.v) from main_tab m might be illustrative. It appears that this full table scan is required (unless there are resources inapparent in what is given, and hash won't be used in RULE.) In the second case, you're requiring an extraneous (and apparently purposeless) sort operation.
Any iso-functional SQL can be tweaked to add extraneous use of CPU. If you take two comparable queries and impose extra all in memory CPU use on one of them, you indeed mask the usefulness of LIO as a thumbrule. So take the one with comparable LIO that uses less CPU.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of J.Velikanovs_at_alise.lv
TESTCASE 1 (output from 10046)
m.n in
(select f.n from
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 4 44.48 46.17 562 1201856 0 4
Rows Row Source Operation
------- ---------------------------------------------------
2 SORT AGGREGATE (cr=801234 r=562 w=0 time=31244261 us)
800000 NESTED LOOPS (cr=801234 r=562 w=0 time=28962918 us)
800000 TABLE ACCESS FULL MAIN_TAB (cr=1230 r=562 w=0 time=3309977 us)
800000 TABLE ACCESS BY INDEX ROWID FILTER_TAB (cr=800004 r=0 w=0
time=16177875 us)
800000 INDEX UNIQUE SCAN FILTER_TAB_I1 (cr=4 r=0 w=0 time=5226711
us)(object id 9699)
SELECT /*+ RULE */ count(m.v) from main_tab m where
exists
(select v from
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0 Execute 12 0.01 0.00 0 0 0 0 Fetch 12 42.38 42.97 1686 5616 0 12
Rows Row Source Operation
------- ---------------------------------------------------
6 SORT AGGREGATE (cr=3738 r=1686 w=0 time=28675198 us) 2400000 FILTER (cr=3738 r=1686 w=0 time=22306438 us) 2400000 TABLE ACCESS FULL MAIN_TAB (cr=3690 r=1686 w=0 time=7478255 us)
24 TABLE ACCESS BY INDEX ROWID FILTER_TAB (cr=48 r=0 w=0 time=524 us)
24 INDEX UNIQUE SCAN FILTER_TAB_I1 (cr=24 r=0 w=0 time=242 us)(object id 9699)
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Wed Jun 30 2004 - 04:03:39 CDT
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
 |
 |