serial direct scan은 11g에 소개된 기능으로 대상 테이블을 size로 구분하여 이에 대해
SGA내의 buffer scan (기존 방식)을 사용할지, direct path read 방식으로 수행할 지를 자동으로 결정하는 기능입니다.
즉, Parallel query를 사용하지 않고 serial scan으로 수행되더라도 direct path 방식으로 수행할 수 있습니다.
유의할 부분은 direct path read는 dirty buffer의 checkpoint를 유발하니 OLTP에서는 유의해야 합니다.  

_serial_direct_read parameter는 segment size와 parameter 설정값에 따라 다르게 동작합니다. 

_serial_direct_read
Segment size
Small
Medium
Large
Very Large
TRUE
Direct Path Read
Direct Path Read
Direct Path Read
Direct Path Read
FALSE
Buffer Cache Read
Buffer Cache Read
Buffer Cache Read
Buffer Cache Read
AUTO
Buffer Cache Read
Object Stat 존재 시 Buffer Cache Read 수행
그외 비용 분석후 결정
비용 분석 후 결정
Direct Path Read

read 방식을 결정하는 segment size는 대상 object의 block 개수와 _small_table_threshold, _very_large_object_threshold parameter와 Buffer cache size에 의해 결정됩니다. 

Segment Size정의
SmallSegment Block < _small_table_threshold
Medium_small_table_threshold < Segment blocks < Buffer cache의 10% 이하
LargeMedium과 Very Large 사이 일 경우
Very LargeBuffer cache * (_very_large_object_threshold/100) 보다 큰 경우

관련 Parameter 
_small_table_threshold : lower threshold level of table size for direct reads. 
                            Default value : 20 (Buffer cache의 2%)

_very_large_object_threshold :
upper threshold level of object size for direct reads.
                                 Default Value : 500 (Buffer cache의 5배)



Oracle 12c 부터 RAW device가 desupport 됩니다. 

Oracle 12c, Desupport for Raw Storage Devices

 

어찌어찌 해서 raw device를 사용한채로 upgrade를 한다해도 raw device를 direct로 사용하면 오류 발생할 거라네요. 

12c로 upgrade 할땐 ASM으로의 migration 까지도 염두해 둬야 되겠네요.. OTL


언듯 raw device 별로 diskgroup을 만들어 tablespace를 생성하는 꼼수가 머리를 스치는 군요 .. ㅋ 



Starting with Oracle Database version 12.1 (release date TBD), support for storing data files, spfiles, controlfiles, online redo logfiles, OCRs and voting files on raw devices directly will end. This means commands such as the following will report an error while attempting to use raw devices directly in Oracle Database Version 12.1:


SQL> create tablespace ABC DATAFILE '/dev/raw/raw1' size 2GB;


Note that while the direct use of raw devices will be de-supported for Oracle Database 12.1, customers can choose to create Oracle ASM diskgroups on top of raw devices. While it is recommended to store all shared files on ASM diskgroups, storing those files on NFS or certified cluster file systems remains supported.


The following SQL commands will not return an error while attempting to use raw devices. Reason: the raw devices in the example below are used indirectly via Oracel ASM (no direct use of raw devices here):


SQL>alter diskgroup MYDG add disk '/dev/raw/ABC1.dbf';

 OR

SQL>create diskgroup MYDB EXTERNAL REDUNDANCY disk 'dev/raw/ABC1.dbf'


Then use the following command to create the tablespace


SQL> create tablespace ABC DATAFILE '+MYDG' size 2GB;


If raw devices are not being used directly in the current release then no further actions need to be taken. However, if raw devices are being used directly currently, then planning should be performed to migrate respective files off raw devices. There are many choices currently to replace raw devices, including Oracle ASM, NFS, and supported cluster file systems.


참고 : Announcement of De-Support of using RAW devices in Oracle Database Version 12.1 (Doc ID 578455.1)


+ Recent posts