00 序 建立环境
配置测试demo
1. 运行 demosql.sql
====== start of section to cut ====== -- -- Copyright (c) Oracle Corporation 1988, 2000. All Rights Reserved. -- -- NAME -- demobld.sql -- -- DESCRIPTION -- This script creates the SQL*Plus demonstration tables in the -- current schema. It should be STARTed by each user wishing to -- access the tables. To remove the tables use the demodrop.sql -- script. -- -- USAGE -- From within SQL*Plus, enter: -- START demobld.sql SET TERMOUT ON PROMPT Building demonstration tables. Please wait. SET TERMOUT OFF DROP TABLE EMP; DROP TABLE DEPT; DROP TABLE BONUS; DROP TABLE SALGRADE; DROP TABLE DUMMY; 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'); CREATE TABLE BONUS (ENAME VARCHAR2(10), JOB VARCHAR2(9), SAL NUMBER, COMM NUMBER); CREATE TABLE SALGRADE (GRADE NUMBER, LOSAL NUMBER, HISAL NUMBER); INSERT INTO SALGRADE VALUES (1, 700, 1200); INSERT INTO SALGRADE VALUES (2, 1201, 1400); INSERT INTO SALGRADE VALUES (3, 1401, 2000); INSERT INTO SALGRADE VALUES (4, 2001, 3000); INSERT INTO SALGRADE VALUES (5, 3001, 9999); CREATE TABLE DUMMY (DUMMY NUMBER); INSERT INTO DUMMY VALUES (0); COMMIT; SET TERMOUT ON PROMPT Demonstration table build is complete. EXIT
2. 运行以下设置
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;
sqlplus 环境设置
Tom 使用的 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
--------------------- sqlplus 中的提示符 --------------------------------------------
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 (seelct global_name, instr(global_name, ‘.’) dot from global_name);
set sqlprompt ‘&gname>’
set termout on
----------------------------------------------------------------------------------
说明:
set serveroutput on size 1000000, 设置打开serveroutput, 并设置缓冲池 1000000
set trimspool on: 假脱机输出文本时, 会出去问本行两端的空格, 而且行款不定, 默认是off, 假脱机输出的文本行宽度则等于所设置的LINESIZE.
set long 5000: 设置LONG 和 CLOB 列时显示的默认字节数.
column plan_plus_exp format a80: 设置由 autotrace 得到的解释计划输出(explain plan output)的默认宽度, a80 通常足以放下整个计划.
column global_name new_value gname: 告诉 sqlplus 取得global_name列中最后一个值, 并将这个值赋给替换变量 gname
set termout off: 不打印执行结果到sqlplus上
show all 可以显示目前 sqlplus 的环境设置
设置 SQL*PLUS的 AUTOTRACE
autotrace 是sqlplus 的一个工具, 可以显示所执行查询的解释计划以及所用的资源.
Tom 采用的配置方法:
1. cd [ORACLE_HOME]/rdbms/admin;
2. 作为 system 登陆 sql*plus; -- 是在上边的目录下登陆
3. 运行@utlxplan;
4. 运行 create public synonym plan_table for plan_table; (我们系统也是将这个table创建在 HTNSADM 用户下的)
5. 运行 grant all on plan_table to public, 这里的public 可以设定为某个特殊用户, public 是指任何用户都可以使用 auto trace 追踪.
接下来创建并授予 plustrace 角色:
1. cd [ORACLE_HOME]/sqlplus/admin;
2. 作为 sys 或 system 登陆sqlplus
3. 运行 @plustrce;
4. 运行 grant plustrace to public -- 同样可以指定某个特殊用户, 我们系统就像给别的用户建立synonym 一样, 只给HANARO, SEHZ两个用户建立了权限.
关于 autotrace
autotrace, 可能列出SQL优化器所用的执行路径, 以及语句的执行统计信息, 成功执行 SQL DML(SELECT, DELETE, UPDATE, MERGE, INSERT) 语句后会生成这个报告, 它对于监视并调优这些语句的性能很有帮助.
控制报告, 设置参数
set autotrace off: 不生成autotrace报告, 这是默认
set autotrace on explain: autotrace 报告只显示优化器执行路径
set autotrace on: autotrace 报告既包括优化器执行路径, 又包括sql语句的统计信息
set autotrace traceonly: 这与 set autotrace on 类似, 但是不显示用户的查询输出(即查询的结果)
配置 Statspack
statspack 作用, 可以使用前后两次快照, 然后比对快照的情况, 可以判断数据库的运行情况.
只有作为 sysdba 连接时才能安装 Statspack.
执行脚本 [ORACLE_HOME]\rdbms\admin 下, @spcreate.sql
执行过程中, 你要考虑3件事情:
- 将创建的 perfstat 模式使用什么密码?
- perfstat 使用默认表空间是什么?
- perfstat 使用的临时表空间是什么?
如果不小心输入错误, 或者是取消了未完成的安装, 再下一次安装前, 要先调用当前目录下的 spdrop.sql, 先删除, 安装时会创建一个 spcpkg.lis 的文件, 如果出现错误就应该检查这个文件.
定制脚本
书中用到的脚本, 及分析
runstats
tom 大师开发的工具, 能对做同一件事的两个不同方法进行比较, 得出优劣情况, 你只需要提供两个不同的方法.
runstats 只是测量3个要素:
- 墙上时钟(wall clock)或耗用时间(elapsed time)
- 系统统计结果: 会并排显示每个方法做某件事(如执行一个解析调用)的次数, 并展示二者之差.
- 闩定(latching): 这个是这个报告的关键输出
注: 闩是一种轻量级的锁. 锁是一种串行化设备, 而串行化设备不支持并发. 我们构建应用时, 应该能够有很好的伸缩性, 这就需要并发的支持, 也就是说, 为1位用户服务与为1000或10000用户服务应该是一样的. 应用中使用闩越少, 性能就越好. 如果一种方法从墙上时钟来看运行时间较长, 但是只使用了另一种方法10%的闩, 我可能会选择前者.
最后, runstats 最好独立使用, 即在单用户数据库上先测试, 在运行过程中, 不希望其他任务队系统的负载或闩产生影响. 只需一个很小的数据库就能完成设置, 比如在自己的 pc 上.
注意: 最好应该有一个自行控制的试验台test数据库, 可以在上面尝试自己的想法.
如果想使用这个工具, 我们需要做3件事.
1. 创建一个视图, 查看系统状态的内容, 需要有查看以下4个v$ view的权限, sys用户有这个权限, 可以在sys用户下创建这个视图, 然后 grant 权限给某个用户
2. 创建一个 table 用来保存我们使用工具后产生的 report.
3. 创建 runstats 包, 也就是工具本身.
要使用 runstats, 需要能访问几个 v$视图, 并创建一个表来存储统计结果, 还要创建 runstats包. v$statname, v$mystat, v$latch
如果你能直接访问视图, 就能直接对这些表进行select操作(相应地可自行创建视图), 否则, 可以由其他人对这些表执行 select 操作为你创建视图, 并授予你在这个视图上执行 select 权限.
创建视图( 如果没有权限访问 v$statname, v$mystat, v$latch, 则可以通过grant来授予权限)
注意: 这里的 grant, 需要针对 v_$statname, v_$mystat, v_$latch 来进行授权, 这才是实际的视图名称.
( 我在这创建了一个用户 leon, 创建了给这个用户的表空间, 然后给这个用户分配了权限, 打算使用这个用户来创建package )
1: CREATE OR REPLACE VIEW STATS
2: AS
3: select 'STAT...' || a.name name, b.value
4: from v$statname a, v$mystat b
5: where a.statistic# = b.statistic#
6: union all
7: select 'LATCH.' || name, gets
8: from v$latch
9: union all
10: select 'STAT...Elapsed Time', hsecs from v$timer
建立完视图以后, 建立一个小表来收集统计信息.
注意, 这里创建的是临时表, 这种表不占用表空间, 而且不同的session之间互相看不到对方的数据, 在会话结束后表中的数据自动清空, preserve rows表示一直保存数据到会话结束
on commit delete rows ( 事务指定, 每次提交时, oracle 会自动 truncate 表) (默认)
on commit preserver rows ( 会话指定, 每次中断会话时, oracle 会自动 truncate 表 )
1: create global temporary table run_stats
2: ( runid varchar2(15),
3: name varchar2(80),
4: value int)
5: on commit preserve rows;
最后, 创建 runstats 包, 其中包含 3 个简单 API 调用:
- RS_START 测试开始时调用
- RS_MIDDLE 测试之间调用
- RS_STOP, 完成时调用, 打印报告.
创建 runstats 包, 如下:
runstats_pkg
参数 p_difference_threshold 用来控制最后打印的数据量. runstats 会收集每次运行的统计结果和闩信息, 然后打印一个报告, 说明每次测试(每个方法)使用了多少资源, 以及不同测试(不同方法)的结果之差. 可以使用这个输入参数来控制只查看差值大于这个数的统计结果和闩信息. 这个参数默认为 0
创建 包体:
runstats_pkg(body)
备注:
chr(9), 这个函数是将阿斯克码多对应的图形, 例如65对应字母’A’, 这里的9 对应水平制表符.
测试 runstat
create table t1 as select * from big_table.big_table where 1 = 0; create table t2 as select * from big_table.big_table where 1 = 0;
bigtable 内容请看下面补充 big_table
exec runstats_pkg.rs_start; -- 初始化的过程
-- 第一种方法插入 1000000 数据
insert into t1 select * from big_table.big_table where rownum <= 1000000commit;
exec runstats_pkg.rs_middle;
-- 第二种方法插入 1000000 数据
begin for x in ( select * from big_table.big_table where rownum <= 1000000) loop insert into t2 values X; end loop; end; /
最后, 我们得出 report
exec runstats_pkg.rs_stop(1000000)
Mystat
set echo off set verify off column value new_val V define S="&1" 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') || '%' / set echo on
上面代码的 column value new_val V 在 Mystat2 中会用到, 这句话的含义是 将 value 这列的值赋值给 V
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') || '%'; / set echo on
show_space
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 ) 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; -- procedure insert procedure 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 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.tablespace_name = ts.tablespace_name into l_segment_space_mgmt using p_segname, p_partition, p_partition, p_owner'; exception when too_many_rows then dbms_output.put_line('This must be a partitioned table, use p_partition=> '); return; end; 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; 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; /
big_table
测试用例, 里边有1~400w 行记录, 大概size 200m ~ 800m
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 := &1; begin insert /*+ append */ into big_table select rownum, a.* from all_objects a where rownum <= &1; 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);
变量命名习惯
全局变量 g_variable
参数变量 p_variable
局部变量 l_variable
