Oracle数据库性能调优实践(一)——概述

摘要：Oracle数据库应用系统的调优主要包括八个方面：1、优化连接数/会话数；2、优化数据库内存；3、优化SQL语句；4、优化索引；5、优化磁盘I/O；6、优化数据存储；7、优化操作系统环境；8、定期生成数据库对象使用状态的统计信息。数据库性能调优的实质就是优化内存、降低CPU负载、改善I/O性能。详细内容请看下文。

1、优化连接数/会话数

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

SQL> select count(*) from v$process;

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

SQL> select count(*) from v$session;

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

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

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

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

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

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

如果需要修改数据库允许的最大连接数，执行alter指令：

SQL> alter system set processes = 1200 scope = spfile;

(注意：执行alter语句并commit后，需要重启数据库才能实现连接数的修改操作生效。)

2、优化数据库内存

对Oracle数据库来说，Oracle 实例= 内存结构 + 进程结构，而内存结构 = SGA + PGA。SGA(系统全局区)是用户存储数据库信息的内存区，该区域为数据库进程所共享。它包含服务器的数据和控制信息，主要包含高速数据缓冲区、共享池、重做日志缓存区、Java池，大型池等内存结构。SGA的设置，理论上SGA的大小应该占OS的内存的 1/3-1/2左右。SGA + PGA + OS使用的内存 < 服务器中的物理内存。

查看当前系统SGA的信息的指令为：

SQL> select name,bytes/1024/1024 as "Size(M)" from v$sgainfo;

备注：根据查询信息显示当前还有10240M可用的SGA内存，系统当前的内存配置还是比较充足的。

不过，我们在实际使用过程中还是可以根据实际需求重新分配内存。

增大系统全局区：

SQL> alter system set sga_max_size=12000m scope=spfile;

增大数据缓存区：

SQL> alter system set db_cache_size=7000m scope=spfile;

增大共享内存区：

SQL> alter system set shared_pool_size=3200m scope=spfile;

增大程序全局区：

SQL> alter system set pga_aggregate_target=5000m scope=spfile;

增大排序区：

SQL> alter system set sort_area_size=3000m scope=spfile;

(注意：执行alter语句并commit后，需要重启数据库才能实现连接数的修改操作生效。)

3、优化SQL语句

SQL 调优的目标是简单的：第一、消除不必要的大表全表搜索，不必要的全表搜索导致大量不必要的 I/O ，从而拖慢整个数据库的性能。第二、确保最优的索引使用 ，对于改善查询的速度，这是特别重要的。有时 Oracle 可以选择多个索引来进行查询，必须检查每个索引并且确保 Oracle 使用正确的索引。第三、确保最优的 JOIN 操作：有些查询使用nested loop join嵌套循环连接快一些，有些则hash join散列连接快一些，另外一些则是sort merge join排序合并连接更快。这三个调优规则看来简单，不过它们占 SQL 调优任务的 90%，需要深入学习 。

4、优化索引

数据库索引是建立在数据表的一列或多个列上的辅助对象，目的是加快访问表中的数据。索引是数据库维护的可选结构，比较难的是怎么准确地判断在什么地方需要使用索引，使用索引有利于调节检索速度。当建立一个索引时，必须指定用于跟踪的表名以及一个或多个表列。一旦建立了索引，在用户表中建立、更改和删除数据库时，数据库就自动地维护索引。如果需要创建索引，请参考下列三个准则：第一、索引应该在SQL语句的"where"或"and"部分涉及的表列被建立。第二、创建索引具有一定范围的表列，这里有一个大致的原则，如果表中列的值占该表中行的20%以内，这个表列就可以作为候选索引表列。第三、如果在SQL语句中多个表列被一起连续引用，则应该考虑将这些表列一起放在一个索引内，数据库将维护单个表列的索引(建立在单一表列上)或复合索引(建立在多个表列上)。

怎么监控无用的索引，如果在一段时间内，发现没有被使用的索引，一般就是无用的索引。其查询指令为：

开始监控：

SQL> alter index index_name monitoring usage;

检查使用状态：

SQL> select * from v$object_usage;

停止监控：

SQL> alter index index_name nomonitoring usage;

5、优化磁盘I/O

使用指令查看数据文件的I/O：

SQL> SELECT NAME,PHYRDS,PHYWRTS FROM V$DATAFILE DF,V$FILESTAT FS WHERE DF.FILE#=FS.FILE# order by readtim desc;

用以下查询语句可以得到各表空间读写次数，phyrds+phywrts 即是磁盘I/O量。

select name,phyrds,phywrts from v$datafile,v$filestat where v$datafile.file# = v$filestat.file# order by readtim desc;

说明：如果发现在磁盘上的写入和读取次数上出现很大的差别，就表明肯定有哪个磁盘负载过多。出现磁盘负载不平衡，这时可以通过移动数据文件来均衡文件I/O：

SQL>alter tablespace tablespace_name offline;

$cp /disk1/test.dbf /disk2/test.dbf;

SQL>alter tablespace tablespace_name rename datafile '/disk1/test.dbf' to '/disk2/test.dbf';

SQL>alter tablespace tablespace online;

$rm /disk1/test.dbf

6、优化数据存储

查询数据库中各个表的实际数据存储使用情况的语句：

SQL> select segment_name, sum(bytes)/1024/1024 MB from user_extents u group by segment_name;

查询表空间对应的存储文件的语句：

SQL> select tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_space from sys.dba_data_files order by tablespace_name;

说明：可以通过扩展表空间对应存储文件的方式扩展表空间。其扩容语句为 alter database datafile '表空间位置' resize 新的容量。

7、优化操作系统环境

操作系统优化时应该考虑的因素有：内存的使用；CPU的使用；IO级别；网络流量等。各个因素互相影响，正确的优化次序是内存、IO、CPU、网络流量。操作系统使用了虚拟内存的概念，虚拟内存使每个应用感觉自己是使用内存的唯一的应用，每个应用都看到地址从0开始的单独的一块内存，虚拟内存被分成4K或8K的page，操作系统通过MMU(memory management unit)管理单元将这些page与物理内存进行映射。

8、定期生成数据库运行状况统计信息

从ORACLE 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。

这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启。

调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。

可以通过以下查询这个JOB的运行情况：

SELECT * FROM Dba_Scheduler_Jobs WHERE Job_Name = 'GATHER_STATS_JOB';

可以通过下面语句查看JOB任务：

SQL> SELECT Job_Name, Last_Start_Date FROM Dba_Scheduler_Jobs;

说明：关闭及开启自动搜集功能，有两种方法，分别如下：

方法一：exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');

方法二：alter system set "_optimizer_autostats_job"=false scope=spfile;

alter system set "_optimizer_autostats_job"=true scope=spfile;