Re: Is this serious?

From: roobaron <member_at_dbforums.com>
Date: Thu, 05 Sep 2002 01:31:37 +0000
Message-ID: <1776320.1031189497@dbforums.com>

From my own experiences with 8.1.5 on Solaris and talking to others i would upgrade to at least 8.1.7 if possible.

Increasing the shared pool will help push the problem of fragmentation away into the future, though it might not solve it. You will need to check whether this SQL using non literals and causing the pool to fill up with similar SQL.

eg select id from table
where name = 'FRED'
select id from table
where name = 'BLAH'

instead of

select id from table
where name =:b

so you will need to check what the library cache ratios are

Prompt ********************************

Prompt ****Library-Cache Miss-Ratio****

Prompt ********************************

SELECT SUM(pins) "Executions",
SUM(reloads) "Cache misses while executing", (sum(reloads)/sum(pins))*100 "Miss-ratio(%)" FROM v$librarycache;

Have a look at Steve Adam's website www.ixora.com.au for some scripts to help with getting the shared_pool parameters set correctly.

New parameters (to test)

shared_pool_size = 73400320 # 70M
shared_pool_reserved_size = 4194304 # 4M _shared_pool_reserved_min_alloc=4000

Please read metalink about 4031 errors (if possible) as to why the min_alloc should be set.

Here is some SQL to help determine the appropriate setting for 4031.

Have Fun

Paul

• Shared memory info
• Paul Moen 14/08/2002
• For Oracle 8i where shared_pool_reserved_min_alloc is hidden PROMPT PROMPT Info on results from metalink Doc ID: 146599.1 PROMPT The ORA-04031 is a result of lack of contiguous space in the shared pool PROMPT reserved space if: PROMPT REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > PROMPT SHARED_POOL_RESERVED_MIN_ALLOC PROMPT PROMPT The ORA-04031 is a result of lack of contiguous space in the library cache if: PROMPT PROMPT REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < PROMPT SHARED_POOL_RESERVED_MIN_ALLOC PROMPT PROMPT or PROMPT PROMPT REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

SELECT free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size FROM v$shared_pool_reserved;

column "name" format a25
column "Value" format a15
column "Desc" format a35 wrap

select KSPPINM "name",
nvl(KSPPSTVL,'NULL') "Value",
KSPPDESC "Desc"
from sys.x$ksppi x, sys.x$ksppcv y
where x.INDX = y.INDX
and KSPPINM = '_shared_pool_reserved_min_alloc';

--
Posted via http://dbforums.com

Received on Wed Sep 04 2002 - 20:31:37 CDT