Regarding pstop in solaris to stop query

Hi All,

This is just question for curiosity. May be i am wrong but please help me in
understanding this.

Below is the details where i am using one test table created from
dba_objects.

What i did is i ran first select * from test and the other one with pstop
that pid from different session.
Both are almost same. Also i had flushed shared/buffer cache 2-3 time before
each test. So, both are doing same.

DB Version : 10.2.0.3 Solaris SPARC 64

SEGMENT_NAME                       BYTES      BLOCKS
---------------------------------- ---------- ----------
TEST                               6291456    384


Statistics (From select * from test without pstop)
----------------------------------------------------------
        504  recursive calls
          0  db block gets
       3714  consistent gets
        341  physical reads
          0  redo size
    2611800  bytes sent via SQL*Net to client
      36512  bytes received via SQL*Net from client
       3277  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      49128  rows processed

Statistics (From select * from test with 2 times pstop)
----------------------------------------------------------
        504  recursive calls
          0  db block gets
       3714  consistent gets
        343  physical reads
          0  redo size
    2611800  bytes sent via SQL*Net to client
      36512  bytes received via SQL*Net from client
       3277  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      49128  rows processed


But from mystat there is a difference. Here is how i had ran

set autotrace on;
alter system flush shared_pool;
alter system flush buffer_cache;
create table x1 as select sn.name, ms.value from v$mystat ms, v$statname sn
where ms.statistic# = sn.statistic# ;
select * from test;
create table x2 as select sn.name, ms.value from v$mystat ms, v$statname sn
where ms.statistic# = sn.statistic# ;

select y1.name,y1.value,y2.value,y2.value-y1.value from y1,y2 where y1.name=
y2.name order by 1;

From 1st and 2nd result there is difference with.

First Second
physical read bytes 6504448 10633216
physical read total IO requests 84 332
physical read total bytes 6569984 10698752
physical read total multi block requests 28 29
physical reads 397 649
physical reads cache 397 649
physical reads cache prefetch 317 321
physical reads prefetch warmup 21 25
recursive calls 4021 16397
sorts (memory) 71 332
sorts (rows) 420 2071
table fetch by rowid 862 1858
execute count  299 1116

So, is it saying that it's revisiting all blocks again but not showing in
autotrace.


-- 

Thanks & Regards,
Taral Desai
Pablo Picasso<http://www.brainyquote.com/quotes/authors/p/pablo_picasso.html>
- "Computers are useless. They can only give you answers."

Other related posts:

  • » Regarding pstop in solaris to stop query - Taral Desai