X-Received: by 10.236.145.34 with SMTP id o22mr6557240yhj.22.1386613498432; Mon, 09 Dec 2013 10:24:58 -0800 (PST) X-Received: by 10.50.136.133 with SMTP id qa5mr313929igb.4.1386613498389; Mon, 09 Dec 2013 10:24:58 -0800 (PST) Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!209.197.12.246.MISMATCH!nx02.iad01.newshosting.com!newshosting.com!news-out.readnews.com!news-xxxfer.readnews.com!209.85.216.88.MISMATCH!p15no15342896qaj.0!news-out.google.com!p7ni13264qat.0!nntp.google.com!ie8no4804421qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.oracle.server Date: Mon, 9 Dec 2013 10:24:57 -0800 (PST) In-Reply-To: <135a1233-a707-4f69-a5bd-b9c98e7da22a@googlegroups.com> Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=69.4.5.254; posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC NNTP-Posting-Host: 69.4.5.254 References: <135a1233-a707-4f69-a5bd-b9c98e7da22a@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <52b7f14e-f684-45f2-8f86-1f5b2a94b171@googlegroups.com> Subject: Re: Growth of CCursor sub-heap of the shared pool From: ddf Injection-Date: Mon, 09 Dec 2013 18:24:58 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: news.cambrium.nl On Sunday, December 8, 2013 5:09:26 PM UTC-7, vsevolod afanassiev wrote: > Oracle 11.1.0.7 on Sun SPARC Solaris 10 >=20 > We observe following: >=20 > - Growth of shared pool due to growth of CCursor sub-heap, mostly in one = sub-pool (ouf of 4). In 12 hours size of CCursor increased from 130 MB to 5= 00 MB. >=20 > - V$SQL shows that some SQL statements experience growth of sharable memo= ry (V$SQL.SHARABLE_MEM) while number of versions remains constant and small= (1 - 2) >=20 > (V$SQL.LOADED_VERSIONS, V$SQL.OPEN_VERSIONS). The SQL statements are simp= le. >=20 > - Flushing shared pool doesn't help >=20 >=20 >=20 > History of the issue: >=20 > - the application was implemented several years ago and was running fine.= It executes simple SQL statements, most accessing just one table, nothing = fancy - no LOBs,=20 >=20 > no LONG columns, no nested tables, etc.=20 >=20 > - A few weeks ago the database started reporting ORA-04031 and in about 2= 0 min it became very slow and had to be restarted >=20 > - Trace files were showing that subpool #3 didn't look normal >=20 >=20 >=20 >=20 >=20 > Subpool#1: >=20 > - free memory: 35 MB >=20 > - sql area: 13 MB >=20 > - CCursor: 13 MB >=20 >=20 >=20 > Subpool#2: >=20 > - free memory: 42 MB >=20 > - sql area: 14 MB >=20 > - CCursor: 170 MB >=20 >=20 >=20 > Subpool#3: >=20 > - free memory: 375 MB >=20 > - sql area: 40KB (40 KB, not MB) >=20 > - CCursor: -1.2 GB (negative value) >=20 >=20 >=20 > Subpool#4: >=20 > - free memory: 43 MB >=20 > - sql area: 14 MB >=20 > - CCursor: 10 MB >=20 >=20 >=20 > In my experience size of 'free memory' is misleading, it is size of 'sql = area' that matters, if it falls below certain threshold the database starts= reporting ORA-04031. >=20 >=20 >=20 > Looking at size of CCursor over time (from STATS$SGASTAT) we noticed that= it was stable at approx 100 MB, two weeks before first ORA-04031 it starte= d growing and has grown to 2 GB. Looking at STATS$SQL_SUMMARY we noticed th= at sum(loaded_versions) was approx 1,500 and remained stable; sum(version_c= ount) was also stable at 2,000. However sum(sharable_mem) experienced linea= r growth from 200 MB to 2 GB. >=20 >=20 >=20 > This behaviour has been reported earlier (bugs 10399563, 11056048), howev= er there is no patch/workaround. >=20 >=20 >=20 > We have taken two heapdumps and run heapdump analyzer: >=20 >=20 >=20 > -- Heapdump Analyzer v1.00 by Tanel Poder ( http://www.tanelpoder.com ) >=20 >=20 >=20 > Total_size #Chunks Chunk_size, From_heap, Chunk_type, Al= loc_reason >=20 > ---------- ------- ------------ ----------------- ----------------- ---= -------------- >=20 > 136467976 122723 1112 , sga heap(3,2), freeable, CCu= rsor >=20 > 81179344 75727 1072 , CCursor, perm, perm >=20 > 28431616 25568 1112 , sga heap(2,2), freeable, CCur= sor >=20 > 16409784 14757 1112 , sga heap(1,2), freeable, CCur= sor >=20 >=20 >=20 >=20 >=20 > We are looking at upgrading to 11.2, however this will take some time.=20 >=20 > Any idea what could help to prevent/reduce growth of CCursor? As far as I know the only way around this is to upgrade to 11.2.0.x; this i= s likely why there is no patch/workaround for this bug (it's fixed in a lat= er release). David Fitzjarrell