Re: Learning more about and reading 10053 trace files

  • From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
  • To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 5 Sep 2006 12:56:18 -0500

Nothing like a little bit of humble pie.....

On 9/5/06, Charles Schultz <sacrophyte@xxxxxxxxx> wrote:

I was going to send this to the list, but I need to think about it some more - I do not want to look like a complete idiot in a public forum. *grin* But for your amusement.....

Thanks, Brandon, that does make sense. However, this itch in the back of
my brain will not go away. Hypothetically, I would think that the optimizer
would still cost out the process of doing a "first k row" optimization.
Obviously, as we all well know, FIRST_ROWS_10 is not limited to returning 10
rows - it merely aims to bring back 10 rows "cheaper". Sometimes
FIRST_ROWS_10 can bring back all rows "faster" (wall clock time) than
ALL_ROWS. Also, the DISTINCT operation happens last, after all the joins,
right? It is not clear in my explain plan, but from other examples I have
worked with, this seemed to be the case.

I guess I need to do a little more reading and testing on this topic.

On 9/5/06, Allen, Brandon < Brandon.Allen@xxxxxxxxxxx> wrote: > > Hi Charles - I just read your post quickly and I'm not sure about this, > but it sounds to me like maybe there is no "First K" rows calculation for > the DISTINCT query because anytime you have a DISTINCT, GROUP BY, ORDER BY > or other clause that forces all the data to be fetched and sorted prior to > being returned to the user, it makes it impossible to do any type of "FIRST > ROWS" processing - because you can't just grab the first 1, 10, or 100 rows > until you grab them all and process them as requested, so it makes sense > that the CBO wouldn't even consider the cost for such an access method. > > Regards, > Brandon > > Privileged/Confidential Information may be contained in this message or > attachments hereto. Please advise immediately if you or your employer do not > consent to Internet email for messages of this kind. Opinions, conclusions > and other information in this message that do not relate to the official > business of this company shall be understood as neither given nor endorsed > by it. >

Charles Schultz

-- Charles Schultz

Other related posts: