Oracle Optimizer: Moving to and working with CBO - Part 4 - Page 2

9.1) DBMS_UTILITY

Oracle provides two procedures under the DBMS_UTILITY package related to statistics generation. (Oracle recommends use of DBMS_STATS package for generating statistics).

DBMS_UTILITY.ANALYZE_SCHEMA

This routine will generate statistics on an individual schema level. It is used for analyzing all tables, clusters and indexes.

It takes the following parameters:

• schema - Name of the schema
• method - Estimation method, COMPUTE or ESTIMATE. DELETE can be used to remove statistics.
• estimate_rows - The number of rows to be considered for estimation.
• estimate_percent - The percentage of rows to be considered for estimation.
• method_opt - Method options. Generate statistics FOR TABLE, FOR ALL COLUMNS, FOR ALL INDEXED COLUMNS, FOR ALL INDEXES.

If the ESTIMATE method is used, then either estimate_rows or estimate_percent should be specified; these actually specify the sample size to be considered.

Call syntax

dbms_utility.analyze_schema(schema, method, estimate_rows, estimate_percent,  method_opt)

e.g.: Computing statistics for a schema

SQL> exec dbms_utility.analyze_schema('SYSTEM', 'COMPUTE');
PL/SQL procedure successfully completed.

e.g.: Estimating statistics for a schema, sample size is 1024 row.

SQL> exec dbms_utility.analyze_schema('FEM', 'ESTIMATE', estimate_rows => 1024);
PL/SQL procedure successfully completed.

e.g.: Estimating statistics for FA schema, sample size is 10 percent of rows.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 10);
PL/SQL procedure successfully completed.

e.g.: Deleting statistics for FA schema

SQL> exec dbms_utility.analyze_schema('FA', 'DELETE');
PL/SQL procedure successfully completed.

e.g.: Estimating statistics with 5 percent rows for all indexes in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 
	method_opt => 'FOR ALL INDEXES');
PL/SQL procedure successfully completed.

e.g.: Estimating statistics with 5 percent rows for columns with indexes in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 
	method_opt => 'FOR ALL INDEXED COLUMNS');
PL/SQL procedure successfully completed.

e.g.: Estimating statistics with 5 percent rows for all columns in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 
	method_opt => 'FOR ALL COLUMNS');
PL/SQL procedure successfully completed.

e.g.: Estimating statistics for all tables in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 
	method_opt => 'FOR TABLE');
PL/SQL procedure successfully completed.

e.g.: Proper sample size should be given, otherwise ORA-01493 is encountered.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => -5);
BEGIN dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => -5); END;

*
ERROR at line 1:
ORA-01493: invalid SAMPLE size specified
ORA-06512: at "SYS.DBMS_DDL", line 179
ORA-06512: at "SYS.DBMS_UTILITY", line 331
ORA-06512: at line 1

DBMS_UTILITY.ANALYZE_DATABASE

DBMS_UTILITY.ANALYZE_DATABASE is used for analyzing all tables, clusters and indexes at database level. It takes the same set of parameters as above except for the schema name.

e.g.: Estimating statistics for database with 30 percent sample.

SQL> exec dbms_utility.analyze_database('ESTIMATE', estimate_percent => 30);
PL/SQL procedure successfully completed.

9.2) ANALYZE command

The ANALYZE command can also be used to collect statistics for individual objects. The Object to be analyzed should belong to the local schema or the user should have ANALYZE ANY TABLE system privilege. This command can be used for the following purpose:

1. Collect statistics for individual objects.
2. Validate the structure of an object.
3. To list migrated or chained rows.
4. Validate REF links.
5. Collect statistics not used by the optimizer.

Oracle recommends the use of the DBMS_STATS package for collecting statistics. The ANALYZE command can be used for the other 4 points mentioned above. Statistics are not collected for columns of type- REFs, varrays, nested tables, LOBs , LONG or object types.

If no sample size is provided when estimating statistics with the ANALYZE command, Oracle will take a default sample size of the first 1064 rows. This may not be effective and most often will result in bad queries.

If the ESTIMATE sample size is greater than 50%, it is as good as the COMPUTE option.

Columns such as EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_SPACE_FREELIST_BLOCKS and NUM_FREELIST_BLOCKS are not populated using the DBMS_STATS package. These are populated by using the ANALYZE command and could be used for maintenance and administration activities.

The below examples are given for statistics generation only.

e.g.: Gathering statistics for a table. This will also compute for individual columns and related indexes.

orAP>analyze table am_statchk compute statistics;
Table analyzed.

e.g.: Deleting statistics for a table. This will delete statistics related to table, columns and related indexes.

orAP> analyze table am_statchk delete statistics;
Table analyzed.

e.g.: Estimating statistics for a table with 20 percent rows.

orAP>analyze table am_statchk estimate statistics sample 20 percent;
Table analyzed.

e.g.: Estimating statistics for a table with 1000 sample rows.

orAP>analyze table am_statchk estimate statistics sample 1000 rows;
Table analyzed.

e.g.: Gathering statistics for indexed columns of a table.

orAP>analyze table am_statchk compute statistics for all indexed columns;
Table analyzed.

e.g.: Computing statistics for all columns in a table.

orAP>analyze table am_statchk compute statistics for all columns;
Table analyzed.

e.g.: Computing statistics for individual indexes.

orAP>analyze index am_statchk_n1 compute statistics;
Index analyzed.

e.g.: Deleting statistics for an index.

orAP>analyze index am_statchk_n1 delete statistics;
Index analyzed.

e.g.: Computing statistics for all indexes and all indexed columns.

orAP>analyze table am21 estimate statistics sample 5 percent for all indexes for all indexed columns;
Table analyzed.

9.3) DBMS_DDL

DBMS_DDL has a routine ANALYZE_OBJECT meant for generating statistics for individual objects (Oracle recommends use of DBMS_STATS package for generating statistics).

Parameters

• type - TABLE, INDEX or CLUSTER.
• schema - schema name.
• name - name of the object.
• method - ESTIMATE, COMPUTE or DELETE.
• estimate_rows - sampling no. of rows.
• estimate_percent - sampling percentage of rows.
• method_opt - method options FOR ALL TABLES, FOR ALL COLUMNS, FOR ALL INDEXED COLUMNS, FOR ALL INDEXES.
• partname - partition, if present, to be analyzed.

e.g.: Below call will generate statistics for table AM21 and its columns and indexes.

orAP>exec dbms_ddl.analyze_object(type => 'TABLE', schema => 'APPS', name => 'AM21', -
> method => 'ESTIMATE', estimate_percent => 5);
PL/SQL procedure successfully completed.

e.g.: Deleting all statistics related to a table

orAP>exec dbms_ddl.analyze_object(type => 'TABLE', schema => 'APPS', name => 'AM21', -
> method => 'DELETE');
PL/SQL procedure successfully completed.