Home » RDBMS Server » Performance Tuning » High Disk Reads and FTS
High Disk Reads and FTS [message #166477] Thu, 06 April 2006 09:35 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #167542 is a reply to message #167541] Thu, 13 April 2006 13:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Let us make this simple first.
The posted query is not readable. Please format it properly and post again. Please post your explain plan along with statistics.
High disk reads may be as a result of HWM or even a improper query.
First Make sure you have collected the statistics for table/indexes with proper bucket size.
Re: High Disk Reads and FTS [message #168070 is a reply to message #167542] Tue, 18 April 2006 11:10 Go to previous message
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

Previous Topic: Large SGA
Next Topic: Re Update index
Goto Forum:
  


Current Time: Sun May 26 22:04:34 CDT 2024