Posts in category Object Statistics

Extension Bypassed Because of Missing Histogram

Today, while tuning a fairly complex query experiencing wrong cardinality estimates, I noticed something I was not aware of. Hence, I thought to write this short post to illustrate how to reproduce the problem I experienced… Create the test table (notice the correlation between the data of the two columns): SQL> CREATE TABLE t 2 […]

Challenges and Chances of the 11g Query Optimizer

Challenges and Chances of the 11g Query Optimizer is the name of a presentation I gave at several events (e.g. Trivadis Performance Days, Oracle OpenWorld, DOAG Konferenz, UKOUG Conference) throughout 2011. Its abstract is the following: With every new release, the query optimizer is enhanced. Oracle Database 11g Release 1 and Release 2 are no […]

Does CREATE INDEX Gather Global Statistics?

You can add the COMPUTE STATISTICS clause to the CREATE INDEX statement. It instructs the SQL statement to gather and store index statistics in the data dictionary, while creating the index. This is useful because the overhead associated with the gathering of statistics while executing this SQL statement is negligible. In Oracle9i, the gathering of […]

Granularity 'APPROX_GLOBAL AND PARTITION'

The patchset 11.1.0.7 introduces in the package DBMS_STATS a new value for the parameter GRANULARITY. The description provided by the development team in $ORACLE_HOME/rdbms/admin/dbmsstat.sql is the following: ‘APPROX_GLOBAL AND PARTITION’ – This option is similar to ‘GLOBAL AND PARTITION’. But the global statistics are aggregated from partition level statistics. It will aggregate all statistics except […]