Home » RDBMS Server » Performance Tuning » High Disk Reads and FTS
| High Disk Reads and FTS [message #166477] |
Thu, 06 April 2006 09:35  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi
Below is the stats pack report and PLAN, This is doing lot of Reads which is the concern of other DBA, want us to tune this, what can be done, Well if you noticed IMG table and DOC table are undergoing FTS, those are actually huge table with 30-40 million rows, even though indexes are there on these tables , Optimizer is smart enough and not chossing index but doing FTS, the DBA's says if i could avoid FTS this will definately help reduce Reads, I suggested increasing Buffer Cache, they are telling " Buffer Cache will be increase if more buffer hit ratio, increasing Cache is not the solution for FTS and High Reads" , Any suggestion from you all there how i should move ahead on this,
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 58,835,146 555,048.5 .56
Disk Reads: 74,727,312 704,974.6 16.58
Rows processed: 789,829 7,451.2
CPU Time(s/ms): 45,510 429,342.8
Elapsed Time(s/ms): 51,683 487,576.1
Sorts: 424 4.0
Parse Calls: 106 1.0
Invalidations: 0
Version count: 1
Sharable Mem(K): 185
Executions: 106
SQL Text
~~~~~~~~
SELECT DISTINCT J.IMG_ID, V.CD_VOL_NB FROM TABLE (PKG_CD_EXTR.GE
TBATVOLTAB(:B1)) V, ( SELECT DISTINCT I.IMG_ID, B.BAT_ID FROM IM
G I, TXN T, DOC D, BAT B WHERE B.BAT_ID = T.BAT_ID AND D.TXN_ID
= T.TXN_ID AND I.IMG_ARC_IN = 0 AND (D.IMG_FRNT_ID = I.IMG_ID OR
D.IMG_REAR_ID = I.IMG_ID) AND B.BAT_ID IN (SELECT BAT_ID FROM T
ABLE (PKG_CD_EXTR.GETBATVOLTAB(:B1))) UNION SELECT DISTINCT I.IM
G_ID, B.BAT_ID FROM IMG I, TXN T, PYMT P, BAT B WHERE B.BAT_ID =
T.BAT_ID AND P.TXN_ID = T.TXN_ID AND I.IMG_ARC_IN = 0 AND (P.IM
G_FRNT_ID = I.IMG_ID OR (P.IMG_REAR_ID = I.IMG_ID AND :B2 = 1 ))
AND B.BAT_ID IN (SELECT BAT_ID FROM TABLE (PKG_CD_EXTR.GETBATVO
LTAB(:B1))) UNION SELECT DISTINCT I.IMG_ID, B.BAT_ID FROM IMG I,
TXN T, REF_ITEM R, BAT B WHERE B.BAT_ID = T.BAT_ID AND R.TXN_ID
= T.TXN_ID AND I.IMG_ARC_IN = 0 AND (R.IMG_FRNT_ID = I.IMG_ID O
R R.IMG_REAR_ID = I.IMG_ID) AND B.BAT_ID IN (SELECT BAT_ID FROM
TABLE (PKG_CD_EXTR.GETBATVOLTAB(:B1))) ) J WHERE J.BAT_ID = V.BA
T_ID ORDER BY V.CD_VOL_NB
All Optimizer Plan(s) for this Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this Hash value, and the Snap Id's they
were first found in the shared pool
-> ordered by Snap Id
Plan
Hash Value Snap Id Cost Optimizer
------------ -------- ---------- --------------------
2228672854 8476 258065 CHOOSE
2228672854 8702 283140 CHOOSE
2228672854 8891 317829 CHOOSE
Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value
--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 2228672854 ----| | | 258065 |
|SORT UNIQUE | | 9M| 262M| 205989 |
| HASH JOIN | | 9M| 262M| 153913 |
| COLLECTION ITERATOR PICKLER FE| | | | |
| VIEW | | 120K| 2M| 154895 |
| SORT UNIQUE | | 120K| 4M| 154895 |
| UNION-ALL | | | | |
| CONCATENATION | | | | |
| NESTED LOOPS | | 50K| 1M| 34849 |
| HASH JOIN | | 50K| 1M| 16988 |
| HASH JOIN | | 64K| 1M| 8660 |
| COLLECTION ITERATOR PIC| | | | |
| NESTED LOOPS | | 10M| 147M| 8170 |
| INDEX FAST FULL SCAN |XAK_TXN_BATID_TXNID | 10M| 98M| 4097 |
| INDEX UNIQUE SCAN |XPK_BAT | 1 | 5 | 1 |
| TABLE ACCESS FULL |DOC | 8M| 84M| 5485 |
| TABLE ACCESS BY INDEX ROW|IMG | 14M| 94M| 13765 |
| INDEX UNIQUE SCAN |XPK_IMG | 1 | | |
| HASH JOIN | | 50K| 1M| 34849 |
| HASH JOIN | | 50K| 1M| 16988 |
| HASH JOIN | | 64K| 1M| 8660 |
| COLLECTION ITERATOR PIC| | | | |
| NESTED LOOPS | | 10M| 147M| 8170 |
| INDEX FAST FULL SCAN |XAK_TXN_BATID_TXNID | 10M| 98M| 4097 |
| INDEX UNIQUE SCAN |XPK_BAT | 1 | 5 | 1 |
| TABLE ACCESS FULL |DOC | 8M| 84M| 5485 |
| TABLE ACCESS FULL |IMG | 14M| 94M| 13765 |
| CONCATENATION | | | | |
| FILTER | | | | |
| HASH JOIN | | 64K| 2M| 46954 |
| HASH JOIN | | 64K| 1M| 29080 |
| HASH JOIN | | 64K| 1M| 8660 |
| COLLECTION ITERATOR PI| | | | |
| NESTED LOOPS | | 10M| 147M| 8170 |
| INDEX FAST FULL SCAN |XAK_TXN_BATID_TXNID | 10M| 98M| 4097 |
| INDEX UNIQUE SCAN |XPK_BAT | 1 | 5 | 1 |
| TABLE ACCESS FULL |PYMT | 10M| 147M| 16163 |
| TABLE ACCESS FULL |IMG | 14M| 94M| 13765 |
| HASH JOIN | | 64K| 2M| 46954 |
| HASH JOIN | | 64K| 1M| 29080 |
| HASH JOIN | | 64K| 1M| 8660 |
| COLLECTION ITERATOR PIC| | | | |
| NESTED LOOPS | | 10M| 147M| 8170 |
| INDEX FAST FULL SCAN |XAK_TXN_BATID_TXNID | 10M| 98M| 4097 |
| INDEX UNIQUE SCAN |XPK_BAT | 1 | 5 | 1 |
| TABLE ACCESS FULL |PYMT | 10M| 147M| 16163 |
| TABLE ACCESS FULL |IMG | 14M| 94M| 13765 |
Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value
| CONCATENATION | | | | |
| NESTED LOOPS | | 1 | 31 | 15 |
| NESTED LOOPS | | 1 | 24 | 13 |
| NESTED LOOPS | | 1 | 14 | 12 |
| MERGE JOIN CARTESIAN | | 1 | 9 | 11 |
| COLLECTION ITERATOR PI| | | | |
| BUFFER SORT | | 1 | 7 | |
| TABLE ACCESS BY INDEX|REF_ITEM | 1 | 7 | |
| INDEX FULL SCAN |XIF_REFITM_IMGRERID | 1 | | |
| INDEX UNIQUE SCAN |XPK_BAT | 1 | 5 | 1 |
| INDEX UNIQUE SCAN |XAK_TXN_BATID_TXNID | 1 | 10 | 1 |
| TABLE ACCESS BY INDEX ROW|IMG | 1 | 7 | 2 |
| INDEX UNIQUE SCAN |XPK_IMG | 1 | | 1 |
| NESTED LOOPS | | 1 | 31 | 15 |
| NESTED LOOPS | | 1 | 24 | 13 |
| NESTED LOOPS | | 1 | 14 | 12 |
| MERGE JOIN CARTESIAN | | 1 | 9 | 11 |
| COLLECTION ITERATOR PI| | | | |
| BUFFER SORT | | 1 | 7 | |
| TABLE ACCESS BY INDEX|REF_ITEM | 1 | 7 | |
| INDEX FULL SCAN |XIF_REFITM_IMGFRNID | 1 | | |
| INDEX UNIQUE SCAN |XPK_BAT | 1 | 5 | 1 |
| INDEX UNIQUE SCAN |XAK_TXN_BATID_TXNID | 1 | 10 | 1 |
| TABLE ACCESS BY INDEX ROW|IMG | 1 | 7 | 2 |
| INDEX UNIQUE SCAN |XPK_IMG | 1 | | 1 |
--------------------------------------------------------------------------------
Thanks.
[Updated on: Thu, 06 April 2006 09:35] Report message to a moderator |
|
|
|
| Re: High Disk Reads and FTS [message #166986 is a reply to message #166477] |
Mon, 10 April 2006 13:04  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi,
Can i get some help on this case of High disk reads,
What is the area where we can look and tune if high disk reads are there, I know FTS is going on and those are huge tables, any other way to work around for High reads
Thanks
|
|
|
|
| Re: High Disk Reads and FTS [message #167541 is a reply to message #166986] |
Thu, 13 April 2006 13:27 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hello
Can you people please guide where to look for this issue for High Disk i don't know from where to start not getting any clue, does the query i posted above has some problem, what are the areas where i can concnetrate for tuning High Disk reads.
Thanks a lot.
|
|
|
|
|
|
| Re: High Disk Reads and FTS [message #168070 is a reply to message #167542] |
Tue, 18 April 2006 11:10 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks for respoding,
I tried to format the SQL code and STATS & Plan are from Production Database.
SELECT DISTINCT J.IMG_ID, V.CD_VOL_NB FROM TABLE
(PKG_CD_EXTR.GETBATVOLTAB(:B1)) V,
( SELECT DISTINCT I.IMG_ID, B.BAT_ID FROM
IMG I,
TXN T,
DOC D,
BAT B
WHERE B.BAT_ID = T.BAT_ID AND
D.TXN_ID = T.TXN_ID AND
I.IMG_ARC_IN = 0 AND
(D.IMG_FRNT_ID = I.IMG_ID OR
D.IMG_REAR_ID = I.IMG_ID) AND
B.BAT_ID IN (SELECT BAT_ID FROM TABLE (PKG_CD_EXTR.GETBATVOLTAB(:B1))) UNION SELECT DISTINCT I.IM
G_ID, B.BAT_ID FROM IMG I,
TXN T,
PYMT P,
BAT B WHERE B.BAT_ID = T.BAT_ID AND
P.TXN_ID = T.TXN_ID AND
I.IMG_ARC_IN = 0 AND
(P.IMG_FRNT_ID = I.IMG_ID OR (P.IMG_REAR_ID = I.IMG_ID AND :B2 = 1 ))
AND B.BAT_ID IN (SELECT BAT_ID FROM TABLE (PKG_CD_EXTR.GETBATVOLTAB(: B1)))
UNION
SELECT DISTINCT I.IMG_ID, B.BAT_ID FROM
IMG I, TXN T, REF_ITEM R, BAT B
WHERE B.BAT_ID = T.BAT_ID AND
R.TXN_ID = T.TXN_ID AND
I.IMG_ARC_IN = 0 AND
(R.IMG_FRNT_ID = I.IMG_ID O
R R.IMG_REAR_ID = I.IMG_ID) AND
B.BAT_ID IN (SELECT BAT_ID FROM
TABLE (PKG_CD_EXTR.GETBATVOLTAB(:B1))) ) J
WHERE J.BAT_ID = V.BA
T_ID ORDER BY V.CD_VOL_NB
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 58,835,146 555,048.5 .56
Disk Reads: 74,727,312 704,974.6 16.58
Rows processed: 789,829 7,451.2
CPU Time(s/ms): 45,510 429,342.8
Elapsed Time(s/ms): 51,683 487,576.1
Sorts: 424 4.0
Parse Calls: 106 1.0
Invalidations: 0
Version count: 1
Sharable Mem(K): 185
Executions: 106
PLAN OUT PUT
|SELECT STATEMENT |----- 2228672854 ----| | | 258065 |
|SORT UNIQUE | | 9M| 262M| 205989 |
| HASH JOIN | | 9M| 262M| 153913 |
| COLLECTION ITERATOR PICKLER FE| | | | |
| VIEW | | 120K| 2M| 154895 |
| SORT UNIQUE | | 120K| 4M| 154895 |
| UNION-ALL | | | | |
| CONCATENATION | | | | |
| NESTED LOOPS | | 50K| 1M| 34849 |
| HASH JOIN | | 50K| 1M| 16988 |
| HASH JOIN | | 64K| 1M| 8660 |
| COLLECTION ITERATOR PIC| | | | |
| NESTED LOOPS | | 10M| 147M| 8170 |
| INDEX FAST FULL SCAN |XAK_TXN_BATID_TXNID | 10M| 98M| 4097 |
| INDEX UNIQUE SCAN |XPK_BAT | 1 | 5 | 1 |
| TABLE ACCESS FULL |DOC | 8M| 84M| 5485 |
| TABLE ACCESS BY INDEX ROW|IMG | 14M| 94M| 13765 |
| INDEX UNIQUE SCAN |XPK_IMG | 1 | | |
| HASH JOIN | | 50K| 1M| 34849 |
| HASH JOIN | | 50K| 1M| 16988 |
| HASH JOIN | | 64K| 1M| 8660 |
| COLLECTION ITERATOR PIC| | | | |
| NESTED LOOPS | | 10M| 147M| 8170 |
| INDEX FAST FULL SCAN |XAK_TXN_BATID_TXNID | 10M| 98M| 4097 |
| INDEX UNIQUE SCAN |XPK_BAT | 1 | 5 | 1 |
| TABLE ACCESS FULL |DOC | 8M| 84M| 5485 |
| TABLE ACCESS FULL |IMG | 14M| 94M| 13765 |
| CONCATENATION | | | | |
| FILTER | | | | |
| HASH JOIN | | 64K| 2M| 46954 |
| HASH JOIN | | 64K| 1M| 29080 |
| HASH JOIN | | 64K| 1M| 8660 |
| COLLECTION ITERATOR PI| | | | |
| NESTED LOOPS | | 10M| 147M| 8170 |
| INDEX FAST FULL SCAN |XAK_TXN_BATID_TXNID | 10M| 98M| 4097 |
| INDEX UNIQUE SCAN |XPK_BAT | 1 | 5 | 1 |
| TABLE ACCESS FULL |PYMT | 10M| 147M| 16163 |
| TABLE ACCESS FULL |IMG | 14M| 94M| 13765 |
| HASH JOIN | | 64K| 2M| 46954 |
| HASH JOIN | | 64K| 1M| 29080 |
| HASH JOIN | | 64K| 1M| 8660 |
| COLLECTION ITERATOR PIC| | | | |
| NESTED LOOPS | | 10M| 147M| 8170 |
| INDEX FAST FULL SCAN |XAK_TXN_BATID_TXNID | 10M| 98M| 4097 |
| INDEX UNIQUE SCAN |XPK_BAT | 1 | 5 | 1 |
| TABLE ACCESS FULL |PYMT | 10M| 147M| 16163 |
| TABLE ACCESS FULL |IMG | 14M| 94M| 13765 |
Please give some areas where i can look into it, some body was HWM issue, some one is recommending increasing Buffer Cache not able to solve this High Disk read issue.
Thanks A Lot !
[Updated on: Tue, 18 April 2006 11:11] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Wed Apr 24 18:19:44 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
