| Bind variable for date type takes longer time in a query [message #635088] |
Fri, 20 March 2015 02:02  |
 |
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  |
Lalit Kumar B
Messages: 3174
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 #635389 is a reply to message #635133] |
Sun, 29 March 2015 07:53 |
 |
Kevin Meade
Messages: 2103
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
