Home » RDBMS Server » Performance Tuning » SQL Query behaving weirdly in 10g (10g)
SQL Query behaving weirdly in 10g [message #309771] Fri, 28 March 2008 14:06 Go to next message
hrsiddareddy
Messages: 1
Registered: March 2008
Junior Member
We just upgraded our database server from 9i to 10g and we have a extract that runs every day night. Previously a SQL query in 9i was taking around 16 mins. but in 10g it is taking more than 5 hrs.

The structure of the query is some thing like below

Select * from
( select
DECODE(q2,22,2,DECODE(we,ee,ee,ee)......) d_a
, func_call(23,23) f_a
, func_call(34,45) f_b
, name
, add
FROM (
SELECT name
,add
,sex
from TABLE_A,TABLE_B

)where f_a + f_b <=24
)where func_call_new(f_a,f_b,d_a) < 100;

func_call_new --> a function in the same package
func_call --> a function in the same package

It looks similar to this but more select DECODES and function calls are there in the query.

TABLE_A usually has 3000 records MAX
TABLE_B has 900,000 records MAX

For this what i did was removed the inner bold select statement and insert the values into a temp_table and the accessed those values form the table.

The fix actually solved the issue and now it is running in 10 mins.
But still i feel there is something i am missing in the solution.

Can anybody suggest me what might be the issue.

The plan look same in 9i and 10g.

Thanks for the time

Harish

Re: SQL Query behaving weirdly in 10g [message #309774 is a reply to message #309771] Fri, 28 March 2008 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Oracle SQL Tuning Guide

Then provide the requested and usual information.

Regards
Michel
Re: SQL Query behaving weirdly in 10g [message #310136 is a reply to message #309771] Mon, 31 March 2008 06:59 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
DEAR ,
THE PROBLEM IS WITH THE WHERE CALUSE ,
IT SEEMS THAT YOU ARE DOING TOO MANY CALCULATION AT THE LEFT OF EQUAL SIGN , WHICH IS ALWAYS VERY SLOW FOR THIS FUNCTION WILL BE APPLIED AT EACH AND EVERY ROW.
SECONDLY , MAY BE PREVIOUSLY U HAD ANY INDEX ON THE COLUMNS IN WHERE CLAUSE , BUT NOW AS U ARE APPLYING FUNCTION , SO THAT INDEX WUD BE AUTOMATICALLY DIS BALED.
PROBABLY THATS WHY your QUERY HAS BECOME REALLY SLOW.

[Updated on: Mon, 31 March 2008 07:00]

Report message to a moderator

Re: SQL Query behaving weirdly in 10g [message #310141 is a reply to message #310136] Mon, 31 March 2008 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I close my ears/eyes when someone is shouting.
Repost in correct way.

Regards
Michel
Re: SQL Query behaving weirdly in 10g [message #310276 is a reply to message #309771] Mon, 31 March 2008 16:37 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
WHAT, I CAN'T HEAR YOU, WHAT? SPEAK UP!
Previous Topic: db_cache_size
Next Topic: 'EXISTS' vs 'IN'
Goto Forum:
  


Current Time: Fri Dec 09 15:59:24 CST 2016

Total time taken to generate the page: 0.17767 seconds