- DBMS_UTILITY.ANALYZE_SCHEMA
DBMS_UTILITY.ANALYZE_SCHEMA is
used to gather statistics for all the tables, clusters and indexes of a schema.
Examples:
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');
Note: It's also possible to analyze the whole database with the DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE'); command.
Examples:
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');
Note: It's also possible to analyze the whole database with the DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE'); command.
- Oracle: Analyze Table or Index
Analyze command
The ANALYZE command is to obtain faster and better statistics use the procedures supplied.
The analyze table can be used to create statistics for 1 table, index or cluster.
Syntax:
ANALYZE table tableName {compute|estimate|delete) statistics options
ANALYZE table indexName {compute|estimate|delete) statistics options
ANALYZE cluster clusterName {compute|estimate|delete) statistics options
Code examples
The ANALYZE command is to obtain faster and better statistics use the procedures supplied.
The analyze table can be used to create statistics for 1 table, index or cluster.
Syntax:
ANALYZE table tableName {compute|estimate|delete) statistics options
ANALYZE table indexName {compute|estimate|delete) statistics options
ANALYZE cluster clusterName {compute|estimate|delete) statistics options
Code examples
ANALYZE table scott compute statistics;
ANALYZE table scott estimate statistics sample 25 percent;
ANALYZE table scott estimate statistics sample 1000 rows;
analyze index sc_idx compute statistics;
analyze index sc_idx validate structure;
- Oracle: DBMS_STATS Gather Statistics of Schema, Tables, Indexes
DBMS_STATS package,
which lets you collect statistics in parallel, collect global statistics for partitioned
objects, and fine tune your statistics collection in other ways. This package
is concerned with optimizer statistics only.
dbms_stats is essential to good SQL performance, and it should always be used before adjusting any of the Oracle optimizer initialization parameters.
Syntax:
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample , method_opt,degree,granularity,cascade,stattab, statid,options,statown ,no_invalidate, gather_temp,gather_fixed);
Code examples:
exec dbms_stats.gather_schema_stats('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);
exec dbms_stats.gather_schema_stats(ownname=>'SCOTT', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
exec dbms_stats.gather_schema_stats(ownname => 'SCOTT', estimate_percent => 25);
exec dbms_stats.gather_table_stats('SCOTT', 'EMPLOYEES');
exec dbms_stats.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
exec dbms_stats.delete_schema_stats('SCOTT');
No comments:
Post a Comment