《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