ORACLE性能优化思路，相关命令和参数修改

ORACLE性能排查原因总结

前言

本文最主要是为遇到性能无从下手的小伙> 伴一个指导的方向，真正的优化过程，细致到分析oracle里面的每个等待事件，分析每个sql的执行计划，以及热点数据的分析等。内容就相当多了。

oracle的性能排查本人总结主要从三大方向入手

服务器资源占用情况
oracle的参数设置
awr报告分析

服务器资源

服务器资源这块，最主要的就是通过linux下的总体情况（top),线程队列(vmstat),内存(free -m)等去排查 cpu 内存磁盘网络等资源占用情况。这里不做详细展开。

oracle参数设置

Oracle数据库中processes,sessions,transactions分别代表进程数、会话数及当前最大允许的交易量。这三个参数的调整，用的最多，根据自己的实际业务并发量进行合理的设置这三个值。

按照官方文档中描述，他们之间是有关系的。

以下是11.2.0.4数据库版本

sessions=(1.5*processes)+22

transactions＝1.1*sessions

在10g版本中

sessions=(1.1*processes) +5

transactions=1.1*sessions

查看processes和sessions参数

SQL> show parameter processes
NAME                                  TYPE         VALUE
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     50

SQL> show parameter sessions
NAME                                  TYPE         VALUE
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
sessions                             integer     60
shared_server_sessions               integer     10

修改processes和sessions值

SQL> alter system set processes=300 scope=spfile;
系统已更改。
SQL> alter system set sessions=335 scope=spfile;
系统已更改。

注意：修改processes和sessions值必须重启oracle服务器才能生效

awr报告分析

这里给出oracle awr报告导出的步骤，具体awr报告的分析过程，由于内容较多，将在后面的>章节单独介绍，如何分析和查看awr报告。

1、进入数据库 sqlplus / as sysdba
2、查看用户 show parameter db_name
3、开始压测后执行 exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
4、结束压测后执行 exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
5、输入命令 @?/rdbms/admin/awrrpt

备注：4和5步骤中不执行，后面默认产生系统规定的时间节点（默认人一小时生成一次报告）

6、输入回车默认是html格式
7、输入1，查询最近一天的
8、输入开始节点和结束节点
9、输入想要生成的报告名称，以html格式结尾
10、生成的awr默认在登陆数据库的路径（试验机：C:\Users\wangxh.PANACRO）

其他常用命令

查询数据库当前进程的连接数：

select count(*) from v$process;

查看数据库当前会话的连接数：

select count(*) from v$session;

查看数据库的并发连接数：

select count(*) from v$session where status='ACTIVE';

查看当前数据库建立的会话情况：

select sid,serial#,username,program,machine,status from v$session;

查询数据库允许的最大连接数：

select value from v$parameter where name = 'processes';

或者

show parameter processes;

查看是否有锁表

SELECT a.username,decode(b.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) LOCK_LEVEL,
c.owner,c.object_name,c.object_type,
a.sid,a.serial#,a.terminal,a.machine,a.program,a.osuser
FROM v$session a,v$lock b,dba_objects c
WHERE b.sid = a.sid
AND b.id1 = c.object_id(+)
AND a.username is NOT Null;

查询表空间使用情况

使用DBA权限登陆

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') 
"使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

结果：

表空间名	表空间大小(M)	已使用空间(M)	使用比	空闲空间(M)	最大块(M)
CSP	30000	29947.37	99.82	52.63	9
SYSTEM	500	491.19	98.24	8.81	7.94
SYSAUX	440	424.31	96.43	15.69	7.94
USERS	5	0.44	8.80	4.56	4.56
UNDOTBS1	22415	190.19	0.85	22224.81	3968
发现表空间只有 52.63 M的空闲,猜测可能是表空间自动扩展失败的问题(表空间的增长量太高，ORACLE默认是50%),

修改表空间文件扩展方式:

SQL>ALTER DATABASE 
    DATAFILE '/u01/oracle/oradata/orcl/ccen01.dbf' AUTOEXTEND 
    ON NEXT 50M MAXSIZE UNLIMITED

总结

oracle提供了丰富的统计信息，包括会话，sql执行，事件等统计视图，可以通过这些视图进行oracle执行情况。本文皆旨在给遇到oracle性能问题无从下手的小伙伴。