ORACLE SQL ADVISOR
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 SQL Advisor 사용법[편집]
1.1 튜닝 샘플 테이블[편집]
CREATE TABLE T_OFFSET
AS
SELECT A.LV, ROWNUM AS RID, OBJECT_TYPE, OBJECT_NAME
FROM DBA_OBJECTS , (
SELECT LEVEL LV
FROM DUAL
CONNECT BY LEVEL <=10000
)A ;1.2 통계정보 갱신[편집]
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'T_OFFSET', CASCADE => TRUE, ESTIMATE_PERCENT =>'10', DEGREE => '1', granularity => 'ALL');select count(*) from t_offset;T_OFFSET
--------
Rows=544,377,340 Blocks=3,530,057
Empty_Blocks=0 Avg_Space=0
Chain_Count=0 Avg_Row_Length=41
Avg_Space_Freelist_Blocks=0 Freelist_Blocks=0
Sample_Size=54,437,734 Last_Analyzed='2020-04-01 11:02:23'
Partitioned='NO' Tablespace='USERS'
Column Name Nullable Column Type Distinct Buckets
-------------------------------- -------- ------------------- ---------- ----------
LV NUMBER 10,000 1
RID NUMBER 544,377,340 1
OBJECT_TYPE VARCHAR2(23) 41 1
OBJECT_NAME VARCHAR2(128) 24,006 1
INDEX
-----------------------------------------------------------------------------
'No Index'1.3 플랜 정보[편집]
- SQL ID 찾기
SELECT *
FROM (SELECT rid, rownum AS rnum
FROM (SELECT *
FROM T_OFFSET
ORDER BY rid)
WHERE rownum <= 100
)
WHERE rnum >= 90;SQL_ID ca44dx44qrq6s, child number 0
-------------------------------------
SELECT * FROM (SELECT rid, rownum AS rnum FROM (SELECT *
FROM T_OFFSET ORDER BY rid)
WHERE rownum <= 100 ) WHERE rnum >= 90
Plan hash value: 1388389564
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 6733K(100)| | 11 |00:01:58.32 | 3526K| 3526K| | | |
|* 1 | VIEW | | 1 | 100 | 2600 | | 6733K (1)| 00:04:24 | 11 |00:01:58.32 | 3526K| 3526K| | | |
|* 2 | COUNT STOPKEY | | 1 | | | | | | 100 |00:01:58.32 | 3526K| 3526K| | | |
| 3 | VIEW | | 1 | 544M| 6749M| | 6733K (1)| 00:04:24 | 100 |00:01:58.32 | 3526K| 3526K| | | |
|* 4 | SORT ORDER BY STOPKEY| | 1 | 544M| 20G| 28G| 6733K (1)| 00:04:24 | 100 |00:01:58.32 | 3526K| 3526K| 6144 | 6144 | 6144 (0)|
| 5 | TABLE ACCESS FULL | T_OFFSET | 1 | 544M| 20G| | 959K (1)| 00:00:38 | 544M|00:01:12.09 | 3526K| 3526K| | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / from$_subquery$_001@SEL$1
2 - SEL$2
3 - SEL$3 / from$_subquery$_002@SEL$2
4 - SEL$3
5 - SEL$3 / T_OFFSET@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">=90)
2 - filter(ROWNUM<=100)
4 - filter(ROWNUM<=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "from$_subquery$_001"."RID"[NUMBER,22], "RNUM"[NUMBER,22]
2 - "RID"[NUMBER,22], ROWNUM[8]
3 - "RID"[NUMBER,22]
4 - (#keys=1) "T_OFFSET"."RID"[NUMBER,22]
5 - "T_OFFSET"."RID"[NUMBER,22]또는
SELECT SQL_ID , SQL_TEXT, SQL_FULLTEXT
FROM V$SQLAREA
WHERE SQL_FULLTEXT LIKE '%T_OFFSET%';1.4 DBA_ADVISOR_LOG 테이블 확인[편집]
- 현재 어떤 단계인지도 확인
SELECT OWNER, TASK_NAME, EXECUTION_START, EXECUTION_END, STATUS
FROM DBA_ADVISOR_LOG ;1.5 DBMS_SQLTUNE 패키지를 이용하여 SQL문 튜닝 권고[편집]
- Enterprise Edition 에서만 가능
1.5.1 튜닝 가이드를 받기 절차[편집]
- Create Tuning Task ->
- Execute Tuning Task ->
- Tuning advisor Report ->
- Drop tuning Task 전에 반드시 Tuning Task list 확인 후 삭제
1.5.1.1 Create Tuning Task[편집]
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_id => '7r036z8114hd8',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1800,
task_name => '7r036z8114hd8_tuning_task',
description => 'Tuning task for statement 7r036z8114hd8.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/1.5.1.2 Execute Tuning Task[편집]
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'ca44dx44qrq6s_tuning_task');1.5.1.3 Tuning advisor Report[편집]
- sqlplus 상에서 확인.
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
col RECOMMENT FORMAT a350
SELECT DBMS_SQLTUNE.report_tuning_task('ca44dx44qrq6s_tuning_task') AS RECOMMENT FROM dual;
SET PAGESIZE 24SQL*Plus: Release 19.0.0.0.0 - Production on 화 3월 31 16:27:49 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
사용자명 입력: xxxxx/"xxxxx!@"@xxx
마지막 성공한 로그인 시간: 화 3월 31 2020 16:21:12 +09:00
다음에 접속됨:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SET LONG 10000;
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 200
SQL> col RECOMMENT FORMAT a350
SQL> SELECT DBMS_SQLTUNE.report_tuning_task('ca44dx44qrq6s_tuning_task') AS RECOMMENT FROM dual;
RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : ca44dx44qrq6s_tuning_task
Tuning Task Owner : OWNER명
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 04/09/2020 11:13:41
Completed at : 04/09/2020 11:13:42
-------------------------------------------------------------------------------
Schema Name: OWNER명
SQL ID : ca44dx44qrq6s
SQL Text : SELECT *
FROM (SELECT rid, rownum AS rnum
FROM (SELECT *
FROM T_OFFSET
ORDER BY rid)
WHERE rownum <= 100
)
WHERE rnum >= 90
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
이 명령문에 대해 잠재적으로 더 나은 실행 계획이 발견되었습니다.
Recommendation (estimated benefit: 93.05%)
------------------------------------------
- 이 명령문에 병렬 실행을 사용하려면 권장 SQL 프로파일을 그대로 적용하는 것이 좋습니다.
execute dbms_sqltune.accept_sql_profile(task_name =>
'ca44dx44qrq6s_tuning_task', task_owner => 'owner명',
replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);
DOP 16을(를) 사용하여 이 병렬 질의를 실행하면 원래 계획에 대한 응답 시간 93.06%이(가) 향상됩니다. 단, 병렬 실행을
사용으로 설정하면 명령문의 리소스 소비가 11.06%까지 증가하여 시스템 처리량이 줄어들 수 있습니다. 또한 훨씬 짧은 기간에 리소스가
소비되므로 사용 가능한 하드웨어 용량이 부족할 경우 동시 명령문의 응답 시간에 부정적인 영향을 줄 수 있습니다.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- 최적기는 실행 계획의 행 ID 3에서 뷰를 병합할 수 없었습니다. 명령문이 "DELETE" 또는 "UPDATE"이고 상위 질의가
명령문에서 최상위 질의가 아닌 경우 최적기는 "ORDER BY" 절을 포함하는 뷰를 병합할 수 없습니다.
- 최적기는 실행 계획의 행 ID 1에서 뷰를 병합할 수 없었습니다. 최적기는 "ROWNUM" 의사 열을 포함하는 뷰를 병합할 수 없습니다.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1388389564
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2600 | | 6733K (1)| 00:04:24 |
|* 1 | VIEW | | 100 | 2600 | | 6733K (1)| 00:04:24 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 544M| 6749M| | 6733K (1)| 00:04:24 |
|* 4 | SORT ORDER BY STOPKEY| | 544M| 20G| 28G| 6733K (1)| 00:04:24 |
| 5 | TABLE ACCESS FULL | T_OFFSET | 544M| 20G| | 959K (1)| 00:00:38 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">=90)
2 - filter(ROWNUM<=100)
4 - filter(ROWNUM<=100)
2- Using Parallel Execution
---------------------------
Plan hash value: 162111451
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2600 | | 467K (1)| 00:00:19 | | | |
|* 1 | VIEW | | 100 | 2600 | | 467K (1)| 00:00:19 | | | |
|* 2 | COUNT STOPKEY | | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | | |
| 4 | PX SEND QC (ORDER) | :TQ10001 | 544M| 6749M| | 467K (1)| 00:00:19 | Q1,01 | P->S | QC (ORDER) |
| 5 | VIEW | | 544M| 6749M| | 467K (1)| 00:00:19 | Q1,01 | PCWP | |
|* 6 | SORT ORDER BY STOPKEY | | 544M| 20G| 28G| 467K (1)| 00:00:19 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 100 | 1300 | | | | Q1,01 | PCWP | |
| 8 | PX SEND RANGE | :TQ10000 | 100 | 1300 | | | | Q1,00 | P->P | RANGE |
|* 9 | SORT ORDER BY STOPKEY| | 100 | 1300 | | | | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 544M| 20G| | 66590 (1)| 00:00:03 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL | T_OFFSET | 544M| 20G| | 66590 (1)| 00:00:03 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">=90)
2 - filter(ROWNUM<=100)
6 - filter(ROWNUM<=100)
9 - filter(ROWNUM<=100)
-------------------------------------------------------------------------------
SQL> SET PAGESIZE 241.5.1.4 Drop tuning Task[편집]
- 삭제전 내용 확인
SELECT OWNER, TASK_NAME, EXECUTION_START, EXECUTION_END, STATUS
FROM DBA_ADVISOR_LOG WHERE OWNER='owner명';- 삭제 하기
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK (task_name => 'ca44dx44qrq6s_tuning_task');
END;
/