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?

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

modify your post using CODE tag, as it is difficult to read

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.