꿈꾸는 DB 전문가

국가공인 SQLD 자격증을 준비한다면 디비안에서 이번에 출간된 SQLD 자격검정 핵심노트를 적극 추천합니다.

책소개 

초보자들이 SQL을 효과적으로 공부할 수 있도록 돕는 방법이 무엇일지 고민하던 끝에 국가공인 'SQL 전문가 가이드' 집필진이 직접 본서를 출간하게 됐다. 자격증 응시자들이 선호하는 문제 풀이 형식을 취했지만, 정확히 학습할 수 있도록 돕는 데 더 큰 목표를 두고 성심껏 실습 스크립트를 개발했다.

순서대로 직접 실습해 가는 과정을 통해 자연스럽게 원리를 이해하도록 문제를 구성했고, 이해가 부족한 초보 독자를 위해 해설도 충실히 달았다. 집필진이 집필 의도에 맞는 예상 문제를 직접 출제하고 해설하였으므로 콘텐츠의 정확성은 그 어느 책보다 신뢰할만하다.

▶ 교보문고

http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9791196395773&orderClick=LEa&Kc=

​

▶ 예스24

http://www.yes24.com/Product/Goods/96272868

​

▶ 알라딘

https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=258709220

​

▶ 인터파크

http://book.interpark.com/product/BookDisplay.do?_method=detail&sc.shopNo=0000400000&sc.prdNo=344617300&sc.saNo=003002001&bid1=search&bid2=product&bid3=title&bid4=001

oracle-base.com/articles/10g/index-monitoring

select * from dba_virus
where view_name like 'DBA%IND';

create table t5 (id varchar2(5) primary key ,
                 ord_dt date ,
                 c3 varchar2(20) ,
                 c4 varchar2(200) ) ;


drop table t5 purge;   
truncate table t5 ;

insert into t5
select * from (
  select object_id -1 , last_ddl_time, object_type, object_name
  from all_objects
  order by object_id )
where rownum <= 50000 ;

commit;
create index t5_x1 on t5(ord_dt) ;

select * from t5;

update t5 set ord_dt = to_date('2017/05/08', 'YY/MM/DD')
where id between 48000 and 49000 ;

select * from t5;
update t5 set ord_dt = to_date('2017/05/09', 'YY/MM/DD')
where id between 49001 and 50000 ;

commit;

select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4
from t5
where ord_dt > '2017/05/08'
and ord_dt <= '2017/05/09'
order by ord_dt, id, c3, c4 ;

select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4
from t5
where ord_dt > '2017/05/08'
and ord_dt <= '2017/05/09'
order by ord_dt, id;



select * from bulk_emp
where empno = 7369 ;


create table bulk_emp
as
select rownum rn, empno, ename, job, to_date('20100101', 'yyyymmdd')+lv as hiredate, sal, deptno
from emp
, (select mod(level-1, 5000) lv from dual connect by level <= 100000) ;

alter table bulk_emp add (constraint pk_bulk_emp primary key(rn));
create index ix1_bulk_emp on bulk_emp(hiredate);




select /*+ gather_plan_statistics */
hiredate, empno, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, empno, ename, job ;

select /*+ gather_plan_statistics */
hiredate, empno, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, empno ;



select /*+ gather_plan_statistics */
hiredate, rn, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, rn, ename, job ;

select /*+ gather_plan_statistics */
hiredate, rn, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, rn ;





select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));



 

SQL_ID  7r72vumqu8n7n, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4 from t5 where 
ord_dt > '2017/05/08' and ord_dt <= '2017/05/09' order by ord_dt, id, 
c3, c4
 
Plan hash value: 1711542383
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   1 |  SORT ORDER BY                        |       |      1 |   1000 |   1000 |00:00:00.01 |      16 |   106K|   106K|96256  (0)|
|*  2 |   FILTER                              |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T5    |      1 |   1000 |   1000 |00:00:00.01 |      16 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | T5_X1 |      1 |   1000 |   1000 |00:00:00.01 |       6 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_DATE('2017/05/09')>TO_DATE('2017/05/08'))
   4 - access("ORD_DT">'2017/05/08' AND "ORD_DT"<='2017/05/09')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

SQL_ID  3pjr8972swmvm, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4 from t5 where 
ord_dt > '2017/05/08' and ord_dt <= '2017/05/09' order by ord_dt, id
 
Plan hash value: 1711542383
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   1 |  SORT ORDER BY                        |       |      1 |   1000 |   1000 |00:00:00.01 |      16 | 99328 | 99328 |88064  (0)|
|*  2 |   FILTER                              |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T5    |      1 |   1000 |   1000 |00:00:00.01 |      16 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | T5_X1 |      1 |   1000 |   1000 |00:00:00.01 |       6 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_DATE('2017/05/09')>TO_DATE('2017/05/08'))
   4 - access("ORD_DT">'2017/05/08' AND "ORD_DT"<='2017/05/09')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |      8 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 |      8 | 52224 | 52224 |47104  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |      8 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |      8 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 | 52224 | 52224 |47104  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("HIREDATE">=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "HIREDATE"<TO_DATE(' 2015-01-04 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
              
              
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 | 46080 | 46080 |40960  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------   

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 | 46080 | 46080 |40960  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

실행계획(EXPLAIN)의 Extra 칼럼에 "Using Index Condition"이라고 표시되는 경우가 있다.
이는 WHERE 절의 인덱스를 이용한 조건(인덱스를 사용한 조건이어야 함)에 체크 조건이 있을 경우 체크 조건 처리를 스토리지 엔진이 하도록 전달하는 것이다.

 Mysql은 Mysql 엔진과 스토리지 엔진으로 구성되어 있는데, 범위를 제한하는 조건은 스토리지 엔진에서 인덱스로 데이터를 찾으면서 사용한다. 그런 다음 찾은 데이터를 Mysql 엔진에게 넘긴다.
 Mysql 엔진은 범위를 제한하지 못하는 체크 조건을 처리하게 된다. 이 체크 조건에 부합하지 않은 레코드는 버리게 된다.
 Mysql 엔진과 스토리지 엔진은 tcp 통신을 하기 때문에 체크 조건에 맞지 레코드를 전달하는 것은 낭비다.

 이렇게 스토리지 엔진이 체크 조건까지 처리하도록 최적화가 되었고, "Condition push down"이라고 불린다. 이게 InnoDB에 5.1버전부터 들어갔는데, extra 칼럼에 Using Index Condition이라고 표시된 건 5.6버전(https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html)부터이다.

Percona Toolkit 사용

pt-query-digest

슬로우쿼리의 요약 정보를 보여줍니다. 사용법은 아래 링크에서 참고 가능합니다.
https://blog.naver.com/sory1008/220940827130

pt-summary

하드웨어 스펙을 요약해 보여 줍니다.
https://blog.naver.com/sory1008/221173659939

pt-online-schema-change

innodb_sync_spin_loops : 스레드가 일시 중단되기 전에 스레드가 InnoDB 뮤텍스가 해제되기를 기다리는 횟수.

(기본값 30.)

innodb_sync_spin_loops 값에 따라 User CPU 사용률의 변화가 큼

nnodb_sync_spin_loops 값에 따라 User CPU 사용률의 변화가큼

Sys CPU 사용률과 Idle CPU 사용률을 보았을 때 값이 10일 때가 가장 적절해 보인다고 필자는 이야기함

innodb_spin_wait_delay : 스핀 잠금을위한 폴링 사이의 최대 지연. (기본값 6.)

CPU 사용률이 높은 상황에선 innodb_spin_wait_delay 값을 줄여도 크게 영향을 미치진 않음.

극단적으로 값을 0으로 설정해야 User CPU 사용이 줄어듦

CPU가 어느정도 여유가 있는 상태에서 innodb_spin_wait_delay 값을 조정하는경우는

어느정도 영향력을 가짐

위 내용은 아래 블로그를 참고함

m.blog.naver.com/PostView.nhn?blogId=hanajava&logNo=220815652704&proxyReferer=https:%2F%2Fwww.google.com%2F

https://stardba.net/chan/mysql-innodb-as-cache-server-monitoring/

https://mysqlserverteam.com/the-mysql-8-0-22-maintenance-release-is-generally-available/