OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.5.0.24.07 ai for Oracle Cloud and Engineered Systems
방법 : 오라클 23ai 신기능 xplan의 SQL Analysis Report
오라클 23ai xplan에 SQL Analysis Report가 추가됨
이 기능은 sql 작성시 실수로 작성된 부분이 있을때 오라클이 찾아줌으로써 쿼리 작성에 도움을 주는 기능임
본문에서는 예제와 함께 설명하겠음
테스트
1. 사전 설정
2. 예제1 조인구문 미작성시
3. 예제2 union 사용시
4. 예제3 조건절 좌변을 가공하여 인덱스 미사용시
테스트
1. 사전 설정
샘플 테이블 생성 및 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL>
drop table t1;
drop table t2;
create table t1 (col1 number, col2 varchar2(10));
create table t2 (col1 number, col2 varchar2(10));
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO t1 (col1, col2)
VALUES (i, DBMS_RANDOM.STRING('A', 10));
END LOOP;
FOR i IN 101..200 LOOP
INSERT INTO t2 (col1, col2)
VALUES (i, DBMS_RANDOM.STRING('A', 10));
END LOOP;
COMMIT;
END;
/
create index t1_ix1 on t1(col1);
create index t1_ix2 on t1(col2);
create index t2_ix1 on t2(col1);
create index t2_ix2 on t2(col2);
|
테스트 전 statistics_level파라미터 all로 변경
|
1
2
3
|
SQL> alter session set statistics_level = all;
Session altered.
|
2. 예제1 조인구문 미작성시
현재 쿼리에 t1, t2 테이블의 조인 구문이 없는 상태임
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
select t1.col1, t2.col2
from t1, t2;
COL1 COL2
---------- ----------
1 QaUaPzuXvY
1 cgwoPYYdyc
..
99 iKLDjfnjse
100 iKLDjfnjse
10000 rows selected.
|
카테시안곱이 발생해 각각 100건씩 있는 테이블 데이터가 10000건으로 늘어남
dbms_xplan.display_cursor 결과 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 140 (100)| | 1 |00:00:00.01 | 14 |
| 1 | SORT AGGREGATE | | 1 | 1 | 26 | | | 1 |00:00:00.01 | 14 |
| 2 | MERGE JOIN CARTESIAN| | 1 | 10000 | 253K| 140 (0)| 00:00:01 | 10000 |00:00:00.01 | 14 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 100 | 1300 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 7 |
| 4 | BUFFER SORT | | 100 | 100 | 1300 | 137 (0)| 00:00:01 | 10000 |00:00:00.01 | 7 |
| 5 | TABLE ACCESS FULL | T2 | 1 | 100 | 1300 | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 7 |
------------------------------------------------------------------------------------------------------------------------
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
1 - SEL$1
- The query block has 1 cartesian product which may be
expensive. Consider adding join conditions or removing the
disconnected tables or views.
|
카테시안곱이 사용되었고 SQL Analysis Report에서 가이드를 보여주고 있음
원문 : The query block has 1 cartesian product which may be expensive. Consider adding join conditions or removing the disconnected tables or views.
번역 : 쿼리 블록에는 비용이 많이 들 수 있는 카테시안 곱이 하나 있습니다. 조인 조건을 추가하거나 연결되지 않은 테이블이나 뷰를 제거하는 것을 고려해 보세요.
3. 예제2 union 사용시
union을 넣어 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
select * from (
select col1 from t1
union
select col1 from t2);
COL1
----------
1
2
..
199
200
200 rows selected.
|
dbms_xplan.display_cursor 결과 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 9 (100)| | 200 |00:00:00.01 | 14 |
| 1 | VIEW | | 1 | 200 | 2600 | 9 (34)| 00:00:01 | 200 |00:00:00.01 | 14 |
| 2 | HASH UNIQUE | | 1 | 200 | 2600 | 9 (34)| 00:00:01 | 200 |00:00:00.01 | 14 |
| 3 | UNION-ALL | | 1 | 200 | 2600 | 9 (34)| 00:00:01 | 200 |00:00:00.01 | 14 |
| 4 | TABLE ACCESS FULL| T1 | 1 | 100 | 1300 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 7 |
| 5 | TABLE ACCESS FULL| T2 | 1 | 100 | 1300 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 7 |
-----------------------------------------------------------------------------------------------------------------------
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
2 - SET$1
- The query block contains UNION which may be expensive.
Consider using UNION ALL if duplicates are allowed or
uniqueness is guaranteed.
|
현재 쿼리에는 중복값이 있어서 정렬 연산이 필요하지만 만약 중복값이 없는 쿼리의 경우 union 대신 union all을 사용해 정렬 연산을 제거할 수 있음
SQL Analysis Report에서 이러한 가이드를 보여주고 있음
원문 : The query block contains UNION which may be expensive. Consider using UNION ALL if duplicates are allowed or uniqueness is guaranteed.
번역 : 쿼리 블록에는 비용이 많이 들 수 있는 UNION이 포함되어 있습니다. 중복이 허용되거나 고유성이 보장되는 경우 UNION ALL을 사용하는 것을 고려해 보세요.
4. 예제3 조건절 좌변을 가공하여 인덱스 미사용시
조건절 좌변을 가공하는 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
select col1
from t1
where substr(col2,1,1) = 'A';
COL1
----------
37
44
58
|
dbms_xplan.display_cursor 결과 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 3 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 60 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 8 |
--------------------------------------------------------------------------------------------------------------------
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
1 - SEL$1 / "T1"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"COL2"
|
col2 컬럼에 인덱스가 있지만 substr 함수를 사용하여 인덱스를 사용하지 못하고 full 스캔을 하고 있음, 이 경우 좌변 컬럼을 가공하지 않고 like를 사용해 인덱스를 사용할 수 있음
SQL Analysis Report에서 이러한 가이드를 보여주고 있음
원문 : The following columns have predicates which preclude their use as keys in index range scan. Consider rewriting the predicates. "COL2"
번역 : 다음 열에는 인덱스 범위 스캔에서 키로 사용할 수 없는 술어가 있습니다. 술어를 다시 작성하는 것을 고려해 보세요. "COL2"
like를 사용해 조건전 좌변을 가공하지 않는 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
select col1
from t1
where col2 like 'A%';
COL1
----------
37
44
58
|
dbms_xplan.display_cursor 결과 확인
|
1
2
3
4
5
6
7
8
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 3 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 3 | 60 | 2 (0)| 00:00:01 | 3 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | T1_IX2 | 1 | 3 | | 1 (0)| 00:00:01 | 3 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------------------------------------------
|
정상적으로 index range scan을 함
참고로 v$sql의 plan_table_output 컬럼을 조건절로 넣어 like 로 검색할수도 있음
아래의 경우는 카테시안곱이 사용된 t1 테이블이 포함된 쿼리를 찾는 예제 쿼리임
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
set lines 200 pages 1000
select distinct sql_id, child_number, sql_text
from v$sql a,
table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>a.sql_id)) b
where b.plan_table_output like '%block has 1 cartesian product%'
and sql_text like '%t1%'
order by sql_id, child_number;
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
03ycjakugsc9m 0
select t1.col1, t2.col2 from t1, t2
3cx5r1uhd7ywp 0
select sum(t1.col1), sum(t2.col1) from t1, t2
7j5g66ajym2f4 0
select /*+ gather_plan_statistics */ sum(t1.col1), sum(t2.col1) from t1, t2
|
결론 :
테스트 결과처럼 SQL Analysis Report에서 쿼리의 오류율을 줄일수 있는 가이드들을 잘 알려줌
오라클 23ai에서는 쿼리 작성시 xplan의 정보들만 잘 확인해도 쿼리의 오류율이 많이 내려갈것으로 보임
참조 :
https://blogs.oracle.com/optimizer/post/sql-analysis-report-in-23c-free
https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/influencing-the-optimizer.html#GUID-A2CEDBF8-948F-4C84-8063-DF8A13D476AD
https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_XPLAN.html#GUID-BAD480AA-351A-48FE-A8E7-F0D8EF643EBF
https://sqlmaria.com/2023/10/11/better-diagnostics-for-sql-regressions-in-19c-and-beyond-joelkallmanday/
https://positivemh.tistory.com/775
https://positivemh.tistory.com/1189
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 xplan의 Hint Report 개선 (0) | 2025.01.27 |
|---|---|
| 오라클 AHF Fleet Insights 구성 가이드 (0) | 2025.01.12 |
| Oracle 23ai New Features Part 2 (Oct 2024) (0) | 2025.01.01 |
| Oracle 23ai New Features Part 1 (June 2024) (0) | 2025.01.01 |
| 오라클 23ai 신기능 Lock Free Reservation (0) | 2024.12.24 |