?
??? Histograms:描述列的分布信息.
??????? 1.如果数据分布不均匀.
???? 2.收集方法:
?????? dbms_stats.gather_table_stats.
???? 3.查看信息
?????? dbms_histograms,dba_tab_histograms
??????
?????? select * from jobs where job_status ='COMPLETE';
??????
??????
?????? execute dbms_stats.gather_table_stats('HR','EMPLOYEES',
???????? METHOD_OPT=>'FOR COLUMNS SIZE 10 SALARY');
?????? 备注:oracle推荐设置METHOD_OPT为FOR ALL COLUMNS SIZE AUTO,使用这个值
?????? oracle可以自动决定哪列需要统计和计算block数.
?????? select endpoint_number,endpoint_value
??????????????? from dba_histograms
???????where owner='HR'
???????? and table_name='T' and column_name='ID';
?????收集统计的信息
????? dbms_stats.auto_sample_size:
????? METHOD_OPT:
???????? REPEAT:
??????AUTO:
??????SKEWONLY:
??????execute dbms_stats.gather_schema_stats(
???????ownname=>'OE',
???????estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
???????method_opt=>'for all columns size AUTO');
??????
?????自动统计收集
??????dbms_stats.gather_schema_stats
??????存储过程的options可选值
??????? Gather Stale
??????? Gather Empty
??????? Gatcher Auto
??????? execute dbms_stats.gather_schema_stats(
???????? ownname=>'OE',
????????? options=>'GATHER AUTO'
??????? );
?????优化器Cost模型
???????? 在plan_table中三列:
???????cpu_cost:
???????io_cost:
???????temp_space:
??????包括CPU的使用情况
??????缓存的影响.
??????预先索引的影响.
?????系统统计信息
???????? 1.系统统计启用CBO相关的IO和CPU.
??????2.系统统计必须被收集.
?????????????? 3.收集系统统计等效于一定时间内对系统的分析.
?????收集存储函数:
??????? dbms_stats
???????? gather_system_stats
??????set_system_stats
??????get_system_stats
????? 自动收集:
??????? OLTP:
????????? execute dbms_stats.gather_system_stats(
???????? interval=>120,
???????? stattab=>'table_name',
???????? statid='OLTP');
??????OLAP:
?????? execute dbms_stats.gather_system_stats(
???????? interval=>120,
???????? stattab=>'table_name',
???????? statid='OLAP');
?????手动收集
???????? 1.手动启动收集存储到数据字典
??????? execute dbms_stats.gather_system_stats(gathering_mode=>'START');
??????2.生成工作负荷
??????3.接收系统统计收集
??????? execute dbms_stats.gather_system_stats(gathering_mode=>'STOP');
???????
?????导入系统统计信息
??????? OLTP:
????????? execute dbms_stats.import_system_stats(
????????? stattab=>'Table_name',
???????statid=>'OLTP');
??????? OLAP:
??? ???execute dbms_stats.import_system_stats(
????????? stattab=>'Table_name',
???????statid=>'OLAP');??
????????????? 1. 创建表的统计信息
??????dbms_stats.create_stat_table(
?????? schema_name=>'SH',
?????? statistics_table_name=>'STATISTICS_TABLE_NAME',
?????? tablespace_name='TBS_DATA'
??????);
??????2.将统计信息拷贝到表中
??????dbms_stats.export_table_stats
??????(
?????? 'SH',?? --schema name
?????? 'SALES',? --table name
?????? NULL, ---no partitions
?????? 'STATISTICS_TABLE_NAME', ---statistics table name
?????? NULL, ---id for? statistics
?????? true?? --index statistics
??????);
??????SALES的统计信息为STATISTICS_TABLE_NAME
??????3.导出统计信息到另外一个数据库导入统计信息.
?????? 通过imp或者exp导入导出,或者databaselink
??????4.将统计信息拷贝到数据字典
??????dbms_stats.import_table_stats
??????(
?????? 'SH',?? --schema name
?????? 'SALES',? --table name
?????? NULL, ---no partitions
?????? 'STATISTICS_TABLE_NAME', ---statistics table name
?????? NULL, ---id for? statistics
?????? true?? --index statistics
??????);