SQL Query - Just too big [message #655490] |
Thu, 01 September 2016 00:04 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/9e73a3d5b2c7ccb2d1ad4f79073d81a9?s=64&d=mm&r=g) |
nathan59
Messages: 9 Registered: August 2016
|
Junior Member |
|
|
All
This query is taking way too long:
select distinct jd.producer_client_id, d.producer_id, jd.bkge_class
from journal_details jd
join journals j on jd.journal_id = j.journal_id
join dealer_codes d on d.dealer_code_id = j.dealer_code_id
join batches b on j.batch_id = b.batch_id
where b.committed_at >= '1-july-2013'
and jd.bkge_class not in ('EMM','MTD','MFRI','MFRO')
and exists (
select 1
from journal_details jd2
join journals j2 on jd2.journal_id = j2.journal_id
join dealer_codes d2 on d2.dealer_code_id = j2.dealer_code_id
join batches b2 on j2.batch_id = b2.batch_id
where b2.committed_at < '1-july-2013'
and j2.journal_type_id != 3
and d.producer_id = d2.producer_id
and jd.producer_client_id != jd2.producer_client_id
and jd.bkge_class = jd2.bkge_class
) and not exists (
select 1
from journal_details jd2
join journals j2 on jd2.journal_id = j2.journal_id
join dealer_codes d2 on d2.dealer_code_id = j2.dealer_code_id
join batches b2 on j2.batch_id = b2.batch_id
where b2.committed_at < '1-july-2013'
and j2.journal_type_id != 3
and d.producer_id = d2.producer_id
and jd.producer_client_id = jd2.producer_client_id
and jd.bkge_class = jd2.bkge_class
) and not exists (
select 1
from fofa_override fo
where jd.producer_client_id = fo.producer_client_id
and d.producer_id = fo.producer_id
and jd.bkge_class = fo.bkge_class
)
Other details:
select count(*) from JOURNAL_DETAILS
18842453
select count(*) from journals
151949
select count(*) from dealer_codes
2892
select count(*) from batches
37525
select count(*) from fofa_override
32029
Other info:
jd.bkge_class - Not very selective (< 100)
There are no composite index on JOURNAL_DETAILS
ANy help would be appreciated. Is it possible to rewrite using analytic functions?
|
|
|
|
Re: SQL Query - Just too big [message #655492 is a reply to message #655491] |
Thu, 01 September 2016 00:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/9e73a3d5b2c7ccb2d1ad4f79073d81a9?s=64&d=mm&r=g) |
nathan59
Messages: 9 Registered: August 2016
|
Junior Member |
|
|
Michael
My version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
This query runs for long time and eventually runs out temp.
MY question is: This joins same tables 3 times (one normal, one exists and one not exists). The difference is - committed_at >= '1-july-2013' or < '1-july-2013', and j2.journal_type_id != 3 in sub queries and and jd.producer_client_id != jd2.producer_client_id in one and and jd.producer_client_id = jd2.producer_client_id in the other.
Execution plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 1581310431
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TepSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72401 | 11M| | 59864 (25)| 00:01:04 |
| 1 | HASH UNIQUE | | 72401 | 11M| 12M| 59864 (25)| 00:01:04 |
|* 2 | HASH JOIN | | 72401 | 11M| | 57382 (26)| 00:01:02 |
|* 3 | HASH JOIN | | 8320 | 1243K| | 49151 (24)| 00:00:53 |
|* 4 | TABLE ACCESS FULL | BATCHES | 30506 | 625K| | 41 (32)| 00:00:01 |
|* 5 | HASH JOIN | | 13073 | 1685K| | 49106 (24)| 00:00:53 |
|* 6 | HASH JOIN RIGHT ANTI | | 266 | 30590 | | 48875 (24)| 00:00:53 |
|* 7 | INDEX FAST FULL SCAN | FOFA_OVERRIDE_IDX02 | 31687 | 618K| | 16 (19)| 00:00:01 |
|* 8 | HASH JOIN ANTI | | 26606 | 2468K|220M| 48854 (24)| 00:00:53 |
|* 9 | HASH JOIN | | 2660K| 190M| | 10635 (49)| 00:00:12 |
|* 10 | HASH JOIN | | 22950 | 1232K| | 290 (34)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | BATCHES | 6096 | 125K| | 41 (32)| 00:00:01 |
|* 12 | HASH JOIN | | 180K| 5991K| | 237 (31)| 00:00:01 |
|* 13 | HASH JOIN | | 3416 | 68320 | | 8 (25)| 00:00:01 |
| 14 | TABLE ACCESS FULL | DEALER_CODES | 2714 | 27140 | | 3 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | DEALER_CODES | 2714 | 27140 | | 3 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | JOURNALS | 149K| 2047K| | 218 (28)| 00:00:01 |
|* 17 | INDEX FAST FULL SCAN | JOURNAL_DETAILS_IX06 | 16M| 323M| | 9271 (44)| 00:00:10 |
| 18 | VIEW | VW_SQ_1 | 10M| 207M| | 23433 (19)| 00:00:25 |
|* 19 | HASH JOIN | | 10M| 704M|976K| 23433 (19)| 00:00:25 |
|* 20 | HASH JOIN | | 84872 | 3978K| | 284 (34)| 00:00:01 |
| 21 | TABLE ACCESS FULL | DEALER_CODES | 2714 | 27140 | | 3 (0)| 00:00:01 |
|* 22 | HASH JOIN | | 88656 | 3289K| | 275 (32)| 00:00:01 |
|* 23 | TABLE ACCESS FULL | BATCHES | 30506 | 625K| | 41 (32)| 00:00:01 |
|* 24 | TABLE ACCESS FULL | JOURNALS | 139K| 2312K| | 222 (29)| 00:00:01 |
|* 25 | INDEX FAST FULL SCAN| JOURNAL_DETAILS_IX06 | 18M| 357M| | 7047 (26)| 00:00:08 |
|* 26 | TABLE ACCESS FULL | JOURNALS | 139K| 2312K| | 222 (29)| 00:00:01 |
|* 27 | INDEX FAST FULL SCAN | JOURNAL_DETAILS_IX06 | 18M| 357M| | 7047 (26)| 00:00:08 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JD"."BKGE_CLASS"="JD2"."BKGE_CLASS" AND "JD2"."JOURNAL_ID"="J2".JOURNAL_ID")
filter("JD"."PRODUCER_CLIENT_ID"<>"JD2"."PRODUCER_CLIENT_ID")
3 - access("J2"."BATCH_ID"="B2"."BATCH_ID")
4 - filter("B2"."COMMITTED_AT"<'1-july-2013' AND TRUNC(INTERNAL_FUNCTION("COMMITTED_AT"))<=TRUNC('1-july-2013'))
5 - access("D2"."DEALER_CODE_ID"="J2"."DEALER_CODE_ID")
6 - access("JD"."PRODUCER_CLIENT_ID"="FO"."PRODUCER_CLIENT_ID" AND "D"."PRODUCER_ID"="FO"."PRODUCER_ID"
AND "JD"."BKGE_CLASS"="FO"."KGE_CLASS")
7 - filter("FO"."BKGE_CLASS"<>'EMM' AND "FO"."BKGE_CLASS"<>'MTD' AND "FO"."BGE_CLASS"<>'MFRI' AND "FO"."BKGE_CLASS"<>'MFRO')
8 - access("D"."PRODUCER_ID"="ITEM_1" AND "JD"."PRODUCER_CLIENT_ID"="ITEM_2" AND "JD"."BKGE_CLASS"="ITEM_3")
9 - access("JD"."JOURNAL_ID"="J"."JOURNAL_ID")
10 - access("J"."BATCH_ID"="B"."BATCH_ID")
11 - filter("B"."COMMITTED_AT">='1-july-2013' AND TRUNC(INTERNAL_FUNCTION("COMMITTED_AT"))>=TRUNC('1-july-2013'))
12 - access("D"."DEALER_CODE_ID"="J"."DEALER_CODE_ID")
13 - access("D"."PRODUCER_ID"="D2"."PRODUCER_ID")
17 - filter("JD"."BKGE_CLASS"<>'MTD' AND "JD"."BKGE_CLASS"<>'EMM' AND "JD"."BGE_CLASS"<>'MFRO' AND "JD"."BKGE_CLASS"<>'MFRI')
19 - access("JD2"."JOURNAL_ID"="J2"."JOURNAL_ID")
20 - access("D2"."DEALER_CODE_ID"="J2"."DEALER_CODE_ID")
22 - access("J2"."BATCH_ID"="B2"."BATCH_ID")
23 - filter("B2"."COMMITTED_AT"<'1-july-2013' AND TRUNC(INTERNAL_FUNCTION("COMMITTED_AT"))<=TRUNC('1-july-2013'))
24 - filter("J2"."JOURNAL_TYPE_ID"<>3)
25 - filter("JD2"."BKGE_CLASS"<>'EMM' AND "JD2"."BKGE_CLASS"<>'MTD' AND "JD2"."BKGE_CLASS"<>'MFRI' AND "JD2"."BKGE_CLASS"<>'MFRO')
26 - filter("J2"."JOURNAL_TYPE_ID"<>3)
27 - filter("JD2"."BKGE_CLASS"<>'EMM' AND "JD2"."BKGE_CLASS"<>'MTD' AND "JD2"."BKGE_CLASS"<>'MFRI' AND "JD2"."BKGE_CLASS"<>'MFRO')
--moderator edit: added [code] tags, please do so yourself in future
[Updated on: Thu, 01 September 2016 01:04] by Moderator Report message to a moderator
|
|
|
|
|
Re: SQL Query - Just too big [message #655497 is a reply to message #655492] |
Thu, 01 September 2016 01:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Can you check for to basic errors?
First, the data types here, b.committed_at >= '1-july-2013'. Are they dates or strings?
Second, is the use of DISTINCT necessary? THe optimizer does not think it removes any rows.
Third, you are joining DEALER_CODES to itself, on "D"."PRODUCER_ID"="D2"."PRODUCER_ID" what is that doing for you?
I often find that considering oddities like this (actually understanding your query) may help a lot.
|
|
|
|
Re: SQL Query - Just too big [message #655500 is a reply to message #655499] |
Thu, 01 September 2016 01:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Another basic error:CREATE INDEX "COMBAT_TST"."FOFA_OVERRIDE_IDX01" ON "FOFA_OVERRIDE" ("PRODUCER_CLIENT_ID")
CREATE INDEX "COMBAT_TST"."FOFA_OVERRIDE_IDX02" ON "FOFA_OVERRIDE" ("PRODUCER_CLIENT_ID", "BKGE_CLASS", "PRODUCER_ID") The first index here serves no purpose. This sort of thing may not cause a problem, but it is indicative of developers not understanding how the database works. I would review the query logic and the data structures before atttempting any tuning.
And, of course, you are persisting with the most bsic error of all: not using [code] tags.
|
|
|
|
Re: SQL Query - Just too big [message #655504 is a reply to message #655501] |
Thu, 01 September 2016 01:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You are of course free to ignore everything I said, and you do not have to say "thank you for trying to assist". However, your refusal do anything about your incorrect use of data types makes you a developer whom I would fire immediately. You may wish to review your position with your manager.
|
|
|
|
|
|
Re: SQL Query - Just too big [message #655508 is a reply to message #655507] |
Thu, 01 September 2016 02:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The implicit type conversion relies on luck: the NLS environment. It can suppress use of either of the two indexes on that table. SQL is a strongly typed language, and developers should respect that. I won't say "good luck with your career" because luck can, as in this case, cover up stupidity.
|
|
|
|
|
Re: SQL Query - Just too big [message #655525 is a reply to message #655511] |
Thu, 01 September 2016 03:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
TABLE NAME ROWCOUNT
batches 37525
dealer_codes 2892
fofa_override 32029
JOURNAL_DETAILS 18842453
journals 151949
Given these row counts, and assuming your query does not have any mistakes in coding (its joins are correct) this query should take a maximum of about 10 seconds (at least that is what I would expect on the systems I work with, yours seems a little slow (line#17 gives this indication)). The fact that you have duplicates in your results suggests the possibility however that your joins are not correct. You need to review your data model and your understanding of what this query is supposed to do to make sure its coded correctly.
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TepSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
|* 17 | INDEX FAST FULL SCAN | JOURNAL_DETAILS_IX06 | 16M| 323M| | 9271 (44)| 00:00:10 |
Also if the cardinalities in the query plan posted are accurate, then indexes are not useful for performance here and the HASH JOINS and FULL TABLE SCANS are correct. But you need to validate these cardinalities to know for sure.
Too bad you did not tell us how long "long" is. Would have been nice to know how long it takes to run in seconds as this could help us with giving you an approach to solve the problem.
For example:
Method #1
If "too long" is not "too long to wait", you could use the GATHER_PLAN_STATISTICS hint to get actual cardinalities of plan steps and from their get a good idea of where you problem lies.
Method #2
You can also always use QUERY DECOMPOSITION AND RECONSTRUCTION to find out where your time is going. This might make sense here since it looks like there may be an error in your code causing duplicates and this would be a way to find that (assuming you understand the query and related data model well enough to know it when you see it). I will get you started.
set timing on
create table temp1
as
select /*+ gather_plan_statistics */
b.batch_id
, b.committed_at
from batches b
where b.committed_at >= to_date('01-jul-2013','dd-mon-rrrr')
/
@showplangps11g
create table temp2
as
select /*+ gather_plan_statistics */
b.batch_id
, b.committed_at
, b.rowid
, j.journal_id
, j.dealer_code_id
-- , j.batch_id
from batches b
join journals j on (j.batch_id = b.batch_id)
where b.committed_at >= to_date('01-jul-2013','dd-mon-rrrr')
/
@showplangps11g
create table temp3
as
select /*+ gather_plan_statistics */
b.batch_id
, b.committed_at
, b.rowid b_rowid
, j.journal_id
, j.dealer_code_id
-- , j.batch_id
, j.rowid j_rowid
, jd.producer_client_id
, jd.bkge_class
-- , jd.journal_id
, jd.rowid jd_rowid
from batches b
join journals j on j.batch_id = b.batch_id
join journal_details jd on jd.journal_id = j.journal_id
where b.committed_at >= to_date('01-jul-2013','dd-mon-rrrr')
and jd.bkge_class NOT IN ( 'EMM', 'MTD', 'MFRI', 'MFRO' )
/
@showplangps11g
You see what I am doing here right? You continue on, adding one piece at a time, looking at the results, query plan details like E-ROWS vs. A-ROWS, and run times; to see where it falls apart.
I have attached my scripts library which contains the script noted in this example, along with the other free materials from my book performance tuning book in case you are interested.
Let us know what you find out. Kevin
|
|
|