--关于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/