oracle wait 时间过长,个人对于v$session_wait中的WAIT_TIME,SECONDS_IN_WAIT,STATE关系的一点理解,望大家批...

最新推荐文章于 2023-07-25 15:06:44 发布

数据领航员

最新推荐文章于 2023-07-25 15:06:44 发布

阅读量629

点赞数

oracle wait 时间过长

终于作出实验来了：

SA调用的脚本代码(instruct.txt)：

select sid from v$mystat where rownum=1;

alter session set session_cached_cursors=100;

select name,value from v$parameter where name='session_cached_cursors';

select to_char(sysdate,'hh24:mi:ss') time from dual;

declare

v_num number(1);

begin

dbms_lock.sleep(300);

for i in 1..200000000

loop

select 1 into v_num from dual;

end loop;

end;

SA调用如下：

SQL> @h:\instruct.txt;

SID

----------

Session altered

NAME VALUE

---------------------------------------------------------------- --------------------------------------------------------------------------------

session_cached_cursors 100

TIME

--------

16:40:42

declare

v_num number(1);

begin

dbms_lock.sleep(300);

for i in 1..200000000

loop

select 1 into v_num from dual;

end loop;

end;

SB调用代码如下(get_stat.txt):

select to_char(sysdate,'hh24:mi:ss') time from dual;

SELECT SID,SEQ#,EVENT,WAIT_TIME,SECONDS_IN_WAIT,STATE FROM v$session_wait WHERE sid=14;

SELECT SID,serial#,STATUS FROM v$session WHERE sid=14;

SELECT * FROM v$sess_io WHERE sid=14;

SELECT b.name,a.value FROM v$sesstat a,v$statname b

WHERE sid=14 AND a.STATISTIC#=b.STATISTIC#

AND b.name IN('CPU used when call started','CPU used by this session','parse count (total)','parse count (hard)','db block gets','consistent gets');

SB调用结果如下：

SQL> @h:\get_stat.txt;

TIME

--------

16:43:14

SID SEQ# EVENT WAIT_TIME SECONDS_IN_WAIT STATE

---------- ---------- ---------------------------------------------------------------- ---------- --------------- -------------------

14 78 PL/SQL lock timer 0 150 WAITING

SID SERIAL# STATUS

---------- ---------- --------

14 29 ACTIVE

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES

---------- ---------- --------------- -------------- ------------- ------------------

14 28 273 10 0 0

NAME VALUE

---------------------------------------------------------------- ----------

CPU used when call started 1

CPU used by this session 1

db block gets 28

consistent gets 273

parse count (total) 62

parse count (hard) 1

6 rows selected

SQL> @h:\get_stat.txt;

TIME

--------

16:45:19

SID SEQ# EVENT WAIT_TIME SECONDS_IN_WAIT STATE

---------- ---------- ---------------------------------------------------------------- ---------- --------------- -------------------

14 78 PL/SQL lock timer 0 270 WAITING

SID SERIAL# STATUS

---------- ---------- --------

14 29 ACTIVE

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES

---------- ---------- --------------- -------------- ------------- ------------------

14 28 273 10 0 0

NAME VALUE

---------------------------------------------------------------- ----------

CPU used when call started 1

CPU used by this session 1

db block gets 28

consistent gets 273

parse count (total) 62

parse count (hard) 1

6 rows selected

这时SA正在执行dbms_lock.sleep(300);所以表现出等待事件:PL/SQL lock timer,两次调用统计视图发现它的LIO根本就没有发生变化.

但是随后调用如下：

SQL> @h:\get_stat.txt;

TIME

--------

16:47:03

SID SEQ# EVENT WAIT_TIME SECONDS_IN_WAIT STATE

---------- ---------- ---------------------------------------------------------------- ---------- --------------- -------------------

14 78 PL/SQL lock timer 30720 369 WAITED KNOWN TIME

SID SERIAL# STATUS

---------- ---------- --------

14 29 ACTIVE

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES

---------- ---------- --------------- -------------- ------------- ------------------

14 9936472 2484386 10 0 0

NAME VALUE

---------------------------------------------------------------- ----------

CPU used when call started 1

CPU used by this session 1

db block gets 10007508

consistent gets 2502145

parse count (total) 64

parse count (hard) 3

6 rows selected

SQL> @h:\get_stat.txt;

TIME

--------

16:54:36

SID SEQ# EVENT WAIT_TIME SECONDS_IN_WAIT STATE

---------- ---------- ---------------------------------------------------------------- ---------- --------------- -------------------

14 78 PL/SQL lock timer 30720 795 WAITED KNOWN TIME

SID SERIAL# STATUS

---------- ---------- --------

14 29 ACTIVE

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES

---------- ---------- --------------- -------------- ------------- ------------------

14 68935040 17234028 10 0 0

NAME VALUE

---------------------------------------------------------------- ----------

CPU used when call started 1

CPU used by this session 1

db block gets 68982076

consistent gets 17245787

parse count (total) 64

parse count (hard)

这两次调用统计视图时,第一次时SA已经开始FOR循环,也就是说它开始working,PL/SQL lock timer等待已经结束了,但是EVENT仍是'PL/SQL lock timer',它的WAIT_TIME=30720cs,大概就等于我sleep的300s,SECONDS_IN_WAIT=369,且LIO有了明显的增加,第二次调用时EVENT仍是'PL/SQL lock timer'，WAIT_TIME=30720cs也不再变化,但SECONDS_IN_WAIT增大为795,且LIO相对于第一次有了明显的增加。

不知这两个帖子和我说的是不是一回事儿：

http://www.itpub.net/553294,1.html

http://www.itpub.net/566570,1.html