OraFAQ Forum: Performance Tuning » SQL Query - Just too big

Home » RDBMS Server » Performance Tuning » SQL Query - Just too big (11g )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Query - Just too big [message #655490]

Thu, 01 September 2016 00:04

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?

Report message to a moderator

Re: SQL Query - Just too big [message #655491 is a reply to message #655490]

Thu, 01 September 2016 00:17

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

Re: SQL Query - Just too big [message #655492 is a reply to message #655491]

Thu, 01 September 2016 00:54

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:

Code: [Select all] [Show/ hide]

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

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)

Report message to a moderator

Re: SQL Query - Just too big [message #655496 is a reply to message #655492]

Thu, 01 September 2016 01:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Code: [Select all] [Show/ hide]

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

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

John Watson
Messages: 8968
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.

Report message to a moderator

Re: SQL Query - Just too big [message #655499 is a reply to message #655496]

Thu, 01 September 2016 01:27

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")

Report message to a moderator

Re: SQL Query - Just too big [message #655500 is a reply to message #655499]

Thu, 01 September 2016 01:32

John Watson
Messages: 8968
Registered: January 2010
Location: Global Village

Senior Member

Another basic error:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: SQL Query - Just too big [message #655501 is a reply to message #655497]

Thu, 01 September 2016 01:38

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 -

Report message to a moderator

Re: SQL Query - Just too big [message #655504 is a reply to message #655501]

Thu, 01 September 2016 01:47

John Watson
Messages: 8968
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.

Report message to a moderator

Re: SQL Query - Just too big [message #655505 is a reply to message #655504]

Thu, 01 September 2016 02:02

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.

Report message to a moderator

Re: SQL Query - Just too big [message #655506 is a reply to message #655505]

Thu, 01 September 2016 02:13

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.

Report message to a moderator

Re: SQL Query - Just too big [message #655507 is a reply to message #655506]

Thu, 01 September 2016 02:15

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..

Report message to a moderator

Re: SQL Query - Just too big [message #655508 is a reply to message #655507]

Thu, 01 September 2016 02:20

John Watson
Messages: 8968
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.

Report message to a moderator

Re: SQL Query - Just too big [message #655511 is a reply to message #655508]

Thu, 01 September 2016 02:30

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 ...

Report message to a moderator

Re: SQL Query - Just too big [message #655522 is a reply to message #655506]

Thu, 01 September 2016 03:30

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

nathan59 wrote on Thu, 01 September 2016 09:13

You can see here - It does an implicit convert.

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: SQL Query - Just too big [message #655525 is a reply to message #655511]

Thu, 01 September 2016 03:50

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Code: [Select all] [Show/ hide]

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.

Code: [Select all] [Show/ hide]

------------------------------------------------------------------------------------------------------------
| 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.

Code: [Select all] [Show/ hide]

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

Attachment: book scripts.zip
(Size: 41.49KB, Downloaded 2143 times)
Attachment: Oracle SQL Performance Tuning and Optimization v26 chapter 1.pdf
(Size: 783.27KB, Downloaded 2661 times)
Attachment: SQL Tuning Worksheet 3.docx
(Size: 122.03KB, Downloaded 2296 times)

Report message to a moderator

Previous Topic:	Driving site hint on a single remote table
Next Topic:	How to Optimise this query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 12 18:09:02 CDT 2025