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

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.

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: "Powell, Mark" <mark.powell2_at_hp.com>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 
Sent: Tuesday, March 18, 2014 3:56 AM
Subject: RE: ORA-4031 "shared pool","unknown object","sga heap(6,1)","KTI SGA freea"
 

Have you looked at ORA-600 [kti_freeing_non_freeable] And ORA-4031 On Shared Pool (Doc ID 1457819.1) ?


-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of GG
Sent: Monday, March 17, 2014 2:46 PM
To: oracle-l_at_freelists.org
Subject: ORA-4031 "shared pool","unknown object","sga heap(6,1)","KTI SGA freea"

Hi,
  Anyone has an idea what is the 'KTI SGA freea' allocation all about ?
We've experienced ora-4031 related to this on 11.2.0.3 with latest PSU applied .
So far, no idea . KGLH0 allocations sizes as well as SQLA was not an issue this time :).

Regards
Greg

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l