Re: ORA-4031 "shared pool","unknown object","sga heap(6,1)","KTI SGA freea"
Date: Sat, 22 Mar 2014 07:05:19 -0700 (PDT)
Message-ID: <1395497119.59190.YahooMailNeo_at_web184302.mail.ne1.yahoo.com>
Hi Greg,
Just as a follow-up - have an eye on the various latch/mutex contention on the various structures in the shared pool, which can/will occur once you reduce the number of sub-pools. In your case you have reduced the number of sub-pools to 42.8% of its original value. You also mentioned your shared server configuration, so that is another additional wrinkle you will have to deal with in this scenario. Good luck!
Cheers,
Gaja
Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com
Phone - +1 (650) 743-6060
LinkedIn - http://www.linkedin.com/in/gajakrishnavaidyanatha
Co-author: Oracle Insights:Tales of the Oak Table - http://www.apress.com/9781590593875 Primary Author: Oracle Performance Tuning 101 - http://www.amzn.com/0072131454 Enabling Exadata, Big Data and Cloud Deployment & Management for Oracle
From: GG <grzegorzof_at_interia.pl>
To: gajav_at_yahoo.com; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Saturday, March 22, 2014 7:49 PM
Subject: Re: ORA-4031 "shared pool","unknown object","sga heap(6,1)","KTI SGA freea"
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:
- 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 -- http://www.freelists.org/webpage/oracle-lReceived on Sat Mar 22 2014 - 15:05:19 CET