Home » RDBMS Server » Performance Tuning » Poor Performance query
Poor Performance query [message #223257] Thu, 08 March 2007 03:27 Go to next message
chara
Messages: 81
Registered: April 2005
Location: th
Member
Dear all,

I have many "inner join" in my query and then it took poor performance for cpu.
Can i use many sub-query for better than many inner join or any method?

Pls advice !

Thanks for advance !
Chara
Re: Poor Performance query [message #223421 is a reply to message #223257] Thu, 08 March 2007 17:18 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
I have similar cpu slow downs during an inner join. I have found that if you wrap sql inside of a procedural type language (smalltalk program, java, pl/sql) it improves performance greatly.

I am not sure buy I think an inner join causes a m by n query (for every row, search every other row) It just kills performance.
Other problem sql statements include but are not limited to:
decode
merge
updates with subselects
subselects
joins
order by
highest
lowest
and more. I *think* it ignores the indexes. Some of these are necessary, there is just no way to get around it.
Do you have ADDM and AWL?

[Updated on: Thu, 08 March 2007 17:19]

Report message to a moderator

Re: Poor Performance query [message #223430 is a reply to message #223421] Thu, 08 March 2007 20:15 Go to previous messageGo to next message
chara
Messages: 81
Registered: April 2005
Location: th
Member
Have ADDM include oracle9i?
Re: Poor Performance query [message #223437 is a reply to message #223430] Thu, 08 March 2007 22:25 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
@nmacdannald, sorry, I disagree with everything you said.

@chara, why don't you try tuning it? There is an excellent Performance Tuning manual distributed with your database disks that is also available online.

As an alternate approach you can use my tuning homepage.

Or if you are serious about getting help on this forum, you could even post the query here along with the Explain Plan, table volumes, available indexes, expected number of rows returned, and selectivity of WHERE conditions in the SQL.

Ross Leishman
Previous Topic: Execution Plan in Trace File
Next Topic: How to tuning our oracle product server?
Goto Forum:
  


Current Time: Wed Dec 07 20:49:47 CST 2016

Total time taken to generate the page: 0.15616 seconds