-
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.
-
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
Best wishes!
Dmitri
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|