sql parsing...

[Jr]

hi,

i see an sql query "select * from table where sr='1020' " appearing number of times in v$sql and increasing continuously.

This sql is being requested by a web applicaton.

possible things i can think about is to let web application request for a procedure instead of a direct sql query with those literals.

Is this approach is the right way ??? Plz suggest...


thanx
Jr.

[marist89]

Sounds like you need to investigate bind variables.

[mrvajrala]

May be somebody can correct me , if i am wrong ...

Jr,
if you find the similar LITERAL statements in the Library Cache and getting excuted more frequently... Ofcourse they will be re-parsed every time( Hope i am not wrong here ) they are getting executed.

I think we have Three Options.

1. Possible chnage the application code to use the BIND variables.

2. if the step 1 is not possible for all Literal statements.
use CURSOR_SHARING=FORCE ( Here Oracle will reuse the literal statements and no parsing is allwowed here )

3. And Only you have couple of statements left and they are still literal statements , why cant we pin them..

Corrections are very much appreciated,

[Jr]

Thanx Jerr and MrVajrala,

Vajrala,

Point 1) is out of my reach as its a VC++, and dont know how to play and advice developer to set it for using bindvariables.

Point 2) It worked - thanx.

And just curious about the point 3) - How to pin sql statements?

Jr.

[mrvajrala]

All the above 3 points are jut my thoughts ....and i am expecting the same from other experienced guys too.

coming to your question...( 3rd point )..

We can pin the cursors( which are hadles to the SQL statements), by getting the ADDRESS,HASH_VALUE from the
v$SQLAREA view .

exec dbms_shared_pool.keep('ADDRESS,HASH_VALUE','C');

You can test this first before doing it on production , and quety the v$db_object_cache where KEPT='Y'

[Jr]

Madhu - Thanx Again !!!

Jeff : I apologize for typomistake.

Jr.