Help - Search - Members - Calendar
Full Version: Query Caching
Oracle DBA Forums > Oracle > Oracle Forum
sandhya
Hi,
Is there any way to ensure that a particular query always stays in the cache?
dave
pin it in the shared pool

but think about why you want to, if you use it often enough it will be in the cache anyway

if it is used infrequently then a parse isn;t goign to add much time to the execution time
burleson
Hi,

>> ensure that a particular query always stays in the cache?

1 - Put the SQL inside a stored procedure
2 - Make a package with the stored procedure
3 - Pin it in the SGA cache with dbms_shared_pool.keep

Please read for details: http://www.dba-oracle.com/art_proc.htm
rastl
May be an OUTLINE would be a solution for you instead of pinning cursors for predefining execution plans and avoiding frequent costly optimizations.
@burleson
Will pinning a SP really also pin cursors build from running it ? Was not able to test it so far.
Best Regards,
Rainer Stenzel
burleson
Hi Ranier,

>> Will pinning a SP really also pin cursors build from running it ?

I don't know, that's an excellent question!

I found this, titled how to pin a cursor:

CODE
conn / as sysdba

GRANT select ON gv_$open_cursor TO uwclass;

conn uwclass/uwclass

-- SQL statement to load cursor into the shared pool
VARIABLE x REFCURSOR

BEGIN
   OPEN :x for
   SELECT *
   FROM all_tables;
END;
/

--Determine address and hash value of the SQL statement
SELECT address, hash_value
FROM gv$open_cursor
WHERE sql_text LIKE '%ALL_TABLES%';

-- substitute your query results for mine, below.

exec sys.dbms_shared_pool.keep('1C5B28DC, 3958201300', 'C');

conn / as sysdba

SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND TYPE = 'CURSOR';


******************************************
>> Was not able to test it so far.

The best way to test it would be to run the queries and then flood the library cache with new SQL and see it it stays!

sandhya
Thanks for the reply..

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.