Re: SQL High version count because of too many varchar2 columns
- From: Yong Huang <yong321@xxxxxxxxx>
- To: nigel.antell@xxxxxxxxxxxxxxxxxxxxxxxx
- Date: Tue, 18 Sep 2012 12:59:35 -0700 (PDT)
> Just to clear things up about the event 10503 - it _does_ work at session
> level.
Nigel,
I can prove it *does* work on my Oracle 11.2.0.3.0 running on Linux 64-bit.
But I must set it in spfile (or pfile) and bounce the instance. Otherwise,
the event appears to be set in the session and can be seen in "oradebug
eventdump session", but the test result is negative and "oradebug dumpvar pga
kxsusrgl" for the process shows zero. As you said, I probably need a patch to
make it work with "alter session". Currently the patch is only available for
AIX.
Here's my test. Set event='10503 trace name context forever, level 4000' in
pfile and bounce.
create table testbind (x varchar2(4000));
var s varchar2(10)
exec :s := 'hello'
select * from testbind where x = :s;
--without event 10503 with level greater than 40, the following would create a
new child cursor
var s varchar2(40)
exec :s := 'hello'
select * from testbind where x = :s;
-- only one child:
SQL> select child_address from v$sql where sql_text = 'select * from testbind
where x = :s';
CHILD_ADDRESS
----------------
0000000083BEA8D8
--max_length is 4000:
SQL> select * from v$sql_bind_metadata where address = '0000000083BEA8D8';
ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ----------
------------------------------
0000000083BEA8D8 1 1 4000 0 S
So, it works. Not working is probably just past memory, and maybe a terrible
one (CPU spike etc).
Yong Huang
--
//www.freelists.org/webpage/oracle-l
Other related posts:
