안녕하세요, 오라클 초짜 입니다..

제목 그대로 오라클 SQL 튜닝 도움이 필요합니다..

제발 도와주소서................OTL..............

----------
-- SQL1 --
----------

SELECT 1 type1,DECODE(TRN_REGUL,'0','선박','항공') "운송",
       1 type2,DECODE(TRN_IO,'A','연착','도착') "도착",
         SUM(DECODE(TRN_LINE,'I',DECODE(TRN_DELAY,'2',0,'3',0,1))) "국내",
         SUM(DECODE(TRN_LINE,'D',DECODE(TRN_DELAY,'2',0,'3',0,1))) "국외",
         SUM(DECODE(TRN_DELAY,'2',0,'3',0,1)) "합계"
  FROM TAB13
 WHERE TRN_DATE BETWEEN '20010501' AND '20020502'
   AND TRN_PURPO IN ('00','05')
   AND TRN_USECODE IN ('0','1')
 GROUP BY DECODE(TRN_REGUL,'0','선박','항공'), DECODE(TRN_IO,'A','연착','도착')
 UNION
SELECT 1,DECODE(TRN_REGUL,'0','선박','항공') "선박/항공",
       2,'소계',
    SUM(DECODE(TRN_LINE,'I',DECODE(TRN_DELAY,'2',0,'3',0,1))) "국내",
         SUM(DECODE(TRN_LINE,'D',DECODE(TRN_DELAY,'2',0,'3',0,1))) "국외",
        SUM(DECODE(TRN_DELAY,'2',0,'3',0,1)) "합계"
 FROM  TAB13
 WHERE TRN_DATE BETWEEN '20010501' AND '20020502'
   AND TRN_PURPO IN ('00','05')
   AND TRN_USECODE IN ('0','1')
 GROUP BY DECODE(TRN_REGUL,'0','선박','항공')
 UNION
SELECT 2,'선박/항공',
       2,'총계',
         SUM(DECODE(TRN_LINE,'I',DECODE(TRN_DELAY,'2',0,'3',0,1))) "국내",
         SUM(DECODE(TRN_LINE,'D',DECODE(TRN_DELAY,'2',0,'3',0,1))) "국외",
         SUM(DECODE(TRN_DELAY,'2',0,'3',0,1)) "합계"
  FROM TAB13
 WHERE TRN_DATE BETWEEN '20010501' AND '20020502'
   AND TRN_PURPO IN ('00','05')
   AND TRN_USECODE IN ('0','1')
 ORDER BY 1,2,3,4 desc

----------
-- SQL2 --
----------

SELECT  DECODE(TRN_REGUL,'0','선박','항공') "운송",
        DECODE(TRN_IO,'A','연착','도착') "도착",
         SUM(DECODE(TRN_LINE,'I',DECODE(TRN_DELAY,'2',0,'3',0,1))) "국내",
         SUM(DECODE(TRN_LINE,'D',DECODE(TRN_DELAY,'2',0,'3',0,1))) "국외",
      SUM(DECODE(TRN_DELAY,'2',0,'3',0,1)) "합계"
FROM    TAB13
WHERE   TRN_DATE BETWEEN '20010501' AND '20020502'
AND TRN_PURPO IN ('00','05')
AND     TRN_USECODE IN ('0','1')
group by rollup(DECODE(TRN_REGUL,'0','선박','항공'),DECODE(TRN_IO,'A','연착','도착'));
 

======================================================================================================

CBO를 사용하는 환경에서는 통계정보 생성 및 sql 튜닝을 적절하게 활용하여 개선된 실행계획을 얻을 수 있음.
문제에서 요구하는 실행 계획을 생성해 보고, 각각의 차이점에 대해서 분석.
분석부분에는 Access Path의 차이, Access row수의 차이 및 튜닝의 핵심포인트라고 생각되는 부분에 대해서 설명.
elapsed time 정보 및 실행계획은 trace file을 생성한 후 tkprof를 통해서 생성된 레포트에서 추출.

1. 통계정보 생성 전 SQL1의 Total elapsed time 및 실행계획
. sesstion level에서 optimizer mode를 RBO로 바꾸어서 실행
2. 통계정보 생성 후 SQL1의 Total elapsed time 및 실행계획
. sesstion level에서 optimizer mode를 CBO(all_rows)로 바꾸어서 실행
3. 통계정보 생성 후 SQL2의 Total elapsed time 및 실행계획
. sesstion level에서 optimizer mode를 CBO(all_rows)로 바꾸어서 실행
4. 상기 3가지 실행계획의 차이점 분석
 

[create_table.sql]

CREATE TABLE TAB13
(TRN_DATE    VARCHAR2(08) NOT NULL,
 TRN_IO      VARCHAR2(01) NOT NULL,
 TRN_NO      VARCHAR2(08) NOT NULL,
 TRN_SID     VARCHAR2(01) NOT NULL,
 TRN_REGUL   VARCHAR2(01),
 TRN_LINE    VARCHAR2(01),
 TRN_DELAY   VARCHAR2(01),
 TRN_PURPO   VARCHAR2(02),
 TRN_USECODE VARCHAR2(01))
;
 

[create_index.sql]

ALTER TABLE TAB13 ADD  ( CONSTRAINT TAB13_PK PRIMARY KEY (TRN_DATE, TRN_IO, TRN_NO, TRN_SID));

[insert_data.sql]

INSERT INTO TAB13 VALUES('20010329','A','QE356','0','0','I','2','00','1');
INSERT INTO TAB13 VALUES('20010330','A','QE1620','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010329','A','QE1620','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010331','A','QE1620','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010329','D','QE523','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010330','D','QE1582','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010330','A','QE734','0','1','D','0','07','9');
INSERT INTO TAB13 VALUES('20010331','A','QE651','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010330','A','QE932','0','0','I','0','00','1');
INSERT INTO TAB13 VALUES('20010329','A','QE1784','0','0','I','0','00','0');
COMMIT;
INSERT INTO TAB13 VALUES('20010330','A','QE1077','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010331','A','QE1784','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010329','D','QE11','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010330','A','QE920','0','0','I','0','00','1');
INSERT INTO TAB13 VALUES('20010331','A','QE920','0','0','I','0','00','1');
INSERT INTO TAB13 VALUES('20010331','A','QE662','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010329','A','QE662','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010330','A','QE662','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010330','D','QE1679','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010331','D','QE1679','0','0','I','0','00','0');
COMMIT;
INSERT INTO TAB13 VALUES('20010329','D','QE1679','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010331','D','QE637','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010330','D','QE637','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010329','A','QE649','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010330','D','QE1457','0','0','I','0','00','1');
INSERT INTO TAB13 VALUES('20010329','A','QE207','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010329','A','QE67','0','1','I','0','05','3');
INSERT INTO TAB13 VALUES('20010331','A','QE207','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010330','A','QE207','0','0','I','0','00','0');
INSERT INTO TAB13 VALUES('20010329','A','QE1611','0','0','I','0','00','0');
COMMIT;