一、       磁盘方面调优

 

1.      规范磁盘阵列

RAID 10比RAID5更适用于OLTP系统，RAID10先镜像磁盘，再对其进行分段，由于对数据的小规模访问会比较频繁，所以对OLTP适用。而RAID5,优势在于能够充分利用磁盘空间，并且减少阵列的总成本。但是由于阵列发出一个写入请求时，必须改变磁盘上已修改的块，需要从磁盘上读取“奇偶校验”块，并且使用已修改的块计算新的奇偶校验块，然后把数据写入磁盘，且会限制吞吐量。对性能有所影响，RAID5适用于OLAP系统。

 

2.      数据文件分布

分离下面的东西,避免磁盘竞争

Ø  SYSTEM表空间

Ø  TEMPORARY表空间

Ø  UNDO表空间

Ø  联机重做日志（放在最快的磁盘上）

Ø  操作系统磁盘

Ø  ORACLE安装目录

Ø  经常被访问的数据文件

Ø  索引表空间

Ø  归档区域（应该总是与将要恢复的数据分离）

例：

²  /: System

²  /u01: Oracle Software

²  /u02: Temporary tablespace, Control file1

²  /u03: Undo Segments, Control file2

²  /u04: Redo logs, Archive logs, Control file4

²  /u05: System, SYSAUX tablespaces

²  /u06: Data1 ,control file3

²  /u07: Index tablespace

²  /u08: Data2

通过下列语句查询确定IO问题

select name ,phyrds,phywrts,readtim,writetim

from v$filestat a,v$datafile b

where a.file#=b.file# order by readtim desc;

 

 

3.      增大日志文件

 

u  增大日志文件的大小，从而增加处理大型INSERT,DELETE,UPDATE操作的比例

查询日志文件状态

select a.member,b.* from v$logfile a,v$log b where a.GROUP#=b.GROUP#

查询日志切换时间

select b.RECID,to_char(b.FIRST_TIME,'yyyy-mm-dd hh34:mi:ss') start_time,a.RECID,to_char(a.FIRST_TIME,'yyyy-mm-dd hh34:mi:ss') end_time,round(((a.FIRST_TIME-b.FIRST_TIME)*25)*60,2) minutes

from v$log_history a ,v$log_history b

where a.RECID=b.RECID+1

order by a.FIRST_TIME desc

 

增大日志文件大小，以及对每组增加日志文件（一个主文件、一个多路利用文件）

u  增大LOG_CHECKPOINT_INTERVAL参数，现已不提倡使用它

如果低于每半小时切换一次日志，就增大联机重做日志大小。如果处理大型批处理任务时频繁进行切换，就增大联机重做日志数目。

alter database add logfile member ‘/log.ora’ to group 1;

alter database drop logfile member ‘/log.ora’;

 

4.      UNDO表空间

修改三个初始参数：

UNDO_MANAGEMENT=AUTO

UNDO_TABLESPACE=CLOUDSEA_UNDO

UNDO_RETENTION=<#of minutes>

 

5.      不要在系统表空间中执行排序

 

 

二、       初始化参数调优

32位的寻址最大支持应该是2的32次方，就是4G大小。但实际中32位系统（XP，windows2003等MS32位系统, ubuntu等linux32 位系统）要能利用4G内存，都是采用内存重映射技术。需要主板及系统的支持。如果关闭主板BIOS的重映射功能，系统将不能利用4G内存，可能只达3.5G.而在windows下看到的一般为3.25G。所以SGA设置为内存的40%，但不能超过3.25G

1.      重要初始化参数

l  SGA_MAX_SIZE

l  SGA_TARGET

l  PGA_AGGREGATE_TARGET

l  DB_CACHE_SIZE

l  SHARED_POOL_SIZE

 

2.      调整DB_CACHE_SIZE来提高性能

它设定了用来存储和处理内存中数据的SGA区域大小，从内存中取数据比磁盘快10000倍以上

根据以下查询出数据缓存命中率

select sum(decode(name,'physical reads',value,0)) phys,

  sum(decode(name,'db block gets',value,0)) gets,

  sum(decode(name,'consistent gets',value,0)) con_gets,

  (1- (sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)) ) ))*100 Hitratio

from v$sysstat;

一个事务处理程序应该保证得到95%以上的命中率，命中率从90%提高到98%可能会提高500%的性能，ORACLE正在通过CPU或服务时间与等待时间来分析系统性能，不太重视命中率，不过现在的库缓存和字典缓存仍将命中率作为基本的调整方法。

 

在调整DB_CACHE_SIZE时使用V$DB_CACHE_ADVICE

select size_for_estimate, estd_physical_read_factor, estd_physical_reads

 from v$db_cache_advice

where name = 'DEFAULT';

 

如果查询的命中率过低，说明缺少索引或者索引受到限制，通过V$SQLAREA视图查询执行缓慢的SQL

 

3.      设定DB_BLOCK_SIZE来反映数据读取量大小

OLTP一般8K

OLAP一般16K或者32K

 

4.      调整SHARED_POOL_SIZE以优化性能

 

正确地调整此参数可以同等可能地共享SQL语句，使得在内存中便能找到使用过的SQL语句。为了减少硬解析次数，优化对共享SQL区域的使用，需尽量使用存储过程、使用绑定变量

 

保证数据字典缓存命中率在95%以上

select ((1- sum(getmisses)/(sum(gets)+sum(getmisses)))*100) hitratio

from v$rowcache

where gets+getmisses <>0;

 

如果命中率小于 99％，就可以考虑增加shared pool 以提高library cache 的命中率

 

SELECT SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",1 - SUM(RELOADS)/SUM(PINS)

FROM V$LIBRARYCACHE;

 

通常规则是把它定为DB_CACHE_SIZE大小的50%－150%，在使用了大量存储过程或程序包，但只有有限内存的系统里，最后分配为150%。在没有使用存储过程但大量分配内存给DB_CACHE_SIZE的系统里，这个参数应该为10%－20%

 

5.      调整PGA_AGGREGATE_TARGET以优化对内存的应用

u  OLTP ：totalmemory*80%*20%

u  DSS:   totalmemory*80%*50%

 

6.      25个重要初始化参数

²  DB_CACHE_SIZE：分配给数据缓存的初始化内存

²  SGA_TARGET：使用了自动内存管理，则设置此参数。设置为0可禁用它

²  PGA_AGGREGATE_TARGET：所有用户PGA软内存最大值

²  SHARED_POOL_SIZE：分配给数据字典、SQL和PL／SQL的内存

²  SGA_MAX_SIZE：SGA可动态增长的最大内存

²  OPTIMIZER_MODE：

²  CURSOR_SHARING：把字面SQL转换成带绑定变更的SQL，可减少硬解析开销

²  OPTIMIZER_INDEX_COST_ADJ：索引扫描成本和全表扫描成本进行调整，设定在1－10间会强制频繁地使用索引，保证索引可用性

²  QUERY_REWRITE_ENABLED：用于启用具体化视图和基于函数的索引功能

²  DB_FILE_MULTIBLOCK_READ_COUNT：对于全表扫描，为了更有效执行IO，此参数可在一次IO中读取多个块

²  LOG_BUFFER：为内存中没有提交的事务分配缓冲区（非动态参数）

²  DB_KEEP_CACHE_SIZE：分配给KEEP池或者额外数据缓存的内存

²  DB_RECYCLE_CACHE_SIZE：

²  DBWR_IO_SLAVES：如果没有异步IO，参数等同于DB_WRITER_PROCESSES模拟异步IO而分配的从SGA到磁盘的写入器数。如果有异步IO，则使用DB_WRITER_PROCESSES设置多个写程序，在DBWR期间更快地写出脏块

²  LARGE_POOL_SIZE：分配给大型PLSQL或其他一些很少使用的ORACLE选项LARGET池的总块数

²  STATISTICS_LEVEL：启用顾问信息，并可选择提供更多OS统计信息来改进优化器决策。默认：TYPICAL

²  JAVA_POOL_SIZE：为JVM使用的JAVA存储过程所分配的内存

²  JAVA_MAX_SESSIONSPACE_SIZE：跟踪JAVA类的用户会话状态所用内存上限

²  MAX_SHARED_SERVERS：当使用共享服务器时的共享服务器上限

²  WORKAREA_SIZE_POLICY：启用PGA大小自动管理

²  FAST_START_MTTR_TARGET：完成一次崩溃恢复的大概时间／S

²  LOG_CHECKPOINT_INTERVAL：检查点频率

²  OPEN_CURSORS：指定了保存用户语句的专用区域大小，如此设置过高会导致ORA-4031

²  DB_BLOCK_SIZE：数据库默认块大小

²  OPTIMIZER_DYNAMIC_SAMPLING：控制动态抽样查询读取的块数量，对正在使用全局临时表的系统非常有用

 

 

三、       SQL调优

1.      使用提示

                   1.1              改变执行路径

通过OPTIMIZER_MODE参数指定优化器使用方法，默认ALL_ROWS

Ø  ALL_ROWS 可得最佳吞吐量执行查询所有行

Ø  FIRST_ROWS(n) 可使优化器最快检索出第一行:

 select  /*+ FIRST_ROWS(1) */  store_id,…  from  tbl_store

 

                   1.2              使用访问方法提示

允许开发人员改变访问的实际查询方式，经常使用INDEX提示

Ø  CLUSTER 强制使用集群

Ø  FULL

Ø  HASH

Ø  INDEX  语法：/*+ INDEX (TABLE INDEX1,INDEX2….) */ COLUMN 1,….

当不指定任何INDEX时，优化器会选择最佳的索引

SELECT /*+ INDEX */ STORE_ID FROM TBL_STORE

Ø  INDEX_ASC 8I开始默认是升序，所以与INDEX同效

Ø  INDEX_DESC

Ø  INDEX_COMBINE  用来指定多个位图索引，而不是选择其中最好的索引

Ø  INDEX_JOIN  只需访问这些索引，节省了重新检索表的时间

Ø  INDEX_FFS  执行一次索引的快速全局扫描，只处理索引，不访问具体表

Ø  INDEX_SS 

Ø  INDEX_SSX_ASC

Ø  INDEX_SS_DESC

Ø  NO_INDEX

Ø  NO_INDEX_FFS

Ø  NO_INDEX_SS

                   1.3              使用查询转换提示

对于数据仓库非常有帮助

Ø  FACT

Ø  MERGE

Ø  NO_EXPAND 语法：/*+ NO_EXPAND */ column1,…

保证OR组合起的IN列表不会陷入困境，/*+ FIRST_ROWS NO_EXPAND */

Ø  NO_FACT

Ø  NO_MERGE

Ø  NO_QUERY_TRANSFORMATION

Ø  NO_REWRITE

Ø  NO_STAR_TRANSFORMATION

Ø  NO_UNSET

Ø  REWRITE

Ø  STAR_TRANSFORMATION

Ø  UNSET

Ø  USE_CONCAT

 

                   1.4              使用连接操作提示

显示如何将连接表中的数据合并在一起，可用两提示直接影响连接顺序。LEADING指定连接顺序首先使用的表，ORDERED告诉优化器基于FROM子句中的表顺序连接这些表，并使用第一个表作为驱动表（最行访问的表）

ORDERED语法：/*+ ORDERED */ column 1,….

访问表顺序根据FROM后的表顺序来

LEADING语法：/*+ LEADING(TABLE1) */ column 1,….

类似于ORDER，指定驱动表

Ø  NO_USE_HASH

Ø  NO_USE_MERGE

Ø  NO_USE_NL

Ø  USE_HASH前提足够的HASH_AREA_SIZE或PGA_AGGREGATE_TARGET

通常可以为较大的结果集提供最佳的响应时间

Ø  USE_MERGE

Ø  USE_NL  通常可以以最快速度返回一个行

Ø  USE_NL_WITH_INDEX

 

                   1.5              使用并行执行

Ø  NO_PARALLEL

Ø  NO_PARALLEL_INDEX

Ø  PARALLEL

Ø  PARALLEL_INDEX

Ø  PQ_DISTRIBUTE

 

                   1.6              其他提示

Ø  APPEND 不会检查当前所用块中是否有剩余空间，而直接插入到表中，会直接将数据添加到新的块中。

Ø  CACHE 会将全表扫描全部缓存到内存中，这样可直接在内存中找到数据，不用在磁盘上查询

Ø  CURSOR_SHARING_EXACT

Ø  DRIVING_SITE

Ø  DYNAMIC_SAMPLING

Ø  MODEL_MIN_ANALYSIS

Ø  NOAPPEND

Ø  NOCACHE

Ø  NO_PUSH_PRED

Ø  NO_PUSH_SUBQ

Ø  NO_PX_JOIN_FILTER

Ø  PUSH_PRED

Ø  PUSH_SUBQ   强制先执行子查询，当子查询很快返回少量行时，这些行可以用于限制外部查询返回行数，可极大地提高性能

例：select /*+PUSH_SUBQ */ emp.empno,emp.ename

    From emp,orders

where emp.deptno=(select deptno from dept where loc=’1’)

Ø  PX_JOIN_FILTER

Ø  QB_NAME

 

2.      调整查询

 

2.1              在V$SQLAREA中选出最占用资源的查询

 

HASH_VALUE：SQL语句的Hash值。

ADDRESS：SQL语句在SGA中的地址。

PARSING_USER_ID：为语句解析第一条CURSOR的用户

VERSION_COUNT：语句cursor的数量

KEPT_VERSIONS：

SHARABLE_MEMORY：cursor使用的共享内存总数

PERSISTENT_MEMORY：cursor使用的常驻内存总数

RUNTIME_MEMORY：cursor使用的运行时内存总数。

SQL_TEXT：SQL语句的文本（最大只能保存该语句的前1000个字符）。

MODULE,ACTION：用了DBMS_APPLICATION_INFO时session解析第一条cursor时信息

SORTS: 语句的排序数

CPU_TIME: 语句被解析和执行的CPU时间

ELAPSED_TIME: 语句被解析和执行的共用时间

PARSE_CALLS: 语句的解析调用(软、硬)次数

EXECUTIONS: 语句的执行次数

INVALIDATIONS: 语句的cursor失效次数

LOADS: 语句载入(载出)数量

ROWS_PROCESSED: 语句返回的列总数

select b.username,a.DISK_READS,a.EXECUTIONS,a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rds_exec_ratio,a.SQL_TEXT

from v$sqlarea a ,dba_users b

where a.PARSING_USER_ID=b.user_id and a.DISK_READS>100 order by a.DISK_READS desc;

 

2.2              在V$SQL中选出最占用资源的查询

与V$SQLAREA类似

select * from

(select sql_text,rank() over (order by buffer_gets desc) as rank_buffers,to_char(100*ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sql)

where rank_buffers <11

 

2.3              确定何时使用索引

²  当查询条件只需要返回很少的行（受限列）时，则需要建立索引，不同的版本中这个返回要求不同

V5:20%  V7:7%  V8i,V9i:4%  V10g: 5%

查看表上的索引

select  a.table_name,a.index_name,a.column_name,a.column_position,a.table_owner

from dba_ind_columns a

where a.table_owner='CLOUDSEA'

 

²  修正差的索引，可使用提示来限制很差的索引，如INDEX，FULL提示

²  在SELECT 和WHERE中的列使用索引

如: select name from tbl where no=?

建立索引：create index test on tbl(name,no) tablespace cloudsea_index storage(….)

对于系统中很关键的查询，可以考虑建立此类连接索引

 

²  在一个表中有多个索引时可能出现麻烦，使用提示INDEX指定使用索引

²  使用索引合并，使用提示INDEX_JOIN

²  基于函数索引，由于使用了函数造成查询很慢.必须基于成本的优化模式,参数：

QUERY_REWRITE_ENALED=TRUE

QUERY_REWRITE_INTEGRITY=TRUSTED (OR ENFORCED)

create index test on sum(test);

 

 

2.4              在内存中缓存表

将常用的相对小的表缓存到内存中，但注意会影响到嵌套循环连接上的驱动表

alter table tablename cache;

 

2.5          使用EXISTS 与嵌套子查询 代替IN

 SELECT …FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT    WHERE DEPT_CAT=’A’);

(方法一: 高效)
SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A’

(方法二: 最高效)
SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);

 

 

四、       使用STATSPACK和AWR报表调整等待和闩锁

1.      10GR2里的脚本

在$ORACLE_HOME/RDBMS/ADMIN下

 

Spcreate.sql  通过调用spcusr.sql spctab.sql 和spcpkg.sql创建STATSPACK环境，使用SYSDBA运行它

Spdrop.sql   调用sptab.sql和spdusr.sql删除整个STATSPACK环境，使用SYSDBA运行它

Spreport.sql  这是生成报表的主要脚本，由PERFSTAT用户运行

Sprepins.sql   为指定的数据库和实例生成实例报表

Sprepsql.sql   为指定的SQL散列值生成SQL报表

Sprsqins.sql   为指定的数据库和实例生成SQL报表

Spauto.sql    使用DBMS_JOB自动进行统计数据收集（照相）

Sprepcon.sql  配置SQLPLUS变量来设置像阈值这样的内容的配置文件

Spurge.sql    删除给定数据库实例一定范围内的快照ID，不删除基线快照

Sptrunc.sql   截短STATSPACK表里所有性能数据

 

 

 

五、       执行快速系统检查

1.      缓冲区命中率

查询缓冲区命中率