关于varchar2 和 ora-600 [17059]

ihekoko · 发表于 2007-3-6 12:04

数据库出现的问题和相关metalink文章的原文在这里
http://www.itpub.net/731071.html

在看过相关文章然后结合自己遇到的情况
头脑中终于有了一点清晰的感觉

在我一直的影响中只要sql进行了良好的bind variable 就应该象许多文档所说的那样是可以做到共享的共享自己的存储共享自己的执行计划  但事实并没有那么简单

做个实验

首先建一个存储过程目的就是为了方便修改变量的实际长度

CREATE OR REPLACE PROCEDURE P_Mytest
AS
l_col_1    VARCHAR2 (4000);
l_col_2    NUMBER (10);
l_col_3    VARCHAR2 (500);
l_col_4    VARCHAR2 (4000);
l_length_1 NUMBER (20);
l_length_3 NUMBER (20);
l_length_4 NUMBER (20);
BEGIN
l_col_2 := 20070305;

FOR i IN 1 .. 1
LOOP
   l_col_1 := l_col_1 || TO_CHAR (SYSDATE, 'yyyymmddhh24');
END LOOP;

FOR i IN 1 .. 1
LOOP
   l_col_3 := l_col_3 || TO_CHAR (SYSDATE, 'yyyymmddhh24');
END LOOP;

FOR i IN 1 .. 1
LOOP
   l_col_4 := l_col_4 || TO_CHAR (SYSDATE, 'yyyymmddhh24');
END LOOP;

SELECT LENGTH (l_col_1), LENGTH (l_col_3), LENGTH (l_col_4)
   INTO l_length_1, l_length_3, l_length_4
   FROM DUAL;

INSERT INTO test_varchar_1
      VALUES (l_col_1, l_col_2, l_col_3, l_col_4);

COMMIT;
END;
/

每execute一次此存储过程我们就修改下
loop的次数使得变量的实际长度有所变化
[PHP]
SQL> alter system flush shared_pool;

System altered.

SQL> set linesize 100
SQL> column cnt fomat 9
SP2-0158: unknown COLUMN option "fomat"
SQL> col cnt format 9
SQL> col text format a60
SQL> SELECT a.version_count cnt,substr(a.sql_text,1,60) text FROM v$sqlarea a WHERE upper(sql_text) LIKE 'INSERT INTO
RCHAR_1%';

no rows selected

SQL> execute p_mytest;

PL/SQL procedure successfully completed.

SQL> SELECT a.version_count cnt,substr(a.sql_text,1,60) text FROM v$sqlarea a WHERE upper(sql_text) LIKE 'INSERT INTO
RCHAR_1%';

CNT TEXT
--- ------------------------------------------------------------
  1 INSERT INTO TEST_VARCHAR_1 VALUES (:B4 , :B3 , :B2 , :B1 )

SQL>
[/PHP]
这时将第一个loop的循环次数改为4

FOR i IN 1 .. 4
LOOP
   l_col_1 := l_col_1 || TO_CHAR (SYSDATE, 'yyyymmddhh24');
END LOOP;

l_col_1的长度变为40bytes
[PHP]
SQL> execute p_mytest;

PL/SQL procedure successfully completed.

SQL> SELECT a.version_count cnt,substr(a.sql_text,1,60) text FROM v$sqlarea a WHERE upper(sql_text) LIKE 'INSERT INTO
RCHAR_1%';

CNT TEXT
--- ------------------------------------------------------------
  2 INSERT INTO TEST_VARCHAR_1 VALUES (:B4 , :B3 , :B2 , :B1 )

SQL>
[/PHP]
此时同一个sql在library中有了2个不同的version
再改高点
FOR i IN 1 .. 14
LOOP
   l_col_1 := l_col_1 || TO_CHAR (SYSDATE, 'yyyymmddhh24');
END LOOP;

l_col_1的长度变为140bytes
[PHP]
SQL> execute p_mytest;

PL/SQL procedure successfully completed.

SQL> SELECT a.version_count cnt,substr(a.sql_text,1,60) text FROM v$sqlarea a WHERE upper(sql_text) LIKE 'INSERT INTO
RCHAR_1%';

CNT TEXT
--- ------------------------------------------------------------
  3 INSERT INTO TEST_VARCHAR_1 VALUES (:B4 , :B3 , :B2 , :B1 )

SQL>
[/PHP]

现在我们把第一个循环改为原来的1次
l_col_1为10bytes

FOR i IN 1 .. 1
LOOP
   l_col_1 := l_col_1 || TO_CHAR (SYSDATE, 'yyyymmddhh24');
END LOOP;

改第二个循环为5次
FOR i IN 1 .. 5
LOOP
   l_col_3 := l_col_3 || TO_CHAR (SYSDATE, 'yyyymmddhh24');
END LOOP;
l_col_3为50bytes

[PHP]
SQL> execute p_mytest;

PL/SQL procedure successfully completed.

SQL> SELECT a.version_count cnt,substr(a.sql_text,1,60) text FROM v$sqlarea a WHERE upper(sql_text) LIKE 'INSERT INTO
RCHAR_1%';

CNT TEXT
--- ------------------------------------------------------------
  4 INSERT INTO TEST_VARCHAR_1 VALUES (:B4 , :B3 , :B2 , :B1 )
SQL>

[/PHP

l_col_1 10bytes
l_col_3  150bytes
[PHP]
SQL> execute p_mytest;

PL/SQL procedure successfully completed.

SQL> SELECT a.version_count cnt,substr(a.sql_text,1,60) text FROM v$sqlarea a WHERE upper(sql_text) LIKE 'INSERT INTO TEST_VA
RCHAR_1%';

CNT TEXT
--- ------------------------------------------------------------
  5 INSERT INTO TEST_VARCHAR_1 VALUES (:B4 , :B3 , :B2 , :B1 )

SQL>

[/PHP]

到这里一条良好bind variable 的sql 居然有了5个不同的版本
dump library看看
[PHP]
SQL> alter session set events'immediate trace name library_cache level 10';

Session altered.

SQL>
[/PHP]
trace file  中的结果:

BUCKET 61156:

  LIBRARY OBJECT HANDLE: handle=693584ec
  name=INSERT INTO TEST_VARCHAR_1 VALUES (:B4 , :B3 , :B2 , :B1 )
[/COLOR]
  hash=29b0eee4 timestamp=03-06-2007 11:01:44
  namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/[12010000]
  kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=1
  lwt=69358504[69358504,69358504] ltm=6935850C[6935850C,6935850C]
  pwt=6935851C[6935851C,6935851C] ptm=69358574[69358574,69358574]
  ref=693584F4[693584F4, 693584F4] lnd=69358580[6953D4F0,695504EC]
DEPENDENCY REFERENCES:
reference latch flags
--------- ----- -------------------
   691b36a0    0 [20]
LOCK OWNERS:
      lock    user  session count mode flags
-------- -------- -------- ----- ---- ------------------------
68eb99cc 67e440dc 67e440dc    1 N [00]
LIBRARY OBJECT: object=696a88e0
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
CHILDREN: size=16

child# table reference handle
------ -------- --------- --------
      0 696a8a8c  696a8814 696a85d8
      1 696a8a8c  696a8894 69ebb060
      2 696a8a8c  6933f51c 6933f42c
      3 696a8a8c  6933f55c 69447440
      4 696a8a8c  6933f59c 69eb8758
[/COLOR]
DATA BLOCKS:
data#    heap  pointer status pins change
----- -------- -------- ------ ---- ------
      0 696a8ae8 696a8968 I/P/A    0 NONE
  BUCKET 61156 total object count=1

在asktom 上有一篇文章
http://asktom.oracle.com/pls/ask ... ON_ID:1542606219593
讲到char 和varchar2的区别时候说到

In PLSQL, what they are saying is that for optimization purposes -- a varchar2 less then
2000 bytes will allocate a fixed amount of memory.  It will preallocate the maximum size
upon instantiation.  This cuts down on the reallocations as different sized strings are
assigned to it.  On the other hand, a varchar2 greater then 2000 characters will truely
be varying sized and as you assign different sized strings to it it will grow dynamically
as needed.

当定义一个varchar2(>2000)的变良的时候
oracle 会根据变量的实际长度分配不同的空间

回到上面
当我们执行一个sql时 oracle 会去shared pool 中的library cache中寻找是否有相同的sql 可以重用这样可以高效但是如果当他发现找到的sql 的变量的存储空间不足以容纳新sql的变量时就只能将sql重新解析一边
做为一个全新的sql放在library cache 待其他session 来共享就象我们上面做的实验一样 l_col_1 实际长度为10bytes 和 l_col_1实际长度为40bytes的sql为2个version 的sql 但前提是l_col_1实际长度为40bytes的sql要后出现因为如果l_col_1实际为40bytes先出现 l_col_1 为10bytes 的sql 是可以重用它的 l_col_1 40bytes 所分配的空间是可以容纳下l_col_1=10bytes的
同样的道理
l_col_1 10bytes  l_col_3  10 bytes

l_col_1 40bytes  l_col_3  10 bytes

l_col_1 10bytes  l_col_3  50 bytes

......

这些sql都可以是不同version的sql 前提就是变量实际长度大的要后出现

至于oracle 在分配给varchar2空间的规律
暂时没什么头绪我想应该和eygle大师的一篇关于librarby cache管理机智的大做有关  希望能有高手出来指点下

回到ora-600 [17059] 的问题上来
metalink 上的有一个bug上的Description:

Bug 4297280  OERI[17059] can occur if over 32767 schemas reference an object

ORA-600[17059] can occur if more than 32767 schemas reference
an object.

Workaround:
   Flush the shared pool

而这个BUG被确定在9206中存在而并未在92XX版本中解决  faint!

我在原帖中上传的trace file中也能看出
出问题的就是一个insert 语句的reference object  超过了32767的最大限制造成了ora-600 [17059]
至于为什么会有这么多其实很好理解
5个变量中有2个varchar2(4000) 一个为varchar2(500) 进行下排列组合就有无数多可能而且这个语句使用非常之频繁以上条件就造就了这个sql引发ora-600 [17059]

总结
1.pl/sql 中在遇到和表字段相关的变量时
  尽量用%type 这样在表结构有什么修改时能及时反映到程序

2  如果在其他语言中执行类似sql 尽量使用pl/sql去调用
3  如果没有必要尽量少使用varrchar2(>2000) 比如能估计到变量的最大长度是远小于2000的时候,一定不要因为觉得方便就一次性定义到varchar2(4000)

4 有的时候oracle并没我们想象的那样完美

bluemoon0083 · 发表于 2007-3-6 13:47

In PLSQL, what they are saying is that for optimization purposes -- a varchar2 less then
2000 bytes will allocate a fixed amount of memory.

当定义一个varchar2(>2000)的变良的时候
oracle 会根据变量的实际长度分配不同的空间

同样的道理
l_col_1 10bytes l_col_3 10 bytes
l_col_1 40bytes l_col_3 10 bytes
l_col_1 10bytes l_col_3 50 bytes
......
这些sql都可以是不同version的sql 前提就是变量实际长度大的要后出现
=================================================================================
我觉得这里有点矛盾，如果先执行2001长度，再执行2002、2003，那么会增加version数
既然都是小于2000，10和40分配的长度应该是一样，不论先后应该都属于同一个版本，这里应该怎么理解？

ihekoko · 发表于 2007-3-6 14:02

并不矛盾
In PLSQL, what they are saying is that for optimization purposes -- a varchar2 less then
2000 bytes will allocate a fixed amount of memory.
是指一个变量定义为varchar2(<2000)比如
varchar2(40)

而在我的例子里
变量定义为varchar2(4000  >2000)
10和40 指的是实际数据的长度

就在asktom的那个回答里
还有这样的话:
In PLSQL, what they are saying is that for optimization purposes -- a varchar2 less then
2000 bytes will allocate a fixed amount of memory.  It will preallocate the maximum size
upon instantiation.  This cuts down on the reallocations as different sized strings are
assigned to it.  On the other hand, a varchar2 greater then 2000 characters will truely
be varying sized and as you assign different sized strings to it it will grow dynamically
as needed.

One takes a little more ram (if you never use the entire string) and performs a little
better, the other uses only what ram it needs but if you cause the string to grow
frequently will not perform as well due to the reallocs to resize the buffer.

There are great differences between a char(1999) and varchar2(1999) as the CHAR is always
1999 characters long physically AND logically.  the varchar is physically 1999 bytes but
logically can be any size in between 0 and 1999.  The char is always blank padded, not so
for the varchar.

ihekoko · 发表于 2007-3-6 14:14

而且令我疑惑的是
按照上面的说法
varchar2(500)应该是固定分配500bytes的空间因为500<2000
但在我的实验中
l_col_1 (varchar2(4000)) 实际长度为10bytes
l_col_3(varchar2(500)) 实际长度为10bytes
与
l_col_1 (varchar2(4000)) 实际长度为10bytes
l_col_3(varchar2(500)) 实际长度为50bytes
也是2个不同的version

这里就解释不通了

希望大师们看到答下疑!

bluemoon0083 · 发表于 2007-3-6 14:30

是不是变量的datatype与实际表上的col的datatype的区别？

LZ你的测试表的表结构如何？

ihekoko · 发表于 2007-3-6 14:34

这和我的表结构无关

我的意思是
l_col_1 varchar2(4000)

select 'aaaaa' into l_col_1 from dual;

此时 l_col_1变量的数据实际长度是5byte

ihekoko · 发表于 2007-3-6 16:55

顶下
还是没人解答我的疑问

1.对于一个声明为varhcar2( >2000)的变量 oracle 是如何分配存储的有什么样的规律

2 对于一个声明为varchar2( <2000)的变量 oracle 是不是直接分配固定的存储比如varchar2(200) 就直接分配200bytes
如果不是那我的例子的不同version的sql 有如何解释?
如果是那么varchar2(200)和char(200)又有什么分别仅仅是asktom 说的 logically上不同吗?

bluemoon0083 · 发表于 2007-3-8 09:02

UP UP

关于varchar2 和 ora-600 [17059]

浏览过的版块