Re: ANALYZE question
Thanks all for your advice. Will check an option to do estimate. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, July 24, 2002 7:23 PM > At the last Open World I attended a couple of sessions > where the general advice for 9i DB is to use ANALYZE > ESTIMATE without specifying ANY value. A few brief > "comparision" tests did show that it got better results > than the alternatives tested. > > As always, YMMV & HTH > > HAND! > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Charlie Mengler > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ANALYZE question
At the last Open World I attended a couple of sessions where the general advice for 9i DB is to use ANALYZE ESTIMATE without specifying ANY value. A few brief "comparision" tests did show that it got better results than the alternatives tested. As always, YMMV & HTH HAND! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ANALYZE question
I looked into the same thing some time ago. I found Oracle papers that
recommend using estimate 25% on the tables and a full analyze on the
indexes.
I wrote the script below to generate a script with all the analyze
statements I needed for the schema to be analyzed. I added date and time
displays so I could see how far along I am while the script is processing.
I run the output script on a weekly basis.
Ron Smith
set feedback off;
set heading off;
set pagesize 0;
set linesize 120;
set heading off;
set feedback off;
set show off;
spool analyze2.sql
select 'set feedback off;'|| CHR(10) || 'set heading off;' || CHR(10) ||
'set pagesize 0;' || 'set linesize 80;'|| CHR(10) |
| 'set heading off;' || CHR(10) || 'set feedback off;' || CHR(10) || 'spool
analyze_schema2.lst;' from dual
/
select 'select ' || '''' || object_type || ' ' || object_name || '''' ||',
to_char(sysdate, ''MM/DD/ HH24:MI'') from dua
l;'|| CHR(10) || 'analyze '|| object_type || ' ' || owner || '.' ||
object_name || ' estimate statistics sample 25 percent f
or table;' from dba_objects where object_type in('TABLE') and owner = 'PROD'
order by object_type desc
/
select 'select ' || '''' || object_type || ' ' || object_name || '''' ||',
to_char(sysdate, ''MM/DD/ HH24:MI'') from dua
l;'|| CHR(10) || 'analyze '|| object_type || ' ' || owner || '.' ||
object_name || ' compute statistics;' from dba_objects w
here object_type in('INDEX') and owner = 'PROD' order by object_type desc
/
This is what part of the output script looks like:
set feedback off;
set heading off;
set pagesize 0;
set linesize 80;
set heading off;
set feedback off;
spool analyze_schema2.lst;
select 'TABLE DIST', to_char(sysdate, 'MM/DD/ HH24:MI') from dual;
analyze TABLE PROD.DIST estimate statistics sample 25 percent for table;
select 'TABLE EXCEPTIONS', to_char(sysdate, 'MM/DD/ HH24:MI') from dual;
analyze TABLE PROD.EXCEPTIONS estimate statistics sample 25 percent for
table;
Ron Smith
-Original Message-
Sent: Wednesday, July 24, 2002 11:25 AM
To: Multiple recipients of list ORACLE-L
Yechiel
Consider ANALYZE TABLE ESTIMATE STATISTICS.
If you consider sampling theory, with larger tables you shouldn't need to
inspect each row. You can vary the number of rows that are sampled. We had a
good discussion on this topic on this list awhile back.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, July 24, 2002 9:59 AM
To: Multiple recipients of list ORACLE-L
On the subject on analyzed:
We are doing analyze compute statistics and it takes about an hour.
Do you know of ways to speed it up?
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Wednesday, July 24, 2002 3:54 PM
> On Wed, Jul 24, 2002 at 04:48:41AM -0800, Boivin, Patrice J wrote:
> > A question:
> >
> > If analyzing SYS objects is a bad idea, why is it included by default in
the
> > analyzing commands (dbms_stats, analyze, dbms_utility.analyze_database)?
>
>
> bug, Doc ID: 203003.996, fixed in 9i...I hate it when that happens.
>
>
>
> >
> > Please correct me if my assumption is wrong, we had strange behaviour
here
> > when SYS objects were analyzed on a development db.
> >
> > Regards,
> > Patrice Boivin
> > Systems Analyst (Oracle Certified DBA)
> >
> > Systems Admin & Operations | Admin. et Exploit. des systèmes
> > Technology Services| Services technologiques
> > Informatics Branch | Direction de l'informatique
> > Maritimes Region, DFO | Région des Maritimes, MPO
> >
> > E-Mail: [EMAIL PROTECTED]
> >
> >
> > -Original Message-
> > Sent: Wednesday, July 24, 2002 9:09 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: ANALYZE question
> >
> > DBMS_STATS can be used to analyze tables.
> >
> > Dave
> >
> > -Original Message-
> > Sent: Wednesday, July 24, 2002 4:03 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Apart from explicity running an ANALYZE command against a table, what,
if
> > any, other events/actions can cause an analyze to be run on the table?
> >
> > -
> > Seán O' Neill
> > Organon (Ireland) Ltd.
> > [subscribed: digest mode]
> >
RE: ANALYZE question
Yechiel Consider ANALYZE TABLE ESTIMATE STATISTICS. If you consider sampling theory, with larger tables you shouldn't need to inspect each row. You can vary the number of rows that are sampled. We had a good discussion on this topic on this list awhile back. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:59 AM To: Multiple recipients of list ORACLE-L On the subject on analyzed: We are doing analyze compute statistics and it takes about an hour. Do you know of ways to speed it up? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, July 24, 2002 3:54 PM > On Wed, Jul 24, 2002 at 04:48:41AM -0800, Boivin, Patrice J wrote: > > A question: > > > > If analyzing SYS objects is a bad idea, why is it included by default in the > > analyzing commands (dbms_stats, analyze, dbms_utility.analyze_database)? > > > bug, Doc ID: 203003.996, fixed in 9i...I hate it when that happens. > > > > > > > Please correct me if my assumption is wrong, we had strange behaviour here > > when SYS objects were analyzed on a development db. > > > > Regards, > > Patrice Boivin > > Systems Analyst (Oracle Certified DBA) > > > > Systems Admin & Operations | Admin. et Exploit. des systèmes > > Technology Services| Services technologiques > > Informatics Branch | Direction de l'informatique > > Maritimes Region, DFO | Région des Maritimes, MPO > > > > E-Mail: [EMAIL PROTECTED] > > > > > > -Original Message- > > Sent: Wednesday, July 24, 2002 9:09 AM > > To: Multiple recipients of list ORACLE-L > > Subject: RE: ANALYZE question > > > > DBMS_STATS can be used to analyze tables. > > > > Dave > > > > -Original Message- > > Sent: Wednesday, July 24, 2002 4:03 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Apart from explicity running an ANALYZE command against a table, what, if > > any, other events/actions can cause an analyze to be run on the table? > > > > - > > Seán O' Neill > > Organon (Ireland) Ltd. > > [subscribed: digest mode] > > > > This message, including attached files, may contain confidential > > information and is intended only for the use by the individual > > and/or the entity to which it is addressed. Any unauthorized use, > > dissemination of, or copying of the information contained herein is > > not allowed and may lead to irreparable harm and damage for which > > you may be held liable. If you receive this message in error or if > > it is intended for someone else please notify the sender by > > returning this e-mail immediately and delete the message. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: O'Neill, Sean > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > 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: Farnsworth, Dave > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > 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: Boivin, Patrice J > > INET: [EMAIL PROTE
Re: ANALYZE question
On Wed, Jul 24, 2002 at 06:53:34AM -0800, Boivin, Patrice J wrote: > I get no hits when I try to see bug 203003.996 in Metalink. I do, try the advanced search, maybe. > > Regards, > Patrice Boivin > Systems Analyst (Oracle Certified DBA) > > Systems Admin & Operations | Admin. et Exploit. des systèmes > Technology Services| Services technologiques > Informatics Branch | Direction de l'informatique > Maritimes Region, DFO | Région des Maritimes, MPO > > E-Mail: [EMAIL PROTECTED] > > > -Original Message- > Sent: Wednesday, July 24, 2002 10:55 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: ANALYZE question > > This message uses a character set that is not supported by the Internet > Service. To view the original message content, open the attached message. > If the text doesn't display correctly, save the attachment to disk, and then > open it using a viewer that can display the original character set. << File: > message.txt >> > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Boivin, Patrice J > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ANALYZE question
On the subject on analyzed: We are doing analyze compute statistics and it takes about an hour. Do you know of ways to speed it up? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, July 24, 2002 3:54 PM > On Wed, Jul 24, 2002 at 04:48:41AM -0800, Boivin, Patrice J wrote: > > A question: > > > > If analyzing SYS objects is a bad idea, why is it included by default in the > > analyzing commands (dbms_stats, analyze, dbms_utility.analyze_database)? > > > bug, Doc ID: 203003.996, fixed in 9i...I hate it when that happens. > > > > > > > Please correct me if my assumption is wrong, we had strange behaviour here > > when SYS objects were analyzed on a development db. > > > > Regards, > > Patrice Boivin > > Systems Analyst (Oracle Certified DBA) > > > > Systems Admin & Operations | Admin. et Exploit. des systèmes > > Technology Services| Services technologiques > > Informatics Branch | Direction de l'informatique > > Maritimes Region, DFO | Région des Maritimes, MPO > > > > E-Mail: [EMAIL PROTECTED] > > > > > > -Original Message- > > Sent: Wednesday, July 24, 2002 9:09 AM > > To: Multiple recipients of list ORACLE-L > > Subject: RE: ANALYZE question > > > > DBMS_STATS can be used to analyze tables. > > > > Dave > > > > -Original Message- > > Sent: Wednesday, July 24, 2002 4:03 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Apart from explicity running an ANALYZE command against a table, what, if > > any, other events/actions can cause an analyze to be run on the table? > > > > - > > Seán O' Neill > > Organon (Ireland) Ltd. > > [subscribed: digest mode] > > > > This message, including attached files, may contain confidential > > information and is intended only for the use by the individual > > and/or the entity to which it is addressed. Any unauthorized use, > > dissemination of, or copying of the information contained herein is > > not allowed and may lead to irreparable harm and damage for which > > you may be held liable. If you receive this message in error or if > > it is intended for someone else please notify the sender by > > returning this e-mail immediately and delete the message. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: O'Neill, Sean > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > 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: Farnsworth, Dave > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > 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: Boivin, Patrice J > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
RE: ANALYZE question
I get no hits when I try to see bug 203003.996 in Metalink. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 10:55 AM To: Multiple recipients of list ORACLE-L Subject: Re: ANALYZE question This message uses a character set that is not supported by the Internet Service. To view the original message content, open the attached message. If the text doesn't display correctly, save the attachment to disk, and then open it using a viewer that can display the original character set. << File: message.txt >> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ANALYZE question
On Wed, Jul 24, 2002 at 04:48:41AM -0800, Boivin, Patrice J wrote: > A question: > > If analyzing SYS objects is a bad idea, why is it included by default in the > analyzing commands (dbms_stats, analyze, dbms_utility.analyze_database)? bug, Doc ID: 203003.996, fixed in 9i...I hate it when that happens. > > Please correct me if my assumption is wrong, we had strange behaviour here > when SYS objects were analyzed on a development db. > > Regards, > Patrice Boivin > Systems Analyst (Oracle Certified DBA) > > Systems Admin & Operations | Admin. et Exploit. des systèmes > Technology Services| Services technologiques > Informatics Branch | Direction de l'informatique > Maritimes Region, DFO | Région des Maritimes, MPO > > E-Mail: [EMAIL PROTECTED] > > > -Original Message- > Sent: Wednesday, July 24, 2002 9:09 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: ANALYZE question > > DBMS_STATS can be used to analyze tables. > > Dave > > -Original Message- > Sent: Wednesday, July 24, 2002 4:03 AM > To: Multiple recipients of list ORACLE-L > > > Apart from explicity running an ANALYZE command against a table, what, if > any, other events/actions can cause an analyze to be run on the table? > > - > Seán O' Neill > Organon (Ireland) Ltd. > [subscribed: digest mode] > > This message, including attached files, may contain confidential > information and is intended only for the use by the individual > and/or the entity to which it is addressed. Any unauthorized use, > dissemination of, or copying of the information contained herein is > not allowed and may lead to irreparable harm and damage for which > you may be held liable. If you receive this message in error or if > it is intended for someone else please notify the sender by > returning this e-mail immediately and delete the message. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: O'Neill, Sean > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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: Farnsworth, Dave > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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: Boivin, Patrice J > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ANALYZE question
A question: If analyzing SYS objects is a bad idea, why is it included by default in the analyzing commands (dbms_stats, analyze, dbms_utility.analyze_database)? Please correct me if my assumption is wrong, we had strange behaviour here when SYS objects were analyzed on a development db. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject: RE: ANALYZE question DBMS_STATS can be used to analyze tables. Dave -Original Message- Sent: Wednesday, July 24, 2002 4:03 AM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ANALYZE question
DBMS_STATS can be used to analyze tables. Dave -Original Message- Sent: Wednesday, July 24, 2002 4:03 AM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ANALYZE question
No Impact. Analyze would just collect the latest statistics for the concerned table and the next time any query gets fired on this table the optimizer (CBO) would generate the execution plan based on these statistics. I also believe that this would be healthier sign as the CBO is generating plans as per the latest what is available and not on the stale ones. Moreover, it would be good that instead of Using Analyze you should use DBMS_STATS.gether_table_statistics stored procedure as it : 1) DBMS_STATS can run in parallel mode and hence would be faster than Analyze (which is a serial operation) 2) Only if the statistics are stale the DBMS_STATS would execute. 3) You can always Export/Import/Set the statistics from one db to another db. Thanks, Vikas Khanna -Original Message- Sent: Wednesday, July 24, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Vikas Khanna INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ANALYZE question
dbms_stats --- "O'Neill, Sean" <[EMAIL PROTECTED]> wrote: > Apart from explicity running an ANALYZE command against a table, > what, if > any, other events/actions can cause an analyze to be run on the > table? > > - > Seán O' Neill > Organon (Ireland) Ltd. > [subscribed: digest mode] > > This message, including attached files, may contain confidential > information and is intended only for the use by the individual > and/or the entity to which it is addressed. Any unauthorized use, > dissemination of, or copying of the information contained herein is > not allowed and may lead to irreparable harm and damage for which > you may be held liable. If you receive this message in error or if > it is intended for someone else please notify the sender by > returning this e-mail immediately and delete the message. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: O'Neill, Sean > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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). __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ANALYZE question
An export has default parameter of STATISTICS=ESTIMATE. If such an exported file is imported , the default import parameter ANALYZE=Y will result in the import utility executing the analyze stmts in dump file. -Original Message- Sean Sent: Wednesday, July 24, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
ANALYZE question
Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Analyze Question -- How CBO uses column statistics for non-in
Jared, Thanks for replying. You are right about binds and histograms. I didn't mean to imply "real" histograms, where the number of buckets (size) are specified. Instead, in this case, a simple "analyze table foo compute statistics" was used. This would result in "column statistics" for *all* columns, indexed or not, but, with only one bucket, number of distinct values, high, low, density etc in the XXX_TAB_COLUMNS view. By the same token, only two rows (endpoint 0 and endpoint 1) for each column would show up in XXX_Histograms. So, yeah, while there are column stats there, "real" histograms weren't done. But still, that is a very good point to bring up. With that said, using binds in place of literals, I still get different plans for different combinations of criteria with those basic column "stats" in place. But, if generation of column stats on the non-indexed columns is *bypassed* (analyze table foo_a compute statistics for table for all indexed columns for all indexes), I would *always* get FTS's on both tables and an HJ regardless of the combinations of criteria. So, even if the column stats were very basic and not "real" histograms, they still influenced the plan based on criteria specified vs not having them at all. I don't know that I ever mentioned it during this thread -- I was trying to be as brief as possible (for me). This all started because a DBA I know was looking for a way to speed up his nightly analyze (while still using compute). One thought he had was to use the "for table for all indexed columns for all indexes" instead of "analyze table foo_a compute statistics" so that the collection of column stats would be bypassed for the non-indexed columns and only collected on the indexed columns. His hopes were that this would speed up the analyze a bit (on 7.3.4.3). He called me and wanted to know if the lack of column stats on the non-indexed columns might influence plans the CBO selects. I couldn't immediately think of a scenario where this would have an impact, thus the question to the list. Subsequent to that, I came up with a few scenarios, and, people have mentioned others. Funny, though, that you mention binds and histograms. I am working with an app where literals are heavily used -- I will see 500 versions of the exact same SQL statement in V$SQLAREA where the only thing that differs is the *literal* value used for the PK criteria (this is repeated for many other SQL statements as well). But, on a 17 million row table they have, there is an index on a Y/N column. 7,000 rows contain Y, the remaining few million have N. They created a histogram on the column, and, the only two queries that hit the table by that column look for a value of Y (so we should use the index). In the case of those two queries, though, they decided to use *binds* (for a change). So, the histogram is worthless and an FTS is performed (well, not anymore ;-) ). Regards, Larry G. Elkins [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Larry, You might want to give this a try using PL/SQL and bind variables. I don't know if this has changed in 8i, but in 8.x, queries using bind variables could not make use of histograms ( column statistics ) Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: larry elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Analyze Question -- How CBO uses column statistics for non-in
Larry, You might want to give this a try using PL/SQL and bind variables. I don't know if this has changed in 8i, but in 8.x, queries using bind variables could not make use of histograms ( column statistics ) Jared On Sat, 3 Feb 2001, larry elkins wrote: > Sam, > > You had the right topic. The original questions was "In what way do > statistics (or lack thereof) on non-indexed columns influence the CBO?". I > was having trouble thinking of a scenario where this would make a > difference, hence my posing the question to the list. > > I finally thought of a scenario and threw it out to the list. That's the > email you responded to. Anyway, I eventually had a chance to test the > scenario. Created 2 tables, A and B, each containing 50,000 rows. Each have > a indexed column containing consecutive integer values from 1 to 50,000 and > a one to one relationship between the tables on this column. Each table also > has a second *non-indexed* column. For table A, the second column contains > 25,000 distinct values with each individual value occurring 2 times. For > table B, the second column contains only 2 distinct values, 0 and 1, with > each value occurring 25,000 times. So, I have an index on the tables to > support a join between the 2 tables, and, no indexes on the second column in > each table. I did a generic analyze compute which would include generating > stats on the non-indexed columns. > > And yes, the CBO would use the stats on the non-indexed column on table A to > decide whether to join to table B using an indexed NLJ, or, an FTS and HJ > (and in some cases an FTS and SMJ). After deleting the stats and > re-analyzing so that stats on the non-indexed columns were not generated, > the CBO always chose, at least on my test cases, to do an FTS on each and > use an HJ. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Analyze Question -- How CBO uses column statistics for non-in
They most definitely non indexed will have influence on the CBO - there is the ANALYZE FOR ALL COLUMNS option anyhow, that is there for that reason. I think also likely used in conjunction with histograms on fields - DSS / Data Warehouse : Regards Sam -Original Message- Sent: Saturday, February 03, 2001 6:47 PM To: Multiple recipients of list ORACLE-L non-in Sam, You had the right topic. The original questions was "In what way do statistics (or lack thereof) on non-indexed columns influence the CBO?". I was having trouble thinking of a scenario where this would make a difference, hence my posing the question to the list. I finally thought of a scenario and threw it out to the list. That's the email you responded to. Anyway, I eventually had a chance to test the scenario. Created 2 tables, A and B, each containing 50,000 rows. Each have a indexed column containing consecutive integer values from 1 to 50,000 and a one to one relationship between the tables on this column. Each table also has a second *non-indexed* column. For table A, the second column contains 25,000 distinct values with each individual value occurring 2 times. For table B, the second column contains only 2 distinct values, 0 and 1, with each value occurring 25,000 times. So, I have an index on the tables to support a join between the 2 tables, and, no indexes on the second column in each table. I did a generic analyze compute which would include generating stats on the non-indexed columns. And yes, the CBO would use the stats on the non-indexed column on table A to decide whether to join to table B using an indexed NLJ, or, an FTS and HJ (and in some cases an FTS and SMJ). After deleting the stats and re-analyzing so that stats on the non-indexed columns were not generated, the CBO always chose, at least on my test cases, to do an FTS on each and use an HJ. Anyway, as you mentioned, there might be other situations as well. Besides looking at the plans themselves, I also used information from Steve Adams' site, http://www.ixora.com.au/home.htm, for info on setting event 10053 and interpreting it's output so that I could "look" into the CBO's head and see how it evaluated and made it's decisions. Regards, Larry G. Elkins [EMAIL PROTECTED] -Original Message- Roberts (ZADCO ITIS) Sent: Friday, February 02, 2001 11:45 PM To: Multiple recipients of list ORACLE-L non-in I missed out on original e-mail regarding this subject so I hope I'm on the right track, but I assume that the CBO will use non-indexed columns in its algorithms. I know DB2 would use non-indexed columns, maybe because DB2 is a more advanced optimizer(only because IBM have been doing it a lot longer than Oracle). I can see one use for the optimizer to use column stats - when a non leading column of a composite index is used in a where.okay yes it is still part of the index but it would probably use the info from analyze in tab_column. There is probably plenty more with the new features like star joins where the optimizer builds tables on the fly. Sam -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: larry elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Sam P. Roberts (ZADCO ITIS) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Analyze Question -- How CBO uses column statistics for non-in
Sam, You had the right topic. The original questions was "In what way do statistics (or lack thereof) on non-indexed columns influence the CBO?". I was having trouble thinking of a scenario where this would make a difference, hence my posing the question to the list. I finally thought of a scenario and threw it out to the list. That's the email you responded to. Anyway, I eventually had a chance to test the scenario. Created 2 tables, A and B, each containing 50,000 rows. Each have a indexed column containing consecutive integer values from 1 to 50,000 and a one to one relationship between the tables on this column. Each table also has a second *non-indexed* column. For table A, the second column contains 25,000 distinct values with each individual value occurring 2 times. For table B, the second column contains only 2 distinct values, 0 and 1, with each value occurring 25,000 times. So, I have an index on the tables to support a join between the 2 tables, and, no indexes on the second column in each table. I did a generic analyze compute which would include generating stats on the non-indexed columns. And yes, the CBO would use the stats on the non-indexed column on table A to decide whether to join to table B using an indexed NLJ, or, an FTS and HJ (and in some cases an FTS and SMJ). After deleting the stats and re-analyzing so that stats on the non-indexed columns were not generated, the CBO always chose, at least on my test cases, to do an FTS on each and use an HJ. Anyway, as you mentioned, there might be other situations as well. Besides looking at the plans themselves, I also used information from Steve Adams' site, http://www.ixora.com.au/home.htm, for info on setting event 10053 and interpreting it's output so that I could "look" into the CBO's head and see how it evaluated and made it's decisions. Regards, Larry G. Elkins [EMAIL PROTECTED] -Original Message- Roberts (ZADCO ITIS) Sent: Friday, February 02, 2001 11:45 PM To: Multiple recipients of list ORACLE-L non-in I missed out on original e-mail regarding this subject so I hope I'm on the right track, but I assume that the CBO will use non-indexed columns in its algorithms. I know DB2 would use non-indexed columns, maybe because DB2 is a more advanced optimizer(only because IBM have been doing it a lot longer than Oracle). I can see one use for the optimizer to use column stats - when a non leading column of a composite index is used in a where.okay yes it is still part of the index but it would probably use the info from analyze in tab_column. There is probably plenty more with the new features like star joins where the optimizer builds tables on the fly. Sam -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: larry elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Analyze Question -- How CBO uses column statistics for non-in
I missed out on original e-mail regarding this subject so I hope I'm on the right track, but I assume that the CBO will use non-indexed columns in its algorithms. I know DB2 would use non-indexed columns, maybe because DB2 is a more advanced optimizer(only because IBM have been doing it a lot longer than Oracle). I can see one use for the optimizer to use column stats - when a non leading column of a composite index is used in a where.okay yes it is still part of the index but it would probably use the info from analyze in tab_column. There is probably plenty more with the new features like star joins where the optimizer builds tables on the fly. Sam p.s. if I missed the boat on this topic ,please ignore -Original Message- Sent: Friday, February 02, 2001 5:51 PM To: Multiple recipients of list ORACLE-L non-indexed columns Steve, Thanks for taking the time to reply. My initial thought was that the stats would be of no use, but, the thing that still makes me wonder is why would a simple "analyze table compute statistics" gather statistics on *all* columns. For this to be the default behavior, there could be a reason. That's why I threw the question out to the list, trying to see if I was overlooking something obvious. I see your answer below, but, one scenario hit me this morning and I thought I would run it by you and the list and let people take shots at it. Assume I have an SQL query joining two tables equal in size and the CBO views the cost of an FTS against either as being the same, and, there is an index supporting joining the tables. In addition to the join criteria, criteria against *non-indexed* columns in each of the two tables are specified. No *indexed* columns criteria, other than the join itself, is specified. So, the CBO needs to choose one of the tables as the "driving" table, doing an FTS, and, needs to decide whether to use an NL, MJ, or HJ between the two. Does it make sense, in this case, that the column stats for the non-indexed column could influence the choice of the driving table and/or the join method, that a generalized selectivity would be construed based on the number of distinct values captured for the columns? Sure, an FTS on at least one table would still be required, but, stats on the non-indexed columns could influence which table to drive by and which join method to use? My "gut" feeling is that in a case such as the one just described, stats on non-indexed columns could influence the access path and join method determined by the CBO. I picked up info on event 10053 from your site. I'll play around with that this weekend. Regards, Larry G. Elkins [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 01, 2001 9:48 PM To: larry elkins; Multiple recipients of list ORACLE-L Hi Larry, I think that the answer is that the statistics are of no use to the CBO until an index is created. @ Regards, @ Steve Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: larry elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Sam P. Roberts (ZADCO ITIS) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Analyze Question -- How CBO uses column statistics for non-indexed columns
Steve, Thanks for taking the time to reply. My initial thought was that the stats would be of no use, but, the thing that still makes me wonder is why would a simple "analyze table compute statistics" gather statistics on *all* columns. For this to be the default behavior, there could be a reason. That's why I threw the question out to the list, trying to see if I was overlooking something obvious. I see your answer below, but, one scenario hit me this morning and I thought I would run it by you and the list and let people take shots at it. Assume I have an SQL query joining two tables equal in size and the CBO views the cost of an FTS against either as being the same, and, there is an index supporting joining the tables. In addition to the join criteria, criteria against *non-indexed* columns in each of the two tables are specified. No *indexed* columns criteria, other than the join itself, is specified. So, the CBO needs to choose one of the tables as the "driving" table, doing an FTS, and, needs to decide whether to use an NL, MJ, or HJ between the two. Does it make sense, in this case, that the column stats for the non-indexed column could influence the choice of the driving table and/or the join method, that a generalized selectivity would be construed based on the number of distinct values captured for the columns? Sure, an FTS on at least one table would still be required, but, stats on the non-indexed columns could influence which table to drive by and which join method to use? My "gut" feeling is that in a case such as the one just described, stats on non-indexed columns could influence the access path and join method determined by the CBO. I picked up info on event 10053 from your site. I'll play around with that this weekend. Regards, Larry G. Elkins [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 01, 2001 9:48 PM To: larry elkins; Multiple recipients of list ORACLE-L Hi Larry, I think that the answer is that the statistics are of no use to the CBO until an index is created. @ Regards, @ Steve Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: larry elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Analyze Question -- How CBO uses column statistics for non-indexed columns
Hi Larry, I think that the answer is that the statistics are of no use to the CBO until an index is created. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 2 February 2001 11:16 To: Multiple recipients of list ORACLE-L non-indexed columns Listers, 7.3.4.3 database on HP-UX 11.0. In what way do statistics (or lack thereof) on non-indexed columns influence the CBO? I've searched high and wide for any info on this and came up with nothing, and, I can't think of a reason or example off the top of my head. And no, this isn't an OCP, homework (I wish I were that young), or interview question. Simply a question a friend called and asked me. Regards, Larry G. Elkins [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Analyze Question -- How CBO uses column statistics for non-indexed columns
Listers, 7.3.4.3 database on HP-UX 11.0. In what way do statistics (or lack thereof) on non-indexed columns influence the CBO? I've searched high and wide for any info on this and came up with nothing, and, I can't think of a reason or example off the top of my head. And no, this isn't an OCP, homework (I wish I were that young), or interview question. Simply a question a friend called and asked me. Regards, Larry G. Elkins [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: larry elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
