shared_pool_reserved_size Parameter

[Former Member]

Hi,

I'm upgrading all the oracle parameters following the recomendations figured in SAP Note [830576|https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=830576] and using as guide the result of the query that is listed in Sap Note [1109753|https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=1171650]. Database Version is 10.2.0.2 and it's a PI Dev Instance. I have this parameter shared_pool_reserved_size and in the EWA appears that it's wrong, Actually I know that it should be the 5% of shared_pool_size but anyone knows the correct value of this parameter??

The value of shared_pool_size parameter is 400M.

And the actual value of shared_pool_reserved_size is 32196526.

Regards.

Isrrael Flores

[Former Member]

Hi,

Try this command.

alter system reset shared_pool_reserved_size scope=spfile sid=u2019*u2019;

Regards

Bhupesh

[Former Member]

Hi Bhupesh,

yup i've tried that but then the error i'm getting is :

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

But no worries, as Stefan has pointed that out in the post above.

Thanks

[Former Member]

Thanks Rohit,

I changed the value accord the blog.

Regards,

Isrrael Flores

[fidel_vales]

Hi,

sorry but,

IMHO the recommendations on that "blog" are quite

• obsolete

• old

...

if you want to set the oracle parameters in 10g you must follow the SAP Note 830576 Parameter recommendations for Oracle 10g

if you take a look at the SAP recommendations on that note, there is no mention of "shared_pool_reserved_size"

For that reason your EWA gives you wrong valie, and not what "Rohit" told you.

Therefore, if you want to follow SAP recommendations and get rid of the "error" in the EWA, do not set the mentioned parameter

[stefan_koehler]

Hello,

in my opinion the EWA is the most useless tool in a SAP landscape. It is mostly out of date or wrong and lead customers to such operations.

Just my 2 cents.

Regards

Stefan

[Former Member]

Hi,

Actually, I was thinking to delete this parameter but I have 5 more instances in my landscape and all of them has this parameter set but in the EWA only appear the error in the PI instance, so this is the reason of my doubt.

I know that is the blog it's old but I try to assign the 10% of the shared_pool_size value.

Anyway thanks for de info, Fidel

Regards

[Former Member]

Hi guys,

I was wondering how do i UNSET the value for shared_pool_reserved_size in oracle?

It's currently now showing a value of 156028108 .

According to the recommendation, we are to set it to "unset" ...

Hope you guys could help me out on this.

Thanks

[stefan_koehler]

Hello,

>I was wondering how do i UNSET the value for shared_pool_reserved_size in oracle?

> It's currently now showing a value of 156028108 .

If you unset/reset a parameter, you always have a default value.

In the case of shared_pool_reserved_size - it is 5% of the value of SHARED_POOL_SIZE (see documentation):

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams196.htm#i1133799

If your parameters are the default value or set trough a parameter file can be verified with the view V$PARAMETER

shell> sqlplus / as sysdba
shell> SELECT NAME, VALUE, ISDEFAULT FROM V$PARAMETER;

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2012.htm#I1030176

Regards

Stefan

[Former Member]

Thanks stefan for your reply...

Well, i did this query and this is what i've got :

SQL> SELECT NAME, VALUE, ISDEFAULT FROM V$PARAMETER where NAME like '%shared_pool_reserved%';

NAME

-

---

VALUE

-

---

ISDEFAULT

-

---

shared_pool_reserved_size

156028108

TRUE

According to the EWR report, i'm supposed to set the value to "unset" or somehow, just unset the whole parameter. I cant find this parameter in the pfile even after creating pfile from spfile.

So i'm actually at wit's end on how to unset the whole parameter value for this. I'm not sure if setting the value to "0" signifies resetting the value ?

[stefan_koehler]

Hello,

the parameter is already reset/unset ... you can see this in the column ISDEFAULT. That is also the reason why you can't find the parameter in the pfile/spfile.

You can't do anymore .. it is already unset (=reset to its default value).

Don't know which recommendation of the EWR you mean .. but you can read my opinion about the EWR/EWA above.

Just crosscheck the sapnote #830576 for Oracle 10g or sapnote #124361 for Oracle 9i, set the parameter like it is described there and forget the EWA/EWR.

Regards

Stefan

[Former Member]

Thanks stefan for ur reply,

I really appreciate the time taken for u to reply on this.

I think i'll just leave it the way it is, as i'm unable to reset it.

Thanks again..

[Former Member]

Hi

5% of shared pool size is the minimum value of shared_pool_reserved_size,thats why it is giving wrong value for it in EWA.

Oracle recommends setting this parameter to 10% of shared pool size.

Please refer to the following blog:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/0667b7c9-0e01-0010-e4a3-873e8765...

Also go through the following blog to know the formula for calculating this

http://sai-oracle.blogspot.com/2006/04/how-much-sharedpoolreservedsize.html

Rohit