Home » RDBMS Server » Performance Tuning » Use of Bind variable
Use of Bind variable [message #422373] Tue, 15 September 2009 23:16 Go to next message
sameertuladhar
Messages: 12
Registered: November 2008
Location: Nepal
Junior Member
Hello,

After spending sometime on studying what bind variable is, i have got some questions. If I am to use bind variable in a script which simply inserts values to one table from another table, where there are lots of joins(left joins), will it be useful to optimize the execution.

I may have to use the same scripts many times but each time the volume of data is different. Each time the table structure is same but the table name will be different. So I am trying to pass the table names through bind variables. Is it possible?

Lets say at a time I am working with a table of 1gb and the next time I may have to work with 100gb. Will the execution plan be same in both the case since the table structure is same or the execution plan will be different because of the volume of data. I am using oracle 10g.

Your suggestions will be really helpful.

Thanks,
Sameer
Re: Use of Bind variable [message #422410 is a reply to message #422373] Wed, 16 September 2009 04:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you have large queries that are run infrequently, then there would be little advantage to using bind variables, and you may well get a better execution plan by using hard coded variables.

Bind variables make the most different when you have quick queries that are executed frequently, where the time taken to parse the query is an appreciable percentage of the total execution time

Also, if there is a long time between executions of the query, then the old plan may well have aged out of the pool anyway.
Re: Use of Bind variable [message #422509 is a reply to message #422410] Wed, 16 September 2009 23:05 Go to previous message
sameertuladhar
Messages: 12
Registered: November 2008
Location: Nepal
Junior Member
Thanks for the quick suggestion, that was really helpful.

Usually the script runs for more than 4 hours when the dataset is really big(more than 50gb). When I tried to calculate the time to generate execution plan, I found it that the execution plan is calculated within few seconds. So in this regard there's no so much gain of time.

Like you said the queries are not run so frequently. So using of bind variable doesn't seem to be appropriate in this case. I will be looking at other options of optimization.

Thanks for your help.
Regards,
Sameer.

Previous Topic: EXECUTE IMMEDIATE 'set autotrace on'; ORA-00922: missing or invalid option
Next Topic: update statement issue
Goto Forum:
  


Current Time: Mon Dec 05 18:58:17 CST 2016

Total time taken to generate the page: 0.09727 seconds