facing problem with the parameter SHARED_POOL value is differ

[Former Member]

Hi Experts,

I am facing a problem *

 the parameter SHARED_POOL_RESERVED_SIZE*  value is 
Different In NODE A 102928220  and NODE B 51464110  memory.

The NODE A is Active.

We are using SAP Ecc5 with oracle 9i on window 2003.

What will be the effect of this parameter it is ok to have difference because I feel there is performance problem since I have change from Node B to nodeA active.

Please suggest What to do. If I have to change the parameter value pleases tell the steps for changing.

The details

SGA_MAX_SIZE 4129140808

SHARED_POOL_RESERVED_SIZE 102928220

SHARED_POOL_SIZE 520093696

SORT_AREA_SIZE 2097152

*ST02 Deatis*

SAP memory Current use

[%] [kB]

Roll area 9.69 25,402

Paging area 10.64 27,901

Extended Memory 36.54 1,835,008

Heap Memory 0

MAx

[kB]

Roll area 38,456

Paging area 60,224

Extended Memory 2,899,968

Heap Memory 0

In memory

[kB]

Roll area 262,144

Paging area 131,072

Extended Memory 5,021,696

On disk [kB]

0

Paging area 131,072

*ST04 Details*

Data buffer Elapsed since start (s) 19,325

Size KB 1,261,568

Quality % 89

Reads 92,315,389

Physical reads 10,043,519

writes 302,040

Buffer busy waits 239,555

Buffer wait time s 1,020

Shared Pool

Size kb 507,904

DD-Cache quality % 86.7

SQL Area getratio % 97.8

pinratio % 99.7

reloads/pins % 0.0004

Log buffer

Size kb 1,176

Entries 1,445,300

Allocation retries 48

Alloc fault rate % 0.0

Redo log wait s 8

Log files (in use) 8( 😎

*St06*

CPU

Utilization user % 69

system % 5

idle % 25

System calls/s 12,236

Interrupts/s 3,170

Count 2

Load average 1 min 2.84

5 min 1.62

15 min 1.68

Context switches/s 4,687

Swap

Commit charge limit Kb 48,492,768

Commit charge free Kb 31,126,272

Maximum swap-space Kb 41,943,040

Actual swap-space Kb 41,910,272

Memory

Physical mem avail Kb 8,376,136 Pages in/s 0

Pages out/s 1,411

Physical mem free Kb 1,301,472 Kb paged in/s 0

Kb paged out/s 11,288

Regards,

[former_member105207 · Accepted solution]

Hi Shiva,

you can change the shared pool size in $ORACLE_HOME\dbs

In file init.ora and initSID.ora

Generally the shared pool reserved size will be 10% value of shared pool size.

suppose if SHARED_POOL_SIZE = 100

then SHARED_POOL_RESERVED_SIZE will be 10.

Make necessary adjustments depending on your requirement.

Please check the following notes.

789011 - FAQ: Oracle memory areas

124361 - Oracle parameterization (R/3 >= 4.x, Oracle 8.x/9.x)

you can perform Automated check for parameter check using this note in sapgui
1171650 - Automated Oracle DB parameter check.

Further, try to maintain the same parameter values in both node A and node B.

Regards,

chandru.

[Former Member · Accepted solution]

Hi Chandru,

Thanks for you suggestion, That means the parameter value details

shared_pool_size = 514641100 (At present)

shared_pool_reserved_size = 102928220 (At present)

As you have suggest that

the shared_pool_reserved_size shuld be 51464110)  10% of shared_pool_size  514641100

How can i make changes the parameter value just open the file *change the value from 102928220 to 51464110* and save that all in both the flies init.ora and initSID.ora

after that do i have to restart my server ? or there is a way without restarting the server .

There is one more thing I want to ask that there is difference in parameter value in Node

*LOG_CHECKPOINT_TIMEOUT             1800*  Node A

where shared_pool_reserved_size = 102928220

And  *LOG_CHECKPOINT_TIMEOUT          0* Node B

where shared_pool_reserved_size = 51464110

Please guide what should be the right Value of LOG_CHECKPOINT_TIMEOUT .

By present active parameter values will they effect the performance.

Regards,

[former_member105207 · Accepted solution]

Hi shiva,

You have to open the file edit the values and thenr restart the oracle database.

Before doing the same take a copy of your init.ora and init<SID>.ora file so that you can switch back to the old values in case of any problems.

if your shared_pool_size is 51464110

then shared_pool_reserver_size is 5146411.

Makes changes in the two files accordingly.

regards,

chandru.

[Former Member · Accepted solution]

Thanks ,

There is one more thing I want to ask that there is difference in parameter value LOG_CHECKPOINT_TIMEOUT is 1800 Node A and in Node B it is 0.

Please suggest what is right value shuled be.

By present active parameter values will they effect the performance.

Kind Regards,

Shiva

[former_member204746 · Accepted solution]

if using Oracle 10g, read SAP note 830576 for exact value to use.

if using Oracle 8 or 9i, check SAP note 124361.

[Former Member]

Dear Shiva,

Usually we change initSID.ora file directly.Whenever we change parameter like this.We should shutdown the oracle and create new spfile from profile by following command after shutting down database

SQL> create spfile from pfile;

Above command will ensure that there is no dissfrence in pfile(initSID.ora) and spfile.

and no conflict will occur.

Regards

Aditya

[Former Member]

Dear All,

As suggest to make change in init. and sid ora in my sever there are this two file where i have make the changes .

initPRD.ora and SPFILEPRD.ORA

I just wnat to cofrim that it is ok..

Regards,

[former_member204746]

if SPFILEPRD.ORA exists, this means that you are using an SPFILE.

you must change parameters using SQLPLUS su as:

sqlplus / as sysdba

alter system set shared_pool_size=412345678 scope both;

exit;

[Former Member]

Thanks , I have done in the file before reading you post. I was going to switch the nodes that day. it is working fine. Do I have to take any step .Please suggest .

Regards,

Shiva

[former_member204746]

I do not understand your last post?

if you changes parameters on both nodes (if using a pfile), that's all there is to do.

[Former Member]

Hi,

I mean to say that i have done the changes in initPRD.ora and SPFILEPRD.ORA because i have not read you post in which you have suggest that i have to do thet change parameters using SQLPLUS.

My sever are working fine in the node where i have made the change but i want to konw that if i have done any thing worng ?

The is also more thing after this there i more buffer busy wait time in st04.

Regards,

Shiva

[stefan_koehler]

Hello Shiva,

just for clarification.

The default value of SHARED_POOL_RESERVED_SIZE will be 5% of the SHARED_POOL_SIZE and not 10%.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1193.htm#REFRN10201

Regards

Stefan