|
-
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
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
|
Reply With Quote