Re: way to make sql statement stay on cache (SGA) logner?

How do you know, that your sql runs faster, when it runs the second (third, and so on) time, because it doesn't have to be parsed, and not because data blocks requested by your sql are cached in buffer cache. Do you have statistics proving that time is spent on parsing and not on physical reads?

Igor Neyman

On Sun, Jan 31, 2010 at 8:48 PM, Surachart Opun <surachart_at_gmail.com> wrote:

> 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-l