OS환경 : Oracle Linux 6.8(64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : SQL 튜닝시 필요한 스크립트1
1. SQL 튜닝 대상선정하기
|
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
SQL>
col diskreads for 9999999
col bufferget for 9999999
col elapsetime for 9999
col return_rows for 999999
col executions for 999
col LAST_LOAD_TIME for a25
col module for a30
col ACTION for a10
col PARSING_SCHEMA_NAME for a10
select
trunc(a.disk_reads/a.executions,0) diskreads, trunc(a.buffer_gets/a.executions,0) bufferget,
trunc(a.elapsed_time/a.executions/1000000,0) elapsetime,
trunc(a.ROWS_PROCESSED/a.executions,0) return_rows,
a.executions, a.last_load_time, module,action, length(sql_fulltext),
sql_fulltext, address,sql_id,parsing_schema_name
from v$sql a
where executions > 0
and command_type in ( 3, 6,7)
and module not in ( 'SQL Developer','Orange for ORACLE DBA')
and buffer_gets / executions > 1000
order by elapsetime desc ;
DISKREADS BUFFERGET ELAPSETIME RETURN_ROWS EXECUTIONS LAST_LOAD_TIME MODULE ACTION LENGTH(SQL_FULLTEXT)
--------- --------- ---------- ----------- ---------- ------------------------- ------------------------------ ---------- --------------------
SQL_FULLTEXT ADDRESS SQL_ID PARSING_SC
-------------------------------------------------------------------------------- ---------------- ------------- ----------
2 7294 0 1 1 2019-01-25/16:01:57 sqlplus@prsjedb2 (TNS V1-V3) (null) 106
select segment_name 00000000863E9570 64bu81jkjy0wa SYS
from dba_extents
where file_id = 4
and 7680 between block_id
128 1818 0 1 1 2019-01-25/16:00:25 sqlplus@prsjedb2 (TNS V1-V3) (null) 283
select table_objno, primary_instance, secondary_instance, owner_instance from sy 00000000895C1368 8mz0wa11tacp0 SYS
0 26566 0 25000 1 2019-01-25/16:01:12 SQL*Plus (null) 47
delete from undo_layer_t3 where rownum <= 25000 0000000087435CC8 57nsvt4f6rmbk JSH
3 rows selected.
|
command_type 설명
command_type - 2 : insert
command_type - 3 : select
command_type - 7 : delete
command_type - 6 : update
2_1. bind 변수 확인(1번 조회 결과에서 ADDRESS 값 이용)
|
1
2
3
4
|
SQL>
select *
from v$sql_bind_capture
where address = '0000000087435CC8';
|
(난 바인드 변수가 없는 쿼리이기 때문에 이 과정 생략)
2_2. bind 변수 확인(위 조회 결과에서 SQL_ID 값 이용)
|
1
2
3
4
5
|
SQL>
select *
from dba_hist_sqlbind
where sql_id = '57nsvt4f6rmbk'
order by snap_id desc, position;
|
(난 바인드 변수가 없는 쿼리이기 때문에 이 과정 생략)
3_1. SQL full text 출력(1번 조회 결과에서 ADDRESS 값 이용)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
select sql_text
--select 'AA'||sql_text||'BB'
from v$sqltext_with_newlines
where address = '0000000087435CC8'
order by Piece;
SQL_TEXT
----------------------------------------------------------------
delete from undo_layer_t3 where rownum <= 25000
1 row selected.
|
3_2. SQL full text 출력(1번 조회 결과에서 SQL_ID 값 이용)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
select sql_text
--select 'AA'||sql_text||'BB'
from v$sqltext_with_newlines
where sql_id = '57nsvt4f6rmbk'
order by Piece;
SQL_TEXT
----------------------------------------------------------------
delete from undo_layer_t3 where rownum <= 25000
1 row selected.
|
4_1. 실행계획 확인(1번 조회 결과에서 SQL_ID 값 이용)
|
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
26
27
28
29
30
31
32
33
34
35
36
|
SQL>
select p.plan_table_output
from
(select distinct sql_id, child_number
from v$sql_plan s
where s.address = '' or s.sql_id = '57nsvt4f6rmbk') s
, table(dbms_xplan.display_cursor (s.sql_id, s.child_number, 'typical')) p;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 57nsvt4f6rmbk, child number 0
-------------------------------------
delete from undo_layer_t3 where rownum <= 25000
Plan hash value: 3708917863
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | 659 (100)| |
| 1 | DELETE | UNDO_LAYER_T3 | | | |
|* 2 | COUNT STOPKEY | | | | |
| 3 | TABLE ACCESS FULL| UNDO_LAYER_T3 | 508K| 659 (2)| 00:00:08 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=25000)
Note
-----
- dynamic sampling used for this statement (level=2)
24 rows selected.
|
4_2. AWR으로 실행계획 확인(1번 조회 결과에서 SQL_ID 값 이용)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
col PLAN for a70
select sql_id,lpad(' ',depth*4,' ')||' '||operation|| ' ' ||options|| ' ' ||object_name|| ' (' ||cost||')'plan
, to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') as "date"
from DBA_HIST_SQL_PLAN
where sql_id in ('57nsvt4f6rmbk')
order by timestamp,sql_id,plan_hash_value, id;
SQL_ID PLAN date
------------- ---------------------------------------------------------------------- -------------------
57nsvt4f6rmbk DELETE STATEMENT (659) 2019-01-25 16:01:12
57nsvt4f6rmbk DELETE UNDO_LAYER_T3 () 2019-01-25 16:01:12
57nsvt4f6rmbk COUNT STOPKEY () 2019-01-25 16:01:12
57nsvt4f6rmbk TABLE ACCESS FULL UNDO_LAYER_T3 (659) 2019-01-25 16:01:12
4 rows selected.
|
4_3. AWR으로 성능 확인
|
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>
col BEGIN_INTERVAL_TIME for a30
select sql_id, module, b.begin_interval_time,
trunc(buffer_gets_delta/decode(executions_delta,0,1,executions_delta)) buffer_gets,
trunc(disk_reads_delta/decode(executions_delta,0,1,executions_delta)) disk_reads,
trunc(fetches_delta/decode(executions_delta,0,1,executions_delta)) fetchs,
trunc(ROWS_PROCESSED_DELTA/decode(executions_delta,0,1,executions_delta)) ROWS_PROCESSED,
trunc(elapsed_time_delta/1000000/decode(executions_delta,0,1,executions_delta)) as elapsed_time,
trunc(IOWAIT_DELTA/1000000/decode(executions_delta,0,1,executions_delta)) IOWAIT,
trunc(APWAIT_DELTA/1000000/decode(executions_delta,0,1,executions_delta)) APWAIT,
trunc(CLWAIT_DELTA/1000000/decode(executions_delta,0,1,executions_delta)) CLWAIT,
trunc(CCWAIT_DELTA/1000000/decode(executions_delta,0,1,executions_delta)) CCWAIT, executions_delta executions
from DBA_HIST_SQLSTAT a, dba_hist_snapshot b
where a.sql_id = '57nsvt4f6rmbk'
and a.snap_id = b.snap_id
and a.dbid = b.dbid
and b.instance_number = 1
and b.begin_interval_time between to_timestamp('20190101','YYYYMMDD') and to_timestamp('20190131','YYYYMMDD')
order by a.snap_id;
SQL_ID MODULE BEGIN_INTERVAL_TIME BUFFER_GETS DISK_READS FETCHS ROWS_PROCESSED ELAPSED_TIME IOWAIT APWAIT CLWAIT CCWAIT EXECUTIONS
------------- ------------------------------ ------------------------------ ----------- ---------- ---------- -------------- ------------ ---------- ---------- ---------- ---------- ----------
57nsvt4f6rmbk SQL*Plus 25-JAN-19 04.00.45.307 PM 26566 0 0 25000 0 0 0 0 1
1 row selected.
|
참고 : trace를 확인하지 못하는 경우(DBMS_XPLAN.DISPLAY_CURSOR 활용)
gather_plan_statistics 힌트와 함께 SQL 실행
|
1
2
3
4
|
SQL>
SELECT /*+ gather_plan_statistics INDEX(EMP PK_EMP) Hello */ ENAME, EMPNO
FROM EMP
WHERE EMPNO = 7900;
|
실행계획 확인
|
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
26
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 1v908z0v3214a, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(EMP PK_EMP) Hello */ ENAME,
EMPNO FROM EMP WHERE EMPNO = 7900
Plan hash value: 2949544139
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
20 rows selected.
|
dbms_xplan.display_cursor format 종류
- Basic, Typical, Outline, All, Advanced, - allstats last, +peeked_binds
https://positivemh.tistory.com/364 참조
방법 : SQL 튜닝시 필요한 스크립트2
buffer_gets 가 높은순으로(top) 튜닝할 쿼리 확인
1. AWR 수집된 시간 및 추출하고자 하는 시간 때를 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> select snap_id, begin_interval_time, end_interval_time
from dba_hist_snapshot s
, v$instance i
where s.instance_number = i.instance_number
order by 1 desc ;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
2206 11-FEB-19 09.00.30.928 AM 11-FEB-19 10.00.43.178 AM
2205 11-FEB-19 08.00.36.624 AM 11-FEB-19 09.00.30.928 AM
2204 11-FEB-19 07.00.24.417 AM 11-FEB-19 08.00.36.624 AM
2203 11-FEB-19 06.00.09.890 AM 11-FEB-19 07.00.24.417 AM
2202 11-FEB-19 05.00.14.895 AM 11-FEB-19 06.00.09.890 AM
2201 11-FEB-19 04.00.17.619 AM 11-FEB-19 05.00.14.895 AM
2200 11-FEB-19 03.00.14.342 AM 11-FEB-19 04.00.17.619 AM
2199 11-FEB-19 02.00.02.146 AM 11-FEB-19 03.00.14.342 AM
|
2. 원하는 snap_id 값을 쿼리에 반영 후 조회(주석은 무시)
|
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
SQL> select b.*
, substr(a.sql_text, 1, 200) sql_text
, (select command_name
from v$sqlcommand c
where c.command_type = a.command_type) as command_type
from dba_hist_sqltext a, (select sql_id, dbid, plan_hash_value, parsing_schema_name
--, round(executions_delta/(6*60*60), 2) as exe_sec -- 초당 실행횟수(맨앞숫자를 변경 - 시간단위)
, executions_delta, buffer_gets, buffer_gets_exe
, cpu_time_sec_exe
, elapsed_time_sec, disk_reads_exe
, fetches_exe, parse_calls_exe, sharable_mem_exe
from (select s.sql_id, s.dbid, s.plan_hash_value, s.parsing_schema_name
, round(sum(s.disk_reads_delta)/sum(s.executions_delta)) as disk_reads_exe
, sum(s.buffer_gets_delta) as buffer_gets -- 총 buffer_gets
, round(sum(s.buffer_gets_delta)/sum(s.executions_delta)) as buffer_gets_exe
, round(sum(s.cpu_time_delta/1000000)/sum(s.executions_delta), 2) as cpu_time_sec_exe
, round(sum(s.elapsed_time_delta/1000000)/sum(s.executions_delta), 2) as elapsed_time_sec
, round(sum(s.fetches_delta)/sum(s.executions_delta)) as fetches_exe
, round(sum(s.parse_calls_delta)/sum(s.executions_delta)) as parse_calls_exe
, round(sum(s.sharable_mem)/sum(s.executions_delta)) as sharable_mem_exe
, sum(s.executions_delta) as executions_delta
--, sum(s.loaded_versions), sum(s.version_count), sum(s.fetches_delta)
from dba_hist_sqlstat s, dba_hist_snapshot n
where s.snap_id = n.snap_id
and s.dbid = n.dbid
and s.instance_number = n.instance_number
-- and s.parsing_schema_name not in ('SYS', 'SYSTEM', 'SYSMAN', 'ORANGE', 'MDSYS', 'APEX_030200', 'DBSNMP')
and s.executions_delta != 0
and n.instance_number = (select instance_number from v$instance)
--and n.snap_id = 102751 -- snap_id 번호
and n.snap_id between 2199 and 2206 -- snap_id 번호
--and trunc(begin_interval_time, 'mi') >= to_date('20181115 0700', 'yyyymmdd hh24:mi') -- 수집시작시간
--and trunc(begin_interval_time, 'mi') < to_date('20181115 0759', 'yyyymmdd hh24:mi') + 1/24/60 -- 수집마지막시간
--and s.sql_id = '28zgkmrfv6xk3'
group by s.sql_id, s.dbid, s.plan_hash_value, s.parsing_schema_name
order by buffer_gets desc -- buffer_gets 으로 order by
)
where 1=1
and rownum <=50
) b
where a.dbid = b.dbid
and a.sql_id = b.sql_id
--and command_type in (2, 3, 6, 7) --/ 2:I, 3:S, 6:U, 7:D
order by buffer_gets desc, 1
;
SQL_ID DBID PLAN_HASH_VALUE PARSING_SCHEMA_NAME EXECUTIONS_DELTA BUFFER_GETS BUFFER_GETS_EXE CPU_TIME_SEC_EXE ELAPSED_TIME_SEC DISK_READS_EXE FETCHES_EXE PARSE_CALLS_EXE
------------- ---------- --------------- ------------------------------ ---------------- ----------- --------------- ---------------- ---------------- -------------- ----------- ---------------
SHARABLE_MEM_EXE SQL_TEXT COMMAND_TYPE
---------------- -------------------------------------------------------------------------------- ----------------------------------------------------------------
679x4qggryd2v 655536183 220652418 SYS 48 24098 502 0 0 0 1 1
4693 select smontabv.cnt, smontabv.time_mp, smontab.scn, smontab.num_mappings, smo SELECT
6ajkhukk78nsr 655536183 0 SYS 7 15551 2222 .03 .04 0 0 1
56733 begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end; PL/SQL EXECUTE
3s58mgk0uy2ws 655536183 0 SYS 717 5398 8 0 0 0 0 1
269 insert into wrm$_snapshot_details (snap_id, dbid, instance_number, table_id, INSERT
5h7w8ykwtb2xt 655536183 0 SYS 726 3210 4 0 0 0 0 0
165 INSERT INTO SYS.WRI$_ADV_PARAMETERS (TASK_ID,NAME,DATATYPE,VALUE,FLAGS,DESCRIPTI INSERT
|
그외 disk_read, cpu_time, elapsed_time 의 Top 쿼리는 order by 구문에 수정 후 사용면 됨
방법 : SQL 튜닝시 필요한 스크립트3
파싱 스키마별 악성 쿼리 갯수 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
select parsing_schema_name "업무"
, count(*) "SQL 개수"
, count(distinct substr(sql_text, 1, 100)) "SQL 개수(Unique)"
, sum(executions) "수행횟수"
, round(avg(buffer_gets/executions)) "논리적 I/O"
, round(avg(disk_reads/executions)) "디스크 I/O"
, round(avg(rows_processed/executions)) "처리건수"
, round(avg(elapsed_time/executions/1000000),2) "평균소요시간"
, count(case when elapsed_time/executions/1000000 >= 10 then 1 end) "악성SQL (10초 이상)"
, round(max(elapsed_time/executions/1000000),2) "최대 소요시간"
from v$sql
where parsing_schema_name not in ('SYS','SYSTEM')
and last_active_time >= to_date('20220301','yyyymmdd')
group by parsing_schema_name;
업무 SQL 개수 SQL 개수(Unique) 수행횟수 논리적 I/O 디스크 I/O 처리건수 평균소요시간 악성SQL (10초 이상) 최대 소요시간
------------- ---------- ---------------- ---------- ---------- ---------- ---------- ------------ ------------------- -------------
IMSI 144 33 2170 8980 9 4625 .06 0 4.6
ORACLE_OCM 14 9 135 30 1 1 .03 0 .11
DBSNMP 5 5 1108 2 0 34 0 0 0
|
ORA-01476 발생시
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> select parsing_schema_name "업무" , count(*) "SQL 개수" , count(distinct substr(sql_text, 1, 100)) "SQL 개수(Unique)" , sum(executions) "수행횟수" -- , round(avg(buffer_gets/executions)) "논리적 I/O" -- , round(avg(disk_reads/executions)) "디스크 I/O" -- , round(avg(rows_processed/executions)) "처리건수" -- , round(avg(elapsed_time/executions/1000000),2) "평균소요시간" -- , count(case when elapsed_time/executions/1000000 >= 10 then 1 end) "악성SQL (10초 이상)" , round(avg(nvl(buffer_gets/decode(executions, 0, NULL, executions), 0))) "논리적 I/O" , round(avg(nvl(disk_reads/decode(executions, 0, NULL, executions), 0))) "디스크 I/O" , round(avg(nvl(rows_processed/decode(executions, 0, NULL, executions), 0))) "처리건수" , round(avg(nvl(elapsed_time/decode(executions/1000000,0,NULL,executions/1000000),0)),2) "평균소요시간" , count(case when nvl(elapsed_time/decode(executions/1000000,0,NULL,executions/1000000),0) >= 10 then 1 end) "악성SQL (10초 이상)" , round(max(nvl(elapsed_time/decode(executions/1000000, 0, NULL, executions/1000000), 0)),2) "최대 소요시간" from v$sql where parsing_schema_name not in ('SYS','SYSTEM') and last_active_time >= to_date('20220601','yyyymmdd') group by parsing_schema_name order by 1; |
참조 : https://kosate.tistory.com/109
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| automatic evolution task 파라미터 설정 (0) | 2019.02.18 |
|---|---|
| 순간적인(1~2분 사이) 과다 실행된 SQL 찾기 (0) | 2019.02.12 |
| 오라클 sql 무료 테스트 환경 livesql.oracle.com (0) | 2019.01.09 |
| 실행계획이 변경된 SQL 확인 및 이유 확인 (0) | 2019.01.08 |
| ASH를 이용한 wait event 및 session 찾기 (0) | 2019.01.03 |