查看: 3648|回复: 7

关于varchar2 和 ora-600 [17059]

[复制链接]
论坛徽章:
3
生肖徽章:虎
日期:2007-09-18 15:23:56会员2007贡献徽章
日期:2007-09-26 18:42:10奥运会纪念徽章:皮划艇激流回旋
日期:2008-06-12 17:50:19
发表于 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并没我们想象的那样完美
论坛徽章:
59
狮子座
日期:2016-03-26 13:35:402013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-02-25 11:06:15ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:20灰彻蛋
日期:2012-04-25 13:19:33紫蛋头
日期:2012-03-14 11:16:09最佳人气徽章
日期:2012-03-13 17:39:18玉石琵琶
日期:2012-02-21 15:04:38鲜花蛋
日期:2011-11-30 14:13:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 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分配的长度应该是一样,不论先后应该都属于同一个版本,这里应该怎么理解?

使用道具 举报

回复
论坛徽章:
3
生肖徽章:虎
日期:2007-09-18 15:23:56会员2007贡献徽章
日期:2007-09-26 18:42:10奥运会纪念徽章:皮划艇激流回旋
日期:2008-06-12 17:50:19
 楼主| 发表于 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.

使用道具 举报

回复
论坛徽章:
3
生肖徽章:虎
日期:2007-09-18 15:23:56会员2007贡献徽章
日期:2007-09-26 18:42:10奥运会纪念徽章:皮划艇激流回旋
日期:2008-06-12 17:50:19
 楼主| 发表于 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

这里就解释不通了

希望大师们看到答下疑!

使用道具 举报

回复
论坛徽章:
59
狮子座
日期:2016-03-26 13:35:402013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-02-25 11:06:15ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:20灰彻蛋
日期:2012-04-25 13:19:33紫蛋头
日期:2012-03-14 11:16:09最佳人气徽章
日期:2012-03-13 17:39:18玉石琵琶
日期:2012-02-21 15:04:38鲜花蛋
日期:2011-11-30 14:13:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2007-3-6 14:30 | 显示全部楼层
是不是变量的datatype与实际表上的col的datatype的区别?

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

使用道具 举报

回复
论坛徽章:
3
生肖徽章:虎
日期:2007-09-18 15:23:56会员2007贡献徽章
日期:2007-09-26 18:42:10奥运会纪念徽章:皮划艇激流回旋
日期:2008-06-12 17:50:19
 楼主| 发表于 2007-3-6 14:34 | 显示全部楼层
这和我的表结构无关

我的意思是
l_col_1  varchar2(4000)

select 'aaaaa' into l_col_1 from dual;

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

使用道具 举报

回复
论坛徽章:
3
生肖徽章:虎
日期:2007-09-18 15:23:56会员2007贡献徽章
日期:2007-09-26 18:42:10奥运会纪念徽章:皮划艇激流回旋
日期:2008-06-12 17:50:19
 楼主| 发表于 2007-3-6 16:55 | 显示全部楼层
顶下
还是没人解答我的疑问

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

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

使用道具 举报

回复
论坛徽章:
59
狮子座
日期:2016-03-26 13:35:402013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-02-25 11:06:15ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:20灰彻蛋
日期:2012-04-25 13:19:33紫蛋头
日期:2012-03-14 11:16:09最佳人气徽章
日期:2012-03-13 17:39:18玉石琵琶
日期:2012-02-21 15:04:38鲜花蛋
日期:2011-11-30 14:13:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2007-3-8 09:02 | 显示全部楼层
UP UP

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表