The following SQL is a good example of scalar sub query tuning.
The original query takes few hours to run. Please see the query and the plan below.
SELECT SUM (FEE_PAID), MAX (INVOICE_DATE)
FROM
(SELECT STH.DATE_OF_TRAN AS INVOICE_DATE,STH.PENDING_SEQ_NO AS PENDING_TRAN_NUMBER,
(SELECT FEE_AMT
FROM FEE_TAB FT1
WHERE T_SEQ_NO =
(SELECT MAX (T_SEQ_NO)
FROM FEE_TAB FT2
WHERE P_SEQ_NO = STH.P_SEQ_NO
AND TYPE = 'AC')) AS FEE_PAID
FROM TRAN_TAB STH
)
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 386 (100)| | | | |
| 1 | FILTER | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 12M| 118M| 7168 (1)| 00:02:10 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 12M| 118M| 7168 (1)| 00:02:10 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | FEE_TAB | 12M| 118M| 7168 (1)| 00:02:10 | Q1,00 | PCWP | |
| 6 | SORT AGGREGATE | | 1 | 15 | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| FEE_TAB | 1 | 15 | 12 (0)| 00:00:01 | | | |
| 8 | INDEX RANGE SCAN | FEE_TAB_IDX2 | 11 | | 3 (0)| 00:00:01 | | | |
| 9 | SORT AGGREGATE | | 1 | 14 | | | | | |
| 10 | PX COORDINATOR | | | | | | | | |
| 11 | PX SEND QC (RANDOM) | :TQ20000 | 1 | 14 | | | Q2,00 | P->S | QC (RAND) |
| 12 | SORT AGGREGATE | | 1 | 14 | | | Q2,00 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 443K| 6070K| 386 (1)| 00:00:07 | Q2,00 | PCWC | |
| 14 | TABLE ACCESS FULL | TRAN_TAB | 443K| 6070K| 386 (1)| 00:00:07 | Q2,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
What is this statement doing?
For each row in TRAN_TAB table, using the P_SEQ_NO, find the latest T_SEQ_NO where TYPE = ‘AC’.
Then for the that T_SEQ_NO, find the FEE_AMT and return it ( T_SEQ_NO is unique)
Finally sum up all FEE_AMTs. Also find the latest INVOICE_DATE.
Why is it taking long?
The query that should ideally take only few seconds is taking few hours.
Parallel query is enabled and both tables are with Parallel Degree 3 Instance 2. Parallel plan generated seems to be not optimal.
It is not clear why execution plan doesn’t start with TRAN_TAB table. It may be a bug or a weakness in the optimizer.
What can be done?
Often, a fast way to fix these kind of issues is to write the SQL in a different way(SQL rewrite.) Here we can think of at least four genuine options.
1. Parallel plans are known to maximize UPU usage. So we could disable parallel with a hint and see if we get a plan that is better.
2. The scalar query has an inner query too. The P_SEQ_NO = STH.P_SEQ_NO filter is only in the inner query. While this is perfectly normal, we could add the P_SEQ_NO = STH.P_SEQ_NO filter to the outer query too. This will not change the result set because T_SEQ_NO selected from the inner query has to have the same P_SEQ_NO. A legitimate rewrite. Please also note that P_SEQ_NO is indexed.
3. We can use analytical function to avoid the inner query. Hopefully that will appear less ‘complicated’ to the optimizer.
4. We can convert the scalar subquery to a join. This is also an easy change here. But for complex statement, this can get a bit difficult.
Basic Analysis
1)TRAN_TAB is a 442595 row, unpartitioned table with Parallel Degree 3 instance 2
Indexes:
TRAN_TAB_IDX1 on (P_SEQ_NO)
TRAN_TAB_IDX2 on (TRAN_SEQ_NO)
TRAN_TAB_IDX3 on (DATE_OF_TRAN)
Number of distinct values of P_SEQ_NO=36580
2)FEE_TAB is a 11,396,005 row, unpartitioned table with Parallel Degree 3 instance 2
Indexes:
FEE_TAB_IDX1 on (DATE_OF_TRAN )
FEE_TAB_IDX2 on (P_SEQ_NO )
FEE_TAB_IDX3 on (FEE_TYPE_PD)
FEE_TAB_IDX4 on (T_SEQ_NO)
Number of distinct values of P_SEQ_NO = 1 082,083
Number of distinct values of FEE_TYPE_PD = 23
Number of distinct values of T_SEQ_NO=11,396,005 (Unique)
Nearly half a million rows in the table and we need to do the look up for all of them. But the interesting thing to note here is that there are only 36580 distinct P_SEQ_NOs
Testing Rewrites
1. Disable Parallel With a Hint
13:46:50 SQL> SELECT /*+noparallel*/ SUM (FEE_PAID), MAX (INVOICE_DATE)
13:46:50 2 FROM (SELECT STH.DATE_OF_TRAN AS INVOICE_DATE,
13:46:50 3 STH.PENDING_SEQ_NO AS PENDING_TRAN_NUMBER,
13:46:50 4 (SELECT FEE_AMT
13:46:50 5 FROM FEE_TAB FT1
13:46:50 6 WHERE T_SEQ_NO =
13:46:50 7 (SELECT MAX (T_SEQ_NO)
13:46:50 8 FROM FEE_TAB FT2
13:46:50 9 WHERE P_SEQ_NO = STH.P_SEQ_NO
13:46:50 10 AND TYPE = 'AC'))
13:46:50 11 AS FEE_PAID
13:46:50 12 FROM TRAN_TAB STH);
SUM(FEE_PAID) MAX(INVOI
------------- ---------
-111304785 13-JAN-15
Elapsed: 00:00:20.69
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2085 (1)| 00:00:38 |
| 1 | TABLE ACCESS BY INDEX ROWID | FEE_TAB | 1 | 10 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FEE_TAB_IDX4 | 1 | | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 15 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| FEE_TAB | 1 | 15 | 12 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | FEE_TAB_IDX2 | 11 | | 3 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 14 | | |
| 7 | TABLE ACCESS FULL | TRAN_TAB | 443K| 6070K| 2085 (1)| 00:00:38 |
-------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5174287 consistent gets
0 physical reads
2. Adding "P_SEQ_NO = STH.P_SEQ_NO" Filter to the Inner Query of the Scalar Subquery
13:47:11 SQL> SELECT SUM (FEE_PAID), MAX (INVOICE_DATE)
13:47:11 2 FROM (SELECT STH.DATE_OF_TRAN AS INVOICE_DATE,
13:47:11 3 STH.PENDING_SEQ_NO AS PENDING_TRAN_NUMBER,
13:47:11 4 (SELECT FEE_AMT
13:47:11 5 FROM FEE_TAB FT1
13:47:11 6 WHERE P_SEQ_NO = STH.P_SEQ_NO
13:47:11 7 AND T_SEQ_NO =
13:47:11 8 (SELECT MAX (T_SEQ_NO)
13:47:11 9 FROM FEE_TAB FT2
13:47:11 10 WHERE P_SEQ_NO = STH.P_SEQ_NO
13:47:11 11 AND TYPE = 'AC'))
13:47:11 12 AS FEE_PAID
13:47:11 13 FROM TRAN_TAB STH);
SUM(FEE_PAID) MAX(INVOI
------------- ---------
-111304785 13-JAN-15
Elapsed: 00:00:04.13
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 386 (1)| 00:00:07 | | | |
|* 1 | TABLE ACCESS BY INDEX ROWID | FEE_TAB | 1 | 16 | 4 (0)| 00:00:01 | | | |
|* 2 | INDEX RANGE SCAN | FEE_TAB_IDX4 | 1 | | 3 (0)| 00:00:01 | | | |
| 3 | SORT AGGREGATE | | 1 | 15 | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| FEE_TAB | 1 | 15 | 12 (0)| 00:00:01 | | | |
|* 5 | INDEX RANGE SCAN | FEE_TAB_IDX2 | 11 | | 3 (0)| 00:00:01 | | | |
| 6 | SORT AGGREGATE | | 1 | 14 | | | | | |
| 7 | PX COORDINATOR | | | | | | | | |
| 8 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 14 | | | Q1,00 | P->S | QC (RAND) |
| 9 | SORT AGGREGATE | | 1 | 14 | | | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 443K| 6070K| 386 (1)| 00:00:07 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL | TRAN_TAB | 443K| 6070K| 386 (1)| 00:00:07 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
5166543 consistent gets
0 physical reads
3. Use Analytical Function to Avoid the Inner Query
13:47:39 SQL> SELECT SUM (FEE_PAID), MAX (INVOICE_DATE)
13:47:39 2 FROM (SELECT STH.DATE_OF_TRAN AS INVOICE_DATE,
13:47:39 3 STH.PENDING_SEQ_NO AS PENDING_TRAN_NUMBER,
13:47:39 4 (SELECT DISTINCT
13:47:39 5 (FIRST_VALUE (FEE_AMT) OVER (ORDER BY T_SEQ_NO DESC))
13:47:39 6 FROM FEE_TAB FT2
13:47:39 7 WHERE P_SEQ_NO = STH.P_SEQ_NO
13:47:39 8 AND TYPE = 'AC')
13:47:39 9 AS FEE_PAID
13:47:39 10 FROM TRAN_TAB STH);
SUM(FEE_PAID) MAX(INVOI
------------- ---------
-111304785 13-JAN-15
Elapsed: 00:00:04.67
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 386 (1)| 00:00:07 | | | |
| 1 | SORT UNIQUE | | 1 | 19 | 14 (15)| 00:00:01 | | | |
| 2 | WINDOW SORT | | 1 | 19 | 14 (15)| 00:00:01 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| FEE_TAB | 1 | 19 | 12 (0)| 00:00:01 | | | |
|* 4 | INDEX RANGE SCAN | FEE_TAB_IDX2 | 11 | | 3 (0)| 00:00:01 | | | |
| 5 | SORT AGGREGATE | | 1 | 14 | | | | | |
| 6 | PX COORDINATOR | | | | | | | | |
| 7 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 14 | | | Q1,00 | P->S | QC (RAND) |
| 8 | SORT AGGREGATE | | 1 | 14 | | | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | 443K| 6070K| 386 (1)| 00:00:07 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL | TRAN_TAB | 443K| 6070K| 386 (1)| 00:00:07 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
4643613 consistent gets
0 physical reads
4. Converting Scalar Subquery to a Join
16:05:34 SQL> SELECT
16:05:34 2 SUM (FEE_PAID), MAX (INVOICE_DATE)
16:05:34 3 FROM (SELECT /*+ordered*/
16:05:34 4 STH.DATE_OF_TRAN AS INVOICE_DATE, STH.PENDING_SEQ_NO AS PENDING_TRAN_NUMBER, v.FEE_PAID
16:05:34 5 FROM TRAN_TAB STH,
16:05:34 6 (SELECT DISTINCT P_SEQ_NO, (FIRST_VALUE (FEE_AMT) OVER (ORDER BY T_SEQ_NO DESC)) FEE_PAID
16:05:34 7 FROM FEE_TAB
16:05:34 8 WHERE TYPE = 'AC') v
16:05:34 9 WHERE STH.P_SEQ_NO = v.P_SEQ_NO);
SUM(FEE_PAID) MAX(INVOI
------------- ---------
-133753785 13-JAN-15
Elapsed: 00:00:05.36
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | | 7560 (1)| 00:02:17 | | | |
| 1 | SORT AGGREGATE | | 1 | 40 | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ20002 | 1 | 40 | | | | Q2,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 40 | | | | Q2,02 | PCWP | |
|* 5 | HASH JOIN | | 550K| 20M| | 7560 (1)| 00:02:17 | Q2,02 | PCWP | |
| 6 | PX RECEIVE | | 443K| 6070K| | 386 (1)| 00:00:07 | Q2,02 | PCWP | |
| 7 | PX SEND HASH | :TQ20001 | 443K| 6070K| | 386 (1)| 00:00:07 | Q2,01 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 443K| 6070K| | 386 (1)| 00:00:07 | Q2,01 | PCWC | |
| 9 | TABLE ACCESS FULL | TRAN_TAB | 443K| 6070K| | 386 (1)| 00:00:07 | Q2,01 | PCWP | |
| 10 | BUFFER SORT | | | | | | | Q2,02 | PCWC | |
| 11 | PX RECEIVE | | 540K| 13M| | 7173 (1)| 00:02:10 | Q2,02 | PCWP | |
| 12 | PX SEND HASH | :TQ20000 | 540K| 13M| | 7173 (1)| 00:02:10 | | S->P | HASH |
| 13 | VIEW | | 540K| 13M| | 7173 (1)| 00:02:10 | | | |
| 14 | SORT UNIQUE | | 540K| 9M| 16M| 7173 (1)| 00:02:10 | | | |
| 15 | WINDOW BUFFER | | 540K| 9M| | 7173 (1)| 00:02:10 | | | |
| 16 | PX COORDINATOR | | | | | | | | | |
| 17 | PX SEND QC (ORDER) | :TQ10001 | 540K| 9M| | 7173 (1)| 00:02:10 | Q1,01 | P->S | QC (ORDER) |
| 18 | SORT ORDER BY | | 540K| 9M| | 7173 (1)| 00:02:10 | Q1,01 | PCWP | |
| 19 | PX RECEIVE | | 540K| 9M| | 7164 (1)| 00:02:09 | Q1,01 | PCWP | |
| 20 | PX SEND RANGE | :TQ10000 | 540K| 9M| | 7164 (1)| 00:02:09 | Q1,00 | P->P | RANGE |
| 21 | PX BLOCK ITERATOR | | 540K| 9M| | 7164 (1)| 00:02:09 | Q1,00 | PCWC | |
|* 22 | TABLE ACCESS FULL| FEE_TAB | 540K| 9M| | 7164 (1)| 00:02:09 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
96 recursive calls
0 db block gets
116736 consistent gets
3949 physical reads
Conclusion
Rewrites using analytical function or adding P_SEQ_NO = STH.P_SEQ_NO to the outer query in the scalar sub query seems to do the trick.
The amount of rewrites in this case is very minimal. All four rewrites give good improvement compared to the original query. But the join approach has significantly less LIO.
I personally think the issue with the original query may be a combination of the following exposing a weakness of the optimizer:
1) parallel, and
2) the fact that the correlation to the scalar sub query is a level deep
Note : 12c seems to have some mechanism to unnest scalar sub queries. But I did not get a chance to explore that yet.
I have been examinating out many of your posts and i can state pretty nice stuff. I will definitely bookmark your blog.
Great ?V I should certainly pronounce, impressed with your web site. I had no trouble navigating through all the tabs as well as related information ended up being truly easy to do to access. I recently found what I hoped for before you know it at all. Quite unusual. Is likely to appreciate it for those who add forums or anything, website theme . a tones way for your client to communicate. Excellent task..
Hello I am so happy I found your webpage, I really found you by mistake, while I was searching on Google for something else, Nonetheless I am here now and would just like to say many thanks for a remarkable post and a all round enjoyable blog (I also love the theme/design), I don’t have time to read it all at the minute but I have book-marked it and also added your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the great work.
What is Zen Cortex? ZenCortex is not just another drop in the ocean of dietary supplements
Hello there, I found your site by the use of Google even as searching for a similar subject, your web site came up, it seems to be good. I have bookmarked it in my google bookmarks.
Woh I love your posts, saved to fav! .
There are certainly a whole lot of particulars like that to take into consideration. That could be a nice point to carry up. I provide the ideas above as basic inspiration however clearly there are questions like the one you deliver up where the most important thing will likely be working in honest good faith. I don?t know if finest practices have emerged around issues like that, however I am certain that your job is clearly recognized as a good game. Both boys and girls feel the influence of only a moment’s pleasure, for the rest of their lives.
Hello my friend! I wish to say that this article is amazing, great written and include approximately all vital infos. I would like to look more posts like this .
Glad to be one of several visitors on this awe inspiring website : D.
Fantastic website. Plenty of useful info here. I am sending it to some pals ans also sharing in delicious. And of course, thanks in your effort!
Some really interesting details you have written.Aided me a lot, just what I was looking for : D.
Valuable info. Lucky me I found your website by accident, and I am shocked why this accident did not happened earlier! I bookmarked it.
It’s laborious to seek out knowledgeable folks on this subject, but you sound like you understand what you’re talking about! Thanks
I like what you guys are up also. Such clever work and reporting! Carry on the superb works guys I have incorporated you guys to my blogroll. I think it’ll improve the value of my website :).
Thank you for the sensible critique. Me and my neighbor were just preparing to do a little research about this. We got a grab a book from our area library but I think I learned more from this post. I’m very glad to see such fantastic info being shared freely out there.
Some genuinely nice and useful info on this web site, besides I think the style holds great features.
Hi , I do believe this is an excellent blog. I stumbled upon it on Yahoo , i will come back once again. Money and freedom is the best way to change, may you be rich and help other people.
Good V I should certainly pronounce, impressed with your web site. I had no trouble navigating through all the tabs as well as related information ended up being truly simple to do to access. I recently found what I hoped for before you know it at all. Quite unusual. Is likely to appreciate it for those who add forums or something, web site theme . a tones way for your client to communicate. Nice task..
Hi there! I know this is kinda off topic however I’d figured I’d ask. Would you be interested in trading links or maybe guest writing a blog post or vice-versa? My website discusses a lot of the same topics as yours and I feel we could greatly benefit from each other. If you are interested feel free to shoot me an email. I look forward to hearing from you! Fantastic blog by the way!
Pretty! This was a really wonderful post. Thank you for your provided information.
My brother recommended I might like this web site. He was totally right. This post actually made my day. You can not imagine simply how much time I had spent for this info! Thanks!
Rattling wonderful information can be found on blog. “Prayer is the wing wherewith the soul flies to heaven, and meditation the eye wherewith we see God.” by Ambrose of Milan.
Thanks for another fantastic post. The place else may just anyone get that type of info in such an ideal approach of writing? I have a presentation next week, and I am on the search for such information.
Oh my goodness! an incredible article dude. Thanks Nevertheless I am experiencing problem with ur rss . Don’t know why Unable to subscribe to it. Is there anybody getting identical rss problem? Anyone who knows kindly respond. Thnkx
I’m so happy to read this. This is the kind of manual that needs to be given and not the accidental misinformation that’s at the other blogs. Appreciate your sharing this best doc.
Rattling excellent information can be found on website. “An executive is a person who always decides sometimes he decides correctly, but he always decides.” by John H. Patterson.
Absolutely indited articles, regards for entropy. “The earth was made round so we would not see too far down the road.” by Karen Blixen.
I got what you mean , thanks for putting up.Woh I am pleased to find this website through google. “Wisdom doesn’t necessarily come with age. Sometimes age just shows up by itself.” by Woodrow Wilson.
Good day! I could have sworn I’ve been to this website before but after reading through some of the post I realized it’s new to me. Anyhow, I’m definitely glad I found it and I’ll be bookmarking and checking back frequently!
I like gathering utile information , this post has got me even more info! .
My coder is trying to persuade me to move to .net from PHP. I have always disliked the idea because of the costs. But he’s tryiong none the less. I’ve been using Movable-type on a number of websites for about a year and am anxious about switching to another platform. I have heard excellent things about blogengine.net. Is there a way I can import all my wordpress posts into it? Any help would be really appreciated!
Have you ever thought about writing an ebook or guest authoring on other sites? I have a blog based upon on the same information you discuss and would really like to have you share some stories/information. I know my subscribers would value your work. If you’re even remotely interested, feel free to shoot me an e mail.
You are my breathing in, I possess few web logs and infrequently run out from post :). “To die for a religion is easier than to live it absolutely.” by Jorge Luis Borges.
My brother suggested I might like this blog. He was totally right. This post actually made my day. You cann’t imagine simply how much time I had spent for this info! Thanks!
Just a smiling visitor here to share the love (:, btw outstanding style. “The price one pays for pursuing a profession, or calling, is an intimate knowledge of its ugly side.” by James Arthur Baldwin.
I think this internet site contains some really great information for everyone :D. “Years wrinkle the skin, but to give up enthusiasm wrinkles the soul.” by Samuel Ullman.
I am continually searching online for articles that can aid me. Thank you!
Nice blog right here! Additionally your web site quite a bit up very fast! What web host are you the use of? Can I am getting your associate link on your host? I desire my website loaded up as quickly as yours lol
I noticed that your artoftuning.net website could be missing out on approximately a thousand visitors daily. Our AI powered traffic system is tailored to enhance your site’s visibility: https://ln.run/VZn5V
We’re offering a free trial that includes four thousand targeted visitors to show the potential benefits. After the trial, we can supply up to 250,000 targeted visitors per month. This opportunity could greatly amplify your website’s reach and visitors.
z6aqiq
A formidable share, I just given this onto a colleague who was doing a bit analysis on this. And he the truth is bought me breakfast as a result of I discovered it for him.. smile. So let me reword that: Thnx for the treat! However yeah Thnkx for spending the time to discuss this, I really feel strongly about it and love reading more on this topic. If doable, as you turn into experience, would you thoughts updating your weblog with more particulars? It’s extremely helpful for me. Huge thumb up for this blog submit!