在此示例中，您将启用运行DML语句，然后启用高频统计信息收集作业。
本示例假定以下内容：
- 您以管理员身份登录数据库。
sh模式的统计信息是最新的。- 高频自动优化器统计信息收集
未启用。
- 在数据字典中查询
sales和customers表的统计信息（包括示例输出）：SET LINESIZE 170 SET PAGESIZE 5000 COL TABLE_NAME FORMAT a20 COL PARTITION_NAME FORMAT a20 COL NUM_ROWS FORMAT 9999999 COL STALE_STATS FORMAT a3 SELECT TABLE_NAME, PARTITION_NAME, NUM_ROWS, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SH' AND TABLE_NAME IN ('CUSTOMERS','SALES') ORDER BY TABLE_NAME, PARTITION_NAME; TABLE_NAME PARTITION_NAME NUM_ROWS STA -------------------- -------------------- -------- --- CUSTOMERS 55500 NO SALES SALES_1995 0 NO SALES SALES_1996 0 NO SALES SALES_H1_1997 0 NO SALES SALES_H2_1997 0 NO SALES SALES_Q1_1998 43687 NO SALES SALES_Q1_1999 64186 NO SALES SALES_Q1_2000 62197 NO SALES SALES_Q1_2001 60608 NO SALES SALES_Q1_2002 0 NO SALES SALES_Q1_2003 0 NO SALES SALES_Q2_1998 35758 NO SALES SALES_Q2_1999 54233 NO SALES SALES_Q2_2000 55515 NO SALES SALES_Q2_2001 63292 NO SALES SALES_Q2_2002 0 NO SALES SALES_Q2_2003 0 NO SALES SALES_Q3_1998 50515 NO SALES SALES_Q3_1999 67138 NO SALES SALES_Q3_2000 58950 NO SALES SALES_Q3_2001 65769 NO SALES SALES_Q3_2002 0 NO SALES SALES_Q3_2003 0 NO SALES SALES_Q4_1998 48874 NO SALES SALES_Q4_1999 62388 NO SALES SALES_Q4_2000 55984 NO SALES SALES_Q4_2001 69749 NO SALES SALES_Q4_2002 0 NO SALES SALES_Q4_2003 0 NO SALES 918843 NO前面的输出显示所有统计信息都不是过时的。
- 在
sales和执行DMLcustomers：-- insert 918K rows in sales INSERT INTO sh.sales SELECT * FROM sh.sales; -- update around 15% of sales rows UPDATE sh.sales SET amount_sold = amount_sold + 1 WHERE amount_sold > 100; -- insert 1 row into customers INSERT INTO sh.customers(cust_id, cust_first_name, cust_last_name, cust_gender, cust_year_of_birth, cust_main_phone_number, cust_street_address, cust_postal_code, cust_city_id, cust_city, cust_state_province_id, cust_state_province, country_id, cust_total, cust_total_id) VALUES(188710, 'Jenny', 'Smith', 'F', '1966', '555-111-2222', '400 oracle parkway','94065',51402, 'Redwood Shores', 52564, 'CA', 52790, 'Customer total', '52772'); COMMIT;总行数
sales增加了100％，但仅将1行添加到customers。 - 将优化器统计信息保存到磁盘：
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; - 再次查询表统计信息（包括示例输出）：
SELECT TABLE_NAME, PARTITION_NAME, NUM_ROWS, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SH' AND TABLE_NAME IN ('CUSTOMERS','SALES') ORDER BY TABLE_NAME, PARTITION_NAME; TABLE_NAME PARTITION_NAME NUM_ROWS STA -------------------- -------------------- -------- --- CUSTOMERS 55500 NO SALES SALES_1995 0 NO SALES SALES_1996 0 NO SALES SALES_H1_1997 0 NO SALES SALES_H2_1997 0 NO SALES SALES_Q1_1998 43687 YES SALES SALES_Q1_1999 64186 YES SALES SALES_Q1_2000 62197 YES SALES SALES_Q1_2001 60608 YES SALES SALES_Q1_2002 0 NO SALES SALES_Q1_2003 0 NO SALES SALES_Q2_1998 35758 YES SALES SALES_Q2_1999 54233 YES SALES SALES_Q2_2000 55515 YES SALES SALES_Q2_2001 63292 YES SALES SALES_Q2_2002 0 NO SALES SALES_Q2_2003 0 NO SALES SALES_Q3_1998 50515 YES SALES SALES_Q3_1999 67138 YES SALES SALES_Q3_2000 58950 YES SALES SALES_Q3_2001 65769 YES SALES SALES_Q3_2002 0 NO SALES SALES_Q3_2003 0 NO SALES SALES_Q4_1998 48874 YES SALES SALES_Q4_1999 62388 YES SALES SALES_Q4_2000 55984 YES SALES SALES_Q4_2001 69749 YES SALES SALES_Q4_2002 0 NO SALES SALES_Q4_2003 0 NO SALES 1837686 SALES 918843 YES 31 rows selected.前面的输出显示统计信息customers不是过时的，而sales是过时的。
- 配置高频自动优化器统计信息收集：
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','180'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240');前面的PL / SQL程序启用高频收集，将最大运行时间设置为3分钟，并将任务执行间隔设置为4分钟。
- 等待几分钟，然后查询数据字典：
COL OPID FORMAT 9999 COL STATUS FORMAT a11 COL ORIGIN FORMAT a20 COL COMPLETED FORMAT 99999 COL FAILED FORMAT 99999 COL TIMEOUT FORMAT 99999 COL INPROG FORMAT 99999 SELECT OPID, ORIGIN, STATUS, TO_CHAR(START_TIME, 'DD/MM HH24:MI:SS' ) AS BEGIN_TIME, TO_CHAR(END_TIME, 'DD/MM HH24:MI:SS') AS END_TIME, COMPLETED, FAILED, TIMED_OUT AS TIMEOUT, IN_PROGRESS AS INPROG FROM DBA_AUTO_STAT_EXECUTIONS ORDER BY OPID;输出显示高频作业执行了两次，而标准自动统计信息收集作业执行了一次：
OPID ORIGIN STATUS BEGIN_TIME END_TIME COMPLETED FAILED TIMEOUT INPROG ----- -------------------- ----------- -------------- -------------- --------- ------ ------- ------ 790 HIGH_FREQ_AUTO_TASK COMPLETED 03/10 14:54:02 03/10 14:54:35 338 3 0 0 793 HIGH_FREQ_AUTO_TASK COMPLETED 03/10 14:58:11 03/10 14:58:45 193 3 0 0 794 AUTO_TASK COMPLETED 03/10 15:00:02 03/10 15:00:20 52 3 0 0
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
