"오라클 코어 - 조나단 루이스"의 두 판 사이의 차이
DB CAFE
(→관련 스크립트) |
(→Oracle Core Essential Internals for DBAs and Developers (Apress)) |
||
3번째 줄: | 3번째 줄: | ||
=== 관련 스크립트 === | === 관련 스크립트 === | ||
* 소스다운로드 https://github.com/apress/oracle-core-esntl-internals-for-dbas-devs | * 소스다운로드 https://github.com/apress/oracle-core-esntl-internals-for-dbas-devs | ||
+ | |||
+ | ==== setenv.sql ==== | ||
+ | * Set up various SQL*Plus formatting commands. | ||
+ | <source lang=sql> | ||
+ | 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 log | ||
+ | |||
+ | |||
+ | </source> | ||
+ | |||
+ | ==== c_dump_seg.sql ==== | ||
+ | * Create a procedure to dump blocks from a segment | ||
+ | <source lang=sql> | ||
+ | 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; | ||
+ | |||
+ | </source> | ||
==== core_demo_02.sql (19c 버전에서 sql에러 발생으로 sql 수정함.) ==== | ==== core_demo_02.sql (19c 버전에서 sql에러 발생으로 sql 수정함.) ==== |
2024년 5월 7일 (화) 23:54 판
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 log
1.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 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
;