Metrics "Global Cache Average CR Get Time" is at 1.273(Unknown):from em 12c

最新推荐文章于 2021-04-08 20:21:20 发布

1ihj2015

最新推荐文章于 2021-04-08 20:21:20 发布

阅读量2.9k

点赞数

分类专栏： Oracle Troubleshooting

本文链接：https://blog.csdn.net/itdba/article/details/42089205

版权

Oracle Troubleshooting 专栏收录该内容

37 篇文章 0 订阅

订阅专栏

初步怀疑与节点2 的频繁操作有关：

问题发生时候：节点1出现cr等待，节点2 等待频繁cs log flush sync 大量插入

INSTANCE_NUMBER SQL_ID EVENT TO_CHAR(SAMPLE_ BLOCKING_SESSION SESSION_ID PROGRAM
--------------- ------ -------------------- --------------- ---------------- ---------- --------------------------------------------------
1 gc cr block busy 20141222 122658 382 oracle@szaadb01.shenzhenair.com (MMON)
2 gcs log flush sync 20141222 122659 2091 1331 oracle@szaadb02.shenzhenair.com (LMS0)

节点2：ash 12:24~12:26

语句查询！

2 节点2等待较多

16:35:29 sys@szaa1(szaadb01.shenzhenair.com)> select instance_number,
16:36:38 2 sql_id,
16:36:38 3 event ,
16:36:38 4 to_char(sample_time, 'yyyymmdd hh24miss'),blocking_session,session_id
16:36:38 5 from dba_hist_active_sess_history

16:36:38 6 where sample_time between to_date('20141222 1224', 'yyyymmdd hh24mi') and

16:36:38 7 to_date('201412221227', 'yyyymmddhh24mi')

16:36:38 8 order by 1, 4

INSTANCE_NUMBER SQL_ID EVENT TO_CHAR(SAMPLE_ BLOCKING_SESSION SESSION_ID
--------------- ------------------ --------------------------------------------- --------------- ---------------- ----------
1 9jaxvkdxqzrrj 20141222 122408 964
1 9jaxvkdxqzrrj 20141222 122408 2025
1 3thj2wp9cufsf cell multiblock physical read 20141222 122408 486
1 cmgwts96cqp40 cell single block physical read 20141222 122408 6
1 bkctyssuhqfv5 read by other session 20141222 122408 486 2221
1 3thj2wp9cufsf 20141222 122418 486
1 473m76jkv4rh2 20141222 122418 683
1 bkctyssuhqfv5 read by other session 20141222 122418 486 2221
1 control file parallel write 20141222 122418 2186
1 7sqwzvrz1j1h9 cell single block physical read 20141222 122428 1541
1 3thj2wp9cufsf read by other session 20141222 122428 2221 486
1 bkctyssuhqfv5 cell multiblock physical read 20141222 122428 2221
1 473m76jkv4rh2 20141222 122438 32
1 3thj2wp9cufsf cell multiblock physical read 20141222 122438 486
1 bkctyssuhqfv5 read by other session 20141222 122438 486 2221
1 cmgwts96cqp40 cell single block physical read 20141222 122438 6
1 bkctyssuhqfv5 cell multiblock physical read 20141222 122448 2221
1 473m76jkv4rh2 20141222 122448 775
1 3thj2wp9cufsf read by other session 20141222 122448 2221 486
1 cmgwts96cqp40 cell single block physical read 20141222 122448 6
1 32dr7tc2983z6 20141222 122448 1920
1 3thj2wp9cufsf cell single block physical read 20141222 122458 486
1 bkctyssuhqfv5 read by other session 20141222 122458 486 2221
1 cmgwts96cqp40 cell single block physical read 20141222 122458 6
1 7qau3g84sytu0 20141222 122508 2192
1 g9su67zf1pna3 cell single block physical read 20141222 122508 1175
1 cmgwts96cqp40 cell single block physical read 20141222 122508 6
1 99xy19sm25ddf 20141222 122508 24
1 3thj2wp9cufsf read by other session 20141222 122508 1175 486
1 bkctyssuhqfv5 read by other session 20141222 122508 1175 2221
1 473m76jkv4rh2 20141222 122518 2095
1 4szpsck2vcgqz cell smart table scan 20141222 122518 977
1 cmgwts96cqp40 cell single block physical read 20141222 122518 6
1 control file sequential read 20141222 122518 2186
1 9jaxvkdxqzrrj 20141222 122528 15
1 32dr7tc2983z6 20141222 122528 127
1 5q1pf7y6mch5m 20141222 122538 36
1 cmgwts96cqp40 cell single block physical read 20141222 122538 6
1 cmgwts96cqp40 cell single block physical read 20141222 122548 6
1 23f7sp7qnw4s9 SQL*Net message from dblink 20141222 122548 36
1 1rpt0s134sxn5 20141222 122548 1147
1 5vptj8m97c87h 20141222 122548 2192
1 32dr7tc2983z6 20141222 122548 225
1 6jqgcs27h87v1 20141222 122558 36
1 473m76jkv4rh2 20141222 122558 577
1 4szpsck2vcgqz cell smart table scan 20141222 122608 879
1 cmgwts96cqp40 cell single block physical read 20141222 122618 6
1 473m76jkv4rh2 20141222 122618 1726
1 6bkn39uufnkc5 20141222 122618 15
1 4szpsck2vcgqz cell smart table scan 20141222 122628 778
1 9jaxvkdxqzrrj 20141222 122628 1541
1 44u1t9zfshgfh cell multiblock physical read 20141222 122628 1728
1 44u1t9zfshgfh 20141222 122638 1728
1 4szpsck2vcgqz cell smart table scan 20141222 122638 1349
1 8vqq8rutq6czp 20141222 122638 15
1 8s3szp0w2r6kj log file switch (checkpoint incomplete) 20141222 122648 2091 696
1 1rpt0s134sxn5 20141222 122648 1147
1 log file sequential read 20141222 122648 2091
1 cmgwts96cqp40 cell single block physical read 20141222 122658 6
1 gc cr block busy 20141222 122658 382
2 8z94c2qvp10t5 SQL*Net more data from client 20141222 122408 1203
2 db file parallel write 20141222 122408 1996
2 gk7c2sw0u5zhy 20141222 122408 771
2 dz63fq17dpck7 20141222 122408 914
2 5xg4cgngz58fy 20141222 122408 1181
2 db file parallel write 20141222 122408 1806
2 0k0qc1uzsyhdp SQL*Net more data from client 20141222 122408 1377
2 log file parallel write 20141222 122408 2091
2 log file sequential read 20141222 122408 1237
2 db file parallel write 20141222 122408 1901
2 20141222 122418 2091
2 gk7c2sw0u5zhy SQL*Net more data from client 20141222 122418 771
2 dz63fq17dpck7 SQL*Net more data from client 20141222 122418 914
2 3g0nxqpy2b83u 20141222 122418 1106
2 3xs2t5pq391vy 20141222 122418 618
2 log file sequential read 20141222 122418 1522
2 20141222 122418 1806
2 db file parallel write 20141222 122418 1901
2 db file parallel write 20141222 122418 1996
2 0k0qc1uzsyhdp 20141222 122418 1377
2 db file parallel write 20141222 122429 1996
2 db file parallel write 20141222 122429 1901
2 0k0qc1uzsyhdp SQL*Net more data from client 20141222 122429 1377
2 8z94c2qvp10t5 SQL*Net more data from client 20141222 122429 1203
2 log file sequential read 20141222 122429 1142
2 3xs2t5pq391vy SQL*Net more data from client 20141222 122429 618
2 db file parallel write 20141222 122429 1806
2 8z94c2qvp10t5 SQL*Net more data from client 20141222 122439 1203
2 log file sequential read 20141222 122439 1237
2 dz63fq17dpck7 20141222 122439 914
2 0k0qc1uzsyhdp SQL*Net more data from client 20141222 122439 1377
2 0k0qc1uzsyhdp SQL*Net more data from client 20141222 122449 1377
2 3g0nxqpy2b83u SQL*Net more data from client 20141222 122449 1106
2 dz63fq17dpck7 SQL*Net more data from client 20141222 122449 914
2 3xs2t5pq391vy cell single block physical read 20141222 122449 618
2 log file sequential read 20141222 122449 1522
2 db file parallel write 20141222 122449 1806
2 db file parallel write 20141222 122449 1901
2 db file parallel write 20141222 122449 1996
2 8z94c2qvp10t5 SQL*Net more data from client 20141222 122449 1203
2 8z94c2qvp10t5 cell single block physical read 20141222 122459 1203
2 3g0nxqpy2b83u 20141222 122459 1106
2 gk7c2sw0u5zhy 20141222 122459 771
2 3xs2t5pq391vy 20141222 122459 618
2 log file parallel write 20141222 122459 2091
2 3xs2t5pq391vy 20141222 122509 618
2 log file sync 20141222 122509 2091 771
2 dz63fq17dpck7 SQL*Net more data from client 20141222 122509 914
2 3g0nxqpy2b83u 20141222 122509 1106
2 8z94c2qvp10t5 SQL*Net more data from client 20141222 122509 1203
2 log file sequential read 20141222 122509 1237
2 0k0qc1uzsyhdp 20141222 122509 1377
2 20141222 122509 1806
2 db file parallel write 20141222 122509 1901
2 20141222 122509 1996
2 log file parallel write 20141222 122509 2091
2 db file parallel write 20141222 122519 1996
2 db file parallel write 20141222 122519 1901
2 db file parallel write 20141222 122519 1806
2 log file sequential read 20141222 122519 1522
2 log file sync 20141222 122519 2091 1377
2 log file parallel write 20141222 122519 2091
2 log file sync 20141222 122519 2091 1106
2 log file sync 20141222 122519 2091 914
2 log file sync 20141222 122519 2091 771
2 log file sync 20141222 122519 2091 618
2 log file sync 20141222 122519 2091 1203
2 dz63fq17dpck7 20141222 122529 914
2 log file parallel write 20141222 122529 2091
2 db file parallel write 20141222 122529 1996
2 db file parallel write 20141222 122529 1901
2 db file parallel write 20141222 122529 1806
2 0k0qc1uzsyhdp SQL*Net more data from client 20141222 122529 1377
2 log file sync 20141222 122529 2091 1203
2 5xg4cgngz58fy SQL*Net more data to client 20141222 122529 1181
2 log file sequential read 20141222 122529 1142
2 log file sync 20141222 122529 2091 1106
2 log file sync 20141222 122529 2091 771
2 log file sync 20141222 122529 2091 618
2 bnnn1qftqd32g cell single block physical read 20141222 122539 1751
2 0k0qc1uzsyhdp SQL*Net more data from client 20141222 122539 1377
2 log file sequential read 20141222 122539 1237
2 dz63fq17dpck7 20141222 122539 914
2 3xs2t5pq391vy SQL*Net more data from client 20141222 122539 618
2 0k0qc1uzsyhdp SQL*Net more data from client 20141222 122549 1377
2 log file parallel write 20141222 122549 2091
2 8z94c2qvp10t5 SQL*Net more data from client 20141222 122549 1203
2 20141222 122549 951
2 gk7c2sw0u5zhy 20141222 122549 771
2 20141222 122549 1901
2 b25bz0tyzjwx4 20141222 122559 628
2 5f03gufh45azb 20141222 122559 767
2 dz63fq17dpck7 20141222 122559 914
2 3g0nxqpy2b83u SQL*Net more data from client 20141222 122559 1106
2 8z94c2qvp10t5 SQL*Net more data from client 20141222 122559 1203
2 3xs2t5pq391vy SQL*Net more data from client 20141222 122559 618
2 db file parallel write 20141222 122609 1901
2 db file parallel write 20141222 122609 1806
2 gk7c2sw0u5zhy 20141222 122609 771
2 0k0qc1uzsyhdp 20141222 122609 1377
2 log file sync 20141222 122609 111
2 8z94c2qvp10t5 20141222 122609 1203
2 db file parallel write 20141222 122609 1996
2 log file sync 20141222 122609 2091 1106
2 log file parallel write 20141222 122609 2091
2 log file sequential read 20141222 122609 1237
2 3xs2t5pq391vy SQL*Net more data from client 20141222 122619 618
2 gk7c2sw0u5zhy SQL*Net more data from client 20141222 122619 771
2 3g0nxqpy2b83u 20141222 122619 1106
2 8z94c2qvp10t5 20141222 122619 1203
2 20141222 122619 1806
2 20141222 122619 1996
2 dz63fq17dpck7 20141222 122619 914
2 db file parallel write 20141222 122629 1996
2 db file parallel write 20141222 122629 1901
2 db file parallel write 20141222 122629 1806
2 log file sync 20141222 122629 2091 618
2 log file sync 20141222 122629 2091 771
2 log file parallel write 20141222 122629 2091
2 3g0nxqpy2b83u cell single block physical read 20141222 122629 1106
2 log file sequential read 20141222 122629 1142
2 log file sync 20141222 122629 2091 1203
2 0k0qc1uzsyhdp SQL*Net more data from client 20141222 122629 1377
2 dz63fq17dpck7 20141222 122629 914
2 log file parallel write 20141222 122639 2091
2 db file parallel write 20141222 122639 1901
2 log file sync 20141222 122639 2091 1377
2 log file sequential read 20141222 122639 1237
2 8z94c2qvp10t5 20141222 122639 1203
2 log file sync 20141222 122639 2091 1106
2 dz63fq17dpck7 SQL*Net more data from client 20141222 122639 914
2 gk7c2sw0u5zhy 20141222 122639 771
2 log file sync 20141222 122639 764
2 3xs2t5pq391vy 20141222 122639 618
2 db file parallel write 20141222 122639 1996
2 8z94c2qvp10t5 SQL*Net more data from client 20141222 122649 1203
2 0k0qc1uzsyhdp 20141222 122649 1377
2 3xs2t5pq391vy SQL*Net more data from client 20141222 122649 618
2 log file sequential read 20141222 122649 1522
2 gk7c2sw0u5zhy 20141222 122649 771
2 dz63fq17dpck7 SQL*Net more data from client 20141222 122649 914
2 control file parallel write 20141222 122659 1142
2 log file sync 20141222 122659 2091 1106
2 log file sync 20141222 122659 2091 914
2 log file sync 20141222 122659 2091 771
2 log file sync 20141222 122659 2091 618
2 log file sync 20141222 122659 2091 509
2 log file sync 20141222 122659 2091 111
2 8z94c2qvp10t5 SQL*Net more data from client 20141222 122659 1203
2 gcs log flush sync 20141222 122659 2091 1331
2 db file parallel write 20141222 122659 1806
2 db file parallel write 20141222 122659 1901
2 db file parallel write 20141222 122659 1996
2 log file parallel write 20141222 122659 2091
2 0k0qc1uzsyhdp 20141222 122659 1377

emc 12c 发现该时间段节点2发生等待陡增。基本可以判定该问题于节点2的测试数据有关。

Top SQL with Top Events

SQL ID	Sampled # of Executions	% Activity	Event	% Event	Top Row Source	% RwSrc	SQL Text
dz63fq17dpck7	123	8.75	CPU + Wait for CPU	5.91	Row Source Not Available	5.91	INSERT INTO ODS_GHFFP_S_LOY_ME...
dz63fq17dpck7	123	8.75	SQL*Net more data from client	2.56	Row Source Not Available	2.56	INSERT INTO ODS_GHFFP_S_LOY_ME...
0k0qc1uzsyhdp	109	7.83	SQL*Net more data from client	3.91	Row Source Not Available	3.91	INSERT INTO ODS_GHFFP_S_CONTAC...
0k0qc1uzsyhdp	109	7.83	CPU + Wait for CPU	3.56	Row Source Not Available	3.56	INSERT INTO ODS_GHFFP_S_CONTAC...
3xs2t5pq391vy	109	7.76	CPU + Wait for CPU	3.84	Row Source Not Available	3.84	INSERT INTO ODS_GHFFP_S_PS_CRE...
3xs2t5pq391vy	109	7.76	SQL*Net more data from client	3.35	Row Source Not Available	3.35	INSERT INTO ODS_GHFFP_S_PS_CRE...
8z94c2qvp10t5	103	7.33	SQL*Net more data from client	3.49	Row Source Not Available	3.49	INSERT INTO ODS_GHFFP_S_LOY_CA...
8z94c2qvp10t5	103	7.33	CPU + Wait for CPU	2.56	Row Source Not Available	2.56	INSERT INTO ODS_GHFFP_S_LOY_CA...
gk7c2sw0u5zhy	93	6.69	SQL*Net more data from client	3.77	Row Source Not Available	3.77	INSERT INTO ODS_GHFFP_S_PER_CO...
gk7c2sw0u5zhy	93	6.69	CPU + Wait for CPU	2.56	Row Source Not Available	2.56	INSERT INTO ODS_GHFFP_S_PER_CO...

1ihj2015

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Metrics "Global Cache Average CR Get Time" is at 1.273(Unknown):from em 12c

初步怀疑与节点2 的频繁操作有关：问题发生时候：节点1出现cr等待，节点2 等待频繁cs log flush sync 大量插入INSTANCE_NUMBER SQL_ID EVENT TO_CHAR(SAMPLE_ BLOCKING_SESSION SESSION_ID PROGRAM--------------- ------ ---
复制链接

扫一扫