Oracle10g优化

直方图：SQL> ANALYZE TABLE finaid COMPUTE STATISTICS FOR COLUMN award SIZE 100;

用图形界面创建统计信息。

优化提示：SQL> SELECT /*+ FIRST_ROWS */ * FROM hr.employees;

其它优化提示有：RULE、FULL SALES(访问SALES表)、 INDEX SALES_ID_PK、PARALLEL。

6. 设置优化模式

init.ora参数OPTIMIZER_MODE：

CHOOSE、RULE、FIRST_ROWS(提高响应时间)、ALL_ROWS(提高吞吐量)。

7. 索引

B-树索引: 适合建在重复值少的字段。

索引的统计信息，索引B-树的高度(建议<4)。

SQL> ANALYZE INDEX employee_last_name_idx VALIDATE STRUCTURE;

SQL> SELECT (DEL_LF_ROWS_LEN/ LF_ROWS_LEN) * 100 “Wasted Space” FROM index_stats WHERE NAME= “EMPLOYEE_LAST_NAME_IDX” ; 建议：索引的空闲空间(<20)。

重组索引：

SQL> alter index scott.pk_dept rebuild online; SQL> alter index scott.pk_dept coalesce;

压缩B树索引：适合于索引字段重复值多的情况

SQL>ALTER INDEX employee_last_name_idx REBUILD COMPRESS;

位图(bitmap)索引：适合建在于重复值多的字段。

位图索引不适合于建在频繁进行insert、update和delete的表上。这些操作的性能代价太高。位图索引适合于数据仓库和DSS。

优化位图索引的init.ora参数：SORT_AREA_SIZE、PGA_AGGREGATE_TARGET。

淘汰的init.ora参数：CREATE_BITMAP_AREA_SIZE、BITMAP_MERGE_AREA_SIZE。

函数索引

必须要把init.ora参数QUERY_REWRITE_ENABLE设成TRUE，才能

创建函数索引。

SQL>SELECT last_name,first_name FROM employees WHERE UPPER(first_name)=?SMITH?;

SQL> CREATE INDEX hr.employee_first_name_upper_idx ON hr.employees(UPPER(first_name));

SQL> SELECT * FROM sales where (price * units) > 10000; SQL> CREATE INDEX sales_total_sale_idx ON sales (price * units) TABLESPACE INDX;

反键索引：适用于序列字段。

反键索引只适用于=和!=查询。使用Between、>、< 查询不会使用反键索引优化。

8. 优化应用程序

索引组织表(IOT) 分区表

簇:索引簇和散列簇。

9. 使用 SQL Analyze(对9i适用)

例子：SELECT department_name, department_id

FROM hr.departments WHERE department_id NOT IN (SELECT department_id FROM hr.employees)

使用Index Tuning Wizard。

10. 并行查询

并行查询可以优化：表扫描、join语句、sort、not in、group by、select distinct、union and union all 、aggregation、PL/SQL functions 、order by 、create table as select 、create index 、rebuild index 、insert ... select、enable constraint、cube、rollup。 用在多CPU和磁盘阵列。 表和索引的并行度。

ALTER TABLE emp PARALLEL 4; ALTER INDEX iemp PARALLEL;

SELECT /*+ PARALLEL(emp,4) */ COUNT(*) FROM emp ;

调整开发系统和生产系统。

应用程序、SQL、内存、I/O、锁定、OS。

第三章 调整共享池(Shared Pool)的性能

1. 监视共享池的性能

共享池由library cache和dictionary cache组成。采用LRU(Least Recently Used)算法管理。library cache用于缓存执行的SQL语句和PL/SQL程序。dictionary cache用于缓存数据字典。

共享池有关的数据字典：V$SQL、V$SQLAREA、V$SQLTEXT、V$SQL_PLAN。(V$session)。

测量library cache的命中率：V$librarycache

SQL>select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache where namespace in (?SQL AREA?,?TABLE/PROCEDURE?,?BODY?,?TRIGGER?);

SQL AREA部分的gethitratio 、pinhitratio要 > 90%。

GETS(语法分析)。 PINS(执行)。

RELOADS(SQL语句需要重新语法分析)、

INVALIDATIONS(SQL语句所引用的表结构发生变化，或视图重新编译)。

select SUM(reloads)/SUM(pins) “Reload Ratio” from V$librarycache;

重新装载率Reload Ratio要 < 1%。

使用STATSPACK来监视library cache。 使用REPORT.TXT来监视library cache。

使用Performance Manager(内存、数据库例程、SQL)来监视library cache。

SGA内存的经验公式。 SGA= 55% 物理内存 Shared Pool = 45% SGA Buffer cache = 45% SGA Redo Log Cache = 10% SGA

测量dictionary cache的命中率：V$rowcache

select 1- (SUM(getmisses)/SUM(gets)) “Data Dictionary Hit Ratio” from V$rowcache;

“Data Dictionary Hit Ratio”的值要 > 85%。 使用STATSPACK来监视dictionary cache。 使用REPORT.TXT来监视dictionary cache。

2. 提高共享池性能的方法

加大共享池的大小：init.ora参数shared_pool_size(动态参数)。注意参

数sga_max_size。

为大型PL/SQL程序设置保留内存：防止其它SQL语句从内存中移走。 init.ora参数SHARED_POOL_RESERVED_SIZE(建议值：10% shared_pool_size)。

销定(Pin)程序：DBMS_SHARED_POOL.KEEP(‘deposit’)。 鼓励代码重用：在SQL语句中使用变量。

例如：SELECT * FROM EMP WHERE ename = ?Smith?; SELECT * FROM EMP WHERE ename= ?John?; 改写为：

v_ename = ?Smith?;

Select * from emp where ename =v_ename;

v_ename = ?John?;

Select * from emp where ename =v_ename;

调整共享池有关的init.ora参数 OPEN_CURSORS：建议值500。

CURSOR_SPACE_FOR_TIME：建议值TRUE。 SESSION_CACHED_CURSORS：建议值TRUE。

CURSOR_SHARING：默认值为EXACT。建议设成SIMILAR或FORCE。

第四章 调整缓冲区高速缓存(Buffer Cache)的性能

1. Buffer Cache的工作原理

Buffer Cache由数据块组成。

LRU列表：MRU ??????. LRU。(全表扫描FTS放在LRU端。) 缓冲区块的状态：Free、Pinned、Clean、Dirty。 Dirty List或Write List(写列表)。

数据库写进程DBW0将缓冲区高速缓存中的数据写到数据文件中。

2. 测量Buffer Cache的性能

测量Buffer Cache的命中率： SQL> select 1-((physical.value – direct.value – lobs.value)/logical.value) “Buffer Cache Hit Ratio” from V$SYSSTAT physical, V$SYSSTAT direct,V$SYSSTAT lobs, V$SYSSTAT logical where physical.name = ?physical reads?

And direct.name = ?physical reads direct? and lobs.name = ?physical reads direct (lob)? And logical.name = ?session logical reads?; “Buffer Cache Hit Ratio”的值要 > 90%。