About idle event

--关于SQL*Net message from client or SQL*Net more data from client
一,第一个原因就是网络存在问题导致客户端和DBMS之间的网络通信存在问题,这里不过多讨论。
二,SQL执行次数异常高导致的情况。
[@more@]
执行次数过多,客户端和DBMS间的网络通信频繁地发生,因此网络相关的等待时间可能会延长。这时Oracle需要等待从客户端发送的响应,
所以等待QL*Net message from client or SQL*Net more data from client,如果整个等待时间中,SQL*Net more data from client
占据着相当比重的等待时间,就可以怀疑过多的执行次数引起的系统性能下降。
Case 1:执行50次的update stament
SQL> update sales set prod_id=13 where prod_id=14 and rownum=1;
1 row updated.
...
SQL> select event,total_waits,time_waited
2 from v$session_event
3 where sid=(select sid from v$mystat where rownum=1)
4 order by 3 desc;
EVENT TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
SQL*Net message from client 468 8697
SQL*Net message to client 469 0
SQL> select n.name,sum(s.value)
2 from v$sesstat s,v$statname n
3 where n.name like'%&stat_name%'
4 and s.statistic#=n.statistic#
5 and s.sid=(select sid from v$mystat where rownum=1)
6 group by n.name;
Enter value for stat_name: parse count (total)
old 3: where n.name like'%&stat_name%'
new 3: where n.name like'%parse count%'
NAME SUM(S.VALUE)
---------------------------------------------------------------- ------------
parse count (total) 542
SQL> select n.name,sum(s.value)
2 from v$sesstat s,v$statname n
3 where n.name like'%&stat_name%'
4 and s.statistic#=n.statistic#
5 and s.sid=(select sid from v$mystat where rownum=1)
6 group by n.name;
Enter value for stat_name: execute count
old 3: where n.name like'%&stat_name%'
new 3: where n.name like'%execute count%'
NAME SUM(S.VALUE)
---------------------------------------------------------------- ------------
execute count 569
Case 2:将50次的update,以一次PL/SQL执行
SQL> begin
2 for id in 1..50 loop
3 update sales set prod_id=13 where prod_id=14 and rownum=1;
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select event,total_waits,time_waited
from v$session_event
2 3 where sid=(select sid from v$mystat where rownum=1)
4 order by 3 desc;
EVENT TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
SQL*Net message from client 22 553
log file sync 1 2
SQL*Net message to client 23 0
SQL> select n.name,sum(s.value)
2 from v$sesstat s,v$statname n
3 where n.name like'%&stat_name%'
4 and s.statistic#=n.statistic#
and s.sid=(select sid from v$mystat where rownum=1)
5 6 group by n.name;
Enter value for stat_name: parse count (total)
old 3: where n.name like'%&stat_name%'
new 3: where n.name like'%parse count (total)%'
NAME SUM(S.VALUE)
---------------------------------------------------------------- ------------
parse count (total) 25
SQL> select n.name,sum(s.value)
2 from v$sesstat s,v$statname n
3 where n.name like'%&stat_name%'
4 and s.statistic#=n.statistic#
5 and s.sid=(select sid from v$mystat where rownum=1)
6 group by n.name;
Enter value for stat_name: execute count
old 3: where n.name like'%&stat_name%'
new 3: where n.name like'%execute count%'
NAME SUM(S.VALUE)
---------------------------------------------------------------- ------------
execute count 78
Case1是每次重复执行SQL语句的情况,Case2是将相同量的工作一次行在PL/SQL上处理的情况。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25586587/viewspace-1054044/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/25586587/viewspace-1054044/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值