Create statistics
来源:互联网 发布:单身日剧 知乎 编辑:程序博客网 时间:2024/05/18 18:21
Since Oracle 8i the Cost Based Optimizer (CBO) is the preferred optimizer for Oracle.
In order to make good use of the CBO, you need to create statistics for the data in the database. There are several options to create statistics.
Analyze command
The ANALYZE command is available for all versions of Oracle, however to obtain faster and better statistics use the procedures supplied - in 7.3.4 and 8.0 DBMS_UTILITY.ANALYZE_SCHEMA,
and in 8i and above - DBMS_STATS.GATHER_SCHEMA_STATS 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;
DBMS_UTILITY.ANALYZE_SCHEMA
With DBMS_UTILITY.ANALYZE_SCHEMA you can gather all the statistics for all the tables, clusters and indexes of a schema.
Code examplesexec 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.
DBMS_STATS.GATHER_SCHEMA_STATS
From Oracle 8i the DBMS_STATS package is the preferred method Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. Once again, it follows a similar format to the other methods:
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');
Note: It's also possible to gather statistics for the whole database with the DBMS_STATS.gather_database_stats; command.
Transfering statistics between database.
It can be very handy to use production statistics on your development database, so that you can forecast the optimizer behavior.
You can do this the following way:
1. Create the statistics table.
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SCHEMA_NAME' ,stat_tab => 'STATS_TABLE' , tblspace => 'STATS_TABLESPACE');
Example:
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SYSTEM',stat_tab => 'STATS_TABLE');
2. Export statistics to statistics table
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA' ,'STATS_TABLE',NULL,'SYSTEM');
3. Import statistics into the data dictionary.
exec DBMS_STATS.IMPORT_SCHEMA_STATS('NEW_SCHEMA','STATS_TABLE',NULL,'SYSTEM');
4. Drop the statistics table.
exec DBMS_STATS.DROP_STAT_TABLE('SYSTEM','STATS_TABLE');
- Create statistics
- Robust Statistics
- Error statistics
- Database statistics
- Statistics Descriptions
- statistics io
- MagicBox Statistics
- ProcessMonitor Statistics
- Pending Statistics
- sufficient statistics
- Update Statistics
- Leetcode Statistics
- Statistics 常用
- Statistics Basis
- Pending statistics
- bayes statistics
- Socket Statistics
- Bayesian statistics
- EVC4.0编译错误:LNK2005
- 设计模式9:Proxy Pattern (代理模式)
- RDS技术资料收集
- 能影响你一生的三十句话!
- eclipse 3.4 下配置 Hibernate tool工具
- Create statistics
- 抽象方法与虚方法
- 实现页面控件输入验证,用js脚本实现
- ACE反应器模型的应用——定时器
- Cost Based Optimizer (CBO) and Database Statistics
- COM引用计数器规则精简公理
- 支持炫丽3D效果Clutter接口之 Moblin 2.0
- Intel发布Clutter 1.0 RC1版
- n倍高