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/