关闭Oracle 11g的DPR特性

关闭Oracle 11g的DPR（Direct Path Read）特性

查看event参数值：

SQL> show parameter event

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string
xml_db_events                        string      enable

设置10949 event,屏蔽DPR特性：

SQL> alter system set event='10949 trace name context forever,level 1' sid='*';
alter system set event='10949 trace name context forever,level 1' sid='*'
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set event='10949 trace name context forever,level 1' sid='*' scope=spfile;

System altered.

由于指定的scope=spfile，需要重启库生效：

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2252824 bytes
Variable Size             222302184 bytes
Database Buffers           83886080 bytes
Redo Buffers                4718592 bytes
Database mounted.
Database opened.

SQL> show parameter event

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      10949 trace name context forev
                                                 er,level 1
xml_db_events                        string      enable

数据库不可以重启的话，就动态去设置这个隐藏参数：

alter system set "_serial_direct_read"=never;

然后再去查下面的SQL确认下更改结果（更改前也可以查下，一般是auto）：

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND (x.ksppinm ='_small_table_threshold'  or x.ksppinm='_serial_direct_read');