Home » RDBMS Server » Performance Tuning » SQL Query - Just too big (11g )
SQL Query - Just too big [message #655490] Thu, 01 September 2016 00:04 Go to next message
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 #655491 is a reply to message #655490] Thu, 01 September 2016 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

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

What does "too long" mean?

Re: SQL Query - Just too big [message #655492 is a reply to message #655491] Thu, 01 September 2016 00:54 Go to previous messageGo to next message
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 #655495 is a reply to message #655490] Thu, 01 September 2016 01:15 Go to previous messageGo to next message
nathan59
Messages: 9
Registered: August 2016
Junior Member
Formatted qry:

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)
Re: SQL Query - Just too big [message #655496 is a reply to message #655492] Thu, 01 September 2016 01:15 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
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) 

post DDL for all tables & indexes
Re: SQL Query - Just too big [message #655497 is a reply to message #655492] Thu, 01 September 2016 01:19 Go to previous messageGo to next message
John Watson
Messages: 7623
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 #655499 is a reply to message #655496] Thu, 01 September 2016 01:27 Go to previous messageGo to next message
nathan59
Messages: 9
Registered: August 2016
Junior Member
All Indexes:

CREATE INDEX "JOURNAL_DETAILS_IX01" ON "COMBAT_TST"."JOURNAL_DETAILS" ("JOURNAL_ID")

CREATE INDEX "JOURNAL_DETAILS_IX02" ON "COMBAT_TST"."JOURNAL_DETAILS" ("ADVISER_ACCOUNT_NO")

CREATE INDEX "JOURNAL_DETAILS_IX03" ON "COMBAT_TST"."JOURNAL_DETAILS" ("ADVISER_CODE")

CREATE INDEX "JOURNAL_DETAILS_IX04" ON "COMBAT_TST"."JOURNAL_DETAILS" ("PRODUCER_CLIENT_ID", "BKGE_CLASS")

CREATE INDEX "JOURNAL_DETAILS_IX05" ON "COMBAT_TST"."JOURNAL_DETAILS" ("BKGE_CLASS")

-------------------------------

CREATE INDEX "JOURNALS_IX01" ON "JOURNALS" ("BATCH_ID")

CREATE INDEX "JOURNALS_IX02" ON "JOURNALS" ("JOURNAL_TYPE_ID")

CREATE INDEX "JOURNALS_IX03" ON "JOURNALS" ("DEALER_CODE_ID")

CREATE INDEX "JOURNALS_IX04" ON "JOURNALS" ("PERIOD_ID")

--------------------------------------------------------------

CREATE INDEX "DEALER_CODES_IX01" ON "DEALER_CODES" ("PRODUCER_ID")

CREATE UNIQUE INDEX "DEALER_CODES_UQ01" ON "DEALER_CODES" ("CODE_NAME", "PRODUCER_ID")

---------------------------------------------------------------------------------------------------------------

CREATE INDEX "COMBAT_TST"."BATCHES_IX01" ON "BATCHES" ("COMMITTED_AT")

CREATE INDEX "COMBAT_TST"."BATCHES_IX02" ON "BATCHES" (TRUNC("COMMITTED_AT"))

CREATE INDEX "COMBAT_TST"."BATCHES_IX03" ON "BATCHES" ("CONFLICT_BATCH")

------------------------------------------------------------------------------------

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")
Re: SQL Query - Just too big [message #655500 is a reply to message #655499] Thu, 01 September 2016 01:32 Go to previous messageGo to next message
John Watson
Messages: 7623
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 #655501 is a reply to message #655497] Thu, 01 September 2016 01:38 Go to previous messageGo to next message
nathan59
Messages: 9
Registered: August 2016
Junior Member
Hi John:

1) committed_at >= '1-july-2013' - It works fine and I don't have to to any convert.

2) Distinct - It did return some duplicates and that is the reason it is added (yes - it adds sorting overhead - but is a necessary evil)

3) Table join - If you look at the subquery, it does join few other tables and filters rows - (d.xxx to d2.xxx)


4) First Index - agreed - It is vendor product and we can't touch it -
Re: SQL Query - Just too big [message #655504 is a reply to message #655501] Thu, 01 September 2016 01:47 Go to previous messageGo to next message
John Watson
Messages: 7623
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 #655505 is a reply to message #655504] Thu, 01 September 2016 02:02 Go to previous messageGo to next message
nathan59
Messages: 9
Registered: August 2016
Junior Member
I will forward it to my manager, if that is what you want.

Thanks a lot.

ANyhow, I changed it to set based and got it to run in few mins.

All I wanted was to find out was whether I can convert this sql to analytic based one (instead of joining the same table again and again).

I am just getting into analytic sqls.
Re: SQL Query - Just too big [message #655506 is a reply to message #655505] Thu, 01 September 2016 02:13 Go to previous messageGo to next message
nathan59
Messages: 9
Registered: August 2016
Junior Member
Also,

SQL> select * from combat_tst.batches
2 where committed_at > '01-Jul-2013';

Execution Plan
----------------------------------------------------------
Plan hash value: 1921840176

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6094 | 422K| 36 (23)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| BATCHES | 6094 | 422K| 36 (23)| 00:00:01 |
-----------------------------------------------------------------------------

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

1 - filter("COMMITTED_AT">TO_DATE(' 2013-07-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("COMMITTED_A

T"))>=

TRUNC(TO_DATE(' 2013-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

You can see here - It does an implicit convert.

Sorry for not saying Thanks in the first place. I was in the middle of getting the work done.

Re: SQL Query - Just too big [message #655507 is a reply to message #655506] Thu, 01 September 2016 02:15 Go to previous messageGo to next message
nathan59
Messages: 9
Registered: August 2016
Junior Member
ALso, John - I forwarded your message to my manager - Let me see whether I will have the job tomorrow..
Re: SQL Query - Just too big [message #655508 is a reply to message #655507] Thu, 01 September 2016 02:20 Go to previous messageGo to next message
John Watson
Messages: 7623
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 #655511 is a reply to message #655508] Thu, 01 September 2016 02:30 Go to previous messageGo to next message
nathan59
Messages: 9
Registered: August 2016
Junior Member
Really!! Anyhow, I don't have time to take it any further as the language used is ...
Re: SQL Query - Just too big [message #655522 is a reply to message #655506] Thu, 01 September 2016 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
nathan59 wrote on Thu, 01 September 2016 09:13
You can see here - It does an implicit convert.
SQL> select * from dual where sysdate >  '01-Jul-2013';
select * from dual where sysdate >  '01-Jul-2013'
                                    *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

As you can see it does not work.

Re: SQL Query - Just too big [message #655525 is a reply to message #655511] Thu, 01 September 2016 03:50 Go to previous message
Kevin Meade
Messages: 2102
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
Previous Topic: Driving site hint on a single remote table
Next Topic: How to Optimise this query
Goto Forum:
  


Current Time: Tue Oct 23 02:06:43 CDT 2018