Re: SQL High version count because of too many varchar2 columns
- From: Martin Klier <usn@xxxxxxxxx>
- To: eagle.f@xxxxxxxxx
- Date: Wed, 12 Sep 2012 09:56:32 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
