Re: SQL High version count because of too many varchar2 columns
Date: Tue, 18 Sep 2012 12:59:35 -0700 (PDT)
Message-ID: <1347998375.72662.YahooMailClassic_at_web184805.mail.gq1.yahoo.com>
> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 18 2012 - 14:59:35 CDT