SQL Tuning Guide는 SQL 파싱이 Syntax Check -> Semantic Check -> Shared Pool Check 순서로 수행된다고 설명하고 있지만 실제로는 Shared Pool Check가 가장 먼저 수행되는 것으로 보입니다. 테스트를 위해 아래와 같이 Shared Pool을 Flush하겠습니다. -- 1-1 ALTER SYSTEM FLUSH SHARED_POOL; -- 1-2: SYS SELECT kglnaobj, kglhdnsd, kglobt03, kglobhd6 FROM x$kglcursor WHERE kglhdadr != kglhdpar AND kglnaobj IN ('SELECT * FORM T1', 'SELECT * FROM T1'); no rows selected ..

지난 COLUMN_STATS 힌트 글에 이어 INDEX_STATS 힌트의 동작을 살펴보겠습니다. INDEX_STATS 힌트의 구문은 아래와 같습니다. /*+ INDEX_STATS(tablespec indexspec method adjustment) */ method에 아래의 항목을 사용할 수 있습니다. { SET | SCALE | SAMPLE | DEFAULT } adjustment는 아래의 형식으로 사용하며 다수의 항목을 함께 사용할 수 있습니다. { BLOCKS | KEYS | CLUSTERING_FACTOR | INDEX_ROWS } = value 테스트를 위해 아래과 같이 테이블과 인덱스를 생성하고 통계정보를 수집하겠습니다. -- 1 DROP TABLE t1 PURGE; CREATE TABLE t..

지난 TABLE_STATS 힌트 글에 이어 COLUMN_STATS 힌트의 동작을 살펴보겠습니다. COLUMN_STATS 힌트의 구문은 아래와 같습니다. /*+ COLUMN_STATS(tablespec columnspec method adjustment) */ method에 아래의 항목을 사용할 수 있습니다. { SET | SCALE | SAMPLE | DEFAULT } adjustment는 아래의 형식으로 사용하며 다수의 항목을 함께 사용할 수 있습니다. { DISTINCT | NULLS | LENGTH | MIN | MAX } = value 테스트를 위해 아래과 같이 테이블을 생성하고 통계정보를 수집하겠습니다. -- 1 DROP TABLE t1 PURGE; CREATE TABLE t1 (c1 NUMBE..

OPT_ESTIMATE 힌트처럼 아래의 세 가지 힌트도 SQL Tuning Advisor(STA)가 내부적으로 사용하는 문서화되지 않은 힌트입니다. 이번 글에서 TABLE_STATS 힌트의 동작을 살펴보고, 다음 글에서 COLUMN_STATS, INDEX_STATS 힌트를 살펴보겠습니다. SELECT name , RTRIM (CASE WHEN BITAND (target_level, 1) != 0 THEN 'STATEMENT + ' END || CASE WHEN BITAND (target_level, 2) != 0 THEN 'QUERY BLOCK + ' END || CASE WHEN BITAND (target_level, 4) != 0 THEN 'OBJECT + ' END || CASE WHEN BITAN..

APEX 개발에 사용하기 위해 Ref 커서를 입력받아 Unpivot한 결과를 반환하는 Pipelined Function을 작성했습니다. 아래와 같이 패키지를 생성하겠습니다. DBMS_SQL.TO_CURSOR_NUMBER 프로시저로 Ref 커서를 DBMS_SQL 커서로 변경하는 부분을 제외하면 Tom Kyte가 개발한 print_table 프로시저와 동작 방식이 동일합니다. -- 1-1 CREATE OR REPLACE PACKAGE pkg_unpivot IS TYPE trd IS RECORD ( name VARCHAR2(4000) , value VARCHAR2(4000) ); TYPE tnt IS TABLE OF trd; FUNCTION fnc_get_value ( i_cursor IN SYS_REFCUR..

OPT_ESTIMATE 힌트는 SQL Tuning Advisor(STA)가 내부적으로 사용하는 문서화되지 않은 힌트입니다. SELECT name , RTRIM (CASE WHEN BITAND (target_level, 1) != 0 THEN 'STATEMENT + ' END || CASE WHEN BITAND (target_level, 2) != 0 THEN 'QUERY BLOCK + ' END || CASE WHEN BITAND (target_level, 4) != 0 THEN 'OBJECT + ' END || CASE WHEN BITAND (target_level, 8) != 0 THEN 'JOIN + ' END, '+ ') AS target_level , version FROM v$sql_hint ..

특정한 경우 DBMS_XPLAN.DISPLAY_CURSOR 결과의 Predicate Information 항목에서 IN 조건이 INTERNAL_FUNCTION으로 표시될 수 있습니다. 테스트를 위해 아래와 같이 테이블을 생성하겠습니다. -- 1 DROP TABLE t1 PURGE; CREATE TABLE t1 (c1 NOT NULL, c2 NOT NULL, c3 NOT NULL, c4 NOT NULL, c5 NOT NULL) AS SELECT ROWNUM, ROWNUM, ROWNUM, ROWNUM, ROWNUM FROM XMLTABLE ('1 to 1000000'); CREATE INDEX t1_x1 ON t1 (c3, c4, c5); 아래 쿼리는 Predicate Information 항목에서 IN 조..

SQL Patch로 CLUSTER_BY_ROWID 힌트를 적용하여 성능을 개선한 사례를 살펴보겠습니다. 글을 작성한 이후 2019년에 이미 유사한 내용의 글을 작성한 것을 알았습니다. 테스트를 위해 아래와 같이 테이블을 생성하겠습니다. -- 1 DROP TABLE t1 PURGE; CREATE TABLE t1 (c1, c2) AS SELECT NVL (NULLIF (MOD (ROWNUM, 100), 0), 100), LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000'); CREATE INDEX t1_x1 ON t1 (c1); 아래 예제는 나쁜 CF로 인해 1번 오퍼레이션에서 10,000개의 블록 I/O가 발생합니다. -- 2 SELECT /*+ INDEX(A) */ *..

OR Expansion을 사용하면 부정형 조건의 성능을 개선할 수 있습니다. 글을 작성한 후 2018년에 유사한 내용의 글(인덱스와 부정형 조건)을 작성한 사실을 알았습니다. 테스트를 위해 아래와 같이 테이블을 생성하겠습니다. -- 1 DROP TABLE t1 PURGE; CREATE TABLE t1 (c1, c2, c3) AS SELECT CEIL (ROWNUM / 100000), DECODE (MOD (ROWNUM, 100000), 0, 'N', 'Y'), LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 1000000'); CREATE INDEX t1_x1 ON t1 (c1, c2); 아래 쿼리는 c2 != 'Y' 조건으로 인해 인덱스 액세스 단계에서 266개의 블록 I/O..

Subquery Pushing으로 동작하는 비상관 단일 행 서브쿼리를 액세스 조건으로 사용하면서브 쿼리가 Execute Call 단계에서 수행됩니다. 이를 통해 쿼리의 성능을 개선한 사례를 살펴보겠습니다. 테스트를 위해 아래와 같이 테이블과 인덱스를 생성하겠습니다. -- 1 DROP TABLE t1 PURGE; DROP TABLE t2 PURGE; CREATE TABLE t1 (c1, c2, c3) AS SELECT CEIL (ROWNUM / 100000), NVL (NULLIF (MOD (ROWNUM, 100000), 0), 100000), LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 1000000'); CREATE TABLE t2 (c1, c2, c3) AS SELECT..

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 ..

UNUSABLE 인덱스 파티션에 의한 Join Factorization 쿼리 변환으로 인해 쿼리의 성능이 저하된 사례를 살펴보겠습니다. 참고로 이 글은 PPWJ 성능 저하 사례와 관련이 있습니다. 테스트를 위해 아래와 같이 테이블을 생성하겠습니다. t1은 비파티션 테이블, t2는 파티션 테이블입니다. t1 테이블에는 t2 테이블의 p1, p2 파티션에 해당하는 데이터가 저장되어 있습니다. -- 1 DROP TABLE t1 PURGE; DROP TABLE t2 PURGE; CREATE TABLE t1 (c1, c2) AS SELECT CEIL ((ROWNUM + 600000) / 100000), ROWNUM + 600000 FROM XMLTABLE ('1 to 200000'); CREATE TABLE t2..