SQL 튜닝하면 제일 많이 고려되는 것은 테이블 및 인덱스에 대한 SCAN 기법이다
악성 SQL은 응답시간이 느려질 수 있습니다 DB 서비스 타임(CPU+wait time)이 느려질 수 있습니다
SQL Tuning은 작성자의 의도를 파악해야 한다 결과를 바꾸는게 아니다 그 후 필요에 따라 튜닝하는 것
SQL은 테이블 인덱스 컬럼 등에 대한 통계 정보를 보는 것이다
결합 인덱스에서 중요한건 컬럼의 순서
INDEX_DESC는 order by를 피하기 위한 적절한 힌트
인덱스를 타는걸 랜덤액세스라 하는데 where order by select 를 탄다
인덱스를 타게 되면 order by를 하더라도 정렬에 대한 비용이 없어지게 된다
orcl@SYS> select * from v$diag_info;
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------------------
1 Diag Enabled
TRUE
1 ADR Base
/u01/app/oracle
1 ADR Home
/u01/app/oracle/diag/rdbms/orcl/orcl
1 Diag Trace
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
1 Diag Alert
/u01/app/oracle/diag/rdbms/orcl/orcl/alert
1 Diag Incident
/u01/app/oracle/diag/rdbms/orcl/orcl/incident
1 Diag Cdump
/u01/app/oracle/diag/rdbms/orcl/orcl/cdump
1 Health Monitor
/u01/app/oracle/diag/rdbms/orcl/orcl/hm
1 Default Trace File
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8250.trc
1 Active Problem Count
4
1 Active Incident Count
19
select cust_id, time_id, sum(amount_sold), count(*)
from sales
group by cust_id, time_id
having time_id between to_date('1999/10/01','YYYY/MM/DD') and to_date('1999/10/31','YYYY/MM/DD')
통쨰로 그룹바이한것 과
select cust_id, time_id, sum(amount_sold), count(*)
from sales
where time_id between to_date('1999/10/01','YYYY/MM/DD') and to_date('1999/10/31','YYYY/MM/DD')
group by cust_id, time_id;
걸러서 그룹바이 한것
SQL의 읽는 순서를 생각해봐야 한다 INDEX가 걸리는 조건은 where order by select 3개의 조건
orcl@SH> alter session set tracefile_identifier='mytraceid2';
Session altered.
orcl@SH> alter session set sql_trace=true;
Session altered.
[oracle@lnx02 trace]$ tkprof orcl_ora_10580_mytraceid.trc sh1.txt explain=sh/sh sys=no
TKPROF: Release 11.2.0.1.0 - Development on Mon Dec 21 10:13:44 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
[oracle@lnx02 trace]$ tkprof orcl_ora_11231_mytraceid.trc sh2.txt explain=sh/sh sys=no
TKPROF: Release 11.2.0.1.0 - Development on Mon Dec 21 10:14:08 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
having절로 조건한 후 분석한 파일
select cust_id, time_id, sum(amount_sold), count(*)
57 from sales
58 group by cust_id, time_id
59 having time_id between to_date('1999/10/01','YYYY/MM/DD') and to_date('1999/ 10/31','YYYY/MM/DD')
60
61 call count cpu elapsed disk query current rows
62 ------- ------ -------- ---------- ---------- ---------- ---------- ------ ----
63 Parse 1 0.00 0.04 0 0 0 0
64 Execute 1 0.00 0.00 0 0 0 0
65 Fetch 194 0.49 0.50 1551 1718 0 2890
66 ------- ------ -------- ---------- ---------- ---------- ---------- ------ ----
67 total 196 0.50 0.55 1551 1718 0 2890
68
69 Misses in library cache during parse: 1
70 Optimizer mode: ALL_ROWS
71 Parsing user id: 88 (SH)
72
73 Rows Row Source Operation
74 ------- ---------------------------------------------------
75 2890 FILTER (cr=1718 pr=1551 pw=0 time=4333 us)
76 143139 PARTITION RANGE ALL PARTITION: 1 28 (cr=1718 pr=1551 pw=0 time=233 446 us cost=2421 size=362520 card=20140)
77 143139 HASH GROUP BY (cr=1718 pr=1551 pw=0 time=98444 us cost=2421 size= 362520 card=20140)
78 918843 TABLE ACCESS FULL SALES PARTITION: 1 28 (cr=1718 pr=1551 pw=0 ti me=636179 us cost=489 size=16539174 card=918843)
where절로 조건한 후 분석한 파일
select cust_id, time_id, sum(amount_sold), count(*)
308 from sales
309 where time_id between to_date('1999/10/01','YYYY/MM/DD') and to_date('1999/10/31','YYY Y/MM/DD')
310 group by cust_id, time_id
311
312 call count cpu elapsed disk query current rows
313 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
314 Parse 1 0.02 0.02 0 0 0 0
315 Execute 1 0.00 0.00 0 0 0 0
316 Fetch 194 0.01 0.02 105 107 0 2890
317 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
318 total 196 0.04 0.05 105 107 0 2890
319
320 Misses in library cache during parse: 1
321 Optimizer mode: ALL_ROWS
322 Parsing user id: 88 (SH)
323
324 Rows Row Source Operation
325 ------- ---------------------------------------------------
326 2890 PARTITION RANGE SINGLE PARTITION: 12 12 (cr=107 pr=105 pw=0 time=21667 us cos t=163 size=400626 card=22257)
327 2890 HASH GROUP BY (cr=107 pr=105 pw=0 time=5909 us cost=163 size=400626 card=222 57)
328 22256 TABLE ACCESS FULL SALES PARTITION: 12 12 (cr=107 pr=105 pw=0 time=17138 us cost=34 size=400626 card=22257)
orcl@SCOTT> create index emp_ename_idx on emp(ename);
Index created.
Elapsed: 00:00:00.03
orcl@SCOTT> select /*+ full(emp) */ empno, ename, job, sal
2 from emp
3 where ename='KING';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 23 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
Statistics
----------------------------------------------------------
24 recursive calls
4 db block gets
8 consistent gets
0 physical reads
1556 redo size
611 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
FTS시 인덱스사용보다 유리할 수 있으며 다중 블록 I/O parallel 사용 가능
orcl@SYSTEM> create table t1(c1 int, ce int);
Table created.
orcl@SYSTEM> create index t1_n1 on t1(c1);
Index created.
orcl@SYSTEM> insert into t1
2 select level, level
3 from dual
4 connect by level <= 10000;
10000 rows created.
orcl@SYSTEM> set autot on
orcl@SYSTEM> select /*+ gather_plan_statistics index(t1) */ c1
2 from t1
3 where c1 >= 5;
Execution Plan
----------------------------------------------------------
Plan hash value: 1088104427
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9996 | 126K| 20 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T1_N1 | 9996 | 126K| 20 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1">=5)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
718 consistent gets
0 physical reads
116 redo size
136867 bytes sent via SQL*Net to client
7741 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9996 rows processed
orcl@SYSTEM> select lf_blks, distinct_keys,br_rows, br_blks
2 from index_stats where name='T1_N1';
LF_BLKS DISTINCT_KEYS BR_ROWS BR_BLKS
---------- ------------- ---------- ----------
19 10000 18 1
결합 인덱스를 만들때
점 + 점 = 식별이 좋은 대상 및 data가 적은 것 기준 고려
점 + 선분 = 점 조건 우선
선분 + 점 = 안 만드는게 좋다
선분 + 선분 = 분포도 좋은것이 앞으로
select * from big_emp
where sal > 2000
order by ename
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12486 | 3267K| | 969 (1)| 00:00:12 |
| 1 | SORT ORDER BY | | 12486 | 3267K| 3576K| 969 (1)| 00:00:12 |
|* 2 | TABLE ACCESS FULL| BIG_EMP | 12486 | 3267K| | 246 (0)| 00:00:03 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAL">2000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
79 recursive calls
0 db block gets
1003 consistent gets
260 physical reads
0 redo size
261511 bytes sent via SQL*Net to client
9424 bytes received via SQL*Net from client
821 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12288 rows processed
'스터디북' 카테고리의 다른 글
| <12/23> 성능고도화 발표 (0) | 2015.12.23 |
|---|---|
| <12/22> SQL Tuning 7장 8장 (0) | 2015.12.22 |
| <12/20> 카페북, DBCA content (0) | 2015.12.21 |
| <12/19> 주말 자습 Oracle 11gR2 설치 (0) | 2015.12.19 |
| <12/13> 주말 자습 성능고도화 1장 및 RAC 설치 (0) | 2015.12.14 |
