아래의 쿼리에서보면 인덱스를 타야할거 같은데 Full Table Scan을 하고 있다.
DB_FILE_MULTIBLOCK_READ_COUNT 의 설정에 따라 Plan이 빠뀐다.
Oracle EBS 11i에서는 DB_FILE_MULTIBLOCK_READ_COUNT=8 을 권장한다.
현 시스템에는 더 높은 숫자로 설정되어 있는데..아무런 문제가 없는 상황에서 CBO에 영향을 미치는 저 파라미터를 변경해도 괜챦을지????
select distinct segment1 locator
from MTL_ITEM_LOCATIONS
where organization_id = :1
and subinventory_code = :2
order by segment1
db_block_size = 8k
MTL_ITEM_LOCATIONS_N4 : ORGANIZATION_ID + SUBINVENTORY_CODE
alter session set db_file_multiblock_read_count = 8; 일경우
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=890 Card=36 Bytes=612)
1 0 SORT (UNIQUE) (Cost=863 Card=36 Bytes=612)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'INV.MTL_ITEM_LOCATIONS' (Cost=836 Card=7K Bytes=109K)
3 2 INDEX (RANGE SCAN) OF 'INV.MTL_ITEM_LOCATIONS_N4' (NON-UNIQUE) (Cost=29 Card=7K)
alter session set db_file_multiblock_read_count = 32; 일경우
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=510 Card=36 Bytes=612)
1 0 SORT (UNIQUE) (Cost=485 Card=36 Bytes=612)
2 1 TABLE ACCESS (FULL) OF 'INV.MTL_ITEM_LOCATIONS' (Cost=459 Card=7K Bytes=109K)
[참고자료]
216205.1 Database Initialization Parameters and Configuration for Oracle Applications 11i
174605.1 bde_chk_cbo.sql - Reports Database Initialization Parameters related to an Apps 12 or 11i
DB_FILE_MULTIBLOCK_READ_COUNT와 퍼포먼스의 관계
* db_file_multiblock_read_count 파라미터는 한번의 I/O 작업으로 읽어들이는
최대 블럭 수를 가리키며 Multi Block I/O 에 관련된 파라미터이다. 디폴트
값은 O/S에 따라서 다른데 보통 db_block_buffers와 Processes에
의해서결정되며 4~32 정도가 많이 사용된다.
이 값을 키우고 SORT/MERGE 를 이용하는 것이 Nested Loop를 이용하는 경우
보다 퍼포먼스가 증가하는가 하는 문제는 Query의 종류와 읽어들이는 데이타의
양(전체에 대한 Percentage)에 따라서 달라지게 된다.
즉, 이 값이 크면 한번의 I/O로 여러개의 블럭을 읽 오므로 Full Table Scan시
에는 퍼포먼스가 증가한다. 하지만 특정 블럭만이 필요한 경우에 불필요한
블럭까지 함께 읽어들이므로 디스크를 읽는 시간이 증가할 뿐 아니라 크기가
한정된 Buffer Pool을 많이 차지해 버림으로써 자주 쓰이는 데이타를 밀어내어
오히려 Cache의 효율을 떨어뜨리는 결과를 가져올 수도 있으므로 작업특성에
따라서 적절한 값을 세팅하여야 한다.
오라클의 Optimizer는 이 값이 크면 작업의 효율성을 위해서 Full Table Scan
을 선택할 가능성이 커지게 된다.
The required block size for Oracle Applications Release 11i is 8K. No other block size may be used.