Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:Documents and Settingsedu>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on 월 6월 2 12:35:02 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

사용자명 입력: system
암호 입력:

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> drop user scott cascade;

사용자가 삭제되었습니다.

SQL> create user scott identified by tiger;

사용자가 생성되었습니다.

SQL> grant connect, resource, dba to scott;

권한이 부여되었습니다.

SQL> exit
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options에서 분리되었습니다.

C:Documents and Settingsedu>cd C:oracleproduct10.2.0db_1BIN

C:oracleproduct10.2.0db_1BIN>imp system/manager full=y file=/NEW_SCOTT_10.DMP

Import: Release 10.2.0.1.0 - Production on 월 6월 2 13:38:37 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

다음에 접속됨: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

엑스포트 파일은 규정 경로를 거쳐 EXPORT:V10.02.01 에 의해 생성되었습니다
KO16MSWIN949 문자집합과 AL16UTF16 NCHAR 문자 집합에 임포트가 완성되었습니다
. SYSTEM 객체를 SYSTEM(으)로 임포트하는 중입니다
. SCOTT 객체를 SCOTT(으)로 임포트하는 중입니다
IMP-00008: 엑스포트 파일에 인식할 수 없는 명령이 있습니다:

. . 테이블                      "ACCOUNT"(를)을 임포트 중      28969 행이 임포트되었습니다
. . 테이블                     "BIG_DEPT"(를)을 임포트 중        289 행이 임포트되었습니다
. . 테이블                      "BIG_EMP"(를)을 임포트 중      28955 행이 임포트되었습니다
. . 테이블                         "DEPT"(를)을 임포트 중          4 행이 임포트되었습니다
. . 테이블                          "EMP"(를)을 임포트 중         14 행이 임포트되었습니다
. . 테이블                "LARGE_ACCOUNT"(를)을 임포트 중     460938 행이 임포트되었습니다
. . 테이블                   "LARGE_DEPT"(를)을 임포트 중        289 행이 임포트되었습니다
. . 테이블                    "LARGE_EMP"(를)을 임포트 중    1091410 행이 임포트되었습니다
. . 테이블                   "PLAN_TABLE"(를)을 임포트 중          0 행이 임포트되었습니다
. . 테이블                    "S_CHANGGO"(를)을 임포트 중          3 행이 임포트되었습니다
. . 테이블                 "S_CHULHAJISI"(를)을 임포트 중     802095 행이 임포트되었습니다
. . 테이블                     "S_GOGAEK"(를)을 임포트 중     378789 행이 임포트되었습니다
. . 테이블                      "S_JAEGO"(를)을 임포트 중     274276 행이 임포트되었습니다
. . 테이블                     "S_MAECHE"(를)을 임포트 중        140 행이 임포트되었습니다
. . 테이블                  "S_PROMOTION"(를)을 임포트 중       1063 행이 임포트되었습니다
. . 테이블                    "S_SANGPUM"(를)을 임포트 중        592 행이 임포트되었습니다
. . 테이블                    "S_SPINOUT"(를)을 임포트 중     154673 행이 임포트되었습니다
. . 테이블                       "S_SUJU"(를)을 임포트 중     827447 행이 임포트되었습니다
. . 테이블                   "S_SUJU_DTL"(를)을 임포트 중    4136970 행이 임포트되었습니다
. . 테이블                    "S_ZIPCODE"(를)을 임포트 중      49033 행이 임포트되었습니다
C:oracleproduct10.2.0db_1BIN>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on 월 6월 2 13:43:48 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ACCOUNT                        TABLE
BIG_DEPT                       TABLE
BIG_EMP                        TABLE
DEPT                           TABLE
EMP                            TABLE
LARGE_ACCOUNT                  TABLE
LARGE_DEPT                     TABLE
LARGE_EMP                      TABLE
PLAN_TABLE                     TABLE
S_CHANGGO                      TABLE
S_CHULHAJISI                   TABLE
S_GOGAEK                       TABLE
S_JAEGO                        TABLE
S_MAECHE                       TABLE
S_PROMOTION                    TABLE
S_SANGPUM                      TABLE
S_SPINOUT                      TABLE
S_SUJU                         TABLE
S_SUJU_DTL                     TABLE
S_ZIPCODE                      TABLE

20 개의 행이 선택되었습니다.

• PGA (Program Global Area) : 유저 프로세스를 처리하는 서버 프로세스에서 생성하여 사용하는 메모리 영역
• SGA (System Global Area) : 디스크에서 읽혀진 데이터가 저장되고 그 데이터들이 읽혀지거나 변경되어지는 작업에 사용되는 공용 메모리 영역
• UGA (User Global Area) : PGA의 부분 집합(정렬 공간, 세션 정보, 커서 상태 정보)

C:Usersuser>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 화 6월 10 20:58:20 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @d:/oracle/product/10.2.0/db_1/sqlplus/admin/plustrce.sql
SQL> drop role plustrace;

롤이 삭제되었습니다.

SQL> create role plustrace;

롤이 생성되었습니다.

SQL>
SQL> grant select on v_$sesstat to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$statname to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$mystat to plustrace;

권한이 부여되었습니다.

SQL> grant plustrace to dba with admin option;

권한이 부여되었습니다.

SQL> set echo off
SQL> grant plustrace to scott;

권한이 부여되었습니다.

SQL> connect scott/tiger
연결되었습니다.
SQL> @d:/oracle/product/10.2.0/db_1/rdbms/admin/utlxplan.sql

테이블이 생성되었습니다.

SQL> set autotrace trace
SQL> select * from big_emp where deptno=10;

5336 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------
Plan hash value: 3814977537

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   294 | 11760 |    43   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BIG_EMP |   294 | 11760 |    43   (5)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=10)

Statistics
----------------------------------------------------------
0  recursive calls --> symentics 수행시 자동으로 실행되는 내부 SQL
0  db block gets --> physical read, 0 이면 이전의 같은실행결과를 buffer cache에서 가져옴
        539  consistent gets --> DML의 경우 "변경후" 값이 저장되는 크기
          0  physical reads --> 물리적 read, 위의 2개보다 보통은 작다.(메모리가 작다면 더 크다)
          0  redo size
256694  bytes sent via SQL*Net to client --> SQL + 기타정보들이 서버로...
       4290  bytes received via SQL*Net from client --> 클라이언트로 보낸 양
357  SQL*Net roundtrips to/from client --> 357번 왔다갔다..
0  sorts (memory) --> sort시 사용된 메모리 공간 (PGA)
0  sorts (disk) --> Temp Segment Table에 저장된 내용, 여기가 크다면 PGA가 부족하다는 뜻
--> 튜닝방법 : PGA 공간 크게 : alter session set sort_area_size=10M;
5336  rows processed

SQL> set autotrace on
SQL> select ename, job, sal, dname
2  from emp, dept
3  where emp.deptno = dept.deptno;

ENAME      JOB              SAL DNAME
---------- --------- ---------- --------------
SMITH      CLERK            800 RESEARCH
ALLEN      SALESMAN        1600 SALES

14 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   854 |     5  (20)| 00:00:01 |  --> (4)
|*  1 |  HASH JOIN         |      |    14 |   854 |     5  (20)| 00:00:01 |  --> (3)
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     2   (0)| 00:00:01 |  --> (1)
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     2   (0)| 00:00:01 |  --> (2)
---------------------------------------------------------------------------

동일레벨이면 먼저 나온 operation 부터
상/하위 레벨이면 하위 operation 부터
Join 종류 ; hash, sort-merge, nested-loop

 

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
456  recursive calls
0  db block gets
69  consistent gets
6  physical reads
0  redo size
996  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
10  sorts (memory)
0  sorts (disk)
14  rows processed

1. AND-EQUAL
- row operation
- 인덱스들에 의해 반환되는 rowid를 병합해서 공통값만을 반환2. CONCATENATION
- row operation
- 반환된 로우들의 합산하는 역할3. connect by
- 계층형 쿼리 실행되는 경우
- 성능을 위해 start with와 connect by절이 인덱스를 사용하여 풀리게 해야 함4. count
- row operation
- rownum pseudo column이 where이 아닌 select문에 위치할때 나타남
- currval, nextval, level, rowid, rownum5. count stopkey
- row operation
- where 절 안에서 조건자 구실을 할 때 나타남

6. filter
- where 절 조건절에서 인덱스 사용못할 때
- 해석은  nested loop와 동일

7. for update
- set operation
- select row에 대한 row-level lock걸게하는 연산
- transaction 종료시 종료

8. hash join
- row or set operation
- 바로 아래 테이블을 비트맵형식으로 메로리 로드 후, 해시 기법으로 조건 로우 추출
- 조인시에 두 테이블 중 한 테이블이 나머지 테이블에 비해 크기가 상당히 작고 메모리에 로드될 공간이 있을때
- 머지조인보다 성능이 나음
- 상위에 위치한 테이블의 사이즈가 작을때 좋은 성능을 낼 수 있다.
- use_hash 힌트는 ordered 힌트 구문과 같이 사용된다.

9. hash anti-join
- 한쪽 테이블의 로우를 추출할 때, 다른 나머지 테이블의 로우와는 매칭되지 않는 로우만 골라내는 조인
- not in/not exists/minus

10. hash semi-join
- exists 절을 이용하는 서브쿼리
- 지원 인덱스가 없는 exists 절을 사용하는 쿼리에서 hash_sj 또는 merge_sj 힌트

11. index range scan
- row operation
- 인덱스를 일정 범위의 값들로 제한을 두고 스캔
- <, >, between, like 'a%'

12. index range scan descending
- 역순으로 인덱스블록 스캔
- 순서로 내림차순

13. index unique scan
- 유니크한 인덱스로부터 유니크한 값을 뽑아내는 로우 연산

14. inlist iterator
- in-list 절에 나오는 인수 갯수만큼 반복연산 수행

15. itersection
- set operation
- 여러 쿼리의 반환 결과셋을 병합하여 공통부분만을 추출
- intersect/union/minus sql연산자는 실행계획시에 항상 sort연산 동반
- union all 의 경우 sort 없음

16. merge join
- 테이블을 각각 따로 정렬한 후에 그 결과를 병합하는 집합연산
- 배치에서 효율적일 수 있음
- 조인 컬럼의 인덱스를 사용할 수 없을 때는 수립
- 실행계획상에 나타나는 테이블의 순서는 중요하지 않음

17. merge anti join

18. merge semi join

19. minus
- set operation
- 첫번째 쿼리 결과에서 두번째 쿼리 결과 제거

20. nested loops
- row operation
- 조인되는 컬럼 중에 적어도 하나에 인덱스가 존재할 때
- 상단 테이블이 driving테이블 아래 테이블이 probed table이 된다
- from절에 나오는 테이블의 순서에 따라 실행계획이 달라진다

SQL> connect as sysdba
사용자명 입력: system
암호 입력:
연결되었습니다.
SQL> show parameter timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE ==> true가 default, true 여야 sql*trace 사용가능
SQL> show parameter max_dump_file_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                   string      UNLIMITED ==> trace dump 파일 최대 크기
SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      C:ORACLEPRODUCT10.2.0ADMIN
ORCLUDUMP ==> 여기에 trace dump 파일이 생성됨

SQL> show parameter sql_trace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql_trace                            boolean     FALSE ==> false 가 아니면 모든 세션의 trace가 됨

SQL> connect as sysdba
사용자명 입력: system
암호 입력:
연결되었습니다.

SQL> startup force
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area  281018368 bytes
Fixed Size                  1248528 bytes
Variable Size              88081136 bytes
Database Buffers          184549376 bytes
Redo Buffers                7139328 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.

SQL> connect scott/tiger
연결되었습니다.
SQL> alter session set sql_trace = true;

세션이 변경되었습니다.

SQL> select * from big_emp where deptno = 01;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO G
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- -
29709 SMITH      CLERK           7902 83/06/13     1654.5          1
     28709 SMITH      CLERK           7902 83/06/13     1654.5

ORA_xxxxx.trc 파일이 생성됨 ==> C:oracleproduct10.2.0db_1BINtkprof.exe 로 분석이 가능함

tkprof 실행
C:oracleproduct10.2.0adminorcludump>C:oracleproduct10.2.0db_1BINtkprof orcl_ora_3160.trc kimstar.tkf sys=no explain=scott/tiger
(input file, output file Recursive-SQL 표시여부 실행계획 표시)
output file
TKPROF: Release 10.2.0.1.0 - Production on 월 6월 2 15:21:12 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.Trace file: orcl_ora_3160.trc
Sort options: default********************************************************************************
count    = number of times OCI procedure was executed : 프로시저 실행 수
cpu      = cpu time in seconds executing : 순수한 cpu 사용시간
elapsed  = elapsed time in seconds executing : 다른놈도 cpu 사용하므로 총 걸린시간 (환경에 따라 elapsed 시간이 틀리므로 여러번 실행후 평균을 잡아야 할것임)
disk     = number of physical reads of buffers from disk : 디스크에서 읽은 버퍼수
query    = number of buffers gotten for consistent read : 메모리에서 가져온 버퍼수
current  = number of buffers gotten in current mode (usually for update) : DML이면 변경전 데이터를 읽은 수 (update, delete, insert 사용후 select 했을때)
rows     = number of rows processed by the fetch or execute call : 필터링되어 리턴된 row갯수
********************************************************************************

alter session set sql_trace = true

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  (SCOTT)
********************************************************************************

select *
from
big_emp where deptno = 01

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.01       0.03        183        188          0          35
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.04       0.04        183        188          0          35

Misses in library cache during parse: 1 --> 0이면 소프트 파싱, 1이면 SGA Cache에 없어서 하드 파싱
Optimizer mode: ALL_ROWS --> 옵티마이저
Parsing user id: 61  (SCOTT) --> 사용자

Rows     Row Source Operation
-------  ---------------------------------------------------
35  TABLE ACCESS FULL BIG_EMP (cr=188 pr=183 pw=0 time=11044 us)

Rows     Execution Plan --> 실행계획
-------  ---------------------------------------------------
0  SELECT STATEMENT   MODE: ALL_ROWS
35   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'BIG_EMP' (TABLE)

 

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

parse count * 0.01 < parse cpu --> 라이브러리 캐쉬 영역이 작음을 의미 --> SHARED_POOL_SIZE 조절
parse count * 0.03 < parse disk --> 데이터 딕셔네리 영역이 작음을 의미 --> SHARED_POOL_SIZE 조절
(execute disk * fetch disk) < ((execute query + fetch query) + (execute current + fetch current) * 0.1) --> 데이터버퍼캐시 영역 작음 --> DB_CHACHE_SIZE 조절

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.01          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.01       0.03        183        188          0          35
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.04       0.04        183        188          0          35

Misses in library cache during parse: 1
Misses in library cache during execute: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       12      0.01       0.00          0          0          0           0
Execute    123      0.09       0.07          0          0          0           0
Fetch      136      0.00       0.08         20        352          0         321
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      271      0.10       0.16         20        352          0         321

Misses in library cache during parse: 12
Misses in library cache during execute: 12

    2  user  SQL statements in session.
123  internal SQL statements in session.
125  SQL statements in session.
1  statement EXPLAINed in this session.
********************************************************************************

아래는 trace 파일 생성에 대한 정보임

Trace file: orcl_ora_3160.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
2  user  SQL statements in trace file.
123  internal SQL statements in trace file.
125  SQL statements in trace file.
14  unique SQL statements in trace file.
1  SQL statements EXPLAINed using schema:
SCOTT.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
1080  lines in trace file.
12  elapsed seconds in trace file.

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0  ==> 0 이면 SGA target 파라메터 값을 참조

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 268M

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT  ==> 9i부터 생긴 파라메터, 바인더 변수 사용시 동일한 파싱결과로 생각한다... (selec * from xxx where deptno=:a)

SQL> alter system set cursor_sharing=similar; --> 비슷하면 재사용하겠다는 뜻, exact는 완전동일

시스템이 변경되었습니다.

SQL> select * from dept where deptno = 10;

    DEPTNO DNAME          LOC
---------- -------------- --------------
10 ACCOUNTING     NEW YORK

SQL> select * from dept where deptno = 20;

    DEPTNO DNAME          LOC
---------- -------------- --------------
20 RESEARCH       DALLAS

SQL> @/sql_area.sql

SQL_TEXT
-------------------------------------------------------------
VERSION_COUNT      LOADS INVALIDATIONS PARSE_CALLS      SORTS
------------- ---------- ------------- ----------- ----------
select * from dept where deptno = 10
1          1             0           1          0

select * from dept where deptno = :"SYS_B_0"
            1          1             0           1          0

SQL> select * from dept where deptno = 30;

    DEPTNO DNAME          LOC
---------- -------------- --------------
30 SALES          CHICAGO

SQL> @/sql_area.sql

SQL_TEXT
-------------------------------------------------------------
VERSION_COUNT      LOADS INVALIDATIONS PARSE_CALLS      SORTS
------------- ---------- ------------- ----------- ----------
select * from dept where deptno = 10
1          1             0           1          0

select * from dept where deptno = :"SYS_B_0"
            2          2             0           2          0

• SQL문은 대문자 또는 소문자로 통일
• 변수명은 SQL문, 객체명, 컬럼명과 구분하기 위해 소문자로 작성
• 다른 스키마의 테이블을 호출할때는 SCHEMA명.TABLE명으로 작성
• SQL문의 각 단어의 여백은 한칸
• SQL문 내의 변수명은 변수 선언 기준 안에 따르며, 해당 컬럼명을 접두어와 결합하여 사용
• SQL문의 SELECT , FROM, WHERE절은 라인의 선두에 기술

1) select * from emp where empno = 7369;  --> ⑮
2) create inex i_emp_empno on emp(empno);
select * from emp where empno = 7369;  --> ⑨
3) select * from emp where rowid = 'AAAAfBAACAAAAEqAAA'; --> ①

세션에서 룰 변경 : Alter session set optimizer_mode = rule;
설정 변경 : init<SID>.ora 파일에서 OPTIMIZER_MODE = RULE; 로 변경

SQL> create index i_emp_deptno on big_emp (deptno);
인덱스가 생성되었습니다.

SQL> create unique index i_emp_empno on big_emp (empno);
인덱스가 생성되었습니다.

SQL> alter session set optimizer_mode = rule;  --> rule based optimizer 설정
세션이 변경되었습니다.

SQL> set autotrace traceonly --> 실행계획 확인

SQL> select ename
2  from big_emp
3  where deptno = 20  --> 9 순위
4        and empno between 100 and 200  --> 원래는 4순위인데 범위 때문에 10순위
5  order by ename;

30 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------
-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | SELECT STATEMENT             |              |
|   1 |  SORT ORDER BY               |              |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BIG_EMP      |
|*  3 |    INDEX RANGE SCAN          | I_EMP_DEPTNO |  --> 9순위
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPNO"<=200 AND "EMPNO">=100)
3 - access("DEPTNO"=20)

Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
196  consistent gets
15  physical reads
0  redo size
750  bytes sent via SQL*Net to client
411  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
30  rows processed

SQL> create index i_emp_deptno on big_emp(deptno);
인덱스가 생성되었습니다.

SQL> create unique index i_emp_empno on big_emp(empno);
인덱스가 생성되었습니다.

 

SQL> analyze table big_emp compute statistics;  --> 테이블 통계 정보 생성 -> 비용 계산됨 -> Data Dictionary (user_table, user_indexes)에 저장됨, select table_name, last_analyzed from user_tables; 로 확인 가능
테이블이 분석되었습니다.

SQL> analyze index i_emp_deptno compute statistics; --> 실은 위의 테이블에 index가 들어 있어서 별도로 생성하지 않아도 된다. 그냥 한번 해봤음
인덱스가 분석되었습니다.

SQL> analyze index i_emp_empno compute statistics;
인덱스가 분석되었습니다.

 

SQL> alter session set optimizer_mode = choose;  --> rule-based optimizer 선택
세션이 변경되었습니다.

SQL> set autotrace traceonly

 

SQL> select ename
2  from big_emp
3  where deptno = 20
4  and empno between 100 and 200
5  order by ename;

30 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    12 |     5  (20)|
|   1 |  SORT ORDER BY               |             |     1 |    12 |     5  (20)|
|*  2 |   TABLE ACCESS BY INDEX ROWID| BIG_EMP     |     1 |    12 |     4   (0)|
|*  3 |    INDEX RANGE SCAN          | I_EMP_EMPNO |    99 |       |     2   (0)| ->RBO와 다른결과
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPTNO"=20)
3 - access("EMPNO">=100 AND "EMPNO"<=200)

Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
750  bytes sent via SQL*Net to client
411  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
30  rows processed

SQL> select blocks from user_tables
2  where table_name = 'BIG_DEPT';

BLOCKS
----------
1

SQL> connect system/inticube
연결되었습니다.
SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16

SQL> select index_name, clustering_factor
2  from user_indexes
3  where index_name = 'I_EMP_EMPNO';

INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
I_EMP_EMPNO                                  533

잘못된 데이터 타입으로 조건 값을 검색하면 변형된다. (S_DATE 컬럼은 날짜 컬럼인데 문자 값을 검색할 때 사용하는 인용부호를 사용한 경우)
SQL> SELECT * FROM emp WHERE s_date = '1999-01-01';
--> SQL> SELECT * FROM emp WHERE s_date = TO_DATE('1999-01-01');

LIKE 연산자는 %(와일드 카드)와 함께 검색하는 경우 사용되지만, 그렇지 않은 경우 =(동등) 조건으로 변형되어 검색된다.
SQL> SELECT * FROM emp WHERE ename LIKE '주종면‘;
--> SQL> SELECT * FROM emp WHERE ename = ‘주종면’;

BETWEEN ~ AND 조건은 > AND < 조건으로 변형되어 검색된다.
SQL> SELECT * FROM emp WHERE salary BETWEEN 100000 AND 200000;
--> SQL> SELECT * FROM emp WHERE salary >= 100000 and salary <= 200000;

인덱스가 생성되어 있는 컬럼의 IN 연산자의 조건은 OR 연산자의 조건으로 변형된다.
SQL> SELECT * FROM emp WHERE ename IN ('SMITH', 'KING');
--> SQL> SELECT * FROM emp WHERE ename = 'SMITH' or ename = 'KING';

인덱스가 생성되어 있는 컬럼의 OR 연산자의 조건은 UNION ALL로 변형된다.
SQL> SELECT * FROM emp WHERE ename = 'SMITH' or sal = 1000;
--> SELECT * FROM emp WHERE ename = 'SMITH'
UNION ALL
SELECT * FROM emp WHERE sal = 1000;

SQL> set autotrace off
SQL> select deptno, count(*) from big_emp
2  group by deptno
3  order by count(*) desc;

    DEPTNO   COUNT(*)
---------- ----------
        30       9362
20       7231
10       5336
60       1263
50        660
4        127
21        126
11        104
102
84        102
35        102

SQL> analyze table big_emp compute statistics for columns deptno size 99; ===> historgram 적용

테이블이 분석되었습니다.

SQL> select * from big_emp where deptno=38;

Execution Plan
----------------------------------------------------------
Plan hash value: 1169875452

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     5 |   180 |     2 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_EMP          |     5 |   180 |     2 (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_BIG_EMP_DEPTNO |     5 |       |     1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=38)

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
8  consistent gets
0  physical reads
0  redo size
1121  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
5  rows processed

SQL> select * from big_emp where deptno=30;

9362 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------
Plan hash value: 3814977537

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  9362 |   329K|    44   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BIG_EMP |  9362 |   329K|    44   (5)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=30)

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
808  consistent gets
0  physical reads
0  redo size
434370  bytes sent via SQL*Net to client
7249  bytes received via SQL*Net from client
626  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
9362  rows processed

SQL> set autotrace off
SQL> select table_name, column_name, num_distinct, num_buckets, sample_size
2  from user_tab_columns
3  where table_name='BIG_EMP' and column_name='DEPTNO';

TABLE_NAME         COLUMN_NAME        NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE
------------------ ------------------ ------------ ----------- -----------
BIG_EMP            DEPTNO                       98          98       28955

excute dbms_stat.gather_table_stats('소유자', '테이블');  --> 테이블 통계 생성
excute dbms_stat.create_table_stats('소유자', '통계테이블');  --> 백업할 통계테이블 생성
excute dbms_stat.export_schema_stats('소유자', '통계테이블');  --> 백업 실행
excute dbms_stat.import_table_stats('소유자', '테이블', null, '통계테이블');  --> 복원 실행

C:Documents and Settingsedu>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on 화 6월 3 11:29:12 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>  analyze table big_emp delete statistics;  ===> 실습위해 기존 통계 정보 지우고 시작하자
테이블이 분석되었습니다.

SQL> set serveroutput on  ===> 화면출력
SQL> DECLARE
2     num_rows NUMBER;
3     num_blocks NUMBER;
4     avg_row_len NUMBER;
5  BEGIN
6     DBMS_STATS.GET_TABLE_STATS('SCOTT', 'BIG_EMP', null, NULL, null, num_rows, num_blocks, avg_row_len); ===> 통계정보가 없는데 읽으라고 하니 오류가 난다.
7     DBMS_OUTPUT.PUT_LINE('num_rows='||num_rows||',num_blocks='||num_blocks||',avg_row_len='||avg_row_len);
8  END;
9  /
DECLARE
*
1행에 오류:
ORA-20000: Unable to get values for table BIG_EMP
ORA-06512: "SYS.DBMS_STATS", 줄 4314에서
ORA-06512: "SYS.DBMS_STATS", 줄 4331에서
ORA-06512: 줄 6에서

SQL> execute dbms_stats.gather_table_stats('SCOTT', 'BIG_EMP');  ===> 통계정보 생성

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> DECLARE
2     num_rows NUMBER;
3     num_blocks NUMBER;
4     avg_row_len NUMBER;
5  BEGIN
6     DBMS_STATS.GET_TABLE_STATS('SCOTT', 'BIG_EMP', null, NULL, null, num_rows, num_blocks, avg_row_len);  ===> 정상 실행됨
7     DBMS_OUTPUT.PUT_LINE('num_rows='||num_rows||',num_blocks='||num_blocks||',avg_row_len='||avg_row_len);
8  END;
9  /
num_rows=29239,num_blocks=182,avg_row_len=40

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> execute dbms_stats.create_stat_table('SCOTT', 'KIMSTAR_STATS');  ===> 백업테이블 생성

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> execute dbms_stats.export_schema_stats('SCOTT', 'KIMSTAR_STATS'); ===> 통계를 테이블에 백업

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> execute dbms_stats.delete_table_stats('SCOTT', 'BIG_EMP');  ===> 통계 삭제

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> DECLARE
2     num_rows NUMBER;
3     num_blocks NUMBER;
4     avg_row_len NUMBER;
5  BEGIN
6     DBMS_STATS.GET_TABLE_STATS('SCOTT', 'BIG_EMP', null, NULL, null, num_rows, num_blocks, avg_row_len);  ===> 또 에러
7     DBMS_OUTPUT.PUT_LINE('num_rows='||num_rows||',num_blocks='||num_blocks||',avg_row_len='||avg_row_len);
8  END;
9  /
DECLARE
*
1행에 오류:
ORA-20000: Unable to get values for table BIG_EMP
ORA-06512: "SYS.DBMS_STATS", 줄 4314에서
ORA-06512: "SYS.DBMS_STATS", 줄 4331에서
ORA-06512: 줄 6에서

SQL> execute dbms_stats.import_table_stats('SCOTT', 'BIG_EMP', null, 'KIMSTAR_STATS'); ===> 복원

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> DECLARE
2     num_rows NUMBER;
3     num_blocks NUMBER;
4     avg_row_len NUMBER;
5  BEGIN
6     DBMS_STATS.GET_TABLE_STATS('SCOTT', 'BIG_EMP', null, NULL, null, num_rows, num_blocks, avg_row_len); ===> 정상처리
7     DBMS_OUTPUT.PUT_LINE('num_rows='||num_rows||',num_blocks='||num_blocks||',avg_row_len='||avg_row_len);
8  END;
9  /
num_rows=29239,num_blocks=182,avg_row_len=40

PL/SQL 처리가 정상적으로 완료되었습니다.