how to solve : ORA-04031

hi all,

I am getting this error whenever i am running a big query which has couple of inline views in it. I have recently loaded oracle9i and this problem started occurring after that. I know that I have to increase the shared memory size and the only way to do that is to increase the SGA size. is there a way to find out what should be the approximate size of SGA so that this error never appears in future? pls guide me once again to get rid of this problem for once and for all.
-----------------the error message----------------------

SQL error occurred while retrieving data. Error: ORA-04031: unable t
o allocate 71416 bytes of shared memory ("large pool" "unknown object" "hash-join subh" "kllcqc:kl
lcqslt")
--------------------------------------------------------

thanks a lot!

Parijat

1) This error indicates that Oracle is unable to allocate memory from the ‘large’ pool.To determine the number of free bytes in the ‘large’ pool execute the following SQL:

SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='LARGE POOL' GROUP BY ROLLUP (NAME);
To resolve the problem, consider increasing the value for ‘LARGE_POOL_SIZE’.

2) You can issue a 'ALTER SYSTEM FLUSH SHARED_POOL' then identify the main SQLs/packages and pin them.

3) Please also increase SHARED_POOL_RESERVED_SIZE to more than size of the biggest PL-SQL you use.

4) Some one suggested the following in other forum:

Determine if the ORA-04031 was a result of fragmentation in the library cache or in the shared pool reserved space by issuing the following query:

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

The ORA-04031 is a result of lack of contiguous space in the shared pool reserved space if:

REQUEST_FAILURES is > 0 and
LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC.

To resolve this consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower the number of objects being cached into the shared pool reserved space and
increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the available memory in the shared pool reserved space.

The ORA-04031 is a result of lack of contiguous space in the library cache if:

REQUEST_FAILURES is > 0 and
LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

or

REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

The first step would be to consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to put more objects into the shared pool reserved space and increase
SHARED_POOL_SIZE.