RE: What's your opinion: ALL_ROWS vs FIRST_ROWS

Yes, we have noticed significant response problems after upgrading from 8.0.5 to 8.1.7.4 using FIRST_ROWS. It seems that something new in 8i is affecting onwards affects the performance.

For example we have a standard DBA script to collect information on extents against a specified user/table and response went up to 282 seconds. Inserting the RULE hint in the script returned the response time back to it's normal 3 seconds. Not had time to investigate but there are obviously some implications here with FIRST_ROWS.  

Lewis Bishop

---
Barclays Enable - ISS - E-NTRUST/Bexleyheath NT
Oracle Database Consultant
Watling Street, Bexleyheath, Kent, DA6 7RR (Mail Van R)
Phone : 020 8298 3418
Mobile: 07950 380857
Email : lewis.bishop_at_woolwich.co.uk
"Enabling Competitive Advantage for Barclays in IT and Business Processing"
 

-----Original Message-----
Sent: 30 October 2002 17:19
To: Multiple recipients of list ORACLE-L

We have an OLTP system that I thought will benefit from first rows.
The sad fact is that when I set optimization to first rows the response
sucks.

Do some testing, as you can change this anytime.

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent: Tuesday, October 29, 2002 11:13 PM




> We're moving from RBO to CBO.

>

> For those of you who use CBO, what mode do you use FIRST_ROWS or ALL_ROWS?

> And why?

>

> My thinking is if it's a database where most of the querying is done on

> small sets of records, then we may want to use FIRST_ROWS. On the other

> hand, if our database is used to generate sizable reports, we might use

> ALL_ROWS.

>

> I also understand that we can always change it per session (with alter

> session) and per query (with hints).

>

> Michael Armstead

> Principal Database Administrator, OCP-Certified

> World Wide Corporate IT Database Administration

> GlaxoSmithKline

>

>

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> --

> Author: Armstead, Michael A

>   INET: maa25681_at_GlaxoWellcome.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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yechiel Adar
  INET: adar76_at_inter.net.il

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.com
-- 
Author: Bishop Lewis
  INET: Lewis.Bishop_at_woolwich.co.uk

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).