Regarding pstop in solaris to stop query

From: Taral Desai <taral.desai_at_gmail.com>
Date: Fri, 4 Sep 2009 15:38:24 -0500
Message-ID: <2b0cd5cd0909041338p782b7f4eo83f961c2532bd3a8_at_mail.gmail.com>



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."

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 04 2009 - 15:38:24 CDT

Original text of this message