| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: analyze stats question
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C080C2.1495DB70
Content-Type: text/plain;
charset="iso-8859-1"
I have had the same experience, and in higher versions of oracle as well. At the client i have in mind, the ESTIMATE/COMPUTE difference made a CATASTROPHIC difference in query planning, and once we did a single COMPUTE, all went back to normal.
Clearly a "work in progress" !
:-)
-----Original Message-----
From: Brian Wisniewski [mailto:brian_wisniewski_at_yahoo.com]
Sent: Wednesday, January 17, 2001 2:26 PM
To: Multiple recipients of list ORACLE-L
Subject: analyze stats question
Version 8.0.5.2.1
I'm getting some interesting results when analyzing tables using different estimate sizes and was hoping someone can explain why I'm seeing these results.
I'm analyzing a partition of a table which contains nearly 800,000 rows
(for that partition). I've used 5, 15, 25, 45 percent and 10000 rows
and the exact same results show up for all 'analyze' columns in
dba_tab_partitions and dba_part_col_statistics. When I calculate the
statistics the only thing that changes is num_distinct in
dba_part_col_statistics for 2 columns. The difference is about 110,000
distinct values which I suspect would be enough to change the values
when using the various different estimate values. Whenever I've looked
closely at this before I've never seen the compute and estimate come up
with the exact same numbers for num rows with large tables but that is
what is happening here.
Wouldn't you think that with a difference of 110,000 distinct values in a 800,000 row partition that estimate 5% and estimate 45% would generate different values for column stats and even num_rows? The estimate calculations return results in about 6 seconds and the compute is taking about 5 minutes. It seems like the estimate is using cached values no matter what the estimate size is but there are too many people using the database to shutdown the database and flush the buffers.
Any thoughts?
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian Wisniewski INET: brian_wisniewski_at_yahoo.com 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Wed Jan 17 2001 - 14:14:22 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C080C2.1495DB70 Content-Type: text/html; charset="iso-8859-1" <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1"> <META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2653.12"> <TITLE>RE: analyze stats question</TITLE> </HEAD> <BODY> <P><FONT SIZE=2>I have had the same experience, and in higher</FONT> <BR><FONT SIZE=2>versions of oracle as well. At the client i have</FONT> <BR><FONT SIZE=2>in mind, the ESTIMATE/COMPUTE difference made</FONT> <BR><FONT SIZE=2>a CATASTROPHIC difference in query planning, and</FONT> <BR><FONT SIZE=2>once we did a single COMPUTE, all went back to normal. </FONT> </P> <P><FONT SIZE=2>Clearly a "work in progress" !</FONT> </P> <P><FONT SIZE=2>:-)</FONT> </P> <P><FONT SIZE=2>-----Original Message-----</FONT> <BR><FONT SIZE=2>From: Brian Wisniewski [<A HREF="mailto:brian_wisniewski_at_yahoo.com">mailto:brian_wisniewski_at_yahoo.com</A>]</FONT> <BR><FONT SIZE=2>Sent: Wednesday, January 17, 2001 2:26 PM</FONT> <BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=2>Subject: analyze stats question</FONT> </P> <BR> <P><FONT SIZE=2>Version 8.0.5.2.1</FONT> </P> <P><FONT SIZE=2>I'm getting some interesting results when analyzing tables using</FONT> <BR><FONT SIZE=2>different estimate sizes and was hoping someone can explain why I'm</FONT> <BR><FONT SIZE=2>seeing these results.</FONT> </P> <P><FONT SIZE=2>I'm analyzing a partition of a table which contains nearly 800,000 rows</FONT> <BR><FONT SIZE=2>(for that partition). I've used 5, 15, 25, 45 percent and 10000 rows</FONT> <BR><FONT SIZE=2>and the exact same results show up for all 'analyze' columns in</FONT> <BR><FONT SIZE=2>dba_tab_partitions and dba_part_col_statistics. When I calculate the</FONT> <BR><FONT SIZE=2>statistics the only thing that changes is num_distinct in</FONT> <BR><FONT SIZE=2>dba_part_col_statistics for 2 columns. The difference is about 110,000</FONT> <BR><FONT SIZE=2>distinct values which I suspect would be enough to change the values</FONT> <BR><FONT SIZE=2>when using the various different estimate values. Whenever I've looked</FONT> <BR><FONT SIZE=2>closely at this before I've never seen the compute and estimate come up</FONT> <BR><FONT SIZE=2>with the exact same numbers for num rows with large tables but that is</FONT> <BR><FONT SIZE=2>what is happening here.</FONT> </P> <P><FONT SIZE=2>Wouldn't you think that with a difference of 110,000 distinct values in</FONT> <BR><FONT SIZE=2>a 800,000 row partition that estimate 5% and estimate 45% would</FONT> <BR><FONT SIZE=2>generate different values for column stats and even num_rows? The</FONT> <BR><FONT SIZE=2>estimate calculations return results in about 6 seconds and the compute</FONT> <BR><FONT SIZE=2>is taking about 5 minutes. It seems like the estimate is using cached</FONT> <BR><FONT SIZE=2>values no matter what the estimate size is but there are too many</FONT> <BR><FONT SIZE=2>people using the database to shutdown the database and flush the</FONT> <BR><FONT SIZE=2>buffers.</FONT> </P> <P><FONT SIZE=2>Any thoughts?</FONT> </P> <P><FONT SIZE=2>- Brian </FONT> </P> <BR> <BR> <P><FONT SIZE=2>__________________________________________________</FONT> <BR><FONT SIZE=2>Do You Yahoo!?</FONT> <BR><FONT SIZE=2>Get email at your own domain with Yahoo! Mail. </FONT> <BR><FONT SIZE=2>http://personal.mail.yahoo.com/</FONT> <BR><FONT SIZE=2>-- </FONT> <BR><FONT SIZE=2>Please see the official ORACLE-L FAQ: http://www.orafaq.com</FONT> <BR><FONT SIZE=2>-- </FONT> <BR><FONT SIZE=2>Author: Brian Wisniewski</FONT> <BR><FONT SIZE=2> INET: brian_wisniewski_at_yahoo.com</FONT> </P> <P><FONT SIZE=2>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=2>San Diego, California -- Public Internet access / Mailing Lists</FONT> <BR><FONT SIZE=2>--------------------------------------------------------------------</FONT> <BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an E-Mail message</FONT> <BR><FONT SIZE=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT SIZE=2>the message BODY, include a line containing: UNSUB ORACLE-L</FONT> <BR><FONT SIZE=2>(or the name of mailing list you want to be removed from). You may</FONT>
 |
 |