Home » RDBMS Server » Performance Tuning » Pin Sql query in shared pool
Pin Sql query in shared pool [message #462635] Fri, 25 June 2010 06:53 Go to next message
paradoxkhan
Messages: 92
Registered: April 2008
Location: Pakistan
Member
Hi,

We are facing performance issues on our production instance 10g(10.2.0.4) 32-bit OELinux 5.3 2GB SGA.
The performance is mainly related to one of the table which is sized about 32Gb. We have rebuild the indexes as well but problem
still persist. We are considering to pin SQL statement in shared pool which is hitting the same table frequently. But as far what we have find, is that we can only pin procedures or function in shared pool. True/false?
If we can, then how to pin SQL statement in shared pool?
If we can not, then is there any other way?

Thanks,

Yasir Aftab

Report message to a moderator

Re: Pin Sql query in shared pool [message #462636 is a reply to message #462635] Fri, 25 June 2010 06:56 Go to previous messageGo to next message
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the query is being executed regularly then it probably never leaves the shared pool, and if it does your Shared pool is probably too small.
Are you sure that's the root of the problem and not something else?

Report message to a moderator

Re: Pin Sql query in shared pool [message #462641 is a reply to message #462636] Fri, 25 June 2010 07:34 Go to previous messageGo to next message
paradoxkhan
Messages: 92
Registered: April 2008
Location: Pakistan
Member

Thanks for the reply. Yes because AWR/ADDM ponited the same table and large physical reads. Any way to increase response time of
queries executed against this table?

Report message to a moderator

Re: Pin Sql query in shared pool [message #462647 is a reply to message #462641] Fri, 25 June 2010 08:02 Go to previous message
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
Senior Member
Quite possibly - indexes maybe, possibly partationing or maybe just rewriting the queries so that they do less work.
But to be able to tell which we would need some data.
So how about giving us the details listed in the last post in this thread: http://www.orafaq.com/forum/t/84315/129190/

Report message to a moderator

Previous Topic: need help tuning this query
Next Topic: Help proving a database is slow
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun May 12 20:19:07 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.