SCN_ASCENDING 힌트는 문서화되지 않은 힌트로 Bug 6688108 - Enhancement: Allow ORA-1555 to be ignored during table scan (Doc ID 6688108.8) 문서에 아래의 설명이 짧게 포함되어 있습니다. 설명에 따르면 이 힌트를 기술한 쿼리는 Undo 재사용으로 인해 Consistent Read가 불가능한 경우 ORA-1555 에러를 발생시키지 않고 새로운 쿼리(snapshot) SCN으로 블록을 액세스하며, 이로 인해 읽기 일관성을 보장하지 않습니다.
ORA-1555 can occur during long running queries involving a ROWID Range or full table scan if the undo has recycled. This error is normal and expected. This fix is a special enhancement to allow an SCN_ASCENDING hint to be supplied in the SQL statement. With this hint the select will take a new snapshot SCN and continue the scan rather than reporting an ORA-1555 to the caller. This can lead to inconsistent results as the SELECT is allowed to see data which is not all consistent to one point in time.
V$SQL_HINT 뷰에 따르면 이 힌트는 8.1.5 버전에 추가된 것으로 보입니다. 병렬 쿼리에 이 힌트를 사용하면 Direct Path Read가 동작하지 않아 성능 저하가 발생할 수 있습니다.
-- 1
SELECT name, target_level, version FROM v$sql_hint WHERE name = 'SCN_ASCENDING';
NAME TARGET_LEVEL VERSION
------------- ------------ -------
SCN_ASCENDING 1 8.1.5
1개의 행이 선택되었습니다.
테스트를 위해 아래와 같이 테이블을 생성하고, 테이블을 조회하겠습니다.
-- 2-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1) AS SELECT ROWNUM FROM XMLTABLE ('1 to 1000000');
-- 2-2
SELECT COUNT (*) FROM t1;
아래 예제에서 두 병렬 쿼리의 실행 계획은 동일하지만 SCN_ASCEDING 힌트를 사용한 3-1번 쿼리는 Conventional Path Read로 동작하여 물리 I/O가 발생하지 않은 것을 확인할 수 있습니다. 병렬 쿼리는 기본적으로 버퍼 캐시를 우회하여 I/O를 수행하는 Direct Path Read로 동작합니다.
-- 3-1
SELECT /*+ PARALLEL(2) SCN_ASCENDING */ COUNT (*) FROM t1;
Parallel Execution Details (DOP=2 , Servers Allocated=2)
=============================================
| Name | Type | Server# | Buffer |
| | | | Gets |
=============================================
| PX Coordinator | QC | | 5 |
| p000 | Set 1 | 1 | 808 |
| p001 | Set 1 | 2 | 819 |
=============================================
SQL Plan Monitoring Details (Plan Hash Value=3110199320)
=======================================================
| Id | Operation | Name | Rows |
| | | | (Actual) |
=======================================================
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | PX COORDINATOR | | 2 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 2 |
| 4 | SORT AGGREGATE | | 2 |
| 5 | PX BLOCK ITERATOR | | 1M |
| 6 | TABLE ACCESS FULL | T1 | 1M |
=======================================================
-- 3-2
SELECT /*+ PARALLEL(2) */ COUNT (*) FROM t1;
Parallel Execution Details (DOP=2 , Servers Allocated=2)
============================================================
| Name | Type | Server# | Buffer | Read | Read |
| | | | Gets | Reqs | Bytes |
============================================================
| PX Coordinator | QC | | 5 | | . |
| p000 | Set 1 | 1 | 829 | 21 | 6MB |
| p001 | Set 1 | 2 | 720 | 26 | 6MB |
============================================================
SQL Plan Monitoring Details (Plan Hash Value=3110199320)
==============================================================================
| Id | Operation | Name | Execs | Rows | Read | Read |
| | | | | (Actual) | Reqs | Bytes |
==============================================================================
| 0 | SELECT STATEMENT | | 1 | 1 | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | |
| 2 | PX COORDINATOR | | 3 | 2 | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 2 | 2 | | |
| 4 | SORT AGGREGATE | | 2 | 2 | | |
| 5 | PX BLOCK ITERATOR | | 2 | 1M | | |
| 6 | TABLE ACCESS FULL | T1 | 26 | 1M | 47 | 12MB |
==============================================================================
아래는 앞선 쿼리의 세션 통계를 비교한 결과입니다. SCN_ASCEDING 힌트를 사용한 3-1번 쿼리의 경우 Direct Path Read로 동작하지 않는 것을 확인할 수 있습니다. Exadata의 경우 Smart Scan이 동작하지 않으므로 심각한 성능 저하가 발생할 수 있습니다.
-- 4
+---------------------------+-------+-------+------+
|NAME | # 3-1| # 3-2|CLASS |
+---------------------------+-------+-------+------+
|consistent gets | 1.63K| 1.55K|Cache |
|consistent gets direct | 0.00 | 1.52K|Cache | -- !
|consistent gets from cache | 1.63K| 31.00 |Cache | -- !
|physical reads | 0.00 | 1.52K|Cache |
|physical reads direct | 0.00 | 1.52K|Cache |
+---------------------------+-------+-------+------+
관련 링크