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

  • From: Gaja Krishna Vaidyanatha <gajav@xxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 22 Mar 2014 02:35:05 -0700 (PDT)

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@xxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> 
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of GG
Sent: Monday, March 17, 2014 2:46 PM
To: oracle-l@xxxxxxxxxxxxx
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

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


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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2014