Home » SQL & PL/SQL » SQL & PL/SQL » SQL very slow (EBS R12.2.8 )
SQL very slow [message #686796] Tue, 10 January 2023 14:39 Go to next message
ccpCCP88
Messages: 7
Registered: January 2023
Junior Member
this scrip run very slow, Please help me to figure root cause, thanks.
Re: SQL very slow [message #686797 is a reply to message #686796] Tue, 10 January 2023 15:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68352
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

For any SQL performances question, please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.

Re: SQL very slow [message #686798 is a reply to message #686796] Wed, 11 January 2023 03:46 Go to previous messageGo to next message
John Watson
Messages: 8796
Registered: January 2010
Location: Global Village
Senior Member
I'll post your SQL the way it should be done (please do so yourself in future):
    --2. ap with no TAX invoice line
 --    UNION ALL
     SELECT (/*SELECT DESCRIPTION
               FROM apps.FND_LOOKUP_VALUES_VL
              WHERE     lookup_type = 'XXAR_UK_TAX_QT_MT'
                    AND lookup_code = TO_CHAR (ap.GL_DATE, 'MON-YYYY'))
                QT,*/  --commented by Leo Chen
             SELECT lu.DESCRIPTION as QT
            from apps.FND_LOOKUP_VALUES_VL aa
           LEFT JOIN apps.FND_LOOKUP_VALUES_VL lu
           ON lu.lookup_type = 'XXAR_UK_TAX_QT_MT'
           AND lu.lookup_code = TO_CHAR (ap.GL_DATE, 'MON-YYYY')),
            TO_CHAR (ap.GL_DATE, 'MON-YYYY')
                MT,
            'PI'
                TYPE,
            CASE
                --9.2
                WHEN UPPER (a3.description) = 'VAT ONLY' THEN 'BOX4' -- net amount = tax
                WHEN EXCEPTION_CODE IN ('A', 'D') THEN 'BOX4'
                WHEN EXCEPTION_CODE IN ('F', 'J') THEN 'NOBOX'
                ELSE 'NOBOX'
            END
                VATBOX,
            CASE
                WHEN UPPER (a3.description) = 'VAT ONLY'
                THEN
                    'NOBOX'                                -- net amount = tax
                WHEN EXCEPTION_CODE IN ('B',
                                        'D',
                                        'E',
                                        'G')
                THEN
                    'BOX7'
                WHEN EXCEPTION_CODE IN ('F', 'J', 'N')
                THEN
                    'NOBOX'
                ELSE
                    'BOX7'
            END
                NETBOX,
            DECODE (INPUT_TAX_CLASSIFICATION_CODE,
                    'T1 GB VAT - 20', 'T0 GB VAT - 0',
                    'VATSTAND', 'T0 GB VAT - 0',
                    'T5 GB VAT - 5', 'T0 GB VAT - 0',
                    INPUT_TAX_CLASSIFICATION_CODE)
                TAX_RATE_CODE,
            aps.vendor_name
                TP_NAME,
            ap.invoice_num
                TRX_NUM,
            --clean wrong tax classification
            AP.GL_DATE,
            'GBP'
                Func_CURR,
            a3.description,
            CASE
                --exception 001 --3/6
                --9.2
                WHEN UPPER (a3.description) = 'VAT ONLY'
                THEN
                    0                                      -- net amount = tax
                WHEN     TO_CHAR (ap.GL_DATE, 'MON-YYYY') = 'NOV-2020'
                     AND zLD.TRX_currency_code = 'EUR'
                THEN
                    ROUND (zLD.line_amt * 0.8997758, 2)
                ELSE
                    ROUND (
                        (NVL (zLD.currency_conversion_rate, 1) * zLD.line_amt),
                        2)
            END
                NET_FUNC_AMT,
            CASE
                WHEN UPPER (a3.description) = 'VAT ONLY'
                THEN
                    ROUND (
                        (NVL (zLD.currency_conversion_rate, 1) * zLD.line_amt),
                        2)
                --222
                /* ROUND (
                                                                (NVL (zLD.currency_conversion_rate, 1) * zLD.line_amt),
                                                                2)
                                                                */
                -- net amount = tax
                ELSE
                    0
            END
                AS TAX_FUNC_AMT,
            A3.LINE_NUMBER,
            CASE
                WHEN AP.invoice_num IN ('3804',
                                        '8001051137',
                                        '34971123',
                                        '34971318')
                THEN
                    'T24'
                WHEN ap.invoice_num IN ('90914') AND ap.org_id = 126
                THEN
                    'T23'
                ELSE
                    'NOREVERSE'
            END
                REVERSE_FLAG,
            CASE
                WHEN ap.invoice_num IN ('90914') AND ap.org_id = 126 THEN 'C'
                ELSE ex.EXCEPTION_CODE
            END
                EX_CODE,
            CASE
                WHEN ap.invoice_num IN ('90914') AND ap.org_id = 126
                THEN
                    'T23'
                ELSE
                    ex.to_tax
            END
                EX_TO,
            aps.vendor_id
                TP_ID
       FROM zx_lines_det_factors  zld,
            ap_invoices_all       ap,
            ap_suppliers          aps,
            ap_invoice_lines_all  a3,
            (SELECT *
               FROM XXEBTAX_EXCEPTION_TAB
              WHERE org_id = fnd_global.org_id) ex
      WHERE     AP.vendor_id = aps.vendor_id
            AND zld.trx_id = ap.invoice_id
            AND zld.INTERNAL_ORGANIZATION_ID = fnd_global.org_id
            AND ap.org_id = fnd_global.org_id
            AND a3.invoice_id = aP.invoice_id
            AND ZLD.trx_line_id = a3.line_number
         /*   AND (NOT EXISTS
                     (SELECT trx_id, trx_line_id
                        FROM zx_lines zl
                       WHERE     zld.trx_id = zl.trx_id
                             AND zld.trx_line_id = zl.trx_line_id
                             AND zl.INTERNAL_ORGANIZATION_ID =
                                 fnd_global.org_id))*/    --updated by Leo Chen
            AND (zld.trx_id,zld.trx_line_id) NOT IN (
              SELECT trx_id, trx_line_id
              FROM zx_lines zl
              WHERE zl.INTERNAL_ORGANIZATION_ID =fnd_global.org_id
              AND trx_id IS NOT NULL
              AND trx_line_id IS NOT NULL)
            AND batch_id <> 2160103
            AND zld.line_amt <> 0
            AND AP.INVOICE_NUM NOT IN
                    ('SCCUKLTD1', '002P523506', '1410011398DD')
            AND CANCELLED_DATE IS NULL
            AND ex.trx_num(+) = ap.invoice_num
            AND ex.line_num(+) = a3.line_number
            AND ex.org_id(+) = ap.org_id
     --ORDER BY type,trx_num, LINE_NUMBER
     ----------------------------------------------
You would need to provide a lot more information to get a proper answer, beginning with the execution plan.

In the meantime, there is one obvious issue: you are projecting this subquery,
SELECT lu.DESCRIPTION as QT
            from apps.FND_LOOKUP_VALUES_VL aa
           LEFT JOIN apps.FND_LOOKUP_VALUES_VL lu
           ON lu.lookup_type = 'XXAR_UK_TAX_QT_MT'
           AND lu.lookup_code = TO_CHAR (ap.GL_DATE, 'MON-YYYY'))
In principle, Oracle will have run that query once for every row returned by the outer query. This is one of the worst types of correlation, and you should (almost certainly) re-write it as an outer join to the other tables.

Re: SQL very slow [message #686803 is a reply to message #686798] Thu, 12 January 2023 07:18 Go to previous messageGo to next message
ccpCCP88
Messages: 7
Registered: January 2023
Junior Member
if I back to original, is it ok? I think pt is very slow is not due to this sentence

SELECT DESCRIPTION
FROM apps.FND_LOOKUP_VALUES_VL
WHERE lookup_type = 'XXAR_UK_TAX_QT_MT'
AND lookup_code = TO_CHAR (ap.GL_DATE, 'MON-YYYY'))
Re: SQL very slow [message #686804 is a reply to message #686803] Thu, 12 January 2023 07:20 Go to previous messageGo to next message
ccpCCP88
Messages: 7
Registered: January 2023
Junior Member
Below changed is reasonable?
/* AND (NOT EXISTS
(SELECT trx_id, trx_line_id
FROM zx_lines zl
WHERE zld.trx_id = zl.trx_id
AND zld.trx_line_id = zl.trx_line_id
AND zl.INTERNAL_ORGANIZATION_ID =
fnd_global.org_id))*/ --updated by Leo Chen
AND (zld.trx_id,zld.trx_line_id) NOT IN (
SELECT trx_id, trx_line_id
FROM zx_lines zl
WHERE zl.INTERNAL_ORGANIZATION_ID =fnd_global.org_id
Re: SQL very slow [message #686805 is a reply to message #686804] Thu, 12 January 2023 07:27 Go to previous messageGo to next message
John Watson
Messages: 8796
Registered: January 2010
Location: Global Village
Senior Member
You have ignored everything that Michel and I have said Sad
Re: SQL very slow [message #686806 is a reply to message #686805] Thu, 12 January 2023 08:02 Go to previous messageGo to next message
ccpCCP88
Messages: 7
Registered: January 2023
Junior Member
I re-write SELECT lu.DESCRIPTION as QT
from apps.FND_LOOKUP_VALUES_VL aa
LEFT JOIN apps.FND_LOOKUP_VALUES_VL lu
ON lu.lookup_type = 'XXAR_UK_TAX_QT_MT'
AND lu.lookup_code = TO_CHAR (ap.GL_DATE, 'MON-YYYY')) into

SELECT DESCRIPTION
FROM apps.FND_LOOKUP_VALUES_VL
WHERE lookup_type = 'XXAR_UK_TAX_QT_MT'
AND lookup_code = TO_CHAR (ap.GL_DATE, 'MON-YYYY'))
Re: SQL very slow [message #686807 is a reply to message #686806] Thu, 12 January 2023 08:04 Go to previous messageGo to next message
ccpCCP88
Messages: 7
Registered: January 2023
Junior Member
execution plan show below:
SELECT STATEMENT, GOAL = ALL_ROWS 0 392879 126239914754 1 155
TABLE ACCESS BY INDEX ROWID BATCHED 1 APPLSYS FND_LOOKUP_VALUES 4 42086 1 51
INDEX RANGE SCAN "LOOKUP_TYPE"='XXAR_UK_TAX_QT_MT' AND "LOOKUP_CODE"=TO_CHAR(:B1,'MON-YYYY') AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='SET1' 2 APPLSYS FND_LOOKUP_VALUES_U1 3 34114 1
NESTED LOOPS ANTI 3 392875 126239872668 1 155
NESTED LOOPS 4 392872 126239845943 1 141
HASH JOIN OUTER "XXEBTAX_EXCEPTION_TAB"."TRX_NUM"(+)="AP"."INVOICE_NUM" AND "XXEBTAX_EXCEPTION_TAB"."LINE_NUM"(+)="A3"."LINE_NUMBER" AND "XXEBTAX_EXCEPTION_TAB"."ORG_ID"(+)="AP"."ORG_ID" 5 392871 126239836711 1 114
HASH JOIN "A3"."INVOICE_ID"="AP"."INVOICE_ID" AND "ZLD"."TRX_LINE_ID"="A3"."LINE_NUMBER" 6 392861 126234331549 1 88
NESTED LOOPS 7 392861 126234331549 1 88
NESTED LOOPS 8
STATISTICS COLLECTOR 9
HASH JOIN "ZLD"."TRX_ID"="AP"."INVOICE_ID" 10 303034 117686858659 346812 23236404
NESTED LOOPS 11 303034 117686858659 346812 23236404
STATISTICS COLLECTOR 12
TABLE ACCESS FULL 13 AP AP_INVOICES_ALL 27967 15997139574 260307 10151973
TABLE ACCESS BY INDEX ROWID BATCHED 14 ZX ZX_LINES_DET_FACTORS 273213 101121308054 1 28
INDEX RANGE SCAN "ZLD"."TRX_ID"="AP"."INVOICE_ID" 15 ZX ZX_LINES_DET_FACTORS_N2
TABLE ACCESS FULL 16 ZX ZX_LINES_DET_FACTORS 273213 101121308054 2372899 66441172
INDEX UNIQUE SCAN "A3"."INVOICE_ID"="AP"."INVOICE_ID" AND "ZLD"."TRX_LINE_ID"="A3"."LINE_NUMBER" 17 AP AP_INVOICE_LINES_U1
TABLE ACCESS BY INDEX ROWID 18 AP AP_INVOICE_LINES_ALL 83326 6179122592 1 21
TABLE ACCESS FULL 19 AP AP_INVOICE_LINES_ALL 83326 6179122592 10631530 223262130
TABLE ACCESS FULL 20 APPS XXEBTAX_EXCEPTION_TAB 10 4878812 262 6812
TABLE ACCESS BY INDEX ROWID 21 AP AP_SUPPLIERS 1 9231 1 27
INDEX UNIQUE SCAN "AP"."VENDOR_ID"="APS"."VENDOR_ID" 22 AP AP_SUPPLIERS_U1 0 1900 1
TABLE ACCESS BY INDEX ROWID BATCHED 23 ZX ZX_LINES 3 26725 82386 1153404
INDEX RANGE SCAN "ZLD"."TRX_ID"="TRX_ID" AND "ZLD"."TRX_LINE_ID"="TRX_LINE_ID" 24 ZX ZX_LINES_N4 2 15843 1
Re: SQL very slow [message #686809 is a reply to message #686807] Thu, 12 January 2023 08:08 Go to previous messageGo to next message
ccpCCP88
Messages: 7
Registered: January 2023
Junior Member
database version is 12c
Re: SQL very slow [message #686811 is a reply to message #686809] Thu, 12 January 2023 08:45 Go to previous messageGo to next message
John Watson
Messages: 8796
Registered: January 2010
Location: Global Village
Senior Member
This looks like trolling: being deliberately stupid, in an attempt to make people angry. I shall be generous, and assume that it is not deliberate.

The plan you have posted is useless. It is not formatted, most of the necessary information is missing.
Michel asked you for the 4 digit version number. You have replied "12c".
You have not attempted to use tags to format your code, even though I went so far as to demonstrate it.

This is an example of how to capture and display your exec plan:
orclz>
orclz> set lin 200
orclz> set pages 1000
orclz>
orclz>
orclz> alter session set statistics_level=all;

Session altered.

orclz> select * from emp join dept using (deptno) where ename='MILLER';

         DEPTNO           EMPNO ENAME      JOB                   MGR HIREDATE                        SAL            COMM DNAME          LOC
--------------- --------------- ---------- --------- --------------- ------------------- --------------- --------------- -------------- -------------
             10            7934 MILLER     CLERK                7782 1982-01-23:00:00:00            1300                 ACCOUNTING     NEW YORK

orclz> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0anq4nkgpgjzq, child number 0
-------------------------------------
select * from emp join dept using (deptno) where ename='MILLER'

Plan hash value: 3625962092

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  NESTED LOOPS                |         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   NESTED LOOPS               |         |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    TABLE ACCESS FULL         | EMP     |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("EMP"."ENAME"='MILLER')
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
   - this is an adaptive plan


27 rows selected.

orclz>

--update: actually, the call to dbms_xplan could be better:
select * from table(dbms_xplan.display_cursor(format=>'allstats last +cost adaptive'));

[Updated on: Thu, 12 January 2023 08:51]

Report message to a moderator

Re: SQL very slow [message #686812 is a reply to message #686811] Thu, 12 January 2023 09:27 Go to previous messageGo to next message
ccpCCP88
Messages: 7
Registered: January 2023
Junior Member
sorry my language poor!!!
db version 12.1.0.2.0

excu
  • Attachment: sql2-exp.txt
    (Size: 5.13KB, Downloaded 20 times)
Re: SQL very slow [message #686813 is a reply to message #686812] Thu, 12 January 2023 11:30 Go to previous messageGo to next message
John Watson
Messages: 8796
Registered: January 2010
Location: Global Village
Senior Member
If you really want help, you do need to follow instructions.
Re: SQL very slow [message #686821 is a reply to message #686796] Mon, 16 January 2023 09:23 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
John,

When reading an Explain Plan, do you want the lowest cost, lowest steps or a combination of both?

Let's say Explain Plan 1 has a cost of 10 but has 10 steps (what I'm calling operations). Explain Plan 2 has a cost of 5 but has 20 steps. Is one Explain Plan better than the other? Or is it something you just have to know when reading the steps?
Re: SQL very slow [message #686822 is a reply to message #686821] Mon, 16 January 2023 09:46 Go to previous messageGo to next message
John Watson
Messages: 8796
Registered: January 2010
Location: Global Village
Senior Member
The execution plan you want is the plan that runs quickest.
Re: SQL very slow [message #686824 is a reply to message #686822] Mon, 16 January 2023 10:27 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
Ok, so how it that determined? The plan with the least amount of steps? Or is it determined by running the query and if it takes 2 minutes to run versus 5 minutes to run then you use the 2 minute query.
Re: SQL very slow [message #686826 is a reply to message #686824] Mon, 16 January 2023 10:30 Go to previous messageGo to next message
John Watson
Messages: 8796
Registered: January 2010
Location: Global Village
Senior Member
I've already shown you one way of timing a query: capture the plan, with statistics_level=all, and look at the A-Time.
Re: SQL very slow [message #686828 is a reply to message #686826] Mon, 16 January 2023 10:59 Go to previous message
Duane
Messages: 508
Registered: December 2002
Senior Member
Just to be clear, I'm not the original poster. I just saw your post about the explain plan and asked a question. Now I know to look at the A-Time. Thank you.
Previous Topic: Base64 to Blob
Next Topic: greatest total amount of purchases per day for each customer_id
Goto Forum:
  


Current Time: Thu Feb 09 02:49:43 CST 2023