分区表有关的两条SQL语句

3月 26, 2009 at 5:31 下午 · Filed under 基础概念

  1. 1.alter table ... modify default attributes for partition ... ...;

例如：

  1. alter table t modify default attributes for partition p1 tablespace ts1;
  1. 2.alter table ... set subpartition template(...);

例如：

  1. alter table t set subpartition template(subpartition sp1,subpartition sp2);

记录一下备查。

评论 (2835)

遭遇ORA-07445[qeshIHInsertPRow()+89]错误

3月 19, 2009 at 4:14 下午 · Filed under 错误诊断

今天在执行一条SQL语句时，遭遇了ORA-07445[qeshIHInsertPRow()+89]错误，这个错误是由如下的SQL语句引起的：

  1. insert into ITEM_ID_TO_ACCOUNT_ID
  2.         (Account_ID, Item_ID)
  3.     select
  4.         Account_ID, Item_ID
  5.     from
  6.         Account_Fee_Info_Item_Once
  7.     where
  8.         (Item_ID >= 113580229) and
  9.         (Item_ID <= 211731589)
  10.     group by
  11.         Account_ID, Item_ID;

很简单的SQL语句，看不出什么问题，上metalink搜索，得到一个类似的错误：

ORA-07445: exception encountered: core dump [qeshIHInsertPRowInBkt()+12]

metalink给出的解释是由于同时了运行2个实例，分配的SGA内存达到了服务器总的内存大小，建议调小SGA，我们出现问题的情况正好和这个符合，4G物理内存的机器跑了两个实例，于是关掉一个实例，确保SGA远小于物理内存后重新运行SQL语句，一段时间后，依然报错，去掉insert将语句改成下面这样：

  1. select
  2.         count(*)
  3.     from
  4.         Account_Fee_Info_Item_Once
  5.     where
  6.         (Item_ID >= 113580229) and
  7.         (Item_ID <= 211731589)
  8.     group by
  9.         Account_ID, Item_ID;

报错，看来和insert部分无关，去掉group by改成下面这样：

  1. select
  2.         count(*)
  3.     from
  4.         Account_Fee_Info_Item_Once
  5.     where
  6.         (Item_ID >= 113580229) and
  7.         (Item_ID <= 211731589);

没有问题了，看来和group by有关，想到这个语句返回的记录数很大，有近亿，执行group by会占用很大的PGA，而这个环境里面PGA分配了2800M，会不会PGA太大引起的问题呢，将PGA调成1800M，重新运行完整的语句没有报错。

评论 (2351)

对ltrim函数的误解

3月 18, 2009 at 11:29 上午 · Filed under 基础概念

Oracle中ltrim函数的原型是ltrim(char,set)，原来一直以为是对char从左到右进行扫描，拿set整个字符串进行匹配，从char中删除掉包含set的部分，看过SQL Reference以后才知道原来不是这样的，应该是逐个扫描char中的字符，去set中寻找匹配，如果在set中就删除，直到char中遇到不在set中的字符，下面做实验来进行验证：

  1. ltrim('abba','ab')

如果按照错误的理解，结果应该是’ba’，而实际上

  1. SYS AS SYSDBA@>select ltrim('abba','ab') from dual;
  2.  
  3. L
  4. -

再举一些例子

  1. SYS AS SYSDBA@>select ltrim('bazcd','ab') from dual;
  2.  
  3. LTR
  4. ---
  5. zcd
  6.  
  7. SYS AS SYSDBA@>select ltrim('ba','bazcd') from dual;
  8.  
  9. L
  10. -

可以看到，不是拿整个字符串进行匹配，而是拿单个字符进行匹配的，同样的rtrim函数也是这样，只不过扫描匹配方向相反。

评论 (1373)

多字段关联更新语句的另一种写法

3月 11, 2009 at 5:14 下午 · Filed under 性能优化

前文中的多字段关联更新语句还有另一种写法，更新子查询内置视图：

  1. update (select test2.y y2,test2.z z2,test2.t t2,test1.y y1,test1.z z1,test2.t t1 from test1,test2 where test1.x=test2.x) set y2=y1,z2=z1,t2=t1;

在sqlplus autotrace中观察到，语句的consistent gets更少

  1. ELCARO @192.168.24.249>update (select test2.y y2,test2.z z2,test2.t t2,test1.y y1,test1.z z1,test2.t t1 from test1,test2 where test1.x=test2.x) set y2=y1,z2=z1,t2=t1;
  2.  
  3. 1000 rows updated.
  4.  
  5. Elapsed: 00:00:00.01
  6.  
  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 3327478618
  10.  
  11. -----------------------------------------------------------------------------
  12. | Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
  13. -----------------------------------------------------------------------------
  14. |   0 | UPDATE STATEMENT    |       |  1000 | 42000 |    11  (10)| 00:00:01 |
  15. |   1 |  UPDATE             | TEST2 |       |       |            |          |
  16. |*  2 |   HASH JOIN         |       |  1000 | 42000 |    11  (10)| 00:00:01 |
  17. |   3 |    TABLE ACCESS FULL| TEST1 |  1000 | 21000 |     5   (0)| 00:00:01 |
  18. |   4 |    TABLE ACCESS FULL| TEST2 |  1000 | 21000 |     5   (0)| 00:00:01 |
  19. -----------------------------------------------------------------------------
  20.  
  21. Predicate Information (identified by operation id):
  22. ---------------------------------------------------
  23.  
  24.    2 - access("TEST1"."X"="TEST2"."X")
  25.  
  26.  
  27. Statistics
  28. ----------------------------------------------------------
  29.           0  recursive calls
  30.        2084  db block gets
  31.          35  consistent gets
  32.           0  physical reads
  33.      339984  redo size
  34.         910  bytes sent via SQL*Net to client
  35.        1055  bytes received via SQL*Net from client
  36.           4  SQL*Net roundtrips to/from client
  37.           1  sorts (memory)
  38.           0  sorts (disk)
  39.        1000  rows processed

评论 (1165)

Oracle中多字段关联更新的写法

3月 11, 2009 at 4:15 下午 · Filed under 性能优化

写更新的SQL语句时有关联更新的需求，要更新的字段值来源于另一张表的字段值，两张表通过共同的字段进行匹配，比如：

  1. update t1 set a=(select a from t2 where t1.id=t2.id),b=(select b from t2 where t1.id=t2.id);

这种写法的效率比较低，更新t1表中的一行数据时，要与t2表做两次连接操作，有如下相对高效的写法：

  1. update t1 set (a,b)=(select a,b from t2 where t1.id=t2.id);

可以通过实验对比一下两种写法消耗的资源。

准备实验用的表和数据：

  1. create table test1(x int,y varchar2(32),z date,constraint pk_test1 primary key(x));
  2.  
  3. create table test2 as select * from test1;
  4.  
  5. begin
  6.  for i in 1..1000 loop
  7.   insert into test1(x,y,z) values(i,chr(mod(i,26)+65),sysdate-i);
  8.  end loop;
  9. end;
  10.  
  11. commit;
  12.  
  13. begin
  14.  for i in 1..1000 loop
  15.   insert into test2(x,y,z) values(i,chr(mod(i+1,26)+65),sysdate-i);
  16.  end loop;
  17. end;
  18.  
  19. commit;

接着在sqlplus中启用autotrace，观察结果：

  1. ELCARO @192.168.24.249>update test2 set y=(select y from test1 where test1.x=test2.x),z=(select z from test1 where test1.x=test2.x);
  2.  
  3. 1000 rows updated.
  4.  
  5. Elapsed: 00:00:00.03
  6.  
  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 2749727533
  10.  
  11. -----------------------------------------------------------------------------------------
  12. | Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  13. -----------------------------------------------------------------------------------------
  14. |   0 | UPDATE STATEMENT             |          |  1000 | 40000 |     3   (0)| 00:00:01 |
  15. |   1 |  UPDATE                      | TEST2    |       |       |            |          |
  16. |   2 |   TABLE ACCESS FULL          | TEST2    |  1000 | 40000 |     3   (0)| 00:00:01 |
  17. |   3 |   TABLE ACCESS BY INDEX ROWID| TEST1    |     1 |    31 |     1   (0)| 00:00:01 |
  18. |*  4 |    INDEX UNIQUE SCAN         | PK_TEST1 |     1 |       |     1   (0)| 00:00:01 |
  19. |   5 |   TABLE ACCESS BY INDEX ROWID| TEST1    |     1 |    22 |     1   (0)| 00:00:01 |
  20. |*  6 |    INDEX UNIQUE SCAN         | PK_TEST1 |     1 |       |     1   (0)| 00:00:01 |
  21. -----------------------------------------------------------------------------------------
  22.  
  23. Predicate Information (identified by operation id):
  24. ---------------------------------------------------
  25.  
  26.    4 - access("TEST1"."X"=:B1)
  27.    6 - access("TEST1"."X"=:B1)
  28.  
  29. Note
  30. -----
  31.    - dynamic sampling used for this statement
  32.  
  33.  
  34. Statistics
  35. ----------------------------------------------------------
  36.          13  recursive calls
  37.        1046  db block gets
  38.        4028  consistent gets
  39.           0  physical reads
  40.      305632  redo size
  41.         908  bytes sent via SQL*Net to client
  42.        1020  bytes received via SQL*Net from client
  43.           4  SQL*Net roundtrips to/from client
  44.           1  sorts (memory)
  45.           0  sorts (disk)
  46.        1000  rows processed
  47.  
  48. ELCARO @192.168.24.249>update test2 set (y,z)=(select y,z from test1 where test1.x=test2.x);
  49.  
  50. 1000 rows updated.
  51.  
  52. Elapsed: 00:00:00.04
  53.  
  54. Execution Plan
  55. ----------------------------------------------------------
  56. Plan hash value: 3743920144
  57.  
  58. -----------------------------------------------------------------------------------------
  59. | Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  60. -----------------------------------------------------------------------------------------
  61. |   0 | UPDATE STATEMENT             |          |  1000 | 40000 |     3   (0)| 00:00:01 |
  62. |   1 |  UPDATE                      | TEST2    |       |       |            |          |
  63. |   2 |   TABLE ACCESS FULL          | TEST2    |  1000 | 40000 |     3   (0)| 00:00:01 |
  64. |   3 |   TABLE ACCESS BY INDEX ROWID| TEST1    |     1 |    40 |     1   (0)| 00:00:01 |
  65. |*  4 |    INDEX UNIQUE SCAN         | PK_TEST1 |     1 |       |     1   (0)| 00:00:01 |
  66. -----------------------------------------------------------------------------------------
  67.  
  68. Predicate Information (identified by operation id):
  69. ---------------------------------------------------
  70.  
  71.    4 - access("TEST1"."X"=:B1)
  72.  
  73. Note
  74. -----
  75.    - dynamic sampling used for this statement
  76.  
  77.  
  78. Statistics
  79. ----------------------------------------------------------
  80.          31  recursive calls
  81.        1072  db block gets
  82.        2035  consistent gets
  83.           0  physical reads
  84.      306068  redo size
  85.         908  bytes sent via SQL*Net to client
  86.         980  bytes received via SQL*Net from client
  87.           4  SQL*Net roundtrips to/from client
  88.           1  sorts (memory)
  89.           0  sorts (disk)
  90.        1000  rows processed

上面的结果很明显，两次连接合并成一次后，consistent gets大约只有原来的一半，猜测如果关联更新更多的字段，节省的consistent gets应该更多，继续实验进行证实。

为两张表各增加一个字段：

  1. alter table test1 add t date default sysdate;
  2.  
  3. alter table test2 add t date default sysdate;

然后对3个字段进行更新：

  1. ELCARO @192.168.24.249>update test2 set y=(select y from test1 where test1.x=test2.x),z=(select z from test1 where test1.x=test2.x),t=(select t from test1 where test1.x=test2.x);
  2.  
  3. 1000 rows updated.
  4.  
  5. Elapsed: 00:00:00.12
  6.  
  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 3905256412
  10.  
  11. -----------------------------------------------------------------------------------------
  12. | Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  13. -----------------------------------------------------------------------------------------
  14. |   0 | UPDATE STATEMENT             |          |  1000 | 49000 |     5   (0)| 00:00:01 |
  15. |   1 |  UPDATE                      | TEST2    |       |       |            |          |
  16. |   2 |   TABLE ACCESS FULL          | TEST2    |  1000 | 49000 |     5   (0)| 00:00:01 |
  17. |   3 |   TABLE ACCESS BY INDEX ROWID| TEST1    |     1 |    31 |     2   (0)| 00:00:01 |
  18. |*  4 |    INDEX UNIQUE SCAN         | PK_TEST1 |     1 |       |     1   (0)| 00:00:01 |
  19. |   5 |   TABLE ACCESS BY INDEX ROWID| TEST1    |     1 |    22 |     2   (0)| 00:00:01 |
  20. |*  6 |    INDEX UNIQUE SCAN         | PK_TEST1 |     1 |       |     1   (0)| 00:00:01 |
  21. |   7 |   TABLE ACCESS BY INDEX ROWID| TEST1    |     1 |    22 |     2   (0)| 00:00:01 |
  22. |*  8 |    INDEX UNIQUE SCAN         | PK_TEST1 |     1 |       |     1   (0)| 00:00:01 |
  23. -----------------------------------------------------------------------------------------
  24.  
  25. Predicate Information (identified by operation id):
  26. ---------------------------------------------------
  27.  
  28.    4 - access("TEST1"."X"=:B1)
  29.    6 - access("TEST1"."X"=:B1)
  30.    8 - access("TEST1"."X"=:B1)
  31.  
  32. Note
  33. -----
  34.    - dynamic sampling used for this statement
  35.  
  36.  
  37. Statistics
  38. ----------------------------------------------------------
  39.         342  recursive calls
  40.        2091  db block gets
  41.        7306  consistent gets
  42.           0  physical reads
  43.      417280  redo size
  44.        1777  bytes sent via SQL*Net to client
  45.        1266  bytes received via SQL*Net from client
  46.           6  SQL*Net roundtrips to/from client
  47.          13  sorts (memory)
  48.           0  sorts (disk)
  49.        1000  rows processed
  50.       
  51. ELCARO @192.168.24.249>update test2 set (y,z,t)=(select y,z,t from test1 where test1.x=test2.x);
  52.  
  53. 1000 rows updated.
  54.  
  55. Elapsed: 00:00:00.03
  56.  
  57. Execution Plan
  58. ----------------------------------------------------------
  59. Plan hash value: 3743920144
  60.  
  61. -----------------------------------------------------------------------------------------
  62. | Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  63. -----------------------------------------------------------------------------------------
  64. |   0 | UPDATE STATEMENT             |          |  1000 | 49000 |     5   (0)| 00:00:01 |
  65. |   1 |  UPDATE                      | TEST2    |       |       |            |          |
  66. |   2 |   TABLE ACCESS FULL          | TEST2    |  1000 | 49000 |     5   (0)| 00:00:01 |
  67. |   3 |   TABLE ACCESS BY INDEX ROWID| TEST1    |     1 |    49 |     2   (0)| 00:00:01 |
  68. |*  4 |    INDEX UNIQUE SCAN         | PK_TEST1 |     1 |       |     1   (0)| 00:00:01 |
  69. -----------------------------------------------------------------------------------------
  70.  
  71. Predicate Information (identified by operation id):
  72. ---------------------------------------------------
  73.  
  74.    4 - access("TEST1"."X"=:B1)
  75.  
  76. Note
  77. -----
  78.    - dynamic sampling used for this statement
  79.  
  80.  
  81. Statistics
  82. ----------------------------------------------------------
  83.           5  recursive calls
  84.        2084  db block gets
  85.        2386  consistent gets
  86.           0  physical reads
  87.      339984  redo size
  88.         910  bytes sent via SQL*Net to client
  89.         984  bytes received via SQL*Net from client
  90.           4  SQL*Net roundtrips to/from client
  91.           1  sorts (memory)
  92.           0  sorts (disk)
  93.        1000  rows processed

上面的结果显示，3次连接合并成一次后，consistent gets的量减少到1/3

评论 (10)

ORA-02082错误

3月 9, 2009 at 5:44 下午 · Filed under 错误诊断

创建指向自己的dblink时报错：ORA-02082: a loopback database link must have a connection qualifier，指向自己的dblink是loopback dblink，创建这样的dblink时，如果dblink名字和数据库名字一样，创建时就会报ORA-02082错误，解决办法很简单，给dblink取一个和db_name不一样的名字就行了。

评论 (2841)

global_names参数与ORA-02085错误

3月 5, 2009 at 3:11 下午 · Filed under 错误诊断

今天同事创建使用dblink的时候碰到ORA-02085错误，报错：

  1. ORA-02085: database link JSDB090104.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to JSDB.REGRESS.RDBMS.DEV.US.ORACLE.COM

这个错误与初始化参数global_names有关，当global_names参数的值被设置成true时，oracle会检查dblink的名字和远程数据库的名字是否一致，不一致时就会报ORA-02085错误，因此解决这个问题的办法很简单，将global_names参数的值改成false即可，修改这个参数无需重启数据库实例。

评论 (3298)

windows计划任务使用ftp命令下载文件碰到的问题

3月 2, 2009 at 10:07 上午 · Filed under 操作系统

上周五需要使用计划任务在周六临晨6点钟下载一些文件，是通过ftp命令调用ftp命令文件的方式，两个脚本文件内容如下：

ftp.bat

  1. ftp -n -i -s:e:\ftp.txt

ftp.txt

  1. open 192.168.33.44
  2. user a
  3. b
  4. lcd e:\
  5. cd /2009/200903/20090302
  6. bin
  7. mget 20090302*.bak
  8. bye

后来发现计划任务失败，文件没有下载成功，手动运行ftp.bat文件时发现命令行窗口中ftp命令在滚动重复执行，但是没有执行ftp.txt文件中的内容，后来同事建议将

  1. ftp -n -i -s:e:\ftp.txt

改成

  1. ftp.exe -n -i -s:e:\ftp.txt

后，发现计划任务能正常运行了

评论 (2006)

使用分区表遭遇ORA-14402错误

2月 27, 2009 at 11:05 上午 · Filed under 错误诊断

将一张表重定义成hash分区表后，应用开始运行后报错：ORA-14402: updating partition key column would cause a partition change，原来应用中有对hasn分区键进行更新的SQL语句，更新分区键会导致记录从一个分区移动到一个分区，即在不同的segment之间移动，rowid会发生改变，默认情况下，这是不允许的，发生这种情况时，就会报错，为了消除这个错误，可以通过如下语句修改表的属性：

  1. alter table ... enable row movement;

记录移动的过程实际上是在原分区删除记录，在新分区创建记录，还有对应的索引的维护，开销较大，考虑到性能问题，后来将那张表改回了普通表。

评论 (1855)

使用可传输表空间移除分区表历史数据

2月 18, 2009 at 5:22 下午 · Filed under 备份恢复

通常，分区表增长速度很快，为了节省空间和资源，需要将历史数据从生产库移除到历史库，我们可以使用可传输表空间进行移除操作，步骤如下：

1.建立与分区表同样表结构的普通表，若分区表有索引，在普通表建立同样的索引
2.使用alter table … exchange partition … with table … (including indexes without validation)语句将需要移除的分区与普通表交换
3.将被移除分区所在的表空间置入只读模式
4.用exp或expdp将被移除分区所在的表空间进行可传输表空间导出
5.将产生的dmp文件和被移除分区所在表空间的数据文件复制到历史库
6.删除上面建立的普通表
7.删除被移除分区所在的表空间
8.删除被移除分区

然后就可以在历史库中很方便的导入可传输表空间，将表数据合并到分区表中

评论 (457)

« Previous entries Project-Id-Version: Wordpress POT-Creation-Date: PO-Revision-Date: 2007-05-16 16:18+0800 Last-Translator: Xiaoyu Huang <007pig@gmail.com> Language-Team: 007pig <007pig@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit X-Poedit-Country: CHINA X-Poedit-SourceCharset: utf-8 X-Poedit-Basepath: D:\phpfiles\wordpress22 Plural-Forms: nplurals=1; plural=0; Project-Id-Version: Wordpress POT-Creation-Date: PO-Revision-Date: 2007-05-16 16:18+0800 Last-Translator: Xiaoyu Huang <007pig@gmail.com> Language-Team: 007pig <007pig@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit X-Poedit-Country: CHINA X-Poedit-SourceCharset: utf-8 X-Poedit-Basepath: D:\phpfiles\wordpress22 Plural-Forms: nplurals=1; plural=0;