Home » RDBMS Server » Performance Tuning » Bind variable for date type takes longer time in a query (toad 10g)
Bind variable for date type takes longer time in a query [message #635088] Fri, 20 March 2015 02:02 Go to next message
nciteamo
Messages: 27
Registered: October 2014
Junior Member
Hii All

i have a query using bind variables, lets say like this below:

variable vName varchar2
variable vDateFrom varchar2
variable vDateTo varchar2

select * from A
where name = :vName
and date between to_date(:vDateFrom) and to_date(:vDateTo)

that query takes very long time for giving data results
but, if i dont use bind variables for the date column, it will make it better performance
exmaple
select * from A
where name = :vName
and date between '1-jan-2012' and '31-jan-2012'

the actual table has more than 150.000.000 records, and i thought with using varibles will improve performance, i think i went wrong when using bind variable for date type, so please can anyone let me know to use bind variable for date type properly
?, Big Thanks

regards,

nciteamo


Lalit : Moved the topic to Performance tuning forum.

[Updated on: Tue, 24 March 2015 07:01] by Moderator

Report message to a moderator

Re: Bind variable for date type takes longer time in a query [message #635090 is a reply to message #635088] Fri, 20 March 2015 02:22 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web
Senior Member
nciteamo wrote on Fri, 20 March 2015 12:32

and date between '1-jan-2012' and '31-jan-2012'


This is also not good. '1-jan-2012' is a string, not a DATE. Oracle might use implicit conversion to get it working, which is an overhead.

Regarding the performance issue using bind variables, look at bind peeking which was in 9i till 10gR2. You need to post the required details which are addressed in the article.
Re: Bind variable for date type takes longer time in a query [message #635133 is a reply to message #635090] Sun, 22 March 2015 07:43 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Compare/post both explains.
Probably - using hard coded values changes explain plan.

HTH
Re: Bind variable for date type takes longer time in a query [message #635389 is a reply to message #635133] Sun, 29 March 2015 07:53 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
nciteamo, please find attached the following documents to help you.

I have included a cutout from Chapter 6 of my book on SQL Tuning which describes the basics of statistics. Though I do not talk about bind variables in my book at all, this section will help you to understand why your query may be running long when using bind variables (think AVERAGE NDV / histograms, and that kind of thing).

I have also included the typical items I provide for SQL Tuning posts, which are Chapter 1: Driving Table and Join Order from the book, the scripts from the book, and a document that details the information you need to tune SQL or have someone help you tune SQL. These documents are all free and free to share so please do so.

If you like what you read, you can learn more from the book.

Please let me point out however, there is no requirement that you have to buy the book in order to use these scripts or share these documents. These documents in this post are free to use and distribute. It is my pleasure to be able to share on OraFAQ.

Kevin

[Updated on: Sun, 29 March 2015 07:54]

Report message to a moderator

Previous Topic: Image File Size
Next Topic: ORA-00439: feature not enabled: Bit-mapped indexes
Goto Forum:
  


Current Time: Mon Oct 22 11:07:17 CDT 2018