How to Use DBMS_ADVANCED_REWRITE in Oracle 10g
在Oracle10g之后,提供了DBMS_ADVANCED_REWRITE包,具有强大的查询重写功能,可以让我们在数据库层面实现很多微妙的调整。假设我们有一个应用,但是现在无法直接修改应用程序的编码,但是又想能够让应用程序的某些SQL产生我们想要的变化,那么就可以使用DBMS_ADVANCED_REWRITE包。
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;
SQL> select count(*) from t;
COUNT(*)
----------
16636
SQL> select count(*) from t1;
COUNT(*)
----------
0
现在我们有表T和T1,表结构相同,但是表T中有1.6万记录,而表T1中没有记录,如果说我们的应用中有一个SQL多次地查询表T的总记录数,占用了大量的CPU和逻辑读,而这样的count记录数又是完全没有用处的,但是我们无法修改应用程序去掉这个SQL,那么我们就可以通过DBMS_ADVANCED_REWRITE包来讲查询表T的SQL转变为查询表T1,这样就大大减少了这条SQL的逻辑读。
首先DBMS_ADVANCED_REWRITE包的执行权限必须显式赋给需要的用户。
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;
/
然后需要设置会话层面的QUERY_REWRITE_INTEGRITY参数,该参数默认值为ENFORCED,表示只有重写后的SQL输出结果跟原结果完全一样时,查询才会被真正重写,在这里需要修改为TRUSTED。
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>
可以看到在重写之后,执行计划中显示直接去查询表T1,而consistent gets也从查询表T需要的69减少为3。
可以从[USER|ALL|DBA]_REWRITE_EQUIVALENCES视图中获得查询重写的信息。
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
DBMS_ADVANCED_REWRITE包也有限制,不可以重写牵涉到SYS用户对象的SQL。
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
在测试中,如果destination_stmt中包含SYS用户对象,是可以成功创建查询重写的,但是在执行SQL的时候却会报ORA-03113错误,后台出现ORA-07445错误,无法正常执行。
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
ORA-01017 When Connect SYSDBA by SQL Developer
在尝试使用SQL Developer用SYSDBA连接数据库时总是报ORA-01017错误。
ORA-01017: invalid username/password; logon denied
实际上用户名密码是正确的,并且在数据库服务器上使用SQL*Plus通过监听连接也是正常的。
C:\Users\Kamus>sqlplus "sys/oracle@orcl11g as sysdba" SQL*Plus: Release 11.1.0.7.0 - Production on Fri Mar 12 12:17:01 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP and Real Application Testing options SQL>
真正的问题是因为数据库密码文件缺失了。在windows下,Oracle数据库密码文件是储存在%ORACLE_HOME%\database目录下,命名为PWD%SID%.ora。
密码文件不存在,数据库实例完全可以正常启动,只是在尝试通过监听登陆SYSDBA的时候就会报ORA-01017错误。
那么为什么在本地使用SQL*Plus是正常的,这实际上是一个错觉,因为在Windows中Oracle默认安装以后会在sqlnet.ora文件中设置SQLNET.AUTHENTICATION_SERVICES = (NTS),这表示支持“Windows NT native authentication”方式登陆数据库,也就是属于OSDBA组的Windows用户不用提供密码也可以通过SYSDBA登陆数据库。sqlnet.ora文件位于%ORACLE_HOME%\network\admin目录下。
我们随便使用一个不存在的用户名密码都是可以登录数据库的。
C:\Users\Kamus>sqlplus "NotExist/nopassword@orcl11g as sysdba" SQL*Plus: Release 11.1.0.7.0 - Production on Fri Mar 12 13:10:49 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP and Real Application Testing options SQL>
修改SQLNET.AUTHENTICATION_SERVICES参数为NONE之后。
SQLNET.AUTHENTICATION_SERVICES = (NONE)
再次测试用SQL*Plus登陆,报ORA-01031错误,即使提供正确的SYS用户密码也会报同样的错误,因为此时密码文件不存在,不能通过密码文件校验SYS用户密码是否正确,而又不允许通过NTS方式登陆数据库。
C:\Users\Kamus>sqlplus "NotExist/nopassword@orcl11g as sysdba" SQL*Plus: Release 11.1.0.7.0 - Production on Fri Mar 12 13:14:07 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. ERROR: ORA-01031: insufficient privileges Enter user-name:
重新创建密码文件,保持sqlnet.ora文件中SQLNET.AUTHENTICATION_SERVICES = (NONE)。
orapwd file=D:\oracle\product\11.1.0\db_1\database\PWDorcl11g.ora password=oracle
这样就只能通过正确的SYS用户和密码才可以用SYSDBA登陆数据库了。
C:\Users\Kamus>sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Fri Mar 12 13:18:32 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. ERROR: ORA-01031: insufficient privileges Enter user-name: C:\Users\Kamus> C:\Users\Kamus>sqlplus sys/oracle as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Fri Mar 12 13:18:44 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP and Real Application Testing options SQL>
同样在这种配置下,SQL Developer也可以正常用SYSDBA登陆数据库了。
ACOUG!
有感于国外Oracle用户组的发达,我跟eygle从今年开始尝试组建中国的Oracle用户组,All China Oracle User Group。我们的站点是acoug.org。
我们希望能够为全中国的Oracle技术爱好者提供一个交流与活动的平台,我们希望能够激发更多人对于Oracle技术以及相关知识的兴趣,我们希望沉浸在这些技术中的人员获得更多的乐趣。这是我跟eygle一直以来的一个理想,但愿我们能为此付出并且有所回报。
我们认识到中国目前掌握Oracle技术的人员广度和深度都跟美国、欧洲甚或是印度存在着较大差距,这需要我们更有热情的去付出,但愿在不久的将来ACOUG在全球Oracle用户组中占据一席之地。
ACOUG计划定期举行线下活动,而线下活动也将是ACOUG未来工作的重点,邀请著名的业内人士(不仅限于国内)来进行主题演讲,并围绕相关主题进行广泛的讨论从而使更多人获益。
eygle的文章有关于ACOUG更多的阐述,请猛击这里。
预计在这个月的3月20日(周六)开展ACOUG第一次线下活动,详细的地点、场地以及演讲主题还未确定,但是请有兴趣的朋友时刻关注ACOUG网站,欢迎参加。
![Chanel [K]](http://www.dbform.com/wp-content/chanelk.png)