ORACLE深入系列，翻譯自Thomas Kyte的 Expert Oracle Database Architecture
我的BLOG http://sunmoonking.spaces.live.com
一, 常用到的設置環境參數的語句
設置SCOTT/TIGER的DEMO
運行@Oracle_HOME/sqlplus/demo/demobld.sql (響應的demodrop.sql.是DROP SCOTT的腳本)
做一個登陸用的login.sql
define _editor=vi
set serveroutput on size 1000000 使DBMS_OUTPUT有效.
set trimspool on SPOOL不會以定長來控制,而是以空格來控制
set long 5000 LONG或CLOG 顯示的長度
set linesize 100
set pagesize 9999 每9999行後打印HEAD
column plan_plus_exp format a80 autotrace後explain plan output的格式
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
set trimspool on; 去除重定向（spool）輸出每行的拖尾空格，缺省為off
得到username@dbname的提示符. scott@WWMDB>
二, 常用到的DBA腳本
Runstats 比較兩個作相同事情的方法的優劣點。本包統計三個值，一個是時間（不重要，由於手工運行，而且偏差較大），二，使用內存方面，三，LATCH。
開發人員經常在自己的PC上裝DB,然後進行開發,這些腳本的目的就是讓開發人員可以觀察自己的SQL是如何運行的以及效率如何.
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;
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;
create or replace package runstats_pkg
as
procedure rs_start; --運行操作之前
procedure rs_middle; --運行第一個操作之後
procedure rs_stop( p_difference_threshold in number default 0 ); --結束 --p_difference_threshold 控制多大差別才予以輸出。
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_time;
end;
procedure rs_middle --記錄第一個SQL運行時間,STATISTIC,LATCH
is
begin
g_run1 := (dbms_utility.get_time-g_start);
insert into run_stats
select ’after 1’, stats.* from stats;
g_start := dbms_utility.get_time;
end;
procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_time-g_start);
dbms_output.put_line
( ’Run1 ran in ’ || g_run1 || ’ hsecs’ );
dbms_output.put_line
( ’Run2 ran in ’ || g_run2 || ’ hsecs’ );
dbms_output.put_line
( ’run 1 ran in ’ || round(g_run1/g_run2*100,2) ||
’% of the time’ );
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’, 10 ) ||
lpad( ’Run2’, 10 ) || lpad( ’Diff’, 10 ) );
for x in
( select rpad( a.name, 30 ) ||
to_char( b.value-a.value, ’9,999,999’ ) ||
to_char( c.value-b.value, ’9,999,999’ ) ||
to_char( ( (c.value-b.value)-(b.value-a.value)), ’9,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’, 10 ) || lpad( ’Run2’, 10 ) ||
lpad( ’Diff’, 10 ) || lpad( ’Pct’, 8 ) );
for x in
( select to_char( run1, ’9,999,999’ ) ||
to_char( run2, ’9,999,999’ ) ||
to_char( diff, ’9,999,999’ ) ||
to_char( round( run1/run2*100,2 ), ’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;
/
測試
SQL> create table wwm as select * from all_objects where 1=2;
表已創建。
SQL> create table wwm2 as select * from all_objects where 1=2;
表已創建。
SQL> exec runstats_pkg.rs_start;
PL/SQL 過程已成功完成。
SQL> insert into wwm select * from all_objects;
已創建34750行。
SQL> exec runstats_pkg.rs_middle;
PL/SQL 過程已成功完成。
然後用第二種方法，一行一行地插入。
SQL> begin
2 for x in (select * from all_objects)
3 loop
4 insert into wwm2 values x;
5 end loop;
6 commit;
7 end;
8 /
---------------( 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;
-----------------
SQL> exec runstats_pkg.rs_stop(1000000)
Run1 ran in 3558 hsecs
Run2 ran in 14263 hsecs
run 1 ran in 24.95% of the time
Name Run1 Run2 Diff
STAT...session pga memory 0 4,040,516 4,040,516
STAT...session pga memory max 0 4,064,632 4,064,632
STAT...session pga memory max 0 4,064,632 4,064,632
STAT...session pga memory 0 4,106,052 4,106,052
STAT...redo size 3,920,896########## 8,291,688
STAT...redo size 3,920,896########## 8,352,408
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
305,006 1,462,079 1,157,073 20.86%
三, Mystat 統計某操作之前和之後的變化。
Mystat.sql腳本捕捉初始值
set echo off
set verify off
column value new_val V
define S="&1"
輸入 1 的值: redo size --可以輸入不同的值來判斷不同的指標
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’)||’%’
/
NAME VALUE
-----------------------------
redo size 16408460
set echo on
中間做需要統計的操作。
1* update wwm set object_name=lower(object_name)
SQL> /
已更新34750行。
mystat2腳本報告異同點。
set echo off
set verify off
select a.name, b.value V, 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’)||’%’
NAME V DIFF
------------------------------------
redo size 26028120 9,619,660
可以看到這次UPDATE產生了9,619,660 bytes of redo.
