60000 bind variables?! Maybe it’s time to use a temporary table instead…

Tanel Poder

2009/02/25

I just noticed a bug 8277300 filed in Metalink with following description:

ORA-7445[XTYQBCB] OCCURS DURING EXECUTING SQL THAT USES 60000 BIND VARIABLES.

Wow! That’s about 100 times more bind variables in a single query than what I’ve seen in past. And I thought that query was bad!!! :)

I suspect this is a massive IN list passed to a query. Maybe it’s time to use a temporary table or a collection for passing in the IN values instead?

[Comments]
Click on Tweet to comment or ask a question! Keep the "via @tanelpoder" in the tweet text to notify me.
NB! Check out my 2019 online training classes here! Practical Linux Performance & Application Troubleshooting training (new), Advanced Oracle SQL Tuning training, Advanced Oracle Troubleshooting training. In addition to the online classes, all attendees will receive personal downloadable video recordings too!