Re: SGA Help......

V.N.Saravana Vijayan wrote:
>
> Hi DBA's,
>
> I am just begining DBA(!!-) . I am getting this error
> when I am trying to do DML operation[
> 2000 SQL stmts I am executing one by one,
> commiting only each 16th SQL stmt].
>
> ORA-04031 Unable to allocate 744 bytes of shared
> memory("unknown object","sga heap","library cache")>!
>
> I dont want to keep increase shared spool size even
> for small DB's.
>
> Shared spool area 6000000
> db_block_size 4096
> db_block_buffers 2000
>
>
> THANKX IN ADVANCE.
>
> +--------------------------------------------------------------------+
> | V.N.Saravana Vijayan email: vijay_at_mercedes.bbn.hp.com |
> +--------------------------------------------------------------------+I think I have a feeling what happens, only I'm not very sure, as you
were not too specific about the nature of your transactions. Any statement that differs from any statement previously parsed will allocate memory in the library cache. Oracle computes a hash total for every statement, if one single character differs this is considered to be a "new" statement. For example:
select * from emp where ename = 'KING' and select * from emp where ename = 'JONES'
are considered to be different statements. This is especially applies to SQL*Plus where every statement is parsed and when you don't embed them in PL/SQL is considered to be a new statement.
When you are executing INSERTS in SQL*Plus, you might consider putting the data in a file and use SQL*Loader instead. As an alternative, somewhere in the utility routines shipped with Oracle there is a PL/SQL procedure that flushes the shared pool. Right now I can't check this in the documentation, because Oracle*Book has hang up.

Hth

Sybrand Bakker,
Senior IS Analyst
Bentley Systems Europe Received on Thu Feb 22 1996 - 00:00:00 CET