RE: SQL High version count because of too many varchar2 columns

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 12 Sep 2012 08:49:55 -0400
Message-ID: <008301cd90e5$1cecbd80$56c63880$_at_rsiz.com>

Notice that the cpu cost of version control may vary significantly from language to language and amongst different techniques.

Initializing the bind var from a constant *may* be a single memcpy depending on the language implementation, then replacing the front end of the buffer with the actual value *may* also be a single memcpy. Your mileage may vary.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Klier
Sent: Wednesday, September 12, 2012 3:57 AM To: eagle.f_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: SQL High version count because of too many varchar2 columns

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-c hild-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

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 12 2012 - 07:49:55 CDT