Stephane,
Three things I don't need to tell you about
your example:
Prior to CPU-costing in 9, the order of
single table predicates can have a dramatic
impact on CPU without changing the amount
of logical I/O.
Logical I/O should always include the statistics
buffer is pinned count - it's just
Had to forward this to the God of Obfuscated SQL here. He in the past created a
select * from bom_with_assembly_steps query in a Forms3 application that could take
as long as an hour to complete. Something on similar lines to what you have. Thank
GOD for Explain SQL/Platinum Plan Analyzer!!
Stephanie,
I've ran into the exact same thing that statistics don't always tell the
real story and that the bottom-line has to be elapsed time.
I am curious however as to how you wrote the best performing (fastest)
query. I have a client who uses the exact same structure and methodology
you
Once upon a time, I had a developer come to my desk complaining that their
query ran very poorly that day, Monday, and that it ran fine on Friday. And
since we had a snapshot of the DB as of that Friday, the dev showed me. The
dev also said that the only change was that WE changed the statistics
Thanks Stephane (1000 pardons on the earlier spelling FO-PAH!).
-Original Message-
Faroult
Sent: Wednesday, April 02, 2003 2:29 PM
To: Multiple recipients of list ORACLE-L
Karen Morton wrote:
Stephanie,
Cough, cough.
I've ran into the exact same thing that statistics don't always
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
-Original Message-
From: Stephane Faroult
interesting tuning case study snipped
A moral to the story? SET TIMING ON. What matters is elapsed time, not
stats. That said, I must check events next week.
Another lesson, which probably wouldn't make any difference in your
case, is
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,
