Congratulations Stephane -- a good lesson for us all.
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Tuesday, April 01, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
The case is not as bad as the subject may let you believe, but not
quoting Disreali was above my strength ... Anyway, an interesting case
encountered today. Basically, a dreadful query, involving a reasonably
big table with an innate tree structure (the stuff connect bys are made
of), accessed through a view with outer joins, user-written functions
called for each line, and search for text (upper(column) like '%STUFF%')
in a number of related columns. The row must be returned whether the
text is found for the row itself, a descendent or an ascendent, which
means that the text search is found twice in two different START WITH
subqueries. The query of hell.
The execution plan is made of a whopping 176 steps. In spite of all
this, the execution time is close to 25s, which is not that bad, given
the context.
There would be much to say about the design, now we're in 'fix it' mode.
Noting the huge number of 'nested loops', the first attempt is to try
the ALL_ROWS hint, to see whether hash joins couldn't improve our case.
Here is the result :
original all_rows
Steps in plan 176 166
recursive calls 259 1776
db block gets 72 324
consistent gets 474556 6700
physical reads 12497 1981
redo size 152 21736
bytes to client 3060 3060
bytes from client 5811 4500
SQL*Net roundtrips 2 2
memory sorts 10 13
disk sorts 0 0
rows 6 6
Elapsed time 24.75 > 4 mn
Although LIOs have drastically reduced, and so have PIOs, our time is
about 10 times worse!
BCHR zealots will note that our original 97% hit-ratio has become a 72%
hit-ratio, but the reason is more likely to be found in the HUGE
increase of recursive calls and the surprising increase of the 'redo
size' stat (why does it generate redo? It's a SELECT ... Must be
something weird going on). I had no access to the server, which
prevented me from tracing, and not much time (this is a customer I visit
only once a week - results first) which means that I have not had time
to inquire about events. But wait, it gets weirder.
After having tried the easy solution, I dug into rewriting. Here are the
stats for 3 successive rewritings :
original 1st rewriting 2nd rewriting 3rd rewriting
Steps in plan 176 112 158 113
recursive calls 259 259 259 252
db block gets 72 66 60 69
consistent gets 474556 80276 376501 80727
physical reads 12497 1068 2109 1406
redo size 152 0 0 0
bytes to client 3060 3060 3060 3060
bytes from client 5811 3705 5306 3803
SQL*Net roundtrips 2 2 2 2
memory sorts 10 10 10 11
disk sorts 0 0 0 0
rows 6 6 6 6
Elapsed time 24.75
Look at the values. How would you rank the variations ?
Answer below
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1st rewriting: 38.06
2nd rewriting: 13.48
3rd rewriting: 3.56
Not an April's fools joke.
A moral to the story? SET TIMING ON. What matters is elapsed time, not
stats. That said, I must check events next week.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
