[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环境中.
--//找到链接:https://www.anbob.com/archives/2779.html,摘要并简单翻译如下:
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观察看看.
[20221015]mmon_slave sql_id=c9umxngkc3byq Automatic Report Flush.sql
-
lfree
2022-11-03 20:14:40 -
Oracle
- 原创