SQL语句调优：
CUUG：地址：http://video.sina.com.cn/v/b/43540633-1820338845.html
笔记：

调优目的是让SQL的优化器选择最佳路径；而不是简单的调整SQL的语法。

优化器使用
计划稳定性

hint的使用
跟踪SQL语句
什么叫柱状图
COPY统计信息到其他数据库

优化器--人脑
rule-based 基于规则 -数据字典(10g淘汰）
cost-based 基于代价 -统计信息

计划稳定性的影响（怎么让执行计划更加稳定）
用户可以稳定执行计划,为了迫使应用程序使用所需的SQL访问路径。
一个一致的执行路径从而维护通过数据库更改。

影响：
新的oracle 版本
新对象统计信息
初始化参数改变
数据库重组
shema的改变

-----利用 stored outline 存储大纲-是一个容器，把执行计划放入这个容器
由于在共享池中的执行计划容易被干扰（default），就在内存中新建一个大纲中去。，当执行select的时候，去大纲中找，
如果找不到然后在去共享池去找；这样能够保证执行计划更加稳定，不受到一些因素干扰；

例子：
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出

SQL> conn scott/tiger
已连接。

SQL> create table emp1 as select * from emp;

表已创建。

SQL> SET AUTOTRACE TRACEONLY;
SQL>

QL> select * from emp1 where empno=7788;

未选定行

执行计划
---------------------------------------------------------
lan hash value: 2226897347

-------------------------------------------------------------------------
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
* 1 | TABLE ACCESS FULL| EMP1 | 1 | 87 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------

redicate Information (identified by operation id):
--------------------------------------------------

1 - filter("EMPNO"=7788)

ote
----
- dynamic sampling used for this statement (level=2)

统计信息
---------------------------------------------------------
168 recursive calls
0 db block gets
23 consistent gets
5 physical reads
0 redo size
696 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

QL>

现在是全表扫描，基于规则，由于没有统计信息；现在建立索引

SQL> create index emp1_empno_indx on emp1(empno);

索引已创建。

SQL> select * from emp1 where empno=7788;

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 3652624907

-----------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost
PU)| Time |
-----------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | 1 | 87 | 1
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP1_EMPNO_INDX | 1 | | 1
(0)| 00:00:01 |
-----------------------------------------------------------------------------
---------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPNO"=7788)

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
696 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>

现在是索引，由于表的结构变化了。

下面例子是建立容器，保证计划的稳定性

练习：
1、grant create any outline to scott；（sys）
2、exec DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES;(scott)
3、alter session set CREATE_STORED_OUTLINES=yanwei;
4、alter session set USE_STORED_OUTLINES=yanwei;
5、create or replace OUTLINE emp1_ename FOR CATEGORY yanwei ON
select * from emp1 where ename='SMITH';
6、select * from emp1 where ename='SMITH';
注意此时ename列没有索引

7、create index emp1_ename on emp1(ENAME);
8、select * from emp1 where ename='SMITH';
(注意此时ename列有索引，但是执行计划仍然为全表
9、如果想使用优化器上索引，必须适应hints；比如：
select * from emp1 where ename='SMITH'；

调优工具：
STATSPACK

EXPLAIN PLAN

SQL trace and TKPROF

SQL*Plus autotrace feature

Oracle SQL Analyze

explain plan
只产生执行计划，不执行，常用于开发初期
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan

Use script utlxplp.sql (Show parallel Query information)
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan

plan_table
–
Use script utlxpls.sql (Hide Parallel Query information)
–
Use script utlxplp.sql (Show parallel Query information)

Using SQL Trace and TKPROF
To use SQL trace and TKPROF:

Set the initialization parameters.

Alter session set SQL_Trace = true

SQL*Plus AUTOTRACE
?
Create the PLAN_TABLE table
?
Create and grant the Plustrace role
?
Autotrace syntax:
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> grant plustrace to scott;
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql SQL> grant plustrace to scott;
set autotrace [ off | on | traceonly ]
[ explain | statistics

Alter session set SQL_Trace = false

Format the trace file with TKPROF.

Interpret the output.
SQL> Explain plan for
2 select last_name from hr.employees;

表统计信息
的行数
块和空的块的数量
平均可用空闲空间
链接或迁移的行数
行平均长度
最后分析日期和样本量
数据字典视图:DBA_TABLES

如何做统计信息
GATHER_TABLE_STATS
–
GATHER_INDEX_STATS
–
GATHER_SCHEMA_STATS
–
GATHER_DATABASE_STATS
–
GATHER_STALE_STATS
Run the application.

索引统计信息
指数水平(高度)
叶块的数量和不同的钥匙
平均每个关键叶块的数量
平均每个关键数据块的数量
的索引条目数量
聚类系数
数据字典视图:DBA_INDEXES

列统计
不同值的数量
最小值、最大值(存储在原始二进制格式)
最后分析日期和样本量
数据字典视图:USER_TAB_COL_STATISTICS

柱状图
直方图描述一个特定的数据分布更详细地列。
他们给谓词选择性估计数据均匀分布。
你创建的直方图分析表……为列…命令
数据字典视图:DBA_HISTOGRAMS DBA_TAB_HISTOGRAMS

analyze table compute statistics
analyze table compute statistics for column产生柱状图
DBA_TAB_HISTOGRAMS

列值所在的比例

例子图片：

建立柱状图
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS
(‘HR',‘EMPLOYEES’, METHOD_OPT => 'FOR COLUMNS
SIZE 10 salary');

在两个数据库之间统计信息传递

ITPub博客

SQL调优

最新文章