 |
| 文件: |
awr.rar |
| 大小: |
73KB |
| 下载: |
下载 | |
困扰了好几天的问题了，至今没有得到一个特别有效的解决办法，请大家帮忙给下参考，
现在读写速度感觉不是很快，不知道具体的原因，请大家帮忙给点建议。
SQL> select count(*),event from v$session_wait group by event;
COUNT(*) EVENT
---------- ----------------------------------------------------------------
1 jobq slave wait
2 db file sequential read
1 Streams AQ: waiting for messages in the queue
1 SQL*Net message to client
10 SQL*Net message from client
1 smon timer
1 pmon timer
11 rdbms ipc message
1 wait for unread message on broadcast channel
1 Streams AQ: qmn slave idle wait
1 Streams AQ: qmn coordinator idle wait
COUNT(*) EVENT
---------- ----------------------------------------------------------------
1 Streams AQ: waiting for time management or cleanup tasks
12 rows selected.
发现等待也没有几个，主要问题可能不再sql语句上了。
那哪里能出现这样问题呢，
awr显示
SQL ordered by Reads
Total Disk Reads: 779,315
Captured SQL account for 82.7% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
68,050 26,881 2.53 8.73 33.91 509.51 3fyvr8a5y64ff (TNS V1-V3) DELETE FROM "CRM2"."TB_BIL_XAC...
55,559 5,803 9.57 7.13 14.76 460.47 c0j2fh3nyqrrn (TNS V1-V3) DELETE FROM "CRM2"."TB_BIL_XAC...
52,665 6,679 7.89 6.76 14.49 416.53 fr0jzrhkbarhp (TNS V1-V3) DELETE FROM "CRM1"."TB_BIL_XAC...
44,978 5,780 7.78 5.77 12.87 349.59 6uuqpyzs7at7b (TNS V1-V3) DELETE FROM "CRM1"."TB_BIL_XAC...
40,475 5,137 7.88 5.19 10.27 325.81 6qadgxstqc51x (TNS V1-V3) DELETE FROM "CRM1"."TB_BIL_XAC...
38,967 5,436 7.17 5.00 10.95 341.20 0340a0skkb8zk (TNS V1-V3) DELETE FROM "CRM1"."TB_BIL_XAC...
38,457 5,021 7.66 4.93 10.82 311.87 3nz74r43a1vph (TNS V1-V3) DELETE FROM "CRM2"."TB_BIL_XAC...
28,961 6,615 4.38 3.72 11.46 274.79 8g85ztqj8hxma (TNS V1-V3) DELETE FROM "CRM1"."TB_BIL_XAC...
26,000 3,406 7.63 3.34 6.44 213.83 dumdb3np1p2kv (TNS V1-V3) DELETE FROM "CRM2"."TB_BIL_XAC...
21,492 27,529 0.78 2.76 25.87 145.66 6kuc397pkpjwv (TNS V1-V3) INSERT INTO "CRM2"."TB_BIL_XAC...
16,218 2,166 7.49 2.08 4.10 141.26 128v44d23q1gu (TNS V1-V3) DELETE FROM "CRM2"."TB_BIL_XAC...
14,669 7,239 2.03 1.88 11.34 112.58 39m6mbca7pmfr (TNS V1-V3) INSERT INTO "CRM1"."TB_BIL_XAC...
14,030 6,912 2.03 1.80 11.64 118.46 185zp9ahwdghs (TNS V1-V3) INSERT INTO "CRM2"."TB_BIL_XAC...
11,325 5,666 2.00 1.45 8.73 86.61 avv0rpsvh7z5u (TNS V1-V3) INSERT INTO "CRM2"."TB_BIL_XAC...
8,993 1,001 8.98 1.15 2.20 64.94 3bgv3fcvfk980 (TNS V1-V3) DELETE FROM "CRM2"."TB_BIL_XAC...
8,251 8,065 1.02 1.06 7.88 66.38 3dmy3masmrbxn (TNS V1-V3) INSERT INTO "CRM2"."TB_BIL_BAL...
8,236 4,180 1.97 1.06 5.70 69.77 6wys56hu28nh7 (TNS V1-V3) INSERT INTO "CRM2"."TB_BIL_XAC...
Back to SQL Statistics
这里elapsed time明显太高了
Segments by Row Lock Waits
% of Capture shows % of row lock waits for each top segment compared
with total row lock waits for all segments captured by the Snapshot
这里等待有点高，
生成了awr报告，以便大家帮忙看看，这里我看各种命中率也不是很低，如果批量处理，速度还是可以忍受的。
但是现在有时候，update,delete,insert上百条的数据，就达到2-10s之间不等，痛苦之季阿！
平时使用情况。
memory used 70%，
cpu used 50%,
i/o used 60%
阅读(1631) | 评论(0) | 转发(0) |
