摘要：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;