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 ..
문자 값이 암시적 데이터 변환에 의해 날짜 값으로 변환되면 INTERNAL_FUNCTION로 표시되고 스토리지 인덱스를 사용할 수 없게 됩니다. 이와 관련하여 예전에 암시적 데이터 변환과 오프로딩 글을 작성했습니다. 앞서 작성한 IN 조건과 INTERNAL_FUNCTION 글도 INTERNAL_FUNCTION으로 표시된 IN 조건의 스토리지 인덱스 사용 여부에 대한 검증을 위해 작성했습니다. 테스트를 위해 아래와 같이 테이블을 생성하고, _serial_direct_read 파라미터를 ALWAYS로 설정하겠습니다. -- 1-1 DROP TABLE t1 PURGE; CREATE TABLE t1 (c1, c2, c3) NOLOGGING AS SELECT 1, CEIL (ROWNUM / 1000000), LPA..
특정한 경우 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 조..
테이블을 HCC 압축하면 인덱스를 통해 테이블을 액세스하는 쿼리의 블록 I/O가 증가하고 이로 인해 쿼리의 성능이 저하될 수 있습니다. 테스트를 위해 아래와 같이 테이블을 생성하겠습니다. -- 1 DROP TABLE t1 PURGE; CREATE TABLE t1 ( c01, c02, c03, c04, c05, c06, c07, c08, c09, c10 , c11, c12, c13, c14, c15, c16, c17, c18, c19, c20 , c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 ) NOLOGGING PARALLEL 10 PARTITION BY RANGE (c01) ( PARTITION p01 VALUES LESS THAN ( 2) , PARTITION..
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..
