Message-ID: <99338353-e6fd-4330-3e58-69cf875c5f77@gmail.com>
Date: Mon, 29 Nov 2021 16:28:40 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.3.0
Subject: Re: Shared Pool Question
Content-Language: en-US
To: oracle-l@freelists.org
References: <6a4532f37f5d48e4b1df80daeac8285b@ex04test91a.ad.rit.edu>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: <6a4532f37f5d48e4b1df80daeac8285b@ex04test91a.ad.rit.edu>
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: gogala.mladen@gmail.com

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 11/29/21 15:04, Scott Canaan wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:6a4532f37f5d48e4b1df80daeac8285b@ex04test91a.ad.rit.edu">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <meta name="Generator" content="Microsoft Word 15 (filtered
        medium)">
      <style>@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:#0563C1;
	text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:#954F72;
	text-decoration:underline;}span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;}div.WordSection1
	{page:WordSection1;}</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
      <div class="WordSection1">
        <p class="MsoNormal">We had a job that runs daily in the evening
          fail over the Thanksgiving weekend due to “ORA-04031: unable
          to allocate 63200 bytes of shared memory”.  One of the
          managers has requested that we find a way to monitor the
          shared pool and be able to catch when it is “going to run out”
          before it happens because “this can’t happen again”.  I have
          not found anything that can do this, so far. 
          <o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">Does anyone here have any ideas?<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">Thank you,<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal"><b><span
              style="font-size:12.0pt;font-family:&quot;Times New
              Roman&quot;,serif;color:#F47B20">Scott Canaan ‘88</span></b><b><span
              style="font-family:&quot;Times New Roman&quot;,serif"><br>
            </span></b><i><span style="font-family:&quot;Times New
              Roman&quot;,serif">Sr Database Administrator
              <br>
            </span></i><span style="font-family:&quot;Times New
            Roman&quot;,serif">Information &amp; Technology Services<br>
            Finance &amp; Administration <b><o:p></o:p></b></span></p>
        <p class="MsoNormal"><b><span style="font-family:&quot;Times New
              Roman&quot;,serif">Rochester Institute of Technology<br>
            </span></b><span style="font-family:&quot;Times New
            Roman&quot;,serif">o: (585) 475-7886 | f: (585) 475-7520</span><b><span
              style="font-family:&quot;Times New Roman&quot;,serif"><o:p></o:p></span></b></p>
        <p class="MsoNormal"><u><span style="font-family:&quot;Times New
              Roman&quot;,serif;color:blue"><a
                href="mailto:srcdco@rit.edu" moz-do-not-send="true"><span
                  style="color:blue">srcdco@rit.edu</span></a></span></u><span
            style="font-family:&quot;Times New Roman&quot;,serif"> | c:
          </span><span style="font-family:&quot;Times New
            Roman&quot;,serif">(585) </span><span
            style="font-family:&quot;Times New Roman&quot;,serif">339-8659
            <br>
            <br>
            <o:p></o:p></span></p>
        <p class="MsoNormal"><b><span
              style="font-size:8.0pt;font-family:&quot;Times New
              Roman&quot;,serif" lang="EN">CONFIDENTIALITY NOTE</span></b><span
            style="font-size:8.0pt;font-family:&quot;Times New
            Roman&quot;,serif" lang="EN">: The information transmitted,
            including attachments, is intended only for the person(s) or
            entity to which it is addressed and may contain confidential
            and/or privileged material. Any review, retransmission,
            dissemination or other use of, or taking of any action in
            reliance upon this information by persons or entities other
            than the intended recipient is prohibited. If you received
            this in error, please contact the sender and destroy any
            copies of this information.</span><span
            style="font-size:8.0pt;font-family:&quot;Times New
            Roman&quot;,serif"><o:p></o:p></span></p>
        <p class="MsoNormal"><o:p> </o:p></p>
      </div>
    </blockquote>
    <p>Hi Scott, <br>
    </p>
    <p>That is probably a bug. Automatic extension usually prioritizes
      shared pool over buffer cache, and, unless you've run out of the
      shared memory altogether, you shouldn't ever run out of shared
      pool. However, the palliative measure would be to set
      SHARED_POOL_SIZE in the SPFILE and restart the instance. That
      parameter is usually not set, but if it is, it determines the
      minimal size of the shared pool.</p>
    <p>Also, it would be nice to know which version of Oracle are you
      using. You have obviously hit a border case and different versions
      might handle it differently.</p>
    <p>Regards<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>
--
http://www.freelists.org/webpage/oracle-l