How to optimize my query to reduce execution time

Question

SELECT
    XCRV.CROSS_REFERENCE JENIS
    ,
    XTD_INV_CONVERT_QTY_UOM_FNC (
        (select min(mcrv2.INVENTORY_ITEM_ID)
        from XTD_CROSS_REFF_ITEM_V mcrv2
        where 1=1
        and mcrv2.cross_reference = XCRV.CROSS_REFERENCE
        and mcrv2.organization_id = MMT.ORGANIZATION_ID) ,
        MMT.ORGANIZATION_ID,
        (SELECT NVL(SUM(MMT2.PRIMARY_QUANTITY), 0)
        FROM
        MTL_MATERIAL_TRANSACTIONS MMT2,
        XTD_CROSS_REFF_ITEM_V XCRV2
        WHERE 
            MMT2.ORGANIZATION_ID = XCRV2.ORGANIZATION_ID
        AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
        AND XCRV2.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
        AND TRUNC(MMT2.TRANSACTION_DATE) <= TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' ) ) 
        ),
        MSIB.PRIMARY_UOM_CODE,
        'BAL'
    ) || '-' || XTD_INV_CONVERT_QTY_UOM_FNC (
        (select min(mcrv2.INVENTORY_ITEM_ID)
        from XTD_CROSS_REFF_ITEM_V mcrv2
        where 1=1
        and mcrv2.cross_reference = XCRV.CROSS_REFERENCE
        and mcrv2.organization_id = MMT.ORGANIZATION_ID) ,
        MMT.ORGANIZATION_ID,
        (SELECT NVL(SUM(MMT2.PRIMARY_QUANTITY), 0)
        FROM
        MTL_MATERIAL_TRANSACTIONS MMT2,
        XTD_CROSS_REFF_ITEM_V XCRV2
        WHERE 
            MMT2.ORGANIZATION_ID = XCRV2.ORGANIZATION_ID
        AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
        AND XCRV2.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
        AND TRUNC(MMT2.TRANSACTION_DATE) <= TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' ) ) 
        ),
        MSIB.PRIMARY_UOM_CODE,
        'PRS'
    ) || '-' || XTD_INV_CONVERT_QTY_UOM_FNC (
        (select min(mcrv2.INVENTORY_ITEM_ID)
        from XTD_CROSS_REFF_ITEM_V mcrv2
        where 1=1
        and mcrv2.cross_reference = XCRV.CROSS_REFERENCE
        and mcrv2.organization_id = MMT.ORGANIZATION_ID) ,
        MMT.ORGANIZATION_ID,
        (SELECT NVL(SUM(MMT2.PRIMARY_QUANTITY), 0)
        FROM
        MTL_MATERIAL_TRANSACTIONS MMT2,
        XTD_CROSS_REFF_ITEM_V XCRV2
        WHERE 
            MMT2.ORGANIZATION_ID = XCRV2.ORGANIZATION_ID
        AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
        AND XCRV2.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
        AND TRUNC(MMT2.TRANSACTION_DATE) <= TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' ) ) 
        ),
        MSIB.PRIMARY_UOM_CODE,
        'BKS'
    ) AS SALDO_AWAL
FROM
    MTL_MATERIAL_TRANSACTIONS MMT,
    MTL_TRANSACTION_TYPES MTT,
    MTL_SYSTEM_ITEMS_B MSIB,
    XTD_CROSS_REFF_ITEM_V XCRV,
    ORG_ORGANIZATION_DEFINITIONS OOD,
    HR_OPERATING_UNITS HOU,
    GL_LEDGERS GL
WHERE
    MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
    AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
    AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
    AND MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
    AND XCRV.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
    AND HOU.BUSINESS_GROUP_ID = OOD.BUSINESS_GROUP_ID
    AND OOD.SET_OF_BOOKS_ID = GL.LEDGER_ID
    --HARDCODE
    AND HOU.ORGANIZATION_ID NOT IN ('82')
    AND XCRV.CROSS_REFERENCE = 'ARB12'
    -- PARAMETERS
    AND GL.LEDGER_ID = NVL(:P_LEDGER, GL.LEDGER_ID)
    AND HOU.ORGANIZATION_ID = NVL(:P_OU_ID, HOU.ORGANIZATION_ID)
    AND OOD.ORGANIZATION_ID = NVL(:P_CABANG, OOD.ORGANIZATION_ID)
    -- HEADING
    AND TRUNC(MMT.TRANSACTION_DATE) BETWEEN TRUNC( TO_DATE( :P_DATE_FROM, 'YYYY/MM/DD' ))  AND TRUNC( TO_DATE( :P_DATE_TO, 'YYYY/MM/DD' )) 
GROUP BY
    XCRV.CROSS_REFERENCE,
    MMT.ORGANIZATION_ID,
    MSIB.PRIMARY_UOM_CODE
ORDER BY
    XCRV.CROSS_REFERENCE

Please optimize my query snippet. When hardcoding only one piece of data, it takes about 4 minutes to execute. However, there are still many data to be loaded, and I also have other columns to add.

When I try to run the query for just one piece of data, it takes about 4 minutes from the attached query. However, there are still many more data to be loaded from the database. For the result, I want to achieve a short execution time.

Answer 1

You have many repeated sub-queries. You can improve performance by not repeating them and calculating the values only once in the JOIN clause rather than multiple times in every row in the WHERE clause:

For example, by using a LATERAL JOIN which is available from Oracle 12 (untested as you provided no sample data or code for the functions you are calling):

SELECT XCRV.CROSS_REFERENCE JENIS,
       XTD_INV_CONVERT_QTY_UOM_FNC(
         XCRV.min_inventory_item_idm
         mii.ORGANIZATION_ID,
         tpq.total_primary_quantity,
         MSIB.PRIMARY_UOM_CODE,
         'BAL'
       ) || '-' || XTD_INV_CONVERT_QTY_UOM_FNC (
         XCRV.min_inventory_item_idm
         mii.ORGANIZATION_ID,
         tpq.total_primary_quantity,
         MSIB.PRIMARY_UOM_CODE,
         'PRS'
       ) || '-' || XTD_INV_CONVERT_QTY_UOM_FNC (
         XCRV.min_inventory_item_idm
         mii.ORGANIZATION_ID,
         tpq.total_primary_quantity,
         MSIB.PRIMARY_UOM_CODE,
         'BKS'
       ) AS SALDO_AWAL
FROM   MTL_MATERIAL_TRANSACTIONS MMT
       INNER JOIN XTD_CROSS_REFF_ITEM_V xcrv
       CROSS JOIN LATERAL (
         select min(mcrv2.INVENTORY_ITEM_ID) AS min_inventory_item_id
         from   XTD_CROSS_REFF_ITEM_V cr
         where  cr.cross_reference = XCRV.CROSS_REFERENCE
         and    cr.organization_id = MMT.ORGANIZATION_ID
       ) miii
       CROSS JOIN LATERAL (
         SELECT COALESCE(SUM(MMT2.PRIMARY_QUANTITY), 0) AS total_primary_quantity
         FROM   MTL_MATERIAL_TRANSACTIONS MMT2
                INNER JOIN XTD_CROSS_REFF_ITEM_V XCRV2
                ON    MMT2.ORGANIZATION_ID   = XCRV2.ORGANIZATION_ID
                  AND MMT2.INVENTORY_ITEM_ID = XCRV2.INVENTORY_ITEM_ID
         WHERE  XCRV2.CROSS_REFERENCE = XCRV.CROSS_REFERENCE
         AND    MMT2.TRANSACTION_DATE < TO_DATE(:P_DATE_FROM, 'YYYY/MM/DD') + 1
       ) tpq
       INNER JOIN MTL_TRANSACTION_TYPES MTT
       ON     MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
       INNER JOIN MTL_SYSTEM_ITEMS_B MSIB
       ON     MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
          AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
       ON     XCRV.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
       INNER JOIN ORG_ORGANIZATION_DEFINITIONS OOD
       ON     MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
       INNER JOIN HR_OPERATING_UNITS HOU
       ON     HOU.BUSINESS_GROUP_ID = OOD.BUSINESS_GROUP_ID
       INNER JOIN GL_LEDGERS GL
       ON     OOD.SET_OF_BOOKS_ID = GL.LEDGER_ID
WHERE  --HARDCODE
       HOU.ORGANIZATION_ID NOT IN ('82')
AND    XCRV.CROSS_REFERENCE = 'ARB12'
       -- PARAMETERS
AND    (:P_LEDGER IS NULL OR GL.LEDGER_ID        = :P_LEDGER)
AND    (:P_OU_ID  IS NULL OR HOU.ORGANIZATION_ID = :P_OU_ID)
AND    (:P_CABANG IS NULL OR OOD.ORGANIZATION_ID = :P_CABANG)
       -- HEADING
AND    MMT.TRANSACTION_DATE >= TO_DATE(:P_DATE_FROM, 'YYYY/MM/DD')
AND    MMT.TRANSACTION_DATE <  TO_DATE(:P_DATE_TO,   'YYYY/MM/DD') + 1
GROUP BY
       XCRV.CROSS_REFERENCE,
       MMT.ORGANIZATION_ID,
       MSIB.PRIMARY_UOM_CODE
ORDER BY
       XCRV.CROSS_REFERENCE

You can also directly use MMT.TRANSACTION_DATE (rather than TRUNC(MMT.TRANSACTION_DATE)) which may allow Oracle to use an index on that column; otherwise you would need to use a function-based index on TRUNC(MMT.TRANSACTION_DATE).