Home » RDBMS Server » Performance Tuning » query with hundreds of bind variables (Oracle database 11gR2
query with hundreds of bind variables [message #641201] Tue, 11 August 2015 17:13 Go to next message
Messages: 4
Registered: August 2015
Location: Mauritius
Junior Member
I am currently trying to analyze a performance issue for a customer where one program that usually took 30 minutes suddenly took 2 hours.
When digging through the awr reports, I found lots of queries that seems to have been generated by a tool, each query got more than 800 bind variables.
Moreover, this program is some kind of bad piece of code that cannot make proper use of oracle parallel queries on a multiprocessor server so the vendor tries to implement parallelization by spawning around 80 identical batch programs in parallel each with different ranges of data to process.
So the same or similar queries (with >800 bind variables) are launched in parallel over 80 oracle sessions.
I can see a very big wait on the "Cursor pin S wait on X" event , but the total wait is misleading because it is cumulated over all concurrent execution of the same program (more than 11000s of wait during a 15min interval on a 2 cores SMT2 POWER6 server).
Cursor_sharing is not set (it defaults to EXACT), I think about setting it to "FORCE" in order to try to reduce the "Cursor pin S wait on X", I also plan to reduce the number of concurrent parallel programs to 20 (10 per core instead of 40 per core currently).
Do you guys have any hints , and what is your opinion on building and executing queries with such a big number of bind variables ? isn't it going to increase the parse time as the matching of queries with different child cursors could become lengthy because of the big number of variables ?

Thanks in advance for your comments

[Updated on: Tue, 11 August 2015 17:18]

Report message to a moderator

Re: query with hundreds of bind variables [message #641203 is a reply to message #641201] Tue, 11 August 2015 18:02 Go to previous messageGo to next message
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
one way to gain visibility into a problem such as you describe is to do as below
then process the trace file using tkprof; which you can even enable EXPLAIN PLAN.
The results will show the duration of every SQL execute by this program.
You can then dig deeper into the longer running SQL to see why & possibly how to improve them.
Unless & until you know where time is actually being spent, you won't know what will give the biggest opportunity for improvement.
Re: query with hundreds of bind variables [message #641207 is a reply to message #641201] Tue, 11 August 2015 21:55 Go to previous message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
Your understanding of cursor sharing is not correct. Setting cursor_sharing=force has no effect if you are already using bind variables.

You need to investigate why the job is taking two hours instead of thirty minutes. Generate AWR reports covering a period when the job ran in 30 mins and another for when it ran in two hours. Post them both here. Don't waste time - unless you have changed the snapshot retention from default, the snaps will be kept for only eight days.
Previous Topic: Help to tune the query
Next Topic: statistics update
Goto Forum:

Current Time: Sat Feb 24 09:12:07 CST 2018

Total time taken to generate the page: 0.08317 seconds