| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_yahoo.com> 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||';'
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
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||';'
HTH,
Sai
http://sai-oracle.blogspot.com
 |
 |