1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL taking time

Discussion in 'SQL PL/SQL' started by nishant87, Dec 22, 2013.

  1. nishant87

    nishant87 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    There is one sql that is taking much time.

    /* Formatted on 2013/12/22 19:46 (Formatter Plus v4.8.8) */
    SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7) */
    aid.invoice_id, gcc.segment1, aid.line_type_lookup_code,
    SUM (NVL (aid.base_amount, aid.amount)) amount
    FROM ap_invoice_distributions aid, gl_code_combinations gcc
    WHERE aid.org_id = 4719
    AND aid.line_type_lookup_code = 'ITEM'
    AND NVL (aid.base_amount, aid.amount) != 0
    AND aid.accounting_date BETWEEN '01-JAN-2011' AND '31-JAN-2011'
    AND aid.accounting_date <= '21-NOV-2013'
    AND gcc.code_combination_id = aid.dist_code_combination_id
    AND aid.invoice_id IN (
    SELECT ai.invoice_id
    FROM ap_invoices ai
    WHERE NVL (NULL, ai.vendor_id) = ai.vendor_id
    AND NVL (NULL, ai.invoice_type_lookup_code) =
    ai.invoice_type_lookup_code
    AND ( ai.cancelled_date IS NULL
    OR ( ai.cancelled_date IS NOT NULL
    AND ai.cancelled_date > '21-NOV-2013'
    )
    ))
    GROUP BY aid.invoice_id, gcc.segment1, aid.line_type_lookup_code
    HAVING SUM (NVL (aid.base_amount, aid.amount)) != 0
    UNION ALL
    SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7)
    */
    aid.invoice_id, gcc.segment1, aid.line_type_lookup_code,
    SUM (NVL (aid.base_amount, aid.amount)) amount
    FROM ap_invoice_distributions aid, gl_code_combinations gcc
    WHERE aid.org_id = 4719
    AND aid.line_type_lookup_code = 'TAX'
    AND NVL (aid.base_amount, aid.amount) != 0
    AND aid.accounting_date BETWEEN '01-JAN-2011' AND '31-JAN-2011'
    AND aid.accounting_date <= '21-NOV-2013'
    AND gcc.code_combination_id = aid.dist_code_combination_id
    AND aid.invoice_id IN (
    SELECT ai.invoice_id
    FROM ap_invoices ai
    WHERE NVL (NULL, ai.vendor_id) = ai.vendor_id
    AND NVL (NULL, ai.invoice_type_lookup_code) =
    ai.invoice_type_lookup_code
    AND ( ai.cancelled_date IS NULL
    OR ( ai.cancelled_date IS NOT NULL
    AND ai.cancelled_date > '21-NOV-2013'
    )
    ))
    AND ( NOT EXISTS (
    SELECT 1
    FROM ap_invoice_distributions aid2
    WHERE aid2.invoice_id = aid.invoice_id
    AND aid2.line_type_lookup_code IN
    ('WTH', 'ITEM', 'PREPAY')
    AND aid2.accounting_date <= '21-NOV-2013')
    OR EXISTS (
    SELECT SUM (NVL (aid8.base_amount, aid8.amount))
    FROM ap_invoice_distributions aid8
    WHERE aid8.invoice_id = aid.invoice_id
    AND aid8.line_type_lookup_code = 'ITEM'
    AND aid8.accounting_date <= '21-NOV-2013'
    GROUP BY aid8.line_type_lookup_code
    HAVING SUM (NVL (aid8.base_amount, aid8.amount)) = 0)
    )
    GROUP BY aid.invoice_id, gcc.segment1, aid.line_type_lookup_code
    HAVING SUM (NVL (aid.base_amount, aid.amount)) != 0
    UNION ALL
    SELECT /*+ INDEX_ASC ( aid
    AP_INVOICE_DISTRIBUTIONS_X7) */
    aid.invoice_id, gcc.segment1, aid.line_type_lookup_code,
    SUM (NVL (aid.base_amount, aid.amount)) amount
    FROM ap_invoice_distributions aid, gl_code_combinations gcc
    WHERE aid.org_id = 4719
    AND aid.line_type_lookup_code = 'FREIGHT'
    AND NVL (aid.base_amount, aid.amount) != 0
    AND aid.accounting_date BETWEEN '01-JAN-2011' AND '31-JAN-2011'
    AND aid.accounting_date <= '21-NOV-2013'
    AND gcc.code_combination_id = aid.dist_code_combination_id
    AND aid.invoice_id IN (
    SELECT ai.invoice_id
    FROM ap_invoices ai
    WHERE NVL (NULL, ai.vendor_id) = ai.vendor_id
    AND NVL (NULL, ai.invoice_type_lookup_code) =
    ai.invoice_type_lookup_code
    AND ( ai.cancelled_date IS NULL
    OR ( ai.cancelled_date IS NOT NULL
    AND ai.cancelled_date > '21-NOV-2013'
    )
    ))
    AND ( NOT EXISTS (
    SELECT 1
    FROM ap_invoice_distributions aid2
    WHERE aid2.invoice_id = aid.invoice_id
    AND aid2.line_type_lookup_code IN
    ('WTH', 'ITEM', 'PREPAY')
    AND aid2.accounting_date <= '21-NOV-2013')
    OR EXISTS (
    SELECT SUM (NVL (aid8.base_amount, aid8.amount))
    FROM ap_invoice_distributions aid8
    WHERE aid8.invoice_id = aid.invoice_id
    AND aid8.line_type_lookup_code = 'ITEM'
    AND aid8.accounting_date <= '21-NOV-2013'
    GROUP BY aid8.line_type_lookup_code
    HAVING SUM (NVL (aid8.base_amount, aid8.amount)) = 0)
    )
    GROUP BY aid.invoice_id, gcc.segment1, aid.line_type_lookup_code
    HAVING SUM (NVL (aid.base_amount, aid.amount)) != 0

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.02 0.01 0 0 0 0
    Execute 330 0.13 0.15 0 0 0 0
    Fetch 529831 77.67 1222.10 122819 2384507 0 529501
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 530162 77.82 1222.27 122819 2384507 0 529501
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    hi.

    1)
    The logic of this SQL-query is not optimal.
    it is necessary to optimize the logic of the query at first.

    2)
    show the plan of this sql query

    p.s.
    what this :

    AND aid.accounting_date BETWEEN '01-JAN-2011' AND '31-JAN-2011'
    AND aid.accounting_date <= '21-NOV-2013'
     
  3. nishant87

    nishant87 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    what do you mean 'logic of this SQL-query is not optimal'.

    ows (1st) Rows (avg) Rows (max) Row Source Operation
    ---------- ---------- ---------- ---------------------------------------------------
    0 0 0 UNION-ALL (cr=12 pr=4 pw=0 time=111437 us)
    0 0 0 FILTER (cr=4 pr=4 pw=0 time=110944 us)
    0 0 0 HASH GROUP BY (cr=4 pr=4 pw=0 time=110941 us)
    0 0 0 CONCATENATION (cr=4 pr=4 pw=0 time=110759 us)
    0 0 0 FILTER (cr=4 pr=4 pw=0 time=110732 us)
    0 0 0 NESTED LOOPS (cr=4 pr=4 pw=0 time=110728 us)
    0 0 0 NESTED LOOPS (cr=4 pr=4 pw=0 time=110720 us cost=17 size=74 card=1)
    0 0 0 NESTED LOOPS (cr=4 pr=4 pw=0 time=110717 us cost=15 size=47 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=4 pr=4 pw=0 time=110711 us cost=13 size=36 card=1)
    0 0 0 INDEX RANGE SCAN AP_INVOICE_DISTRIBUTIONS_X6 (cr=4 pr=4 pw=0 time=110704 us cost=12 size=0 card=2)(object id 9278695)
    0 0 0 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=0 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
    0 0 0 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 33901)
    0 0 0 INDEX UNIQUE SCAN AP_INVOICES_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 27998)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICES_ALL (cr=0 pr=0 pw=0 time=0 us cost=2 size=27 card=1)
    0 0 0 FILTER (cr=0 pr=0 pw=0 time=8 us)
    0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
    0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=10 size=74 card=1)
    0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=8 size=63 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICES_ALL (cr=0 pr=0 pw=0 time=0 us cost=4 size=27 card=1)
    0 0 0 INDEX RANGE SCAN AP_INVOICES_U2 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 28004)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=4 size=36 card=1)
    0 0 0 INDEX RANGE SCAN XXAP_AP_INVOICE_DISTRI_X2 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 2527394)
    0 0 0 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 33901)
    0 0 0 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=0 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
    0 0 0 FILTER (cr=4 pr=0 pw=0 time=388 us)
    0 0 0 HASH GROUP BY (cr=4 pr=0 pw=0 time=387 us)
    0 0 0 CONCATENATION (cr=4 pr=0 pw=0 time=252 us)
    0 0 0 FILTER (cr=4 pr=0 pw=0 time=243 us)
    0 0 0 FILTER (cr=4 pr=0 pw=0 time=241 us)
    0 0 0 NESTED LOOPS (cr=4 pr=0 pw=0 time=239 us)
    0 0 0 NESTED LOOPS (cr=4 pr=0 pw=0 time=237 us cost=17 size=74 card=1)
    0 0 0 NESTED LOOPS (cr=4 pr=0 pw=0 time=234 us cost=15 size=47 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=4 pr=0 pw=0 time=231 us cost=13 size=36 card=1)
    0 0 0 INDEX RANGE SCAN AP_INVOICE_DISTRIBUTIONS_X6 (cr=4 pr=0 pw=0 time=227 us cost=12 size=0 card=2)(object id 9278695)
    0 0 0 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=0 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
    0 0 0 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 33901)
    0 0 0 INDEX UNIQUE SCAN AP_INVOICES_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 27998)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICES_ALL (cr=0 pr=0 pw=0 time=0 us cost=2 size=27 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=5 size=23 card=1)
    0 0 0 INDEX RANGE SCAN XXAP_AP_INVOICE_DISTRI_X2 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(object id 2527394)
    0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us)
    0 0 0 SORT GROUP BY NOSORT (cr=0 pr=0 pw=0 time=0 us cost=5 size=30 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=5 size=30 card=1)
    0 0 0 INDEX RANGE SCAN XXAP_AP_INVOICE_DISTRI_X2 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(object id 2527394)
    0 0 0 FILTER (cr=0 pr=0 pw=0 time=2 us)
    0 0 0 FILTER (cr=0 pr=0 pw=0 time=1 us)
    0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
    0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=10 size=74 card=1)
    0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=8 size=63 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICES_ALL (cr=0 pr=0 pw=0 time=0 us cost=4 size=27 card=1)
    0 0 0 INDEX RANGE SCAN AP_INVOICES_U2 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 28004)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=4 size=36 card=1)
    0 0 0 INDEX RANGE SCAN XXAP_AP_INVOICE_DISTRI_X2 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 2527394)
    0 0 0 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 33901)
    0 0 0 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=0 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=5 size=23 card=1)
    0 0 0 INDEX RANGE SCAN XXAP_AP_INVOICE_DISTRI_X2 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(object id 2527394)
    0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us)
    0 0 0 SORT GROUP BY NOSORT (cr=0 pr=0 pw=0 time=0 us cost=5 size=30 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=5 size=30 card=1)
    0 0 0 INDEX RANGE SCAN XXAP_AP_INVOICE_DISTRI_X2 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(object id 2527394)
    0 0 0 FILTER (cr=4 pr=0 pw=0 time=83 us)
    0 0 0 HASH GROUP BY (cr=4 pr=0 pw=0 time=82 us)
    0 0 0 CONCATENATION (cr=4 pr=0 pw=0 time=46 us)
    0 0 0 FILTER (cr=4 pr=0 pw=0 time=39 us)
    0 0 0 FILTER (cr=4 pr=0 pw=0 time=36 us)
    0 0 0 NESTED LOOPS (cr=4 pr=0 pw=0 time=32 us)
    0 0 0 NESTED LOOPS (cr=4 pr=0 pw=0 time=31 us cost=17 size=74 card=1)
    0 0 0 NESTED LOOPS (cr=4 pr=0 pw=0 time=29 us cost=15 size=47 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=4 pr=0 pw=0 time=28 us cost=13 size=36 card=1)
    0 0 0 INDEX RANGE SCAN AP_INVOICE_DISTRIBUTIONS_X6 (cr=4 pr=0 pw=0 time=21 us cost=12 size=0 card=2)(object id 9278695)
    0 0 0 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=0 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
    0 0 0 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 33901)
    0 0 0 INDEX UNIQUE SCAN AP_INVOICES_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 27998)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICES_ALL (cr=0 pr=0 pw=0 time=0 us cost=2 size=27 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=5 size=23 card=1)
    0 0 0 INDEX RANGE SCAN XXAP_AP_INVOICE_DISTRI_X2 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(object id 2527394)
    0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us)
    0 0 0 SORT GROUP BY NOSORT (cr=0 pr=0 pw=0 time=0 us cost=5 size=30 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=5 size=30 card=1)
    0 0 0 INDEX RANGE SCAN XXAP_AP_INVOICE_DISTRI_X2 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(object id 2527394)
    0 0 0 FILTER (cr=0 pr=0 pw=0 time=3 us)
    0 0 0 FILTER (cr=0 pr=0 pw=0 time=1 us)
    0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
    0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=10 size=74 card=1)
    0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=8 size=63 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICES_ALL (cr=0 pr=0 pw=0 time=0 us cost=4 size=27 card=1)
    0 0 0 INDEX RANGE SCAN AP_INVOICES_U2 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 28004)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=4 size=36 card=1)
    0 0 0 INDEX RANGE SCAN XXAP_AP_INVOICE_DISTRI_X2 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 2527394)
    0 0 0 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 33901)
    0 0 0 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=0 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=5 size=23 card=1)
    0 0 0 INDEX RANGE SCAN XXAP_AP_INVOICE_DISTRI_X2 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(object id 2527394)
    0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us)
    0 0 0 SORT GROUP BY NOSORT (cr=0 pr=0 pw=0 time=0 us cost=5 size=30 card=1)
    0 0 0 TABLE ACCESS BY INDEX ROWID AP_INVOICE_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=5 size=30 card=1)
    0 0 0 INDEX RANGE SCAN XXAP_AP_INVOICE_DISTRI_X2 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=1)(object id 2527394)
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    it isn't the sql-plan.... this output is similar to a source of trc (src) of the file.

    show the plan who receives as an output of it, 'explain the plan' or dbms_xplan(display,display_cursor ...) , or show to full trc the file.


    for example...why you use such condition:
    AND aid.accounting_date BETWEEN of '01-JAN-2011' AND '31-JAN-2011'
    AND aid.accounting_date <= '21-NOV-2013'
    for table ap_invoice_distributions aid
    ?