This is a complete oracle performance diagnosis case in solving bad performance of oracle database.

It covers:

1. How to use statspack report to narrow down the cause of problem?
2. Typical steps to figure out similar problem?
3. And, further discussion on relating internal machenism, histograms,
   CBO Optimizer, hidden init parameters, dbms_stats, and so on.

See attached for PDF copy.

See what Tom said about "difference between soft parse and hard parse". 

 for complete article.

You Asked
How do you actually create statistics without creating histograms? I know you can supply the auto or the actual number of buckets using the command in dbms_stats.

Is a bucket of 1 the same as no histograms? There is a disagreement (with me and another DBA) on the way to do this.

thanks.



and we said...

[url=http://www.oraclepoint.com]JRYS3AG2LOraclePointPj9Id2wHW[/url]

ops$tkyte%ORA10GR2> create table t as select * from all_users;

Table created.

ops$tkyte%ORA10GR2> column column_name format a15
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select column_name, count(*) from user_tab_histograms where
table_name = 'T' group by column_name;

COLUMN_NAME       COUNT(*)
--------------- ----------
USER_ID                  2
CREATED                  2
USERNAME                 2

ops$tkyte%ORA10GR2> select * from t where username = 'x';

no rows selected

ops$tkyte%ORA10GR2> select * from t where user_id = -1;

no rows selected

ops$tkyte%ORA10GR2> select * from t where created = sysdate;

no rows selected

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select column_name, count(*) from user_tab_histograms where
table_name = 'T' group by column_name;

COLUMN_NAME       COUNT(*)
--------------- ----------
USER_ID                  2
CREATED                 30
USERNAME                 2





Ok, so by default in 10g, Oracle uses size auto to gather column stats - that is, it looks at predicates you've used and the data in the columns and figures out what histograms to gather

If you don't want that, you can just get endpoints (one bucket - high and low values and number of values - this is useful stuff generally) by doing this:

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all
columns size 1' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select column_name, count(*) from user_tab_histograms where
table_name = 'T' group by column_name;

COLUMN_NAME       COUNT(*)
--------------- ----------
USER_ID                  2
CREATED                  2
USERNAME                 2



Now, if you really really didn't want ANY column information, you could do this:

ops$tkyte%ORA10GR2> exec dbms_stats.delete_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for
columns ' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select column_name, count(*) from user_tab_histograms where
table_name = 'T' group by column_name;

no rows selected

Note: The above entry was published at AskTom.

The case study posting http://www.oraclepoint.com/topic.php?filename=312&extra=page%3D1 is also another good case for your information while you experience query performance problem.

This is a Tom's blog posting about "Bind Variable Peeking" at http://tkyte.blogspot.com/2007/09/tuning-with-sqltracetrue.html. Well explained.