Re: High shared pool usage
From: Yong Huang <yong321_at_yahoo.com>
Date: Sun, 2 Oct 2011 12:08:23 -0700 (PDT)
Message-ID: <1317582503.77911.YahooMailClassic_at_web80601.mail.mud.yahoo.com>
> 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 ...
Date: Sun, 2 Oct 2011 12:08:23 -0700 (PDT)
Message-ID: <1317582503.77911.YahooMailClassic_at_web80601.mail.mud.yahoo.com>
> 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 Sun Oct 02 2011 - 14:08:23 CDT