I/O Performance Problem - Need help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: I/O Performance Problem - Need help

  1. #1
    Join Date
    May 2008
    Posts
    29

    I/O Performance Problem - Need help

    Gurus,

    I need your assistance here. I will try my best to explain the problem below. Any comment and suggestion will be highly appreciated.

    Main issue: we are running a process to generate invoice number for cable subscribers. To process 152K invoices the response is 54 minutes. To complete our full subscriber based, the estimated time will be near to 26 hours.
    Code:
    From Trace
    
    (1) Overal Wait statistics from the trace
    
    Event waited on                                     Times   Max. Wait  Total Waited
    ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                   391525        0.00          0.27
      SQL*Net message from client                391525        0.00         47.40
      db file sequential read                            77802        0.50        146.89
    
    The SQL*Net message from client wait has been distributed into many sql statements with high executions. Hence I am not considering this wait as a culprit.
    
    (2) The following queries are waiting for 3 minutes on DB_File_Seq_Read
    (a) 
    SELECT Inv_ID FROM
     Invoice WHERE ((((SNUM=:PREDVALUE0) AND 
      (SBQ=:PREDVALUE1)) AND 
      (SBV1=:PREDVALUE2)) AND 
      (STYPE=:PREDVALUE3)) AND 
      (SReq=:PREDVALUE4)
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  -------
    Parse        0      0.00       0.00          0          0          0           0
    Execute  78273     26.30      25.68          0          0          0           0
    Fetch    78273     10.64     102.40      50529     557486          0       78273
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total   156546     36.94     128.09      50529     557486          0       78273
    
    --------------------------------------------------------------------------| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                      |     1 |    26 |     6   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| invoice    |     1 |    26 |     6   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | invoice_PK |     8 |       |     4   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("invoice"."sbq"=9 AND "invoice"."sbv1"=1 AND
                  "invoice"."stype"=1 AND "invoice"."sre"=1)
       2 - access("invoice"."snum"='9872345670')
    
    Statistics
    ----------------------------------------------------------
             69  recursive calls
              0  db block gets
             27  consistent gets
              4  physical reads
              0  redo size
            519  bytes sent via SQL*Net to client
            465  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 63  
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ----------
      db file sequential read                     50529        0.50         96.08
    
    (b) 
    
    SELECT CableOperator.invoicecompanyid,CableOperator.OperatorRef,CableOperator.templateref
    FROM
     CableOperator WHERE CableOperator.Operatornum=:PREDVALUE0
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ------
    Parse        0      0.00       0.00          0          0          0           0
    Execute  78273      7.70       7.33          0          0          0           0
    Fetch    78273      4.72      38.54      20152     313094          0       78273
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total   156546     12.42      45.88      20152     313094          0       78273
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 63  
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                    78273        0.00          0.03
      SQL*Net message from client                 78273        0.00          8.81
      db file sequential read                           20152        0.06         35.04
    
    (3) Looking at the V$Sessionwait i found that the SIGNIFICANT single block I/O is waiting on following 2 segments 
    
    SEGMENT_NAME
    ---------------------------------------------------------------
    Invoice
    CableOperator
    (4) Both the segments are sitting on the same tablespace sharing the same physical disk


    Question:


    (1) The duration of the trace file was 30 minutes and the overall total wait time is 3 minutes.
    Referring to the first query, to fetch one record the consistnt gets is 27 - is not that high?
    (2) Is it a possibility of I/O problem in Hardware how the datafiles are configured?
    Last edited by tamilselvan; 09-05-2008 at 03:35 PM.

  2. #2
    Join Date
    May 2008
    Posts
    29
    Forgot to add

    while capturing p1 and p2 from v$syswait, I also captured the physicalreads and blockreads for those P1 from v$filemetric ..

    FILE_ID PHYSICAL_READS PHYSICAL_BLOCK_READS PHYSICAL_WRITES PHYSICAL_BLOCK_WRITES
    ---------- -------------- -------------------- --------------- ---------------------
    14 4240 4285 12 12
    14 4240 4285 12 12
    15 4493 4493 1 1
    15 4493 4493 1 1
    14 4240 4285 12 12




    I am curious to know why the PHYSICAL_READS & the PHYSICAL_BLOCK_READS are so high ... bcoz the waits were pretty frequent (in every couple of seconds).

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    modify your post using CODE tag, as it is difficult to read
    Best wishes!
    Dmitri

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    You executed the query
    SELECT Inv_ID FROM
    Invoice WHERE ((((SNUM=:PREDVALUE0) AND
    (SBQ=:PREDVALUE1)) AND
    (SBV1=:PREDVALUE2)) AND
    (STYPE=:PREDVALUE3)) AND
    (SReq=:PREDVALUE4)


    78273 times and it returned 78273 rows. What it means you are processing one row a time. And it did 600K IOs. Or I can say 6.7 IO per execution which is not bad.

    To improve the performance, use bulk collect option. Or change the logic such a way that you process the data in one or two SQL statements.

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