Re: Pinning cursors knowing the hash_values and SQL's ahead of time

I think if the sql is part of pl/sql (other then dynamic) then better to pin 
the pl/sql itself. 
  If you still need to pin sql then it can be made part of startup. Of course 
you will need to run it and bring it to v$sql and then get address, hash_value. 
Like pinning of pl/sql objects.  If you pin lots of sql later after startup and 
keep doing it then it might not be so beneficial. Thanks

  
Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx> wrote:
  You can explicitly pin a sql cursor only if it is in the library cache. You 
need to get hash_value and sql_address of that cursor to be able to pin it.

exec dbms_shared_pool.keep('sql_address, hash_value', 'C');

That will pin heap 0 and heap 6 of that cursor until you unpin it. Make sure to 
set session_cached_curosrs parameter also to further reduce impact of parsing.

As demonstrated below, you can run interested sql's queries from the pl/sql 
block and they will have the same hash values when your execute them from your 
app, only caveat would be Pl/sql makes them uppercase, unless you use execute 
immediate, and your bind variables may take a different name and hence 
different hash value.

23:21:28 SQL> begin
23:21:28 2 for i in (select 1 from dual) loop
23:21:28 3 null;
23:21:28 4 end loop;
23:21:28 5 end;
23:21:28 6 /

PL/SQL procedure successfully completed.

23:21:28 SQL> select hash_value, address, sql_text||';' from v$sql where 
sql_text like 'SELECT 1 FROM DUAL%';

HASH_VALUE ADDRESS
---------- ----------------
SQL_TEXT||';'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
32127143 00000003F112ADB8
SELECT 1 FROM DUAL;


23:22:08 SQL> alter system flush shared_pool;

System altered.

23:22:17 SQL> select hash_value, address, sql_text||';' from v$sql where 
sql_text like 'SELECT 1 FROM DUAL%';

no rows selected

23:22:25 SQL> SELECT 1 FROM DUAL;

1
----------
1

23:22:32 SQL> select hash_value, address, sql_text||';' from v$sql where 
sql_text like 'SELECT 1 FROM DUAL%';

HASH_VALUE ADDRESS
---------- ----------------
SQL_TEXT||';'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
32127143 00000003F112ADB8
SELECT 1 FROM DUAL;


23:22:41 SQL> show rel
release 1002000100

HTH,
Sai
http://sai-oracle.blogspot.com



__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--
http://www.freelists.org/webpage/oracle-l




 __________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com

Other related posts: