Contradictory Display of parameter shared_pool_size?

[Former Member]

Hello All,

I want to change the value of the parameter shared_pool_size. I am on Oracle 9i and I confirm that dynamic SGA is activated.

I use brtools to change this parameter and use scope=spfile.

After I change the parameter I go back to the Display parameter menu and when I display this parameter value it shows,

Parameter value=<old value> Why?

Value in spfile = <new value>

when i give the cmd show parmeter from SQL prompt it displays <old value>. Why???

Now even if i use scope='memory' or 'both' the outputs are,

In brtools Menu,

Parameter value=<old value>

Value in spfile = <old value>

and through sql prompt -> <old value>

can someone please explain?

I understand this parameter change doesnot require a restart because Dynamic SGA is activated but even a restart of DB doesnot help.

Thanks,

Bidwan

[former_member1351727 · Accepted solution]

Hi,

I think this is the problem with the parameter decrease . Even we have Dynamic SGA in place...If that is in use then if you try decrease the value it will not change until that space gets freeed up if that part of the buffer is in Use.

Increasing will take effect immediately

decreasing will take time.

However even I am not clear with the point that it did not take effect after the stop/start of the DB.

Thanks.

[Former Member · Accepted solution]

Thanks Vani,

When I try to increase the parameter, it works but only with a restart and if I try to decrease the parameter it works too(with a restart) but only till the lower limit of 92274688. If i try to decrease beyond it doenot work at all.

Ques 1. Any idea why restart is required although Dynamic SGA is activated

Ques 2 Why there is a lower limit?

[stefan_koehler · Accepted solution]

Hello Bidwan,

thanks for the pfile.

For more information about the Dynamic SGA handling, take a look at the oracle documentation (Part: Dynamic SGA Granules):

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96524/c08memor.htm#8452

Your SGA is 219750092 bytes ( = round about 210 MB).

Shared pool old: 92274688 bytes ( = round about 88 MB)

Shared pool new: 91274688 bytes ( = round about 87 MB)

Now with the information from the oracle documenation:

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 128 MB, and it is 16 MB for larger SGAs. There may be some platform dependency, for example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 128 MB.
A database administrator grows a component's SGA use with ALTER SYSTEM statements to modify the initialization parameter values. Oracle takes the new size, rounds it up to the nearest multiple of 16MB, and adds or takes away granules to meet the target size

Your change is to small to get a change, because of the granule size.

Regards

Stefan

[Former Member · Accepted solution]

Excellent explaination Vani and Stefan!!!

Thanks a lot!

Now when i try to increase or decrease the parameter by a good margin it works.

One last question pertaining to this topic,

Dynamic SGA means that you can change certain parameters without restarting the DB. But, when you change the parameter, the scope has to be 'both'. Right?

Because when i use scope='spfile', for the new value to come into effect it always needs a restart.

Thanks,

Bidwan

[stefan_koehler · Accepted solution]

Hello Bidwan,

> Because when i use scope='spfile', for the new value to come into effect it always needs a restart.

If you use scope='spfile', yes you need a restart of the database. Both means that it is set for the running instance and in the spfile.

If you want to change it in the running instance only, you can use scope='memory'.

Take a look at here for the 3 different options:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/create.htm#1014422

Regards

Stefan

[former_member1351727]

Hi,

When Dynamic SGA is in use the allocation and deallocation of memory parameters happens in the units of Granules.

As a rule of thumb if the Total SGA is less than 1 GB then the Granule size is 4 MB.

So if we decrease or increase any parameter it takes place in that 4 MB units.

In your case:

Parameter value (value) ...... 92274688 <---Old Value

Value in spfile (spfval) ..... 91274688 <---New Value

So if we consider your system is having Granule size as 4 MB,

Then it allocates only in mutiples of that.

Old Value =92274688 /1024 = 90112 /1024 = 88MB (22 granules)

New Value =91274688 /1024 = 89135.4375 /1024 = 87.04632568359375 MB (21.7615814208984375 Granules)

So it ruonds up to the nearest granule (always to the higher) and allocates that to the parameter.

This is what is my analysis of the problem.

To make sure this is correct can you try allocation 21 or 20 Granules exactly and see if the value is decreasing or not.

So the new value for Shared pool if we take 20 Granules and considering granule size is 4MB will be

83886080 bytes.(83886080/1024 = 81920 /1024 = 80MB /4 =20 Granules)

Hope this helps ...

Thanks.

[Former Member]

may be your database startup under pfile not spfile, so you could not use alter system set shared_pool_size=？？scope = both to change the parameters. you can create new spfile use command "create spfile from pfile".

hopes this tip will help you!

[Former Member]

Thanks Zhou,

I have 2 questions now,

Question 1: How do I know whether database startup is under pfile or spfile?

Question 2: When I use scope = spfile , it doesnot update the parameter value in the pfile. Is it?

Thanks,

Bidwan

[stefan_koehler]

Hello Bidwan,

> Question 1: How do I know whether database startup is under pfile or spfile?

SQL> show parameter spfile

> Question 2: When I use scope = spfile , it doesnot update the parameter value in the pfile. Is it?

No, the pfile is only updated in the case of "SQL> create pfile from spfile".

Regards

Stefan

[Former Member]

Thanks Stefan,

show parameter spfile

>> results in VALUE=spfile path...so my db startup is through spfile...

In my spfile i can see the <new value>

Then why the <new value> is not reflected in

1. show parameter shared_pool_size ???

2. Brtools -> Display Parameter value ->

Parameter value=<old value> Why?

Value in spfile = <new value>

Any pointers will be apreciated.

Thanks in advance.

[Former Member]

HI,

if it does not affected then

edit the init.ora file, add

shared_pool_size=<value>

and restart your database.

regards,

kaushal

[stefan_koehler]

Hello Bidwan,

please do the following and post the content of the pfile.

SQL> create pfile from spfile;

Regards

Stefan

[Former Member]

Hello All,

After executing the command SQL> create pfile from spfile; i restarted the insstance

SQL> startup

ORACLE instance started.

Total System Global Area 219750092 bytes

Fixed Size 454348 bytes

Variable Size 134217728 bytes

Database Buffers 83886080 bytes

Redo Buffers 1191936 bytes

Database mounted.

Database opened.

SQL> show parameter shared_pool_size

NAME TYPE VALUE

-

---

-

---

-

---

shared_pool_size big integer 92274688 <---Old Value

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

F:\Documents and Settings\abcadm.HOME>brtools

BR0651I BRTOOLS 6.40 (15)

.

.

.

.

-

---

Information about database parameter 'shared_pool_size'

1 - Parameter description (desc) . size in bytes of shared pool

2 - Parameter type (type) ........ big integer

3 - Modifiable attribut (modif) .. both

4 - Defined in spfile (spfile) ... yes

5 - Database instance (instance) . *

6 - Defualt value (default) ...... no

7 - Parameter value (value) ...... 92274688 <---Old Value

8 - Value in spfile (spfval) ..... 91274688 <---New Value

9 - Comment on update (comment) ..

My Pfile :

initABC.ora

*.background_dump_dest='E:\oracle\ABC\saptrace\background'

*.compatible='9.2.0'

*.control_file_record_keep_time=30

*.control_files='E:\oracle\ABC\origlogA\cntrl\cntrlABC.dbf','E:\oracle\ABC\sapdata1\system_1\cntrl\cntrlABC.dbf','E:\oracle\ABC\saparch\cntrl\cntrlABC.dbf'

*.core_dump_dest='E:\oracle\ABC\saptrace\background'

*.db_block_size=8192

*.db_cache_size=81920000

*.db_file_multiblock_read_count=8

*.db_files=254

*.db_name='ABC'

*.dml_locks=4000

*.enqueue_resources=8000

*.fast_start_mttr_target=900

*.hash_join_enabled=false

*.log_archive_dest='E:\oracle\ABC\oraarch\ABCarch'

*.log_archive_start=true

*.log_buffer=1048576

*.log_checkpoint_interval=0

*.log_checkpoint_timeout=0

*.log_checkpoints_to_alert=true

*.open_cursors=800

*.optimizer_features_enable='9.2.0'

*.optimizer_index_cost_adj=10

*.optimizer_mode='choose'

*.pga_aggregate_target=31457280

*.processes=80

*.remote_os_authent=true

*.sessions=96

*.sga_max_size=209750092

*.shared_pool_reserved_size=9011200

*.shared_pool_size=91274688

*.sort_area_retained_size=0

*.sort_area_size=2097152

*.statistics_level='typical'

*.timed_statistics=true

*.transaction_auditing=FALSE

*.undo_management='AUTO'

*.undo_retention=43200

*.undo_tablespace='PSAPUNDO'

*.user_dump_dest='E:\oracle\ABC\saptrace\usertrace'

*.workarea_size_policy='AUTO'

Can someone thow some light now?

Thanks,

Bidwan