Re: OCP Question (Perf Tuning)

From: Jay Wade <fish_dba_at_hotmail.com>
Date: Tue, 06 Jan 2004 13:05:00 -0800
Message-ID: <F001.005DBE00.20040106130500@fatcity.com>

Thanks for the help.
Do you have any ideas to the FIRST_ROWS behavior in regards to NL?

I've been looking and found

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#39473

it goes into detail about using hints for joins but now how the FIRST_ROWS forces a join selection on anything other then sample size. I'm interested to know more about the behavior and the links that I am finding don't seem to offer the depth I thought they would. Or maybe they do and I'm missing the boat....

>From: "Ryan" <ryan_oracle_at_cox.net>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: OCP Question (Perf Tuning)
>Date: Tue, 06 Jan 2004 11:34:26 -0800
>
>thought so, I'm not 100% certain the OCP will say that though. alot of
>inaccuracies in that test.
>
>btw, Ive been playing with first_rows lately. I've noticed that it has a
>preference for 'INDEX FULL SCAN' over 'INDEX RANGE SCAN'. Ive found that in
>some test cases where you have two tables approximately 3m and 1.5m rows in
>size, that INDEX RANGE SCAN actually returns the first 25 or so records
>faster, than 'INDEX FULL SCAN', there by making FIRST_ROWS, inferior.
>
>Surprised me. I've read some docs on this and I think that a range scan is
>always preferably when you only want a few rows? What am I missing?
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Tuesday, January 06, 2004 1:59 PM
>
>
> > Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to
>all other
> > methos despite the price.
> >
> > On 2004.01.06 13:44, Jay Wade wrote:
> > > Hello:
> > >
> > > I was looking through some OCP questions posted on the web and came
>across
> > > the one below.
> > > I believe the answer is (D), because the join type would be dependent
>on
>the
> > > number of rows within the table. Is this correct or does the
>OPTIMIZER_MODE
> > > set to FIRST_ROWS alter this behavior?
> > >
> > >
> > > The cost-based optimizer can choose between a nested loops join and a
>sort
> > > merge join operation. All tables are analyzed and the OPTIMIZER_MODE
>is
>set
> > > to FIRST_ROWS. Which execution plan will be the result?
> > >
> > > a. The sort-merge join.
> > >
> > > b. The nested loops join.
> > >
> > > c. This depends on some sort parameter values.
> > >
> > > d. This depends on the number of rows in each table.
> > >
> > > _________________________________________________________________
> > > Check your PC for viruses with the FREE McAfee online computer scan.
> > > http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Jay Wade
> > > INET: fish_dba_at_hotmail.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).
> > >
> >
> > --
> > Mladen Gogala
> > Oracle DBA
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Mladen Gogala
> > INET: mgogala_at_adelphia.net
> >
> > 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).
> >
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Ryan
> INET: ryan_oracle_at_cox.net
>
>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).

Working moms: Find helpful tips here on managing kids, home, work — and yourself. http://special.msn.com/msnbc/workingmom.armx

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay Wade
  INET: fish_dba_at_hotmail.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).

Received on Tue Jan 06 2004 - 15:05:00 CST