微课sql优化(2)-为什么需要收集统计信息
1、为什么需要收集统计信息
Cost-based optimizer (CBO)，Based on object statistics，优化器统计信息描述数据库中的对象的详细信息，查询优化器使用这些统计信息为每个SQL语句选择最佳执行计划。数据库将优化器统计信息存储在数据字典中。您可以使用数据字典视图访问这些统计信息。由于数据库中的对象可能会不断更改，因此必须定期更新统计信息，以便准确描述这些对象。Oracle数据库自动维护优化器统计信息。11g：Scheduled Maintenance Window Times，In 11g daily maintenance windows are provided. by default these are defined as :Weeknights: Starts at 10 p.m. and ends at 2 a.m.Weekends: Starts at 6 a.m. is 20 hours long.10g：With Oracle Database 10g the default maintenance window is configured to cover the following periods:10 pm to 6 am every weekdayAll weekend (Friday 10 pm to Monday 6 am)可以使用该DBMS_STATS软件包手动维护优化器统计信息。2、哪些表需要收集统计信息
自动统计任务能满足大部分数据库的业务需求，但存在特殊情况需要手工收集统计信息，1、业务表数据被delete、truncate、impdp/imp大批量数据变理时。2、数据变更量超过10%时。查询哪些表需要收集统计信息col table_name for a30col object_type for a30select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh34:mi') last_analyzedfrom dba_tab_statisticswhere owner = 'HT'and (stale_stats = 'YES' or last_analyzed is null);3、练习题1、测试统计信息阈值： 变更量超过10%
创建测试表：create table ht.test as select * from dba_objects;收集统计信息：exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',tabname => 'TEST',method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 4,cascade => TRUE);删除10%数据：SQL> DELETE FROM ht.TEST WHERE ROWNUM<=(select count(1) from ht.TEST)*0.1;8632 rows deleted.SQL> commit;Commit complete.exec dbms_stats.flush_database_monitoring_info; --刷新变更信息查看数据变更信息set line 200col table_owner for a10col table_name for a30col pname for a20col spname for a20select table_owner,table_name,partition_name pname,subpartition_name spname,inserts,updates,deletes from dba_tab_modificationswhere table_owner='HT';TABLE_OWNE TABLE_NAME PNAME SPNAME INSERTSUPDATES DELETES------------------- --------------------------- -------------- -------------- ------------ ----------------HT TEST 0 0 8632查看统计信息是否过期col table_name for a30col object_type for a30COL last_analyzed for a20select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh34:mi') last_analyzedfrom dba_tab_statisticswhere owner = 'HT' AND TABLE_NAME='TEST';OWNER TABLE_NAME OBJECT_TYPE STALE_STA LAST_ANALYZED---------- ---------------------- ------------------------------ --------- --------------------HT TEST TABLE NO 2017-08-18 21:26再删除一行数据：DELETE FROM ht.TEST WHERE ROWNUM=1;exec dbms_stats.flush_database_monitoring_info; --刷新变更信息再次查看统计信息是否过期col table_name for a30col object_type for a30COL last_analyzed for a20select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh34:mi') last_analyzedfrom dba_tab_statisticswhere owner = 'HT' AND TABLE_NAME='TEST';4、小结
哪些情况需要从新收集统计信息,1、业务表数据被delete、truncate、impdp/imp大批量数据变更时。2、数据变更量超过10%时,统计信息状态标记为过期。
Cost-based optimizer (CBO)，Based on object statistics，优化器统计信息描述数据库中的对象的详细信息，查询优化器使用这些统计信息为每个SQL语句选择最佳执行计划。数据库将优化器统计信息存储在数据字典中。您可以使用数据字典视图访问这些统计信息。由于数据库中的对象可能会不断更改，因此必须定期更新统计信息，以便准确描述这些对象。Oracle数据库自动维护优化器统计信息。11g：Scheduled Maintenance Window Times，In 11g daily maintenance windows are provided. by default these are defined as :Weeknights: Starts at 10 p.m. and ends at 2 a.m.Weekends: Starts at 6 a.m. is 20 hours long.10g：With Oracle Database 10g the default maintenance window is configured to cover the following periods:10 pm to 6 am every weekdayAll weekend (Friday 10 pm to Monday 6 am)可以使用该DBMS_STATS软件包手动维护优化器统计信息。2、哪些表需要收集统计信息
自动统计任务能满足大部分数据库的业务需求，但存在特殊情况需要手工收集统计信息，1、业务表数据被delete、truncate、impdp/imp大批量数据变理时。2、数据变更量超过10%时。查询哪些表需要收集统计信息col table_name for a30col object_type for a30select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh34:mi') last_analyzedfrom dba_tab_statisticswhere owner = 'HT'and (stale_stats = 'YES' or last_analyzed is null);3、练习题1、测试统计信息阈值： 变更量超过10%
创建测试表：create table ht.test as select * from dba_objects;收集统计信息：exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',tabname => 'TEST',method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 4,cascade => TRUE);删除10%数据：SQL> DELETE FROM ht.TEST WHERE ROWNUM<=(select count(1) from ht.TEST)*0.1;8632 rows deleted.SQL> commit;Commit complete.exec dbms_stats.flush_database_monitoring_info; --刷新变更信息查看数据变更信息set line 200col table_owner for a10col table_name for a30col pname for a20col spname for a20select table_owner,table_name,partition_name pname,subpartition_name spname,inserts,updates,deletes from dba_tab_modificationswhere table_owner='HT';TABLE_OWNE TABLE_NAME PNAME SPNAME INSERTSUPDATES DELETES------------------- --------------------------- -------------- -------------- ------------ ----------------HT TEST 0 0 8632查看统计信息是否过期col table_name for a30col object_type for a30COL last_analyzed for a20select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh34:mi') last_analyzedfrom dba_tab_statisticswhere owner = 'HT' AND TABLE_NAME='TEST';OWNER TABLE_NAME OBJECT_TYPE STALE_STA LAST_ANALYZED---------- ---------------------- ------------------------------ --------- --------------------HT TEST TABLE NO 2017-08-18 21:26再删除一行数据：DELETE FROM ht.TEST WHERE ROWNUM=1;exec dbms_stats.flush_database_monitoring_info; --刷新变更信息再次查看统计信息是否过期col table_name for a30col object_type for a30COL last_analyzed for a20select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh34:mi') last_analyzedfrom dba_tab_statisticswhere owner = 'HT' AND TABLE_NAME='TEST';4、小结
哪些情况需要从新收集统计信息,1、业务表数据被delete、truncate、impdp/imp大批量数据变更时。2、数据变更量超过10%时,统计信息状态标记为过期。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
