Oracle数据库常见性能分析参考手册

该词条用于记录我们在开发过程，用到Oracle数据库时，在数据库脚本设计与数据库性能上的一些经验总结。欢迎留言补充指正。

更多了解oracle使用和性能优化方面的问题，请参看oracle官方文档。

Oracle使用小知识

1、在给外场人员提供oracle数据库脚本时，需要注意：外场人员一般都是用sqlplus命令端去执行，因此在涉及到存储过程等过程时语句时，注意在语句中增加结束符“/”。

2、对表上某个字段创建索引，需要考虑到该字段是否可以为空。如果可以为空，就需要考虑升级情况，老数据上oracle默认该字段为空值。这种情况下，该字段的索引不会生效。需要考虑效率问题。比如查询条件中排除掉老数据。

Oracle数据库日志路径

10g: $ORACLE_BASE/admin/SID/bdump/alert_SID.log --SID替换为数据库实例名

11g：$ORACLE_BASE/diag/rdbms/SID/SID/trace/alert_SID.log

12c: $ORACLE_BASE/diag/rdbms/prod/PROD/alert --通过视图v$diag_info; 查询到的

Oracle数据库性能awr报告采集

cd $ORACLE_HOME/rdbms/admin
sqlplus sys/oracle@gomcr as sysdba;
SQL> @awrrpt.sql;
Enter value for report_type:   回车
Enter value for num_days:7
                              10638 11 Dec 2008 04:00      1
                              10639 11 Dec 2008 05:00      1
                              10640 11 Dec 2008 06:00      1
                              10641 11 Dec 2008 07:00      1
                              10642 11 Dec 2008 08:00      1
                              10643 11 Dec 2008 09:00      1
                              10644 11 Dec 2008 10:00      1
                              10645 11 Dec 2008 11:00      1
                              10646 11 Dec 2008 12:00      1
                              10647 11 Dec 2008 13:00      1
                              10648 11 Dec 2008 14:00      1
                              10649 11 Dec 2008 15:00      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 10648
Enter value for end_snap: 10649
sql>quit
注：begin_snap与 end_snap不要间隔时间太长，推荐连续的id，生成的html文件即为arw报告

 

Oracle数据库性能分析常见查询

请用system用户登录数据库，执行以下sql检查Oracle性能，并将检查结果输出为文件。

数据库连接泄露判断

关注测试过程中，如果SQL语句链接数是持续增加，可能存在泄漏。

select substr(q.SQL_TEXT,0,40),count(*) from v$sqlarea q,v$session s where s.PREV_HASH_VALUE=q.HASH_VALUE group by substr(q.SQL_TEXT,0,40) order by 2 desc

查看数据库所在磁盘IO情况

select d.name, 
f.phyrds reads, 
f.phywrts wrts, 
(f.readtim / decode(f.phyrds, 0, -1, f.phyrds)) readtime, 
(f.writetim / decode(f.phywrts, 0, -1, phywrts)) writetime 
from v$datafile d, v$filestat f 
where d.file# = f.file# 
order by d.name;

查看cpu和等待时间占比

select metric_name, value 
from v$sysmetric 
where metric_name in 
('Database CPU Time Ratio', 'Database Wait Time Ratio') 
and intsize_csec = (select max(intsize_csec) from v$sysmetric);

查看耗时查询

select b.username username, 
a.LAST_ACTIVE_TIME, 
a.FIRST_LOAD_TIME, 
a.LAST_LOAD_TIME, 
a.PLSQL_EXEC_TIME, 
a.disk_reads reads, 
a.executions exec, 
a.disk_reads / decode(a.executions, 0, 1, a.executions) rds_exec_ratio, 
a.sql_text Statement 
from v$sqlarea a, dba_users b 
where a.parsing_user_id = b.user_id 
and a.disk_reads > 100000 
order by a.disk_reads desc;

某条SQL执行的trace信息

不同版本，方法可能不一样。

设置打印trace标志: alter session set sql_trace = true;

执行需要分析的sql；
获取trace文件路径：select name, value from v$parameter where name like '%dump_dest%' 
转换文件格式： 
tkprof ora***.trc **.txt （此转换语句是在dos环境下执行，比如，tkprof D:\ems_ora_1344.trc  D:\zzz.txt         tkprof是Oracle的命令 ）

oracle11g版本取trace信息具体步骤：
用system用户登录数据库，依次执行下面的sql语句；
1、执行下面的语句，设置session会话：
alter session set events '10046 trace name context forever, level 12';
2、执行下面的语句，执行耗时的查询：
具体的select 查询语句
3、执行下面的语句，查询trace文件位置：
select name, value from v$parameter where name = 'user_dump_dest';
4、执行下面的语句，查询trace文件的文件名：
select value from v$diag_info where name = 'Default Trace File'

注意：这4条语句需要依次连续执行，整个过程中sql连接不能中断。
     如果出现sql连接中断，需要重新登录后从第一条语句重新依次执行。
     第2步的查询语句比较耗时的情况下，请根据环境情况选择较空闲的时机执行。

触发器和序列

#sqlplus /nolog
SQL> conn 实际库用户名/密码@数据库实例SID 

SELECT * FROM user_sequences WHERE sequence_name = '序列名';

select * from user_objects where object_name = '触发器名';

索引状态与索引重建

#sqlplus /nolog
SQL> conn 实际库用户名/密码@数据库实例SID 

1、查询分区索引的状态

select index_name, status
  from user_ind_partitions
 where index_name in (select index_name
                        from user_indexes
                       where table_name = '表名'
                         and partitioned = 'YES');

2、查询非分区索引的状态

select index_name,status from user_indexes where table_name = '表名' and partitioned = 'NO' ;

如果上述结果的status为不可用。说明索引失效，需要重建索引

3、重建索引

在空闲时间进行执行。避免增加环境压力。

--企业版支持在线重建索引--
alter index 索引名 rebuild online nologging;


--标准版不支持重建索引--
alter index 索引名 rebuild nologging;

手动缩小oracle数据库表空间

1、用oracle的系统dba用户system,sqlplus登录数据库，执行如下语句计算各表空间可缩小的范围。

select 'alter database datafile ''' || file_name || ''' resize ' || ceil( (nvl(hwm,1)*8192)/1024/1024 ) || 'm;' cmd

from dba_data_files a,    
( select file_id, max(block_id+blocks-1) hwm from dba_extents       group by file_id ) b

where a.file_id = b.file_id(+) and ceil(blocks*8192/1024/1024)-ceil((nvl(hwm,1)*8192)/1024/1024 ) > 0;

2、对步骤1中返回的结果CMD，即为可以缩小的所有表空间和可以缩小大小情况的操作语句。

3、依次执行步骤1中返回的结果CMD的语句，可对相应的表空间大小进行自动缩小。
   比如，执行 alter database datafile 'D:\DEVELOPMENT\ORACLE\ORADATA\UEP\SYSTEM01.DBF' resize 1193m; 即可自动缩小 SYSTEM01.DBF表空间大于1193m。

4、通过执行 select file_name,bytes FROM DBA_DATA_FILES 可以查看运行前后对比。

注意： 请在数据库空闲的时候执行该操作。该功能为alter数据库文件操作，请小心谨慎。