《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);

4) AWR
(转载自：http://wenku.baidu.com/view/0d2e474bfe4733687e21aa33.html)
1. AWR报告
Oracle Database 10g 提供了一个显著改进的工具：自动工作负载信息库 (AWR:Automatic Workload Repository)。 Oracle 建议用户用这个取代 Statspack。
1.1. AWR的由来
10g之前的oracle用户的连接将产生会话，当前会话记录保存在v$session中；处于等待状态的会话会被复制一份放在v$session_wait中。当该连接 断开后，其原来的连接信息在v$session和v$session_wait中就会被删除;oracle10g及之后保留下了v$session_wait中的这些信息，并多了v$active_session_history（ASH）视图，记录每个活动session在v$session_wait中最近10次的等待事件。
ASH的采样数据是保存在内存中。而分配给ASH的内存空间是有限的，当所分配空间占满后，旧的记录就会被覆盖掉；而且数据库重启后，所有的这些ASH信息都会消失。这样，对于长期检测oracle的性能是不可能的。在Oracle10g中，提供了永久保留ASH信息的方法，这就是AWR（Automatic Workload Repository，自动工作负载库）。
由于全部保存ASH中的信息是非常耗费时间和空间的，AWR采用的策略是：每小时对v$active_session_history进行采样一次，并将信息保存到磁盘中，并且保留7天，7天后旧的记录才会被覆盖。这些采样信息被保存在视图wrh$_active_session_history中。而这个采样频率（1小时）和保留时间（7天）是可以根据实际情况进行调整的，这就给DBA们提供了更加有效的系统监测工具。

1.2. AWR的启用
在默认情况下，Oracle启用数据库统计收集这项功能（即启用AWR）。是否启用AWR由初始化参数STATISTICS_LEVEL控制。通过SHOW PARAMETER命令，可以显示STATISTICS_LEVEL的当前值：
SQL> SHOW PARAMETER STATISTICS_LEVEL
SQL语句的执行结果是：
NAME TYPE VALUE
statistics_level string TYPICAL

如果STATISTICS_LEVEL的值为TYPICAL或者 ALL，表示启用AWR；如果STATISTICS_LEVEL的值为BASIC，表示禁用AWR。
初始化参数statistics_level介绍：
AWR的行为受到参数STATISTICS_LEVEL的影响。这个参数有三个值：
*BASIC：awr统计的计算和衍生值关闭.只收集少量的数据库统计信息.
*TYPICAL：默认值．只有部分的统计收集.他们代表需要的典型监控oracle数据库的行为.
*ALL : 所有可能的统计都被捕捉. 并且有操作系统的一些信息.这个级别的捕捉应该在很少的情况下,比如你要更多的sql诊断信息的时候才使用.

1.3. 快照（SNAPSHOT）
每隔一小时，内存监控进程（MMON）自动地采集一次统计信息，并把这些信息存放到负载库中，一次采样就是一个快照。为了节省空间，采集的数据在7天后自动清除。快照的频率和保留时间可以由用户修改。
1) 查看快照的频率和保留时间（默认为每1小时采样一次，采样信息保留时间为7天）
select * from dba_hist_wr_control;
select DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION from wrm$_wr_control;
2) 修改 快照的频率和保留时间（单位用分钟）
exec dbms_workload_repository.modify_snapshot_settings(interval=>60, retention=>7*24*60);
3) 手工生成快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
4) 手工删除指定范围的快照
exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 3965, high_snap_id => 3966, dbid => 3437504306);
5) 查看有多少个快照
select count(1) from wrh$_active_session_history;
select count(1) from dba_hist_active_sess_history;

通过查询视图DBA_HIST_SNAPSHOT，可以知道系统中产生了哪些快照。
select * from DBA_HIST_SNAPSHOT;
6) 采样数据存放位置
这些采样数据都存储在SYSAUX表空间中，并且以WRM$_* 和 WRH$_*的格式命名。前一种类型存储元数据信息（如检查的数据库和采集的快照），后一种类型保存实际采集的统计数据。
select table_name from dba_tables where table_name like 'WRM$%';
TABLE_NAME
-----------------------
WRM$_WR_CONTROL
WRM$_SNAP_ERROR
WRM$_SNAPSHOT
WRM$_DATABASE_INSTANCE
WRM$_BASELINE
当SYSAUX表空间满后，AWR将自动覆盖掉旧的信息，并在警告日志中记录一条相关信息：
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_3533490838_1522 by 128 in tablespace SYSAUX

1.4. 设置基线
1.4.1. 创建基线
基线（baseline）是一种机制,这样你可以在重要时间的快照信息集做标记。一个基线定义在一对快照之间,快照通过他们的快照序列号识别.每个基线有且只有一对快照。一次典型的性能调整实践从采集量度的基准线集合、作出改动、然后采集另一个基准线集合开始。可以比较这两个集合来检查所作的改动的效果。在 AWR 中，对现有的已采集的快照可以执行相同类型的比较。
假定一个名称为 apply_interest 上午 2:00 到 4:00 之间运行，对应快照 ID 4150 到 4151。我们可以为这些快照定义一个名称为 apply_interest_1 的基准线：
SQL> exec dbms_workload_repository.create_baseline(4150, 4151, 'apply_interest_1');
这一操作将快照从 4150 到 4151 编号，作为上面指定的基准线的一部分。查看现有的基准线：
SQL> select *from dba_hist_baseline;
DBID BASELINE_ID BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME
---------- ----------- -------------------- ------------- -------------------------------- ----------- -------------------------------
3437504306 1 apply_interest_1 4150 07-3月 -11 03.00.47.627 上午 4151 07-3月 -11 04.00.12.567 上午

SQL> select *from wrm$_baseline;
DBID BASELINE_ID BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------- ----------- ------------------------------ ------------- -----------
3437504306 1 apply_interest_1 4150 4151
在一些调整步骤之后，我们可以创建另一个基准线 — 假设名称为 apply_interest_2（下午2点到4点），然后只为那些与这两条基准线相关的快照比较量度。
SQL> exec dbms_workload_repository.create_baseline(4162, 4163, 'apply_interest_2');
像这样把快照分隔在仅仅几个集合中有助于研究调整对于性能量度的影响。
1.4.2. 删除基线
分析之后使用 drop_baseline() 来删除基准线；快照将保留（也可级联删除）。此外，当清除例程开始删除旧的快照时，与基准线相关的快照不会被清除，从而允许进行进一步的分析。如果要删除一个基准线:
SQL>exec dbms_workload_repository.drop_baseline(baseline_name=>'apply_interest_1', cascade=>false);

SQL> select *from wrh$_active_session_history where snap_id in (4150,4151);

SNAP_ID DBID INSTANCE_NUMBER SAMPLE_ID SAMPLE_TIME SESSION_ID ...
4150 3437504306 1 14900840 07-3月 -11 02.55.02.038 上午 162 ...
4150 3437504306 1 14900200 07-3月 -11 02.44.21.942 上午 165 ...
....
4151 3437504306 1 14901980 07-3月 -11 03.14.02.213 上午 165 ...
4151 3437504306 1 14901790 07-3月 -11 03.10.52.183 上午 165 ...
4151 3437504306 1 14901490 07-3月 -11 03.05.52.138 上午 167 ...

--级联删除（基线与快照一块删）
SQL> exec dbms_workload_repository.drop_baseline(baseline_name=>'apply_interest_2', cascade=>true);

SQL> select *from wrh$_active_session_history where snap_id in (4162,4163);
未选定行

1.5. 生成AWR报告
Oracle可以产生两种类型的AWR报告：文本格式和HTML 格式。HTML格式的报告界面更加友好。AWR报告用于显示两个快照或者两个时间点之间捕捉到的数据。AWR报告其实就是一张数据库健康体检表，它显示了数据库健康的各项指标。
有针对整个数据库的AWR报告，有针对某个实例的AWR报告（在集群环境），有针对单条SQL语句的AWR报告。通过运行Oracle自带的SQL脚本产生
AWR报告，只是产生不同的AWR报告，需要运行不同的脚本。
--产生整个数据库的AWR报告，运行脚本awrrpt.sql。
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
--产生某个实例的AWR报告，运行脚本awrrpti.sql。
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
--产生某条SQL语句的AWR报告，运行脚本awrsqrpt.sql。
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
--注： $ORACLE_HOME代表Oracle的主目录。
示例：产生整个数据库的AWR报告。本例中，Oracle的主目录是 /oracle/product/10.2.0/db_1/rdbms/admin

运行脚本awrrpt.sql。
SQL> @/oracle/product/10.2.0/db_1/rdbms/admin/awrrpt.sql
选择报告的类型。
1. 输入 report_type 的值: html 本例中选择HTML。
列出最近两天产生的快照（主要是获得快照ID）
2. 输入 num_days 的值: 2

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
telemt TELEMT 4123 06 Mar 2011 00:00 1
4124 06 Mar 2011 01:01 1
....
4160 07 Mar 2011 13:00 1
4161 07 Mar 2011 14:00 1

指定起始快照的ID和结束快照的ID。

1. Specify the Begin and End Snapshot Ids
2. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3. 输入 begin_snap 的值: 4123
4. Begin Snapshot Id specified: 4123
5.
6. 输入 end_snap 的值: 4161
7. End Snapshot Id specified: 4161
本例中起始快照的ID是4123，结束快照的ID是4161。
指定报告的名字。
输入 report_name 的值: myreport.html
解决办法：用sys用户来执行或者对用户进行授权 grant all on dbms_workload_repository to perfstat;

1.6. AWR报告分析
1.6.1. SQLordered by Elapsed Time：
记录了执行总和时间的TOP SQL(请注意是监控范围内该SQL的执行时间总和，而不是单次SQL执行时间ElapsedTime= CPUTime+Wait Time)。
SQL ordered by Elapsed Time
• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Elapsed Time(S): SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。单位时间为秒。ElapsedTime= CPUTime+Wait Time
CPU Time(s): 为SQL语句执行时CPU占用时间总时长，此时间会小于等于Elapsed Time时间。单位时间为秒。
Executions: SQL语句在监控范围内的执行次数总计。
Elap per Exec(s):执行一次SQL的平均时间。单位时间为秒。
% Total DB Time: 为SQL的Elapsed Time时间占数据库总时间的百分比。
SQL ID:SQL语句的ID编号，点击之后就能导航到下边的SQL详细列表中，点击IE的返回可以回到当前SQL ID的地方。
SQL Module: 显示该SQL是用什么方式连接到数据库执行的，如果是用SQL*Plus或者PL/SQL链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。
SQL Text:简单的sql提示，详细的需要点击SQL ID。
1.6.2. SQL ordered by CPU Time:
记录了执行占CPU时间总和时间最长的TOP SQL(请注意是监控范围内该SQL的执行占CPU时间总和，而不是单次SQL执行时间)。
1.6.3. SQL ordered by Gets:
记录了执行占总buffer gets(逻辑IO)的TOP SQL(请注意是监控范围内该SQL的执行占Gets总和，而不是单次SQL执行所占的Gets).
1.6.4. SQL ordered by Reads:
记录了执行占总磁盘物理读(物理IO)的TOP SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和，而不是单次SQL执行所占的磁盘物理读)。
1.6.5. SQL ordered by Executions:
记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数。
1.6.6. SQL ordered by Parse Calls:
记录了SQL的软解析次数的TOP SQL。
说到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：
　　1、语法检查(syntax check)
　　检查此sql的拼写是否语法。
　　2、语义检查(semantic check)
　　诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
　　3、对sql语句进行解析(prase)
　　利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。
　　4、执行sql，返回结果(execute and return)
　　其中，软、硬解析就发生在第三个过程里。
　　Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值；
　　假设存在，则将此sql与cache中的进行比较；
　　假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。
　　诚然，如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。
　　创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。这就是在很多项目中，倡导开发设计人员对功能相同的代码要努力保持代码的一致性，以及要在程序中多使用绑定变量的原因。
1.6.7. SQL ordered by Sharable Memory:
记录了SQL占用library cache的大小的TOP SQL。
Sharable Mem (b)：占用library cache的大小。单位是byte。
1.6.8. SQL ordered by Version Count:
记录了SQL的打开子游标的TOP SQL。
在 硬解析的过程中，进程会一直持有library cach latch，直到硬解析结束。硬解析结束以后，会为该SQL产生两个游标，一个是父游标，另一个是子游标。父游标里主要包含两种信息：SQL文本以及优化 目标（optimizer goal）。父游标在第一次打开时被锁定，直到其他所有的session都关闭该游标后才被解锁。当父游标被锁定的时候是不能被交换出library cache的，只有在解锁以后才能被交换出library cache，这时该父游标对应的所有子游标也被交换出library cache。子游标包括游标所有的信息，比如具体的执行计划、绑定变量等。子游标随时可以被交换出library cache，当子游标被交换出library cache时，oracle可以利用父游标的信息重新构建出一个子游标来，这个过程叫reload。可以使用下面的方式来确定reload的比率：
SELECT 100*sum(reloads)/sum(pins) Reload_Ratio FROM v$librarycache;
一 个父游标可以对应多个子游标。子游标具体的个数可以从v$sqlarea的version_count字段体现出来。而每个具体的子游标则全都在 v$sql里体现。当具体的绑定变量的值与上次的绑定变量的值有较大差异（比如上次执行的绑定变量的值的长度是6位，而这次执行的绑定变量的值的长度是 200位）时或者当SQL语句完全相同，但是所引用的对象属于不同的schema时，都会创建一个新的子游标
1.6.9. SQL ordered by Cluster Wait Time:
记录了集群的等待时间的TOP SQL

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11980046/viewspace-736006/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11980046/viewspace-736006/