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

最新推荐文章于 2024-05-28 06:39:22 发布

原创最新推荐文章于 2024-05-28 06:39:22 发布 · 8.2k 阅读

1 ·

CC 4.0 BY-SA版权

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文详细介绍了在Oracle数据库中通过调整参数来优化全表扫描性能的方法，包括理解_serial_direct_read参数的作用，如何根据实际情况设置此参数以提高查询效率，并提供了参数查询和修改的步骤。

[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