Shared pool

[sunkutup]

I had run this query on my database and got the following


SQL> select to_number(v$parameter.value)value, v$sgastat.bytes,
2 (v$sgastat.bytes/v$parameter.value)*100 "Percent Free"
3 from v$sgastat,v$parameter
4 where v$sgastat.name = 'free memory'
5 and v$parameter.name = 'shared_pool_size';

Shared Pool Size Free Bytes Percent Free
---------------- ---------------- ------------
29,000,000 2,878,876 9.92715862

Can someone please assist me with this problem. i am made to believe that the shared pool is small. To what value should I set the shared pool?

[pando]

how have you determined that is small with that simple result?

[julian]

Here is the basic rule:

Check from init.ora the value for DB_BLOCK_BUFFERS. Calculate how much this makes in bytes. Say you use block size of 8K and DB_BLOCK_BUFFERS = 5000. Then you come with a value of 40000K. The SHARED_POOL_SIZE should generally be 50% up to 150% of the calculated value. So we are now in the interval (approx.) 20M-60M. Now comes the tricky part, 20M? 40M? 60M? It depends on the amount of stored PL/SQL (functions, packages, etc). If you have a lot of them go fot 60M shared pool. There are Oracle DBs without any stored procedures. Then you might downsize your shared pool to even 10-15% of the calculated value based on DB_BLOCK_BUFFERS.

Was my explanation clear enough :-)

[nabaig]

Julian,

What happens when I know that my buffer cache is not enough? What I mean is, because of poorly written SQL's and poor indexing done on a legacy system, a lot of full table scans are taking place and my buffer cache hit ratio is 50-60%. How do I go about calculating the right size of the SGA?
I am in the process of fixing the full table scan problems but till then???

Thanks,
Nizar

[vijay8282]

Hi,

I am running SAP on Oracle 8.1.6 on NT.

DB Size is 106 GB.

I don't know much about SAP. How can I find abt the amount of PL/SQL stored.

My SGA is sized at 400000000.
buffers r 90000
block size is 8k

any views reg this.

[julian]

Originally posted by nabaig
Julian,

What happens when I know that my buffer cache is not enough? What I mean is, because of poorly written SQL's and poor indexing done on a legacy system, a lot of full table scans are taking place and my buffer cache hit ratio is 50-60%. How do I go about calculating the right size of the SGA?
I am in the process of fixing the full table scan problems but till then???

Thanks,
Nizar

Increase first DB_BLOCK_BUFFERS until

select 1 - (phy.value / (cur.value + con.value)) "BUFFER HIT RATIO (> 0.90 OK)"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads';

returns more than 0.9 There should be some activity in the DB in order to get the correct ratio. It might take some days to get the BUFFER HIT RATIO in the high 90s. How big is your DB, how much RAM do you have and what is the current value of DB_BLOCK_BUFFERS?

Ones you fixed the buffer's number, you can estimate the proper size of the shared pool.

[pando]

to calculate shared pool check v$db_object_cache, v$sqlarea

[julian]

How can I find abt the amount of PL/SQL stored.

For user SCOTT run:

select ceil(sum(vsize(text))/1024) "PL/SWL in Kilobytes" from dba_source where owner='SCOTT';

My SGA is sized at 400000000.
buffers r 90000
block size is 8k
any views reg this.

As I don't know anything about your DB, I could only say that the values a bit high but if you have the memory.... How much RAM do you have?

[vijay8282]

Statistics

RAM 2 GB

Hit Ratio 96 - 97 % (Observed over a period of time)

Now ?

[pando]

extracted from ilt books

SELECT SUM(sharable_mem)
FROM v$sqlarea
WHERE executions > 5
/

SELECT SUM(250 * users_opening)
FROM v$sqlarea
/

SELECT SUM(sharable_mem)
FROM v$db_object_cache
WHERE type = 'PACKAGE'
or type = 'PACKAGE BODY'
or type = 'FUNCTION'
or type = 'PROCEDURE'
/

SELECT 250 * value bytes_per_user
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'opened cursors current'
AND s.sid = 15
/


sum all is your shared pol usage

DONT set your shared pool too big if it´s not necessary, you will increase library cache lock latch contention if you do so