[20221015]mmon_slave sql_id=c9umxngkc3byq Automatic Report Flush.sql
[20221015]mmon_slave sql_id=c9umxngkc3byq Automatic Report Flush.sql
--//生產系統最佳化快接近尾聲,不過一條sql語句後臺mmon執行的,有點靠前,分析看看.
1.環境:
SYS@192.168.100.235:1521/orcl> @ pr
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.分析:
SYS@192.168.100.235:1521/orcl> @ ashtop sql_id,module1 "module1!='jdbc thin client'" &day
Total Distinct Distinct
Seconds AAS %This SQL_ID MODULE1 FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- -------------------- ------------------- ------------------- ---------- --------
6369 .1 25% | backup full datafile 2022-10-14 00:47:26 2022-10-14 09:35:51 1 2393
4817 .1 19% | w3wp.exe 2022-10-13 10:55:25 2022-10-14 10:55:13 4 4055
2173 .0 8% | backup incr datafile 2022-10-13 23:50:10 2022-10-14 00:26:36 1 2173
1526 .0 6% | backup archivelog 2022-10-14 00:26:45 2022-10-14 03:12:14 1 1352
713 .0 3% | c9umxngkc3byq mmon_slave 2022-10-13 10:59:46 2022-10-14 10:54:46 713 713
...
--//MODULE=backup full datafile,怎麼回事,這麼慢嗎?而且出現時間交錯.archivelog的備份出現2022-10-14 00:26:45.
--//而全備份開始時間卻是2022-10-14 00:47:26.
--//註解:已經搞清楚了,存在2次備份,1次寫入虛擬磁帶庫,另外1次寫入NFS.
--//實際上1次寫入虛擬磁帶庫的archivelog結束時間大約在2022-10-14 00:47:XX,接著做全備份到虛擬磁帶庫.
--//一些細節另外寫一篇blog.
SYS@192.168.100.235:1521/orcl> @ ashtop sql_id,action "module1='mmon_slave'" &day
Total Distinct Distinct
Seconds AAS %This SQL_ID ACTION FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- --------------------------------------- ------------------- ------------------- ---------- --------
687 .0 70% | c9umxngkc3byq Automatic Report Flush 2022-10-17 10:06:44 2022-10-18 09:33:42 687 687
55 .0 6% | 9yv5dwv8k0awg Automatic Report Flush 2022-10-17 10:21:46 2022-10-18 08:50:42 33 55
36 .0 4% | ADR Auto Purge Task 2022-10-17 20:09:30 2022-10-17 20:10:05 1 36
19 .0 2% | 2vb9hsvpw0gtg Flush KSXM hash table action 2022-10-17 10:44:49 2022-10-18 09:00:44 19 19
15 .0 2% | 22356bkgsdcnh Monitor FRA Space 2022-10-17 10:29:48 2022-10-18 09:30:45 15 15
14 .0 1% | Flush KSXM hash table action 2022-10-17 10:29:48 2022-10-18 09:30:45 1 14
13 .0 1% | Auto-Flush Slave Action 2022-10-17 15:00:58 2022-10-18 10:00:45 1 13
10 .0 1% | Cleanup KGL Handle Action 2022-10-17 11:27:41 2022-10-18 07:58:32 1 10
8 .0 1% | 1kb56hd6c73bv Execute Stats Advisor auto task action 2022-10-17 11:09:08 2022-10-18 06:09:55 8 8
7 .0 1% | dcbt0gjzjwwbs Auto-Flush Slave Action 2022-10-17 15:00:57 2022-10-18 10:00:44 7 7
6 .0 1% | 1zrnrntukph5h AWR Auto-Purge Slave Action 2022-10-17 22:13:13 2022-10-17 22:13:18 1 6
6 .0 1% | 2p9fv35c7zxtg Flush KSXM hash table action 2022-10-17 22:15:17 2022-10-18 06:15:37 3 6
6 .0 1% | drktaf71uygnb Auto ADDM Slave Action 2022-10-17 11:00:49 2022-10-18 08:00:40 6 6
5 .0 1% | 1j6tnz8fcm4c3 Cleanup optimizer workload information 2022-10-17 18:34:13 2022-10-18 06:34:18 3 5
5 .0 1% | 3kqrku32p6sfn Flush KSXM hash table action 2022-10-17 15:45:01 2022-10-18 04:45:34 5 5
5 .0 1% | 5n48v6nam7jg2 Auto-Flush Slave Action 2022-10-17 14:00:54 2022-10-18 09:00:41 5 5
5 .0 1% | atwuyuvqkf27w Automatic Report Flush 2022-10-17 22:20:17 2022-10-18 08:50:43 5 5
4 .0 0% | 0dfxfyy5r32qq Flush KSXM hash table action 2022-10-17 13:44:56 2022-10-18 09:45:46 4 4
4 .0 0% | 743pmc01unxc6 Auto-Flush Slave Action 2022-10-17 12:00:52 2022-10-18 09:00:43 4 4
4 .0 0% | 8ncaj7hgazfyp Cleanup optimizer workload information 2022-10-17 12:34:09 2022-10-18 06:34:14 4 4
4 .0 0% | b13g21mgg8y98 Flush KSXM hash table action 2022-10-17 10:59:50 2022-10-18 09:15:45 4 4
4 .0 0% | bku5fwd8bhsn6 Execute Stats Advisor auto task action 2022-10-17 10:09:05 2022-10-18 09:10:02 4 4
4 .0 0% | d1p03prpzpquf Auto-Flush Slave Action 2022-10-17 13:00:52 2022-10-18 03:00:27 4 4
3 .0 0% | 3dbzmtf9ahvzt Flush KSXM hash table action 2022-10-17 11:44:52 2022-10-17 19:00:09 3 3
3 .0 0% | bjck50zs3nc95 Auto-Flush Slave Action 2022-10-17 11:00:47 2022-10-18 01:00:22 3 3
3 .0 0% | f705bwx3q0ydq Execute Stats Advisor auto task action 2022-10-17 14:09:15 2022-10-18 00:09:40 3 3
2 .0 0% | 0q61gy73sq1jq Auto-Flush Slave Action 2022-10-17 23:00:17 2022-10-18 00:00:20 2 2
2 .0 0% | 5cu0x10yu88sw Index usage tracking statistics flush 2022-10-18 00:30:23 2022-10-18 09:30:45 2 2
2 .0 0% | 663jk29qwzufs Auto-Flush Slave Action 2022-10-17 12:00:50 2022-10-18 02:00:25 2 2
2 .0 0% | b1d01znfww5sh Auto-Flush Slave Action 2022-10-17 13:00:51 2022-10-18 00:00:19 2 2
30 rows selected.
SYS@192.168.100.235:1521/orcl> @ sqlhh c9umxngkc3byq 1
BEGIN_INTERVAL_TIME INST_ID SQL_ID PLAN_HASH_VALUE EXECUTIONS ELA_MS_PER_EXEC CPU_MS_PER_EXEC ROWS_PER_EXEC LIOS_PER_EXEC BLKRD_PER_EXEC IOW_MS_PER_EXEC AVG_IOW_MS CLW_MS_PER_EXEC APW_MS_PER_EXEC CCW_MS_PER_EXEC
------------------- ---------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- ---------------
2022-10-13 11:00:46 1 c9umxngkc3byq 3083280885 50 632 633 0.0 0 0 0 0.0 0 0 0
2022-10-13 12:00:49 1 c9umxngkc3byq 3083280885 53 577 576 0.0 0 0 0 0.0 0 0 0
2022-10-13 13:00:52 1 c9umxngkc3byq 3083280885 55 560 570 0.0 0 0 0 0.0 0 0 0
2022-10-13 14:00:54 1 c9umxngkc3byq 3083280885 49 580 621 0.0 0 0 0 0.0 0 0 0
2022-10-13 15:00:57 1 c9umxngkc3byq 3083280885 53 584 596 0.0 0 0 0 0.0 0 0 0
2022-10-13 16:00:59 1 c9umxngkc3byq 3083280885 43 591 591 0.1 0 0 0 0.0 0 0 0
2022-10-13 17:00:02 1 c9umxngkc3byq 3083280885 47 578 579 0.1 0 0 0 0.0 0 0 0
2022-10-13 18:00:04 1 c9umxngkc3byq 3083280885 49 584 624 0.0 0 0 0 0.0 0 0 0
2022-10-13 19:00:06 1 c9umxngkc3byq 3083280885 54 581 630 0.0 0 0 0 0.0 0 0 0
2022-10-13 20:00:09 1 c9umxngkc3byq 3083280885 56 576 630 0.0 0 0 0 0.0 0 0 0
2022-10-13 21:00:11 1 c9umxngkc3byq 3083280885 54 580 604 0.0 0 0 0 0.0 0 0 0
2022-10-13 22:00:14 1 c9umxngkc3byq 3083280885 44 566 591 0.1 0 0 0 0.0 0 0 0
2022-10-13 23:00:16 1 c9umxngkc3byq 3083280885 56 569 568 0.0 0 0 0 0.0 0 0 0
2022-10-14 00:00:19 1 c9umxngkc3byq 3083280885 56 581 584 0.0 0 0 0 0.0 0 0 0
2022-10-14 01:00:22 1 c9umxngkc3byq 3083280885 52 572 573 0.0 0 0 0 0.0 0 0 0
2022-10-14 02:00:24 1 c9umxngkc3byq 3083280885 49 582 589 0.0 0 0 0 0.0 0 0 0
2022-10-14 03:00:27 1 c9umxngkc3byq 3083280885 53 597 601 0.0 0 0 0 0.0 0 0 0
2022-10-14 04:00:29 1 c9umxngkc3byq 3083280885 54 573 592 0.0 0 0 0 0.0 0 0 0
2022-10-14 05:00:32 1 c9umxngkc3byq 3083280885 57 565 597 0.0 0 0 0 0.0 0 0 0
2022-10-14 06:00:34 1 c9umxngkc3byq 3083280885 57 554 584 0.0 0 0 0 0.0 0 0 0
2022-10-14 07:00:36 1 c9umxngkc3byq 3083280885 55 575 596 0.0 0 0 0 0.0 0 0 0
2022-10-14 08:00:39 1 c9umxngkc3byq 3083280885 55 579 598 0.0 0 0 0 0.0 0 0 0
2022-10-14 09:00:41 1 c9umxngkc3byq 3083280885 53 555 555 0.0 0 0 0 0.0 0 0 0
23 rows selected.
$ rlsql sys/xxxxx@192.168.100.235:1521/orcl as sysdba @sqlhh c9umxngkc3byq 100 | grep "^2022-10-1. 22:"
2022-10-10 22:00:11 1 c9umxngkc3byq 3083280885 40 562 581 0.2 0 0 0 0.0 0 0 0
2022-10-11 22:00:12 1 c9umxngkc3byq 3083280885 41 563 564 0.1 0 0 0 0.0 0 0 0
2022-10-12 22:00:14 1 c9umxngkc3byq 3083280885 40 559 559 0.1 0 0 0 0.0 0 0 0
2022-10-13 22:00:14 1 c9umxngkc3byq 3083280885 44 566 591 0.1 0 0 0 0.0 0 0 0
2022-10-14 22:00:13 1 c9umxngkc3byq 3083280885 26 578 595 0.4 0 0 0 0.0 0 0 0
2022-10-15 22:00:13 1 c9umxngkc3byq 3083280885 28 585 598 0.4 0 0 0 0.0 0 0 0
2022-10-16 22:00:14 1 c9umxngkc3byq 3083280885 28 604 631 0.3 0 0 0 0.0 0 0 0
2022-10-17 22:00:13 1 c9umxngkc3byq 3083280885 14 644 645 0.9 0 0 0 0.0 0 0 0
quit
--//從每小時的執行次數看,我猜測如果執行很快,每分鐘呼叫1次.這樣應該每小時執行接近60次.
--//假設每小時執行55次,迴圈佔55分鐘,(60-55)*60/55 = 5.454秒,這樣每次迴圈執行1次佔用5秒.還是很慢的.不知道這樣推斷是否存在
--//問題.
--//ELA_MS_PER_EXEC列也可以看出,每次執行需要0.6秒上下.
--//2022-10-1X 22:00:XX 執行次數偏少,也說明晚上分析表佔用許多資源.
--//從ROWS_PER_EXEC也可以看出大部分執行返回0行.0.14*55 = 7.7,也就是1個小時內最多返回8行.(ROWS_PER_EXEC=0.9是一個例外).
SYS@192.168.100.235:1521/orcl> @ sqlid c9umxngkc3byq
--sql_id = c9umxngkc3byq
select sql_id, sql_exec_id, dbop_name, dbop_exec_id, to_char(sql_exec_start, 'mm:dd:yyyy hh24:mi:ss'), to_char(first_refresh_time, 'mm:dd:yyyy hh24:mi:ss'), to_char(last_refresh_time, 'mm:dd:yyyy hh24:mi:ss'), elapsed_time, px_servers_allocated, sid, sess
ion_serial#, key, con_id from v$sql_monitor where report_id = 0 and status != 'EXECUTING' and status != 'QUEUED' and px_qcsid is null and last_refresh_time > (select nvl(last_cycle_time, sysdate-(5/1440)) from v$sy
s_report_stats)
--//方便閱讀格式化如下:
SELECT sql_id
,sql_exec_id
,dbop_name
,dbop_exec_id
,TO_CHAR (sql_exec_start, 'mm:dd:yyyy hh24:mi:ss')
,TO_CHAR (first_refresh_time, 'mm:dd:yyyy hh24:mi:ss')
,TO_CHAR (last_refresh_time, 'mm:dd:yyyy hh24:mi:ss')
,elapsed_time
,px_servers_allocated
,sid
,session_serial#
,key
,con_id
FROM v$sql_monitor
WHERE report_id = 0
AND status != 'EXECUTING'
AND status != 'QUEUED'
AND px_qcsid IS NULL
AND last_refresh_time >
(SELECT NVL (last_cycle_time, SYSDATE - (5 / 1440))
FROM v$sys_report_stats)
--//檢索Automatic Report Flush,發現https://blog.csdn.net/qq_23170065/article/details/117717132.
--//裡面提到遭遇ORA-12850 bug,特別在rac環境中.
--//找到連結:摘要並簡單翻譯如下:
if you upgrading to Oracle 12c, sometimes seen high CPU use in the MMON_SLAVE modules.These are associated with Oracle
new Automatic Report Capturing functionality.
如果您升級到Oracle 12c，有時會看到在MMON_SLAVE模組中使用高CPU。這些都與Oracle新的自動報告捕獲功能相關聯。
Oracle 11g is introduced real-time SQL monitoring , for real-time monitoring of the performance of SQL in the
implementation;
Oracle 11g引入了實時SQL監控，用於在實現過程中實時監控SQL的效能；
Oracle 12c to further expand its introduction of the historical SQL monitoring function. This function is similar to the
history management of the ASH information through the background process MMON_SLAVE on a regular basis to SQL monitoring
information stored in the database table.
Oracle 12c將進一步擴充套件其對歷史SQL監控功能的引入。該功能類似於透過後臺程式MMON_SLAVE定期對在資料庫表中儲存的SQL監控資訊進
行ASH資訊的歷史管理。
As part of this feature, some monitoring SQLs are executed by MMON_SLAVE to identify the resource-intensive SQLs and
generate the SQL Monitoring report automatically for those SQLs. Those SQLs consume little more CPU and it is expected
behavior being a new feature.Such monitoring queries can be identified from (G)V$SQLSTATS.
作為這個特性的一部分，一些監視sql由MMON_SLAVE執行，以識別資源密集型的sql和Oracle 12c將進一步擴充套件其對歷史SQL監控功能的引
入。該功能類似於透過後臺程式MMON_SLAVE定期對在資料庫表中儲存的SQL監控資訊進行ASH資訊的歷史管理。
MMON consumes little more CPU due to the monitoring activity in 12.1, However, If the CPU consumption is significantly
high then it is not an expected behavior and could be due to optimizer choosing suboptimal plan for the SQL statements.
由於12.1中的監視活動，MMON很少消耗更多的CPU，但是，如果CPU消耗非常高，那麼它就不是一個預期的行為，可能是由於最佳化器為SQL
語句選擇了次優計劃。
This can happen due to Adaptive Optimization, a new feature in 12c.
這可能是由於自適應最佳化，一個在12c的新特性。
--//我的理解相當於定期收集有問題sql語句,Automatic Report Flush.
3.跟蹤看看:
--//to enable trace the MMON_SLAVE function behavior,
begin
dbms_monitor.serv_mod_act_trace_enable
(service_name=>'SYS$BACKGROUND',
module_name=>'MMON_SLAVE',
action_name=>'Automatic Report Flush',
waits => true,
binds => true);
end;
/
--//TIP:
--//The service_name in the argument corresponds to the service_name of the v$session view,
--//the module_name corresponds to the module of the v$session view,
--//and the action_name corresponds to the action of the v$session view. The query is as follows:
SYS@192.168.100.235:1521/orcl> SELECT sid, serial#, client_identifier, service_name, action, module FROM V$SESSION where module='MMON_SLAVE';
SID SERIAL# CLIENT_IDENTIFIER SERVICE_NAME ACTION MODULE
---- ---------- ----------------- -------------- ----------------------------- ------------
582 4713 SYS$BACKGROUND Automatic Report Flush MMON_SLAVE
1987 38769 SYS$BACKGROUND KDILM background CLeaNup MMON_SLAVE
2265 22782 SYS$BACKGROUND Intensive AutoTask Dispatcher MMON_SLAVE
2846 44614 SYS$BACKGROUND KDILM background EXEcution MMON_SLAVE
SYS@192.168.100.235:1521/orcl> @ sid 1987
sid = 1987
SPID PID SID SERIAL# CLIENT_INFO PNAME TRACEFILE PROGRAM TERMINAL SQL_ID STATUS C50
------ ------- ---------- ---------- -------------------- ------ --------------------------------------------------------------- -------------------- ------------ ------------- -------- --------------------------------------------------
201993 135 1987 38769 M001 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m001_201993.trc oracle@LIS-DB (M001) UNKNOWN ACTIVE alter system kill session '1987,38769' immediate;
SYS@192.168.100.235:1521/orcl> SELECT sid, serial#, client_identifier, service_name, action, module FROM V$SESSION where module='MMON_SLAVE';
SID SERIAL# CLIENT_IDENTIFIER SERVICE_NAME ACTION MODULE
---- ---------- ----------------- -------------- ----------------------------------------------- ------------
582 4713 SYS$BACKGROUND KDILM background EXEcution MMON_SLAVE
1987 38769 SYS$BACKGROUND ADR Container Space Management Statistics Flush MMON_SLAVE
2265 22782 SYS$BACKGROUND KDILM background CLeaNup MMON_SLAVE
2846 44614 SYS$BACKGROUND Automatic Report Flush MMON_SLAVE
--//另外我發現並不固定sid執行Automatic Report Flush.現在變成sid=2846執行.
$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
$ grep c9umxngkc3byq *.trc
orcl_m000_38013.trc:PARSING IN CURSOR #140215206045744 len=525 dep=1 uid=0 oct=3 lid=0 tim=26801013773037 hv=3837898710 ad='1b73c3578' sqlid='c9umxngkc3byq'
orcl_m001_201993.trc:PARSING IN CURSOR #140539323626640 len=525 dep=1 uid=0 oct=3 lid=0 tim=26800833665247 hv=3837898710 ad='1b73c3578' sqlid='c9umxngkc3byq'
orcl_m002_251187.trc:PARSING IN CURSOR #139897641759048 len=525 dep=1 uid=0 oct=3 lid=0 tim=26800653593126 hv=3837898710 ad='1b73c3578' sqlid='c9umxngkc3byq'
orcl_m002_251187.trc:PARSING IN CURSOR #139897641759048 len=525 dep=1 uid=0 oct=3 lid=0 tim=26800713536130 hv=3837898710 ad='1b73c3578' sqlid='c9umxngkc3byq'
orcl_m003_79084.trc:PARSING IN CURSOR #140331843165032 len=525 dep=1 uid=0 oct=3 lid=0 tim=26800953756281 hv=3837898710 ad='1b73c3578' sqlid='c9umxngkc3byq'
--//m000 .. m003都有執行.
$ ls -l orcl_m000_38013.trc orcl_m001_201993.trc orcl_m002_251187.trc orcl_m003_79084.trc
-rw-r-----. 1 oracle oinstall 26803 2022-10-18 09:20:41 orcl_m000_38013.trc
-rw-r-----. 1 oracle oinstall 138675 2022-10-18 09:21:42 orcl_m001_201993.trc
-rw-r-----. 1 oracle oinstall 284024 2022-10-18 09:21:41 orcl_m002_251187.trc
-rw-r-----. 1 oracle oinstall 332230 2022-10-18 09:19:41 orcl_m003_79084.trc
--//間隔1分鐘.不過orcl_m001_201993.trc,orcl_m002_251187.trc時間重合,存在多次呼叫嗎?ls 顯示應該是修改時間.
--//to verify the diag can be query dba_enabled_traces after enabe trace above;
SELECT primary_id AS service_name, qualifier_id1 AS module_name,
qualifier_id2 AS action_name, waits, binds
FROM dba_enabled_traces
WHERE trace_type = 'SERVICE_MODULE_ACTION'
SERVICE_NAME MODULE_NAME ACTION_NAME WAITS BINDS
-------------- ----------- ---------------------- ----- -----
SYS$BACKGROUND MMON_SLAVE Automatic Report Flush TRUE TRUE
--//分析其中1個.
$ tkprof orcl_m001_201993.trc out.txt
TKPROF: Release 19.0.0.0.0 - Development on Tue Oct 18 09:31:09 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
--//trace file will show below sql
SQL ID: c9umxngkc3byq Plan Hash: 3083280885
select sql_id, sql_exec_id, dbop_name, dbop_exec_id, to_char(sql_exec_start,
'mm:dd:yyyy hh24:mi:ss'), to_char(first_refresh_time, 'mm:dd:yyyy
hh24:mi:ss'), to_char(last_refresh_time, 'mm:dd:yyyy hh24:mi:ss'),
elapsed_time, px_servers_allocated, sid, session_serial#, key, con_id
from
v$sql_monitor where report_id = 0 and status != 'EXECUTING' and
status != 'QUEUED' and px_qcsid is null and last_refresh_time >
(select nvl(last_cycle_time, sysdate-(5/1440)) from
v$sys_report_stats)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 6 3.53 3.53 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 3.53 3.53 0 0 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 FIXED TABLE FULL X$KESWXMON (cr=0 pr=0 pw=0 time=579541 us starts=1 cost=0 size=111 card=1)
1 1 1 FIXED TABLE FULL X$KERPISTATS (cr=0 pr=0 pw=0 time=13 us starts=1 cost=0 size=11 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
class slave wait 5 119.48 357.30
--//與sql_id=c9umxngkc3byq完全對上.
--//Also show call sys.dbms_auto_report_internal.i_save_report to save sql monitor to table , Later you can query the
--//relevant view DBA_HIST_REPORTS and DBA_HIST_REPORTS_DETAILS
--//To disable MMON slave tracing:
begin
dbms_monitor.serv_mod_act_trace_disable(service_name=>'SYS$BACKGROUND',
module_name=>'MMON_SLAVE',
action_name=>'Automatic Report Flush');
end;
/
4.禁用該功能:
--//從上面的sqlhh的輸出可以看出,Automatic Report Flush就是一個雞肋,ROWS_PER_EXEC的輸出基本為0,而消耗的資源相對較多.
--//可以禁止它,5*60*24 = 7200 ,這樣相當於一天之內節約db time減少接近7200秒(相當於2個小時).
SYS@192.168.100.235:1521/orcl> @hide _report_capture_cycle_time
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------------------------- --------------------------------------------------------- ------------- ------------- ------------ ----- ---------
_report_capture_cycle_time Time (in sec) between two cycles of report capture daemon TRUE 60 60 FALSE IMMEDIATE
SYS@192.168.100.235:1521/orcl> @pd _report_capture_cycle_time
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- -------------------------- ----- ---------------------------------------------------------
5054 13BE _report_capture_cycle_time 60 Time (in sec) between two cycles of report capture daemon
--//預設60秒.
--//The new feature can be disabled to reduce the CPU consumption:
--//SQL> alter system set "_report_capture_cycle_time"=0; /* Default is 60 seconds */
SYS@192.168.100.235:1521/orcl> alter system set "_report_capture_cycle_time"=0 scope=memory;
System altered.
--//等多幾天再觀察看看.
--//有一點點奇怪的是ODA執行就不慢.也許ODA效能更好,前面的資料庫建立在虛擬機器上的.
SYS@192.168.100.141:1521/dyhis> @ pr
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
SYS@192.168.100.141:1521/dyhis> @ sqlhh c9umxngkc3byq 4
no rows selected
--//沒有記錄.
SYS@192.168.100.141:1521/dyhis> @ sqlid c9umxngkc3byq
--sql_id = c9umxngkc3byq
select sql_id, sql_exec_id, dbop_name, dbop_exec_id, to_char(sql_exec_start, 'mm:dd:yyyy hh24:mi:ss'), to_char(first_refresh_time, 'mm:dd:yyyy hh24:mi:ss'), to_char(last_refresh_time, 'mm:dd:yyyy hh24:mi:ss'), elapsed_time, px_servers_allocated, sid, sess
ion_serial#, key, con_id from v$sql_monitor where report_id = 0 and status != 'EXECUTING' and status != 'QUEUED' and px_qcsid is null and last_refresh_time > (select nvl(last_cycle_time, sysdate-(5/1440)) from v$sy
s_report_stats)
SYS@192.168.100.141:1521/dyhis> @ dpc c9umxngkc3byq '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c9umxngkc3byq, child number 0
-------------------------------------
select sql_id, sql_exec_id, dbop_name, dbop_exec_id,
to_char(sql_exec_start, 'mm:dd:yyyy hh24:mi:ss'),
to_char(first_refresh_time, 'mm:dd:yyyy hh24:mi:ss'),
to_char(last_refresh_time, 'mm:dd:yyyy hh24:mi:ss'), elapsed_time,
px_servers_allocated, sid, session_serial#, key, con_id from
v$sql_monitor where report_id = 0 and status != 'EXECUTING' and
status != 'QUEUED' and px_qcsid is null and
last_refresh_time > (select nvl(last_cycle_time,
sysdate-(5/1440)) from v$sys_report_stats)
Plan hash value: 297230549
------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|* 1 | FIXED TABLE FULL | X$KESWXMON | 1 | 113 | 0 (0)|
|* 2 | FIXED TABLE FULL| X$KERPISTATS | 1 | 11 | 0 (0)|
------------------------------------------------------------------------
--//注意Plan hash value: 297230549,而前面的是3083280885.為什麼?而看到的執行計劃是一樣的.難道不同的機器執行計劃相同,Plan
--//hash value可以不同嗎?
--//出現問題的系統看到的執行計劃如下:
Plan hash value: 3083280885
------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|* 1 | FIXED TABLE FULL | X$KESWXMON | 1 | 111 | 0 (0)|
|* 2 | FIXED TABLE FULL| X$KERPISTATS | 1 | 11 | 0 (0)|
------------------------------------------------------------------------
--//Plan hash value: 3083280885 ,為什麼呢?
--//另外寫一篇blog觀察看看.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2921795/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Automatic Workload Repository Compare Period report
- 問題：V$SESSION SQL_ID 為空，找不到SQL_IDSessionSQL
- Automatic Manageability Features : Automatic Maintenance Tasks (53)AINaN
- Automatic Manageability Features : Automatic Workload Repository (52)
- ASM(Automatic Storage Management)ASM
- Disable Oracle Automatic JobsOracle
- statspack report分析
- Standby Database for reportDatabase
- Oracle Statspack ReportOracle
- Automatic Storage Management (ASM)(轉)ASM
- Automatic SQL Tuning 原理SQL
- SAP APP: Automatic Payment ProgramAPP
- Oracle Automatic PGA Memory ManagementOracle
- Automatic Manageability Features (51)
- 有相同sql_id的sql語句SQL
- 透過AWR REPORT 或 ADDM REPORT進行SQLTUNESQL
- Jest-Vue-ReportVue
- EBS SQL --> Form & ReportSQLORM
- ituring Error ReportError
- 系統bug report
- android junit reportAndroid
- statspack report分析 (zt)
- SQL_ID怎麼轉化成HASH_VALUESQL
- React18 Automatic batchingReactBAT
- Oracle 12c Automatic ReoptimizationOracle
- Automatic Reference Counting-SwiftSwift
- AWR: Automatic Workload Repository
- ADDM: Automatic Database Diagnostic MonitorDatabase
- Automatic Workload Repository ViewsView
- Automatic Diagnostic Repository (ADR)
- Automatic SQL Tuning and SQL ProfilesSQL
- AWR(Automatic Workload Repository)
- Oracle SQL_ID轉換成SQL_HASH_VALUEOracleSQL
- In V$SESSION, column SQL_ID is not NULL while STATUS is INACTIVESessionSQLNullWhile
- Oracle如何根據SQL_TEXT生成SQL_IDOracleSQL
- 根據SQL_ID檢視執行計劃SQL
- ash報告中無sql_id的情況SQL
- [轉]Pentaho:Create a Report with MongoDBMongoDB