Re: ORA-4031 "shared pool","unknown object","sga heap(6,1)","KTI SGA freea"

From: GG <grzegorzof_at_interia.pl>
Date: Sat, 22 Mar 2014 12:49:50 +0100
Message-ID: <532D78DE.4050603_at_interia.pl>



Hi,

  thanks for all valuable comments . We dont have interval partitioning nor ddl (its oltp env no exadata) .
In our case ora-4031 was caused (according to MOS) by shared pool fragmetnation
and recommendations are:

  1. Change the hidden parameter, _shared_pool_reserved_pct to 10 or 15 :

SQL> alter system set "_shared_pool_reserved_pct"=10 scope=spfile or
add this in the pfile : "_shared_pool_reserved_pct"=10

Restart the database.

Set _kghdsidx_count to 3 in order to be sure the shared pool will not be divided in many subpools(you currently have 7).

sql> alter system set "_kghdsidx_count"=3 scope=spfile;

I did some awr research around time of issue and found many TX- row lock contention waits, still not sure if it was cause or effects. Because of our architecture we have like 1k sessions serviced by 30 shared servers and the app badly reacts on slow downs . Meaning connection storm and same sql amplifications on many different session .
This is crap as You can imagine but thats how we run :) .

Regards
Greg

W dniu 2014-03-22 10:35, Gaja Krishna Vaidyanatha pisze:
> Hi Greg,
>
> For the specific issue that you have written about, Mark as guided you
> in the right direction. Nevertheless, I am wondering whether you are
> experiencing ORA-4031s for specific operations or is it random.
>
> We battled with an issue specific to DDL operations especially on
> INTERVAL-PARTITIONED tables. If you are getting ORA-4031s for that,
> you should probably look into:
>
> -- Bug#16864042 -- Unnecessary library cache operations on for DDLs on
> Exadata
>
> Even though the text says Exadata, it is Generic and applicable across
> all platforms. We have run into this issue even on non-Exadata
> databases. We had to deal with this and a slew of issues with the
> shared pool on 11.2.0.3 with excessive consumption on
> KQRLPO/KQRXPO/KGLH0/SQLA/PRTMV. Even after patching with #13814739 we
> had no choice but to go to 11.2.0.4 as the issue was not resolved in
> 11.2.0.3. Our biggest problem was that regardless of how much free
> memory there was in the shared pool (in total), there was significant
> cluttering and zero-aging of DDLs in the first "sub-pool". When the
> first sub-pool ran out of free memory, the ORA-4031s started to pop up
> at an incredible frequency. Hope this helps.
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Mar 22 2014 - 12:49:50 CET

Original text of this message