CODEWITHSUNDEEP
oracle-databasesql-execution-plansqlperformance
Rich Bianco
Rich Bianco

Reputation: 4174

Analysis of Oracle Explain Plan - Oracle Doing Full Table Access

We have a problematic SQL and in looking at the Explain Plan there appears to be a lot of Full Table Access. From what I understand this is normal especially if the query is more on the inside of the plan versus being at the top.

Each of the tables have indexes on them and proper columns used so it is not clear why Oracle will not use the indexes. Can anyone provide high level guidance as to whether I should expect so many full table scans, or does this SQL need better optimization? We are running out of memory in Oracle often, removing the SORT does help on lowering the cost but not memory.

SQL

SELECT
     TRNLOAD.WORK_ORDER_NUMBER,
     TRNMANIFESTHDR.CUST_MANIFEST_NUMBER,
     TRNMANIFESTHDR.STATE_MANIFEST_NUMBER,
     TRNINVENTORY.MAN_PAGE_NO,
     ...snipped columns for size
     TRNINVENTORY.MAN_SYS_NUMBER,
     NVL(REFINVSPLITCODES.DOUBLE_COUNT,'N') double_count,
     MONTHS_BETWEEN(SYSDATE,NVL(TRNLOAD.CHECKIN_TIME, TRNINVENTORY.GROUPED_DATE)) months,
     AESOP.DF_GETINVCOLORCODE 
        (TRNINVENTORY.INV_STATUS,
         TRNLOAD.CHECKOUT_TIME,
         DF_IS_ONSITECUST(TRNMANIFESTHDR.LOC_CODE,TRNMANIFESTHDR.BILLING_CUSTOMER))
              color_cd,
     TRNINVENTORY.CREATED_BY,
     TRNMANIFESTDETAIL.EPA_CONSENT_NUMBER 
FROM REFGENERATOR,
     TRNINVENTORY,
     TRNLOAD,
     TRNMANIFESTDETAIL,
     TRNMANIFESTHDR,
     REFWASTESTREAM,
     REFRTTAXCODES,
     REFINVENTORYSTATUS,
     REFINVSPLITCODES,
     REFHANDLINGCODES,
     reftreatmentgroup,
     TRNMANIFRETAILTRIP  trip
WHERE    (TRNMANIFESTDETAIL.LOC_CODE(+) = TRNINVENTORY.LOC_CODE)
     AND (TRNMANIFESTDETAIL.MAN_SYS_NUMBER(+) = TRNINVENTORY.MAN_SYS_NUMBER)
     AND (TRNMANIFESTDETAIL.MAN_PAGE_NUMBER(+) = TRNINVENTORY.MAN_PAGE_NO)
     AND (TRNMANIFESTDETAIL.MAN_LINE_NUMBER(+) = TRNINVENTORY.MAN_LINE_NO)
     AND (TRNMANIFESTHDR.LOC_CODE(+) = TRNMANIFESTDETAIL.LOC_CODE)
     AND (TRNMANIFESTHDR.MAN_SYS_NUMBER(+) = TRNMANIFESTDETAIL.MAN_SYS_NUMBER)
     AND (TRNMANIFESTHDR.MAN_PAGE_NO(+) = TRNMANIFESTDETAIL.USER_MANPAGE)
     AND (TRNMANIFESTHDR.LOC_CODE = TRNLOAD.LOC_CODE(+))
     AND (TRNMANIFESTHDR.WORK_ORDER_NUMBER = TRNLOAD.WORK_ORDER_NUMBER(+))
     AND (TRNMANIFESTHDR.LOC_CODE = REFGENERATOR.LOC_CODE(+))
     AND (TRNMANIFESTHDR.GEN_SYS_NUMBER = REFGENERATOR.GEN_SYS_NUMBER(+))
     AND (TRNMANIFESTDETAIL.LOC_CODE = REFWASTESTREAM.LOC_CODE(+))
     AND (TRNMANIFESTDETAIL.WASTE_STREAM_NUMBER = REFWASTESTREAM.WASTE_STREAM_NUMBER(+))
     AND (TRNMANIFESTDETAIL.PROFILE_NUMBER = REFWASTESTREAM.PROFILE_NUMBER(+))
     AND (REFHANDLINGCODES.LOC_CODE(+) = TRNMANIFESTDETAIL.LOC_CODE)
     AND (REFHANDLINGCODES.WASTE_STREAM_NUMBER(+) = TRNMANIFESTDETAIL.WASTE_STREAM_NUMBER)
     AND (REFHANDLINGCODES.PROFILE_NUMBER(+) = TRNMANIFESTDETAIL.PROFILE_NUMBER)
     AND (refhandlingcodes.loc_code = reftreatmentgroup.loc_code(+))
     AND (refhandlingcodes.tgroup = reftreatmentgroup.tgroup(+))
     AND (REFINVENTORYSTATUS.ACTION_ID = TRNINVENTORY.INV_STATUS)
     AND (trninventory.split_code = refinvsplitcodes.code(+))
     AND (trninventory.loc_code = refrttaxcodes.loc_code(+))
     AND (trninventory.inv_taxcode = REFRTTAXCODES.TAX_ID(+))
     AND ( (TRNINVENTORY.LOC_CODE = :ai_loc))
     and (trip.loc_code (+) = trninventory.loc_code)
     and (trip.inventory_no (+) = trninventory.inventroty_no)
     and (trip.split_no (+) = trninventory.split_no)
     AND NOT (NVL (TRNINVENTORY.IS_PRODUCT, 'N') = 'Y' OR NVL (REFWASTESTREAM.WASTE_TYPE, 'A') = 'D')
     AND nvl(TRNLOAD.WORK_ORDER_TYPE,'STD') = 'STD' 
ORDER BY TRNLOAD.WORK_ORDER_NUMBER,
     TRNINVENTORY.MAN_SYS_NUMBER,
     TRNMANIFESTDETAIL.USER_MANPAGE,
     DF_sort_string (TRNMANIFESTDETAIL.USER_MANLINE),
     TRNINVENTORY.INVENTROTY_NO,
     TRNINVENTORY.SPLIT_NO,
     TRNINVENTORY.GROUPED_DATE,
     TRNINVENTORY.GROUP_NO

EXPLAIN PLAN

I had to adjust this to be more narrow for readability, which is why there are some abbreviations. 

------------------------------------------------------------------------------------------
| Id | Operation                              | Name                | Rows | Bytes| Cost |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                       |                     |  377K|  198M|  112K|
|  1 | SORT ORDER BY                          |                     |  377K|  198M|  112K|
|  2 |  HASH JOIN RIGHT OUTER                 |                     |  377K|  198M| 68645|
|  3 |   TBL ACCESS FULL                      | TRNMANIFRETAILTRIP  |  128 | 2944 |    3 |
|  4 |   HASH JOIN RIGHT OUTER                |                     |  377K|  190M|68639 |
|  5 |    TBL ACCESS FULL                     | REFTREATMENTGROUP   |  101 | 1313 |    3 |
|  6 |    FILTER                              |                     |      |      |      |
|  7 |     HASH JOIN RIGHT OUTER              |                     |  377K|  186M|68633 |
|  8 |      TBL ACCESS FULL                   | REFWASTESTREAM      |  204K|   11M|  755 |
|  9 |      HASH JOIN RIGHT OUTER             |                     |  377K|  163M|58820 |
| 10 |       TBL ACCESS FULL                  | REFGENERATOR        | 80447| 3535K|  594 |
| 11 |       FILTER                           |                     |      |      |      |
| 12 |        HASH JOIN RIGHT OUTER           |                     |  377K|  147M|50460 |
| 13 |         TBL ACCESS FULL                | TRNLOAD             |  507K|   16M|2920  |
| 14 |         HASH JOIN RIGHT OUTER          |                     |  376K|  135M|39501 |
| 15 |          TBL ACCESS FULL               | TRNMANIFESTHDR      |  844K|   49M| 4646 |
| 16 |          HASH JOIN RIGHT OUTER         |                     |  376K|  113M|26090 |
| 17 |           TBL ACCESS FULL              | REFHANDLINGCODES    |  183K| 4483K|  631 |
| 18 |           HASH JOIN RIGHT OUTER        |                     |  376K|  104M|19737 |
| 19 |            TBL ACCESS FULL             | TRNMANIFESTDETAIL   |  289K|   14M| 4496 |
| 20 |            HASH JOIN RIGHT OUTER       |                     |  376K|   85M| 9890 |
| 21 |             TBL ACCESS FULL            | REFINVSPLITCODES    |   48 |  288 |    3 |
| 22 |             HASH JOIN RIGHT OUTER      |                     |  376K|   83M| 9883 |
| 23 |              TBL ACCESS BY INDEX ROWID | REFRTTAXCODES       |    8 |   80 |    3 |
| 24 |               INDEX RANGE SCAN         | PK_TAXCODE_LOCATION |    8 |      |    1 |
| 25 |              HASH JOIN                 |                     |  376K|   80M| 9877 |
| 26 |               INDEX FULL SCAN          | IDX_INVSTATUS_TYPE  |   22 |  176 |    1 |
| 27 |               TBL ACCESS FULL          | TRNINVENTORY        |  376K|   77M| 9873 |
------------------------------------------------------------------------------------------

Upvotes: 0

Views: 1267

Answers (1)

Roger Cornejo
Roger Cornejo

Reputation: 1547

Q: Can anyone provide high level guidance as to whether I should expect so many full table scans, or does this SQL need better optimization?

A: there are very few places where the data is being subset, that is the first place to look for index tuning opportunities, but I see functions applied to the column which may imply the need for a function based index.

The next place to look is the join conditions, are the join columns indexed. Oracle can choose to ignore indexes on joined columns if it thinks full table scan is just as efficient or more efficient.

Generally, I'd say don't worry about full table scans, except, I would worry about full table scans if the following are true:

  1. the performance requirements are not being met
  2. the full scans are on large tables
  3. the wait events for the session running the SQL show high waits on "db file scattered read" for the large object in question (i.e. full table scan)

Upvotes: 1

Related Questions