Forum Stats

  • 3,758,159 Users
  • 2,251,346 Discussions
  • 7,870,074 Comments

Discussions

Categories

Optimalize SQL Query Oracle R12

2»

Answers

  • SeánMacGC
    SeánMacGC Member Posts: 2,914 Gold Trophy
    edited May 23, 2018 3:09AM

    No, not necessarily.

    The key thing here is that the estimated 'Rows' value is reasonably accurate -- if that is badly out, then the optimizer can make the wrong choice, resulting in a suboptimal execution step (like opting for a Nested Loop Join where a Hash Join may have been much better), and that's why it helps to see both the 'estimated' and 'actual' row counts at each step of the execution plan. Up-to-date statistics play a central role here, but it's also possible that other factors are at play, like conditions spanning multiple columns of a single table, or a pipelined function, etc.

    In your plan you have a series of Nested Loop Joins, for example, and perhaps those might  be better remodelled alternatively, and also bear in mind that with 12c (and 11gr2), execution plans can change from one invocation to the next, due to the adaptive feedback mechanisms.

    · Share on TwitterShare on Facebook
  • 3706549
    3706549 Member Posts: 15
    edited May 23, 2018 3:55AM

    OK,

    I think I got my excepted result thanks for EXPLAIN PLAN FOR (I have never used it before):

    Have you any idea how to make it little bit better? Some join I missed?

    end.png

    EXPLAIN PLAN FOR

        SELECT

        gbh.batch_type,

        gbh.batch_no,

        gbh.batch_id,

        msi.segment1 as ITEM,

        msi.description,

        lot.lot_number as LOT,

        gmd.line_type,

         msi.inventory_item_id

        ,fmd.attribute1 as SIGNIFICANT_ITEM

        FROM

        gme.gme_batch_header gbh,

        gme.gme_material_details gmd,

        --inv.mtl_material_transactions mmt,

        inv.mtl_system_items_b msi,

        inv.mtl_transaction_lot_numbers lot

        ,GMD.fm_matl_dtl fmd

        WHERE 1=1

        AND gbh.batch_id = gmd.batch_id

        AND gbh.organization_id = gmd.organization_id

        AND gbh.formula_id = fmd.formula_id

        AND msi.inventory_item_id = fmd.inventory_item_id

        AND msi.organization_id = fmd.organization_id

        AND gmd.LINE_TYPE = fmd.LINE_TYPE

        AND gmd.LINE_NO = fmd.LINE_NO --MOZNA BUDE POTREBA ODSTRANIT!

        AND gmd.organization_id = msi.organization_id

        AND lot.transaction_source_id = gbh.batch_id

        AND lot.organization_id = gmd.organization_id

        AND lot.inventory_item_id = gmd.inventory_item_id

        --AND lot.lot_number = '1EP17171590' AND msi.inventory_item_id = 1765716

        AND gbh.batch_type = 0

       -- AND gbh.BATCH_STATUS in (3, 4) --4 CLOSED, 3-COMPLETED

        AND gbh.BATCH_STATUS = 4

        AND gbh.organization_id = 1509

    · Share on TwitterShare on Facebook
  • SeánMacGC
    SeánMacGC Member Posts: 2,914 Gold Trophy
    edited May 23, 2018 4:09AM

    No, not really, because this is not an execution plan (what actually happened), it's an explain plan (what the database thinks will happen), and without knowing the details here it's very difficult to estimate accurately. For example, it might be that the Full Table Scan on FM_MATL_DTL is suboptimal, but it's not possible to say definitively without seeing the bigger picture.

    · Share on TwitterShare on Facebook
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited May 23, 2018 4:10AM 
    3706549 wrote:Cookiemonster76,There is the list of columns which are indexed.

    I know there's a list - I referred to it in reply #6 when I pointed out it needs to include index names so we can see which columns are in which index.

    If you are accessing a table using three columns then a single index on all three columns is going to be much more useful than three indexes each covering just one of the three columns.

    We can't tell which of those cases apply here because you haven't supplied index names as part of the list.

    · Share on TwitterShare on Facebook
  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    edited May 23, 2018 4:43AM 
    SeánMacGC wrote:3706549 wrote:SeánMacGC,I tried you suggestions and I think I got even worst result:Not necessarily, there's something else at play here; please run:SELECT /*+ gather_plan_statistics*/ ... then this immediately after the query runs: SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST'));And post the output here.

    3706549 - Please do this.

    · Share on TwitterShare on Facebook
  • 3706549
    3706549 Member Posts: 15
    edited May 23, 2018 7:39AM

    SeanMacGC,

    you are right, maybe I should remove this table and use it in final result.

    Cookiemonster,

    can you give me some example which join will be better than? I did not know that about indexes.

    See list below:

        

    COLUMN_NAMEUNIQUENESSINDEX_NAMETABLE_NAME
    ITEM_IDNONUNIQUEFM_MATL_DTLI1FM_MATL_DTL
    LINE_TYPENONUNIQUEFM_MATL_DTLI1FM_MATL_DTL
    FORMULA_IDNONUNIQUEFM_MATL_DTLI1FM_MATL_DTL
    FORMULALINE_IDUNIQUEFM_MATL_DTL_PKFM_MATL_DTL
    FORMULA_IDUNIQUEFM_MATL_DTL_U1FM_MATL_DTL
    LINE_TYPEUNIQUEFM_MATL_DTL_U1FM_MATL_DTL
    LINE_NOUNIQUEFM_MATL_DTL_U1FM_MATL_DTL
    ORGANIZATION_IDNONUNIQUEGME_BATCH_HEADER_N1GME_BATCH_HEADER
    BATCH_NONONUNIQUEGME_BATCH_HEADER_N1GME_BATCH_HEADER
    BATCH_STATUSNONUNIQUEGME_BATCH_HEADER_N1GME_BATCH_HEADER
    BATCH_CLOSE_DATENONUNIQUEGME_BATCH_HEADER_N2GME_BATCH_HEADER
    BATCH_IDUNIQUEGME_BATCH_HEADER_PKGME_BATCH_HEADER
    ORGANIZATION_IDNONUNIQUEGME_BATCH_HEADER_U1GME_BATCH_HEADER
    BATCH_NONONUNIQUEGME_BATCH_HEADER_U1GME_BATCH_HEADER
    BATCH_TYPENONUNIQUEGME_BATCH_HEADER_U1GME_BATCH_HEADER
    ORGANIZATION_IDNONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
    INVENTORY_ITEM_IDNONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
    LINE_TYPENONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
    BATCH_IDNONUNIQUEGME_MATERIAL_DETAILS_N1GME_MATERIAL_DETAILS
    PHANTOM_IDNONUNIQUEGME_MATERIAL_DETAILS_N2GME_MATERIAL_DETAILS
    MATERIAL_DETAIL_IDUNIQUEGME_MATERIAL_DETAILS_PKGME_MATERIAL_DETAILS
    BATCH_IDUNIQUEGME_MATERIAL_DETAILS_U1GME_MATERIAL_DETAILS
    LINE_NOUNIQUEGME_MATERIAL_DETAILS_U1GME_MATERIAL_DETAILS
    LINE_TYPEUNIQUEGME_MATERIAL_DETAILS_U1GME_MATERIAL_DETAILS
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N1MTL_SYSTEM_ITEMS_B
    SEGMENT1NONUNIQUEMTL_SYSTEM_ITEMS_B_N1MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N10MTL_SYSTEM_ITEMS_B
    PLANNER_CODENONUNIQUEMTL_SYSTEM_ITEMS_B_N10MTL_SYSTEM_ITEMS_B
    WEB_STATUSNONUNIQUEMTL_SYSTEM_ITEMS_B_N11MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N11MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
    CUSTOMER_ORDER_ENABLED_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
    SERVICE_ITEM_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
    VENDOR_WARRANTY_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
    USAGE_ITEM_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N12MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N13MTL_SYSTEM_ITEMS_B
    WIP_SUPPLY_LOCATOR_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N13MTL_SYSTEM_ITEMS_B
    BASE_ITEM_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N14MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N14MTL_SYSTEM_ITEMS_B
    STYLE_ITEM_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N15MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N15MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N16MTL_SYSTEM_ITEMS_B
    EAM_ITEM_TYPENONUNIQUEMTL_SYSTEM_ITEMS_B_N16MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N2MTL_SYSTEM_ITEMS_B
    DESCRIPTIONNONUNIQUEMTL_SYSTEM_ITEMS_B_N2MTL_SYSTEM_ITEMS_B
    INVENTORY_ITEM_STATUS_CODENONUNIQUEMTL_SYSTEM_ITEMS_B_N3MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N4MTL_SYSTEM_ITEMS_B
    AUTO_CREATED_CONFIG_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N4MTL_SYSTEM_ITEMS_B
    WH_UPDATE_DATENONUNIQUEMTL_SYSTEM_ITEMS_B_N5MTL_SYSTEM_ITEMS_B
    ITEM_CATALOG_GROUP_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N6MTL_SYSTEM_ITEMS_B
    CATALOG_STATUS_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N6MTL_SYSTEM_ITEMS_B
    PRODUCT_FAMILY_ITEM_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N7MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N7MTL_SYSTEM_ITEMS_B
    COMMS_NL_TRACKABLE_FLAGNONUNIQUEMTL_SYSTEM_ITEMS_B_N8MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N8MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N9MTL_SYSTEM_ITEMS_B
    BUYER_IDNONUNIQUEMTL_SYSTEM_ITEMS_B_N9MTL_SYSTEM_ITEMS_B
    INVENTORY_ITEM_IDUNIQUEMTL_SYSTEM_ITEMS_B_U1MTL_SYSTEM_ITEMS_B
    ORGANIZATION_IDUNIQUEMTL_SYSTEM_ITEMS_B_U1MTL_SYSTEM_ITEMS_B
    TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N1MTL_TRANSACTION_LOT_NUMBERS
    ORGANIZATION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N2MTL_TRANSACTION_LOT_NUMBERS
    TRANSACTION_DATENONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N2MTL_TRANSACTION_LOT_NUMBERS
    LOT_NUMBERNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N3MTL_TRANSACTION_LOT_NUMBERS
    INVENTORY_ITEM_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N3MTL_TRANSACTION_LOT_NUMBERS
    ORGANIZATION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N3MTL_TRANSACTION_LOT_NUMBERS
    TRANSACTION_SOURCE_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N4MTL_TRANSACTION_LOT_NUMBERS
    PRODUCT_CODENONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N5MTL_TRANSACTION_LOT_NUMBERS
    PRODUCT_TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N5MTL_TRANSACTION_LOT_NUMBERS
    SERIAL_TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N7MTL_TRANSACTION_LOT_NUMBERS
    TRANSACTION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N7MTL_TRANSACTION_LOT_NUMBERS
    ORGANIZATION_IDNONUNIQUEMTL_TRANSACTION_LOT_NUMBERS_N7MTL_TRANSACTION_LOT_NUMBERS
    · Share on TwitterShare on Facebook
This discussion has been closed.