Re: way to make sql statement stay on cache (SGA) logner?
Date: Sun, 31 Jan 2010 20:39:11 -0800 (PST)
Message-ID: <146474.36864.qm_at_web32006.mail.mud.yahoo.com>
I do love this feature and that Oracle now does a lot of this, (for us DBA's who've been around a little longer...:)) automatically now, but, BUT...
Before you keep anything, please take the time to ensure they are the right things to keep... (psst, Surachart, that certain place we've been talking about, next time you should ask them about a certain developer in the UK that brought an entire production system to a standstill because he decided to keep EVERYTHING after he'd repeatedly been taken through both the keep/pin feature and the dangers of over-doing it! :))
Different environments/versions will require different edits to this script, (depending on how many average statements are executed, etc...), but it's a good starting point to review what might be best to keep and unkeep...
select decode(kept, 'YES', 'unkeep', 'keep') sort0,
type sort1,
owner sort2,
name sort3,
'exec dbms_shared_pool.' ||
decode(kept, 'YES', 'unkeep', 'keep') || '(''' ||
owner || '.' || name || ''',''' ||
decode(type, 'TYPE', 'T',
'TRIGGER', 'R',
'SEQUENCE', 'Q', 'P') || ''');' text
from sys.v_$db_object_cache
where ((executions >= 100 and kept = 'NO')
or (executions < 50 and kept = 'YES'))
and type in ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TYPE',
'TRIGGER','SEQUENCE')
union
select distinct decode(o.kept, 'YES', 'unkeep', 'keep') sort0,
o.type sort1,
o.owner sort2,
o.name sort3,
'exec dbms_shared_pool.' ||
decode(o.kept, 'YES', 'unkeep', 'keep') || '(''' ||
a.address || ', ' || a.hash_value || ''');' text
from sys.v_$db_object_cache o,
sys.v_$sqlarea a
where ((o.executions >= 100 and o.kept = 'NO')
or (o.executions < 50 and o.kept = 'YES'))
and o.type in ('CURSOR', 'INVALID TYPE') and a.sql_text = o.name
order by 1 desc, 2 asc, 3 asc, 4 asc;
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
- On Sun, 1/31/10, Surachart Opun <surachart_at_gmail.com> wrote:
From: Surachart Opun <surachart_at_gmail.com>
Subject: Re: way to make sql statement stay on cache (SGA) logner?
To: mccdba1_at_yahoo.com
Cc: oracle-l_at_freelists.org, oracle-db-l_at_groups.ittoolbox.com
Date: Sunday, January 31, 2010, 6:48 PM
If that is SQL Statement, do it to be Procedure and pin in shared pool.
You can find out on Oracle Docs by search "pin sql"
DBMS_SHARED_POOL.KEEP to pin your objects. This procedure ensures that your system does not run out of shared memory before the objects are loaded. By pinning the objects early in the life of the instance, you prevent memory fragmentation that could result from pinning a large portion of memory in the middle of the shared pool.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_shpool.htm
some example: http://surachartopun.com/2008/02/oracle-pinnig-objects.html
Good Luck
Surachart Opun
http://surachartopun.com
On Mon, Feb 1, 2010 at 6:17 AM, dba1 mcc <mccdba1_at_yahoo.com> wrote:
We have ORACLE 10GR2 on UNIX server and some of SQL statements run much longer on first after that it only run few seconds. I know the reason first time SQL statements run need parse and other things, after that SQL statement on cache (SGA) and it will run shorter. But eventually SQL statement still will be age out from SGA.
Does there has way to make SQL statement stay longer on cache (SGA)?
Thanks.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 31 2010 - 22:39:11 CST