SQL語句調優：
CUUG：地址：
筆記：

調優目的是讓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');

在兩個資料庫之間統計資訊傳遞

SQL調優

相關文章