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

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Tue, 26 Dec 2006 23:30:32 -0800 (PST)
Message-ID: <20061227073032.32955.qmail@web56015.mail.re3.yahoo.com>

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

Received on Wed Dec 27 2006 - 01:30:32 CST