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性能问题无从下手的小伙伴。