오라클 코어 - 조나단 루이스
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 Oracle Core Essential Internals for DBAs and Developers (Apress)[편집]
1.1 관련 스크립트[편집]
1.1.1 setenv.sql[편집]
- Set up various SQL*Plus formatting commands.
rem
rem Script: setenv.sql
rem Author: Jonathan Lewis
rem Dated: June 2002
rem Purpose: Set up various SQL*Plus formatting commands.
rem
rem Notes:
rem
set pause off
rem
rem If you want to call dbms_xplan.display_cursor() to get the
rem place for the last statement you executed you will have to
rem set serveroutput off
rem
set serveroutput on size 1000000 format wrapped
rem
rem I'd like to enable java output, but it seems
rem to push the UGA up by about 4MB when I do it
rem
rem execute dbms_java.set_output(1000000)
rem
rem Reminder about DOC, and using the # to end DOC
rem the SET command stops doc material from appearing
rem
execute dbms_random.seed(0)
set doc off
doc
end doc is marked with #
#
set linesize 120
set trimspool on
set pagesize 24
set arraysize 25
-- set longchunksize 32768
-- set long 32768
set autotrace off
clear breaks
ttitle off
btitle off
column owner format a15
column segment_name format a20
column table_name format a20
column index_name format a20
column object_name format a20
column subobject_name format a20
column partition_name format a20
column subpartition_name format a20
column column_name format a20
column column_expression format a40 word wrap
column constraint_name format a20
column referenced_name format a30
column file_name format a60
column low_value format a24
column high_value format a24
column parent_id_plus_exp format 999
column id_plus_exp format 990
column plan_plus_exp format a90
column object_node_plus_exp format a14
column other_plus_exp format a90
column other_tag_plus_exp format a29
column access_predicates format a80
column filter_predicates format a80
column projection format a80
column remarks format a80
column partition_start format a12
column partition_stop format a12
column partition_id format 999
column other_tag format a32
column object_alias format a24
column object_node format a13
column other format a150
column os_username format a30
column terminal format a24
column userhost format a24
column client_id format a24
column statistic_name format a35
column namespace format a20
column attribute format a20
column hint format a40
column start_time format a25
column end_time format a25
column time_now noprint new_value m_timestamp
set feedback off
select to_char(sysdate,'hh24miss') time_now from dual;
commit;
set feedback on
set timing off
set verify off
alter session set optimizer_mode = all_rows;
spool log1.1.2 c_dump_seg.sql[편집]
- Create a procedure to dump blocks from a segment
rem
rem Script: c_dump_seg.sql
rem Author: Jonathan Lewis
rem Dated: December 2002
rem Purpose: Create a procedure to dump blocks from a segment
rem
rem Last tested
rem 11.2.0.2
rem 11.1.0.7
rem 10.2.0.3
rem 10.1.0.4
rem 9.2.0.6
rem 8.1.7.4
rem
rem Notes:
rem The code is very simple minded with no error trapping.
rem It only covers the first extent (extent zero) of a segment
rem Could be enhanced to use get_ev to save and restore the state
rem of event 10289 (the one that controls raw/cooked dumps).
rem
rem Change in 10.2: the raw block dump always appears in
rem a block dump, you cannot stop it. Event 10289 blocks
rem the appearance of the formatted dump
rem
rem Script has to be run by a DBA who has the privileges to
rem view v$process, v$session, v$mystat
rem
rem Usage
rem -- the notes assume the tablespace is not ASSM.
rem execute dump_seg('tablex'); -- dump first data block
rem execute dump_seg('tablex',5) -- dump first five data blocks
rem execute dump_seg('indexy',1,'INDEX') -- dump root block of index
rem execute dump_seg('tableX',i_start_block=>0 ) -- dump seg header block
rem
rem Various "optimizer" issues with 10g:
rem select * from dba_extents
rem where segment_name = 'T1'
rem and extent_id = 0;
rem vs.
rem select * from dba_extents
rem where segment_name = 'T1'
rem order by extent_id;
rem
rem On one system, the first query crashed with error:
rem ORA-00379: no free buffers available in buffer pool DEFAULT for block size 2K
rem
rem There had been an object in the 2K tablespace,
rem which had been dropped but not purged. There
rem were no buffers allocated to the 2K cache,
rem hence the failure. And it was not possible
rem to purge the recyclebin without creating the
rem cache.
rem
rem Clearly, the join order had changed because of
rem the extent_id predicate - and this led to the
rem crash
rem
rem For this reason, I changed the code to query by
rem segment and order by extent_id - stopping at the
rem zero extent
rem
rem Performance can also be affected by how many extents
rem you have, and whether you have collected statistics
rem (in 10g) on the fixed tables - because of the call to
rem check the extents in the segment headers.
rem
rem Internal enhancements in 11g
rem You get a dump of all the copies in the buffer cache,
rem and a copy of the version of the block on disc.
rem
start setenv
create or replace procedure dump_seg(
i_seg_name in varchar2,
i_block_count in number default 1,
i_seg_type in varchar2 default 'TABLE',
i_start_block in number default 1,
i_owner in varchar2 default sys_context('userenv','session_user'),
i_partition_name in varchar2 default null,
i_dump_formatted in boolean default true,
i_dump_raw in boolean default false
)
as
m_file_id number;
m_block_min number;
m_block_max number;
m_process varchar2(32);
begin
for r in (
select
file_id,
block_id + i_start_block block_min,
block_id + i_start_block + i_block_count - 1 block_max
from
dba_extents
where
segment_name = upper(i_seg_name)
and segment_type = upper(i_seg_type)
and owner = upper(i_owner)
and nvl(partition_name,'N/A') = upper(nvl(i_partition_name,'N/A'))
order by
extent_id
) loop
m_file_id := r.file_id;
m_block_min := r.block_min;
m_block_max := r.block_max;
exit;
end loop;
if (i_dump_formatted) then
execute immediate
'alter session set events ''10289 trace name context off''';
execute immediate
'alter system dump datafile ' || m_file_id ||
' block min ' || m_block_min ||
' block max ' || m_block_max
;
end if;
if (i_dump_raw) then
execute immediate
'alter session set events ''10289 trace name context forever''';
execute immediate
'alter system dump datafile ' || m_file_id ||
' block min ' || m_block_min ||
' block max ' || m_block_max
;
end if;
execute immediate
'alter session set events ''10289 trace name context off''';
--
-- For non-MTS, work out the trace file name
--
select
spid
into
m_process
from
v$session se,
v$process pr
where
se.sid = (select sid from v$mystat where rownum = 1)
and pr.addr = se.paddr
;
dbms_output.new_line;
dbms_output.put_line(
'Dumped ' || i_block_count || ' blocks from ' ||
i_seg_type || ' ' || i_seg_name ||
' starting from block ' || i_start_block
);
dbms_output.new_line;
dbms_output.put_line('Trace file name includes: ' || m_process);
dbms_output.new_line;
exception
when others then
dbms_output.new_line;
dbms_output.put_line('Unspecified error.');
dbms_output.put_line('Check syntax.');
dbms_output.put_line('dumpseg({segment_name},[{block count}],[{segment_type}]');
dbms_output.put_line(' [{start block (1)}],[{owner}],[{partition name}]');
dbms_output.put_line(' [{dump formatted YES/n}],[{dump raw y/NO}]');
dbms_output.new_line;
raise;
end;
.
/
show errors
drop public synonym dump_seg;
create public synonym dump_seg for dump_seg;
grant execute on dump_seg to public;1.1.3 c_dump_log.sql[편집]
- Dump the current online redo log file.
rem
rem Script: c_dump_log.sql
rem Author: Jonathan Lewis
rem Dated: December 2002
rem Purpose: Dump the current online redo log file.
rem
rem
rem Last tested
rem 11.2.0.2
rem 10.2.0.3
rem 10.1.0.4
rem 9.2.0.8
rem 8.1.7.4
rem
rem Notes:
rem Must be run as a DBA
rem Very simple minded - no error trapping
rem
start setenv
create or replace procedure dump_log
as
m_log_name varchar2(255);
m_process varchar2(32);
begin
select
lf.member
into
m_log_name
from
V$log lo,
v$logfile lf
where
lo.status = 'CURRENT'
and lf.group# = lo.group#
and rownum = 1
;
execute immediate
'alter system dump logfile ''' || m_log_name || '''';
select
spid
into
m_process
from
v$session se,
v$process pr
where
se.sid = --dbms_support.mysid
(select sid from v$mystat where rownum = 1)
and pr.addr = se.paddr
;
dbms_output.put_line('Trace file name includes: ' || m_process);
end;
.
/
show errors
create public synonym dump_log for dump_log;
grant execute on dump_log to public;
spool off
set doc off
doc
----------------------------------------------
Skipping IMU Redo Record: cannot be filtered by XID/OBJNO
-------------------------------------------------
----------------------------------------------
Skipping IMU Redo Record: cannot be filtered by XID/OBJNO
-------------------------------------------------
----------------------------------------------
Skipping IMU Redo Record: cannot be filtered by XID/OBJNO
-------------------------------------------------
----------------------------------------------
Skipping IMU Redo Record: cannot be filtered by XID/OBJNO
-------------------------------------------------
#1.1.4 c_dump_undo_block.sql[편집]
rem
rem Script: c_dump_undo_block.sql
rem Author: Jonathan Lewis
rem Dated: December 2002
rem Purpose: Create a procedure to dump your CURRENT undo block
rem
rem Last tested
rem 11.2.0.2
rem 11.1.0.7
rem 10.2.0.3
rem 9.2.0.8
rem Not tested
rem 10.1.0.4
rem 8.1.7.4
rem
rem Notes:
rem The code is very simple minded with no error trapping.
rem Has to be run by SYS to create the procedure.
rem
start setenv
create or replace procedure dump_undo_block
as
m_xidusn number;
m_header_file_id number;
m_header_block_id number;
m_start_file_id number;
m_start_block_id number;
m_file_id number;
m_block_id number;
m_process number;
begin
select
xidusn,
start_ubafil,
start_ubablk,
ubafil,
ubablk
into
m_xidusn,
m_start_file_id,
m_start_block_id,
m_file_id,
m_block_id
from
v$session ses,
v$transaction trx
where
ses.sid = (select mys.sid from V$mystat mys where rownum = 1)
and trx.ses_addr = ses.saddr
;
select
file_id, block_id
into
m_header_file_id,
m_header_block_id
from
dba_rollback_segs
where
segment_id = m_xidusn
;
dbms_output.put_line('Header File: ' || m_header_file_id || ' Header block: ' || m_header_block_id);
dbms_output.put_line('Start File: ' || m_start_file_id || ' Start block: ' || m_start_block_id);
dbms_output.put_line('Current File: ' || m_file_id || ' Current block: ' || m_block_id);
dbms_system.ksdwrt(1,'===================');
dbms_system.ksdwrt(1,'Undo Segment Header');
dbms_system.ksdwrt(1,'===================');
execute immediate
'alter system dump datafile ' || m_header_file_id ||' block ' || m_header_block_id;
dbms_system.ksdwrt(1,'================');
dbms_system.ksdwrt(1,'Undo Start block');
dbms_system.ksdwrt(1,'================');
execute immediate
'alter system dump datafile ' || m_start_file_id ||' block ' || m_start_block_id;
if m_start_block_id != m_block_id then
dbms_system.ksdwrt(1,'==================');
dbms_system.ksdwrt(1,'Current Undo block');
dbms_system.ksdwrt(1,'==================');
execute immediate
'alter system dump datafile ' || m_file_id ||' block ' || m_block_id;
end if;
select
spid
into
m_process
from
v$session se,
v$process pr
where se.sid = (select sid from v$mystat where rownum = 1)
and
pr.addr = se.paddr
;
dbms_output.put_line('Trace file name includes: ' || m_process);
end;
/
grant execute on dump_undo_block to public;
drop public synonym dump_undo_block;
create public synonym dump_undo_block for dump_undo_block;
set doc off
doc
#1.1.5 core_demo_02.sql (19c 버전에서 sql에러 발생으로 sql 수정함.)[편집]
rem
rem Script: core_demo_02.sql
rem Author: Jonathan Lewis
rem Dated: Feb 2011
rem Purpose:
rem
rem Last tested
rem 11.2.0.2
rem 10.2.0.3
rem Not relevant
rem 9.2.0.8
rem 8.1.7.4
rem
rem Notes:
rem Can only be run as SYS in this form
rem
rem Diagnostics for core undo/redo concepts
rem
column indx format 9999
-- Size of in-memory undo and private redo after a small change
-- 19c 버전에서 에러 발생
-- select
-- indx,
-- to_number(ktifpupe,'XXXXXXXX') -
-- to_number(ktifpupb,'XXXXXXXX') undo_size,
-- to_number(ktifpupc,'XXXXXXXX') -
-- to_number(ktifpupb,'XXXXXXXX') undo_usage,
-- to_number(ktifprpe,'XXXXXXXX') -
-- to_number(ktifprpb,'XXXXXXXX') redo_size,
-- to_number(ktifprpc,'XXXXXXXX') -
-- to_number(ktifprpb,'XXXXXXXX') redo_usage
-- from
-- x$ktifp
-- ;
-- 19c 버전에서 실행
select indx,
-- KTIFPTXFLG,ADDR,KTIFPNO, KTIFPSTA, KTIFPXCB xctaddr,
to_number(KTIFPUPE, 'XXXXXXXXXXXXXXXX') -
to_number(KTIFPUPB, 'XXXXXXXXXXXXXXXX') undo_size,
(to_number(KTIFPUPB, 'XXXXXXXXXXXXXXXX') - to_number(KTIFPUPC, 'XXXXXXXXXXXXXXXX'))*-1 undo_usage,
to_number(KTIFPRPE, 'XXXXXXXXXXXXXXXX') - to_number(KTIFPRPB, 'XXXXXXXXXXXXXXXX') redo_size,
(to_number(KTIFPRPB, 'XXXXXXXXXXXXXXXX') -to_number(KTIFPRPC, 'XXXXXXXXXXXXXXXX'))*-1 redo_usage
-- , KTIFPPSI,KTIFPRBS,KTIFPTCN
from x$ktifp
;
-- Sizes of private redo
select
indx,
PNEXT_BUF_KCRFA_CLN,
PTR_KCRF_PVT_STRAND,
FIRST_BUF_KCRFA,
LAST_BUF_KCRFA,
-- LASTCHANGE_KCRFA, not available in 11.2
STRAND_SIZE_KCRFA strand_size,
SPACE_KCRF_PVT_STRAND strand_space
from
x$kcrfstrand
;