Thank you Martin. I don't know why oracle design it like this. Maybe at the very beginning it's for saving space in shared pool. But now the server has much more memory, a few Mb or Gb should not be a problem. And all these versions takes more memory than one version with max variable length. The real problem is large number of versions can lead to mutex contention. :( On Wednesday, September 12, 2012, Martin Klier wrote: > Hi Eagle Fan, > > this is a known issue. All solutions I know are an application approach: > Avoid this length changes of the bind variables, or pad them with > blanks. The last one is ugly or useless, but that's all you can do. > > "Technical background: The database does adjust the length of CHAR bind > buffers to preset sizes. There are > four steps: 32, 128, 2000 and 4000 bytes. So if we are execute a > statement with a bind value of 10 bytes, > the buffer will be 32 bytes. Do we re-execute it with 52 bytes in the > bind variable, the previously created > child cursor cannot be reused, and will be recreated with a bind buffer > of 128 bytes. The system view > v$sql_shared_cursor indicates this invalidated child cursor as > BIND_LENGTH_UPGRADEABLE." > > I've been speaking about this problem and the impacts of many SQL > versions at IOUG 2012 and nobody in the audience had a better solution, > I'm afraid. > If you are interested in the presentation or paper, have a look here: > > http://www.usn-it.de/index.php/2012/04/26/ioug-2012-presentation-resolving-child-cursor-issues-resulting-in-mutex-waits/ > > If you have further questions, just answer here. > > Best regards > Martin > > Eagle Fan schrieb: > > Hi: > > We have a table which has 17 varchar2 columns. Each varchar2 column has > > different bind variable ranges. > > > > for example varchar2(4000) has 4 ranges: 0-32,33-128,129-2000,2001-4001. > if > > the bind variables' length is in different ranges, it will create a new > > version! > > > > With 17 varchar2 columns, we have more than 3000 versions! > > > > We are using java code, if there any method to make oracle bind all > > different variables with the max length? > > > > Thanks. > > > > > > -- > Usn's IT Blog for Linux, Oracle, Asterisk > http://www.usn-it.de > > -- Eagle Fan (www.dbafan.com) -- //www.freelists.org/webpage/oracle-l