| 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)?
Please excuse me if I don’t make myself clear enough.
My personal discovery is that LIO is not good indicator of any SQL
effectiveness.
I tried to show it by some examples.
In first case approximately the same by execution time (cpu utilization)
queries differs in LIO by 200 times.
In second case tow queries exactly the same by LIO count, differs 4 times
in cpu time.
The meaning of SQL-s is insignificant. Significant is measurement of SQL
effectiveness.
I sow many references in books of famous Oracle experts, that LIO is the
indicator on CPU utilization.
The same I have told others for the years:
- If you need to detect top CPU consumers, then take a look on TOP SQL-s
sorted by LIO-s.
- From two executions plans of the same SQL the fastest will be one with
minimum LIO-s.
As we can see statements above is not true. My question is: What is the best method to detect TOP CPU consumers?
At the moment I see just some more or less accurate methods:
- Usage of CPU_TIME column in V$SQL view.
What you would say?
Jurijs
PS I am new in this list. I will try to learn how to express myself more clear in English ;)
"Mark W. Farnham" <mwf_at_rsiz.com>
Sent by: oracle-l-bounce_at_freelists.org
29.06.2004 16:30
Please respond to oracle-l
To: <oracle-l_at_freelists.org>
cc:
Subject: RE: How to compare two different SQL executions
plans (question is more deeper then seams from first look)?
I'm not sure what you're trying to ask.
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.
I still think the answer to the question that I suggest you meant to ask
is
"Yes."
Still, I'm not entirely sure I understood what you were trying to suggest and if someone has a better idea I'd love to be enlightened.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of J.Velikanovs_at_alise.lv
Sent: Tuesday, June 29, 2004 6:58 AM
To: oracle-l_at_freelists.org
Subject: How to compare two different SQL executions plans (question is
more deeper then seams from first look)?
PS In this mail context I am speaking about CPU resource utilization by
SQL-s. All we know that CPU resource is the main HW resource that executes
code of our systems.
I am 7 years working as Oracle DBA.
Before this day I have told Developers and other Oracle users from my
country, that the main SQL efficiency indicator is buffer gotten (LIO)
during execution of particular SQL.
Fastest from two SQL (or SQL execution plans) will be SQL with lower LIO
indicator.
HA HA HA!!!
I have two TESTCASE-s to proof that we can't use method above.
(I have suspicion that those are just two from many.We can find many
different cases.)
vvvvvvvvvvvvvvvv vvvvvvvvvvvvvvvv
TOPs SQL by Buffers) for identifying ineffective SQL-s. ^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^
It is looks like: I am going to tell Developers to go back to old method
for SQL execution plans effectiveness evaluation:
"Take your watch and just measure the execution time"
No No No! It not going to work effective then buffer gets method (Cary
Millsap perfectly described this in “Oracle Perfomance” book).
Am in prostration ;(
I don't know that to tell peoples!
I don't see any appropriate method how to compare SQL-s effectiveness.
Any ideas?
In hope to find best method,
Thanks in advance,
Jurijs
9268222
SQL2
Query=5616
Elapsed=42.98
Query/sec=130 blocs/sec
COMMENTS:
- Approximately the same execution time, but 200 times different LIO count
!!!Conclusion!!!:
SQL1 execute LIO 200 times effective then SQL2.
Imagine if with the same Query/sec indicators SQL-s will query the same
LIO count for example
vvvvvvvvvvvvvvvv SQL1 LIO = SQL2 LIO = 100 000 ,then ^^^^^^^^^^^^^^^^
vvvvvvvvvvvvvvvv
SQL1 cpu time * 200 = SQL2 cpu time ^^^^^^^^^^^^^^^^
vvvvvvvvvvvvvvvv SQL1q=SQL2q and SQL1cpu*4=SQL2cpu ^^^^^^^^^^^^^^^^
!!!Conclusion!!!
Two SQL-s, the same LIO count, but 4 times lower cpu consumption by SQL1
then SQL2.
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------- ------ -------- ---------- ---------- ---------- ----------
Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 1)
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)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
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------- ------ -------- ---------- ---------- ---------- ----------
Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 1)
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)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
TESTCASE 2 (output from 10046)
SELECT count(*)
from
(select * from testsort)
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1000 0.11 0.09 0 0 0 0 Fetch 1000 49.12 49.27 0 23000 0 1000------- ------ -------- ---------- ---------- ---------- ----------
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT AGGREGATE (cr=23000 r=0 w=0 time=49263863 us) 10000000 TABLE ACCESS FULL TESTSORT (cr=23000 r=0 w=0 time=26198104 us)
SELECT count(*)
from
(select * from testsort order by 1)
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1000 0.21 0.20 0 0 0 0 Fetch 1000 173.38 175.41 0 23000 0 1000------- ------ -------- ---------- ---------- ---------- ----------
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT AGGREGATE (cr=23000 r=0 w=0 time=175399815 us) 10000000 VIEW (cr=23000 r=0 w=0 time=150958286 us) 10000000 SORT ORDER BY (cr=23000 r=0 w=0 time=102002996 us) 10000000 TABLE ACCESS FULL TESTSORT (cr=23000 r=0 w=0 time=27638643 us)
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------Received on Tue Jun 29 2004 - 15:47:03 CDT
 |
 |