Re: iostat - multiblock read count

From: utkanbir <hopehope_123_at_yahoo.com>
Date: 23 May 2005 00:05:39 -0700
Message-ID: <f6c90ebe.0505222305.23774566@posting.google.com>

Hi Chao ,

I have checked the statstpack output regarding to this query , in raw trace file i see lots of 'global cache cr request ' wait events but the time they take is very little comparing to the disk read events:

select /*+NOPARALLEL(M) */count(*)
from
taniadm.MERKEZ_CIKIS_34 m

call     count       cpu    elapsed       disk      query    current  
     rows

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

Parse        3      0.00       0.01          1          1          0  
        0
Execute      3      0.00       0.00          0          0          0  
        0
Fetch        3     61.10     767.28     307181     307209          0  
        3

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

total        9     61.10     767.30     307182     307210          0  
        3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 46

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=102403 r=102399 w=0 time=262103077 us)

10133769 TABLE ACCESS FULL MERKEZ_CIKIS_34 (cr=102403 r=102399 w=0 time=257079273 us)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

Waited ----------
SQL*Net message to client 8 0.00 0.00 SQL*Net message from client 8 78.11 173.33 global cache cr request 154082 0.12 6.73 db file scattered read 23984 1.01 707.97 latch free 6 0.03 0.05 SQL*Net break/reset to client 2 0.00 0.00 library cache lock 4 0.00 0.00 db file sequential read 3 0.01 0.02

Here the total times waited value for global cache cr request is large but total waited is very small . The majority of query time spent in disk io.

For the direct/io , i have checked the oracle executables, straced them (especially open system calls) and saw the o_direct flag , and :

filesystemio_options is set to none. (for ocfs i was told it was not necessary to set it, since ocfs uses direct io without this parameter)

Kind Regrads,

"chao_ping" <zhuchao_at_gmail.com> wrote in message news:<1116479070.144515.220410_at_g47g2000cwa.googlegroups.com>...
> One possible reason could because of your global cache management. If
> you shutdown one node, maybe result will be diffirent.
>
> One question, is your OCFS doing directIO read? Else filesystem cache
> can mask your result.
Received on Mon May 23 2005 - 02:05:39 CDT