19c-高频自动优化器统计收集_冰山一角

http://blog.sina.com.cn/u/1294121330

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

19c-高频自动优化器统计收集

(2019-08-01 15:11:10)

3. High-Frequency Automatic Optimizer Statistics Collection

高频自动优化器统计收集

您可以配置轻量级，高频率的自动任务，定期收集过时对象的优化程序统计信息。在执行DBMS_STATS统计信息收集作业之间，统计信息可能会过时。通过更频繁地收集统计信息，优化程序可以生成更优化的计划。

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/gathering-optimizer-statistics.html#GUID-374ACB86-2512-4658-9170-8505B3EACF7D

原先每天晚上10点数据库统计信息收集不再存放在select * from dba_scheduler_jobs，而是：

SELECTCLIENT_NAME, STATUS

FROM DBA_AUTOTASK_CLIENT

WHERE CLIENT_NAME = 'auto optimizer stats collection';

select * FROM dba_Autotask_Operation;

select * FROM dba_Autotask_Client_Job;--0

select * FROM dba_Autotask_Job_History where client_name='auto optimizer stats collection' order by window_start_time desc;

select * from dba_autotask_status;

select * from dba_autotask_task;

select * from dba_autotask_schedule;

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE (

client_name => 'auto optimizer stats collection'

, operation => NULL

, window_name => NULL

);

END;

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE (

client_name => 'auto optimizer stats collection'

, operation => NULL

, window_name => NULL

);

END;

How High-Frequency Automatic Optimizer Statistics Collection Works

To enable and disable the high-frequency task, set the execution interval, and set the maximum run time, use the DBMS_STATS.SET_GLOBAL_PREFS procedure. The high-frequency task is “lightweight” and only gathers stale statistics. It does not perform actions such as purging statistics for non-existent objects or invoking Optimizer Statistics Advisor. The standard automated job performs these additional tasks.

Automatic statistics collection jobs that run in the maintenance window are not affected by the high-frequency jobs. The high-frequency tasks do not start during the maintenance window, so only one statistics task runs at a time. You can monitor the tasks by querying DBA_AUTO_STAT_EXECUTIONS.

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

WHERE ORIGIN='HIGH_FREQ_AUTO_TASK'

ORDER BY OPID;

13.2.2 Setting Preferences for High-Frequency Automatic Optimizer Statistics Collection

To enable and disable the task, use DBMS_STATS.SET_GLOBAL_PREFS.

You can use DBMS_STATS.SET_GLOBAL_PREFS to set preferences to any of the following values:

AUTO_TASK_STATUS

Enables or disables the high-frequency automatic optimizer statistics collection. Values are:

- ON — Enables high-frequency automatic optimizer statistics collection.
- OFF — Disables high-frequency automatic optimizer statistics collection. This is the default.
AUTO_TASK_MAX_RUN_TIME

Configures the maximum run time in seconds of an execution of high-frequency automatic optimizer statistics collection. The maximum value is 3600 (equal to 1 hour), which is the default.

AUTO_TASK_INTERVAL

Specifies the interval in seconds between executions of high-frequency automatic optimizer statistics collection. The minimum value is 60. The default is 900 (equal to 15 minutes).

To configure the high-frequency task:

Log in to the database as a user with administrator privileges.
To enable the high-frequency task, set the AUTO_TASK_STATUS preference to ON.

The following example enables the automatic task:

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');

To set the maximum run time, set the AUTO_TASK_MAX_RUN_TIME preference to the desired number of seconds.

The following example sets the maximum run time to 10 minutes:

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','600');

To set the frequency, set the AUTO_TASK_INTERVAL preference to the desired number of seconds.

The following example sets the frequency to 8 minutes:

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240');

13.2.3 High-Frequency Automatic Optimizer Statistics Collection: Example

In this example, you enable run DML statements, and then enable the high-frequency statistics collection job.

This example assumes the following:

You are logged in to the database as an administrator.
The statistics for the sh schema are fresh.
High-frequency automatic optimizer statistics collection

is not enabled.

Query the data dictionary for the statistics for the sales and customers tables (sample output included):

2. SET LINESIZE 170

3. SET PAGESIZE 5000

4. COL TABLE_NAME FORMAT a20

5. COL PARTITION_NAME FORMAT a20

6. COL NUM_ROWS FORMAT 9999999

7. COL STALE_STATS FORMAT a3

9. SELECT TABLE_NAME, PARTITION_NAME, NUM_ROWS, STALE_STATS

10. FROM DBA_TAB_STATISTICS

11. WHERE OWNER = 'SH'

12. AND TABLE_NAME IN ('CUSTOMERS','SALES')

13. ORDER BY TABLE_NAME, PARTITION_NAME;

14.

15. TABLE_NAME PARTITION_NAME NUM_ROWS STA

16. -------------------- -------------------- -------- ---

17. CUSTOMERS 55500 NO

18. SALES SALES_1995 0 NO

19. SALES SALES_1996 0 NO

20. SALES SALES_H1_1997 0 NO

21. SALES SALES_H2_1997 0 NO

22. SALES SALES_Q1_1998 43687 NO

23. SALES SALES_Q1_1999 64186 NO

24. SALES SALES_Q1_2000 62197 NO

25. SALES SALES_Q1_2001 60608 NO

26. SALES SALES_Q1_2002 0 NO

27. SALES SALES_Q1_2003 0 NO

28. SALES SALES_Q2_1998 35758 NO

29. SALES SALES_Q2_1999 54233 NO

30. SALES SALES_Q2_2000 55515 NO

31. SALES SALES_Q2_2001 63292 NO

32. SALES SALES_Q2_2002 0 NO

33. SALES SALES_Q2_2003 0 NO

34. SALES SALES_Q3_1998 50515 NO

35. SALES SALES_Q3_1999 67138 NO

36. SALES SALES_Q3_2000 58950 NO

37. SALES SALES_Q3_2001 65769 NO

38. SALES SALES_Q3_2002 0 NO

39. SALES SALES_Q3_2003 0 NO

40. SALES SALES_Q4_1998 48874 NO

41. SALES SALES_Q4_1999 62388 NO

42. SALES SALES_Q4_2000 55984 NO

43. SALES SALES_Q4_2001 69749 NO

44. SALES SALES_Q4_2002 0 NO

45. SALES SALES_Q4_2003 0 NO

SALES 918843 NO

The preceding output shows that none of the statistics are stale.

Perform DML on sales and customers:

47. -- insert 918K rows in sales

48. INSERT INTO sh.sales SELECT * FROM sh.sales;

49. -- update around 15% of sales rows

50. UPDATE sh.sales SET amount_sold = amount_sold + 1 WHERE amount_sold > 100;

51. -- insert 1 row into customers

52. INSERT INTO sh.customers(cust_id, cust_first_name, cust_last_name,

53. cust_gender, cust_year_of_birth, cust_main_phone_number,

54. cust_street_address, cust_postal_code, cust_city_id,

55. cust_city, cust_state_province_id, cust_state_province,

56. country_id, cust_total, cust_total_id)

57. VALUES(188710, 'Jenny', 'Smith', 'F', '1966', '555-111-2222',

58. '400 oracle parkway','94065',51402, 'Redwood Shores',

59. 52564, 'CA', 52790, 'Customer total', '52772');

COMMIT;

The total number of sales rows increased by 100%, but only 1 row was added to customers.

Save the optimizer statistics to disk:

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

Query the table statistics again (sample output included):

62. SELECT TABLE_NAME, PARTITION_NAME, NUM_ROWS, STALE_STATS

63. FROM DBA_TAB_STATISTICS

64. WHERE OWNER = 'SH'

65. AND TABLE_NAME IN ('CUSTOMERS','SALES')

66. ORDER BY TABLE_NAME, PARTITION_NAME;

67.

68. TABLE_NAME PARTITION_NAME NUM_ROWS STA

69. -------------------- -------------------- -------- ---

70. CUSTOMERS 55500 NO

71. SALES SALES_1995 0 NO

72. SALES SALES_1996 0 NO

73. SALES SALES_H1_1997 0 NO

74. SALES SALES_H2_1997 0 NO

75. SALES SALES_Q1_1998 43687 YES

76. SALES SALES_Q1_1999 64186 YES

77. SALES SALES_Q1_2000 62197 YES

78. SALES SALES_Q1_2001 60608 YES

79. SALES SALES_Q1_2002 0 NO

80. SALES SALES_Q1_2003 0 NO

81. SALES SALES_Q2_1998 35758 YES

82. SALES SALES_Q2_1999 54233 YES

83. SALES SALES_Q2_2000 55515 YES

84. SALES SALES_Q2_2001 63292 YES

85. SALES SALES_Q2_2002 0 NO

86. SALES SALES_Q2_2003 0 NO

87. SALES SALES_Q3_1998 50515 YES

88. SALES SALES_Q3_1999 67138 YES

89. SALES SALES_Q3_2000 58950 YES

90. SALES SALES_Q3_2001 65769 YES

91. SALES SALES_Q3_2002 0 NO

92. SALES SALES_Q3_2003 0 NO

93. SALES SALES_Q4_1998 48874 YES

94. SALES SALES_Q4_1999 62388 YES

95. SALES SALES_Q4_2000 55984 YES

96. SALES SALES_Q4_2001 69749 YES

97. SALES SALES_Q4_2002 0 NO

98. SALES SALES_Q4_2003 0 NO

99. SALES 1837686

100. SALES 918843 YES

101.

102. 31 rows selected.

The preceding output shows that the statistics are not stale for customers but are stale for sales.

Configure high-frequency automatic optimizer statistics collection:

104. EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');

105. EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','180');

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240');

The preceding PL/SQL programs enable high-frequency collection, set the maximum run time to 3 minutes, and set the task execution interval to 4 minutes.

Wait for a few minutes, and then query the data dictionary:

107. COL OPID FORMAT 9999

108. COL STATUS FORMAT a11

109. COL ORIGIN FORMAT a20

110. COL COMPLETED FORMAT 99999

111. COL FAILED FORMAT 99999

112. COL TIMEOUT FORMAT 99999

113. COL INPROG FORMAT 99999

114.

115. SELECT OPID, ORIGIN, STATUS, TO_CHAR(START_TIME, 'DD/MM HH24:MI:SS' ) AS BEGIN_TIME,

116. TO_CHAR(END_TIME, 'DD/MM HH24:MI:SS') AS END_TIME, COMPLETED, FAILED,

117. TIMED_OUT AS TIMEOUT, IN_PROGRESS AS INPROG

118. FROM DBA_AUTO_STAT_EXECUTIONS

119. ORDER BY OPID;

The output shows that the high-frequency job executed twice, and the standard automatic statistics collection job executed once:

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

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：OracleDatabase-WorkingondatabaseswithSGAof1TBand2.6TB

后一篇：19c-dbca功能增强

新浪BLOG意见反馈留言板　欢迎批评指正