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

  • From: Eagle Fan <eagle.f@xxxxxxxxx>
  • To: Martin Klier <usn@xxxxxxxxx>
  • Date: Fri, 14 Sep 2012 15:25:31 +0800

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


Other related posts: