문서유형ㅣ기술정보
분야ㅣ관리/환경설정
적용제품버전ㅣTibero 7.2.4
문서번호ㅣTADTI103
개요
본 문서에서는 플랜을 검색하여 특정 유형의 플랜이 들어있는 쿼리를 찾는 방법을 예시를 통해 안내합니다.
방법
1. hash join 중 rowid 검색 쿼리 추출
추출 쿼리
SELECT (SELECT username
FROM dba_users
WHERE user_id = a.parsing_user_id) AS parsing_username,
a.last_active_time,
a.sql_text
FROM v$sqlarea a,
( SELECT DISTINCT bb.sql_id
FROM v$sql_plan bb,
( SELECT DISTINCT sql_id
FROM v$sql_plan
WHERE UPPER(operation) LIKE 'HASH JOIN%'
AND UPPER(access_predicates) LIKE '%ROWID%'
) cc
WHERE bb.sql_id = cc.sql_id
AND bb.object_owner NOT IN ('SYS')
) b
WHERE a.sql_id = b.sql_id
AND (UPPER(a.sql_text) LIKE '%ROWID%');
수행 예시
-- 테스트 데이터 생성
SQL> conn tibero/tmax
Connected to Tibero.
SQL> CREATE TABLE t1 AS SELECT LEVEL id, RPAD('A',100,'A') data FROM dual CONNECT BY LEVEL <= 1000;
Table 'T1' created.
SQL> CREATE TABLE t2 AS SELECT LEVEL id, RPAD('B',100,'B') data FROM dual CONNECT BY LEVEL <= 1000;
Table 'T2' created.
SQL> CREATE INDEX t1_idx ON t1(id);
Index 'T1_IDX' created.
SQL> CREATE INDEX t2_idx ON t2(id);
Index 'T2_IDX' created.
-- 쿼리 수행 및 플랜 확인
SQL> SET AUTOT TRACEONLY
SELECT /*+ USE_HASH(t1 t2) */
t1.data
FROM t1
WHERE t1.ROWID IN (
SELECT ROWID FROM t2 WHERE t2.id < 10
);
SQL ID: au8yy6kdc1hp1
Child number: 239
Plan hash value: 3354623698
Execution Plan
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 HASH JOIN (Cost:14, %%CPU:0, Rows:12)
2 INDEX (RANGE SCAN): T2_IDX (Cost:2, %%CPU:0, Rows:12)
3 TABLE ACCESS (FULL): T1 (Cost:12, %%CPU:0, Rows:1000)
Predicate Information
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access: ("T1"."ROWID" = "T2"."ROWID") (0.002)
2 - access: ("T2"."ID" < 10) (0.012)
Note
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 - dynamic sampling used for this table (20 blocks)
3 - dynamic sampling used for this table (20 blocks)
NAME VALUE
------------------------------ ----------
db block gets 208
consistent gets 228
physical reads 1
redo size 0
sorts (disk) 0
sorts (memory) 2
rows processed 0
-- hash join 중 rowid 검색 쿼리 조회
SQL> COL PARSING_USERNAME FOR A20
SQL> COL LAST_ACTIVE_TIME FOR A25
SQL> COL SQL_TEXT FOR A60
SQL> SELECT (SELECT username
FROM dba_users
WHERE user_id = a.parsing_user_id) AS parsing_username,
a.last_active_time,
a.sql_text
FROM v$sqlarea a,
( SELECT DISTINCT bb.sql_id
FROM v$sql_plan bb,
( SELECT DISTINCT sql_id
FROM v$sql_plan
WHERE UPPER(operation) LIKE 'HASH JOIN%'
AND UPPER(access_predicates) LIKE '%ROWID%'
) cc
WHERE bb.sql_id = cc.sql_id
AND bb.object_owner NOT IN ('SYS')
) b
WHERE a.sql_id = b.sql_id
AND (UPPER(a.sql_text) LIKE '%ROWID%');
PARSING_USERNAME LAST_ACTIVE_TIME SQL_TEXT
-------------------- ------------------------- ------------------------------------------------------------
TIBERO 2025/10/27 SELECT /*+ USE_HASH(t1 t2) */
t1.data
FROM t1
WHERE t1.ROWID IN (
SELECT ROWID FROM t2 WHERE t2.id < 10
)
1 row selected.
2. hash join 아래 2 개의 index scan 가 있는 쿼리 추출
추출 쿼리
SELECT DISTINCT s.sql_id,
(SELECT username FROM dba_users WHERE user_id = s.parsing_user_id) AS parsing_username,
s.last_active_time,
s.sql_text
FROM v$sql s
WHERE s.sql_id IN (
SELECT b.sql_id
FROM v$sql_plan b
WHERE UPPER(b.operation) LIKE 'HASH JOIN%'
GROUP BY b.sql_id, b.child_number, b.id
HAVING COUNT(
CASE
WHEN EXISTS (
SELECT 1
FROM v$sql_plan a
WHERE a.sql_id = b.sql_id
AND a.child_number = b.child_number
AND a.parent_id = b.id
AND UPPER(a.operation) LIKE 'INDEX%'
)
THEN 1
END
) = 1
AND (
SELECT COUNT(*)
FROM v$sql_plan a
WHERE a.sql_id = b.sql_id
AND a.child_number = b.child_number
AND a.parent_id = b.id
AND UPPER(a.operation) LIKE 'INDEX%'
) = 2
)
ORDER BY s.last_active_time DESC;
수행 예시
-- 테이블 및 인덱스는 1-2와 동일한 테이블 사용
-- 쿼리 수행 및 플랜 확인
SQL> SELECT /*+ USE_HASH(t1 t2) INDEX(t1 T1_IDX) INDEX(t2 T2_IDX) */ t1.id
FROM t1, t2
WHERE t1.id = t2.id;
SQL ID: 4rsgs8sx2ubcu
Child number: 289
Plan hash value: 943179889
Execution Plan
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 HASH JOIN (Cost:24, %%CPU:0, Rows:1194)
2 INDEX (FULL): T2_IDX (Cost:4, %%CPU:0, Rows:1000)
3 TABLE ACCESS (ROWID): T1 (Cost:20, %%CPU:0, Rows:1000)
4 INDEX (FULL): T1_IDX (Cost:4, %%CPU:0, Rows:1000)
Predicate Information
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access: ("T2"."ID" = "T1"."ID") (0.001)
Note
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 - dynamic sampling used for this table (20 blocks)
4 - dynamic sampling used for this table (20 blocks)
NAME VALUE
------------------------------ ----------
db block gets 204
consistent gets 125
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 2
rows processed 1000
SQL> col sql_id for a20
SQL> col parsing_username for a20
SQL> col last_active_time for a25
SQL> col sql_text for a50
SQL>SELECT DISTINCT s.sql_id,
(SELECT username FROM dba_users WHERE user_id = s.parsing_user_id) AS parsing_username,
s.last_active_time,
s.sql_text
FROM v$sql s
WHERE s.sql_id IN (
SELECT b.sql_id
FROM v$sql_plan b
WHERE UPPER(b.operation) LIKE 'HASH JOIN%'
GROUP BY b.sql_id, b.child_number, b.id
HAVING COUNT(
CASE
WHEN EXISTS (
SELECT 1
FROM v$sql_plan a
WHERE a.sql_id = b.sql_id
AND a.child_number = b.child_number
AND a.parent_id = b.id
AND UPPER(a.operation) LIKE 'INDEX%'
)
THEN 1
END
) = 1
AND (
SELECT COUNT(*)
FROM v$sql_plan a
WHERE a.sql_id = b.sql_id
AND a.child_number = b.child_number
AND a.parent_id = b.id
AND UPPER(a.operation) LIKE 'INDEX%'
) = 2
)
ORDER BY s.last_active_time DESC;
SQL_ID PARSING_USERNAME LAST_ACTIVE_TIME SQL_TEXT
-------------------- -------------------- ------------------------- --------------------------------------------------
23ngd7705wmzc TIBERO 2025/11/06 SELECT /*+ USE_HASH(t1 t2) INDEX(t1 T1_IDX) INDEX(
t2 T2_IDX) */ t1.id
FROM t1, t2
WHERE t1.id = t2.id
1 rows selected.