《Oracle编程艺术》学习笔记(1)-配置环境

1) SQL*Plus的login.sql 文件
使用SQL*PLUS登陆时，SQL*PLUS会按照如下的顺序读取并执行脚本：
· $oracle_home/sqlplus/admin/glogin.sql
· 当前目录下的login.sql，或者设置一个环境变量SQLPATH(Windows下面通过注册表)来指定一个目录

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
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

脚本说明：
· DEFINE_EDITOR=VI：设置SQL*Plus使用的默认编辑器。可以把默认编辑器设置为你最喜欢的文本编辑器（而不是字处理器），如记事本（Notepad）或emacs。
· SET SERVEROUTPUT ON SIZE 1000000：这会默认地打开DBMS_OUTPUT（这样就不必每次再键入这个命令了）。另外也将默认缓冲区大小设置得尽可能大。
· SET TRIMSPOOL ON：假脱机输出文本时，会去除文本行两端的空格，而且行宽不定。如果设置为OFF（默认设置），假脱机输出的文本行宽度则等于所设置的LINESIZE。
· SET LONG 5000：设置选择LONG 和CLOB 列时显示的默认字节数。
· SET LINESIZE 100：设置SQL*Plus显示的文本行宽为100个字符。
· SET PAGESIZE 9999：PAGESIZE可以控制SQL*Plus多久打印一次标题，这里将PAGESIZE设置为一个很大的数（所以每页只有一组标题）。
· COLUMN PLAN_PLUS_EXP FORMAT A80：设置由AUTOTRACE得到的解释计划输出（explain plan output）的默认宽度。A80通常足以放下整个计划。
· 之后这部分用于建立SQL*Plus提示符：COLUMN GLOBAL_NAME NEW_VALUE GNAME 指令告诉SQL*Plus 取得GLOBAL_NAME 列中的最后一个值，并将这个值赋给替换变量GNAME。接下来，从数据库中选出GLOBAL_NAME，并与我的登录用户名连接。这样一来，就能知道我是谁，还有我在哪儿。

2）设置SQL*Plus的AUTOTRACE
（1）cd [ORACLE_HOME]/rdbms/admin；
（2）作为SYSTEM登录SQL*Plus；
（3）运行@utlxplan；
（4）运行CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE；
（5）运行GRANT ALL ON PLAN_TABLE TO PUBLIC。
下一步是创建并授予PLUSTRACE角色：
（1）cd [ORACLE_HOME]/sqlplus/admin；
（2）作为SYS 或SYSDBA登录SQL*Plus；
（3）运行@plustrce；
（4）运行GRANT PLUSTRACE TO PUBLIC。

通过设置AUTOTRACE系统变量可以控制这个报告：
· SET AUTOTRACE OFF：不生成AUTOTRACE报告，这是默认设置。
· SET AUTOTRACE ON EXPLAIN：AUTOTRACE报告只显示优化器执行路径。
· SET AUTOTRACE ON STATISTICS：AUTOTRACE 报告只显示SQL语句的执行统计信息。
· SET AUTOTRACE ON：AUTOTRACE报告既包括优化器执行路径，又包括SQL语句的执行统计信息。
· SET AUTOTRACE TRACEONLY：这与SET AUTOTRACE ON类似，但是不显示用户的查询输出（如果有的话）。

另外，也可以利用explain plan语句来得到执行计划，默认情况下执行计划存入plan_table中，可以使用DBMS_XPLAN包来查看结果。例如：
explain plan for select *from emp where deptno=10;
select * from table(dbms_xplan.display);

3）配置statspack
安装
只有作为SYSDBA 连接时才能安装Statspack。
只需运行[ORACLE_HOME]\rdbms\admin目录下的spcreate.sql脚本。
安装过程中，需要将创建的PERFSTAT用户，需要指定使用什么密码，默认表空间和临时表空间是什么？
如果需要重新安装Statspack，之前应该先用spdrop.sql 删除用户（PERFSTAT）和已经安装的视图。

生成报告
执行statspack.snap可以产生系统快照，运行两次，然后执行@spreport.sql就可以生成一个基于两个时间点的报告。(使用perfstat或者sys用户登录，否则很多表无权限访问)

可以使用spauto.sql来定义自动收集数据任务。spauto.sql的关键内容如下
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
来看看dbms_job.submit的定义：

PROCEDURE SUBMIT
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER OUT
WHAT VARCHAR2 IN
NEXT_DATE DATE IN DEFAULT
INTERVAL VARCHAR2 IN DEFAULT
NO_PARSE BOOLEAN IN DEFAULT
INSTANCE BINARY_INTEGER IN DEFAULT
FORCE BOOLEAN IN DEFAULT

因此，上面这条语句的意思就是从当前时间的下一个整点之后，每隔1小时执行1次statspack.snap。
可以通过修改spauto.sql来修改间隔。
执行后可用下列语句查看任务运行的时间：
select job, next_date, next_sec from user_jobs where job = :jobno;

应及时移除收集任务，通过下面的语句：
execute dbms_job.remove(:jobno)

删除历史数据
如需删除历史数据，可以用@sptrunc.sql脚本删除所有的数据。也可以通过删除stats$snapshot表来进行，其他表中的数据也会相应的级联删除。
declare
snapid number;
begin
select max(snap_id) into snapid from stats$snapshot;
delete from stats$snapshot where snap_id <= snapid;
end;
/

收集数据选项
Statspack有两种类型的收集选项
·级别（level）：控制收集数据的类型
共有三种快照级别，默认值是5。
a.level 0: 一般性能统计。包括等待事件、系统事件、系统统计、回滚段统计、行缓存、SGA、会话、锁、缓冲池统计等等。
b.level 5: 增加SQL语句。除了包括level0的所有内容，还包括SQL语句的收集，收集结果记录在stats$sql_summary中。
c.level 10: 增加子锁存统计。包括level5的所有内容。并且还会将附加的子锁存存入stats$lathc_children中。在使用这个级别时需要慎重，建议在Oracle support的指导下进行。

可以通过以下语句修改缺省的级别设置，如果只是改变本次收集level，则不需指定i_modify_parameter参数。
execute statspack.snap(i_snap_level=>0,i_modify_parameter=>'true');

·门限（threshold）：设置收集的数据的阈值。
快照门限只应用于stats$sql_summary表中获取的SQL语句。有以下门限值：
a. executions_th这是SQL语句执行的数量(默认值是100)
b. disk_reads_tn这是SQL语句执行的磁盘读入数量（默认值是1000）
c. parse_calls_th这是SQL语句执行的解析调用的数量（默认值是1000）
d. buffer_gets_th这是SQL语句执行的缓冲区获取的数量（默认值是10000）
任何一个门限值超过以上参数的SQL就会被收集并产生一条记录。

通过调用statspack.modify_statspack_parameter函数可以改变门限的默认值。例如：
execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000);