OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » shared pool (oracle 10.2.0.4 2 node rac,aix 6.3)

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

shared pool [message #460796]

Mon, 14 June 2010 20:11

ranganathan1984
Messages: 11
Registered: April 2007
Location: 37,park terrace,bridgepor...

Junior Member

Hi,
we are running a 2 node rac database of oracle 10.2.0.4 in aix 6.3. The shared pool utilisation goes up when we run our jobs and sql statements. But it does not come down. It been 2 gig for over 3 days without any processes running.
Is there a threshold time period after which oracle will release
all the space utilisation from the shared pool????

Report message to a moderator

Re: shared pool [message #460797 is a reply to message #460796]

Mon, 14 June 2010 20:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there a threshold time period after which oracle will release all the space utilisation from the shared pool????

Release what to where & for what purpose?
Exactly what problem are you trying to solve?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: shared pool [message #460798 is a reply to message #460797]

Mon, 14 June 2010 20:26

ranganathan1984
Messages: 11
Registered: April 2007
Location: 37,park terrace,bridgepor...

Junior Member

Thanks for the quick response.
Database:oracle 10.2.0.4 2 node rac
Os: aix 6.1
When we run some sql statements which involve purge and inserts,
the shared pool utilisation spikes up to 4 gig.
When we do select sum(bytes) from v$sgastat where pool='shared pool' we get 4 gig.
Even after 2 days of not running any processes this value does not come down,it stays at 4 gig.
We tried bouncing the database still it won't come down.
So was wondering is there a threshold parameter that oracle has after which it will release this shared pool memory and show us a reduced value of shared pool utilisation when we run the above mentioned query?

Report message to a moderator

Re: shared pool [message #460799 is a reply to message #460798]

Mon, 14 June 2010 20:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Even after 2 days of not running any processes this value does not come down,it stays at 4 gig.

This is not a problem.
Therefore nothing needs to be done about it.

Report message to a moderator

Re: shared pool [message #460800 is a reply to message #460799]

Mon, 14 June 2010 20:50

ranganathan1984
Messages: 11
Registered: April 2007
Location: 37,park terrace,bridgepor...

Junior Member

Thanks.
The problem happens when we run more processes.
The sga_target is set to 7 gig,so when we run more processes,
the shared pool utilisation spikes up again to 5 gig and we run into ora 04031 when sga reaches its peak. The error says cannot allocate bytes of shared memory.
Its solution will be to increase the sga,but we have a limitation of only 7 gig,so cannot increase the sga.
So was wondering if i could bring down the 4 gig of shared pool to a lesser value somewhere close to 1.
Alter system flush shared pool also does not bring down the value.
i.e.
If i do an alter system flush shared pool;
Then check select sum(bytes) from v$sgastat where pool='shared pool', it stays at the same value instead of going down.

Report message to a moderator

Re: shared pool [message #460801 is a reply to message #460800]

Mon, 14 June 2010 20:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You are attacking the symptom; not the root cause.

Tell us about the application that is abusing Oracle.

post a copy of your current pfile

>the shared pool utilisation spikes up again to 5 gig and we run into ora 04031 when sga reaches its peak

I have no idea why such demands are placed upon the SGA.
One approach I used in the past to avoid ORA-04031 was to PIN all PL/SQL packages in the application into SGA via DB STARTUP trigger.
They consumed only about 1.5GB & never saw this error again.

[Updated on: Mon, 14 June 2010 22:36]

Report message to a moderator

Re: shared pool [message #460955 is a reply to message #460801]

Tue, 15 June 2010 13:23

mikerault
Messages: 21
Registered: June 2010
Location: Alpharetta, Ga

Junior Member

Are you using AMM (sga_target, sga_max_size)? You may want to disable AMM and just use base parameters for sizing your components. Also, run an AWR or Statspack and see what is happening as far as SQL versions. Are you using CURSOR_SHARING? A setting of SIMILAR can result in multiple SQL versions, even when not needed. Set it to FORCE or set the undocumented parameter _sqlexec_progression_cost to its maximum value.

Mike

Report message to a moderator

Re: shared pool [message #460961 is a reply to message #460955]

Tue, 15 June 2010 14:00

ranganathan1984
Messages: 11
Registered: April 2007
Location: 37,park terrace,bridgepor...

Junior Member

Thanks so much for the responses.
We found out what may be causing the problem of ora 04031 and the shared pool memory utilisation not coming down.
Would like to share it:
Its metalink note 556140.1.
ORA-4031 On Using Auto SGA [ID 556140.1]

The issue may be because of Shared Pool fragmentation.

Will be setting up the hidden parameter "_shared_pool_reserved_pct"=10 as described in the metalink note
and monitor the database then.

Report message to a moderator

Re: shared pool [message #460976 is a reply to message #460961]

Tue, 15 June 2010 19:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Will be setting up the hidden parameter "_shared_pool_reserved_pct"=10 as described in the metalink note and monitor the database then.
Again just fighting the symptom

Since you don't answer our questions/requests, perhaps we should stop trying to answer yours.

Report message to a moderator

Re: shared pool [message #460996 is a reply to message #460976]

Wed, 16 June 2010 00:54

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Is it possible that your application is generating a lot of sql statements without using bind variables?

Report message to a moderator

Previous Topic:	TKPROF Output Understanding
Next Topic:	Tuning PL/SQLApplications

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 07:18:57 CST 2024