oracle query 调优,利用query_rewrite调优SQL性能甚至改变结果

1，问题：

一般为了解决性能问题，可能需要使用outline、sql profile固定执行计划。

dbms_advanced_rewrite重写SQL，甚至可能通过重写改变查询结果。

2，条件：

--权限：

execute on dbms_advanced_rewrite

create materialized view

--回话或系统级设置

query_rewrite_integerity=trusted/stale_tolerated(enfored为缺省值)

3，测试：

3.1版本

SQL> select * from v$version;

BANNER

----------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

3.2准备用户

SQL> create user tom identified by tom;

User created.

SQL> grant connect,resource to tom;

Grant succeeded.

SQL> grant execute on dbms_advanced_rewrite to tom;

Grant succeeded.

3.3 准备测试表：

SQL> create table t1(x int);

Table created.

SQL> create table t2(x int);

Table created.

SQL> insert into t1 values(1);

1 row created.

SQL> r

1* insert into t1 values(1)

1 row created.

SQL> commit;

Commit complete.

3.4 声明等价性

begin

SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (

name             => 'TOM.SQL1',

source_stmt      => 'select * from t1',

destination_stmt => 'select * from t2',

validate         =>false);

end;

/

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29

ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185

ORA-06512: at line 2

SQL> conn / as sysdba

SQL> grant create materialized view to tom;

Grant succeeded.

SQL> conn tom/tom

SQL> begin

SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (

name             => 'TOM.SQL1',

source_stmt      => 'select * from t1',

destination_stmt => 'select * from t2',

validate         =>false);

end;

/

2    3    4    5    6    7    8

PL/SQL procedure successfully completed.

3.5 看效果

SQL> select * from t1;

X

----------

1

1

--需要设置查询重写完整性

SQL> alter session set query_rewrite_integrity=trusted;

Session altered.

SQL> select * from t1;

no rows selected

--变为大写，不妨碍重写

SQL> select * from T1;

no rows selected

--增加空格，不妨碍重写

SQL> select * from  T1;

no rows selected

--增加别名，影响重写

SQL> select * from t1 a;

X

----------

1

1