Re: High shared pool usage
Date: Mon, 3 Oct 2011 08:44:38 +0300
Message-ID: <OF827CF8EF.C921F145-ONC225791E.001F06B0-C225791E.001F8DCA_at_seb.lt>
>What I usually do is keep running a DDL, such as grant select
to dba, on the said table during the period of those inserts. The memory used by the SQLs will be released.
funny enough :) Do you know pool mechanics behind that behaviour ?
>> insert into table t001; insert into table t002 ...
>That's actually quite common.
didn't know that :))) Sounds like some tool or method is behind it. Probably good for monitoring of ETL progress... A good way to torture oracle too.
brgds, Laimis N
Please consider the environment before printing this e-mail
From: Yong Huang <yong321_at_yahoo.com>
To: Laimutis.Nedzinskas_at_seb.lt
Cc: oracle-l_at_freelists.org
Date: 2011.10.02 22:08
Subject: Re: High shared pool usage
> I've seen a case when each insert of some ETL generated a new sql.
> There was no way to handle it with cursor_sharing options: the
> guys(developers) decided to use a new alias for each insert:
> insert into table t001; insert into table t002 ...
That's actually quite common. There's no good solution from the DBA side. What I usually do is keep running a DDL, such as grant select to dba, on the said table during the period of those inserts. The memory used by the SQLs will be released. 11g doesn't do that though, and dbms_shared_pool.purge on individual SQLs has to be used.
Yong Huang
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 03 2011 - 00:44:38 CDT