Shulou(Shulou.com)06/01 Report--

1. Why do you need to collect statistics Cost-based optimizer (CBO), Based on object statistics, optimizer statistics to describe the details of the objects in the database, and the query optimizer uses these statistics to select the best execution plan for each SQL statement. The database stores optimizer statistics in a data dictionary. You can access these statistics using the data dictionary view. Because objects in the database may change constantly, statistics must be updated periodically to accurately describe these objects. The Oracle database automatically maintains optimizer statistics. 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 weekday All weekend (Friday 10 pm to Monday 6 am) you can use this DBMS_STATS package to manually maintain optimizer statistics. 2. Which tables need to collect statistics automatic statistical tasks can meet the business needs of most databases, but there are special circumstances that require manual collection of statistical information. 1. When the business table data is modified by delete, truncate, impdp/imp mass data. 2. When the amount of data change exceeds 10%. Query which tables need to collect statistics col table_name for A30 col object_type for A30 select owner, table_name, object_type, stale_stats, to_char (LAST_ANALYZED,'yyyy-mm-dd hh34:mi') last_analyzed from dba_tab_statistics where owner = 'HT' and (stale_stats =' YES' or last_analyzed is null) 3. Exercise 1. Threshold of test statistics: create a test table with more than 10% changes: create table ht.test as select * from dba_objects; collect statistics: exec DBMS_STATS.GATHER_TABLE_STATS (ownname = > 'HT',tabname = >' TEST',method_opt = > 'for all columns size repeat',no_invalidate = > FALSE,degree = > 4 cascade = > TRUE); delete 10% data: SQL > DELETE FROM ht.TEST WHERE ROWNUM commit; Commit complete. Exec dbms_stats.flush_database_monitoring_info;-refresh change information to view data change information set line 200 col table_owner for a10 col table_name for A30 col pname for A20 col spname for A20 select table_owner,table_name,partition_name pname,subpartition_name spname,inserts,updates,deletes from dba_tab_modifications where table_owner='HT' TABLE_OWNE TABLE_NAME PNAME SPNAME INSERTS UPDATES DELETES- -HT TEST 0 8632 check whether the statistics are out of date col table_name for A30 col object_type for A30 COL last_analyzed for A20 select owner Table_name, object_type, stale_stats, to_char (LAST_ANALYZED,'yyyy-mm-dd hh34:mi') last_analyzed from dba_tab_statistics where 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 another row of data: DELETE FROM ht.TEST WHERE ROWNUM=1 Exec dbms_stats.flush_database_monitoring_info;-refresh the change information again to check whether the statistics are out of date col table_name for A30 col object_type for A30 COL last_analyzed for A20 select owner, table_name, object_type, stale_stats, to_char (LAST_ANALYZED,'yyyy-mm-dd hh34:mi') last_analyzed from dba_tab_statistics where owner = 'HT' AND TABLE_NAME='TEST' 4. Summarize what situations require new collection of statistical information: 1. When the business table data is changed by delete, truncate, impdp/imp mass data. 2. When the amount of data changes exceeds 10%, the statistical information status is marked as expired.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.