Home > Default > Ora-4031 after reducing the max sga

Ora-4031 after reducing the max sga

November 30Hits:0
Advertisement
Oracle 10g R2 and Baan 5c on AIX 5.3 L (p550, 3792M , 4 lcpu)
After I reduced the max_sga_size (so to avoid the paging), now I see more ora-4031 (out of shared memory loading library cache object) warnings in the udump trc file, not in alert file. In addition, they appeared in the log.ora.sql of Baan as an error message, so the query requests were hung or stopped.
Reading from other posts and metalink note: 146599.1, it indicated that the system has extensive fragmentation problem. (REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC. )
So I used the tips form AskTom to find the query scripts that may have bind variable problem and I did found some, most of them in 2~4 multiples. However, not all of them gave ora-4031 warning/error, actually only very few.
So what should I do for the best practice of the performance? Correct the problems with the bind variable of all queries or
increase the max SGA again.
All the queries were produced by the Baan´s processes or modules.

Answers

I got hit this thread while searching some other subjects.
(This is kind of old... but answered for a long time)
I think this is somewhat interesting problem.
Here is one query showing in the query of mulitple copies in system by using asktom technique. The system return 3 copies.
SELECT /*+ FIRST_ROWS INDEX(A [email protected][email protected]) */ A.T$CLST FROM >[email protected] A WHERE (A.T$CLST = :@ OR A.T$CLST = :@) AND A.T$ORNO = :@I think there is a chance that you're hitting bind mismatch problem.
Is [email protected]$CLST(What an annoying names~) column is VARCHAR2 type? If yes, following thread will be helpful.
Re: Is there any way to avoid hard parsing caused by "BIND_MISMATCH" in 10g

Read other 12 answers

Tags:

Related Articles

  • Ora-4031 after reducing the max sgaNovember 30

    Oracle 10g R2 and Baan 5c on AIX 5.3 L (p550, 3792M , 4 lcpu) After I reduced the max_sga_size (so to avoid the paging), now I see more ora-4031 (out of shared memory loading library cache object) warnings in the udump trc file, not in alert file. In

  • Explain one query in 256M shared pool and ORA-4031 .October 11

    Hi, looks like Oracle 9.2.0.8 got some problems with explaining huge (1000 lines) queries, here goes ORA-4031 dump, shared pool is about 256 M but there are no other sessions in that DB only mine . I can reproduce that in 1GB shared pool as well . **

  • ORA-4031 [512024] [large pool] [unknown object] [large pool] [PX msg pool]November 30

    Hello Friends, I am frequently getting error in alert.log file. ORA-4031 [512024] [large pool] [unknown object] [large pool] [PX msg pool] Database version: 11gR2 OS : Solaris 10 SGA Size 4gb SGA Target 4112mb I am unable to identify the cause for ab

  • Oracle Support ORA-4031 Troubleshooting Tool problem - FILE VALIDATION FAILNovember 30

    Hi, Having problem while trying to use ORA-4031 Troubleshooting Tool on the Oracle Support site. I believe I upload all files correctly: db alert log, 4031 trace file and AWR report (HTML), answered YES to the question. But, get this error: "FILE VAL

  • Ora-4031&ora-1280 error-Shared Memory ProblemNovember 30

    Sir , I am using oracle Streams for Data Replication I am facing the problem while confifurung cApture Process..It is giving error-ora-4031 description Ora-4031-Unable to allocate <n> bytes of shared memory ( "Shared Pool","Unknown Ob

  • Cause of ORA 4031 ErrorNovember 30

    In a interview I got a question what will be the cause of ORA 4031 ErrorWere you supposed to directly answer this question, or were you allowed to browse oracle error codes manual? ORA-4031: Unable to allocate bytes of shared memory ORA-4031 is a ver

  • ORA-604 and ORA-4031 during installation of SAPNovember 30

    Hi, I tried to install Oracle 10.2.0.2 on Windows 2003 with SAP R3E 47X2. However, during the installation on the Database Load phases it pops up a ORA-604 and ORA-4031 error log. It was unable to completed the Database Load phase showed as R3 load j

  • ORA-4031 SolarisNovember 30

    Hi, I'm having problems with the following error message: ORA-4031: unable to allocate 72 bytes of shared memory ("large pool", "unknown object", "session heap", "trigger condition node"). It always appears the SECO

  • ORA 4031November 30

    ora 4031 is caused by a lot of 'in' and/or 'or' in one sql statement. and not by an (maybe) poor performance statement caused by a not existing index. SELECT LIKE THIS : p_rsize = 3500   LOOP AT i_edidc INTO w_edidc.     r_docnum-low = w_edidc-docnum

  • Max SGA size?November 30

    I've got a box with 2 gigs of RAM, but no matter what I set the kernel and init.ora parameters to, I can't start an instance with more than an 800 meg SGA. That's any combo of db_block_buffers and shared_pool_size. I get a Linux error 22 or something

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.748 s.