RE: What's your opinion: ALL_ROWS vs. FIRST_ROWS **CLOSED**
Thanks everyone. I did decide on CHOOSE. It seemed like the best choice considering most of our queries aren't run interactively through tools like SQL*Plus. In fact it should become an even better choice as our applications are becoming more and more browser based. ALL_ROWS also looked like it would be the best choice considering our batch reporting requirements. Michael Armstead Principal Database Administrator, OCP-Certified World Wide Corporate IT Database Administration GlaxoSmithKline > -Original Message- > From: John Kanagaraj [SMTP:[EMAIL PROTECTED]] > Sent: Monday, November 04, 2002 3:39 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: What's your opinion: ALL_ROWS vs. FIRST_ROWS > > Hi Mike, > > > Your article was very good in describing RBO vs. CBO. I hope > > you don't mind > > me using it to help better describe why we're switching from > > RBO to CBO to > > my team. > > I forgot to mention this before, but you should also look at Tim Gorman's > excellent paper on the CBO at http://www.evdbt.com - search in the > library. > > > On the other hand it didn't help me much in practical use of ALL_ROWS > > (CHOOSE) vs. FIRST_ROWS. I understand this was out of the scope of the > > paper. > > Yes, but we can always discuss this here in the list :) The problem I have > with the FIRST_ROWS is that _all_ queries would default to use this mode. > While this may be good for a specific interactive query, it certainly > would > perform poorly for batch jobs, more so when the table joins larger tables. > IMHO, you are better off leaving it to default to CHOOSE and control > specific modes. This can be done via a variety of methods, including login > triggers, Outlines, anf finally the code itself. > > > Note: When leaving your web page I was notified that my IP address was > > captured. I didn't like that. > > My web page is served by Yahoo! Geocities, and I obtained that when it was > free (and still is free). I suppose that they capture some info and do > manipulate cookies. This is the first time, though that I have come across > such a message. I will need to investigate further... > > Hth, > John Kanagaraj > Oracle Applications DBA > DBSoft Inc > (W): 408-970-7002 > > What would you see if you were allowed to look back at your life at the > end > of your journey in this earth? > > ** The opinions and statements above are entirely my own and not those of > my > employer or clients ** > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: John Kanagaraj > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Armstead, Michael A INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: What's your opinion: ALL_ROWS vs. FIRST_ROWS
Hi Mike, > Your article was very good in describing RBO vs. CBO. I hope > you don't mind > me using it to help better describe why we're switching from > RBO to CBO to > my team. I forgot to mention this before, but you should also look at Tim Gorman's excellent paper on the CBO at http://www.evdbt.com - search in the library. > On the other hand it didn't help me much in practical use of ALL_ROWS > (CHOOSE) vs. FIRST_ROWS. I understand this was out of the scope of the > paper. Yes, but we can always discuss this here in the list :) The problem I have with the FIRST_ROWS is that _all_ queries would default to use this mode. While this may be good for a specific interactive query, it certainly would perform poorly for batch jobs, more so when the table joins larger tables. IMHO, you are better off leaving it to default to CHOOSE and control specific modes. This can be done via a variety of methods, including login triggers, Outlines, anf finally the code itself. > Note: When leaving your web page I was notified that my IP address was > captured. I didn't like that. My web page is served by Yahoo! Geocities, and I obtained that when it was free (and still is free). I suppose that they capture some info and do manipulate cookies. This is the first time, though that I have come across such a message. I will need to investigate further... Hth, John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: What's your opinion: ALL_ROWS vs. FIRST_ROWS
Michael - In Oracle9i it goes even further. Now there is FIRST_ROWS_1, FIRST_ROWS_10, and on up to _1000. Just thought you might want to take that in consideration. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, November 04, 2002 1:04 PM To: Multiple recipients of list ORACLE-L John, Your article was very good in describing RBO vs. CBO. I hope you don't mind me using it to help better describe why we're switching from RBO to CBO to my team. On the other hand it didn't help me much in practical use of ALL_ROWS (CHOOSE) vs. FIRST_ROWS. I understand this was out of the scope of the paper. Note: When leaving your web page I was notified that my IP address was captured. I didn't like that. Michael Armstead Principal Database Administrator, OCP-Certified World Wide Corporate IT Database Administration GlaxoSmithKline > -Original Message- > From: John Kanagaraj [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, October 31, 2002 12:54 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: What's your opinion: ALL_ROWS vs FIRST_ROWS > > Mike, > > Check this out and let me know if this is helpful. Larry has already > answered the main concern, but this paper may address some of the other > hidden mines :) > > http://www.geocities.com/john_sharmila/links.htm - click on the IOUG paper > link. > > John Kanagaraj > Oracle Applications DBA > DBSoft Inc > (W): 408-970-7002 > > What would you see if you were allowed to look back at your life at the > end > of your journey in this earth? > > ** The opinions and statements above are entirely my own and not those of > my > employer or clients ** > > > > -Original Message- > > From: Armstead, Michael A [mailto:maa25681@;GlaxoWellcome.com] > > Sent: Tuesday, October 29, 2002 1:14 PM > > To: Multiple recipients of list ORACLE-L > > Subject: What's your opinion: ALL_ROWS vs FIRST_ROWS > > > > > > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: John Kanagaraj > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Armstead, Michael A INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGur
RE: What's your opinion: ALL_ROWS vs. FIRST_ROWS
H. I had no such message using Opera 6.05 with Java/JS/Cookies off. Although, by default, IP logging is on for many webservers, so just about anywhere you surf to has "captured" your IP. Probably one reason more why we have a proxy server here at work. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Armstead, Michael A [mailto:maa25681@;GlaxoWellcome.com] > Sent: Monday, November 04, 2002 1:04 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: What's your opinion: ALL_ROWS vs. FIRST_ROWS > > > Note: When leaving your web page I was notified that my IP address was > captured. I didn't like that. > > Michael Armstead -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: What's your opinion: ALL_ROWS vs. FIRST_ROWS
John, Your article was very good in describing RBO vs. CBO. I hope you don't mind me using it to help better describe why we're switching from RBO to CBO to my team. On the other hand it didn't help me much in practical use of ALL_ROWS (CHOOSE) vs. FIRST_ROWS. I understand this was out of the scope of the paper. Note: When leaving your web page I was notified that my IP address was captured. I didn't like that. Michael Armstead Principal Database Administrator, OCP-Certified World Wide Corporate IT Database Administration GlaxoSmithKline > -Original Message- > From: John Kanagaraj [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, October 31, 2002 12:54 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: What's your opinion: ALL_ROWS vs FIRST_ROWS > > Mike, > > Check this out and let me know if this is helpful. Larry has already > answered the main concern, but this paper may address some of the other > hidden mines :) > > http://www.geocities.com/john_sharmila/links.htm - click on the IOUG paper > link. > > John Kanagaraj > Oracle Applications DBA > DBSoft Inc > (W): 408-970-7002 > > What would you see if you were allowed to look back at your life at the > end > of your journey in this earth? > > ** The opinions and statements above are entirely my own and not those of > my > employer or clients ** > > > > -Original Message- > > From: Armstead, Michael A [mailto:maa25681@;GlaxoWellcome.com] > > Sent: Tuesday, October 29, 2002 1:14 PM > > To: Multiple recipients of list ORACLE-L > > Subject: What's your opinion: ALL_ROWS vs FIRST_ROWS > > > > > > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: John Kanagaraj > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Armstead, Michael A INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: What's your opinion: ALL_ROWS vs FIRST_ROWS
Mike, Check this out and let me know if this is helpful. Larry has already answered the main concern, but this paper may address some of the other hidden mines :) http://www.geocities.com/john_sharmila/links.htm - click on the IOUG paper link. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** > -Original Message- > From: Armstead, Michael A [mailto:maa25681@;GlaxoWellcome.com] > Sent: Tuesday, October 29, 2002 1:14 PM > To: Multiple recipients of list ORACLE-L > Subject: What's your opinion: ALL_ROWS vs FIRST_ROWS > > > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: John Kanagaraj INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: What's your opinion: ALL_ROWS vs FIRST_ROWS
Regarding you dd query on extents, when you use FIRST_ROWS/ALL_ROWS, cost based is used whether an object has statistics or not. And when going against un-analyzed objects like the dictionary, where rule based is preferred, your dd scripts can really suffer because they will still use cost based methods. Your inserting the RULE hint into your dd script gets you back to rule (obvious man I am). Setting to choose would do the same as long as you don't have stats on the dd objects. Ran into this at a place where they had ALL_ROWS set -- lots of my scripts would take forever. On a side note, recursive SQL issued by the DB will still use CHOOSE even when you have set ALL_ROWS/FIRST_ROWS, and without stats on the dd objects, will then fall to rule. This "choose" mode can be seen in a raw trace file, indicated by the og=4 value (1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose). PARSING IN CURSOR #2 len=83 dep=1 uid=0 oct=3 lid=0 tim=102303376 hv=365454555 ad='2f1cfc8' select cols,audit$,textlength,intcols,property,flags,rowid from view$ where obj#=:1 END OF STMT PARSE #2:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=102303377 Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:root@;fatcity.com]On Behalf Of Bishop > Lewis > Sent: Thursday, October 31, 2002 3:19 AM > To: Multiple recipients of list ORACLE-L > Subject: 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 : [EMAIL PROTECTED] "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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: What's your opinion: ALL_ROWS vs FIRST_ROWS
Besides, I have seen the Oracle Consulting prefers to set optimizer to 'CHOOSE' wherever they did their project for customized application/databases(not Oracle Financials 10.7) Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 30 Oct 2002 10:00:09 -0800 I guess that's why when I take a look at the initialization files of databases in large shops where it's expected the DBAs know what they're doing, by and large the databases are running in CHOOSE (all rows if there are sufficient statistics) mode. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 30, 2002 11:19 AM > 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 <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Tom Pall INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). _ Unlimited Internet access -- and 2 months free! Try MSN. http://resourcecenter.msn.com/access/plans/2monthsfree.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: What's your opinion: ALL_ROWS vs FIRST_ROWS
I guess that's why when I take a look at the initialization files of databases in large shops where it's expected the DBAs know what they're doing, by and large the databases are running in CHOOSE (all rows if there are sufficient statistics) mode. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 30, 2002 11:19 AM > 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 <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Tom Pall INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: What's your opinion: ALL_ROWS vs FIRST_ROWS
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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: What's your opinion: ALL_ROWS vs FIRST_ROWS
This is purely subjective, but my gut feel has always been that first_rows bends too heavily toward index usage, and all_rows bengs too far away from it.. So we're stuck? Not really. You can find some useful middle ground with some tweaking of the optimizer_... parms in init.ora. Although "tweaking" sounds hideous, what you are really doing is giving the optimizer *more* accurate information..You're telling it the probability of index blocks being cached, the differential between a multiblock read and a single block read hth connor --- Mark Richard <[EMAIL PROTECTED]> wrote: > Michael, > > I think you are correct... OLTP tends to go for > FIRST_ROWS. OLAP tends to > go for ALL_ROWS. I say "tends" because I'm sure > there are a multitude of > reasons for selecting the other option. I guess you > need to look at the > queries being run against the database, and the > applications using those > queries. Will they benefit by receiving a partial > result first? If the > application can happily take the first few rows and > display them to the > screen then FIRST_ROWS might be good, but if the > application is going to > load them entire set into an array and then display > the first few to the > screen then you might as well select ALL_ROWS. The > difference can be that > subtle when you think about it. > > I think the difference is often negligable as well - > especially depending > on the query. If the query is only going to return > a few rows then it > won't really matter. Similarly, if the query has to > read a lot of rows and > perform some kind of sort / aggregate function then > there is little > opportunity to return the first rows until every row > has been sorted - > again, it won't really matter. The good news, > therefore, is that unless > you fall into the "I have lots of queries that > return large result sets > without performing sort operations" then it won't > make a big difference to > you performance. > > Cheers, > Mark. > > > > > > > "Armstead, Michael > > > A" To: > Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > > > lcome.com> > Subject: What's your opinion: ALL_ROWS vs > FIRST_ROWS > Sent by: > > > [EMAIL PROTECTED] > > > > > > > > > 30/10/2002 08:13 > > > Please respond to > > > ORACLE-L > > > > > > > > > > > > > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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
Re: What's your opinion: ALL_ROWS vs FIRST_ROWS
"Armstead, Michael A" wrote: > > 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 > Michael, FIRST_ROWS is when you have a nervous user waiting for his/her results - OLTP. ALL_ROWS is when you want all the job to be done as fast as possible - Batch. This is the broad picture. Now, if your queries are used to generate HTML pages, this is no longer OLTP - it's batch since you cannot return the page before the query is completed. HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: What's your opinion: ALL_ROWS vs FIRST_ROWS
Michael, I think you are correct... OLTP tends to go for FIRST_ROWS. OLAP tends to go for ALL_ROWS. I say "tends" because I'm sure there are a multitude of reasons for selecting the other option. I guess you need to look at the queries being run against the database, and the applications using those queries. Will they benefit by receiving a partial result first? If the application can happily take the first few rows and display them to the screen then FIRST_ROWS might be good, but if the application is going to load them entire set into an array and then display the first few to the screen then you might as well select ALL_ROWS. The difference can be that subtle when you think about it. I think the difference is often negligable as well - especially depending on the query. If the query is only going to return a few rows then it won't really matter. Similarly, if the query has to read a lot of rows and perform some kind of sort / aggregate function then there is little opportunity to return the first rows until every row has been sorted - again, it won't really matter. The good news, therefore, is that unless you fall into the "I have lots of queries that return large result sets without performing sort operations" then it won't make a big difference to you performance. Cheers, Mark. "Armstead, Michael A" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: What's your opinion: ALL_ROWS vs FIRST_ROWS Sent by: [EMAIL PROTECTED] 30/10/2002 08:13 Please respond to ORACLE-L 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). <<>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it.
