ORACLE深入 第一章ORACLE DBA常用语句和脚本(1)

a3769ge29v

ORACLE深入 第一章ORACLE DBA常用语句和脚本(1)
ORACLE 环境变量 DBA脚本
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.