Re: Pinning cursors knowing the hash_values and SQL's ahead of time
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: saibabu_d@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 27 Dec 2006 19:25:05 -0800 (PST)
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
--
//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:
