Home » RDBMS Server » Performance Tuning » Query taking more time
Query taking more time [message #603509] Tue, 17 December 2013 02:46 Go to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
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-DEC-2012' AND '31-DEC-2012'
AND aid.accounting_date <= '22-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 > '22-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-DEC-2012' AND '31-DEC-2012'
AND aid.accounting_date <= '22-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 > '22-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 <= '22-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 <= '22-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-DEC-2012' AND '31-DEC-2012'
AND aid.accounting_date <= '22-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 > '22-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 <= '22-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 <= '22-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
Re: Query taking more time [message #603510 is a reply to message #603509] Tue, 17 December 2013 02:58 Go to previous messageGo to next message
John Watson
Messages: 4558
Registered: January 2010
Location: Global Village
Senior Member
Please format your code properly withing [code] tags, as described here: How to use [code] tags and make your code easier to read
You have been asked to do this before. And also, what is the problem? You have not said.
Re: Query taking more time [message #603511 is a reply to message #603509] Tue, 17 December 2013 03:10 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
I have attached the tkp .Please convert that from txt to tkp
Re: Query taking more time [message #603512 is a reply to message #603511] Tue, 17 December 2013 03:11 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
The top query is taking too much of time
Re: Query taking more time [message #603513 is a reply to message #603512] Tue, 17 December 2013 03:12 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
/* Formatted on 2013/12/17 14:42 (Formatter Plus v4.8.Cool */
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 = :b1
AND aid.line_type_lookup_code = 'ITEM'
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN :b2 AND :b3
AND aid.accounting_date <= :b4
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 (:b5, ai.vendor_id) = ai.vendor_id
AND NVL (:b6, 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 > :b4
)
))
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 = :b1
AND aid.line_type_lookup_code = 'TAX'
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN :b2 AND :b3
AND aid.accounting_date <= :b4
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 (:b5, ai.vendor_id) = ai.vendor_id
AND NVL (:b6, 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 > :b4
)
))
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 <= :b4)
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 <= :b4
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 = :b1
AND aid.line_type_lookup_code = 'FREIGHT'
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN :b2 AND :b3
AND aid.accounting_date <= :b4
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 (:b5, ai.vendor_id) = ai.vendor_id
AND NVL (:b6, 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 > :b4
)
))
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 <= :b4)
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 <= :b4
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
Re: Query taking more time [message #603514 is a reply to message #603513] Tue, 17 December 2013 03:13 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 330 0.15 0.17 0 0 0 0
Fetch 529837 111.40 1876.08 151301 2384537 0 529507
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 530168 111.57 1876.27 151301 2384537 0 529507
Re: Query taking more time [message #603516 is a reply to message #603514] Tue, 17 December 2013 03:24 Go to previous messageGo to next message
martijn
Messages: 278
Registered: December 2006
Location: Netherlands
Senior Member
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.01          0          0          0           0
Execute    330      0.15       0.17          0          0          0           0
Fetch   529837    111.40    1876.08     151301    2384537          0      529507
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   530168    111.57    1876.27     151301    2384537          0      529507


As requested...please use code-tags, like I have done above as an example.
In this way we can actually read what is your post instead of guess.

Also please explain what is exactly your problem.
I guess your query is to slow (?)
Does that mean slower then before. Or slow in general.
Please supply ddl
Please supply explain plan in the post. I (but I'm speaking for me) will not just open a attachement in a forum.
Are you statistics up to date

Think about what information we need for helping you. (and then supply that information)
Re: Query taking more time [message #603517 is a reply to message #603516] Tue, 17 December 2013 03:30 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
when we submit the program the query which we have provided takes long time and because of this query program went into long running
Re: Query taking more time [message #603518 is a reply to message #603517] Tue, 17 December 2013 03:33 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
Plan hash value: 2452623412

--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 114 | 710 (1)| 00:00:09 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 114 | 710 (1)| 00:00:09 |
| 3 | NESTED LOOPS ANTI | | 1 | 51 | 708 (1)| 00:00:09 |
| 4 | NESTED LOOPS | | 1 | 45 | 707 (1)| 00:00:09 |
| 5 | VIEW | VW_SQ_1 | 8 | 104 | 681 (1)| 00:00:09 |
| 6 | HASH UNIQUE | | 8 | 1688 | | |
| 7 | NESTED LOOPS | | | | | |
| 8 | NESTED LOOPS | | 8 | 1688 | 681 (1)| 00:00:09 |
| 9 | NESTED LOOPS | | 9 | 1809 | 663 (1)| 00:00:08 |
| 10 | NESTED LOOPS | | 9 | 1611 | 636 (1)| 00:00:08 |
| 11 | NESTED LOOPS | | 1 | 154 | 29 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 139 | 28 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 118 | 9 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| FND_LOOKUP_VALUES | 1 | 55 | 6 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | XXAR_FND_LOOKUP_VALUES_N1 | 13 | | 3 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID| PER_ALL_PEOPLE_F | 1 | 63 | 3 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | PER_PEOPLE_F_N54 | 2 | | 1 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | PER_ALL_ASSIGNMENTS_F | 1 | 21 | 19 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | PER_ASSIGNMENTS_F_X1 | 17 | | 2 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | PER_ASSIGNMENT_STATUS_TYPES | 1 | 15 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PER_ASSIGNMENT_STATUS_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | PO_HEADERS_ALL | 16 | 400 | 607 (1)| 00:00:08 |
|* 23 | INDEX RANGE SCAN | PO_HEADERS_N3 | 1607 | | 5 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | PO_DISTRIBUTIONS_ALL | 1 | 22 | 4 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | PO_DISTRIBUTIONS_N3 | 5 | | 2 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PO_LINES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | PO_LINES_ALL | 1 | 10 | 2 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | AP_INVOICE_DISTRIBUTIONS_ALL | 1 | 32 | 4 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | AP_INVOICE_DISTRIBUTIONS_N7 | 2 | | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | XXAP_NAF_SUPP_INV_STG_VLVS_PK | 415K| 2432K| 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | AP_INVOICES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID | AP_INVOICES_ALL | 1 | 63 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Re: Query taking more time [message #603519 is a reply to message #603518] Tue, 17 December 2013 03:34 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
Above is not right.Please ignore
Re: Query taking more time [message #603520 is a reply to message #603519] Tue, 17 December 2013 03:37 Go to previous messageGo to next message
John Watson
Messages: 4558
Registered: January 2010
Location: Global Village
Senior Member
There is a forum here http://www.orafaq.com/forum/f/38/ where you can experiment with learning how to format code properly for posting.
Re: Query taking more time [message #603530 is a reply to message #603519] Tue, 17 December 2013 04:01 Go to previous messageGo to next message
martijn
Messages: 278
Registered: December 2006
Location: Netherlands
Senior Member
nishant87 wrote on Tue, 17 December 2013 10:34
Above is not right.Please ignore

Not to worry, but have a very cloase look at : this page. Using code tags is explained there very nicely.
Re: Query taking more time [message #603532 is a reply to message #603518] Tue, 17 December 2013 04:21 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can see elapsed in martijn's first post, he formatted part of the tkprof attached in the OP.
Re: Query taking more time [message #603533 is a reply to message #603532] Tue, 17 December 2013 04:22 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
Why are there union alls in the code but not in the execution plan?
Re: Query taking more time [message #603534 is a reply to message #603533] Tue, 17 December 2013 04:28 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
they are in the execution plan, it does both in one go at the top.
A better question is why does the select contain union all, the differences between the three selects aren't great enough to justify it.
@nishant87 - try rewriting it without the union alls.
Re: Query taking more time [message #603538 is a reply to message #603534] Tue, 17 December 2013 04:35 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
Nope, still can't see 'em. Although to be fair I also didnt see the post saying it was guff so.....maybe my aging eyes.
Re: Query taking more time [message #603540 is a reply to message #603538] Tue, 17 December 2013 04:36 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
I was looking in the tkprof attached to the initial post.
Re: Query taking more time [message #603542 is a reply to message #603540] Tue, 17 December 2013 04:40 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
aaaaah that'd do it Smile
Re: Query taking more time [message #603668 is a reply to message #603542] Wed, 18 December 2013 05:29 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
The elapsed time is more.After 1st unionall select doesn't retuen any row nor the second one.When i checked one by one i found the below query which is in sql taking more time

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 > '22-NOV-2013'
));

Re: Query taking more time [message #603669 is a reply to message #603534] Wed, 18 December 2013 05:37 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 17 December 2013 10:28

@nishant87 - try rewriting it without the union alls.

Re: Query taking more time [message #603671 is a reply to message #603668] Wed, 18 December 2013 05:45 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
SQL> EXPLAIN PLAN FOR
2 SELECT ai.invoice_id
3 FROM ap_invoices ai
4 WHERE NVL (NULL, ai.vendor_id) = ai.vendor_id
5 AND NVL (NULL, ai.invoice_type_lookup_code) =
6 ai.invoice_type_lookup_code
7 AND ( ai.cancelled_date IS NULL
8 OR ( ai.cancelled_date IS NOT NULL
9 AND ai.cancelled_date > '22-NOV-2013'
)); 10

Explained.

BETSYU1(Dev)-SQL> SET LINESIZE 130
BETSYU1(Dev)-SQL> SET PAGESIZE 0
BETSYU1(Dev)-SQL> SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY); 2
Plan hash value: 2198335773

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106K| 2804K| 143K (7)| 00:28:42 |
|* 1 | TABLE ACCESS FULL| AP_INVOICES_ALL | 106K| 2804K| 143K (7)| 00:28:42 |
-------------------------------------------------------------------------------------

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

1 - filter(("CANCELLED_DATE" IS NULL OR "CANCELLED_DATE">TO_DATE('
2013-11-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND
NVL("ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),'
',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SU
BSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,1
0))),(-99)) AND "INVOICE_TYPE_LOOKUP_CODE" IS NOT NULL)

18 rows selected.

BETSYU1(Dev)-SQL>
Re: Query taking more time [message #603672 is a reply to message #603671] Wed, 18 December 2013 05:48 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
Your predicates there don't really leave it many choices without getting remarkably creative.

You're testing for is null, that really limits your options.
Re: Query taking more time [message #603686 is a reply to message #603669] Wed, 18 December 2013 07:58 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
As Roachcoach says, you don't really have any options for speeding that bit up (expect for maybe using parallel).
So your best bet is to rewrite the query so that that slow bit isn't done three times for every execution.

Therefore:
cookiemonster wrote on Wed, 18 December 2013 11:37
cookiemonster wrote on Tue, 17 December 2013 10:28

@nishant87 - try rewriting it without the union alls.


Re: Query taking more time [message #603690 is a reply to message #603686] Wed, 18 December 2013 08:14 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
Depending on the version and data skew you could get creative with indexing: be that function based index or virtual column and index but those are rather situational and would require varying degrees of code rewriting/view&synonym creation which may not sit well with stuff outside of this single query.

So you have a few options but truth be told, cookiemonsters idea is the best and least obtrusive starter, if it isnt enough, research some of the things I mention here. I'm not going to go into detail because if you go this route then you really need to understand what you're doing and not just lift it from a forum.
Re: Query taking more time [message #603715 is a reply to message #603690] Wed, 18 December 2013 11:49 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2328
Registered: May 2013
Location: World Wide on the Web
Senior Member
Even I would go with Cookiemonster's solution.
Another thing, In 11g a virtual column as a function over a column is same as a function based index.
Re: Query taking more time [message #603772 is a reply to message #603715] Thu, 19 December 2013 04:49 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
I know, but I cant tell what version he's on Smile

Plus chances are it'll still be too slow even without them so it is something to be considered (and useful reading as it wont be the last time someone runs into issues around nulls Smile)
Re: Query taking more time [message #603780 is a reply to message #603715] Thu, 19 December 2013 06:09 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
i don't know how i can remove unionall from that
Re: Query taking more time [message #603782 is a reply to message #603780] Thu, 19 December 2013 06:22 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well the difference between the 2nd and 3rd part is:
2nd:
AND aid.line_type_lookup_code = 'TAX'

3rd:
AND aid.line_type_lookup_code = 'FREIGHT'


So changing that line in the 2nd to:
AND aid.line_type_lookup_code IN ('TAX', 'FREIGHT')

will allow you to get rid of the 3rd.

Do that then compare the 1st section to the 2nd and see if you can reconcile the differences - you've got a subquery or two that will need to be OR'd in the where clause. It's not difficult, give it a try. If you get stuck post what you attempted and we'll point out where the issue is.
Re: Query taking more time [message #603808 is a reply to message #603782] Thu, 19 December 2013 09:04 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
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-Dec-2012' AND '31-Dec-2012'
AND aid.accounting_date <= '22-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 > '22-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 IN ('TAX','FREIGHT')
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN '01-Dec-2012' AND '31-Dec-2012'
AND aid.accounting_date <= '22-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 > '22-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 <= '22-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 <= '22-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



i am on 11i
Re: Query taking more time [message #603810 is a reply to message #603808] Thu, 19 December 2013 09:12 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
/* Formatted on 2013/12/19 20:42 (Formatter Plus v4.8.Cool */
PROCEDURE create_tem_table_invoices
IS
v_found NUMBER;
v_exch_rate_reexp NUMBER;
v_value_set_id NUMBER;
v_amount NUMBER;
v_valid_inv NUMBER;
v_amount_remaining NUMBER;
v_sum_dist_amount NUMBER;
v_source xxap_invoice_aging_tmp.SOURCE%TYPE;
v_dist_split NUMBER;
v_segment_desc VARCHAR2 (240);
v_group_desc VARCHAR2 (240);
v_due_date DATE;
v_parent_flex_value VARCHAR2 (240);
-- Added by Quanam SR 10-04-2013 RFC#1140830
v_chr_parent_from VARCHAR2 (240);
v_chr_parent_to VARCHAR2 (240);

--
CURSOR c_dates
IS
SELECT /*+ INDEX ( gp GL_PERIODS_U2) */
gp.start_date, gp.end_date
FROM gl_periods gp
WHERE gp.period_set_name = :c_calendar_name
AND ( (gp.end_date <= TRUNC (:p_fecha_hasta))
OR (TRUNC (:p_fecha_hasta) BETWEEN gp.start_date AND gp.end_date
)
);

-- and gp.end_date <= trunc(:p_fecha_hasta);
CURSOR c_distributions (
p_start_date IN DATE,
p_end_date IN DATE,
p_value_set_id IN NUMBER
)
IS
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7) */
aid.invoice_id, gcc.segment1
-- ,ffvl.description
-- ,ffv.parent_flex_value
--,ffvl2.description nombre_grupo
, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc --,
--fnd_flex_value_norm_hierarchy ffv--,
-- fnd_flex_values_vl ffvl
WHERE aid.org_id = :cp_org_id
-- Commented by Quanam SR 02-05-2013 and aid.posted_flag ='Y'
-- and aid.posted_flag ='Y'
--
AND aid.line_type_lookup_code = 'ITEM'
AND NVL (aid.base_amount, aid.amount) <> 0
AND aid.accounting_date BETWEEN p_start_date AND p_end_date
AND aid.accounting_date <= :p_fecha_hasta
AND gcc.code_combination_id = aid.dist_code_combination_id
-- Added by Quanam SR 10-04-2013 RFC#1140830
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (:p_vendor_id, ai.vendor_id) = ai.vendor_id
AND NVL (:p_invoice_type, 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 > :p_fecha_hasta
)
))
--
-- and ffv.flex_value_set_id = p_value_set_id
-- and gcc.segment1 between ffv.child_flex_value_low and ffv.child_flex_value_high
-- and ( ( :p_num_currency_func = 'MXN' and ffv.parent_flex_value Like '2%'
-- and ffv.parent_flex_value not like '2ZZ%' )
-- or (ffv.parent_flex_value like '03%' and :p_num_currency_func= 'ARS')
-- )
/*and aid.invoice_id in (select ps.invoice_id
from ap_payment_schedules ps
where ps.invoice_id = aid.invoice_id
and (((nvl (ps.amount_remaining, 0) != 0)
or ( nvl (ps.amount_remaining, 0) = 0
and --((i.cancelled_date is not null and i.cancelled_date > :p_fecha_hasta )
exists ( select 1
from ap_invoice_payments aip, ap_checks ac
where aip.check_id = ac.check_id
and aip.invoice_id = aid.invoice_id
and aip.accounting_date > :p_fecha_hasta
and decode (ac.status_lookup_code, 'VOIDED', 'Y', 'N') = 'N'
)
)
or exists ( select 1
from ap_invoice_distributions
where line_type_lookup_code = 'PREPAY'
and accounting_date > :p_fecha_hasta
and invoice_id = aid.invoice_id
)
)
))*/
-- and ffvl.flex_value_set_id = p_value_set_id
-- and ffvl.flex_value = gcc.segment1
-- and ffvl.summary_flag = 'N'
-- and ffvl.enabled_flag = 'Y'
--and aid.invoice_id =369219
-- and gcc.segment1 = '0308'
GROUP BY aid.invoice_id, gcc.segment1
--,ffvl.description
-- ,ffv.parent_flex_value
, aid.line_type_lookup_code
-- Added by Quanam SR 30-APR-2013 RFC#1140830 To discard some lines with item and its reversal
HAVING SUM (NVL (aid.base_amount, aid.amount)) <> 0
--
UNION ALL
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7) */
aid.invoice_id, gcc.segment1
--,ffvl.description
-- ,ffv.parent_flex_value
, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc --,
--fnd_flex_value_norm_hierarchy ffv--,
--fnd_flex_values_vl ffvl
WHERE aid.org_id = :cp_org_id
-- Commented by Quanam SR 02-05-2013 and aid.posted_flag ='Y'
-- and aid.posted_flag ='Y'
--
AND aid.line_type_lookup_code = 'TAX'
AND NVL (aid.base_amount, aid.amount) <> 0
AND aid.accounting_date BETWEEN p_start_date AND p_end_date
AND aid.accounting_date <= :p_fecha_hasta
AND gcc.code_combination_id = aid.dist_code_combination_id
-- Commented by Quanam SR 10-04-2013 RFC#1140830
/*
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'))
*/
-- Added by Quanam SR 10-04-2013 RFC#1140830
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (:p_vendor_id, ai.vendor_id) = ai.vendor_id
AND NVL (:p_invoice_type, 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 > :p_fecha_hasta
)
))
-- Added by Quanam SR 10-04-2013 RFC#1140830
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 <= :p_fecha_hasta)
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 <= :p_fecha_hasta
--AND NVL(aid8.base_amount,aid8.amount) <> 0
GROUP BY aid8.line_type_lookup_code
HAVING SUM (NVL (aid8.base_amount, aid8.amount)) = 0)
)
--

--
--
--and ffv.flex_value_set_id = p_value_set_id
--and gcc.segment1 between ffv.child_flex_value_low and ffv.child_flex_value_high
--and ( ( :p_num_currency_func = 'MXN' and ffv.parent_flex_value Like '2%'
-- and ffv.parent_flex_value not like '2ZZ%' )
-- or (ffv.parent_flex_value like '03%' and :p_num_currency_func= 'ARS')
-- )
/*and aid.invoice_id in (select ps.invoice_id
from ap_payment_schedules ps
where ps.invoice_id = aid.invoice_id
and (((nvl (ps.amount_remaining, 0) != 0)
or ( nvl (ps.amount_remaining, 0) = 0
and --((i.cancelled_date is not null and i.cancelled_date > :p_fecha_hasta )
exists ( select 1
from ap_invoice_payments aip, ap_checks ac
where aip.check_id = ac.check_id
and aip.invoice_id = aid.invoice_id
and aip.accounting_date > :p_fecha_hasta
and decode (ac.status_lookup_code, 'VOIDED', 'Y', 'N') = 'N'
)
)
or exists ( select 1
from ap_invoice_distributions
where line_type_lookup_code = 'PREPAY'
and accounting_date > :p_fecha_hasta
and invoice_id = aid.invoice_id
)
)
))
--and ffvl.flex_value_set_id = p_value_set_id
--and ffvl.flex_value = gcc.segment1
--and ffvl.summary_flag = 'N'
--and ffvl.enabled_flag = 'Y'
--and aid.invoice_id =369219
--and gcc.segment1 = '0308'*/
GROUP BY aid.invoice_id, gcc.segment1
--,ffvl.description
--,ffv.parent_flex_value
, aid.line_type_lookup_code
-- Added by Quanam SR 30-APR-2013 RFC#1140830 To discard some lines with item and its reversal
HAVING SUM (NVL (aid.base_amount, aid.amount)) <> 0
--
-- Added by Quanam SR 30-APR-2013 RFC#1140830
UNION ALL
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7) */
aid.invoice_id, gcc.segment1
--,ffvl.description
-- ,ffv.parent_flex_value
, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc --,
--fnd_flex_value_norm_hierarchy ffv--,
--fnd_flex_values_vl ffvl
WHERE aid.org_id = :cp_org_id
AND aid.line_type_lookup_code = 'FREIGHT'
AND NVL (aid.base_amount, aid.amount) <> 0
AND aid.accounting_date BETWEEN p_start_date AND p_end_date
AND aid.accounting_date <= :p_fecha_hasta
AND gcc.code_combination_id = aid.dist_code_combination_id
-- Commented by Quanam SR 10-04-2013 RFC#1140830
/*
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'))
*/
-- Added by Quanam SR 10-04-2013 RFC#1140830
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (:p_vendor_id, ai.vendor_id) = ai.vendor_id
AND NVL (:p_invoice_type, 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 > :p_fecha_hasta
)
))
-- Added by Quanam SR 10-04-2013 RFC#1140830
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 <= :p_fecha_hasta)
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 <= :p_fecha_hasta
--AND NVL(aid8.base_amount,aid8.amount) <> 0
GROUP BY aid8.line_type_lookup_code
HAVING SUM (NVL (aid8.base_amount, aid8.amount)) = 0)
)
--

--
--
--and ffv.flex_value_set_id = p_value_set_id
--and gcc.segment1 between ffv.child_flex_value_low and ffv.child_flex_value_high
--and ( ( :p_num_currency_func = 'MXN' and ffv.parent_flex_value Like '2%'
-- and ffv.parent_flex_value not like '2ZZ%' )
-- or (ffv.parent_flex_value like '03%' and :p_num_currency_func= 'ARS')
-- )
--and ffvl.flex_value_set_id = p_value_set_id
--and ffvl.flex_value = gcc.segment1
--and ffvl.summary_flag = 'N'
--and ffvl.enabled_flag = 'Y'
--and aid.invoice_id =369219
--and gcc.segment1 = '0308'*/
GROUP BY aid.invoice_id, gcc.segment1
--,ffvl.description
--,ffv.parent_flex_value
, aid.line_type_lookup_code
-- Added by Quanam SR 30-APR-2013 RFC#1140830 To discard some lines with item and its reversal
HAVING SUM (NVL (aid.base_amount, aid.amount)) <> 0;

--
CURSOR c_payment (p_invoice_id IN NUMBER)
IS
SELECT ps.due_date, ps.amount_remaining
FROM ap_payment_schedules ps
WHERE ps.invoice_id = p_invoice_id;

CURSOR c_invoices (p_invoice_id IN NUMBER)
IS
SELECT i.invoice_id, i.vendor_id, i.invoice_num,
i.invoice_currency_code, i.payment_currency_code,
i.payment_cross_rate, i.vendor_site_id, i.amount_paid,
i.invoice_type_lookup_code, i.accts_pay_code_combination_id,
i.invoice_date, i.exchange_rate, i.cancelled_date, i.org_id,
i.gl_date, i.payment_status_flag, i.terms_id --,
--ps.due_date
FROM ap_invoices i --,
-- ap_payment_schedules ps
WHERE i.invoice_id = p_invoice_id
AND NVL (:p_vendor_id, i.vendor_id) = i.vendor_id
AND NVL (:p_invoice_type, i.invoice_type_lookup_code) =
i.invoice_type_lookup_code
AND ( i.cancelled_date IS NULL
OR ( i.cancelled_date IS NOT NULL
AND i.cancelled_date > :p_fecha_hasta
)
);

/* and i.invoice_id in (select ps.invoice_id
from ap_payment_schedules ps
where ps.invoice_id = p_invoice_id
--and (nvl(i.cancelled_date, next_day(trunc(:p_fecha_hasta),1)) > trunc(:p_fecha_hasta))
and ps.invoice_id = i.invoice_id
and (((nvl (ps.amount_remaining, 0) * nvl (i.exchange_rate, 1) != 0)
or ( nvl (ps.amount_remaining, 0) * nvl (i.exchange_rate, 1) = 0
and ((i.cancelled_date is not null and i.cancelled_date > :p_fecha_hasta )
or exists ( select 1
from ap_invoice_payments aip, ap_checks ac
where aip.check_id = ac.check_id
and aip.invoice_id = i.invoice_id
and aip.accounting_date > :p_fecha_hasta
and decode (ac.status_lookup_code, 'VOIDED', 'Y', 'N') = 'N'
)
)
or exists ( select 1
from ap_invoice_distributions
where line_type_lookup_code = 'PREPAY'
and accounting_date > :p_fecha_hasta
and invoice_id = i.invoice_id
)
)
)
and (i.cancelled_date is null or (i.cancelled_date is not null and i.cancelled_date > :p_fecha_hasta))
));
/* and ((NVL(ps.amount_remaining, 0)*NVL(i.exchange_rate, 1)!= 0)
or (NVL(ps.amount_remaining, 0)*NVL(i.exchange_rate, 1)=0
and ( exists (select 1
from ap_invoice_payments aip
, ap_checks ac
where aip.check_id = ac.check_id
and aip.invoice_id = i.invoice_id
and aip.accounting_date > trunc(:p_fecha_hasta)
and DECODE (ac.status_lookup_code,'VOIDED', 'Y','N') = 'N')
or exists (select 1
from ap_invoice_distributions aid
where aid.line_type_lookup_code = 'PREPAY'
and aid.accounting_date > trunc(:p_fecha_hasta)
and aid.invoice_id = i.invoice_id)
)
)
or (nvl(i.cancelled_date, next_day(trunc(:p_fecha_hasta),1)) > trunc(:p_fecha_hasta))
);*/--and i.vendor_id=16702 ;
CURSOR c_valid_inv (p_invoice_id IN NUMBER)
IS
SELECT COUNT (1)
FROM ap_invoice_payments aip, ap_checks ac
WHERE aip.check_id = ac.check_id
AND aip.invoice_id = p_invoice_id
AND aip.accounting_date > :p_fecha_hasta
AND DECODE (ac.status_lookup_code, 'VOIDED', 'Y', 'N') = 'N'
AND ROWNUM = 1;

CURSOR c_valid_inv2 (p_invoice_id IN NUMBER)
IS
SELECT COUNT (1)
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = 'PREPAY'
AND accounting_date > :p_fecha_hasta
AND ROWNUM = 1;

CURSOR c_dist_split (
p_invoice_id IN NUMBER,
p_line_type_lookup_code IN VARCHAR2
)
IS
SELECT /*+ INDEX ( xxap_invoice_aging_all XXAP_INVOICE_AGING_ALL_N1) */
segment1, sum_dist_amount
FROM xxap_invoice_aging_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = p_line_type_lookup_code
AND request_id = :cp_concurrent_id
AND org_id = :cp_org_id;

CURSOR c_dist_split2 (
p_invoice_id IN NUMBER,
p_line_type_lookup_code IN VARCHAR2,
p_segment1 IN VARCHAR2
)
IS
SELECT /*+ INDEX ( xxap_invoice_aging_all XXAP_INVOICE_AGING_ALL_N1) */
SUM (DECODE (segment1, p_segment1, 1, 0)),
SUM (DECODE (segment1, p_segment1, 0, sum_dist_amount))
FROM xxap_invoice_aging_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = p_line_type_lookup_code
AND request_id = :cp_concurrent_id
AND org_id = :cp_org_id;

CURSOR c_segment_description (
p_value IN VARCHAR2,
p_value_set_id IN NUMBER
-- Added by Quanam SR 10-04-2013 RFC#1140830
,
p_parent_from IN VARCHAR2,
p_parent_to IN VARCHAR2
--
)
IS
/*Select t.description
From fnd_flex_values_tl t,
fnd_flex_values b
Where b.flex_value_id = t.flex_value_id
And t.LANGUAGE = USERENV ('LANG')
And b.flex_value = p_value
And b.flex_value_set_id = p_value_set_id
And b.summary_flag = 'N'
And b.enabled_flag = 'Y';*/
SELECT t.description, ffv.parent_flex_value
FROM fnd_flex_values_tl t,
fnd_flex_values b,
fnd_flex_value_norm_hierarchy ffv
WHERE b.flex_value_id = t.flex_value_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.flex_value = p_value
AND b.flex_value_set_id = p_value_set_id
AND ffv.flex_value_set_id = p_value_set_id
AND p_value BETWEEN ffv.child_flex_value_low
AND ffv.child_flex_value_high
-- Commented by Quanam SR 10-04-2013 RFC#1140830
-- and ffv.parent_flex_value between :P_PARENT_FROM and :P_PARENT_TO
-- and ffv.parent_flex_value like '03%'
-- Added by Quanam SR 10-04-2013 RFC#1140830
AND ffv.parent_flex_value IN (
SELECT lookup_code
FROM fnd_lookup_values_vl flv
WHERE ( NVL ('', territory_code) = territory_code
OR territory_code IS NULL
)
AND lookup_type = 'EMR AP PARENT FLEX VALUE LAM'
AND (lookup_type LIKE 'EMR AP PARENT FLEX VALUE LAM')
AND (view_application_id = 3)
AND (security_group_id = 0)
AND NVL (enabled_flag, 'Y') = 'Y'
AND flv.meaning BETWEEN p_parent_from AND p_parent_to)
--
AND b.summary_flag = 'N'
AND b.enabled_flag = 'Y';

CURSOR c_segment_description2 (p_value IN VARCHAR2, p_value_set_id IN NUMBER)
IS
SELECT t.description, ffv.parent_flex_value
FROM fnd_flex_values_tl t,
fnd_flex_values b,
fnd_flex_value_norm_hierarchy ffv
WHERE b.flex_value_id = t.flex_value_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.flex_value = p_value
AND b.flex_value_set_id = p_value_set_id
AND ffv.flex_value_set_id = p_value_set_id
AND p_value BETWEEN ffv.child_flex_value_low
AND ffv.child_flex_value_high
--and ffv.parent_flex_value Like '2%'
AND ffv.parent_flex_value BETWEEN :p_parent_from AND :p_parent_to
AND ffv.parent_flex_value NOT LIKE '2ZZ%'
AND b.summary_flag = 'N'
AND b.enabled_flag = 'Y';

CURSOR c_group_description (p_value IN VARCHAR2, p_value_set_id IN NUMBER)
IS
SELECT t.description
FROM fnd_flex_values_tl t, fnd_flex_values b
WHERE b.flex_value_id = t.flex_value_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.flex_value = p_value
AND b.flex_value_set_id = p_value_set_id;
BEGIN
BEGIN
/*--12-AUG-2011 LAM Support / AMalatesta v8.0 added group_by in the query to table fnd_id_flex_segments--*/
SELECT /*+ index_asc ( fifs fnd_id_flex_segments_u2) */
fifs.flex_value_set_id
INTO v_value_set_id
FROM fnd_id_flex_segments fifs
WHERE fifs.segment_name = 'XXGL_BUSINESS_UNIT'
AND fifs.id_flex_code = 'GL#'
GROUP BY fifs.flex_value_set_id;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE ('1',
'OTHERS for search in fnd_id_flex_segments: '
|| SUBSTR (SQLERRM, 1, 100)
);
END;

-- Added by Quanam SR 10-04-2013 RFC#1140830
BEGIN
SELECT meaning
INTO v_chr_parent_from
FROM fnd_lookup_values_vl
WHERE ( NVL ('', territory_code) = territory_code
OR territory_code IS NULL
)
AND lookup_type = 'EMR AP PARENT FLEX VALUE LAM'
AND (lookup_type LIKE 'EMR AP PARENT FLEX VALUE LAM')
AND (view_application_id = 3)
AND (security_group_id = 0)
AND NVL (enabled_flag, 'Y') = 'Y'
AND lookup_code = :p_parent_from;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
srw.MESSAGE
('18',
'NO_DATA_FOUND for search in PARENT FLEX VALUE FROM: '
|| SUBSTR (SQLERRM, 1, 100)
);
WHEN OTHERS
THEN
srw.MESSAGE ('19',
'OTHERS for search in PARENT FLEX VALUE FROM: '
|| SUBSTR (SQLERRM, 1, 100)
);
END;

BEGIN
SELECT meaning
INTO v_chr_parent_to
FROM fnd_lookup_values_vl
WHERE ( NVL ('', territory_code) = territory_code
OR territory_code IS NULL
)
AND lookup_type = 'EMR AP PARENT FLEX VALUE LAM'
AND (lookup_type LIKE 'EMR AP PARENT FLEX VALUE LAM')
AND (view_application_id = 3)
AND (security_group_id = 0)
AND NVL (enabled_flag, 'Y') = 'Y'
AND lookup_code = :p_parent_to;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
srw.MESSAGE ('19',
'NO_DATA_FOUND for search in PARENT FLEX VALUE TO: '
|| SUBSTR (SQLERRM, 1, 100)
);
WHEN OTHERS
THEN
srw.MESSAGE ('19',
'OTHERS for search in PARENT FLEX VALUE TO: '
|| SUBSTR (SQLERRM, 1, 100)
);
END;

srw.MESSAGE ('20', 'v_chr_parent_from : ' || v_chr_parent_from);
srw.MESSAGE ('20', 'v_chr_parent_to : ' || v_chr_parent_to);

--
FOR r_dates IN c_dates
LOOP
FOR r_distributions IN c_distributions (r_dates.start_date,
r_dates.end_date,
v_value_set_id
)
LOOP
v_found := 0;
v_dist_split := 0;
v_sum_dist_amount := 0;
v_valid_inv := 0;
v_amount_remaining := 0;
v_segment_desc := NULL;
v_group_desc := NULL;

/*
FOR r_dist_split in c_dist_split(r_distributions.invoice_id
,r_distributions.line_type_lookup_code)
LOOP
IF r_dist_split.segment1 = r_distributions.segment1
THEN
v_found:=1;
ELSIF r_dist_split.sum_dist_amount !=0 AND r_dist_split.segment1 != r_distributions.segment1
THEN
v_dist_split:=1;
END IF;
END LOOP;*/
BEGIN
OPEN c_dist_split2 (r_distributions.invoice_id,
r_distributions.line_type_lookup_code,
r_distributions.segment1
);

FETCH c_dist_split2
INTO v_found, v_sum_dist_amount;

CLOSE c_dist_split2;
EXCEPTION
WHEN OTHERS
THEN
v_found := 0;
v_sum_dist_amount := 0;
END;

IF NVL (v_sum_dist_amount, 0) != 0
THEN
v_dist_split := 1;
END IF;

IF NVL (v_found, 0) = 0
THEN
IF :p_num_currency_func = 'ARS'
THEN
BEGIN
OPEN c_segment_description (r_distributions.segment1,
v_value_set_id,
-- Added by Quanam SR 10-04-2013 RFC#1140830
v_chr_parent_from,
v_chr_parent_to
--
);

FETCH c_segment_description
INTO v_segment_desc, v_parent_flex_value;

CLOSE c_segment_description;
EXCEPTION
WHEN OTHERS
THEN
v_segment_desc := NULL;
v_parent_flex_value := NULL;
END;
ELSE
BEGIN
OPEN c_segment_description2 (r_distributions.segment1,
v_value_set_id
);

FETCH c_segment_description2
INTO v_segment_desc, v_parent_flex_value;

CLOSE c_segment_description2;
EXCEPTION
WHEN OTHERS
THEN
v_segment_desc := NULL;
v_parent_flex_value := NULL;
END;
END IF;

BEGIN
OPEN c_group_description
(v_parent_flex_value --r_distributions.parent_flex_value
,
v_value_set_id
);

FETCH c_group_description
INTO v_group_desc;

CLOSE c_group_description;
EXCEPTION
WHEN OTHERS
THEN
v_group_desc := NULL;
END;

IF v_segment_desc IS NOT NULL AND v_group_desc IS NOT NULL
THEN
FOR r_invoices IN c_invoices (r_distributions.invoice_id)
LOOP
v_found := 0;
v_valid_inv := 0;

BEGIN
OPEN c_payment (r_distributions.invoice_id);

FETCH c_payment
INTO v_due_date, v_amount_remaining;

CLOSE c_payment;
EXCEPTION
WHEN OTHERS
THEN
v_due_date := NULL;
v_amount_remaining := NULL;
END;

IF ( r_invoices.cancelled_date IS NULL
OR ( r_invoices.cancelled_date IS NOT NULL
AND r_invoices.cancelled_date > :p_fecha_hasta
)
)
THEN
IF NVL (v_amount_remaining, 0) != 0
THEN
v_valid_inv := 1;
END IF;

IF v_valid_inv !=
1
--and r_invoices.cancelled_date > :p_fecha_hasta
THEN
BEGIN
OPEN c_valid_inv (r_distributions.invoice_id);

FETCH c_valid_inv
INTO v_valid_inv;

CLOSE c_valid_inv;
EXCEPTION
WHEN OTHERS
THEN
v_valid_inv := 0;
END;
END IF;

IF NVL (v_valid_inv, 0) != 1
THEN
BEGIN
OPEN c_valid_inv2 (r_distributions.invoice_id);

FETCH c_valid_inv2
INTO v_valid_inv;

CLOSE c_valid_inv2;
EXCEPTION
WHEN OTHERS
THEN
v_valid_inv := 0;
END;
END IF;
END IF;

IF NVL (v_valid_inv, 0) = 1
THEN
IF r_invoices.invoice_currency_code <>
:p_num_currency_func
THEN
BEGIN
SELECT COUNT (1)
INTO v_found
FROM xxap_inv_currency_aging_tmp
WHERE invoice_currency_code =
r_invoices.invoice_currency_code;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for search in xxap_inv_currency_aging_all with invoice_currency_code: '
|| r_invoices.invoice_currency_code
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;

IF v_found = 0
THEN
BEGIN
SELECT ROUND (DECODE (r.eop_rate,
0, 0,
(1 / r.eop_rate)
),
5
)
INTO v_exch_rate_reexp
FROM gl_period_statuses gp,
fnd_application fa,
gl_translation_rates r
WHERE gp.set_of_books_id = :p_sob_id
AND gp.application_id = fa.application_id
AND application_short_name = 'SQLGL'
AND r.set_of_books_id = gp.set_of_books_id
AND r.period_name = gp.period_name
AND r.to_currency_code =
r_invoices.invoice_currency_code
AND gp.end_date =
(SELECT MAX (gp.end_date)
FROM gl_period_statuses gp,
fnd_application fa,
gl_translation_rates r
WHERE gp.set_of_books_id = :p_sob_id
AND gp.application_id =
fa.application_id
AND application_short_name =
'SQLGL'
AND r.set_of_books_id =
gp.set_of_books_id
AND r.period_name = gp.period_name
AND r.to_currency_code =
r_invoices.invoice_currency_code
AND gp.end_date <= :p_fecha_hasta);
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for search in gl_period_statuses with invoice_currency_code: '
|| r_invoices.invoice_currency_code
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;

BEGIN
INSERT INTO xxap_inv_currency_aging_tmp
(invoice_currency_code,
rate
)
VALUES (r_invoices.invoice_currency_code,
v_exch_rate_reexp
);

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for insert into xxap_inv_currency_aging_all with invoice_currency_code: '
|| r_invoices.invoice_currency_code
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
END IF;
END IF;

BEGIN
INSERT INTO xxap_invoice_aging_all
(invoice_id,
vendor_id,
invoice_num,
invoice_currency_code,
payment_currency_code,
payment_cross_rate,
vendor_site_id,
amount_paid,
invoice_date,
invoice_type_lookup_code,
accts_pay_code_combination_id,
exchange_rate,
cancelled_date,
org_id, gl_date,
line_type_lookup_code,
segment1,
segment_description, segment_group,
group_description, sum_dist_amount,
payment_status_flag,
distribution_split, terms_id,
due_date, request_id,
created_by, creation_date
)
VALUES (r_invoices.invoice_id,
r_invoices.vendor_id,
r_invoices.invoice_num,
r_invoices.invoice_currency_code,
r_invoices.payment_currency_code,
r_invoices.payment_cross_rate,
r_invoices.vendor_site_id,
r_invoices.amount_paid,
r_invoices.invoice_date,
r_invoices.invoice_type_lookup_code,
r_invoices.accts_pay_code_combination_id,
r_invoices.exchange_rate,
r_invoices.cancelled_date,
r_invoices.org_id, r_invoices.gl_date,
r_distributions.line_type_lookup_code,
r_distributions.segment1,
v_segment_desc, v_parent_flex_value,
--r_distributions.parent_flex_value,
v_group_desc, r_distributions.amount,
r_invoices.payment_status_flag,
v_dist_split, r_invoices.terms_id,
v_due_date, :cp_concurrent_id,
:cp_user_id, SYSDATE
);

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for insert into xxap_invoice_aging_all with invoice_id: '
|| TO_CHAR (r_distributions.invoice_id)
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;

IF v_dist_split != 0
THEN
BEGIN
UPDATE xxap_invoice_aging_all
SET distribution_split = v_dist_split,
last_update_date = SYSDATE,
last_updated_by = :cp_user_id
WHERE invoice_id = r_distributions.invoice_id
AND line_type_lookup_code =
r_distributions.line_type_lookup_code
AND segment1 != r_distributions.segment1
AND sum_dist_amount != 0
AND request_id = :cp_concurrent_id
AND org_id = :cp_org_id;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for update xxap_invoice_aging_all with invoice_id: '
|| TO_CHAR (r_distributions.invoice_id)
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
END IF;
END IF;
END LOOP;
END IF;
ELSE
BEGIN
UPDATE xxap_invoice_aging_all
SET sum_dist_amount =
(sum_dist_amount + r_distributions.amount
),
distribution_split = v_dist_split,
last_update_date = SYSDATE,
last_updated_by = :cp_user_id
WHERE invoice_id = r_distributions.invoice_id
AND line_type_lookup_code =
r_distributions.line_type_lookup_code
AND segment1 = r_distributions.segment1
AND request_id = :cp_concurrent_id
AND org_id = :cp_org_id;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for update xxap_invoice_aging_all with invoice_id: '
|| TO_CHAR (r_distributions.invoice_id)
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;

BEGIN
UPDATE xxap_invoice_aging_all
SET distribution_split = v_dist_split,
last_update_date = SYSDATE,
last_updated_by = :cp_user_id
WHERE invoice_id = r_distributions.invoice_id
AND line_type_lookup_code =
r_distributions.line_type_lookup_code
AND segment1 != r_distributions.segment1
AND sum_dist_amount != 0
AND request_id = :cp_concurrent_id
AND org_id = :cp_org_id;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for update xxap_invoice_aging_all with invoice_id: '
|| TO_CHAR (r_distributions.invoice_id)
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
END IF;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE (1,
'Error en CREATE_TEM_TABLE_INVOICES: '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
Re: Query taking more time [message #603813 is a reply to message #603810] Thu, 19 December 2013 09:17 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
Cookiemaster:
you mean to say that we need to remove the OR condition.As i don't see that if we remove OR then something else fit there
Re: Query taking more time [message #603814 is a reply to message #603810] Thu, 19 December 2013 09:19 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
What a big block of unformatted code.

You've already been asked to read and follow How to use [code] tags and make your code easier to read?
If you don't do it I'm not going to help you any further, because I've got better things to do than scan that code as it is.

Also, if you're going to post a procedure like that it helps if it comes with a question. What do you expect us to do with that?
Re: Query taking more time [message #603815 is a reply to message #603813] Thu, 19 December 2013 09:20 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
Yesterday i have posted the explain plan and found that the subquery which is
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 > '22-Nov-2013'
)
))
is taking more time in retriving data when i ran seperately
Re: Query taking more time [message #603816 is a reply to message #603813] Thu, 19 December 2013 09:23 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
nishant87 wrote on Thu, 19 December 2013 15:17
Cookiemaster:
you mean to say that we need to remove the OR condition.As i don't see that if we remove OR then something else fit there


No you need to add a OR.
One of the selects has a combined exists/not exists clause, the other doesn't.
So to combine the two you need to add OR so that the exists/not exists is only applied when appropriate (i.e. depending on the sole other difference between the two).
Re: Query taking more time [message #603818 is a reply to message #603815] Thu, 19 December 2013 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
nishant87 wrote on Thu, 19 December 2013 15:20
Yesterday i have posted the explain plan and found that the subquery which is
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 > '22-Nov-2013'
)
))
is taking more time in retriving data when i ran seperately


And we commented on that at the time, so why bring it up again?
Re: Query taking more time [message #603821 is a reply to message #603818] Thu, 19 December 2013 09:33 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
Just wanna tell you that it takes more time when i ran it seperately
Re: Query taking more time [message #603831 is a reply to message #603821] Thu, 19 December 2013 12:31 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
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 > '22-Nov-2013'
)
))
Re: Query taking more time [message #603832 is a reply to message #603831] Thu, 19 December 2013 12:34 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
i am not familiar with the code tag,so sometime post looks different...i do that in toad and when i paste same in forum,it looks different.i am really sorry lalit for the inconvenience caused to you
Re: Query taking more time [message #603833 is a reply to message #603831] Thu, 19 December 2013 12:36 Go to previous messageGo to next message
John Watson
Messages: 4558
Registered: January 2010
Location: Global Village
Senior Member
Your refusal to format your code is downright insulting. Has it ever occurred to you that if you consistently annoy the people with whom you work you are unlikely to progress very far?
Goodbye.
Re: Query taking more time [message #603839 is a reply to message #603832] Thu, 19 December 2013 12:53 Go to previous message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
http://en.wikipedia.org/wiki/Plonk_(Usenet)
Previous Topic: Performance Monitoring .
Next Topic: Oracle alerts
Goto Forum:
  


Current Time: Thu Sep 18 04:56:17 CDT 2014

Total time taken to generate the page: 0.16982 seconds