SQLP 20회

----------------------------1번 복원 완료 -----------------------------------------

1. 주어진 SQL 문와 Trace 결과를 바탕으로 최적의 성능으로 튜닝하시오

 

<SQL TEXT>

SELECT  B.OBJECT_ID,B.OBJECT_NAME,C.OBJECT_TYPE,C.OBJECT_ID

FROM SQLP20_01 A,

        SQLP20_02 B,

        SQLP20_03 C

WHERE 1=1

AND A.OWNER = B.OWNER

AND B.OBJECT_NAME = C.OBJECT_NAME   --> B 와 C 의 조인 컬럼이 A 와 B의 조인컬럼과 동일하지 않음

AND A.OWNER LIKE 'Z1%'

AND C.OBJECT_TYPE = 'SQLP'

AND C.OBJECT_ID > 10

 

<INDEX 구성>

SQLP20_01 : PK_SQLP20_01 (OWNER)  --> PK

SQLP20_02 : PK_SQLP20_02 (OWNER, OBJECT_NAME) ---> PK

            IX_SQLP20_02 (OBJECT_NAME)

SQLP20_03 : IX_SQLP20_03  (OBJECT_NAME, OBJECT_ID, OBJECT_TYPE) 

 

<10046 trace>

Rows     Row Source Operation

-------  -----------------------------------------------------------------------

     56  NESTED LOOPS  (cr=3932 pr=0 pw=0 time=0 us cost=14035 size=285108 card=4132)

   1000   NESTED LOOPS  (cr=1071 pr=0 pw=0 time=3371 us cost=6082 size=166950 card=3975)

    100    INDEX RANGE SCAN PK_SQLP20_01 (cr=3 pr=0 pw=0 time=396 us cost=3 size=3312 card=552)

   1000    TABLE ACCESS BY INDEX ROWID SQLP20_02 (cr=1068 pr=0 pw=0 time=1630 us cost=11 size=252 card=7)

   1000     INDEX RANGE SCAN PK_SQLP20_02 (cr=74 pr=0 pw=0 time=282 us cost=5 size=0 card=7)

     56   INDEX RANGE SCAN IX_SQLP20_03 (cr=2861 pr=0 pw=0 time=0 us cost=2 size=27 card=1)

 

<Sample Data Script --> 글 맨 밑에 첨부>

 

---복원 중 -------

-----------------------------------------------------------------------------------

1. 주어진 SQL 문와 Trace 결과를 바탕으로 최적의 성능으로 튜닝하시오

    ** 네버스탑 님의 댓글로 수정

    ** 불끈님의 댓글로 수정

 

1) SQL Text >

SELECT a.상품명, b. ... , c. ...

  FROM a, b, c

 WHERE a.ProdCode LIKE 'ZZ%'

   AND b.ContCode = a.ProdCode

   AND c.col1 = ...

   AND c.col2 >=  (범위조건)

   AND c.col3 = b.col4

 

2) SQL Trace

NL --> 56 rows

 NL --> 1000 rows

   TA 

      INDEX TA - range scan (pk : prodcode )   -->  100 rows (CR =5)

   TB             --> 1000 rows (CR = 2300)

      INDEX TB - range scan (pk : contcode + ? ) --> 1000 rows (CR = 100)

  INDEX TC --> 56 rows (150 block?)

 

 

3) 특이사항

   - SELECT 절에 A 테이블의 컬럼은 없음

   - C 테이블로의 접근은 없음 

   - INDEX TA, INDEX TB 는 PK 인덱스임

 

 

2. 주어진 SQL 문의 최적 SQL 을 재작성하시오 (필요하면 INDEX 생성)

    * 네버스탑, LunaStar 님의 댓글로 수정

 

1) SQL TEXT >

SELECT ...

  FROM a, b

 WHERE a.ContCode = b.ProdCode

   AND SUBSTR(a.dt,1,6) = SUBSTR(b.dt,1,6)

   AND b.id = :id

   AND b.ProdCode IS NOT NULL

   AND a.code IN ('A','B','C','1','2','3')

UNION ALL

SELECT ...

  FROM a, b

 WHERE a.ContCode = b.UserCode

   AND SUBSTR(a.dt,1,6) = SUBSTR(b.dt,1,6)

   AND b.id = :id

   AND b.ProdCode IS NULL

   AND a.code IN ('A','B','C','1','2','3')

 

2) SQL 전제 조건

 - a Table은 500만건

 - b Table은 5000만건

 - a.ContCode의  Cardinality가 10 

 - b.ProdCode의  Cardinality가 50 

 - a.code의 값은 7개가 있으며 균등분포함

 

-----------------------------------------------------------------------------------

 

감사합니다~~~~

 

 

<복원 1번 문제 Sample Data Script>

DBA_OBJECTS 를 사용하여 문제와 유사한 TRACE 생성 (데이터 포함)

 

--DROP TABLE SQLP20_01 PURGE;

--DROP TABLE SQLP20_02 PURGE;

--DROP TABLE SQLP20_03 PURGE;

 

CREATE TABLE SQLP20_01 AS SELECT * FROM SYS.DBA_OBJECTS;

CREATE TABLE SQLP20_02 AS SELECT * FROM SYS.DBA_OBJECTS;

CREATE TABLE SQLP20_03 AS SELECT * FROM SYS.DBA_OBJECTS;

 

CREATE INDEX PK_SQLP20_01 ON SQLP20_01 (OWNER);

CREATE INDEX PK_SQLP20_02 ON SQLP20_02 (OWNER,OBJECT_NAME);

CREATE INDEX IX_SQLP20_03 ON SQLP20_03 (OBJECT_NAME,OBJECT_ID,OBJECT_TYPE) ;

 

--Sample Data Count

SELECT COUNT(*) FROM SYS.DBA_OBJECTS;

--72805

 

--DROP SEQUENCE SQ_SQLP20_01;

CREATE SEQUENCE SQ_SQLP20_01 INCREMENT BY 1 START WITH 1 CYCLE MAXVALUE 100;

 

UPDATE SQLP20_01

SET OWNER = 'Z1' || SQ_SQLP20_01.NEXTVAL

WHERE LENGTH(OWNER) > 2

AND ROWNUM < 101;

 

COMMIT;

 

UPDATE SQLP20_02

SET OWNER = 'Z1' || SQ_SQLP20_01.NEXTVAL

WHERE LENGTH(OWNER) > 2

AND ROWNUM < 1001;

 

COMMIT;

 

UPDATE SQLP20_02

SET OBJECT_NAME = 'SQLP'|| SQ_SQLP20_01.NEXTVAL

WHERE LENGTH(OBJECT_NAME) > 2

AND OWNER LIKE 'Z1%'

AND ROWNUM < 101;

 

COMMIT;

 

UPDATE SQLP20_03

SET OBJECT_NAME = 'SQLP'|| SQ_SQLP20_01.NEXTVAL

WHERE LENGTH(OBJECT_NAME) > 2

AND ROWNUM < 50001;

 

COMMIT;

 

UPDATE SQLP20_03

SET OBJECT_NAME = OBJECT_NAME||'03'

WHERE OBJECT_NAME NOT LIKE 'SQLP%';

 

COMMIT;

 

UPDATE SQLP20_03

SET OBJECT_ID = SQ_SQLP20_01.NEXTVAL * 10

WHERE OBJECT_NAME LIKE 'SQLP%'

AND ROWNUM < 40001;

 

COMMIT;

 

UPDATE SQLP20_03

SET OBJECT_TYPE = 'SQLP'

WHERE OBJECT_NAME LIKE 'SQLP%'

AND OBJECT_ID > 10

AND ROWNUM < 57;

 

COMMIT;

 

EXEC DBMS_STATS.GATHER_TABLE_STATS('LSS','SQLP20_01');

EXEC DBMS_STATS.GATHER_TABLE_STATS('LSS','SQLP20_02');

EXEC DBMS_STATS.GATHER_TABLE_STATS('LSS','SQLP20_03');

 

<SQL TRACE 용 SQL문>

 

SELECT /*+ LEADING(A B C) USE_NL(B) USE_NL(C) */

       B.OBJECT_ID,B.OBJECT_NAME,C.OBJECT_TYPE,C.OBJECT_ID

FROM SQLP20_01 A,

     SQLP20_02 B,

     SQLP20_03 C

WHERE 1=1

AND A.OWNER = B.OWNER

AND B.OBJECT_NAME = C.OBJECT_NAME

AND A.OWNER LIKE 'Z1%'

AND C.OBJECT_TYPE = 'SQLP'

AND C.OBJECT_ID > 10

 

 

cafe.naver.com/sqlpd/5115