oracle统计信息查看与收集的方法是什么
这篇文章主要讲解了“oracle统计信息查看与收集的方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“oracle统计信息查看与收集的方法是什么”吧！
一、查看表统计信息
SQL>altersessionsetNLS_DATE_FORMAT='YYYY-MM-DDHH24:MI:SS';SQL>selectt.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZEDfromuser_tablestwheretable_name='WOO';
备注：通过脚本查看统计信息,参考MOS：SCRIPT - Select to show Optimizer Statistics for CBO (Doc ID 31412.1)
二、查看表上的索引信息
selecttable_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzedfromuser_indexestwheretable_name='DUMP_TABLE';SQL>coltable_nameformata11SQL>colindex_nameformata16SQL>selecttable_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed2fromuser_indexest3wheretable_name='DUMP_TABLE';TABLE_NAMEINDEX_NAMEBLEVELNUM_ROWSLEAF_BLOCKSLAST_ANAL-------------------------------------------------------------------DUMP_TABLEWORNUM_IND010118-MAR-20
三、检查当前统计信息收集策略
setlinesize140colWINDOW_NAMEformata17colREPEAT_INTERVALfora55colDURATIONfora15selectt1.window_name,t1.repeat_interval,t1.durationfromdba_scheduler_windowst1,dba_scheduler_wingroup_memberst2wheret1.window_name=t2.window_nameandt2.window_group_namein('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');SQL>setlinesize140SQL>colWINDOW_NAMEformata17SQL>colREPEAT_INTERVALfora55SQL>colDURATIONfora15SQL>selectt1.window_name,t1.repeat_interval,t1.durationfrom2dba_scheduler_windowst1,dba_scheduler_wingroup_memberst23wheret1.window_name=t2.window_name4andt2.window_group_name5in('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');WINDOW_NAMEREPEAT_INTERVALDURATION---------------------------------------------------------------------------------------MONDAY_WINDOWfreq=daily;byday=MON;byhour=22;byminute=0;bysecond=0+00004:00:00TUESDAY_WINDOWfreq=daily;byday=TUE;byhour=22;byminute=0;bysecond=0+00004:00:00WEDNESDAY_WINDOWfreq=daily;byday=WED;byhour=22;byminute=0;bysecond=0+00004:00:00THURSDAY_WINDOWfreq=daily;byday=THU;byhour=22;byminute=0;bysecond=0+00004:00:00FRIDAY_WINDOWfreq=daily;byday=FRI;byhour=22;byminute=0;bysecond=0+00004:00:00SATURDAY_WINDOWfreq=daily;byday=SAT;byhour=6;byminute=0;bysecond=0+00020:00:00SUNDAY_WINDOWfreq=daily;byday=SUN;byhour=6;byminute=0;bysecond=0+00020:00:007rowsselected.
3.1. 关闭自动统计信息收集
BEGINDBMS_SCHEDULER.DISABLE(name=>'"SYS"."SATURDAY_WINDOW"',force=>TRUE);END;/
3.2 修改自动统计信息持续时间
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SATURDAY_WINDOW"',attribute=>'DURATION',value=>numtodsinterval(180,'minute'));END;/
3.3 修改自动统计信息开始时间，每周六22点开始
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."SATURDAY_WINDOW"',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0');END;/
3.4 开启自动统计信息收集
BEGINDBMS_SCHEDULER.ENABLE(name=>'"SYS"."SATURDAY_WINDOW"');END;/
3.5 再次检查策略是否正确
setlinesize200colREPEAT_INTERVALfora60colDURATIONfora30selectt1.window_name,t1.repeat_interval,t1.durationfromdba_scheduler_windowst1,dba_scheduler_wingroup_memberst2wheret1.window_name=t2.window_nameandt2.window_group_namein('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');WINDOW_NAMEREPEAT_INTERVALDURATION------------------------------------------------------------------------------------------------------------------------MONDAY_WINDOWfreq=daily;byday=MON;byhour=22;byminute=0;bysecond=0+00004:00:00TUESDAY_WINDOWfreq=daily;byday=TUE;byhour=22;byminute=0;bysecond=0+00004:00:00WEDNESDAY_WINDOWfreq=daily;byday=WED;byhour=22;byminute=0;bysecond=0+00004:00:00THURSDAY_WINDOWfreq=daily;byday=THU;byhour=22;byminute=0;bysecond=0+00004:00:00FRIDAY_WINDOWfreq=daily;byday=FRI;byhour=22;byminute=0;bysecond=0+00004:00:00SATURDAY_WINDOWfreq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0+00020:00:00SUNDAY_WINDOWfreq=daily;byday=SUN;byhour=6;byminute=0;bysecond=0+00020:00:007rowsselected.
四、手工收集统计信息
4.1 收集索引统计信息
execdbms_stats.gather_index_stats(ownname=>'WOO',indname=>'WORNUM_IND',estimate_percent=>'10',degree=>'4');
4.2 收集表和索引统计信息
execdbms_stats.gather_table_stats(ownname=>'WOO',tabname=>'DUMP_TABLE',estimate_percent=>10,method_opt=>'forallindexedcolumns',cascade=>TRUE);
4.3 收集表的统计信息
execdbms_stats.gather_table_stats(ownname=>'WOO',tabname=>'DUMP_TABLE',estimate_percent=>10,method_opt=>'forallindexedcolumns');
4.4 收集分区表统计信息
execdbms_stats.gather_table_stats(ownname=>'WOO',tabname=>'DUMP_TABLE',partname=>'p_20190318',estimate_percent=>10,method_opt=>'forallindexedcolumns',cascade=>TRUE);
4.5 收集某个用户的统计信息
execdbms_stats.gather_schema_stats(ownname=>'WOO',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');
4.6 收集整个数据库的统计信息
execdbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');
五、动态采样：
对于新创建的表，当访问此表时，oracle会动态的收集这个表的相关信息，等到晚上10点，再将其收集到数据字典中。
SQL>setlinesize200SQL>setautotracetraceonlySQL>select*fromDUMP_TABLE;10rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:1795212136--------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------|0|SELECTSTATEMENT||10|20390|3(0)|00:00:01||1|TABLEACCESSFULL|DUMP_TABLE|10|20390|3(0)|00:00:01|--------------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------4recursivecalls0dbblockgets9consistentgets0physicalreads0redosize1305bytessentviaSQL*Nettoclient523bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed
- dynamic sampling used for this statement (level=2) 表示动态采样，但是不记录数据字典，除非手动收集表的统计信息。
咱们通过user_tables查看结果也是一样的
SQL>setautotraceoff;SQL>selectnum_rows,blocks,last_analyzedfromuser_tableswheretable_name='DUMP_TABLE';NUM_ROWSBLOCKSLAST_ANAL-----------------------------SQL>
六、统计信息收集完之后：
SQL>setlinesize200SQL>setautotracetraceonlySQL>select*fromDUMP_TABLE;10rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:1795212136--------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------|0|SELECTSTATEMENT||10|470|3(0)|00:00:01||1|TABLEACCESSFULL|DUMP_TABLE|10|470|3(0)|00:00:01|--------------------------------------------------------------------------------Statistics----------------------------------------------------------33recursivecalls0dbblockgets56consistentgets0physicalreads0redosize1305bytessentviaSQL*Nettoclient523bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient6sorts(memory)0sorts(disk)10rowsprocessedSQL>setautotraceoff;SQL>altersessionsetNLS_DATE_FORMAT='YYYY-MM-DDHH24:MI:SS';SQL>selectnum_rows,blocks,last_analyzedfromuser_tableswheretable_name='DUMP_TABLE';NUM_ROWSBLOCKSLAST_ANALYZED---------------------------------------1042020-03-1817:06:16SQL>
感谢各位的阅读，以上就是“oracle统计信息查看与收集的方法是什么”的内容了，经过本文的学习后，相信大家对oracle统计信息查看与收集的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
