How to Use DBMS_ADVANCED_REWRITE in Oracle 10g

on

在Oracle10g之后,提供了DBMS_ADVANCED_REWRITE包,具有强大的查询重写功能,可以让我们在数据库层面实现很多微妙的调整。假设我们有一个应用,但是现在无法直接修改应用程序的编码,但是又想能够让应用程序的某些SQL产生我们想要的变化,那么就可以使用DBMS_ADVANCED_REWRITE包。

[sourcecode language=”sql” light=”true”]drop table t;
create table t as select object_id,object_name from dba_objects;
drop table t1;
create table t1 as select object_id,object_name from dba_objects where 1=0;[/sourcecode]

[sourcecode language=”sql” light=”true”]SQL> select count(*) from t;

COUNT(*)
———-
16636

SQL> select count(*) from t1;

COUNT(*)
———-
0[/sourcecode]

现在我们有表T和T1,表结构相同,但是表T中有1.6万记录,而表T1中没有记录,如果说我们的应用中有一个SQL多次地查询表T的总记录数,占用了大量的CPU和逻辑读,而这样的count记录数又是完全没有用处的,但是我们无法修改应用程序去掉这个SQL,那么我们就可以通过DBMS_ADVANCED_REWRITE包来讲查询表T的SQL转变为查询表T1,这样就大大减少了这条SQL的逻辑读。

首先DBMS_ADVANCED_REWRITE包的执行权限必须显式赋给需要的用户。

[sourcecode language=”sql” light=”true”]CONN sys/password AS SYSDBA

GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO kamus;

CONN kamus/password

BEGIN
SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
name => ‘t_rewrite’,
source_stmt => ‘SELECT count(*) FROM t’,
destination_stmt => ‘SELECT count(*) FROM t1’,
validate => FALSE,
rewrite_mode => ‘TEXT_MATCH’);
END;
/[/sourcecode]

然后需要设置会话层面的QUERY_REWRITE_INTEGRITY参数,该参数默认值为ENFORCED,表示只有重写后的SQL输出结果跟原结果完全一样时,查询才会被真正重写,在这里需要修改为TRUSTED。

[sourcecode language=”sql” light=”true”]SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
SQL> set autot on
SQL> select count(*) from t;

COUNT(*)
———-
0

Execution Plan
———————————————————-
Plan hash value: 238181912

—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | | | |
| 3 | TABLE ACCESS FULL| T1 | 1 | | 2 (0)| 00:00:01 |
—————————————————————————-

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
417 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = ENFORCED;
SQL> select count(*) from t;

COUNT(*)
———-
16636

Execution Plan
———————————————————-
Plan hash value: 2966233522

——————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————-
| 0 | SELECT STATEMENT | | 1 | 22 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 16639 | 22 (0)| 00:00:01 |
——————————————————————-

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
0 recursive calls
0 db block gets
69 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>[/sourcecode]

可以看到在重写之后,执行计划中显示直接去查询表T1,而consistent gets也从查询表T需要的69减少为3。

可以从[USER|ALL|DBA]_REWRITE_EQUIVALENCES视图中获得查询重写的信息。

[sourcecode language=”sql” light=”true”]SQL> select * from user_rewrite_equivalences;

OWNER NAME SOURCE_STMT DESTINATION_STMT REWRITE_MO
—— ———- ————————- ————————- ———-
KAMUS T_REWRITE SELECT count(*) FROM t SELECT count(*) FROM t1 TEXT_MATCH[/sourcecode]

DBMS_ADVANCED_REWRITE包也有限制,不可以重写牵涉到SYS用户对象的SQL。

[sourcecode language=”sql” light=”true”]drop table t1;
create table t1 as select * from all_tables where 1=0;

SQL> BEGIN
2 SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
3 name => ‘t_rewrite’,
4 source_stmt => ‘SELECT count(*) FROM all_tables’,
5 destination_stmt => ‘SELECT count(*) FROM t1’,
6 validate => FALSE,
7 rewrite_mode => ‘TEXT_MATCH’);
8 END;
9 /
BEGIN
*
ERROR at line 1:
ORA-30354: Query rewrite not allowed on SYS relations
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2[/sourcecode]

在测试中,如果destination_stmt中包含SYS用户对象,是可以成功创建查询重写的,但是在执行SQL的时候却会报ORA-03113错误,后台出现ORA-07445错误,无法正常执行。

[sourcecode language=”sql” light=”true”]SQL> BEGIN
2 SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
3 name => ‘t_rewrite’,
4 source_stmt => ‘SELECT count(*) FROM t1’,
5 destination_stmt => ‘SELECT count(*) FROM all_tables’,
6 validate => FALSE,
7 rewrite_mode => ‘TEXT_MATCH’);
8 END;
9 /

PL/SQL procedure successfully completed.

SQL> select * from user_rewrite_equivalences;

OWNER NAME SOURCE_STMT DESTINATION_STMT REWRITE_MO
—— ———- ————————- ————————- ———-
KAMUS T_REWRITE SELECT count(*) FROM t1 SELECT count(*) FROM all_ TEXT_MATCH
tables

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

Session altered.

SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8360
Session ID: 135 Serial number: 22[/sourcecode]

延展阅读:
Using DBMS_ADVANCED_REWRITE with an HINT to change the execution plan - Gavin Soorma

4 Comments Add yours

  1. Zeeno says:

    SQL里不能有序列、SYSDATE、绑定变量等等……限制太多了,貌似用途有限。

  2. fanngyuan says:

    好像不能带绑定变量
    感觉不太实用
    而且还不能用全文检索吧
    我上次用这个结合全文检索来调优like ‘*fff’这种查询
    直接报错

  3. dbabc says:

    问个弱弱的问题,文中的高亮代码是如何实现的?

  4. kamus says:

    @Zeeno
    不能期望这个包能将程序中所有的SQL都毫无痕迹的转变为另外一个SQL,这很危险而且也并不应该是数据库层面该做的事情。
    至于带绑定变量的SQL重写,可以参考Metalink 392214.1,将rewrite_mode设置为GENERAL,这样可以忽略where条件而重写SQL,慎用!

    @dbabc
    用WP-Syntax或者SyntaxHighlighter Evolved插件

Leave a Reply

Your email address will not be published. Required fields are marked *