| Poor Performance query [message #223257] |
Thu, 08 March 2007 03:27  |
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  |
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 #223437 is a reply to message #223430] |
Thu, 08 March 2007 22:25 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
