11g中_serial_direct_read是动态参数，可以在线修改

最新推荐文章于 2021-04-02 21:35:55 发布

msdnchina

最新推荐文章于 2021-04-02 21:35:55 发布

阅读量7.9k

点赞数

分类专栏： Oracle DB

本文链接：https://blog.csdn.net/msdnchina/article/details/45393821

版权

Oracle DB 专栏收录该内容

773 篇文章 3 订阅

订阅专栏

[oracle@rhel63single ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 26 13:54:56 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set "_serial_direct_read" = false sid = '*';

System altered.

SQL> show parameter serial

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_serial_direct_read                  string      FALSE
serial_reuse                         string      disable
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u02/app/oracle/product/11.2.0
                                                 .4/db_1/dbs/spfiletest.ora
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             612369288 bytes
Database Buffers          448790528 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.
SQL> show parameter serial

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_serial_direct_read                  string      FALSE
serial_reuse                         string      disable
SQL> select x.ksppinm, y.ksppstvl
  2  from x$ksppi x , x$ksppcv y
  3  where x.indx = y.indx
  4  and x.ksppinm like '_serial_direct_read'
  5  order by x.ksppinm; 

KSPPINM
--------------------------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------
_serial_direct_read
FALSE

值得注意的是，_serial_direct_read的默认值是auto，

如下参考自：http://www.savedba.com/?p=619

在11gr2之后，当一个表的块数大于_small_table_threshold的时候，就被判定为大表，否则就视为小表。对于大表，在进行全表扫描的时候，oracle会根据_serial_direct_read的值来确定是否执行direct path read，如果值为auto，那就会direct path read。

参考文章：

http://www.eygle.com/archives/2012/05/oracle_11g_direct_path_read.html

http://www.savedba.com/?p=619