Re: way to make sql statement stay on cache (SGA) logner?
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: kjped1313@xxxxxxxxx
- Date: Wed, 24 Mar 2010 11:50:46 -0400
I'm in the midst of troubleshooting some performance issues myself and came
across this post. I just learned that part of an application's code
actually caches about 30 smaller tables when the system comes up. These 30
tables represent about 1798 8k blocks and are mostly static but they happen
to be the most frequently queried tables, system wide(by a large margin).
In reading some of Kellyn's warnings, how can I check to see if the fact
that the tables are cached are causing issues? For what its worth, I'm
already in a memory-starved situation.
On Mon, Feb 1, 2010 at 2:00 PM, Kellyn Pedersen <kjped1313@xxxxxxxxx> wrote:
> I'm trying to determine the exact question here, but I'm assuming that
> there needs clarification of the terms "pin" and "keep" or that you simply
> want to know table/index keep vs. other objects...
>
> I think this might help:
>
> "To use the DBMS_SHARED_POOL package to pin a SQL or PL/SQL area, complete
> the following steps.
>
> 1. Decide which *packages/cursors you would like pinned in memory.*
> 2. Start up the database.
> 3. Make the call to *DBMS_SHARED_POOL.KEEP to pin it.* "
>
> If you are looking for simply how to keep a table in memory:
>
> *alter TABLE USER.TABLE_NAME storage (buffer_pool keep);*
>
>
>
> Again, be very careful to keep the RIGHT things in memory. I've seen two
> developers who've used these features to make a database environment
> unusable, (so happy I was no longer at these company's when it occurred, but
> felt bad for previous coworkers who had to clean up the mess left by the
> folks who went for "too much of a good thing is rarely a good thing"
> syndrome!)
>
>
> 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 *Mon, 2/1/10, dba1 mcc <mccdba1@xxxxxxxxx>* wrote:
>
>
> From: dba1 mcc <mccdba1@xxxxxxxxx>
>
> Subject: Re: way to make sql statement stay on cache (SGA) logner?
> To: surachart@xxxxxxxxx, kjped1313@xxxxxxxxx
>
> Cc: oracle-l@xxxxxxxxxxxxx, oracle-db-l@xxxxxxxxxxxxxxxxxxxx
> Date: Monday, February 1, 2010, 10:45 AM
>
> does there has way using DBMS_SHARED_POOL.KEEP to pine a table?
>
>
>
> --- On Sun, 1/31/10, Kellyn Pedersen
> <kjped1313@xxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=kjped1313@xxxxxxxxx>>
> wrote:
>
> > From: Kellyn Pedersen
> > <kjped1313@xxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=kjped1313@xxxxxxxxx>
> >
>
> > Subject: Re: way to make sql statement stay on cache (SGA) logner?
> > To:
> > mccdba1@xxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=mccdba1@xxxxxxxxx>,
> surachart@xxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=surachart@xxxxxxxxx>
>
> > Cc:
> > oracle-l@xxxxxxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=oracle-l@xxxxxxxxxxxxx>,
> oracle-db-l@xxxxxxxxxxxxxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=oracle-db-l@xxxxxxxxxxxxxxxxxxxx>
> > Date: Sunday, January 31, 2010, 11:39 PM
>
> > 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@xxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=surachart@xxxxxxxxx>>
> wrote:
> >
> >
> > From: Surachart Opun
> > <surachart@xxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=surachart@xxxxxxxxx>
> >
> > Subject: Re: way to make sql statement stay on cache (SGA)
> > logner?
> > To:
> > mccdba1@xxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=mccdba1@xxxxxxxxx>
> > Cc:
> > oracle-l@xxxxxxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=oracle-l@xxxxxxxxxxxxx>
> ,
> > oracle-db-l@xxxxxxxxxxxxxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=oracle-db-l@xxxxxxxxxxxxxxxxxxxx>
> > 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@xxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=mccdba1@xxxxxxxxx>
> >
> > 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.
> >
> >
> >
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
>
>
Other related posts:
