1. 初始化参数脚本
修改glogin.sql 脚本, 此脚本为登录 sql/plus时会读取的脚本, 可以在这个脚本上设置一些参数, 比如 set serveroutput on 等等, 那么在登录时就会直接使用该参数了, 下边是常设置的参数:
-- 我经常使用的设置
define _editor=/usr/bin/vim
set serveroutput on size 100000( 其中 size 是设置缓冲区大小, 一般可以不使用 )
set linesize 120
set newpage 0
set feedback on
set pagesize 12 (Tom 大师使用 set pagesize 9999)
set trimspool on ( 去除末尾空格, 一般报表使用 )
set long 5000 ( 大数据5000个字节 )
set sqlprompt "_user '@' _connect_identifier>"
column plan_plus_exp format a80
set termout on ( Tom 大师使用 )
另外, 就是这些配置信息放置的问题, 有两个地方
1. $ORACLE_HOME/sqlplus/admin/glogin.sql, 这个文件, 所有的用户使用 sqlplus 时, 都会读取这个信息, 换句话说, 你的设置会被他人所共享.
所以, 一般推荐不要更改这个文件中的内容.
2. 就是你进入 sqlplus 的当前目录下, 文件名为, login.sql, 这样, 就要求你每次进入sqlplus时, 都要从这个目录进去, 这样, 这个文件中的设置内容就会生效.(可以通过linux的实体连接对经常使用的几个目录中都添加login.sql文件, 这样的好处是, 可以做到一改全改.)
2. 在 SQL*PLUS 设置 autotrace
设置autotrace 的目的是, 跟踪SQL, 直接就可以查看执行SQL的执行计划 和 statics 资源使用情况.
注意: autotrace 只能在 sqlplus 中使用.
-------------------------------------------------------------------------------------------------------------------------------
使用过程:
关于autotrace几个常用选项的说明:
SET AUTOTRACE OFF ------------ 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ----- AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------- 同set autotrace on，但是不显示查询输出
结果解释
physical reads 物理读——执行SQL的过程中，从硬盘上读取的数据块个数
redo size 重做数——执行SQL的过程中，产生的重做日志的大小
bytes set via sql*net to client 通过sql*net发送给客户端的字节数
bytes received via sql*net from client 通过sql*net接受客户端的字节数
sorts(memory) 在内存中发生的排序
sorts(disk) 不能在内存中发生的排序，需要硬盘来协助
rows processed 结果的记录数
db block gets 从 buffer cache 中读取的块
consistent gets 从 buffer cache 中读取的 undo 数据的block数量
AutoTrace进行优化的注意事项
1． 可以通过设置timing来得到执行SQL所用的时间，但不能仅把这个时间来当作SQL执行效率的唯一量度。这个时间会包括进行AUTOTRACE的一些时间消耗，所以这个时间并不仅仅是SQL执行的时间。这个时间会与SQL执行时间有一定的误差，而在SQL比较简单的时候尤为明显。
2． 判断SQL效率高低应该通过执行SQL执行状态里面的逻辑读的数量 逻辑读 =（db block gets+ consistent gets）
-------------------------------------------------------------------------------------------------------------------------------
安装: 以下安装, 我们系统是通过 htnsadm 用户安装的, 另外 也不是将权限赋给了 public, 而是指定的user
a. cd $ORACLE_HOME/rdbms/admin
以SYS用户登录sqlplus(要有create table 和 create public synonym 权限)
@utlxplan 运行这个脚本会创建一个表
grant all on plan_table to public;
b. cd $ORACLE_HOME/sqlplus/admin
登录sqlplus以 SYSDBA 权限
@plustrce 运行这个脚本
grant plustrace to public
c. 设置 set autotrace traceonly 来检验是否设置成功
set autotrace off 关闭跟踪
3. 设置 timed_statistics
timed_statistics 这个参数是在实例初始化参数文件中设置的, 用来测量各种内部操作的执行时间, 如果没有设置这个参数,跟踪文件的输出价值就会锐减.
timed_statistics=true, alter session set timed_statistics=true;
4. SQLTRACE 工作
sql_trace用于将每条SQL语句的性能信息写到数据库服务器文件系统的跟踪文件中, 所以需要很多I/O操作, 对性能是有影响的, 而且这些文件直接读取, 难以理解, 要通过工具 TKPROF
可以通过参数 SQL_TRACE 设置这个过程, 如果设置为true, 那么数据库就会对SQL进行跟踪, 这里要特别注意, 不要将全库都设置SQL_TRACE, 因为很耗费资源, 如果想针对某个session来进行跟踪, 看以按照如下:
在生产环境中慎用 SQL_TRACE
alter session set sql_trace=true; 启用 sqltrace
alter session set sql_trace=false; 关闭 sqltrace
SQL_TRACE 会把文件写到 user_dump_dest 参数目录下, 而且会慢慢便的很大.
SQL_TRACE 不推荐在 shared-server 中使用.
Unix:
Windows:
select c.vale || ‘\ora’ || to_char(a.spid, ‘fmooooo’ ) || ‘.trc’
from v$process a, v$session b, v$parameter c
where a.addr = b.paddr
and b.audsid = userenv(‘sessionid’)
and c.name = ‘user_dump_dest’;
跟踪文件大小限制 设置:
alter session set max_dump_file_size = unlimited;
补充: 10046 事件说明
10046 事件是 oracle 提供的内部事件, 对SQL_TRACE的增强, 10046可以设置4个级别:
level 1: 启用标准的 SQL_TRACE 功能, 等价于SQL_TRACE.
level 4: 等价于 level1 + 绑定变量
level 8: 等价于 level1 + 等待事件跟踪
level12: 等价于 leve1 + level4 + level8
在session 级别设置等待事件: alter session set events '10046 trace name context forever, level 8';
关闭等待事件: alter session set events '10046 trace name context off';
案例: java 应用, 一个大型购物网站
首先, 找到需要跟踪的session信息:
select sid, serial#, username from v$session; ( 这里查询出来的, 我们需要跟踪的结果为:)
sid serial# username
7 284 IFLOW
11 214 IFLOW
16 1042 IFLOW
然后, 设置跟踪:
exec dbms_system.set_sql_trace_in_session(7, 284, true); -- 在一个session下 设置另一个session的 SQL_TRACE.
exec dbms_system.set_sql_trace_in_session(11, 214, true);
exec dbms_system.set_sql_trace_in_session(16, 1042, true);
此时, 在前台的页面中正常操作, 然后等待一段时间, 关闭 SQL_TRACE.
exec dbms_system_set_sql_trace_in_session(7, 284, false);
exec dbms_system.set_sql_trace_in_session(11, 214, false);
exec dbms_system.set_sql_trace_in_session(16, 1042, false);
然后, 检查 trace 文件: 在 user_dump_dest 目录下, 可找到生成的跟踪文件, 使用 tkprof 对文件进行格式化:
另外, 通过10046 可以查看等待事件的信息, 主要记录等待事件的动态视图: v$session_wait, v$system_event, 前者是跟session有关, 后者是整个数据库的说明.
5. 使用 TKPROF
作用: 主要是用来查看 SQL_TRACE 文件的, 生成人们能够读懂的报告.
tkprof 是一个操作系统命令, 是在操作系统环境下运行的, 估计是安装完oracle软件以后, 自动带的.
tkprof <trace_file_name> <report_file_name>
例如 tkprof ora01525.trc tkpro_rep1.txt
6. Setting up Statspack ( 免费, 好用 )
statspack 作用, 可以使用前后两次快照, 然后比对快照的情况, 可以判断数据库的运行情况.
只有作为 sysdba 连接时才能安装 Statspack.
执行脚本 [ORACLE_HOME]\rdbms\admin 下, @spcreate.sql
执行过程中, 你要考虑3件事情:
- 将创建的 perfstat 模式使用什么密码? answer: perfstat
- perfstat 使用默认表空间是什么? answer: perfstat
- perfstat 使用的临时表空间是什么? answer: temp
如果不小心输入错误, 或者是取消了未完成的安装, 再下一次安装前, 要先调用当前目录下的 spdrop.sql, 先删除, 安装时会创建一个 spcpkg.lis 的文件, 如果出现错误就应该检查这个文件.
由于 Statspack 需要一定的存储空间, 最好建立独立的表空间, 如果采样间隔较短, 周期较长, 打算长期使用, 那么就可能需要大一点的表空间, 假设每隔半个小时采样一次, 连续采样一周, 那么statspack 产生的数据量是很大的, 所以使用 statspack 一定要注意, 如果设置了间隔自动采样, 忘记关闭, 就会出现表空间不足, 所以尽量避免使用自动采样, 这里先设置 500m 的表空间(最少 100m)
create tablespace perfstat
datafile '/opt/u01/app/oracle/oradata/dbhz01/perfstat.dbf'
size 500m
extent management local;
接下来开始安装 perfstat, 首先执行脚本: [ORACLE_HOME]\rdbms\admin 下, @spcreate.sql
按照提示, 输入以上三个 answer 就可以了
如何使用 statspack
为了能够执行自动 statspack, job_queue_process(这是一个参数, 这个参数必须大于0)
alter system set job_queue_processes=6;
另外要设置上边的 timed_statistic 为 true.
规划自动任务: 先看[ORACLE_HOME]\rdbms\admin 这个目录下的, spauto.sql, 其中 dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
其中, 1/24 HH 表示每隔一小时
1/48 M, 即每隔半小时
1/144MI, 即每隔10分钟
1/288 MI, 即每隔5分钟
可以修改这个时间间隔, 然后执行 spauto.sql 脚本
关于采样间隔, 通常建议每隔 1 小时采样一次, 对于特殊环境要求时间更短, 则可使用临时snap.
执行单次采样: exec statspack.snap
然后隔几次, 再执行一个单次采样, 然后生成报告, 生成报告办法: @?/rdbms/admin/spreport.sql
删除定时任务:
select job, log_user, priv_user, last_date, next_date, interval from user_jobs; -- 查询到 job No.
execute dbms_job.remove('22') -- 22 为 job No.
当完成了一个采样报告, 就应该及时删除这个 job 任务, 在生产环境中, 遗漏一个无人照顾的job是非常危险的, 如果 statspack 连续运行一个星期或更长时间, 采样的数据量可能会非常惊人
生成分析报告
@spreport
注意: 在生成报告之前, oracle 会查询列出所有采样的 snap ID, 可以按照先后顺序任选两个 Snap ID 生成一个报告, begin_snap 就是起始snap号， end_snap就是结束snap号, 生成报表名称可以自己定义, 也可以使用缺省的.
注: 不用刻意去查找 snap ID, 因为在运行spreport这个脚本时, 会将所有的snap Id 及时间点都显示出来, 你所要做的是 从这些ID中挑出两个作为 begin, end. 另外, 最后还会提供要保存的文件名, 例如 我输入1.txt
删除历史数据
删除 stats$snapshot 数据表中的相应数据, 其他表中的数据会相应的级联删除:
例如: delete from stats$snapshot where snap_id <= 166;
oracle 提供了删除历史数据的脚本, sppurge.sql , 不过以上的删除办法会产生大量的 归档日志, 如果采样了大量数据, 直接delete缓慢, 可以考虑 sptrunc.sql ( truncate 操作)
