<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
'IT > OCP 자격증 (취득)' 카테고리의 다른 글
OCP 교육정리 - PLAN TABLE 생성 (0) | 2019.12.01 |
---|---|
OCP 교육정리 - 오라클 시작/종료, 용량 체크, 계정 활성화 (0) | 2019.12.01 |
OCP 교육정리 - 오라클 튜닝(1) (0) | 2019.12.01 |