《Oracle编程艺术》学习笔记


1) SQL*Plus的login.sql 文件
使用SQL*PLUS登陆时，SQL*PLUS会按照如下的顺序读取并执行脚本：
· $Oracle_home/sqlplus/admin/glogin.sql
· 当前目录下的login.sql，或者设置一个环境变量SQLPATH(Windows下面通过注册表)来指定一个目录
[sql]
  1. define _editor=vi  
  2. set serveroutput on size 1000000  
  3. set trimspool on  
  4. set long 5000  
  5. set linesize 100  
  6. set pagesize 9999  
  7. column plan_plus_exp format a80  
  8. column global_name new_value gname  
  9. set termout off  
  10. define gname=idle  
  11. column global_name new_value gname  
  12. select lower(user) || '@' || substr( global_name, 1,  
  13. decode( dot, 0, length(global_name), dot-1) ) global_name  
  14. from (select global_name, instr(global_name,'.') dot from global_name );  
  15. set sqlprompt '&gname> '  
  16. 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);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9..
  • 40
  • 下一页
【内容导航】
第1页：配置环境 第2页：开发成功的Oracle应用
第3页：SELECT FOR UPDATE SKIP LOCKED 第4页：Oracle体系结构
第5页：Oracle中的文件 第6页：Oracle中的文件-参数文件
第7页：Oracle中的文件-Trace文件 第8页：Oracle中的文件-警告日志文件
第9页：Oracle中的文件-数据文件 第10页：SGA,PGA和UGA
第11页：Oracle进程 第12页：Oracle的锁
第13页：Oracle的并发与多版本控制 第14页：写一致性
第15页：事务原子性 第16页：事务持久性
第17页：DEFERRABLE约束 第18页：REDO和UNDO
第19页：数据库日志模式 第20页：块清除
第21页：ORA-01555:snapshot too old 错误 第22页：表类型
第23页：段 第24页：堆组织表
第25页：索引组织表 第26页：索引聚簇表
第27页：散列聚簇表 第28页：有序散列聚簇表
第29页：临时表和CBO 第30页：B*树索引
第31页：什么情况下适合使用B*树索引 第32页：位图索引（bitmap index）
第33页：基于函数的索引 第34页：数据类型-字符串和二进制串
第35页：数据类型-数值 第36页：数据类型-时间日期
第37页：数据类型-LOB 第38页：表分区
第39页：索引分区 第40页：分区的优点

相关内容