DB Sequential Read is killing me
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: DB Sequential Read is killing me

  1. #1
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452

    DB Sequential Read is killing me

    Hi all,
    It feels good to be back to the old grounds
    Well I have come with the same old question again
    DB Sequential File reads
    Here is my statspack report


    Code:
                  Snap Id     Snap Time      Sessions Curs/Sess Comment
                --------- ------------------ -------- --------- -------------------
    Begin Snap:      5938 29-Sep-06 17:26:51      222  62,937.3
      End Snap:      5939 29-Sep-06 17:45:53      203  68,830.6
       Elapsed:               19.03 (mins)
    
    Cache Sizes (end)
    ~~~~~~~~~~~~~~~~~
                   Buffer Cache:     6,512M      Std Block Size:          8K
               Shared Pool Size:       912M          Log Buffer:      8,192K
    
    Load Profile
    ~~~~~~~~~~~~                            Per Second       Per Transaction
                                       ---------------       ---------------
                      Redo size:            327,017.33              6,816.72
                  Logical reads:             35,226.16                734.29
                  Block changes:              1,379.77                 28.76
                 Physical reads:              7,828.18                163.18
                Physical writes:              1,200.11                 25.02
                     User calls:                645.95                 13.46
                         Parses:                 68.72                  1.43
                    Hard parses:                 21.82                  0.45
                          Sorts:                850.77                 17.73
                         Logons:                  0.20                  0.00
                       Executes:              2,138.36                 44.57
                   Transactions:                 47.97
    
      % Blocks changed per Read:    3.92    Recursive Call %:     82.98
     Rollback per transaction %:    0.00       Rows per Sort:    425.05
    
    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %:   99.66       Redo NoWait %:     99.99
                Buffer  Hit   %:   80.75    In-memory Sort %:    100.00
                Library Hit   %:   98.41        Soft Parse %:     68.24
             Execute to Parse %:   96.79         Latch Hit %:     99.69
    Parse CPU to Parse Elapsd %:   78.01     % Non-Parse CPU:     97.37
     Shared Pool Statistics        Begin   End
                                   ------  ------
                 Memory Usage %:   90.68   92.07
        % SQL with executions>1:   11.59   13.99
      % Memory for SQL w/exec>1:   14.88   17.29
    
    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~                                                     % Total
    Event                                               Waits    Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    db file sequential read                         1,856,951      78,320    65.99
    CPU time                                                        8,530     7.19
    buffer busy waits                                 138,214       8,363     7.05
    db file scattered read                            597,840       4,955     4.17
    log file sync                                      48,047       3,711     3.13
              -------------------------------------------------------------
    ^LWait Events for DB: ICCM  Instance: ICCM  Snaps: 5938 -5939
    -> s  - second
    -> cs - centisecond -     100th of a second
    -> ms - millisecond -    1000th of a second
    -> us - microsecond - 1000000th of a second
    -> ordered by wait time desc, waits desc (idle events last)
    
                                                                       Avg
                                                         Total Wait   wait    Waits
    Event                               Waits   Timeouts   Time (s)   (ms)     /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    db file sequential read         1,856,951          0     78,320     42     33.9
    buffer busy waits                 138,214        526      8,363     61      2.5
    db file scattered read            597,840          0      4,955      8     10.9
    log file sync                      48,047        364      3,711     77      0.9
    direct path read                   46,301          0      2,465     53      0.8
    PL/SQL lock timer                      51         51      2,403  47112      0.0
      Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
    --------------- ------------ -------------- ------ -------- --------- ----------
          3,645,051           69       52,826.8   40.8  1018.97   2395.30 2930303446
    Module: JDBC Thin Client
    SELECT CAF_NUM,MDN,RECEIPT_NO,PAYMENT_DATE,PAYMENT_AMOUNT,PAYMEN
    T_MODE,PAYMENT_TYPE,PAYMENT_SOURCE ,SUBSTR(NM_CL_RCL_CUSTTYPE,0,
    INSTR( NM_CL_RCL_CUSTTYPE,',',1)-1) CUSTOMER_NAME ,SUBSTR(NM_CL_
    RCL_CUSTTYPE,INSTR( NM_CL_RCL_CUSTTYPE,',',1,1)+1,INSTR( NM_CL_R
    CL_CUSTTYPE,',',1,2)-INSTR( NM_CL_RCL_CUSTTYPE,',',1,1)-1) CREDI
    
            652,895            1      652,895.0    7.3   712.12  18009.31 3020569594
    DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
    broken BOOLEAN := FALSE; BEGIN REP09_TELE(536875135); :mydate :=
     next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
    
            504,780            1      504,780.0    5.6   666.32  17656.68 1172080501
    DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
    broken BOOLEAN := FALSE; BEGIN REP09_TELE(536874839); :mydate :=
     next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
    
            391,309           12       32,609.1    4.4    60.90     93.08 1987881048
    Module: JDBC Thin Client
    UPDATE ICCM_DE_INTERACTIONS SET STATUS =2 WHERE DESTANATION_NAME
     IN ('CLARIFY_FLASH','FLASH_EXIT') AND STATUS = 0
    
            345,842           12       28,820.2    3.9    48.73     58.38 2127400582
    Module: JDBC Thin Client
    SELECT  ACCOUNT_NO,ACTION_CODE,DECODE(DESTANATION_NAME,'CLARIFY_
    FLASH','Activate','FLASH_EXIT','DeActivateAll') FLASH_STR,ACTION
    _DATE, TREATMENT_TYPE,MESSAGE ,REFERENCE_NO ,MDNNO FROM  ICCM_DE
    _INTERACTIONS  WHERE  DESTANATION_NAME IN ('CLARIFY_FLASH','FLAS
    H_EXIT') AND STATUS = 2 ORDER BY ACTION_DATE,ACCOUNT_NO
    
            335,031           12       27,919.3    3.7    46.93     50.83  388966200
    Module: JDBC Thin Client
    UPDATE ICCM_DE_INTERACTIONS SET STATUS =1 WHERE DESTANATION_NAME
     IN ('CLARIFY_FLASH','FLASH_EXIT') AND STATUS = 2
    
            331,342            1      331,342.0    3.7   887.22   1926.20 1371170133
    Module: SQL*Plus
    delete from invoice a where invoice_id in ( select invoice_id fr
    om invoice_txt_ar a minus select invoice_id from invoice@iccm_ad
    c0 )
    
            277,089            1      277,089.0    3.1   505.71   1312.15 3561242787
    Module: SQL*Plus
    delete from invoice a where invoice_id in ( select invoice_id fr
    om invoice_txt_ar a minus select invoice_id from invoice@iccm_ad
    c6 )
    
            249,671           52        4,801.4    2.8   206.23  19963.74 3145258518
    Module: JDBC Thin Client
    SELECT COUNT(DT.CAF_NUM) FROM ICCM_DUNNING_TREATMENT DT, (SELECT
     CAF_NUM,CIOU_ID FROM ICCM_CUSTOMER_RISK_SCORE WHERE CIOU_ID = :
    B1 ) CAF WHERE CAF.CAF_NUM = DT.CAF_NUM AND CAF.CIOU_ID = :B1 AN
    D (ACTION_DATE)=(SELECT MAX(ACTION_DATE) FROM ICCM_DUNNING_TREAT
    ^LSQL ordered by Reads for DB: ICCM  Instance: ICCM  Snaps: 5938 -5939
    Statistic                                      Total     per Second    per Trans
    --------------------------------- ------------------ -------------- ------------
    CPU used by this session                     853,040          747.0         15.6
    CPU used when call started                   510,519          447.0          9.3
    CR blocks created                             58,413           51.2          1.1
    Cached Commit SCN referenced                 147,495          129.2          2.7
    Commit SCN cached                                 27            0.0          0.0
    DBWR buffers scanned                         183,810          161.0          3.4
    DBWR checkpoint buffers written              122,238          107.0          2.2
    DBWR checkpoints                                   1            0.0          0.0
    DBWR free buffers found                      173,266          151.7          3.2
    DBWR lru scans                                    93            0.1          0.0
    DBWR make free requests                          123            0.1          0.0
    sorts (disk)                                       5            0.0          0.0
    sorts (memory)                               971,575          850.8         17.7
    sorts (rows)                             412,972,642      361,622.3      7,538.1
    table fetch by rowid                      18,108,710       15,857.0        330.5
    table fetch continued row                     16,371           14.3          0.3
    table scan blocks gotten                   9,184,411        8,042.4        167.6
    table scan rows gotten                   363,876,722      318,631.1      6,641.9
    user calls                                   737,678          646.0         13.5
    user commits                                  54,784           48.0          1.0
    user rollbacks                                     1            0.0          0.0
    The database is an OLTP with an average 3000 users hitting any point of time.
    Can I do something about it? or start cleaning my guns
    Bye the way I miss Gandalf and Mr Hanky (togather)
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Seems like a lot of wait for 20 mintues. Not sure you're going to resolve this on an instance level. My next step would be to start looking at either some of the long running processes or processes that are doing most of your work. I suspect you have a query or two that are using the wrong index.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Everything seems eternal in this database
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Its 200 users and not 3000 huh?!

    Your application performes around 9 million physical reads and 40 million logical reads in 19 minutes with 200 users (not 3000 as you said, and how many ACTIVE?) that is probably abit too much. I was on a site for a couple of months supporting a 3000 users (40 active users at any time) crm system (peak time 3500) and in peak time it only performs around 170 million logical reads and 12 million physical reads in an 60 minutes interval (triple yours).

    Also please show us the average read time on your datafiles

    My suggestions:

    * Check top SQL

    * Increase your db cache, add a couple of GB, to 9GB (buffer busy waits is showing you have db cache problem)

    * Tune your db cache, use keep pool if you can, identify the most accessed table and index (you need some monitoring for that), running scripts against x$bh for example. Cache the objects in the keep pool.


    Rgds

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width