OraFAQ Forum: Performance Tuning » sql query running for too long .....

Home » RDBMS Server » Performance Tuning » sql query running for too long ..... (oracle 10g)

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

sql query running for too long ..... [message #291554]

Fri, 04 January 2008 16:31

yogeshse
Messages: 11
Registered: December 2005
Location: Chennai

Junior Member

hi,

This query was not taking much time on oracle 9i. recently we moved to 10g. it ran for whole day and didn't complete. what could be reason ...

select * from customer_loctn c1
where src_id = 'AA'
and inact_dt > sysdate
and crx_code_type = 'TTR'
and crx_code_id ^= 'r'
and not exists
(select 'X' from customer_loctn c2
where c2.cust_id = c1.cust_id
and c2.src_id ^= 'AA'
and c2.inactv_dt > sysdate
and c2.loctn_id ^= c1.loctn_id
);

loctn_id is primary key and i also have index -
CUST_LOCTN_IX02 ON CUSTOMER_LOCTN("ZIP_CD", "STATE_CD", "INACT_DT", "SRC_ID", "CUST_ID")

Report message to a moderator

Re: sql query running for too long ..... [message #291556 is a reply to message #291554]

Fri, 04 January 2008 16:58

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

Senior Member

Could be any of one thousand and one reasons.

Maybe somebody lost an index in the move.
Maybe you forgot to collect statistics.
Maybe your databases are using different parameter settings.

You will need to post some additional information. Did you do any performance analysis on your own before you posted here? If so, where are the plans and timings and rowcounts for us to look at. If not, then, not to be rude but don't call us, we'll call you.

Check to make sure your tables and indexes match.
Try collecting statistics correctly again.
Look at the differences between parameter settings of each.
Look at the differences in query plans between the two.

If you do these things you will most likely find the answer without our help.

By the way, when you collect stats and it hoses your system, don't whine to me about it just because I suggested it, you should really be talking with your DBA about this and letting them do it.

Get back to us when you have the goods, Kevin

Report message to a moderator

Re: sql query running for too long ..... [message #291557 is a reply to message #291554]

Fri, 04 January 2008 17:06

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

Senior Member

sorry, forgot two other things.

Is that really the order of columns in your index. They look backwords to me for this query. I think maybe these are the two indexes you really need.

(src_id,crx_code_type,inact_dt,crx_code_id)
(cust_id,inactv_dt,src_id,loctn_id)

Also, is this really the query you are running or did you retype something cause looks like there might be spelling errors in your columns names. Do you really have these two date columns?

and inact_dt > sysdate
and c2.inactv_dt > sysdate

Maybe you do and it is OK, but seems somewhat co-incidental to have two date names different only by a missing last character V. At the very least that is a really crappy job done by your database modelers and/or DBAs to not enforce better naming standards for two different date columns in the same table. Of course there are possible reasons for this too as in maybe you inherited these names from a legacy systems in which case keeping the names like this is the right thing to do so I am not trying to beat anyone up here, just asking the question for clarification.

Kevin

Report message to a moderator

Previous Topic:	11g SQL PERFORMANCE ANALYZER
Next Topic:	slow query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 05:48:09 CDT 2024