๐ก ์ ๊ทํ๋ฅผ ๊ธฐ๋ณธ์ผ๋ก ํ๋ ์ตํ์ ์๋จ์ผ๋ก ๋ฐ์ ๊ทํ ํฉ๋๋ค.
์ค๋ณต๋ ๋ฐ์ดํฐ๊ฐ ํ ์ด๋ธ์ ์ฐ์ํด์ ๋ฐ์ํ์ง ์๋๋ก emp, dept๋ฅผ ๋ถ๋ฆฌํ์ฌ ํ ์ด๋ธ๋ก ๊ตฌ์ฑํ๋ฏ์ด ํ ์ด๋ธ์ ์ฑ๊ฒฉ์ ๋ง์ถฐ์ ๋ถ๋ฆฌํ๋ ๊ฒ์ ๋งํ๋ค. empํ ์ด๋ธ์๋ ์ฌ์์ ๋ํ ์ ๋ณด๋ง ์๊ณ dept์๋ ๋ถ์์ ๋ํ ์ ๋ณด๋ง ์๋ค. ๋ฐ์ดํฐ ์ค๋ณต์ด ์๋๊ฒ ํ๋ ค๊ณ ๋ถ๋ฆฌ๋ฅผ ํ๋ค.
๋ถ๋ฆฌ๋ฅผ ํ๋ค๋ณด๋ ์กฐ์ธ์ด ๋ถ๊ฐํผํด์ก๋ค!
โ๏ธ select ๋ฌธ์ ์ํ ๋จ๊ณ(๊ณผ์ )
1. ํ์ฑ ----------> SQL์ ๊ธฐ๊ณ์ด๋ก ๋ณํํ๊ณ ์คํ๊ณํ์ ์์ฑํ๋ ๊ฒ
2. ์ต์คํํธ
3. ํจ์น
select e.ename, d.loc from e, dept d where e.deptno = d.deptno;
โ ํ ์ด๋ธ 2๊ฐ ์กฐ์ธ์ ํ๋ฉด ๋ง๋ค ์ ์๋ ์คํ๊ณํ์ด ๋ช๊ฐ๊ฐ ๋ ๊น?
1. ์กฐ์ธ ์์ : 2๊ฐ์ง* emp -> dept * dept -> emp
- ์กฐ์ธ ๋ฐฉ๋ฒ : 3๊ฐ์ง
* nested loop ์กฐ์ธ * hash ์กฐ์ธ * sort merge ์กฐ์ธ
โ ์ด 6๊ฐ์ ์คํ๊ณํ์ ์ตํฐ๋ง์ด์ ๋ฅผ ๋ง๋ค๊ณ ๊ฐ์ฅ ๋น์ฉ์ด ์ ๊ฒ ๋๋ ๊ณํ์ ์ ํ
- ํ ์ด๋ธ์ด 3๊ฐ๋ฉด?
์กฐ์ธ ์์ :3 ! = 3*2*1=6
์กฐ์ธ ๋ฐฉ๋ฒ : 3๊ฐ
์ด 18๊ฐ์ ์คํ๊ณํ์ ์์ฑ / ํ ์ด๋ธ 4๊ฐ๋ฉด 72๊ฐ, 5๊ฐ๋ฉด 360๊ฐ.....
์ด์ค์์ ์ ์ผ ์ผ(?) ์คํ๊ณํ ํ๋๋ง ์ ํํ๋๊น ํ์ฑ์ด ๋๋ฌด ๋๋ฆฌ๋ค.โก๏ธ ๋๋ฌด ์กฐ์ธ๋๋ SQL์ด ๋๋ฆฌ๋ค๊ณ ํ๋ค๋ฉด, ๋ฐ์ ๊ทํ๋ฅผ ๊ณ ๋ คํด๋ณด์์ผ ํ๋ค.
1. ์์ ์คํ๊ณํ
explain plan for
select ename, sal
from emp
where ename='SCOTT';
select * from table(dbms_xplan.display);
2. ์ค์ ์คํ๊ณํ
: buffers์ ๊ฐฏ์๋ฅผ ๋ณผ ์ ์๋ค. ํ๋ ์ /ํ๋ฅผ ์ด๊ฒ์ผ๋ก ๊ตฌ๋ถํ ๊ฒ์!
select /*+ gather_plan_statistics */ ename, sal
from emp
where ename='SCOTT';
SELECT *
FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
๋ค๋ฅธ ์ํฐํฐ(ํ
์ด๋ธ)์ ์๋ ์์ฑ(์ปฌ๋ผ)์ ์์ ์ ๊ฒ์ผ๋ก ์ ์ํ๋ ๊ฒ์ ๋งํ๋ค.
ex) emp ํ
์ด๋ธ์ loc์ dname์ ํฌํจ์ํค๋ ๊ฒ์ด๋ค.
์ค์ต1.
์๋์ 2๊ฐ์ง ์ํฉ์ ์ค์ ์คํ๊ณํ์ ๋ณด๊ณ ๋น๊ตํ๊ธฐ
1. ์ ๊ทํ ํ์ ๋
select /*+ gather_plan_statistics */ e.ename, d.loc from emp e, dept d where e.deptno = d.deptno; SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 6 | | | | |* 1 | HASH JOIN | | 1 | 14 | 14 |00:00:00.01 | 6 | 909K| 909K| 716K (0)| | 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 3 | | | | | 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 3 | | | | ----------------------------------------------------------------------------------------------------------------
2. ๋ฐ์ ๊ทํ ํ์ ๋
2-1 emp ํ ์ด๋ธ์ loc์ปฌ๋ผ์ ์ถ๊ฐalter table emp add loc varchar2(13);
2-2 emp ํ ์ด๋ธ์ ์ถ๊ฐํ loc์ปฌ๋ผ์ ๊ฐ์ ํด๋น๋ถ์๋ฒํธ์ ๋ถ์์์น๋ก ๊ฐฑ์
merge๋ฌธ ์ฐธ๊ณmerge into emp e using dept d on (e.deptno=d.deptno) when matched then update set e.loc=d.loc; -- ๋ฐ์ ๊ทํ๋ฅผ ํ์ผ๋ฏ๋ก ์กฐ์ธ์ ์ํด๋ ๋๊ณ ์๋์ ๊ฐ์ด ์ฟผ๋ฆฌํด๋ ๋๋ค. select /*+ gather_plan_statistics */ ename, loc from emp ; SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); ------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 3 | | 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------
โก๏ธ ๋ฒํผ์ ๊ฐฏ์๊ฐ 6๊ฐ์์ 3๊ฐ๋ก ์ค์ด๋ค์๋ค!
๋ฌธ์
(๋ฐ์ ๊ทํ) emp ํ
์ด๋ธ์ dname์ปฌ๋ผ์ ์ถ๊ฐํ๊ณ merge๋ฌธ์ผ๋ก ํด๋น ์ฌ์์ ๋ถ์๋ช
์ผ๋ก ๊ฐ์ ๊ฐฑ์ ํ๊ธฐ
desc dept; -- dname ์ถ๊ฐํ๊ธฐ alter table emp add dname varchar2(14); -- merge๋ฌธ์ผ๋ก ๊ฐ ๊ฐฑ์ merge into emp e using dept d on (e.deptno=d.deptno) when matched then update set e.dname=d.dname; -- ๋ฐ์ ๊ทํ๋ฅผ ํ์ผ๋ฏ๋ก ์กฐ์ธ์ ์ํด๋ ๋๊ณ ์๋์ ๊ฐ์ด ์ฟผ๋ฆฌํด๋ ๋๋ค. select /*+ gather_plan_statistics */ ename, dname from emp ; SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); ------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 3 | | 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------
๐ก primary์ ์ฝ๊ณผ unique ์ ์ฝ์ ๊ฑธ๋ฉด ์๋์ผ๋ก ์ธ๋ฑ์ค๊ฐ ์์ฑ์ด๋๋ค.
alter table emp add constraint emp_empno_pk primary key(empno);
โ๏ธ ์ ์ฝ์ด ์ ์์ฑ๋์๋์ง ํ์ธ
select table_name, constraint_name from user_constraints where table_name='EMP';
โ๏ธ ์ธ๋ฑ์ค๊ฐ ์ ์์ฑ๋์๋์ง ํ์ธselect index_name, uniqueness from user_indexes where table_name='EMP';
โก๏ธ ์ธ๋ฑ์ค ์ด๋ฆ์ด ์ ์ฝ ์ด๋ฆ์ผ๋ก ์์ฑ์ด ๋๋ค.
โ ํ์ฌ์ DB์ ์ค๋ธ์ ํธ ๋ค์ ์ด๋ฆ์ ๊ฒฐ์ ํ ๋ ๊ท์น์ด ์๋ค. ์ด ๊ท์น๋ค์ ๋ฐ๋ผ์ผํจ
์) ํ
์ด๋ธ๋ช
์ 8๊ธ์๋ฅผ ๋์ผ๋ฉด ์๋๊ณ ์ด๋ฆ๋ง๋ค ์๋ฏธ๊ฐ ์์ด์ผํจ.
UEHBLDMT ---> UE HB LD MT
์ ์๊ธ์ต ํ์ฌ์ฝ์ด ํ์ฌ์ฝ์ด ๋ง์คํฐํ
์ด๋ธ
โ ์ธ๋ฑ์ค ์ด๋ฆ๋ ๊ท์น์ด ์๋ค!
UEHBLDIDX1 ---> UE HB LD IDX๋ฒํธ
๊ทธ๋ฐ๋ฐ PK์ ์ ๋ํฌ ์ ์ฝ ๊ฐ์ ๊ฒฝ์ฐ๋ ์ธ๋ฑ์ค๊ฐ ์๋์ผ๋ก ์์ฑ๋๋๋ฐ ์ธ๋ฑ์ค ์ด๋ฆ์ด ์ ์ฝ์ด๋ฆ์ผ๋ก ์์ฑ๋๋๊น ๋ฉํ ๊ท์น์ ๋ง์ง ์์์ ๊ฐ์ฌ๋ ์ง์ ์ด ๋ ์ ์๋ค. DBA๊ฐ ์ ์ฝ ์ด๋ฆ๊ณผ ์ธ๋ฑ์ค ์ด๋ฆ์ ์๋ก ๋ค๋ฅด๊ฒ ํ ํ์๊ฐ ์์!!
alter table emp add constraint emp_empno_pk primary key(empno); alter table emp drop constraint emp_empno_pk; alter table emp add constraint emp_empno_pk primary key(empno) using index (create unique index emp_idx1 on emp(empno));
๋ฌธ์
dept ํ
์ด๋ธ์ deptno์ primary key์ ์ ์ฝ์ ๊ฑฐ๋๋ฐ, ์๋์ผ๋ก ์์ฑ๋๋ ์ธ๋ฑ์ค ์ด๋ฆ์ dept_idx1 ๋ผ๊ณ ํด์ ์์ฑ๋๊ฒ ๋ง๋ค๊ธฐ
alter table dept add constraint dept_deptno_pk primary key(deptno) using index (create unique index dept_idx1 on dept(deptno));
-- ์ ์ฝ์ด๋ฆ ํ์ธ select table_name, constraint_name from user_constraints where table_name='DEPT'; # DEPT DEPT_DEPTNO_PK -- ์ธ๋ฑ์ค ์ด๋ฆ ํ์ธ select index_name, uniqueness from user_indexes where table_name='DEPT'; # DEPT_IDX1 UNIQUE
1. view ์ฌ์ฉ์ ์ฅ์ ์ SQL ์ฝ๋๊ฐ ์ฌํํด์ง๋ค.
-- salesman์ธ ์ฌ์๋ค๋ง ๋ชจ์ view๋ฅผ ์์ฑ create view emp_salesman as select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where job='SALESMAN'; -- ๋ทฐ๋ฅผ ์ฌ์ฉ ํ์ ๋ select * from emp_salesman where sal >= 1200; -- ๋ทฐ๋ฅผ ์ฌ์ฉ ์ํ์ ๋ select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where job = 'SALESMAN' and sal >= 1200;
๐จ VEIW๋ฅผ ์ฌ์ฉํ์ ๋์ ์ฃผ์์ฌํญ!
: ์๋ชป๋ ์คํ๊ณํ์ ์ ๋ฐํ ์ ์๋ค.โ ์๋์ ์กฐ์ธ๋ฌธ์ฅ์ ์์ฑํ๊ณ ์คํ๊ณํ์ ํ์ธํด๋ณด๊ธฐ
select /*+ gather_plan_statistics leading(d e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.loc from emp_salesman e, dept d where e.deptno = d.deptno and e.sal >= 1500; SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 6 | | | | |* 1 | HASH JOIN | | 1 | 2 | 2 |00:00:00.01 | 6 | 909K| 909K| 712K (0)| | 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 3 | | | | |* 3 | TABLE ACCESS FULL| EMP | 1 | 2 | 2 |00:00:00.01 | 3 | | | | ----------------------------------------------------------------------------------------------------------------
โก๏ธ
leading(d e)
๋ผ๊ณ ํํธ๋ฅผ ์ฃผ๋ฉด dept ๋จผ์ ๋๋ผ์ด๋น ํ๊ณ view๋ฅผ ๋๋ผ์ด๋น ํด๋ผ ! ๋ผ๋ ๊ฒ์ด๋ค. ๊ทธ๋ฐ๋ฐ ์ง๊ธ ์คํ๊ณํ์๋ emp_salesman ์์ฒด๊ฐ ์๊ณ emp๋ง ๋ณด์ธ๋ค. ์ ์๋ณด์ด๋๋ฉด ์ตํฐ๋ง์ด์ ๊ฐ view๋ฅผ ํด์ฒดํ๊ณ ์๋์ฒ๋ผ ์คํํ๋ค.select /*+ gather_plan_statistics leading(d e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.loc from emp e, dept d where e.deptno = d.deptno and job = 'SALESMAN' and e.sal >= 1500;
select /*+ gather_plan_statistics no_merge(e) leading(d e) */
e.empno, e.ename, e.job, e.mgr, e.sal, d.loc
from emp_salesman e, dept d
where e.deptno = d.deptno and e.sal >= 1500;
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 6 | | | |
|* 1 | HASH JOIN | | 1 | 2 | 2 |00:00:00.01 | 6 | 909K| 909K| 721K (0)|
| 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 4 |00:00:00.01 | 3 | | | |
| 3 | VIEW | EMP_SALESMAN | 1 | 2 | 2 |00:00:00.01 | 3 | | | |
|* 4 | TABLE ACCESS FULL| EMP | 1 | 2 | 2 |00:00:00.01 | 3 | | | |
-------------------------------------------------------------------------------------------------------------------------
โก๏ธ no_merge
ํํธ๋ ๋ทฐ๋ฅผ ํด์ฒดํ์ง ๋ง์๋ผ ๋ผ๋ ํํธ์ด๋ค!
๐ก ํ์์์ DBA๋ DB์ ์ฑ๋ฅ์ด ๋๋ฆฐ์ง ํ์ธ์ ํด์ผํ๋ค. ํ์ธํ ๋ ์ฃผ๋ก Active session (๋๊ธฐํ๋ ์ธ์
)๋ค์ด ๋ฌด์จ ์ด์ ๋ก ๋๊ธฐํ๋์ง ๊ทธ ๋๊ธฐ ์ด๋ฒคํธ๋ฅผ ํ์ธํด์ผํ๋ค.
๊ทธ ๋๊ธฐ ์ด๋ฒคํธ ์ค์์ ์๋์ ๋๊ธฐ ์ด๋ฒคํธ๊ฐ ์ถํํ๋ฉด shared pool์ SQL์ด ๊ณต์ ๋์ง ๋ชปํ๊ณ ์๋ค๋ ์ฆ๊ฑฐ์
๋๋ค.
latch
: shared pool
latch
: libary cacheโก๏ธ ํด๊ฒฐ ๋ฐฉ๋ฒ : SQL์ PL/SQL๋ก ์ฝ๋ฉํ๋ ๊ฒ์ ๊ถ๊ณ ํ๊ฑฐ๋ PL/SQL ๋ด์ ๋ฆฌํฐ๋ด SQL ์ฝ๋ฉ์ด ์๋ค๋ฉด ๋ฐ์ธ๋ ๋ณ์๋ก ๋ณ๊ฒฝํ๊ธฐ๋ฅผ ๊ฐ๋ ฅํ๊ฒ ๊ถ๊ณ ํด์ผํ๋ค.
์ค์ต
owi.zip ์ ๋ชจ๋ฐํ
์ผ๋ก /home/oracle๋ฐ์ ์ฌ๋ฆฌ๊ณ unzip์ผ๋ก ์์ถ ํ๊ธฐ
โ๏ธ ์์ถ ํ๊ธฐ
$ unzip owi.zip
SYS> @install
Elapsed: 00:00:00.00
Input data file name(for default tablespace ex. c:\owi\owi01.dbf): /home/oracle/owi01.dbf
Package body created.
Elapsed: 00:00:00.01
11:55:53 PROD > show user
USER is "OWI"
โ๏ธ ์กํฐ๋ธ ์ธ์ ๋ณด๋ ์คํฌ๋ฆฝํธ
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
โ๏ธ ์คํฌ๋ฆฝํธ ์ค๋น๊ฐ ๋๋ฉด ๋๋ ค๋ณด๊ธฐ shared_pool_latch
OWI> @exec
11:58:27 PROD > accept event_name char prompt 'Event name to simulate: '
Event name to simulate: shared_pool_latch
11:59:03 PROD > accept session_num number default 10 prompt 'Session count [10]: '
Session count [10]: 10
11:59:06 PROD > accept expired_type number default 1 prompt 'Expired by time(1) or looping count(2) [1]: '
Expired by time(1) or looping count(2) [1]: 1
11:59:08 PROD > accept interval number default 30 prompt 'Execution internval(sec or count) [30]: '
Execution internval(sec or count) [30]: 60
11:59:13 PROD > accept enable_trace number default 0 prompt 'Enable_trace (1=TRUE, 0=FALSE) [0]: '
Enable_trace (1=TRUE, 0=FALSE) [0]: 1
11:59:18 PROD > ACCEPT exec_method NUMBER DEFAULT 0 prompt 'Exec method(0=Oracle Job, 1=Unix Shell) [0]: '
Exec method(0=Oracle Job, 1=Unix Shell) [0]: 0
11:59:23 PROD > accept init_on_creation number default 1 prompt 'Init data(1=TRUE, 0=FALSE) [1]: '
Init data(1=TRUE, 0=FALSE) [1]: 1
๋ฌธ์
ADDM ๋ ํฌํธ๋ฅผ ๋ ์ ํ์ฌ db๊ฐ ๋๋ฆฐ ์ด์ ์ ํด๊ฒฐ์ฑ
์ ์ดํด๋ณด๊ธฐ
OWI> exec dbms_workload_repository.create_snapshot; OWI> @?/rdbms/admin/addmrpt.sql
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
------------------------------- ------------------- ---------------
1 PL/SQL Execution .27 | 42.86 1
2 Soft Parse .24 | 38.27 2
3 Hard Parse Due to Literal Usage .11 | 17.15 1
4 Latch Free Waits .03 | 4.79 0
5 CPU Usage .02 | 3.49 1
Finding 3: Hard Parse Due to Literal Usage
Impact is .11 active sessions, 17.15% of total activity.
--------------------------------------------------------
SQL statements were not shared due to the usage of literals. This resulted in
additional hard parses which were consuming significant database time.
Recommendation 1: Application Analysis
Estimated benefit is .11 active sessions, 17.15% of total activity.
-------------------------------------------------------------------
Action
Investigate application logic for possible use of bind variables instead
of literals. โ
๋ฆฌํฐ๋ด ๋ณ์ ๋์ ๋ฐ์ธ๋ ๋ณ์๋ฅผ ์จ๋ผ.
Action
Alternatively, you may set the parameter "cursor_sharing" to "force".
โ
cursor_sharing๋ฅผ force๋ก ๋ฐ๊ฟ๋ผ
Rationale
At least 27 SQL statements with FORCE_MATCHING_SIGNATURE
17109837705936771014 and PLAN_HASH_VALUE 3909286317 were found to be
using literals. Look in V$SQL for examples of such SQL statements.
Symptoms That Led to the Finding:
---------------------------------
Hard parsing of SQL statements was consuming significant database time.
Impact is .11 active sessions, 17.16% of total activity.
Contention for latches related to the shared pool was consuming
significant database time.
Impact is .09 active sessions, 14.36% of total activity.
Wait class "Concurrency" was consuming significant database time.
Impact is .09 active sessions, 14.36% of total activity.
์ ์ฌ์๊ฐ ๋ฌธ์
cursor_sharing๋ฅผ force๋ก ๋ฐ๊พธ๊ธฐ
12:10:09 PROD > alter system set cursor_sharing=force 12:10:37 PROD > show parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string FORCE
๐ก Shared pool์์ ๊ณต์ ๊ฐ ๋๋ ๋๊ฐ์ SQL์ด๋ ๋ฌด์์ธ๊ฐ?
- ๋์๋ฌธ์ ๊ตฌ๋ถ
select ename, sal from emp where empno = 7788; SELECT ENAME, SAL FROM EMP WHRER EMPNO = 7788;
- ๊ณต๋ฐฑ๊ณผ ๋ค์ฌ์ฐ๊ธฐ๋ ๊ตฌ๋ถ
select ename, sal from emp where empno = 7788; select ename, sal from emp where empno = 7788;
- ๋ฆฌํฐ๋ด SQL์ ๊ตฌ๋ถ
: ๊ฐ๊ธ์ SQL์ PL/SQL๋ก ์ฝ๋ฉ์ํ๊ณ ๋ฆฌํฐ๋ด SQL์ ๋ฐ์ธ๋ ๋ณ์๋ก ๋ณ๊ฒฝํด์ ์ฝ๋ฉ์ ํ๊ฒ๋ ํด์ผlatch : shared pool
์ด ๋ฐ์ํ์ง ์๋๋ค.select ename, sal from emp where empno = 7788; select ename, sal from emp where empno = 7902;
- sql๋ฌธ์ฅ
- ์คํ๊ณํ
- ํ์คํธ๋ฆฌ
์ด๋ ๊ฒ 3๊ฐ๊ฐ ํ์ฑ ๊ฒฐ๊ณผ๋ฌผ์ธ๋ฐ ์ด๊ฒ์ shared pool์ ์ฌ๋ฆฐ๋ค.
โ
์ํํธ ํ์ฑ๊ณผ ํ๋ ํ์ฑ์ ์ฐจ์ด?
์ํํธ ํ์ฑ์ ์ํํ๋ SQL๊ณผ ๋๊ฐ์ SQL์ ๊ณต์ ํ์์ ์ฐพ์์ ํ์ฑ๊ณผ์ ์ ์๋ตํ๋ ๊ฒ์ด๊ณ ํ๋ํ์ฑ์ ์ฐพ์ง ๋ชปํด์ ๋ค์ ํ์ฑํ๋ ๊ฒ์ ๋งํ๋ค.
๐ก ์ค๋ผํด ์ตํฐ๋ง์ด์ ๊ฐ ์ด๋ค ์ญํ ?
๐ก ์ค๋ผํด ์ตํฐ๋ง์ด์ ์ ๊ฐ ์คํ๊ณํ์ ๋ง๋๋ ๋จ๊ณ
โญ ์ค๋ผํด ์ธ์คํด์ค์ ๋ฉ๋ชจ๋ฆฌ๋ SGA, PGA๋ก ๊ตฌ์ฑ๋์ด ์๋ค.
select empno, ename
from emp
where empn=7788;
1. parsing :
๊ตฌ๋ฌธ ๋ถ์ : SQL ๋ฌธ๋ฒ์ด ์ค๋ฅ๊ฐ ์๋์ง ๊ฒ์ฌ ์๋ฏธ ํ์ธ : empํ ์ด๋ธ์ db๊ฐ ์กด์ฌํ๋ํ ์ด๋ธ์ ๊ถํ ํ์ธ : empํ ์ด๋ธ์ select ํ ์ ์๋ ๊ถํ์ด ์๋์ง ํ์ธ โ ์ด SQL์ ์ํด PGA์์ญ ๊ณต๊ฐ ํ๋ณด๋ฅผ ํ๋ค.
โก๏ธ ๋๊ฐ์ SQL์ด ๊ณต์ ํ์ ์์ผ๋ฉด ๋ฐ๋ก ์คํ(์ํํธํ์ฑ)ํ๊ณ ์์ผ๋ฉด ํ์ฑ์ ํด์ ๊ณต์ ํ์ ์ฌ๋ฆฐ๋ค. (ํ๋ํ์ฑ) ์ด๋ latch๋ฅผ ์ก๋๋ฐ, latch๋ shared pool latch์ด๋ค. ์ก์์ผ ํ์ฑํ ๊ฒฐ๊ณผ๋ฌผ 3๊ฐ์ง๋ฅผ ์ฌ๋ฆด ์ ์๋ค.
2. execute :๋ฐ์ดํฐ๋ฅผ buffer cache์์ ์ฐพ๊ณ ์์ผ๋ฉด datafile์์ ์ฐพ์์ ๋ณต์ฌ๋ณธ์ ๋ฒํผ์บ์์ ์ฌ๋ฆฐ๋ค.
3. fetch :
์ฐพ์ data๋ฅผ ์๋ฒํ๋ก์ธ์๊ฐ ์ ์ ํ๋ก์ธ์์๊ฒ ์ ๋ฌํ๋ ๊ฒ์ด๋ค.
๐ก ๊ณํ์ ๋์ถํ๋ ๊ฒ์ด ์ตํฐ๋ง์ด์ ์ด๋ค. ์ธ๋ฑ์ค ์ค์บ๊ณผ ํํ
์ด๋ธ ์ค์บ ์ค์ ๋ ๋น ๋ฅธ๊ฒ์ ๊ฒฐ์ ํ๋๊ฒ์ด ์ตํฐ๋ง์ด์ ์ ์ญํ ์ด๋ค!
select * from emp where job = 'MANAGER';
โก๏ธ job
์ ์ธ๋ฑ์ค๊ฐ ์๋ค๋ฉด full table scan
,index range scan
๋ฅผ ํ ์ ์๋๋ฐ 2๊ฐ์ ์คํ๊ณํ์ค์ ์ด๋ค๊ฒ์ ์ ํํ๋๋ฉด, ๊ฐ์ฅ ๋น์ฉ์ด ์ ๊ฒ ๋๋ ๊ฒ์ ์ ํํ๋ค. ์๋ฅผ๋ค์ด job์ด manager์ธ ์ฌ์์ด ์ ์ฒด ์ฌ์์์ 1๋ช
์ด๋ค ๋ผ๊ณ ํ๋ค๋ฉด full table scan
๋ณด๋ค index range scan
์ด ๋น์ฉ์ด ์ ๊ฒ ๋ค๊ณ , job์ด manager์ธ ์ฌ์์ด 14๋ช
์ค์ 13๋ช
์ด๋ค ๋ผ๊ณ ํ๋ฉด ์ธ๋ฑ์ค ์ค์บ ๋ณด๋ค ํํ
์ด๋ธ ์ค์บ์ด ๋น์ฉ์ด ๋ ์ ๊ฒ ๋ ๋ค!
job
์ด manager์ธ ์ฌ์์ด ๋ช๋ช
์๋์ง ์์์ผํ๋๋ฐ ๊ทธ๊ฒ์ ์์๋ด๊ธฐ ์ํด ์ตํฐ๋ง์ด์ ๊ฐ ํ
์ด๋ธ ํต๊ณ์ ๋ณด๋ฅผ ๋ด
๋๋ค. ๊ทธ๋์ DBA๊ฐ ํ
์ด๋ธ ํต๊ณ์ ๋ณด๋ฅผ ์์งํด์ค์ผ ํ๋ค!!!
์ค์ต1.
@demobld
- ์ ์คํฌ๋ฆฝํธ ๋๋ฆฌ๊ณ job์ด PRESIDNT ๊ฐ ์๋ ์ฌ์๋ค์ ์ ๋ถ ์ง์ ์ ๋งค๋์ ๋ก ์ ๋ฐ์ดํธ ํ๋ค.
16:24:30 PROD > update emp 16:24:35 2 set job='MANAGER' 16:24:43 3 where job!='PRESIDENT'; commit;
- job์ ์ต๋ฑ์ค๋ฅผ ์์ฑ
create index emp_job on emp(job);
- emp ํ ์ด๋ธ์ ํ ์ด๋ธ ํต๊ณ์ ๋ณด๋ฅผ ์์งํ๋ค.
exec dbms_stats.gather_table_stats('SCOTT', 'EMP');
- ์๋์ 2๊ฐ์ SQL์ ์คํ๊ณํ์ ํ์ธํ๊ณ ์คํ๊ณํ์ด ์ฑ ์ฒ๋ผ ์ ๋ง ๊ฐ๊ฐ ๋๋ฅด๊ฒ ๋์ค๋์ง ํ์ธ
explian plan for select * from emp where job='PRESIDENT'; select * from table(dbms_xplan.display); explian plan for select * from emp where job='MANAGER'; select * from table(dbms_xplan.display);
- ์ค์ ์คํ ๊ณํ
select /*+ gather_plan_statistics */ * from emp where job='MANAGER'; SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); ------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 13 |00:00:00.01 | 3 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 13 | 13 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------ select /*+ gather_plan_statistics */ * from emp where job='PRESIDENT'; SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
์ค๋์ ๋ง์ง๋ง ๋ฌธ์
emp ํ
์ด๋ธ์ ๊ฑด์๋ฅผ 10000๊ฑด ์ด์ ๊ฐ์ ๋ก ๋๋ฆฌ๊ณ , job์ด PRESIDENT๋ 1๋ช
๋ง ๋๊ณ ๋๋จธ์ง๋ ์ ๋ถ MANAGER๋ก ๋ณ๊ฒฝํ๊ธฐ. ๊ทธ๋ฆฌ๊ณ ์ ํ
์คํธ ๋ค์ ํ๊ธฐ
- emp ํ ์ด๋ธ ๊ฑด์๋ฅผ 10000๊ฑด์ด์ ๋๋ฆฌ๊ธฐ
insert into emp select * from emp; PROD > select rowid from emp where rownum < 2; ROWID ------------------ AAADM4AABAAAHcpAAA
- 1๊ฑด๋นผ๊ณ ๋ชจ๋ MANAGER๋ก ๋ณ๊ฒฝ
update emp set job='MANAGER'; update emp set job='PRESIDENT' where rowid='AAADM4AABAAAHcpAAA'; commit;
โก๏ธ ์ด๋ฐ ๊ทน๋จ์ ์ธ ๋ฐ์ดํฐ์ ๊ฒฝ์ฐ๋ ๊ทธ๋ฅ ํต๊ณ์ ๋ณด๋ฅผ ์์งํ๋ฉด ์๋๊ณ , ํ์คํ ๊ทธ๋จ ํต๊ณ์ ๋ณด๋ฅผ ์์งํด์ฃผ์ด์ผ ํ๋ค.
--์๊น ์์ ๋ ๋ฐ๊ฟ๋์ force ์๋๋๋ก ๋๋๋ฆผ alter system set cursor_sharing=exact; -- ํต๊ณ์ ๋ณด ์์ง exec dbms_stats.gather_table_stats('SCOTT','EMP', method_opt => 'for all columns size skewonly'); or analyze table emp compute statistics for columns job;
- ์ค์ ์คํ ๊ณํ
select /*+ gather_plan_statistics */ count(*) from emp where job='MANAGER'; SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); ------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1052 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1052 | |* 2 | INDEX FAST FULL SCAN| EMP_JOB | 1 | 28671 | 28671 |00:00:00.06 | 1052 | ------------------------------------------------------------------------------------------- select /*+ gather_plan_statistics */ count(*) from emp where job='PRESIDENT'; SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); --------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 47 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 47 | |* 2 | INDEX RANGE SCAN| EMP_JOB | 1 | 1 | 1 |00:00:00.01 | 47 | ---------------------------------------------------------------------------------------
๋ค์์ค์ต
- t table ์์ฑ
create table t as select decode(rownum,1,1,99) ID, all_objects.* from all_objects;
- ์ธ๋ฑ์ค ์์ฑ
create index t_idx on t (id);
- ํต๊ณ์ ๋ณด ํ์ธ
set autotrace traceonly explain select * from t where id= 1; ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 171 | 2 (0)| 00:00 :01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 171 | 2 (0)| 00:00 :01 | |* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00 :01 | -------------------------------------------------------------------------------
PROD > select * from t where id=99; -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13447 | 2245K| 47 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 13447 | 2245K| 47 (0)| 00:00:01 | --------------------------------------------------------------------------