Home » RDBMS Server » Performance Tuning » Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown object
Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown object [message #64767] Fri, 23 January 2004 20:19 Go to next message
Arvind Bhandari
Messages: 50
Registered: May 2003
Member
Hello, we are using Oracle 9i Database and Report 3.0 but there is some problem related with Shared memory after running SQL. When we run following SQL in SQL and Report 3.0 then it gives following Error

ERROR at line 2:
ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown object","hash-join subh","kllcqas:kllsltba")

SELECT NVL(SUM(A.Loan_Amt),0)
       FROM BMHLACC A,BTDLLED B,BMDPRIGRP C
       WHERE A.Branch_Cd = '03'
       AND   A.Acc_Type  = B.Acc_Type
       AND   A.Acc_No    = B.Acc_No
       AND   A.Branch_Cd  = C.Branch_Cd
       AND   A.Pri_Grp_Cd = C.Pri_Grp_Cd
       AND   A.Purpose_Cd = C.Pur_Cd
       AND   TRUNC(C.Eff_Fr) = (SELECT TRUNC(MAX(Eff_Fr))
                                FROM BMDPRIGRP
                                WHERE Branch_Cd  = A.Branch_Cd
                                AND   Pri_Grp_Cd = A.Pri_Grp_Cd
                                AND   Pur_Cd     = A.Purpose_Cd
                                AND   TRUNC(Eff_Fr) <= To_Date('31-Dec-2003'))
       AND  A.Loan_Amt <= C.Pri_Limit
       AND ((A.status = 'O') OR
            ( (A.Acc_Type,A.Acc_No) in (select acc_type,acc_no
                                  from bmcllacc
                                  where trunc(Trans_DT) > To_Date('31-Dec-2003') AND   Status = 'A'))
           )
       AND  B.Bal_Amt >= 0
--       AND  A.Pri_Grp_Cd = :Pri_Grp_Cd
       AND  TRUNC(A.Open_Dt) <= To_Date('31-Dec-2003')
       AND  A.Catg_Cd IN('GN','WM','SC')
       AND  Trunc(b.trans_DT) = (select trunc(max(trans_DT))
                                 from btdlled
                                 where acc_type = a.acc_type
                                 and  acc_no = a.acc_no
                                 and trunc(trans_DT) <= To_Date('31-Dec-2003'))
       and b.sno = (select max(sno)
                    from btdlled
                    where acc_type = a.acc_type
                    and  acc_no = a.acc_no
                    and trunc(trans_DT) = trunc(b.trans_DT))

In init.ora the parameter for share_pool_size has set to

shared_pool_size=33554432

open_cursors=300

When we tried to change open cursor parameter then it's not change. When we see on Enterprise Manager console, the Configration parameter shown dynamicaly for both the parameter.

can we change both paramter or it automatically change dynamically in oracle 9i ?

Thanks

Arvind

 

 

 

 

 
Re: Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown ob [message #64768 is a reply to message #64767] Sat, 24 January 2004 04:10 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Hi,
you may get this error when
i) shared_pool_size is 'really' insufficient for the workload
ii) shared_pool, gets fragmented for some reasons(tons of unique sql becos of lack of bind variables,huge anonymous pl/sql, UGA of Shared servers being allocated in shared pool instead of large pool etc)

In your case,it may be that you have insufficient shared_pool_size and hence you can increase it .

thiru@9.2.0:SQL>show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 33554432

thiru@9.2.0:SQL>alter system set shared_pool_size=35m;

System altered.

thiru@9.2.0:SQL>show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 37748736
thiru@9.2.0:SQL>


You dont have to increase open_cursors in this case.

-Thiru
Re: Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown ob [message #64772 is a reply to message #64768] Tue, 27 January 2004 02:19 Go to previous messageGo to next message
Arvind Bhandari
Messages: 50
Registered: May 2003
Member
Thanks Thiru, actually shared_pool_size was not increased through alter system in SQL session but in Oracle enterprise manager console i have increase the MAX SGA Size and increase shared_pool_size and large_pool_size. after that query runs. but one doubt again that when we increased the MAX_SGA_SIZE parameter it automatic increase the value of shared_pool_size. after that we have substact 10 MB from shared_pool_size and added to large_pool_size.

once again thanks. if server manager is not available in oracle 9i then how can we shutdown and startup database as in oracle 8i we can do by running svrmgrl

Arvind

Arvind
Re: Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown ob [message #64775 is a reply to message #64772] Tue, 27 January 2004 07:20 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Arvind,
you use sqlplus to startup/shutdown database

dbatest@samgdeab06:/ora1/oracle/app/oracle/admin/dbatest/udump>sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.3.0 - Production on Tue Jan 27 12:23:27 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  336679728 bytes
Fixed Size                   455472 bytes
Variable Size             117440512 bytes
Database Buffers          218103808 bytes
Redo Buffers                 679936 bytes
Database mounted.
Database opened.



-Thiru
Re: Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown ob [message #64777 is a reply to message #64775] Wed, 28 January 2004 02:50 Go to previous messageGo to next message
Arvind Bhandari
Messages: 50
Registered: May 2003
Member
thanks again thiru. i have tried to shutdown through SQLp plus but my machine hangs at that time. anyway i shall try again. if we shutdown the database through SQL plus and exits from the session then how can we again connect SQL plus session. as in ORACLE 8i SERVER MANAGER runs even database shut down. but in ORACLE 9i SQL session will not work if database down

yesterday we have changed that open_cursors and Shared_pool_size but today again set to as it was in previous. when we see in init.ora then it shows previous entry. I think yesterday changed specific for that session only and it stores in session's initial parameter file. so how can we change parameters direcly in init.ora. as in oracle 8i we first shutdown the database through server manager and change mannualy in initorcl.ora and startup the database.
how can we increase the SGA and shared_pool_size permanently in init.ora file

Thanks again thiru

Arvind
Re: Oralce Error ORA-04031: unable to allocate 4608 bytes of shared memory ("large pool","unknown ob [message #64778 is a reply to message #64777] Wed, 28 January 2004 04:58 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Arvind,
to establish a sqlplus connection , without actually establishing a session to the database ,

DEV08@samgdeab06:/ora1/oracle/app/oracle/admin/dbatest/bdump>sqlplus /nolog

SQL*Plus: Release 9.0.1.4.0 - Production on Wed Jan 28 09:44:22 2004

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

SQL> 



that will work even when the database is down.

There is no separate session parameter file . There is only one parameter file ( init.ora or spfile.ora) where the instnace configuration parameters are configured.

Some parameters are session modifiable and some are only system modifiable(ie requires an instance bounce to take effect).

If you are using init.ora and you want to make the changes permanent, you will need to edit the init.ora manually and bounce the instance.

If you are using spfile, then the following rules apply :

SCOPE Clause Description
SCOPE = SPFILE
The change is applied in the server parameter file only. The effect is as follows:

For dynamic parameters, the change is effective at the next startup and is persistent.
For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.

SCOPE = MEMORY
The change is applied in memory only. The effect is as follows:

For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
For static parameters, this specification is not allowed.

SCOPE = BOTH
The change is applied in both the server parameter file and memory. The effect is as follows:

For dynamic parameters, the effect is immediate and persistent.
For static parameters, this specification is not allowed.


It is an error to specify SCOPE=SPFILE or SCOPE=BOTH if the server is not using a server parameter file. The default is SCOPE=BOTH if a server parameter file was used to start up the instance, and MEMORY if a traditional initialization parameter file was used to start up the instance.

Hope this clarifies..
Thiru
Re: Oralce Error ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","unknown o [message #64939 is a reply to message #64768] Thu, 11 March 2004 21:33 Go to previous message
jitendra
Messages: 11
Registered: January 2002
Junior Member
pls solve the same
Previous Topic: utlbstat, utlestat
Next Topic: Local schema vs. public schema
Goto Forum:
  


Current Time: Wed Sep 30 11:16:22 CDT 2020