SCOTT/TIGER 스키마 설정
- 많은 예제는 SCOTT 스키마 테이블 사용
- 스키마 생성 방법1
@$ORACLE_HOME/sqlplus/demo/demobld.sql
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON ');
alter table emp add constraint emp_pk primary key (empno);
alter table dept add constraint dept_pk primary key (deptno);
alter table emp add constraint emp_fk_dept foreign key (deptno) references dept;
alter table emp add constraint emp_fk_emp foreign key (mgr) references emp;
@$ORACLE_HOME/sqlplus/demo/demodrop.sql
환경 설정
- 대부분 예제는 SQL*Plus 환경에서 수행 됨
- login.sql
define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on
SQL*Plus 에서 Autotrace 설정
-- connect system
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
GRANT ALL ON PLAN_TABLE TO PUBLIC;
-- connect sys
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
GRANT PLUSTRACE TO PUBLIC;
| 설정 | 리포트 |
|---|
| SET AUTOTRACE OFF | 생성 안됨 (기본값) |
| SET AUTOTRACE ON EXPLAIN | 옵티마이저 실행 경로만 보여줌 |
| SET AUTOTRACE ON STATISTICS | SQL 문 실행 통계정보만 보여줌 |
| SET AUTOTRACE ON | 옵티마이저 실행 경로 + SQL 문 실행 통계정보 보여줌 |
| SET AUTOTRACE TRACEONLY | SET AUTOTRACE ON 과 같으나 사용자 쿼리 결과값을 안보여줌 |
SQL> set autotrace off
SQL> select * from dual;
D
-
X
SQL> set autotrace on explain
SQL> select * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> set autotrace on statistics
SQL> select * from dual;
D
-
X
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
522 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace on
SQL> select * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
522 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace traceonly;
SQL> select * from dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
522 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Statspack 설정
-- connect sys
-- 설치
@$ORACLE_HOME/rdbms/admin/spcreate.sql
-- 제거
@$ORACLE_HOME/rdbms/admin/spdrop.sql
사용자 정의 스크립트
- Runstats
- 같은 작업을 하는 두 개의 다른 방법을 비교
- Wall clock or elapsed time
- System statistics (v$statname, v$mystat)
- Latching (v$latch)
- 설치
drop table run_stats;
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;
grant select any table to ops$tkyte;
create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch
union all
select 'STAT...Elapsed Time', hsecs from v$timer;
delete from run_stats;
commit;
create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( p_difference_threshold in number default 0 );
end;
/
create or replace package body runstats_pkg
as
g_start number;
g_run1 number;
g_run2 number;
procedure rs_start
is
begin
delete from run_stats;
insert into run_stats
select 'before', stats.* from stats;
g_start := dbms_utility.get_cpu_time;
end;
procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_cpu_time-g_start);
insert into run_stats
select 'after 1', stats.* from stats;
g_start := dbms_utility.get_cpu_time;
end;
procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_cpu_time-g_start);
dbms_output.put_line
( 'Run1 ran in ' || g_run1 || ' cpu hsecs' );
dbms_output.put_line
( 'Run2 ran in ' || g_run2 || ' cpu hsecs' );
if ( g_run2 <> 0 )
then
dbms_output.put_line
( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
'% of the time' );
end if;
dbms_output.put_line( chr(9) );
insert into run_stats
select 'after 2', stats.* from stats;
dbms_output.put_line
( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );
for x in
( select rpad( a.name, 30 ) ||
to_char( b.value-a.value, '999,999,999' ) ||
to_char( c.value-b.value, '999,999,999' ) ||
to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
-- and (c.value-a.value) > 0
and abs( (c.value-b.value) - (b.value-a.value) )
> p_difference_threshold
order by abs( (c.value-b.value)-(b.value-a.value))
) loop
dbms_output.put_line( x.data );
end loop;
dbms_output.put_line( chr(9) );
dbms_output.put_line
( 'Run1 latches total versus runs -- difference and pct' );
dbms_output.put_line
( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||
lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );
for x in
( select to_char( run1, '999,999,999' ) ||
to_char( run2, '999,999,999' ) ||
to_char( diff, '999,999,999' ) ||
to_char( round( run1/decode( run2, 0, to_number(0), run2) *100,2 ), '99,999.99' ) || '%' data
from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
sum( (c.value-b.value)-(b.value-a.value)) diff
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
and a.name like 'LATCH%'
)
) loop
dbms_output.put_line( x.data );
end loop;
end;
end;
/
exec runStats_pkg.rs_start; -- Runstats 테스트 시작
exec runStats_pkg.rs_middle; -- Runstats 중간 부분
exec runStats_pkg.rs_stop; -- Runstats 종료 부분 (리포트 출력)
create table t1
as select * from big_table where 1 = 0;
create table t2
as select * from big_table where 1 = 0;
exec runstats_pkg.rs_start;
insert into t1
select * from big_table
where rownum <= 1000000;
commit;
exec runstats_pkg.rs_middle;
begin
for x in ( select * from big_table where rownum <= 1000000 )
loop
insert into t2 values X;
end loop;
commit;
end;
/
exec runstats_pkg.rs_stop(1000000);
-- 결과
Run1 ran in 120 cpu hsecs
Run2 ran in 2622 cpu hsecs
run 1 ran in 4.58% of the time
Name Run1 Run2 Diff
STAT...execute count 110 1,000,147 1,000,037
STAT...opened cursors cumulati 111 1,000,154 1,000,043
LATCH.shared pool 417 1,002,657 1,002,240
STAT...recursive calls 962 1,011,359 1,010,397
STAT...db block changes 109,047 2,084,843 1,975,796
STAT...Effective IO time 0 2,420,814 2,420,814
LATCH.cache buffers chains 581,941 5,509,280 4,927,339
STAT...file io wait time 5,130,525 3,786 -5,126,739
STAT...undo change vector size 3,842,244 67,926,428 64,084,184
STAT...redo size 119,240,628 381,261,612 262,020,984
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
816,211 6,883,628 6,067,417 11.86%
PL/SQL procedure successfully completed.
set echo off
set verify off
column value new_val V
define S="&1"
column name format a45
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
-- and lower(a.name) = lower('&S')
/
set echo on
set echo off
set verify off
column diff format a18
select a.name, b.value Value, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on
SQL> @mystat "redo size"
SQL> set echo off
NAME VALUE
--------------------------------------------- ----------
redo size 609969716
SQL> update big_table set owner = lower(owner) where rownum <= 1000;
1000 rows updated.
SQL> @mystat2
SQL> set echo off
NAME VALUE DIFF
--------------------------------------------- ---------- ------------------
redo size 610066552 96,836
-- 참고
SQL> @mystat "redo size"
SQL> set echo off
NAME VALUE
--------------------------------------------- ----------
redo size 610066552
redo size for lost write detection 0
redo size for direct writes 32480
- Show_Space
- 데이터베이스 세그먼트의 공간 활용 정보 출력
- show_space SPEC
desc show_space
-- P_SEGNAME : 테이블/인덱스 세그먼트 이름
-- P_OWNER : 스키마 (기본값 : 현재유저)
-- P_TYPE : 객체 유형 (기본값 : TABLE)
-- P_PARTITION : 파티션
SQL> exec show_space('BIG_TABLE');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 14,616
Total Blocks............................ 15,360
Total Bytes............................. 125,829,120
Total MBytes............................ 120
Unused Blocks........................... 581
Unused Bytes............................ 4,759,552
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 2,413,440
Last Used Block......................... 443
PL/SQL procedure successfully completed.
| 항목 | 의미 |
|---|
| Unformatted Blocks | 하이 워터 마크 아래에 할당된 블록 수, 사용된 적이 없는 블록 (Unformatted + Unused = ASSM 내에서 사용된 적이 없는 블록) |
| FS1 ~ FS4 Blocks | 데이터를 가진 포멧된 블록 |
| Full Blocks | 가득 찬 블록 |
| Total Blocks, Total Bytes, Total Mbytes | 세그먼트의 총 공간 정보 |
| Unused Blocks, Unused Bytes | 사용된 적이 없는 공간 정보 |
| Last Used Ext Field | 데이터가 있는 마지막 익스텐트 파일 ID |
| Last Used Ext BlockId | 마지막 익스텐트의 시작 블록 ID |
| Last Used Block | 마지막 익스텐트의 마지막 블록의 블록 ID 오프셋 |
create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4_bytes number;
l_full_blocks number; l_full_bytes number;
-- inline procedure to print out numbers nicely formatted
-- with a simple label
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
to_char(p_num,'999,999,999,999') );
end;
begin
-- this query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- this query determines if the object is a ASSM object or not
begin
execute immediate
'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.segment_type = :p_type
and seg.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner, p_type;
exception
when too_many_rows then
dbms_output.put_line
( 'This must be a partitioned table, use p_partition => ');
return;
end;
-- if the object is in an ASSM tablespace, we must use this API
-- call to get space information, else we use the FREE_BLOCKS
-- API for the user managed segments
if l_segment_space_mgmt = 'AUTO'
then
dbms_space.space_usage
( p_owner, p_segname, p_type, l_unformatted_blocks,
l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );
else
dbms_space.free_blocks(
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);
p( 'Free Blocks', l_free_blks );
end if;
-- and then the unused space API call to get the rest of the
-- information
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0
/
alter table big_table nologging;
declare
l_cnt number;
l_rows number := 1000000;
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a
where rownum <= 1000000;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/
alter table big_table add constraint
big_table_pk primary key(id);
exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);
코딩 규칙
create or replace package body my_pkg
as
g_variable varchar2(25);
procedure p( p_variable in varchar2 )
is
l_variable varchar2(25);
begin
null;
end;
end;
/
-- g_variable : 글로벌 패키지 변수
-- p_variable : 프로시저 형식 매개 변수
-- l_variable : 로컬 변수
-- 테이블의 컬럼과 PL/SQL 변수 구분을 위한 PREFIX("g_" / "p_" / "l_")
create procedure p( ENAME in varchar2 )
as
begin
for x in ( select * from emp where ename = ENAME ) loop
dbms_output.put_line( x.empno );
end loop;
end;
-- EMP.ENAME IS NOT NULL 인 모든 레코드 출력
-- 회피1 : ENAME => P.ENAME (변수 명시)
-- 회피2 : ENAME => P_ENAME (변수 이름 변경)