sql parsing...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: sql parsing...

  1. #1
    Join Date
    Jan 2002
    Posts
    148
    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sounds like you need to investigate bind variables.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Feb 2001
    Posts
    290
    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,

    Madhu Reddy
    xdollor@yahoo.com

  4. #4
    Join Date
    Jan 2002
    Posts
    148
    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.

  5. #5
    Join Date
    Feb 2001
    Posts
    290
    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'

    Madhu Reddy
    xdollor@yahoo.com

  6. #6
    Join Date
    Jan 2002
    Posts
    148
    Madhu - Thanx Again !!!

    Jeff : I apologize for typomistake.

    Jr.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width