| query with hundreds of bind variables [message #641201] |
Tue, 11 August 2015 17:13  |
 |
fplatel
Messages: 4
Registered: August 2015
Location: Mauritius
|
Junior Member |
|
|
Hello
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
Fabrice
[Updated on: Tue, 11 August 2015 17:18] Report message to a moderator |
|
|
|
|
|
| Re: query with hundreds of bind variables [message #641207 is a reply to message #641201] |
Tue, 11 August 2015 21:55  |
John Watson
Messages: 8964
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
