서영이네집

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Oracle 10g XE는 처음에 설치할 때 AL16UTF16으로 자동 설정됩니다.

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

매번 까먹고 항상 찾느라 고생한다... =ㅅ=;;

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

※ 이 글은 "실전 SQL 튜닝" 강의를 듣고 개인적인 의견과 함께 정리한 내용입니다.

 실행계획이란 DB가 SQL문을 어떻게 실행할 것인가에 대한 계획입니다. 말 그대로 계획이기 때문에 실제로 SQL문이 실행되었을 때 실행계획과 다른게 실행될 수도 있습니다. 하지만 이런 경우는 거의 보기 힘듭니다. 만약 실행계획과 실행이 다른 것 같다면 Trace를 사용해서 확인 하셔야 합니다.

사전 준비

 만약 한번도 실행계획을 실행해보지 않으셨다면 먼저 plan_table을 먼저 생성해야 합니다. plan_table이란 실행계획의 결과를 저장하는 테이블입니다. 보통 별도로 테이블을 생성하신 적이 없다면 직접 만들어줘야 합니다.
 어렵진 않으니 걱정하지 마세요. 다음 순서대로 따라하시면 됩니다.

1. sqlplus에  접속합니다.
2. '@utlxplan.sql'을 입력하고 엔터~
3. 만약 기존의 객체가 이름을 사용하고 있다(ORA-00955)고 나온다면 'drop table plan_table;'을 입력하고 엔터~
4. 다시 2번을 실행하세요.

 plan_table이 생성되었다면 이제 실행계획을 실행할 수 있는 준비가 되었습니다.
 만약 sqlplus에 접속하는게 어려우시다면 아래 경로를 찾아서 sql문을 복사해 와서 툴에서 실행시키셔도 됩니다.


만약 파일을 찾지 못했거나 찾기가 힘들면 그냥 아래 sql을 실행하세요.

more..

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

오라클에서 TO_CHAR을 이용해서 날짜 관련 값을 구할수 있는 방법을 정리했습니다.
스쳐지나감의 미덕 의 내용을 포멧만 약간 편집했습니다.


SELECT To_char(SYSDATE,'CC')
FROM   dual; --Century

SELECT To_char(SYSDATE,'YYYY')
FROM   dual; --Year (*NOTE reducing the 'Y's reduces the display accordingly) from dual;

SELECT To_char(SYSDATE,'Y,YYY')
FROM   dual; --Four digit year with comma

SELECT To_char(SYSDATE,'YYY,Y')
FROM   dual; --Comma acts as a seperator. (Comma may be any punctuation eg !,/ etc..) from dual;

SELECT To_char(SYSDATE,'YEAR')
FROM   dual; --Year in words

SELECT To_char(SYSDATE,'Q')
FROM   dual; --The quarter of the year

SELECT To_char(SYSDATE,'MM')
FROM   dual; --The number of the month

SELECT To_char(SYSDATE,'RM')
FROM   dual; --The number of the month in roman numerals (Why!?) from dual;

SELECT To_char(SYSDATE,'MONTH')
FROM   dual; --The month. (Rpadded to 9 with spaces); For lower case 'Month'.

SELECT To_char(SYSDATE,'WW')
FROM   dual; --The week of the year

SELECT To_char(SYSDATE,'W')
FROM   dual; --The week of the month

SELECT To_char(SYSDATE,'DDD')
FROM   dual; --The day of the year

SELECT To_char(SYSDATE,'DD')
FROM   dual; --The day of the month

SELECT To_char(SYSDATE,'D')
FROM   dual; --The day of the week

SELECT To_char(SYSDATE,'DAY')
FROM   dual; --THURSDAY The day of the week

SELECT To_char(SYSDATE,'DY')
FROM   dual; --The day of the week abbreviated

SELECT To_char(SYSDATE,'J')
FROM   dual; --Julian Date Number of days since January 1st, 4712BC

SELECT To_char(SYSDATE,'HH')
FROM   dual; --hour clock. may also be to_char (SYSDATE,'HH12') from dual;

SELECT To_char(SYSDATE,'HH24')
FROM   dual; --hr clock

SELECT To_char(SYSDATE,'MI')
FROM   dual; --Minutes

SELECT To_char(SYSDATE,'SS')
FROM   dual; --Seconds

SELECT To_char(SYSDATE,'SSSSS')
FROM   dual; --The seconds since last midnight

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

※ 이 글은 "실전 SQL 튜닝" 강의를 듣고 개인적인 의견과 함께 정리한 내용입니다.

Cost Based Optimizer의 작동 원리

CBO의 작동 순서

CBO는 다음과 같은 순서를 거쳐서 수행 됩니다.

1. Parsing 단계
   파싱 단계는 SQL 구문(syntax)과 의미검사(semantics)를 수행합니다. 예를 들어 SQL 구문이 정확한지 검사하고 참조된 테이블에 대해 사용자의 접근권한을 검사합니다. SQL은 파싱트리(parsing tree) 형태로 변형되어 옵티마이저에게 넘겨집니다.
   
   
2. 옵티마이저(Qeury Optimizer)
   위 그림에서 점선형태의 사각형으로 표시된 부분이 옵티마이저의 주요 구성요소입니다. 각 구성요소의 역할은 아래와 같습니다.
   
   
3. 질의 변환(Query Rewriter 또는 Transformer)
   질의 변환 단계는 파싱트리를 받아들여서 질의 변환을 수행합니다. 질의 변환은 사용자가 작성한 SQL문을 더 나은 실행계획을 찾을 수 있는 SQL문으로 변환합니다.
   
   
4. 실행계획 생성(Plan Generator)와 Estimator
   오라클 옵티마이저는 실행계획 생성과 비용산정 모듈을 수행하기 전에 질의에서 사용된 모든 테이블들과 각 테이블에서 정의된 인덱스들에 관해 기본적인 통계정보들(테이블 블럭수, 로우의 평균길이, 인덱스의 높이, 인덱스 리프 블록의 수 등)과 각 테이블에 대한 다양한 엑세스 경로(Full table scan, Index scan 등)에 대한 비용정보를 미리 산출하여 Cost Model을 만듭니다. 이러한 일련의 정보를 바탕으로 옵티마이저는 최적의 실행계획을 생성하여 'Best Query Plan'을 작성합니다.

CBO에서만 가능한 기능들

CBO는 위와 같이 작동하기 때문에 RBO(Rule Based Optimizer)에는 없는 CBO에서만 가능한 기능들이 있습니다

• 테이블 및 인덱스의 Partitioning
• 인덱스 구성 테이블(Index-organized table)
• Reverse Key Index
• Function Based Index
• SELECT 문장에서의 SAMPLE절
• 병렬 Query 및 병렬 DML
• Star Join 및 Star 변형
• Optimizer 확장
• Materialaized View를 이용한 Query rewrite
• Enterprise Manager progress meter
• 해쉬 Join
• Bitmap 인덱스 및 Bitmap Join 인덱스
• 인덱스 skip scan 알고리즘

CBO의  작동원리

• 실행계획 중 cost가 가장 적은 실행계획을 선택
• 컬럼별 데이터 분포에 대한 통계정보(=히스토그램) 사용
• 복잡한 관계표현에서 때로 잘못된 실행계획을 수립할 수도 있는데 이런 경우 힌트를 사용하여 수정하도록 함
• 오브젝트에 대한 통계정보를 기준으로 실행계획을 작성하므로 통계정보가 제대로 생성되어 있지 않은경우, 응용프로그램의 성능에 악영향을 미치게 되므로 주의
• Cost engine은 I/O cost, Network cost, CPU cost 등도 고려하도록 설계되어 있음

※ 통계정보 : 테이블의 데이터 건수, 평균 실이, 컬럼 별 distinct 값의 수, 인덱스 leaf node의 depth 등이 저장

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

오라클에서 실행계획을 보는 방법은 여러가지가 있습니다. 토드나 오렌지 등의 툴을 이용하면 간편하고 쉽지만 sql만을 사용해서 봐야 하는 경우가 종종 있습니다. 그럴 경우는 다음과 같이 sql을 사용하면 됩니다.

explain plan for
select * from emp;

위와 같이 sql을 사용한 후 아래 sql을 실행시키면 실행계획을 볼 수 있습니다.

select * from table(dbms_xplan.display);

아래와 같은 방법도 있습니다.

explain plan
set statement_id = 'stmt1' for
select * from emp;

select * from table(dbms_xplan.display('plan_table','stmt1','all'));
select * from table(dbms_xplan.display('plan_table','stmt1','typical'));
select * from table(dbms_xplan.display('plan_table','stmt1','basic'));

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

※ 이 글은 "실전 SQL 튜닝" 강의를 듣고 개인적인 의견과 함께 정리한 내용입니다.

CBO와 관련된 잘못된 편견

• 항상 주기적으로 통계정보를 생성해야 한다.
  - sql문의 실행계획은 통계정보가 바뀌면 항상 바뀌는 것은 아니지만 바뀔수도 있습니다. 평소에 잘 돌아가던 sql도 통계정보가 변경됨으로써 실행계획이 바뀌어서 기존보다 느려질 수도 있습니다. 하지만 대부분의 sql은 통계정보를 생성하면 통계정보가 크게 바뀐게 아니라면 기존과 큰 차이가 없을 것입니다.
• CBO의 가장 적게 드는 COST가 실제로도 가장 적게 드는 COST인가?
  - 위 말을 간단하게 표현하자면 '최적의 실행계획이 실제로도 가장 최적인가?'입니다. 하지만 불행히도 실제로는 100% 맞다고 할 수 없습니다. 이는 CBO가 사용하는 자료가 통계적인 자료이기 때문에 sql과 data에 따라서 달라질 수 있기 때문입니다.
• Oracle 10g에서는 RBO를 지원하지 않는다.
  - 10g에서는 기본이 CBO 입니다. 그러나 RBO의 기능은 존재합니다. 하지만 RBO에 관한 성능 및 기술지원은 보장하지 못합니다.
  

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

※ 이 글은 "실전 SQL 튜닝" 강의를 듣고 개인적인 의견과 함께 정리한 내용입니다.

CBO에서 Cost란?

 Cost란 말 그대로 비용입니다. DB에서 쿼리를 실행할 때 필요한 비용인거죠. 그리고 비용이 많이 들수록 DB의 응답속도와 부하는 늘어납니다. 그렇다면 이 Cost는 무엇을 근거로 산출되고 무엇을 Cost라고 하는 걸까요?

Cost는 무엇을 기준으로 나오는 값인가?

Jonathan Lewis says : The cost is the optimizer's best estimate of the time it will take to execute the statement.
=> 비용이란 optimizer가 sql 문장을 수행하는데 걸리는 시간의 최선의 예측치다.

여기서 수행하는데 걸리는 시간의 최선의 예측치는 간단하게 말하자면 "execution time = CPU time + I/O time" 이런 공식으로 계산했을 때 가장 빠른 시간을 말합니다. 하지만 Lock등이 발생해서 생기는 wait time은 계산에 들어가지 않습니다. 왜냐구요? 당연히 optimizer는 wait time등을 예측 할 수 없기 때문이죠.
I/O time은 다음과 같습니다. "I/O time =  Single Block I/O time(like index)  + Multi Block I/O time(like table full scan"

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.