OCP 교육 정리 - 오라클 튜닝 (2)

<OCP 자격증 교육 정리 - 오라클 튜닝 (2)>

*잠겨있는 사용자 계정 활성화 방법 (비밀번호 변경도 함께)
sqlplus / as sysdba
SQL> alter user hr identified by hr account unlock;
SQL> alter user sh identified by sh account unlock;
SQL> alter user scott identified by tiger account unlock;

--dictionary
select * from dictionary;

select * from user_indexes;
select * from user_tables;

select * from jobs;

* 실행계획
1. dbms_xplan.display 실행계획 PLAN SET 
explain plan
set statement_id = 'demo01' for
select e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id = d.department_id;

explain plan for
select e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id = d.department_id;

select * from table(dbms_xplan.display());

--scott/tiger
explain plan
set statement_id='demo01' for
select * from emp 
where ename='KING';

select * from table(dbms_xplan.display());
select * from table(dbms_xplan.display(null, null, 'ALL')); 
select * from table(dbms_xplan.display(null, null, 'ADVANCED -PROJECTION -PREDICATE -ALIAS')); --자세히

* 실행계획 PLAN_TABLE 테이블 생성
cd $ORACLE_HOME/rdbms/admin
sqlplus hr/hr @utlxplan.sql

* autotrace를 실행하기 위한 권한 할당
grant select_catalog_role to hr;
grant select any dictionary to hr;


* 계획설명 F10
* Autotrace F6
conn hr/hr
1)
set autotrace traceonly;
select * from v$sql_plan;::

2)
set autotrace on statistics;
select * from v$sql_plan;

3)
set autotrace traceonly statistics;
select * from v$sql_plan;


* v$sql에서 SQL_ID 얻어 실행계획보기
select sql_id, sql_text from v$sql
where sql_text like '%select e.last_name,%'; --5ct951bdw2cy2

select plan_table_output from table(DBMS_XPLAN.DISPLAY_CURSOR('5ct951bdw2cy2'));


* PL/SQL을 통한 AWR 관리
exec dbms_workload_repository.create_snapshot('ALL');
select * from dba_hist_snapshot;

* df -h 용량확인
wget 파일받기
tar xvfz 압축풀기
./01_query_execute.sh 실행

* row id
select rowid, e.*
from employees e 
where rownum <10;

* 유니크 인덱스
create unique index pk_emp on emp(empno);

select * from emp 
where empno = 9999;

* 인덱스
create index idx_emp_deptno on emp(deptno);
select /*+ index(emp idx_emp_deptno) */ * 
from emp
where deptno = 10 
and sal > 1000;

select * from emp
where deptno >20
order by deptno desc;

* 함수기반인덱스
create index ix_fbi on emp(upper(ename));
select *
from emp
where upper(ename) like 'A%';

* Fast Full Scan
select /*+ index_ffs(emp idx_emp_deptno) */ deptno
from emp
where deptno is not null;

* skip scan
create index ix_ss on emp(deptno, sal);
select /*+index_ss(emp ix_ss) */ *
from emp
where sal<1500;

# 참고: http://adminschool.net > oracle > oracle developer guide