1. 测量SQL语句的性能
Tkprof(Trace Kernel Profile)工具的使用
功能：测量SQL语句的性能。
查看tkprof的不同参数 c:>tkprof[@more@]1. 测量SQL语句的性能
Tkprof(Trace Kernel Profile)工具的使用
功能：测量SQL语句的性能。
查看tkprof的不同参数 c:>tkprof

c:> tkprof D:oracleadminERPudumperp_ora_1436.trc bao.txt sys=no
explain=scott/tiger@erp
sys=no的含义：不包含递归SQL语句(即访问数据字典的隐含语句)

SQL语句的处理要经过三个阶段：Parse、Execute、Execute。
需要优化的SQL语句：
占用过多的CPU时间。
Parse、Execute、Execute阶段的时间太长。
从磁盘读太多的数据块，而从内存中读很少的数据块。
访问许多数据块，但只返回几条数据。

Top SQL 的使用
代替Tkprof。
首先找出哪些SQL语句的性能差，需要优化。
Top SQL 中的数据来源于V$SQL。

Top Sessions的使用

2. SQL语句的解释计划(EXPLAIN PLAN)
通过解释计划，可以找出SQL语句性能低的原因。

用图形界面来生成SQL语句的解释计划：
用SQL ScratchPad。

用命令来生成SQL语句的解释计划：
先检查sys用户下是否有plan_table表，如果没有，
执行d:oracleora92rdbmsadminutlxplan.sql脚本。

SQL>explain plan for SELECT e.empno, e.ename, d.deptno, d.loc
FROM scott.emp e, scott.dept d WHERE e.deptno = d.deptno;

查询执行计划：
SQL>select lpad(' ',4*(level-2)) || operation || ' ' || options || ' ' || object_name
"EXECUTION_PLAN" from plan_table start with id =0 connect by prior id = parent_id;

3．STATSPACK报告中的SQL语句性能
SQL ordered by Gets(按Gets排序的SQL语句)
SQL ordered by Reads(按Reads排序的SQL语句)
SQL ordered by Executions(按Executions排序的SQL语句)
SQL ordered by Parse Calls(按Parse Calls排序的SQL语句)

4. Oracle优化方式
优化方式：基于rule和cost.

基于rule时的优化等级：根据语法和表结构优化
1 Single row by rowid
2 Single row by cluster join
3 Single row by hash cluster key with unique or primary key
4 Single row by unique or primary key
5 Cluster join
6 Hash cluster key
7 Indexed cluster key
8 Composite key
9 Single-column indexes
10 Bounded range search on indexed columns
11 Unbounded range search on indexed columns
12 Sort-merge join
13 MAX or MIN of indexed column
14 ORDER BY on indexed columns
15 Full table scan
以 SELECT empno FROM emp WHERE ename = 'CHUNG' AND sal > 2000;语句为例说明访问路径 。分析应在哪个字段上创建索引？
缺点：小表的全表扫描比索引效率高，索引字段值的差异性小。

基于cost时的优化根据表和索引的统计信息优化，优先采用。
根据表和索引的统计信息包括：
每个表或索引的大小。
每个表或索引所包括的数据行数。
每个表或索引所使用的数据块数量。
每个表行的字节数。
索引字段值的差异性(基数)。

5. 统计信息的创建
SQL> ANALYZE TABLE employee COMPUTE STATISTICS;
SQL> ANALYZE INDEX employee_last_name_idx COMPUTE STATISTICS;

查询统计信息，可用图形界面或DBA_TABLES。

SQL> ANALYZE TABLE employee DELETE STATISTICS;

如果表或索引的数据量很大时，可以使用样本来创建统计信息：
SQL> ANALYZE TABLE employee ESTIMATE STATISTICS;
默认的样本大小为1064行。

SQL> ANALYZE TABLE employee ESTIMATE STATISTICS
SAMPLE 500 ROWS;
SQL> ANALYZE TABLE employee ESTIMATE STATISTICS
SAMPLE 35 PERCENT;

创建字段的统计信息：
SQL> ANALYZE TABLE employee ESTIMATE STATISTICS
FOR COLUMNS employee_id 500 size 200;
size的默认值是75。可以是1到254。

字段上的数据假设是正态分布。
直方图：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 employee
WHERE UPPER(first_name)=’SMITH’;

SQL> CREATE INDEX employee_first_name_upper_idx
ON employee(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、>、< 查询不会使用反键索引优化。

索引组织表(IOT)

分区表
簇:索引簇和散列簇。

8. 使用 SQL Analyze。
例子：SELECT department_name, department_id
FROM hr.departments
WHERE department_id NOT IN (SELECT department_id
FROM hr.employees)

使用Index Tuning Wizard。

第四课 调整共享池(Shared Pool)的性能

共享池由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)。

1. 监视共享池的性能
测量library cache的命中率：V$librarycache
SQL>select namespace,gethitratio,pinhitratio,reloads,invalidations
from v$libarycache
where namespace in (‘SQL AREA’,’TABLE/PROCEDURE’,’BODY’,’TRIGGER’);

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

GETS(语法分析)。
PINS(执行)。
RELOADS(SQL语句需要重新语法分析)、
INVALIDATIONS(例如视图重新编译)。

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内存的经验公式。

测量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。
(设置SGA内存大小的经验公式：
SGA = 55%物理内存
Shared pool = 45% SGA
Buffer cache = 45% SGA
Redo Log Cache = 10% SGA
)

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

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

调整共享池有关的init.ora参数
OPEN_CURSORS：建议值500。
CURSOR_SPACE_FOR_TIME：建议值TRUE。
SESSION_CACHED_CURSORS：建议值TRUE。
CURSOR_SHARING：默认值为EXACT。建议设成EXACT或FORCE。