What is the impact/limitation of oracle select with large number of bind variables?

Posted by Igal Serban on Stack Overflow See other posts from Stack Overflow or by Igal Serban
Published on 2010-04-23T21:53:33Z Indexed on 2010/04/23 22:03 UTC
Read the original article Hit count: 253

Filed under:

Oracle

|

oracle10g

We had our oracle server chocking during processing a select statement with close to 3500(!!) bind variables.

This select is, obviously, build dynamically by code that we can't change. During the execution of this select the db server went to 100% cpu usage and our system almost halted.

We know how to reproduce this problem. So we can prevent this specific condition. But I am wondering if there is a way to protect the db ( by configuration) from this type of problems.

© Stack Overflow or respective owner

Related posts about Oracle

E-Business Suite Technology Sessions at OpenWorld 2012

as seen on Oracle Blogs - Search for 'Oracle Blogs'
Oracle OpenWorld 2012 is almost here! We're looking forward to updating you on our products, strategy, and roadmaps. This year, the E-Business Suite Applications Technology Group (ATG) will participate in 25 speaker sessions, two Meet the Experts round-table discussions, five demoground booths and… >>> More
26 Days: Countdown to Oracle OpenWorld 2012

as seen on Oracle Blogs - Search for 'Oracle Blogs'
Welcome to our countdown to Oracle OpenWorld! Oracle OpenWorld 2012 is just around the corner. In less than 26 days, San Francisco will be invaded by an expected 50,000 people from all over the world. Here on the Oracle WebCenter team, we’ve all been working to help make the experience a great… >>> More
OTN ???? ?????? ???????

as seen on Oracle Blogs - Search for 'Oracle Blogs'
Database ?? Database ??????? Database ?????????? Java WebLogic Server/????????·???? SOA/BPM/????? ???????/???? ID??/?????? ?????EPM/BI EPM/BI ??????? EPM/BI ???? OS/??? ???? ????? MySQL Database ?? ???? ?? ????????? ??? ?? ORACLE MASTER… >>> More
SOA PARTNER COMMUNITY NEWSLETTER JULY 2012

as seen on Oracle Blogs - Search for 'Oracle Blogs'
SOA PARTNER COMMUNITY NEWSLETTER JULY 2012 Dear SOA partner community member To provide our community members the best of our knowledge, we want your feedback on our SOA Partner community. Thus we are organizing SOA Partner Community Survey 2012. We… >>> More
Oracle ADF Coverage at OOW

as seen on Oracle Blogs - Search for 'Oracle Blogs'
Below is the schedule for all ADF related sessions at a glance. Note the Meet and greet session added for Wednesday Octiber 3rd from 4.30 pm to 5:30. Oracle ADF and Fusion Development General Session Mon 1 Oct,… >>> More

Related posts about oracle10g

How to set _optimizer_search_limit and _optimizer_max_permutations in Oracle10g.

as seen on Server Fault - Search for 'Server Fault'
I am working on a product that must support both MSSQL and Oracle (10g and 11g). I have some very complex queries that seem to run without issue on MSSQL 2005/2008, but very, very slow with Oracle. The CPU on the oracle server skyrockets for long periods of time, and it seems like the optimizer… >>> More
Any suggestions on how to extract 6 million records from an oracle10g ?

as seen on Stack Overflow - Search for 'Stack Overflow'
I just want to give you a little background Need to write a PL-SQL which will extract 6 million record joining different tables and create a file of that. Need more suggestions, specifically on how to fetch these many records. As fetching these million of records on a single go can be a highly resource… >>> More
How to write a optimized code to fetch 6 million records from Oracle10G database and write them on a

as seen on Stack Overflow - Search for 'Stack Overflow'
What to use in oracle 10G to extract 6 million records and create a flat file of it ? >>> More
how to know the names of other users in oracle10g?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi guys,i want to know how can we know the other users of oracle 10g on the same system. Is there any querry for that? thank you!!! >>> More
Where is the C precompiler for Oracle10g located?

as seen on Stack Overflow - Search for 'Stack Overflow'
I am looking for a newer version of the Pro*C/C++ to upgrade my procui.exe 9.0.1.1.1. I downloaded the 10g client disk and when install I have the following options: instant client administrator runtime custom I dont seem to able to find the actual program other than just getting some… >>> More