1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Avoiding Disk Reads

Discussion in 'Server Administration and Options' started by prashant, Sep 10, 2010.

  1. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Oracle Version : 10.2.0.4
    OS : AIX 5.3

    Hi,

    I have a select statement in which a 3 or more tables with size upto 5G are involved. I have a db_cache_size of 8G. Following is the TKPROF output for one of these select statements.

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.01 0.00 0 0 0 0
    Fetch 208 12.75 120.66 43198 1584471 0 103847
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 210 12.76 120.67 43198 1584471 0 103847

    Misses in library cache during parse: 1
    Optimizer mode: FIRST_ROWS
    Parsing user id: 24 (FLEXFIN) (recursive depth: 1)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    103847 NESTED LOOPS SEMI (cr=1584471 pr=43198 pw=0 time=111426675 us)
    183776 NESTED LOOPS (cr=849158 pr=25265 pw=0 time=64774938 us)
    183776 SORT UNIQUE (cr=113845 pr=18730 pw=0 time=46378125 us)
    367507 TABLE ACCESS BY INDEX ROWID CSTB_AMOUNT_DUE (cr=113845 pr=18730 pw=0 time=43769957 us)
    370325 INDEX RANGE SCAN IND_CCY_DUE (cr=1918 pr=1918 pw=0 time=8915537 us)(object id 7195)
    183776 TABLE ACCESS BY INDEX ROWID CSTB_CONTRACT (cr=735313 pr=6535 pw=0 time=19776476 us)
    183776 INDEX UNIQUE SCAN PK_CSTB_CONTRACT (cr=551537 pr=3722 pw=0 time=17751021 us)(object id 48362)
    103847 TABLE ACCESS BY INDEX ROWID CSTB_CONTRACTIS (cr=735313 pr=17933 pw=0 time=52704966 us)
    183776 INDEX UNIQUE SCAN PK_CSTB_CONTRACTIS (cr=551537 pr=4006 pw=0 time=17867757 us)(object id 48363)

    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    db file sequential read 43198 0.10 110.20

    As we can see there are no Full Table Scans and most of the scans are unique index scans. But still 1 query execution goes for 208 fetches.

    Is there a way to avoid disk reads for this query?

    I already tried increasing my db_cache_size. Also i cant pin them in keep cache as they are too big to be sitting there.

    Thank you.

    Best Regards,
    Prashant
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can't avoid disk reads on active tables no matter how large your cache may be as modified data blocks invalidate the cached copies. Make the tables read-only then you can put them in the KEEP cache and never have to access the table data.
     
    prashant likes this.
  3. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi Zargon,

    Thank you for your reply.

    Does this also mean we can only pin static tables in Keep Cache? I do have some tables of size in few hundred MB's, but they have lot of updates each day. So is it a good idea to pin them in DB_KEEP_CACHE?

    Best Regards,
    Prashant
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That depends. Read here:

    http://oratips-ddf.blogspot.com/2008/05/thats-keeper.html

    It's really dependent upon how heavily updated these tables are and how often the cache needs to be refreshed. You may find that increasing the buffer cache and taking these tables out of the KEEP pool is a better (more efficient) option. You won't know that, though, until you test your theory.