V$SQLAREA question about bind variables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: V$SQLAREA question about bind variables

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    V$SQLAREA question about bind variables

    Does the SQL_TEXT column show the statement as sent to the engine? What I'm after is: is this column a good method finding out whether or not user statements use bind variables?
    If not - what is?

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Yes, you can use sql_text...

    If literals are used, parsing will be more, so you can use the following code to identify SQL statements receiving a lot of parse calls...

    Code:
    select rpad(sql_text,300), parse_calls, executions from v$sqlarea
    where parse_calls > 100 and executions < 2*parse_calls
    HTH.

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Yes, thanks.
    But - (a)if Bind variables are used in the original statement, do they appear in SQL_TEXT column?

    e.g.
    SELECT col1, col2, col3 FROM tableA WHERE col2 = :bindvar

    or (b) does SQL_TEXT show the statement after the engine has resolved the bind variables into a 'literal' statement.

    e.g.
    SELECT col1, col2, col3 FROM tableA WHERE col2 = 10089

    If (b)is the PARSE statistic the only way to discover if bind variables are being used?

  4. #4
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    The bind variables from the original statement are shown, not the value.

    HTH
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Thanks - thats the answer I wanted.

    I'm looking at some statements passed into the Ora 8.1.5 DB from a VB6 application using OLEDB.

    The statements appear with double quotes around each object name. Is this just the way the VB app constructs the string and send it to the database?

    e.g.
    SELECT "ORD_NO","ORD_ID","TRACKING_NO","NO_CARTONS" FROM "SCHEMA_OWNER"."SHIPMENTS"

  6. #6
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Yes, I've come across several applications which do the same.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

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