Re: Large Pool vs Shared Pool

"Kevin S. Smith" <SpamNoMore_at_EriePlating.com> wrote in message news:<ug1hb5d0ikbvfa_at_corp.supernews.com>...
> Oracle 8i on Redhat 7.2.
>
> I read that instead of using a large pool, you can set the
> SHARED_POOL_RESERVED_SIZE init.ora parameter to reserve memory in the shared
> pool for large SQL statments to be processed. Is there an advantage to using
> either one of these? We are not running multithreaded server, but we do run
> some very large SQL queries. Any insight on this would be appreciated.
>
>
> Kevin

Kevin, the shared_pool_reserved_size is set by default to a version/platform specific default. I think the documentation says it is set to 10%, but we found out otherwise working with Oracle support on 04031 errors that have been traced to bugs.

It the large pool parameter is still valid I would not use it or change the default setting for shared_pool_reserved_size unless I had indications of shared pool memory or performance problems.

You can query v$shared_pool_reserved to see if your system is using the reserved area. If you run into ORA-04031 errors but have space in the reserve area support might direct you to lower the size threshold for considering objects large using _shared_pool_reserved_min_alloc.

Here is some SQL to be ran as SYS or Internal: set echo off

--
-- SQL to investigate shr pool memory prob
--     Based on Oracle support tar response
--   a) show stat for reserved area
--   b) show sql causing flushing
--
--  Note - You must be sys to run this code, uses x$ table.
--
-- 20010627  Mark D Powell   Save code to show shr pool
--
col avg_free_size heading 'AVG|FREE|SIZE'
col avg_used_size heading 'AVG|USED|SIZE'

spool pool_flushed

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

-- code that causes code to be flushed from the shared pool
-- selecting against the ksmlru fixed table flushes its contents

SELECT
  *
FROM   X$KSMLRU
WHERE  ksmlrsiz > 0;

spool off

Notice the comment about querying ksmlru causes its contents to be
lost so spooling is a good idea when querying this view.

HTH -- Mark D Powell --