OraFAQ Forum: Performance Tuning » Bind variable issue

Home » RDBMS Server » Performance Tuning » Bind variable issue (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Bind variable issue [message #583143]

Fri, 26 April 2013 05:44

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

John Watson
Messages: 8960
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?

Report message to a moderator

Re: Bind variable issue [message #583145 is a reply to message #583143]

Fri, 26 April 2013 05:49

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

What do you think :p16 is if not a bind variable?

Report message to a moderator

Re: Bind variable issue [message #583146 is a reply to message #583144]

Fri, 26 April 2013 05:50

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

Report message to a moderator

Re: Bind variable issue [message #583147 is a reply to message #583146]

Fri, 26 April 2013 05:51

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

Report message to a moderator

Re: Bind variable issue [message #583150 is a reply to message #583147]

Fri, 26 April 2013 05:56

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Bind variable issue [message #583154 is a reply to message #583150]

Fri, 26 April 2013 06:05

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?

Report message to a moderator

Re: Bind variable issue [message #583157 is a reply to message #583154]

Fri, 26 April 2013 06:15

John Watson
Messages: 8960
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.

Report message to a moderator

Previous Topic:	v$sql_plan.object_owner incorrect?
Next Topic:	How to Improve query performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 16:13:48 CST 2024