OCT-14

03-Nov

Bind Variable Graduation:  Oracle creates a child cursor if the length of the bind variable increases for an existing cursor.   There can be 4 groups based on the bind variable sizes up to 32 bytes, 33 – 128 bytes, 129 – 2000 bytes and above 2000 bytes.

 

Oracle 11.2 capture a new child creation reason in v$sql_shared_cursor under the column BIND_LENGTH_UPGRADABLE.

 

http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3059.htm#REFRN30254

 

However below issue mentioned dosen’t looks a due to a bind variable graduation rather it may due to bind variable peeking.

 

http://comments.gmane.org/gmane.comp.db.oracle.general/19841

 

To purge a shared cursor


SQL> exec dbms_dbms_shared_pool.purge(‘address,hash value’,’c’);

 

A good post on ACS – Adaptive cursor sharing

http://database.com.mk/wordpress/2010/05/03/adaptive-cursor-sharing/

 

 

You can get the bind variable sizes for each child cursor by


SELECT sq.child_number,mb.position,mb.max_length,mb.data_type,

FROM  V$SQL sq, V$SQL_BIND_METADATA mb

WHERE sq.sql_id = mb.sql_id AND

Sq.child_address = mb.address AND

Sq.sql_id=’&SQL_ID’

ORDER BY sq.child_number, mb.position;

 

Using bind variable in sqlplus

 


SQL> VARIABLE eid NUMBER;

SQL> EXECUTE :eid  := 45;

SQL> SELECT * FROM EMP where empid = :eid ;

 

01-Nov

 

Status of System Statistics can get

 


col pval2 for a30

SELECT  pname,pval1,pval2

From aux_stats$ WHERE sname='SYSSTATS_INFO';

 

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: