講最佳化時大致寫的一個提綱，內容分db的物理設計和邏輯設計，主要以物理設計為主，邏輯設計介紹的內容不多，提綱裡把物理結構設計和例項最佳化有機的結合在一起，把邏輯結構設計和應用調整結合在一起......[@more@]

Oracle效能調整指導綱要

資料庫物理結構設計和例項級別的調整

一、Oracle效能最佳化方法論

1．為什麼（what）要最佳化（系統慢了？慢是表象）

2．怎樣（how）最佳化？（需要找到慢的原因）

a．是系統的問題？

b．是資料庫的問題？

3．誰（who）來最佳化？

a．系統架構師（系統架構設計的有問題，麻煩大了）

b．系統管理員（os、storage問題）

c．資料庫管理員（db問題）

d．應用程式設計人員（應用設計問題）

e．應用程式開發人員（程式碼書寫問題）

是誰的問題由誰來承擔，找到理由拒絕讓他們說不…

4．什麼時候（when）最佳化？（最佳化時是否需要停應用：7×24）

5．制定相應的調整策略

6．組建調整的人員

7．開始調整

8．觀察效能的變化

9．再調整再觀察，直到效能可以接受

10． 調整結束，撰寫調整報告，開會總結，分享經驗

二、 oracle是如何工作的（How Oracle Works）

How Oracle Works
The following example describes the most basic level of operations that Oracle performs. This illustrates an Oracle configuration where the user and associated server process are on separate computers (connected through a network).
An instance has started on the computer running Oracle (often called the host or database server).
A computer running an application (a local computer or client workstation) runs the application in a user process. The client application attempts to establish a connection to the server using the proper Oracle Net Services driver.
The server is running the proper Oracle Net Services driver. The server detects the connection request from the application and creates a dedicated server process on behalf of the user process.
The user runs a SQL statement and commits the transaction. For example, the user changes a name in a row of a table.
The server process receives the statement and checks the shared pool for any shared SQL area that contains a similar SQL statement. If a shared SQL area is found, then the server process checks the user's access privileges to the requested data, and the previously existing shared SQL area is used to process the statement. If not, then a new shared SQL area is allocated for the statement, so it can be parsed and processed.
The server process retrieves any necessary data values from the actual datafile (table) or those stored in the SGA.
The server process modifies data in the system global area. The DBWn process writes modified blocks permanently to disk when doing so is efficient. Because the transaction is committed, the LGWR process immediately records the transaction in the redo log file.
If the transaction is successful, then the server process sends a message across the network to the application. If it is not successful, then an error message is transmitted.
Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users' transactions and prevents contention between transactions that request the same data.

三、合理使用計算機資源

和計算機互動無非是和下面5種重要資源互動：

1．File (disk)

IO問題無疑是計算機發展的最大瓶頸，因此在部署storage時一定要考慮io的吞吐量(throughout)

2．Memory (為什麼計算機要使用記憶體？減少物理io)

3．Process

a．thread（windows）

b．process（unix/linux）

4．Network（*）

5．Cpu （*）

1.過度消耗cpu資源的sql診斷和調整

2.latch等待會消耗大量cpu

3.大量解析消耗cpu資源

三、 IO調整

1．IO調整的原則

a．最小化io的訪問（要貫穿整個應用的設計）

b．均衡各類檔案的分佈(ctl,dbf,redo,archivedlog,tempfile)

c．合理使用temporary tablespace（group）

2．儲存級調整

a．儲存規劃不合理，導致io過於集中

b．尋找證據

1）iostat

2）Topas

3）Select * from v$filestat

4）Select * from v$tempstat

c．Sa(system administrator)或者廠商協助調整

3．Db級調整

a．瞭解資料庫的物理結構

b．合理規劃和io相關的檔案分佈

c．均衡（分散）io

d．檔案分佈的原則

1）controlfile單獨存放，mirror不要過多，2份足以

2）redo單獨存放，不要放在raid 5上，member不要過多，2份足以

3）undo單獨存放

4）datafile儘可能的要分散，尤其是分割槽表和分割槽索引所儲存的資料檔案

5）tempfile檔案單獨存放，不同的使用者使用不同的臨時表空間，最好能使用temporary tablespace group

6）index 和data分離，對io影響不大，便於管理（index的原理rowid）

7）archivelog和所有其他檔案分離，要務必和redo絕對分離

四、 記憶體調整

1．os級調整

a．尋找證據

1）vmstat （換頁嚴重 page in page out）

2）topas

b．明確os記憶體不足的原因

2．db級調整

a．瞭解sga的作用及組成部分

1）advisor的啟用和使用

2）調整sga_target

3）調整buffer cache

4）調整shared pool

b．引數sga_max_size的作用，9.2引入該引數之後可以動態調整記憶體

c．lock_sga的作用，防止sga被os swap出去，windows下不起作用

d．瞭解pga的作用

調整pga_aggregate_target

e．理解work area的重要概念有助於理解和調整pga

五、 process調整

1．程式的種類和作用

a）background

b）server

c）user（client）

2．專用模式和共享模式

3. 合理的調整process引數

4. windows客戶端v$session裡欄位process的含義

六、 block空間使用調整

1．Dmt和lmt

2．Assm(bitmap)和mssm（freelist）

3．Pctfree和pctused的作用以及相互制約關係

七、 lock和latch爭奪

1．row level lock

2．table level lock(表級鎖的相容性)

3．Block（阻塞）和deadlock

4．latch(在wait event中介紹)

八、 statistics

1．什麼是統計資訊?它的作用是什麼？

2．例項級的統計資訊v$sysstat

3．Session級別的統計資訊v$sesstat

4．Optimizer statistics（在應用調整裡介紹）

5．Optimizer statistics對oracle生成執行計劃產生重要影響

6．系統統計資訊

九、 wait event

1. 什麼是等待事件（v$event）

2. oracle透過等待事件來反應系統的效能問題

3. 透過等待事件診斷系統效能問題

十、 Metric(度量、指標)和baseline

1．Metric比statistics粒度更細，可以從多角度分析問題

2．Metric的引入主要為了實現proactive（積極、主動）維護資料庫

3．Baseline（基線）的作用，Baseline的引入為了方便、準確的給Metric設定值，從而使系統根據Metric產生更有意義的alert，根據這些alert我們能夠事先維護系統最終把問題處理在萌芽狀態

十一、 statspack、awr、addm、ash

工欲善其事，必先利其器

Oracle中重要效能診斷工具介紹

十二、 Checkpoint和redo調整

1．例項恢復

2．控制例項恢復的時間其實就是控制checkpoint發生的頻率

3．調整redo的大小

資料庫邏輯結構設計和應用級別的調整

十三、應用設計

1. 在設計表時嚴格遵守第三正規化

2. 理解不同的應用對資料的儲存和訪問方式以便使用不同的oracle table type

3. oracle 支援的table type

a) heap

b) iot

c) partition

d) cluster

e) temporary

4. 合理使用index

5. 合理使用view

View可以增加程式的可讀性、控制資料的安全訪問，也是物件導向特性的一個體現，但是view的使用在某種程度上對sql效能沒有任何提高

6. 合理使用trigger

a. 在大型的、複雜的應用裡儘可能的少使用trigger，使用它方便的同時可能不僅會使應用的靈活性受到限制同時可能會對效能產生影響

b. 如果trigger實現的功能很複雜，那麼最好把複雜的業務邏輯透過procedure或者function來實現之後再在trigger裡呼叫process或者function

7. 要儘可能的多使用procedure、function或者package

a. procedure、function，package進行了預編譯

b. procedure、function，package中sql自動使用繫結變數

8. 儘可能的多使用synonym

Synonym的引入是oracle物件導向思想的又一重要體現，在procedure、function，package裡儘可能的多使用Synonym以減少由於物件（如表）名字改變而大量修改引用該物件的procedure、function，package

9. 少使用sequence

使用sequence有時候確實很方便，不過sequence不能保證絕對的連續，而且如果對sequence的一些特性（如cache）控制不好會對效能產生影響

10.合理使用外來鍵

在大型的、複雜的應用裡儘可能的少使用外來鍵，外來鍵的使用會使資料的完整性、一致性得到有力保障，但是它的使用有時會使應用的靈活性受到很大限制，如果不使用它，那麼資料的完整性和一致性完全需要我們應用開發人員自己來控制，這對應用設計和開發人員都提出了很高的要求，這個到底是否使用根據自己的應用特點和團隊技術實力自行選擇

11.繫結變數

a. 在以oltp為主的應用系統中對使用繫結變數，因為在oltp系統裡都是一些小的事務對應的sql，sql的解析可能消耗的資源往往大於執行消耗的資源，因此我們儘可能的要減少解消耗的資源對系統效能的影響

b. 在以olap為主的應用系統裡通常不需要使用繫結變數，因為在olap應用系統裡往往都是一些大的事務對用的長時間執行的sql，sql的執行時間遠遠大於sql的解析時間，因此使用繫結變數沒有太多意義

十四、應用調整

1.Optimizer介紹(cbo，rbo)

2.Rbo介紹

3.Execute plan

a.set auto trace [on|traceonly {explain|statistics}]

b.explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ]
FOR < sql_statement >

建立plan_table: $ORACLE_HOME/rdbms/admin/utlxplan.sql

select*from table(dbms_xplan.display('PLAN_TABLE','TEST','ADVANCED'));

c.v$sql_plan

d.10046 event

e.plsql developer F5

4.Index的原理

rowid的格式以及在index leaf node中的儲存

5.Index的種類

a. b*tree

b. bitmap

6.Index的scan

a.Unique scan

b．Scan scan

c．Desc scn

d．Index full scan

e．Fast full scan

f．Index skip scan

7.Cbo介紹

8.Optimizer statistics

Dbms_stats介紹

9.Table access sort

a. ordered

b. leading

c. use_hash

d. use_nl

e. use_merge

10.Optimizer hint

a.index

b.no_index

c.full

d.rull

11.Table join method

a.loop nested join

b.sort merge join

c.hash join

12.Plan stability

13.Stored outline

14.確定high load sql

a.v$sqlarea

b.ASH

15.Sql trace+tkprof

十五、sql調整指導公式

用友著名sql調優專家李奎陽（pub id：anlinew）總結了他多年來的sql調優公式：T=S/V

原文連線：

http://www.itpub.net/viewthread.php?tid=931723&highlight=%2Banlinew

下面是引用他的解釋：

我的SQL調優公式T＝S/V

比較愚鈍，從事調優工作這麼些年，處理過的效能問題也不少，也就總結出這麼個公式來

S指sql所需訪問的資源總量，V指sql單位時間所能訪問的資源量，T自然就是SQL執行所需時間了這個公式很簡單，調優的目標是減少T，T＝S/V，那麼我們要調的物件自然就要放到這裡的S和V上，調優的方法就是圍繞減少S和增大V增大V（單位時間所能訪問的資源量），在硬體裝置不變的情況下所能做的只有充分利用現有資源，如：透過調整SGA充分利用物理MEMORY，透過並行處理充分利用CPU，透過調整IO分佈充分利用硬碟處理能力，還有。。。。升級裝置？減少S（sql所需訪問的資源總量），這通常是調優工作的重中之重，SQL調優的主要目的就是圍繞著如何減少S在進行，在ORACLE中，所需訪問的資源以block記，一條SQL執行所讀寫的block數直接影響到SQL的執行時間，如何知道SQL執行所讀寫的block數呢，簡單的方法如下：
set autotrace traceonly
SQL> select count(*) from test;已用時間: 00: 00: 10.01執行計劃
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19342 (1)| 00:03:53 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 5285K| 19342 (1)| 00:03:53 |
-------------------------------------------------------------------統計資訊
----------------------------------------------------------
204 recursive calls
0 db block gets
70655 consistent gets
70616 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed透過上面的資訊我們可以看到此SQL訪問的block數為70655，並且基本上是物理讀，其執行時間為00: 00: 10.01大家可能一眼就看出來了，這條SQL執行了全表掃描，加索引最佳化就可以了，沒錯，索引訪問正是減少SQL所需訪問資源的一個主要途徑其效果也很明顯已用時間: 00: 00: 01.89執行計劃
----------------------------------------------------------
Plan hash value: 826211483

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3089 (2)| 00:00:38 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| I_TEST1 | 5285K| 3089 (2)| 00:00:38 |
-------------------------------------------------------------------------統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
11218 consistent gets
11197 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed其訪問的block數變為11218，其執行時間隨之變為00: 00: 01.89透過減少S，我們可以看到T得到明顯的減小上面情況是在V不變的情況下（都是物理讀）的差別再看看V最大化的結果

SQL> select count(*) from test;執行計劃
----------------------------------------------------------
Plan hash value: 826211483

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3089 (2)| 00:00:38 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| I_TEST1 | 5285K| 3089 (2)| 00:00:38 |
-------------------------------------------------------------------------統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
11218 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed已用時間: 00: 00: 00.79

SQL> select /*+parallel(test 2)*/ count(*) from test;執行計劃
----------------------------------------------------------
Plan hash value: 826211483

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3089 (2)| 00:00:38 |
| 1 | SORT AGGREGATE | | 1 |