Shared Pool Size

[toshi]

Hello Everyone,

Question about the size of the shared pool on the SGA:

If you type in the following in SQL*Plus:
SQL> show parameter shared_pool_size

It gives you the following output:
NAME TYPE VALUE
------------------------ ----------- ------------------------------
shared_pool_size big integer 50331648

Yet when you query on v$sgastat like this:
SQL> select sum(bytes) from v$sgastat where pool = 'shared pool';

It give you the following output:
SUM(BYTES)
----------
67108864

This tells me that obviously my query on the v$sgastat is not necessarily true on how to compute for the shared pool size. Are there some other views where I can derived the shared pool size? I assume the correct value would be whatever the parameter tells me (which is in my case 50331648).

Any ideas would be greatly appreciated.

Also, why is the value of the parameter log_buffer different from the redo buffers in v$sga?

SQL> show parameter log_buffer;

NAME TYPE VALUE
------------------------------------ ----------- ----------
log_buffer integer 524288

SQL> show sga;

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes


Again, any ideas would be greatly appreciated. Please bear with me as my questions might appear to some readers as so "newbie". That's because I am a newbie in Oracle Administration.

Thanks

[sursujmad]

SQL> show parameter shared_pool_size

It gives you the following output:
NAME TYPE VALUE
------------------------ ----------- ------------------------------
shared_pool_size big integer 50331648

the value is from the parameter file. this value may not be the
value of your shared_pool if you are using Oracle 9i as you will be able to change shared_pool_size dynamically.


Regards,

[vastdba]

Originally posted by sursujmad
SQL> show parameter shared_pool_size

It gives you the following output:
NAME TYPE VALUE
------------------------ ----------- ------------------------------
shared_pool_size big integer 50331648

the value is from the parameter file. this value may not be the
value of your shared_pool if you are using Oracle 9i as you will be able to change shared_pool_size dynamically.


Regards,

Could you explain how can we change the parameters. ok. it is possible in INIT*.ORA file. But how is possible to make change them dynamically.

VAST

[Orca777]

Hi
in 9iR2 you can do it :

ALTER SYSTEM SET SHARED_POOL_SIZE =66M SCOPE=BOTH;

There are some restriction to the max-size and you should
specify which scope :

memory is immediate but not persistant
spfile is not immediate but persistant
both is both
Orca

[toshi]

But how about the redo log cache, the parameter log_buffer is static in 9i yet when you query the v$sga, it show a different number as compared to the parameter.


SQL> show parameter log_buffer;

NAME TYPE VALUE
------------------------------------ ----------- -----------
log_buffer integer 524288


SQL> show sga;

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes


Thanks for your ideas.

Best Regards,
toshi