Home » RDBMS Server » Performance Tuning » Bind variable issue (Oracle 9i)
Bind variable issue [message #583143] Fri, 26 April 2013 05:44 Go to next message
savugar
Messages: 33
Registered: February 2012
Member
Hi,

The below query is running slowly from today morning. When I check this query, I noticed that it is not using bind variables because both parse_calls and executions values are same. What change I have to do to utilize bind variable in this query?

SELECT * FROM ( SELECT a.id FROM XML_TAG T1  WHERE  (  a.kln LIKE  :P16 )  ) WHERE ROWNUM <= 200


SQL> select sql_text,users_executing,FETCHES,FIRST_LOAD_TIME,EXECUTIONS,PARSE_CALLS,ROWS_PROCESSED,HASH_VALUE,PLAN_HASH_VALUE,LAST_LOAD_TIME from v$sql where
 hash_value=238154930;

SQL_TEXT                       USERS_EXECUTING    FETCHES FIRST_LOAD_TIME     EXECUTIONS PARSE_CALLS ROWS_PROCESSED HASH_VALUE PLAN_HASH_VALUE LAST_LOAD_TIME

------------------------------ --------------- ---------- ------------------- ---------- ----------- -------------- ---------- --------------- --------------
-----
SELECT * FROM ( SELECT                      10          0 2013-04-12/07:28:54         10          10      0  238154930      2831607955 2013-04-26/11:22:16
a.id FROM XML_TAG A
WHERE  (  a.kln LIKE
:P16 )  ) WHERE ROWNUM <= 200

SELECT * FROM ( SELECT                       2          6 2013-04-12/07:28:54          8           8     32  238154930      2831607955 2013-04-26/11:09:54
a.id FROM XML_TAG A
WHERE  (  a.kln LIKE
:P16 )  ) WHERE ROWNUM <= 200

[Updated on: Fri, 26 April 2013 05:49]

Report message to a moderator

Re: Bind variable issue [message #583144 is a reply to message #583143] Fri, 26 April 2013 05:48 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
Quote:

SELECT * FROM ( SELECT a.id FROM XML_TAG T1 WHERE ( a.kln LIKE :P16 ) ) WHERE ROWNUM <= 200
This is not valid SQL. To what does the alias a refer?
Re: Bind variable issue [message #583145 is a reply to message #583143] Fri, 26 April 2013 05:49 Go to previous messageGo to next message
cookiemonster
Messages: 10977
Registered: September 2008
Location: Rainy Manchester
Senior Member
What do you think :p16 is if not a bind variable?
Re: Bind variable issue [message #583146 is a reply to message #583144] Fri, 26 April 2013 05:50 Go to previous messageGo to next message
savugar
Messages: 33
Registered: February 2012
Member
Sorry. Some typo mistake

This is the actual query
SELECT * FROM ( SELECT a.id FROM XML_TAG A  WHERE  (  a.kln LIKE  :P16 )  ) WHERE ROWNUM <= 200

Re: Bind variable issue [message #583147 is a reply to message #583146] Fri, 26 April 2013 05:51 Go to previous messageGo to next message
savugar
Messages: 33
Registered: February 2012
Member
here is P16 is declared as a bind variable. But it is not getting used as bind variable
Re: Bind variable issue [message #583150 is a reply to message #583147] Fri, 26 April 2013 05:56 Go to previous messageGo to next message
cookiemonster
Messages: 10977
Registered: September 2008
Location: Rainy Manchester
Senior Member
Of course it is, the query would error out otherwise.
I think you need to read up on parsing and understand the difference between hard parses and soft parses.
Bind variables avoid hard parses, they can also help avoid soft parses but that's not automatic and depends on the code running the query.

Also I think your query needs an order by unless you really want 200 random rows back.
Re: Bind variable issue [message #583154 is a reply to message #583150] Fri, 26 April 2013 06:05 Go to previous messageGo to next message
savugar
Messages: 33
Registered: February 2012
Member
Here bind variable declared to get the benefit of soft parse. But it is doing hard parse. That's why I am seeing the same value in both executions and parse_calls. In this case, what changes needs to be done to do soft parse?
Re: Bind variable issue [message #583157 is a reply to message #583154] Fri, 26 April 2013 06:15 Go to previous message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
Quote:
But it is doing hard parse. That's why I am seeing the same value in both executions and parse_calls.
No it is not doing a hard parse, it is doing a soft parse for each execution. If you want to reduce the soft parsing, set session_cached_cursors to a value greater than zero and then if a session soft parses the stament three times the cursoe will be cached in its PGA. Then you will see the figure for parse_calls stabilize. But each session has to do this.
Previous Topic: v$sql_plan.object_owner incorrect?
Next Topic: How to Improve query performance
Goto Forum:
  


Current Time: Tue Sep 30 09:33:23 CDT 2014

Total time taken to generate the page: 0.06096 seconds