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 |
|
ccpCCP88
Messages: 11 Registered: January 2023
|
Junior Member |
|
|
this scrip run very slow, Please help me to figure root cause, thanks.
|
|
|
|
Re: SQL very slow [message #686798 is a reply to message #686796] |
Wed, 11 January 2023 03:46 |
John Watson
Messages: 8938 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 #686811 is a reply to message #686809] |
Thu, 12 January 2023 08:45 |
John Watson
Messages: 8938 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 #686821 is a reply to message #686796] |
Mon, 16 January 2023 09:23 |
Duane
Messages: 557 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 #686824 is a reply to message #686822] |
Mon, 16 January 2023 10:27 |
Duane
Messages: 557 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 |
John Watson
Messages: 8938 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 |
Duane
Messages: 557 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.
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:50:37 CDT 2024
|