Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 5C74010034B362 for ; Tue, 30 Nov 2021 16:24:08 +0100 (CET) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 1AF124162B; Tue, 30 Nov 2021 15:24:06 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 01B203FDC7; Tue, 30 Nov 2021 15:24:06 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1638285846; bh=NNF5oW9xn2ZWVu1hCVo7SxERE/3A1WAA5TIw2B4/gI4=; h=From:Sender:Sender:From; b=fK4rhCQ6zBPXX1AP3p4qU1ZpOPgovw6cYD4Lh4Q9KtVNQgjHui+azV0ixTdQIKGMx 8uylfNIPWgkUjc1j5l7edqcaHxkdqO77izKnqnPxV0ZJkfyLDFaqAZkuaeiWhtDZRJ QiaZltpla+/ygZB9lGVDp9I3WbaNeeHtmNJmxFQ8= X-Virus-Scanned: by FreeLists at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id oR5skqzZ9dor; Tue, 30 Nov 2021 15:24:05 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 0B958400B0; Tue, 30 Nov 2021 15:24:03 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1638285844; bh=NNF5oW9xn2ZWVu1hCVo7SxERE/3A1WAA5TIw2B4/gI4=; h=From:Sender:Sender:From; b=pMyywu83jJ+BGHZCJVzZBqKscncrF2+wqMCt8wkgCYVYJ8qve1f4Waynlva4Ce0it hMyO1DcB4poJTlDQpgggV9tm9TPReLdVKwi6CKAKjADxHyan5qZbHVcs3L1eh91jL5 oaVFgoj7yTy745AhAgWwqV3aF1r1TpFFTECPiZ1M= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 30 Nov 2021 15:24:01 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 0DDAA3FDC7 for ; Tue, 30 Nov 2021 15:24:01 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20210112 header.b=lU58hH7i; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id UiB4M0zkW3V0 for ; Tue, 30 Nov 2021 15:24:01 +0000 (UTC) Received: from mail-qk1-f175.google.com (mail-qk1-f175.google.com [209.85.222.175]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 007EF3F96C for ; Tue, 30 Nov 2021 15:24:00 +0000 (UTC) Received: by mail-qk1-f175.google.com with SMTP id 193so27129298qkh.10 for ; Tue, 30 Nov 2021 07:24:00 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=MoZGc3z+M6/0mGNIzEaaKs81b7ielQsiSZG0Lu98mVQ=; b=uEODE49W5G1RFneK9gJ2QiE8wbEHLjF93CyvKfNtXP5HCGK7eTyde45+yBhBgL1I1J 8HT9kIZax+e7yinGDQwI4cIB17EEhkqXaBTdppZWpSkaYbruYtyWH7w3od5tqj5i44fL 5250a00faX4KbSm4B7fuXTv3OKU9r2eu+hAmJXvoUscvXMp1KuEDDZaA60VMts106rw6 biNoKSXyepOrODAAqrJp5Xx++jpO+77T2DZxPoqTWE0XhAQbt0W2y4mU7NcqPFf5Fms3 au0Ux+u1Nv/OWplupdiM/UCcSugYO36/TLoX1DBDrWfYmOk+VCfcPCHlM5jc1HUH4evU JKeA== X-Gm-Message-State: AOAM531TSkKQq5ZEG4TUdH92fqX3/FIUtJtKNr2ncJK+E3saqufGMeJX 7H6SyaPoq1IJRmb8ZLxqyg2O9wTVG4s= X-Google-Smtp-Source: ABdhPJxlj4A82QvTygvpsbvJ4PM6DVXOAde9tJ3pfaRBdNatCAx1SAp/Eb+yK+cva2WDGNQpLIZ3CQ== X-Received: by 2002:a05:620a:24cf:: with SMTP id m15mr38934664qkn.623.1638285840009; Tue, 30 Nov 2021 07:24:00 -0800 (PST) Received: from [192.168.2.100] (pool-72-79-19-93.nwrknj.east.verizon.net. [72.79.19.93]) by smtp.gmail.com with ESMTPSA id e17sm11233051qtw.18.2021.11.30.07.23.59 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 30 Nov 2021 07:23:59 -0800 (PST) Content-Type: multipart/alternative; boundary="------------Mg0O0hNnZxadHV5WLTYDqLXJ" Message-ID: <29c11f25-8e3f-793c-56f5-b0d829de9032@gmail.com> Date: Tue, 30 Nov 2021 10:23:58 -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: Scott Canaan , "oracle-l@freelists.org" References: <6a4532f37f5d48e4b1df80daeac8285b@ex04test91a.ad.rit.edu> <99338353-e6fd-4330-3e58-69cf875c5f77@gmail.com> <6e9947599525425b9a5e9d09b5f052cc@ex04test91a.ad.rit.edu> From: Mladen Gogala In-Reply-To: <6e9947599525425b9a5e9d09b5f052cc@ex04test91a.ad.rit.edu> X-archive-position: 81471 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: gogala.mladen@gmail.com Precedence: normal Reply-To: gogala.mladen@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --------------Mg0O0hNnZxadHV5WLTYDqLXJ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/30/21 07:21, Scott Canaan wrote: > > Mladen, > >    Sorry I left out the version of Oracle.  It is 19.12.  I did grow > the shared pool and bounce the instance.  The request is to find a way > to detect when it is going to run out before it does so the export (or > other job) doesn’t fail. > > *Scott Canaan ‘88** > */Sr Database Administrator > /Information & Technology Services > Finance & Administration ** > > *Rochester Institute of Technology > *o: (585) 475-7886 | f: (585) 475-7520** > > _srcdco@rit.edu _| c: (585) 339-8659 > Hi Scott, There is a fixed table V$SHARED_POOL_RESERVED. It has the several useful columns, among which are FREE_SPACE, AVG_FREE_SIZE,FREE_COUNT, MAX_FREE_SIZE and USED_SPACE. Free space in the shared pool is not contiguous, it is in chunks. If the application requires chunk larger than the largest one available, you will get 4031. The last time I got that, it was caused by BULK COLLECT INTO where I was careless with the WHERE condition. I would check V$DB_OBJECT_CACHE for the sharable memory hogs. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------Mg0O0hNnZxadHV5WLTYDqLXJ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 11/30/21 07:21, Scott Canaan wrote:

Mladen,

   Sorry I left out the version of Oracle.  It is 19.12.  I did grow the shared pool and bounce the instance.  The request is to find a way to detect when it is going to run out before it does so the export (or other job) doesn’t fail.

 

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration

Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520

srcdco@rit.edu | c: (585) 339-8659

Hi Scott,

There is a fixed table V$SHARED_POOL_RESERVED. It has the several useful columns, among which are FREE_SPACE, AVG_FREE_SIZE,FREE_COUNT, MAX_FREE_SIZE and USED_SPACE. Free space in the shared pool is not contiguous, it is in chunks. If the application requires chunk larger than the largest one available, you will get 4031.

The last time I got that, it was caused by BULK COLLECT INTO <pl/sql table> where I was careless with the WHERE condition. I would check V$DB_OBJECT_CACHE for the sharable memory hogs.

Regards


-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------Mg0O0hNnZxadHV5WLTYDqLXJ-- -- http://www.freelists.org/webpage/oracle-l