Lies, damn lies and statistics

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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).