오라클 튜닝//옵티마이저

--오라클 엔터프라이즈 매니저 콘솔 사용법
--.sql 문 실행
--튜닝
 -실행 계획 확인 모드 설정
 -실행계획

 -옵티마이저
  -공식 기반 옵티마이저
 -4순위
 -8순위 복합 인덱스
 -9순위
//////////////////////////////////////////////////////////

C:\oracle\ora92\network\admin\tnsnames.ora 확인(네트워크 클라이언측 설정 파일)
C:\oracle\ora92\network\admin\listener.ora 설정(네트워크 서버측 설정 파일)
(ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST =218.234.13.180)(PORT = 1521))
자기 ip 로 바꿔줌

system/manager 로 접속
---설정후 다른 사람 디비에 접속---

sqlplus scott@jong/tiger
sqlplus (사용자)(컴터이름)(비밀번호)

--튜닝(속도향상)

conn /as sysdba

--셀렉트 권한 부여 롤로
@C:\oracle\ora92\sqlplus\admin\plustrce.sql

re:
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
1행에 오류:
ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다

SQL> create role plustrace;

롤이 생성되었습니다.

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

권한이 부여되었습니다.

SQL> grant select on v_$statname to plustrace;

-- 생성한 권한 scott  부여
grant plustrace to scott;

권한이 부여되었습니다.

SQL> grant select on v_$session to plustrace;

권한이 부여되었습니다.

SQL> grant plustrace to dba with admin option;

권한이 부여되었습니다.

SQL>
SQL> set echo off
SQL>

SQL> grant plustrace to scott;

권한이 부여되었습니다.

새로 .sql 문 실행
@C:\oracle\ora92\rdbms\admin\utlxplan.sql

re:
테이블이 생성되었습니다

select * from division;

re:

      DNO DNAME                PHONE                POSITION
--------- -------------------- -------------------- ----------
       10 FINANCE              032-277-0411         INCHON
       20 RESEARCH             061-535-1212         BUSAN
       30 SALES                02-555-4985          SEOUL
       40 MARKETING            031-284-3800         SUWON

---실행 계획 확인 모드 설정

set autotrace on;
select * from division;
re:

       DNO DNAME                PHONE                POSITION
---------- -------------------- -------------------- ----------
        10 FINANCE              032-277-0411         INCHON
        20 RESEARCH             061-535-1212         BUSAN
        30 SALES                02-555-4985          SEOUL
        40 MARKETING            031-284-3800         SUWON

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DIVISION'

 

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

 

select * from disivion;
set autotrace trace;
select * from division;

------------------------ 스콧이 없을 경우-----------------
create tablespace ejb1 
 datafile 'd:\iedu\ejb1.dbf' size 100m;

create user scott
 identified by tiger
 default tablespace ejb1
 temporary tablespace ejbtemp;
grant connect, resource to scott;

host
imp system/manager file=d:\iedu\scott.dmp from user=scott

exit
conn scott/tiger

@C:\oracle\ora92\rdbms\admin\utlxplan.sql

--실행계획
set autotrace off;
select * from emp;
select index_name,table_name from user_indexes;
re:

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
SYS_C003021                    STUDENT
SYS_C003022                    SCORE
SYS_C003023                    TECH
SYS_C003024                    PART

--(다 나온것 다 지우기 인덱스== 기본키 지우면 다 없음)
alter table part
 drop primary key;

select index_name,table_name from user_indexes;
re:
선택된 레코드가 없습니다.

select ename,sal,dname from emp e,dept d
 where e.emp=d.dept;

select ename.sal,dname from emp e.dept d
 order by dname;

select deptno,avg(sal) from big_emp
 group by deptno;

select ename,sal from emp e where deptno=30;

--인덱스 가 존재 하지 않으므로 인덱스 생성

create index i_big_emp_deptno on big_emp(deptno);

select ename,sal from big_emp e where deptno=30;

//////////////////////////////////////////////
--옵티마이저
-공식 기반 옵티마이저(우선순위)

(공식기반 옵티마이저 실행우선순위)

1.ROWID에 의한 단일행 실행
2.Cluster-Join에 의한 단일행 실행
3.Unique-Key, Primary-Key를 사용한 Hash-Cluster Key에 의한   단일행 실행
4.Unique-Key, Primary-Key에 의한 단일행 실행
5.Cluster 조인
6.Hash-Cluster Key
7.인덱스화된 Cluster-Key
8.복합 인덱스
9.단일 칼럼 인덱스(EQUAL)
10.인덱스가 구축된 칼럼에 대한 제한된 범위 검색 (BETWEEN,     LIKE, < AND >, =표현식)
11.인덱스가 구축된 컬럼에 대한 무제한 범위의 검색 (>=,=<표   현식)

 

ALTER SESSION
 SET OPTIMIZER_MODE = RULE;

select ename,sal from big_emp e where deptno=30;
----------------------------------------------
select ename,sal from big_emp e
 where deptno=30;
select ename,sal from big_emp e
 where deptno between 30 and 80;
select ename,sal from big_emp e
 where deptno>30;

차례대로 순서 가 빠름
기본키로 두면 (rowid)를 두면 더 빠름

 

 

 

--4순위
alter table big_dept
 add constraint big_dept_deptno_pk primary key(deptno);
select * from big_dept where deptno=30;

-컴퓨터에 존재하지 않아 테이블 다른 걸로 변경
alter table  tech
 add constraint  tech_bunho_pk  primary key (bunho);

select * from tech;
 
--8순위 복합 인덱스

create index i_big_emp_job_deptno
 on big_emp(job,deptno);

select * from big_emp
 where job='ANALYST' and deptno=40;

drop index i_big_emp_job_deptno;

-9순위
-실행계획 취소 모드
set autorace off
--인덱스 확인
select index_name from user_indexes;

set autorace trace

select * from big_emp where deptno=30 and job='SALESMAN';