optimise query with date ranges to search on 2

[CanNeverThinkOfaName]

Hi all

This query takes WAY too long to run (over an hour) - can anyone think of any other way to write it please?!!

SELECT to_Date((to_char(A.ADDATE,'dd/mm/yyyy')||' '||A.ADTIME),'dd/mm/yyyy hh24:mi:ss'), B.ACCOUNTID,
A.CAMPAIGN, ( C.TITLE ), A.WEEK, C.TITLE,
D.TITLE
FROM A, B, C, D, E
WHERE ( E.POID_ID0=D.OBJ_ID0 )
AND ( E.ACCOUNT_NO=B.ACCOUNTID(+) )
AND ( C.EXTERNALNO(+)=B.CHANNELID )
AND ( B.USAGETIMESTAMP between (to_Date((to_char(A.ADDATE,'dd/mm/yyyy')||' '||A.ADTIME),'dd/mm/yyyy hh24:mi:ss') - ((1/24/60)*10)) and
(to_Date((to_char(A.ADDATE,'dd/mm/yyyy')||' '||A.ADTIME),'dd/mm/yyyy hh24:mi:ss') + ((1/24/60)*10)) )
AND ( ( C.TITLE ) = '4' )
and trunc(to_Date((to_char(A.ADDATE,'dd/mm/yyyy')||' '||A.ADTIME),'dd/mm/yyyy hh24:mi:ss')) >= '6-oct-08' and
trunc(to_Date((to_char(A.ADDATE,'dd/mm/yyyy')||' '||A.ADTIME),'dd/mm/yyyy hh24:mi:ss')) <= '8-oct-08'

addate is something like '06/10/2008' and addtime is 14:57:00' but they are stored in seperate columns in the database.

If I say just one day on the last line- instead of >= X and <= Y -it only takes 10 mins(still terrible but better than nothing!)

If you would like some sample data or more background let me know!
thanks for any help in advance!

 

[SantaMufasa]

CanNever...,

Please let me make an analogy...Given the facts that you have presented so far, it is rather similar to going to a physician and saying only, "Doc, can you make me feel better?"

To investigate performance issues for the above, you should post the following information:[ul][li]an EXECUTION PLAN generated by Oracle of your query[/li][li]the list of indexes and their columns (in indexed-column order), and whether any of the indexed columns are function-based indexes[/li][li]the count of rows in each of your join tables[/li][li]the LAST_ANALYZED date for each of your join tables[/li][/ul]Please post the above, then I'm certain that we can start making worthwhile suggestions for performance improvement.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CanNeverThinkOfaName]

Sorry Mufasa- I was a big vague!!!
Thanks for helping me out!

Execution Plan:

Plan
SELECT STATEMENT ALL_ROWSCost : 20,080 Bytes : 59,400 Cardinality : 495
11 FILTER
10 HASH JOIN Cost : 20,080 Bytes : 59,400 Cardinality : 495
8 HASH JOIN Cost : 19,513 Bytes : 55,278 Cardinality : 498
6 HASH JOIN Cost : 18,411 Bytes : 46,314 Cardinality : 498
2 TABLE ACCESS BY INDEX ROWID C Cost : 3 Bytes : 54 Cardinality : 2
1 INDEX RANGE SCAN IDX$$_146BB0001 Cost : 1 Cardinality : 2
5 NESTED LOOPS Cost : 18,407 Bytes : 1,186,086 Cardinality : 17,971
3 TABLE ACCESS FULL A Cost : 3 Bytes : 36 Cardinality : 1
4 TABLE ACCESS FULL B Cost : 18,404 Bytes : 787,050 Cardinality : 26,235
7 MAT_VIEW ACCESS FULL E Cost : 1,100 Bytes : 2,475,126 Cardinality : 137,507
9 MAT_VIEW ACCESS FULL D Cost : 565 Bytes : 1,218,339 Cardinality : 135,371


Indexes
IDX$$_146BB0001 on C.title

Row Count
A - 274
B - 11919533
C - 299
D - 137600
E - 137600

Last Analyzed Dates

a - 15/10/2008 22:00:23
B - 20/10/2008 22:07:27
C - 22/10/2008 22:00:36
D - 22/10/2008 22:02:58
E - 20/10/2008 22:07:36

 

[SantaMufasa]

CanNever,

At first blush, if your only index amongst all the tables is on the TITLEs of 299 rows (and TITLE is not even mentioned in your WHERE clause), then Oracle's only alternative is to do costly full-table scans on all of the tables...that is a ton of work, especially when joining against a 12-million-row table !

The fact that you are storing your DATEs and TIMEs in two separate columns, and having continually to do MIXED-MODE expression transformations (DATE-to-CHARACTER-to-DATE) is extremely costly, especially without INDEXes. (BTW, since Oracle always stores a TIME in all DATE expressions, why does your data architecture fracture your DATE/TIME components into separate columns?)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LKBrwnDBA]

Adding to Dave's statement about the cost of MIXED-MODE expression transformations (DATE-to-CHARACTER-to-DATE), you do not need to use the time here:

-- etc -- 
And ( ( C.TITLE ) = '4' )
And A.ADDATE Between TO_DATE('06-oct-08','DD-mon-yy')
                 And TO_DATE('08-oct-08','DD-mon-yy')
-- etc --

Also, post an example of the values for these date/time columns executing this select:

SELECT to_char(A.ADDATE,'dd/mm/yyyy hh24:mi:ss') addate
, to_char(A.ADTIME,'dd/mm/yyyy hh24:mi:ss') adtime
FROM A SAMPLE(10);




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[CanNeverThinkOfaName]

My brain wasn't all there yesterday- there is a second (function based) index on the massive table B

TO_DATE(TO_CHAR("ADDATE",'dd/mm/yyyy')||' '||"ADTIME",'dd/mm/yyyy hh24:mi:ss')-.006944444444444444444444444444444444444444,TO_DATE(TO_CHAR("ADDATE",'dd/mm/yyyy')||' '||"ADTIME",'dd/mm/yyyy hh24:mi:ss')+.006944444444444444444444444444444444444444

but the execution plan doesnt seem to use that??


The reason the date and time are in 2 columns is due to the importing process but I could change that.

Do you suggest then putting indexes on the B, D and E tables relating to the columns they join on??

By the way - it took ELEVEN hours to run the between 2 dates query!!!

 

[SantaMufasa]

Do you suggest then putting indexes on the B, D and E tables relating to the columns they join on??

Absolutely, positively ! In my experience, 95+% of the time, performance issues/inappropriately long-running queries result from either, or a combination, of these issues:[ul][li]Missing/Incorrect indexes.[/li][li]Missing/Stale statistics on the tables.[/li][/ul]Ensure resolution of those two issues, and Oracle usually takes care of the rest.



Consolidating your DATE/TIME components into a single column would also be very helpful.

Please let us know how your troubleshooting progresses.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CanNeverThinkOfaName]

Well THANK YOU!

I added an index to b.usagetimestamp , b.channelid, b.externalno and also just seached addate alone for the date parts (as LKBrwnDBA suggested) - and it takes 30 seconds!!

I only wish now I had done it incrementally and worked out which made it work best- I'm guessing the index on b.usagetimestamp!

Whats the best way to learn about how to tune Oracle databases and understand the architecture- any good books or courses you could recommend? I've got a whole database that's crying out for tuning!

 

[SantaMufasa]

Whats the best way to learn about how to tune Oracle databases and understand the architecture- any good books or courses you could recommend?

Here are 48 titles available from Amazon.com that deal specifically with Tuning Oracle Databases and Applications. You should find from that list several specific to your Oracle version, general application, and affordability. (I'm also a contributor on Dan Tow's "SQL Tuning", but that publication is not limited to Oracle Tuning.)


As far as classes go, I used to teach Tuning Oracle Databases and Applications while an Oracle University instructor. I liked the material, so I can recommend your taking instructor-led Tuning classes from Oracle. I'm sure there are others (possibly more affordable) classes available from third-party education providers near you, but I simply don't know which one(s) to recommend.

I'm delighted that our suggestions brought your query performance down to an appropriate level!

Congratulations!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[BJCooperIT]

CanNeverThinkOfaName,

Your helpers in this thread deserve a star for their efforts, don't you think?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com