[20240409]time_model_name=CONNECTION_MGMT表示什么.txt
[20240409]time_model_name=CONNECTION_MGMT表示什么.txt
--//上午看了ashtop的time_model_name的输出,对于CONNECTION_MGMT表示怎么不是很清楚,探究看看.
1.环境:
SYS@127.0.0.1:9014/ywdb> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.分析:
--//一天之内,数据库CPU消耗分布情况.
SYS@127.0.0.1:9014/ywdb> @ ashtop time_model_name,time_model 1=1 &day
Total Distinct Distinct Distinct
Seconds AAS %This TIME_MODEL_NAME TIME_MODEL FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- -------------------------------------------------- ---------- ------------------- ------------------- ---------- -------- -----------
27086 .3 42% | SQL_EXECUTION 1024 2024-04-08 15:31:15 2024-04-09 15:31:07 20129 24365 20945
25275 .3 39% | 0 2024-04-08 15:31:28 2024-04-09 15:31:04 1223 15932 16026
5033 .1 8% | PARSE HARD_PARSE 144 2024-04-08 15:31:13 2024-04-09 15:31:08 1 4834 4834
1662 .0 3% | CONNECTION_MGMT SQL_EXECUTION 1032 2024-04-08 15:31:28 2024-04-09 15:31:07 1441 1556 1609
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1622 .0 3% | PARSE 16 2024-04-08 15:32:05 2024-04-09 15:29:50 2 1527 1527
959 .0 1% | PARSE HARD_PARSE SQL_EXECUTION 1168 2024-04-08 15:31:57 2024-04-09 15:30:38 4 912 911
726 .0 1% | SQL_EXECUTION PLSQL_EXECUTION 3072 2024-04-08 15:33:05 2024-04-09 15:30:52 687 702 718
628 .0 1% | CONNECTION_MGMT SQL_EXECUTION PLSQL_EXECUTION 3080 2024-04-08 15:31:54 2024-04-09 15:30:23 578 585 578
432 .0 1% | CONNECTION_MGMT 8 2024-04-08 15:33:16 2024-04-09 15:30:56 13 375 375
317 .0 0% | CURSOR_CLOSE 65536 2024-04-08 15:35:08 2024-04-09 15:27:07 1 315 315
295 .0 0% | CONNECTION_MGMT PARSE SQL_EXECUTION 1048 2024-04-08 15:32:33 2024-04-09 15:29:10 75 261 270
162 .0 0% | PARSE SQL_EXECUTION 1040 2024-04-08 15:53:52 2024-04-09 14:57:28 8 88 89
159 .0 0% | PARSE HARD_PARSE PLSQL_COMPILATION 8336 2024-04-08 15:42:43 2024-04-09 15:24:20 1 155 155
132 .0 0% | PARSE BIND 32784 2024-04-08 15:46:33 2024-04-09 15:28:45 1 132 132
125 .0 0% | BIND 32768 2024-04-08 15:32:16 2024-04-09 15:22:44 1 125 125
103 .0 0% | PARSE HARD_PARSE SQL_EXECUTION PLSQL_COMPILATION 9360 2024-04-08 15:44:28 2024-04-09 15:11:14 2 103 103
84 .0 0% | CONNECTION_MGMT PARSE 24 2024-04-08 16:02:31 2024-04-09 15:27:30 2 83 83
22 .0 0% | PARSE HARD_PARSE BIND 32912 2024-04-08 16:36:13 2024-04-09 14:30:52 1 22 22
11 .0 0% | PARSE HARD_PARSE CURSOR_CLOSE 65680 2024-04-08 18:00:29 2024-04-09 15:30:35 1 11 11
7 .0 0% | CONNECTION_MGMT CURSOR_CLOSE 65544 2024-04-08 16:04:14 2024-04-09 12:18:29 1 7 7
5 .0 0% | CONNECTION_MGMT BIND 32776 2024-04-08 16:38:42 2024-04-09 11:04:44 1 5 5
5 .0 0% | CONNECTION_MGMT SQL_EXECUTION BIND 33800 2024-04-09 03:45:37 2024-04-09 15:11:29 1 5 5
4 .0 0% | SQL_EXECUTION PLSQL_EXECUTION CURSOR_CLOSE 68608 2024-04-08 15:47:01 2024-04-08 20:01:46 2 4 4
3 .0 0% | CONNECTION_MGMT SQL_EXECUTION PLSQL_EXECUTION 68616 2024-04-09 01:49:52 2024-04-09 09:39:21 3 3 3
CURSOR_CLOSE
2 .0 0% | PARSE HARD_PARSE SQL_EXECUTION PLSQL_RPC 5264 2024-04-09 02:56:01 2024-04-09 02:58:18 1 2 2
2 .0 0% | PARSE SQL_EXECUTION BIND 33808 2024-04-09 10:09:18 2024-04-09 12:54:03 1 2 2
2 .0 0% | SQL_EXECUTION BIND 33792 2024-04-09 09:51:40 2024-04-09 10:43:47 1 2 2
2 .0 0% | SQL_EXECUTION PLSQL_RPC 5120 2024-04-09 02:56:00 2024-04-09 02:58:22 2 2 2
2 .0 0% | SQL_EXECUTION SEQUENCE_LOAD 132096 2024-04-09 11:21:44 2024-04-09 15:10:44 2 2 2
1 .0 0% | PARSE SQL_EXECUTION SEQUENCE_LOAD 132112 2024-04-09 08:41:50 2024-04-09 08:41:50 1 1 1
30 rows selected.
--//CONNECTION_MGMT表示怎么,不是很理解,看看涉及的sql语句.
SYS@127.0.0.1:9014/ywdb> @ ashtop event,sql_id "TIME_MODEL_NAME like '%CONNECTION_MGMT%'" &day
Total Distinct Distinct Distinct
Seconds AAS %This EVENT SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------- ------------------- ------------------- ---------- -------- -----------
519 .0 17% | ck3nrshb15tb4 2024-04-08 15:35:47 2024-04-09 15:30:23 490 503 493
423 .0 14% | 284xbhpcdj6qa 2024-04-08 15:37:09 2024-04-09 15:29:57 382 403 393
334 .0 11% | 2024-04-08 15:36:48 2024-04-09 15:34:55 3 319 319
192 .0 6% | control file sequential read 1ur5t42rf0u31 2024-04-08 15:46:19 2024-04-09 15:30:00 192 192 192
158 .0 5% | 1ur5t42rf0u31 2024-04-08 15:38:52 2024-04-09 15:31:07 131 136 145
150 .0 5% | Disk file operations I/O 1ur5t42rf0u31 2024-04-08 15:39:26 2024-04-09 15:29:11 150 150 150
127 .0 4% | null event 2024-04-08 15:38:06 2024-04-09 15:11:19 1 127 127
119 .0 4% | ava733f5wb3d0 2024-04-08 15:41:59 2024-04-09 15:34:53 103 119 119
79 .0 3% | 9516qxyvhpxqu 2024-04-08 15:36:06 2024-04-09 15:29:02 74 79 79
72 .0 2% | ba8g9u1zbjb4b 2024-04-08 15:48:38 2024-04-09 15:24:46 64 70 67
68 .0 2% | 05uqdabhzncdc 2024-04-08 15:41:24 2024-04-09 15:02:24 49 67 68
53 .0 2% | 9fnk61d6zcjn4 2024-04-08 16:03:41 2024-04-09 15:19:02 47 53 53
45 .0 1% | 0ws7ahf1d78qa 2024-04-08 16:03:01 2024-04-09 15:19:27 28 42 44
45 .0 1% | 55nw5c8cbw4jy 2024-04-08 15:38:47 2024-04-09 15:32:00 43 45 45
44 .0 1% | dx3hwmu87p6xr 2024-04-08 16:05:00 2024-04-09 15:06:49 34 44 44
39 .0 1% | cna1abuq78rgp 2024-04-08 15:45:39 2024-04-09 15:11:01 20 38 38
32 .0 1% | cfgm0uwrb1h6f 2024-04-08 15:43:09 2024-04-09 15:06:20 20 32 32
30 .0 1% | b5j7y2r5zr37t 2024-04-08 16:06:48 2024-04-09 15:29:54 20 30 30
30 .0 1% | b9mxb1kghymbd 2024-04-08 15:40:25 2024-04-09 15:30:33 18 30 30
29 .0 1% | cursor: pin S d07m1n4vj9x3j 2024-04-08 16:38:52 2024-04-08 16:39:04 1 11 11
29 .0 1% | 459f3z9u4fb3u 2024-04-08 15:59:20 2024-04-09 15:27:30 17 29 29
26 .0 1% | library cache: mutex X 2024-04-08 16:38:39 2024-04-08 16:39:05 1 9 9
24 .0 1% | g7yzjptxk6z2k 2024-04-08 16:02:16 2024-04-09 15:32:05 17 24 22
20 .0 1% | 015zuc7252hrq 2024-04-08 16:38:49 2024-04-09 13:30:08 12 20 20
20 .0 1% | 0j233gt0r70df 2024-04-08 15:55:24 2024-04-09 15:20:25 19 20 20
20 .0 1% | 9ahamm7dx5waa 2024-04-08 16:36:16 2024-04-09 11:46:01 9 20 20
20 .0 1% | attd5u7hsmn6h 2024-04-08 15:59:14 2024-04-09 14:19:09 17 20 20
19 .0 1% | fmr02svdc8wwc 2024-04-08 16:17:45 2024-04-09 15:34:45 13 19 19
19 .0 1% | gwf89gra9cpbz 2024-04-08 15:58:11 2024-04-09 13:05:58 19 19 19
18 .0 1% | 13mr23q7dtfpp 2024-04-08 16:19:11 2024-04-09 15:15:14 13 18 18
30 rows selected.
--//看看涉及的sql语句.
SYS@127.0.0.1:9014/ywdb> @ sql_id ck3nrshb15tb4
--SQL_ID = ck3nrshb15tb4
begin tlogon.logon; end;;
--//防水墙东西.用户登录必须执行的命令.
SYS@127.0.0.1:9014/ywdb> @ sql_id 284xbhpcdj6qa
--SQL_ID = 284xbhpcdj6qa
SELECT 1 FROM SYS.DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'DBA' AND GRANTEE = :B1 ;
SYS@127.0.0.1:9014/ywdb> @ sql_id 1ur5t42rf0u31
--SQL_ID = 1ur5t42rf0u31
SELECT COUNT(*) FROM V$DATABASE T WHERE T.DATABASE_ROLE = 'PRIMARY';
SYS@127.0.0.1:9014/ywdb> @ sql_id ava733f5wb3d0
--SQL_ID = ava733f5wb3d0
SELECT ASSET_ID
, OBJ_OWNER || '.' || OBJ_NAME OBJNAME
, CMDTYPE
, RULE_NAME
, AUDIT_LEVEL
, ACTION_LEVEL
, RULE_VALUE
FROM MC$RULE_ASSET_TAB_PRIVRULE
WHERE (FTYPE, FVALUE) IN (('APPNAME', :B6 ), ('IPADDR', :B5 ), ('HOST', :B4 ), ('MACADDR', :B3 ), ('DBUSER', :B2 ), ('EUSER', TO_CHAR(:B1 )), ('PUBLIC', '00'),
('NOREGISTER', '00'))
ORDER BY ASSET_ID
, OBJNAME
, CMDTYPE;
--//视乎都是登录时执行相关sql命令.估计看到大部分的执行来源tlogon.logon,已经加密看不到这部分源代码.
SYS@127.0.0.1:9014/ywdb> column text format a100
SYS@127.0.0.1:9014/ywdb> select * from DBA_SOURCE where upper(text) like '%RULE_ASSET_TAB_PRIVRULE%';
OWNER NAME TYPE LINE TEXT
--------- --------------------------- ------- ---- -------------------------------------------------------------------------
HZMCASSET TRG$RULE_ASSET_TAB_PRIVRULE TRIGGER 2 before delete or update or insert on MC$RULE_ASSET_TAB_PRIVRULE
HZMCASSET TRG$RULE_ASSET_TAB_PRIVRULE TRIGGER 1 trigger trg$RULE_ASSET_TAB_PRIVRULE
SYS@127.0.0.1:9014/ywdb> @ ashtop program1 sql_id='ck3nrshb15tb4' &day
Total Distinct Distinct Distinct
Seconds AAS %This PROGRAM1 FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------------ ------------------- ------------------- ---------- -------- -----------
254 .0 46% | python.exe 2024-04-08 15:40:26 2024-04-09 15:36:21 232 248 232
224 .0 41% | portal.exe 2024-04-08 15:41:53 2024-04-09 15:40:13 200 209 201
39 .0 7% | w3wp.exe 2024-04-08 16:08:49 2024-04-09 13:45:48 39 39 39
14 .0 3% | httpd.exe 2024-04-08 16:29:36 2024-04-09 15:19:54 14 14 14
9 .0 2% | jdbc thin client 2024-04-09 02:16:54 2024-04-09 13:09:41 9 9 9
3 .0 1% | oracle@dm01dbadm01.gxhospital.com (tns v1-v3) 2024-04-08 17:07:23 2024-04-09 15:29:33 3 3 3
2 .0 0% | histolyupload_ypyfdj_zy.exe 2024-04-08 16:27:55 2024-04-09 03:52:57 2 2 2
2 .0 0% | oracle@fyhis2 (j000) 2024-04-08 15:41:41 2024-04-09 07:51:30 2 2 2
2 .0 0% | sqlplus@fyhis1 (tns v1-v3) 2024-04-08 18:24:24 2024-04-09 04:20:20 2 2 2
1 .0 0% | sqlplus@gxqyydg4 (tns v1-v3) 2024-04-09 10:43:23 2024-04-09 10:43:23 1 1 1
10 rows selected.
SYS@127.0.0.1:9014/ywdb> @ ashtop program1 sql_id='1ur5t42rf0u31' &day
Total Distinct Distinct Distinct
Seconds AAS %This PROGRAM1 FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------------ ------------------- ------------------- ---------- -------- -----------
250 .0 49% | python.exe 2024-04-08 15:41:01 2024-04-09 15:37:38 239 246 247
186 .0 37% | portal.exe 2024-04-08 15:46:43 2024-04-09 15:24:05 171 178 177
45 .0 9% | w3wp.exe 2024-04-08 15:59:56 2024-04-09 15:10:45 45 44 45
14 .0 3% | httpd.exe 2024-04-08 16:38:30 2024-04-09 14:09:50 12 12 12
5 .0 1% | jdbc thin client 2024-04-09 02:58:53 2024-04-09 15:38:13 5 5 5
3 .0 1% | oracle@dm01dbadm01.gxhospital.com (tns v1-v3) 2024-04-09 08:31:54 2024-04-09 10:25:09 3 3 3
2 .0 0% | oracle@fyhis2 (j000) 2024-04-08 19:49:46 2024-04-09 13:57:25 2 2 2
1 .0 0% | histolyupload_ypyfdj.exe 2024-04-09 10:20:48 2024-04-09 10:20:48 1 1 1
8 rows selected.
--//想起链接的测试:
--//https://blog.itpub.net/267265/viewspace-2766041/=>[20210401]跟踪sqlplus登录执行了什么.txt
SYS@127.0.0.1:9014/ywdb> @ ashtop time_model_name "sql_id in ('cm5vu20fhtnq1','m5vu20fhtnq1','3nkd3g3ju5ph1','459f3z9u4fb3u','0ws7ahf1d78qa','5ur69atw3vfhj','0k8522rmdzg4k')" &day
Total Distinct Distinct Distinct
Seconds AAS %This TIME_MODEL_NAME FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- -------------------------------------------------- ------------------- ------------------- ---------- -------- -----------
59 .0 55% | CONNECTION_MGMT SQL_EXECUTION 2024-04-08 15:59:20 2024-04-09 15:19:27 47 56 58
31 .0 29% | CONNECTION_MGMT PARSE 2024-04-08 16:10:32 2024-04-09 15:27:30 1 31 31
9 .0 8% | CONNECTION_MGMT 2024-04-08 16:03:01 2024-04-09 15:43:54 6 9 9
3 .0 3% | 2024-04-08 19:56:50 2024-04-09 09:32:16 3 3 3
2 .0 2% | CONNECTION_MGMT BIND 2024-04-08 18:39:57 2024-04-09 11:04:44 1 2 2
1 .0 1% | CONNECTION_MGMT CURSOR_CLOSE 2024-04-08 16:04:14 2024-04-08 16:04:14 1 1 1
1 .0 1% | PARSE 2024-04-08 23:13:12 2024-04-08 23:13:12 1 1 1
1 .0 1% | SQL_EXECUTION 2024-04-09 14:07:25 2024-04-09 14:07:25 1 1 1
8 rows selected.
--//看看生产系统exadata的情况:
> @ dashtop time_model_name,time_model 1=1 &day
Total
Seconds AAS %This TIME_MODEL_NAME TIME_MODEL FIRST_SEEN LAST_SEEN
--------- ------- ------- -------------------------------------------------- ---------- ------------------- -------------------
385170 4.5 68% SQL_EXECUTION 1024 2024-04-08 15:56:37 2024-04-09 15:00:14
89740 1.0 16% 0 2024-04-08 15:56:37 2024-04-09 15:00:14
78730 .9 14% CONNECTION_MGMT SQL_EXECUTION 1032 2024-04-08 15:56:38 2024-04-09 15:00:14
6780 .1 1% PARSE 16 2024-04-08 15:57:19 2024-04-09 15:00:14
1740 .0 0% CONNECTION_MGMT 8 2024-04-08 16:07:08 2024-04-09 14:46:38
1700 .0 0% CURSOR_CLOSE 65536 2024-04-08 15:56:58 2024-04-09 14:49:13
1100 .0 0% SQL_EXECUTION PLSQL_EXECUTION 3072 2024-04-08 16:04:13 2024-04-09 14:48:59
870 .0 0% PARSE HARD_PARSE SQL_EXECUTION 1168 2024-04-08 16:13:00 2024-04-09 14:25:37
580 .0 0% CONNECTION_MGMT SQL_EXECUTION PLSQL_EXECUTION 3080 2024-04-08 16:26:58 2024-04-09 14:49:03
480 .0 0% BIND 32768 2024-04-08 16:04:23 2024-04-09 14:56:17
450 .0 0% PARSE BIND 32784 2024-04-08 15:58:21 2024-04-09 14:49:44
310 .0 0% PARSE HARD_PARSE 144 2024-04-08 16:17:29 2024-04-09 14:05:49
270 .0 0% PARSE SQL_EXECUTION 1040 2024-04-08 16:47:39 2024-04-09 14:45:57
250 .0 0% PARSE HARD_PARSE SQL_EXECUTION PLSQL_COMPILATION 9360 2024-04-08 16:26:37 2024-04-09 11:46:05
230 .0 0% CONNECTION_MGMT PARSE 24 2024-04-08 16:08:31 2024-04-09 14:45:05
230 .0 0% CONNECTION_MGMT PARSE SQL_EXECUTION 1048 2024-04-08 16:04:02 2024-04-09 12:38:58
80 .0 0% CONNECTION_MGMT PARSE HARD_PARSE SQL_EXECUTION 9368 2024-04-08 16:35:24 2024-04-09 05:01:27
PLSQL_COMPILATION
40 .0 0% CONNECTION_MGMT PARSE HARD_PARSE SQL_EXECUTION 1176 2024-04-08 23:02:58 2024-04-09 11:52:27
30 .0 0% CONNECTION_MGMT CURSOR_CLOSE 65544 2024-04-08 16:39:54 2024-04-09 14:02:01
30 .0 0% SQL_EXECUTION PLSQL_EXECUTION CURSOR_CLOSE 68608 2024-04-08 17:17:18 2024-04-09 10:52:07
20 .0 0% CONNECTION_MGMT BIND 32776 2024-04-08 18:47:11 2024-04-09 12:26:03
20 .0 0% CONNECTION_MGMT SQL_EXECUTION PLSQL_EXECUTION 68616 2024-04-09 09:16:28 2024-04-09 10:26:36
CURSOR_CLOSE
20 .0 0% SQL_EXECUTION SEQUENCE_LOAD 132096 2024-04-08 17:01:47 2024-04-09 10:21:26
10 .0 0% CONNECTION_MGMT SQL_EXECUTION BIND 33800 2024-04-08 18:18:27 2024-04-08 18:18:27
10 .0 0% SQL_EXECUTION PLSQL_RPC 5120 2024-04-08 17:00:35 2024-04-08 17:00:35
25 rows selected.
--//可以看出所谓的防水墙占用的消耗,CPU消耗占比14%.顺便说一下必须使用dashtop,不然无法查询一天的情况.
> @ dashtop event,sql_id "TIME_MODEL_NAME like '%CONNECTION_MGMT%'" &day
Total
Seconds AAS %This EVENT SQL_ID FIRST_SEEN LAST_SEEN
--------- ------- ------- ------------------------------------------ ------------- ------------------- -------------------
75670 .9 93% 8vmu6k690g87k 2024-04-08 16:00:15 2024-04-09 15:00:14
1170 .0 1% 2024-04-08 16:07:08 2024-04-09 14:45:05
800 .0 1% null event 2024-04-08 16:11:27 2024-04-09 14:46:38
470 .0 1% baqaqddvtt1wq 2024-04-08 17:25:55 2024-04-09 14:23:33
....
30 .0 0% 09s66z1atzwzh 2024-04-08 23:58:29 2024-04-09 09:55:56
30 .0 0% 4psdjxwgtmga2 2024-04-09 00:07:05 2024-04-09 09:46:07
30 rows selected.
> @ sql_id 8vmu6k690g87k
--SQL_ID = 8vmu6k690g87k
SELECT UPPER (NVL (PROGRAM, 'null'))
,UPPER (MODULE)
,TYPE
,DECODE
(
NVL (INSTR (PROCESS, ':'), 0)
,0, NVL (PROCESS, 1234)
,SUBSTR (PROCESS, 1, INSTR (PROCESS, ':') - 1)
)
,OSUSER
,MACHINE
,SCHEMANAME
,USERNAME
,SERVICE_NAME
,SID
,SERIAL#
FROM SYS.V_$SESSION
WHERE SID = SYS_CONTEXT ('userenv', 'sid');
--//做了格式化处理.这条语句为什么消耗这么多资源,我已经在链接:
--//https://blog.itpub.net/267265/viewspace-2927994/ =>[20221130]测试访问视图v$session几种情况的性能差异.txt
--//https://blog.itpub.net/267265/viewspace-2926302/ =>[20221128]再谈防水墙(视图访问性能问题).txt
> @ ashtop time_model_name sql_id='8vmu6k690g87k' &day
Total Distinct Distinct Distinct
Seconds AAS %This TIME_MODEL_NAME FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- -------------------------------------------------- ------------------- ------------------- ---------- -------- -----------
59413 .7 100% | CONNECTION_MGMT SQL_EXECUTION 2024-04-08 16:13:39 2024-04-09 16:13:06 59351 33871 59364
45 .0 0% | SQL_EXECUTION 2024-04-08 17:11:02 2024-04-09 15:30:39 45 44 45
30 .0 0% | CONNECTION_MGMT PARSE SQL_EXECUTION 2024-04-08 16:17:00 2024-04-09 15:44:37 1 30 30
1 .0 0% | CONNECTION_MGMT SQL_EXECUTION PLSQL_EXECUTION 2024-04-09 15:32:23 2024-04-09 15:32:23 1 1 1
3.总结:
--//到此应该明白TIME_MODEL_NAME=CONNECTION_MGMT,应该是登录时执行的连接管理命令.
--//MGMT应该表示management .
--//上午看了ashtop的time_model_name的输出,对于CONNECTION_MGMT表示怎么不是很清楚,探究看看.
1.环境:
SYS@127.0.0.1:9014/ywdb> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.分析:
--//一天之内,数据库CPU消耗分布情况.
SYS@127.0.0.1:9014/ywdb> @ ashtop time_model_name,time_model 1=1 &day
Total Distinct Distinct Distinct
Seconds AAS %This TIME_MODEL_NAME TIME_MODEL FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- -------------------------------------------------- ---------- ------------------- ------------------- ---------- -------- -----------
27086 .3 42% | SQL_EXECUTION 1024 2024-04-08 15:31:15 2024-04-09 15:31:07 20129 24365 20945
25275 .3 39% | 0 2024-04-08 15:31:28 2024-04-09 15:31:04 1223 15932 16026
5033 .1 8% | PARSE HARD_PARSE 144 2024-04-08 15:31:13 2024-04-09 15:31:08 1 4834 4834
1662 .0 3% | CONNECTION_MGMT SQL_EXECUTION 1032 2024-04-08 15:31:28 2024-04-09 15:31:07 1441 1556 1609
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1622 .0 3% | PARSE 16 2024-04-08 15:32:05 2024-04-09 15:29:50 2 1527 1527
959 .0 1% | PARSE HARD_PARSE SQL_EXECUTION 1168 2024-04-08 15:31:57 2024-04-09 15:30:38 4 912 911
726 .0 1% | SQL_EXECUTION PLSQL_EXECUTION 3072 2024-04-08 15:33:05 2024-04-09 15:30:52 687 702 718
628 .0 1% | CONNECTION_MGMT SQL_EXECUTION PLSQL_EXECUTION 3080 2024-04-08 15:31:54 2024-04-09 15:30:23 578 585 578
432 .0 1% | CONNECTION_MGMT 8 2024-04-08 15:33:16 2024-04-09 15:30:56 13 375 375
317 .0 0% | CURSOR_CLOSE 65536 2024-04-08 15:35:08 2024-04-09 15:27:07 1 315 315
295 .0 0% | CONNECTION_MGMT PARSE SQL_EXECUTION 1048 2024-04-08 15:32:33 2024-04-09 15:29:10 75 261 270
162 .0 0% | PARSE SQL_EXECUTION 1040 2024-04-08 15:53:52 2024-04-09 14:57:28 8 88 89
159 .0 0% | PARSE HARD_PARSE PLSQL_COMPILATION 8336 2024-04-08 15:42:43 2024-04-09 15:24:20 1 155 155
132 .0 0% | PARSE BIND 32784 2024-04-08 15:46:33 2024-04-09 15:28:45 1 132 132
125 .0 0% | BIND 32768 2024-04-08 15:32:16 2024-04-09 15:22:44 1 125 125
103 .0 0% | PARSE HARD_PARSE SQL_EXECUTION PLSQL_COMPILATION 9360 2024-04-08 15:44:28 2024-04-09 15:11:14 2 103 103
84 .0 0% | CONNECTION_MGMT PARSE 24 2024-04-08 16:02:31 2024-04-09 15:27:30 2 83 83
22 .0 0% | PARSE HARD_PARSE BIND 32912 2024-04-08 16:36:13 2024-04-09 14:30:52 1 22 22
11 .0 0% | PARSE HARD_PARSE CURSOR_CLOSE 65680 2024-04-08 18:00:29 2024-04-09 15:30:35 1 11 11
7 .0 0% | CONNECTION_MGMT CURSOR_CLOSE 65544 2024-04-08 16:04:14 2024-04-09 12:18:29 1 7 7
5 .0 0% | CONNECTION_MGMT BIND 32776 2024-04-08 16:38:42 2024-04-09 11:04:44 1 5 5
5 .0 0% | CONNECTION_MGMT SQL_EXECUTION BIND 33800 2024-04-09 03:45:37 2024-04-09 15:11:29 1 5 5
4 .0 0% | SQL_EXECUTION PLSQL_EXECUTION CURSOR_CLOSE 68608 2024-04-08 15:47:01 2024-04-08 20:01:46 2 4 4
3 .0 0% | CONNECTION_MGMT SQL_EXECUTION PLSQL_EXECUTION 68616 2024-04-09 01:49:52 2024-04-09 09:39:21 3 3 3
CURSOR_CLOSE
2 .0 0% | PARSE HARD_PARSE SQL_EXECUTION PLSQL_RPC 5264 2024-04-09 02:56:01 2024-04-09 02:58:18 1 2 2
2 .0 0% | PARSE SQL_EXECUTION BIND 33808 2024-04-09 10:09:18 2024-04-09 12:54:03 1 2 2
2 .0 0% | SQL_EXECUTION BIND 33792 2024-04-09 09:51:40 2024-04-09 10:43:47 1 2 2
2 .0 0% | SQL_EXECUTION PLSQL_RPC 5120 2024-04-09 02:56:00 2024-04-09 02:58:22 2 2 2
2 .0 0% | SQL_EXECUTION SEQUENCE_LOAD 132096 2024-04-09 11:21:44 2024-04-09 15:10:44 2 2 2
1 .0 0% | PARSE SQL_EXECUTION SEQUENCE_LOAD 132112 2024-04-09 08:41:50 2024-04-09 08:41:50 1 1 1
30 rows selected.
--//CONNECTION_MGMT表示怎么,不是很理解,看看涉及的sql语句.
SYS@127.0.0.1:9014/ywdb> @ ashtop event,sql_id "TIME_MODEL_NAME like '%CONNECTION_MGMT%'" &day
Total Distinct Distinct Distinct
Seconds AAS %This EVENT SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------- ------------------- ------------------- ---------- -------- -----------
519 .0 17% | ck3nrshb15tb4 2024-04-08 15:35:47 2024-04-09 15:30:23 490 503 493
423 .0 14% | 284xbhpcdj6qa 2024-04-08 15:37:09 2024-04-09 15:29:57 382 403 393
334 .0 11% | 2024-04-08 15:36:48 2024-04-09 15:34:55 3 319 319
192 .0 6% | control file sequential read 1ur5t42rf0u31 2024-04-08 15:46:19 2024-04-09 15:30:00 192 192 192
158 .0 5% | 1ur5t42rf0u31 2024-04-08 15:38:52 2024-04-09 15:31:07 131 136 145
150 .0 5% | Disk file operations I/O 1ur5t42rf0u31 2024-04-08 15:39:26 2024-04-09 15:29:11 150 150 150
127 .0 4% | null event 2024-04-08 15:38:06 2024-04-09 15:11:19 1 127 127
119 .0 4% | ava733f5wb3d0 2024-04-08 15:41:59 2024-04-09 15:34:53 103 119 119
79 .0 3% | 9516qxyvhpxqu 2024-04-08 15:36:06 2024-04-09 15:29:02 74 79 79
72 .0 2% | ba8g9u1zbjb4b 2024-04-08 15:48:38 2024-04-09 15:24:46 64 70 67
68 .0 2% | 05uqdabhzncdc 2024-04-08 15:41:24 2024-04-09 15:02:24 49 67 68
53 .0 2% | 9fnk61d6zcjn4 2024-04-08 16:03:41 2024-04-09 15:19:02 47 53 53
45 .0 1% | 0ws7ahf1d78qa 2024-04-08 16:03:01 2024-04-09 15:19:27 28 42 44
45 .0 1% | 55nw5c8cbw4jy 2024-04-08 15:38:47 2024-04-09 15:32:00 43 45 45
44 .0 1% | dx3hwmu87p6xr 2024-04-08 16:05:00 2024-04-09 15:06:49 34 44 44
39 .0 1% | cna1abuq78rgp 2024-04-08 15:45:39 2024-04-09 15:11:01 20 38 38
32 .0 1% | cfgm0uwrb1h6f 2024-04-08 15:43:09 2024-04-09 15:06:20 20 32 32
30 .0 1% | b5j7y2r5zr37t 2024-04-08 16:06:48 2024-04-09 15:29:54 20 30 30
30 .0 1% | b9mxb1kghymbd 2024-04-08 15:40:25 2024-04-09 15:30:33 18 30 30
29 .0 1% | cursor: pin S d07m1n4vj9x3j 2024-04-08 16:38:52 2024-04-08 16:39:04 1 11 11
29 .0 1% | 459f3z9u4fb3u 2024-04-08 15:59:20 2024-04-09 15:27:30 17 29 29
26 .0 1% | library cache: mutex X 2024-04-08 16:38:39 2024-04-08 16:39:05 1 9 9
24 .0 1% | g7yzjptxk6z2k 2024-04-08 16:02:16 2024-04-09 15:32:05 17 24 22
20 .0 1% | 015zuc7252hrq 2024-04-08 16:38:49 2024-04-09 13:30:08 12 20 20
20 .0 1% | 0j233gt0r70df 2024-04-08 15:55:24 2024-04-09 15:20:25 19 20 20
20 .0 1% | 9ahamm7dx5waa 2024-04-08 16:36:16 2024-04-09 11:46:01 9 20 20
20 .0 1% | attd5u7hsmn6h 2024-04-08 15:59:14 2024-04-09 14:19:09 17 20 20
19 .0 1% | fmr02svdc8wwc 2024-04-08 16:17:45 2024-04-09 15:34:45 13 19 19
19 .0 1% | gwf89gra9cpbz 2024-04-08 15:58:11 2024-04-09 13:05:58 19 19 19
18 .0 1% | 13mr23q7dtfpp 2024-04-08 16:19:11 2024-04-09 15:15:14 13 18 18
30 rows selected.
--//看看涉及的sql语句.
SYS@127.0.0.1:9014/ywdb> @ sql_id ck3nrshb15tb4
--SQL_ID = ck3nrshb15tb4
begin tlogon.logon; end;;
--//防水墙东西.用户登录必须执行的命令.
SYS@127.0.0.1:9014/ywdb> @ sql_id 284xbhpcdj6qa
--SQL_ID = 284xbhpcdj6qa
SELECT 1 FROM SYS.DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'DBA' AND GRANTEE = :B1 ;
SYS@127.0.0.1:9014/ywdb> @ sql_id 1ur5t42rf0u31
--SQL_ID = 1ur5t42rf0u31
SELECT COUNT(*) FROM V$DATABASE T WHERE T.DATABASE_ROLE = 'PRIMARY';
SYS@127.0.0.1:9014/ywdb> @ sql_id ava733f5wb3d0
--SQL_ID = ava733f5wb3d0
SELECT ASSET_ID
, OBJ_OWNER || '.' || OBJ_NAME OBJNAME
, CMDTYPE
, RULE_NAME
, AUDIT_LEVEL
, ACTION_LEVEL
, RULE_VALUE
FROM MC$RULE_ASSET_TAB_PRIVRULE
WHERE (FTYPE, FVALUE) IN (('APPNAME', :B6 ), ('IPADDR', :B5 ), ('HOST', :B4 ), ('MACADDR', :B3 ), ('DBUSER', :B2 ), ('EUSER', TO_CHAR(:B1 )), ('PUBLIC', '00'),
('NOREGISTER', '00'))
ORDER BY ASSET_ID
, OBJNAME
, CMDTYPE;
--//视乎都是登录时执行相关sql命令.估计看到大部分的执行来源tlogon.logon,已经加密看不到这部分源代码.
SYS@127.0.0.1:9014/ywdb> column text format a100
SYS@127.0.0.1:9014/ywdb> select * from DBA_SOURCE where upper(text) like '%RULE_ASSET_TAB_PRIVRULE%';
OWNER NAME TYPE LINE TEXT
--------- --------------------------- ------- ---- -------------------------------------------------------------------------
HZMCASSET TRG$RULE_ASSET_TAB_PRIVRULE TRIGGER 2 before delete or update or insert on MC$RULE_ASSET_TAB_PRIVRULE
HZMCASSET TRG$RULE_ASSET_TAB_PRIVRULE TRIGGER 1 trigger trg$RULE_ASSET_TAB_PRIVRULE
SYS@127.0.0.1:9014/ywdb> @ ashtop program1 sql_id='ck3nrshb15tb4' &day
Total Distinct Distinct Distinct
Seconds AAS %This PROGRAM1 FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------------ ------------------- ------------------- ---------- -------- -----------
254 .0 46% | python.exe 2024-04-08 15:40:26 2024-04-09 15:36:21 232 248 232
224 .0 41% | portal.exe 2024-04-08 15:41:53 2024-04-09 15:40:13 200 209 201
39 .0 7% | w3wp.exe 2024-04-08 16:08:49 2024-04-09 13:45:48 39 39 39
14 .0 3% | httpd.exe 2024-04-08 16:29:36 2024-04-09 15:19:54 14 14 14
9 .0 2% | jdbc thin client 2024-04-09 02:16:54 2024-04-09 13:09:41 9 9 9
3 .0 1% | oracle@dm01dbadm01.gxhospital.com (tns v1-v3) 2024-04-08 17:07:23 2024-04-09 15:29:33 3 3 3
2 .0 0% | histolyupload_ypyfdj_zy.exe 2024-04-08 16:27:55 2024-04-09 03:52:57 2 2 2
2 .0 0% | oracle@fyhis2 (j000) 2024-04-08 15:41:41 2024-04-09 07:51:30 2 2 2
2 .0 0% | sqlplus@fyhis1 (tns v1-v3) 2024-04-08 18:24:24 2024-04-09 04:20:20 2 2 2
1 .0 0% | sqlplus@gxqyydg4 (tns v1-v3) 2024-04-09 10:43:23 2024-04-09 10:43:23 1 1 1
10 rows selected.
SYS@127.0.0.1:9014/ywdb> @ ashtop program1 sql_id='1ur5t42rf0u31' &day
Total Distinct Distinct Distinct
Seconds AAS %This PROGRAM1 FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------------ ------------------- ------------------- ---------- -------- -----------
250 .0 49% | python.exe 2024-04-08 15:41:01 2024-04-09 15:37:38 239 246 247
186 .0 37% | portal.exe 2024-04-08 15:46:43 2024-04-09 15:24:05 171 178 177
45 .0 9% | w3wp.exe 2024-04-08 15:59:56 2024-04-09 15:10:45 45 44 45
14 .0 3% | httpd.exe 2024-04-08 16:38:30 2024-04-09 14:09:50 12 12 12
5 .0 1% | jdbc thin client 2024-04-09 02:58:53 2024-04-09 15:38:13 5 5 5
3 .0 1% | oracle@dm01dbadm01.gxhospital.com (tns v1-v3) 2024-04-09 08:31:54 2024-04-09 10:25:09 3 3 3
2 .0 0% | oracle@fyhis2 (j000) 2024-04-08 19:49:46 2024-04-09 13:57:25 2 2 2
1 .0 0% | histolyupload_ypyfdj.exe 2024-04-09 10:20:48 2024-04-09 10:20:48 1 1 1
8 rows selected.
--//想起链接的测试:
--//https://blog.itpub.net/267265/viewspace-2766041/=>[20210401]跟踪sqlplus登录执行了什么.txt
SYS@127.0.0.1:9014/ywdb> @ ashtop time_model_name "sql_id in ('cm5vu20fhtnq1','m5vu20fhtnq1','3nkd3g3ju5ph1','459f3z9u4fb3u','0ws7ahf1d78qa','5ur69atw3vfhj','0k8522rmdzg4k')" &day
Total Distinct Distinct Distinct
Seconds AAS %This TIME_MODEL_NAME FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- -------------------------------------------------- ------------------- ------------------- ---------- -------- -----------
59 .0 55% | CONNECTION_MGMT SQL_EXECUTION 2024-04-08 15:59:20 2024-04-09 15:19:27 47 56 58
31 .0 29% | CONNECTION_MGMT PARSE 2024-04-08 16:10:32 2024-04-09 15:27:30 1 31 31
9 .0 8% | CONNECTION_MGMT 2024-04-08 16:03:01 2024-04-09 15:43:54 6 9 9
3 .0 3% | 2024-04-08 19:56:50 2024-04-09 09:32:16 3 3 3
2 .0 2% | CONNECTION_MGMT BIND 2024-04-08 18:39:57 2024-04-09 11:04:44 1 2 2
1 .0 1% | CONNECTION_MGMT CURSOR_CLOSE 2024-04-08 16:04:14 2024-04-08 16:04:14 1 1 1
1 .0 1% | PARSE 2024-04-08 23:13:12 2024-04-08 23:13:12 1 1 1
1 .0 1% | SQL_EXECUTION 2024-04-09 14:07:25 2024-04-09 14:07:25 1 1 1
8 rows selected.
--//看看生产系统exadata的情况:
> @ dashtop time_model_name,time_model 1=1 &day
Total
Seconds AAS %This TIME_MODEL_NAME TIME_MODEL FIRST_SEEN LAST_SEEN
--------- ------- ------- -------------------------------------------------- ---------- ------------------- -------------------
385170 4.5 68% SQL_EXECUTION 1024 2024-04-08 15:56:37 2024-04-09 15:00:14
89740 1.0 16% 0 2024-04-08 15:56:37 2024-04-09 15:00:14
78730 .9 14% CONNECTION_MGMT SQL_EXECUTION 1032 2024-04-08 15:56:38 2024-04-09 15:00:14
6780 .1 1% PARSE 16 2024-04-08 15:57:19 2024-04-09 15:00:14
1740 .0 0% CONNECTION_MGMT 8 2024-04-08 16:07:08 2024-04-09 14:46:38
1700 .0 0% CURSOR_CLOSE 65536 2024-04-08 15:56:58 2024-04-09 14:49:13
1100 .0 0% SQL_EXECUTION PLSQL_EXECUTION 3072 2024-04-08 16:04:13 2024-04-09 14:48:59
870 .0 0% PARSE HARD_PARSE SQL_EXECUTION 1168 2024-04-08 16:13:00 2024-04-09 14:25:37
580 .0 0% CONNECTION_MGMT SQL_EXECUTION PLSQL_EXECUTION 3080 2024-04-08 16:26:58 2024-04-09 14:49:03
480 .0 0% BIND 32768 2024-04-08 16:04:23 2024-04-09 14:56:17
450 .0 0% PARSE BIND 32784 2024-04-08 15:58:21 2024-04-09 14:49:44
310 .0 0% PARSE HARD_PARSE 144 2024-04-08 16:17:29 2024-04-09 14:05:49
270 .0 0% PARSE SQL_EXECUTION 1040 2024-04-08 16:47:39 2024-04-09 14:45:57
250 .0 0% PARSE HARD_PARSE SQL_EXECUTION PLSQL_COMPILATION 9360 2024-04-08 16:26:37 2024-04-09 11:46:05
230 .0 0% CONNECTION_MGMT PARSE 24 2024-04-08 16:08:31 2024-04-09 14:45:05
230 .0 0% CONNECTION_MGMT PARSE SQL_EXECUTION 1048 2024-04-08 16:04:02 2024-04-09 12:38:58
80 .0 0% CONNECTION_MGMT PARSE HARD_PARSE SQL_EXECUTION 9368 2024-04-08 16:35:24 2024-04-09 05:01:27
PLSQL_COMPILATION
40 .0 0% CONNECTION_MGMT PARSE HARD_PARSE SQL_EXECUTION 1176 2024-04-08 23:02:58 2024-04-09 11:52:27
30 .0 0% CONNECTION_MGMT CURSOR_CLOSE 65544 2024-04-08 16:39:54 2024-04-09 14:02:01
30 .0 0% SQL_EXECUTION PLSQL_EXECUTION CURSOR_CLOSE 68608 2024-04-08 17:17:18 2024-04-09 10:52:07
20 .0 0% CONNECTION_MGMT BIND 32776 2024-04-08 18:47:11 2024-04-09 12:26:03
20 .0 0% CONNECTION_MGMT SQL_EXECUTION PLSQL_EXECUTION 68616 2024-04-09 09:16:28 2024-04-09 10:26:36
CURSOR_CLOSE
20 .0 0% SQL_EXECUTION SEQUENCE_LOAD 132096 2024-04-08 17:01:47 2024-04-09 10:21:26
10 .0 0% CONNECTION_MGMT SQL_EXECUTION BIND 33800 2024-04-08 18:18:27 2024-04-08 18:18:27
10 .0 0% SQL_EXECUTION PLSQL_RPC 5120 2024-04-08 17:00:35 2024-04-08 17:00:35
25 rows selected.
--//可以看出所谓的防水墙占用的消耗,CPU消耗占比14%.顺便说一下必须使用dashtop,不然无法查询一天的情况.
> @ dashtop event,sql_id "TIME_MODEL_NAME like '%CONNECTION_MGMT%'" &day
Total
Seconds AAS %This EVENT SQL_ID FIRST_SEEN LAST_SEEN
--------- ------- ------- ------------------------------------------ ------------- ------------------- -------------------
75670 .9 93% 8vmu6k690g87k 2024-04-08 16:00:15 2024-04-09 15:00:14
1170 .0 1% 2024-04-08 16:07:08 2024-04-09 14:45:05
800 .0 1% null event 2024-04-08 16:11:27 2024-04-09 14:46:38
470 .0 1% baqaqddvtt1wq 2024-04-08 17:25:55 2024-04-09 14:23:33
....
30 .0 0% 09s66z1atzwzh 2024-04-08 23:58:29 2024-04-09 09:55:56
30 .0 0% 4psdjxwgtmga2 2024-04-09 00:07:05 2024-04-09 09:46:07
30 rows selected.
> @ sql_id 8vmu6k690g87k
--SQL_ID = 8vmu6k690g87k
SELECT UPPER (NVL (PROGRAM, 'null'))
,UPPER (MODULE)
,TYPE
,DECODE
(
NVL (INSTR (PROCESS, ':'), 0)
,0, NVL (PROCESS, 1234)
,SUBSTR (PROCESS, 1, INSTR (PROCESS, ':') - 1)
)
,OSUSER
,MACHINE
,SCHEMANAME
,USERNAME
,SERVICE_NAME
,SID
,SERIAL#
FROM SYS.V_$SESSION
WHERE SID = SYS_CONTEXT ('userenv', 'sid');
--//做了格式化处理.这条语句为什么消耗这么多资源,我已经在链接:
--//https://blog.itpub.net/267265/viewspace-2927994/ =>[20221130]测试访问视图v$session几种情况的性能差异.txt
--//https://blog.itpub.net/267265/viewspace-2926302/ =>[20221128]再谈防水墙(视图访问性能问题).txt
> @ ashtop time_model_name sql_id='8vmu6k690g87k' &day
Total Distinct Distinct Distinct
Seconds AAS %This TIME_MODEL_NAME FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- -------------------------------------------------- ------------------- ------------------- ---------- -------- -----------
59413 .7 100% | CONNECTION_MGMT SQL_EXECUTION 2024-04-08 16:13:39 2024-04-09 16:13:06 59351 33871 59364
45 .0 0% | SQL_EXECUTION 2024-04-08 17:11:02 2024-04-09 15:30:39 45 44 45
30 .0 0% | CONNECTION_MGMT PARSE SQL_EXECUTION 2024-04-08 16:17:00 2024-04-09 15:44:37 1 30 30
1 .0 0% | CONNECTION_MGMT SQL_EXECUTION PLSQL_EXECUTION 2024-04-09 15:32:23 2024-04-09 15:32:23 1 1 1
3.总结:
--//到此应该明白TIME_MODEL_NAME=CONNECTION_MGMT,应该是登录时执行的连接管理命令.
--//MGMT应该表示management .
