Oracle 사의 Product Manager인 Nigel Bayliss가 포스팅한 New SQL Analysis Report in Oracle Database 23c Free! 글에서 Oracle 23c에 SQL Analysis Report 기능이 추가된 것을 알 수 있습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2) AS SELECT ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000');
CREATE INDEX t1_x1 ON t1 (c1);
아래 쿼리는 조인 조건이 없어 cartesian product이 발생합니다. 실행 계획 하단에 조인 조건을 추가하나 테이블 제외를 고려하라는 SQL Analysis Report 항목이 표시되는 것을 확인할 수 있습니다.
-- 2
EXPLAIN PLAN SET statement_id = '2' FOR SELECT a.c1, b.c1 FROM t1 a, t1 b;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100M| 762M| 288K (1)| 00:00:12 |
| 1 | MERGE JOIN CARTESIAN| | 100M| 762M| 288K (1)| 00:00:12 |
| 2 | TABLE ACCESS FULL | T1 | 10000 | 40000 | 30 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 10000 | 40000 | 288K (1)| 00:00:12 |
| 4 | TABLE ACCESS FULL | T1 | 10000 | 40000 | 29 (0)| 00:00:01 |
-----------------------------------------------------------------------------
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.
아래 쿼리는 UNION 연산자를 사용합니다. UNION 대신 UNION ALL 사용을 고려하라는 SQL Analysis Report 항목이 표시됩니다.
-- 3
EXPLAIN PLAN SET statement_id = '3' FOR SELECT * FROM t1 UNION SELECT * FROM t1;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 2050K| 545 (1)| 00:00:01 |
| 1 | HASH UNIQUE | | 20000 | 2050K| 545 (1)| 00:00:01 |
| 2 | UNION-ALL | | 20000 | 2050K| 545 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 10000 | 1025K| 30 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 10000 | 1025K| 30 (0)| 00:00:01 |
----------------------------------------------------------------------------
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
1 - SET$1
- The query block contains UNION which may be expensive.
Consider using UNION ALL if duplicates are allowed or
uniqueness is guaranteed.
아래 쿼리는 c1 칼럼을 가공하여 인덱스를 사용하지 못합니다. "C1" 조건의 재작성을 고려하라는 SQL Analysis Report 항목이 표시됩니다.
-- 4
EXPLAIN PLAN SET statement_id = '4' FOR SELECT * FROM t1 WHERE c1 + 0 = 1;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 30 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 105 | 30 (0)| 00:00:01 |
--------------------------------------------------------------------------
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.
"C1"
SQL Analysis Report 데이터는 other_xml 칼럼에 저장되어 있습니다. 저장된 유형(CP, UN, PR)과 속성(CDDATA)에 따라 DBMS_XPLAN 패키지에서 SQL Analysis Report 항목을 표시하는 것으로 보입니다.
-- 5
SELECT statement_id
, XMLSERIALIZE (CONTENT EXTRACT (XMLTYPE (other_xml), 'other_xml/sql_analysis')) AS sql_analysis
FROM plan_table
WHERE other_xml IS NOT NULL;
STATEMENT_ID SQL_ANALYSIS
------------ ---------------------------------------------------------------------------------------------------------------------------------
2 <sql_analysis><q><n><![CDATA[SEL$1]]></n><m t="CP"><a p="1"><![CDATA[1]]></a></m></q></sql_analysis>
3 <sql_analysis><q><n><![CDATA[SET$1]]></n><m t="UN"/></q></sql_analysis>
4 <sql_analysis><q><n><![CDATA[SEL$1]]></n><t><f><![CDATA["T1"@"SEL$1"]]></f><m t="PR"><a p="1"><![CDATA["C1"]]></a></m></t></q>...
3 행이 선택되었습니다.
V$SQL_PLAN 뷰의 other_xml 칼럼에도 SQL Analysis Report 데이터가 저장되어 있습니다. 아래 쿼리로 캐싱된 커서의 SQL Analysis Report를 유형을 조회하여 튜닝 대상을 추출할 수 있습니다.
-- 6
SELECT DISTINCT
b.type
, a.sql_id
FROM v$sql_plan a
, XMLTABLE ('other_xml/sql_analysis/q/m' PASSING XMLTYPE (a.other_xml) COLUMNS type VARCHAR2(4000) PATH '@t') b
WHERE a.other_xml IS NOT NULL
AND EXISTSNODE (XMLTYPE (a.other_xml), 'other_xml/sql_analysis') = 1
ORDER BY b.type
, a.sql_id;
참고로 이 기능은 _sql_analysis 파라미터로 제어할 수 있습니다.
-- 7
NAME VALUE DEFAULT_VALUE DESCRIPTION
------------- ----- ------------- -----------------------------------------
_sql_analysis TRUE TRUE enable SQL analysis for reporting in plan