一、識別占用資源較多的語句的方法（4種方法）

1.測試組和最終用戶反饋的與反應(yīng)緩慢有關(guān)的問題。

2.利用V_$SQLAREA視圖提供了執(zhí)行的細節(jié)。（執(zhí)行、讀取磁盤和讀取緩沖區(qū)的次數(shù)）

• 數(shù)據(jù)列

EXECUTIONS：執(zhí)行次數(shù)

DISK_READS：讀盤次數(shù)

COMMAND_TYPE：命令類型（3:select,2:insert;6:update;7delete;47:pl/sql程序單元）

OPTIMIZER_MODE：優(yōu)化方式

SQL_TEXT：Sql語句

SHARABLE_MEM：占用shared pool的內(nèi)存多少

BUFFER_GETS：讀取緩沖區(qū)的次數(shù)

• 用途

1、幫忙找出性能較差的SQL語句

2、幫忙找出最高頻率的SQL

3、幫忙分析是否需要索引或改善聯(lián)接

監(jiān)控當(dāng)前Oracle:family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman''>的session，如出現(xiàn)時鐘的標(biāo)志，表示此進程中的sql運行時間較長。

4. Trace工具：

a)查看數(shù)據(jù)庫服務(wù)的初始參數(shù)：timed_statistics、user_dump_dest和max_dump_file_size

b)Step 1: alter session set sql_trace=true

c)Step 2: run sql；

d)Step 3: alter session set sql_trace=false

e)Step 4:使用 “TKPROF”轉(zhuǎn)換跟蹤文件

f)Parse，解析數(shù)量大通常表明需要增加數(shù)據(jù)庫服務(wù)器的共享池大小，

query或current提取數(shù)量大表明如果沒有索引，語句可能會運行得更有效，

disk提取數(shù)量表明索引有可能改進性能，

library cache中多于一次的錯過表明需要一個更大的共享池大小

二、如何管理語句處理和選項

•基于成本（Cost Based） 和基于規(guī)則（Rule Based） 兩種優(yōu)化器， 簡稱為CBO 和RBO

•Optimizer Mode參數(shù)值:

Choose：如果存在訪問過的任何表的統(tǒng)計數(shù)據(jù) ，則使用基于成本的Optimizer,目標(biāo)是獲得最優(yōu)的通過量。如果一些表沒有統(tǒng)計數(shù)據(jù)，則使用估計值。如果沒有可用的統(tǒng)計數(shù)據(jù)，則將使用基于規(guī)則的Optimizer。

All_rows：總是使用基于成本的Optimizer，目標(biāo)是獲得最優(yōu)的通過量。

First_rows_n：總是使用基于成本的Optimizer，目標(biāo)是對返回前N行（“n”可以是1，10，100或者1000）獲得最優(yōu)的響應(yīng)時間。

First_rows：用于向后兼容。使用成本與試探性方法的結(jié)合，以便快速傳遞前幾行。

RULE：總是使用基于規(guī)則的Optimizer

三、使用數(shù)據(jù)庫特性來獲得有助于查看性能的處理統(tǒng)計信息（解釋計劃和AUTOTRACE）

No1: Explain Plan

A）使用Explain工具需要創(chuàng)建Explain_plan表，這必須先進入相關(guān)應(yīng)用表、視圖和索引的所有者的帳戶內(nèi). (@D:oracleora92rdbmsadminutlxplan)

B） 表結(jié)構(gòu)：

STATEMENT_ID：為一條指定的SQL語句確定特定的執(zhí)行計劃名稱。如果在EXPLAN PLAN語句中沒有使用SET STATEMENT_ID，那么此值會被設(shè)為NULL。

OPERATION：在計劃的某一步驟執(zhí)行的操作名稱，例如：Filters，Index，Table，Marge Joins and Table等。

OPTION：對OPERATION操作的補充，例如：對一個表的操作，OPERATION可能是TABLE ACCESS，但OPTION可能為by ROWID或FULL。

Object_Owner：擁有此database Object的Schema名或Oracle帳戶名。

Object_name：Database Object名

Object_type：類型，例如：表、視圖、索引等等

ID：指明某一步驟在執(zhí)行計劃中的位置。

PARENT_ID：指明從某一操作中取得信息的前一個操作。通過對與ID和PARENT_ID使用Connect By操作，我們可以查詢整個執(zhí)行計劃樹。

C）EXPLAIN搜索路徑解釋

•全表掃描（Full Table Scans）(無可用索引,大量數(shù)據(jù),小表 ,全表掃描hints,HWM(High Water Mark), Rowid掃描)

•索引掃描

索引唯一掃描（Index Unique Scans）

索引范圍掃描（Index Range Scans）

索引降序范圍掃描（Index Range Scans Descending）

索引跳躍掃描（Index Skip Scans）

全索引掃描（Full Scans）

快速全索引掃描（Fast Full Index Scans）

索引連接（Index Joins）

位圖連接（Bitmap Joins）

•如何選擇訪問路徑： CBO首先檢查WHERE子句中的條件以及FROM子句，確定有哪些訪問路徑是可用的。然后CBO使用這個訪問路徑產(chǎn)生一組可能的執(zhí)行計劃，再通過索引、表的統(tǒng)計信息評估每個計劃的成本，最后優(yōu)化器選擇成本最低的一個。

•表的連接方式：

Nested Loops會循環(huán)外表（驅(qū)動表），逐個比對和內(nèi)表的連接是否符合條件。在驅(qū)動表比較小，內(nèi)表比較大，而且內(nèi)外表的連接列有索引的時候比較好。當(dāng)SORT_AREA空間不足的時候，Oracle也會選擇使用NL。基于Cost的Oracle優(yōu)化器(CBO)會自動選擇較小的表做外表。（優(yōu)點：嵌套循環(huán)連接比其他連接方法有優(yōu)勢,它可以快速地從結(jié)果集中提取第一批記錄,而不用等待整個結(jié)果集完全確定下來。缺點：如果內(nèi)部行源表(讀取的第二張表（內(nèi)表）已連接的列上不包含索引,或者索引不是高度可選時, 嵌套循環(huán)連接效率是很低的。如果驅(qū)動行源表(從驅(qū)動表中提取的記錄)非常龐大時,其他的連接方法可能更加有效。）

SORT- merge JOIN，將兩表的連接列各自排序然后合并，只能用于連接列相等的情況，適合兩表大小相若的情況（在缺乏數(shù)據(jù)的選擇性或者可用的索引時,或者兩個源表都過于龐大(超過記錄數(shù)的5%)時,排序合并連接將比嵌套循環(huán)連更加高效。但是,排列合并連接只能用于等價連接(WHERE D.deptno=E.dejptno,而不是WHERE D.deptno>=E.deptno)。排列合并連接需要臨時的內(nèi)存塊,以用于排序(如果SORT_AREA_SIZE設(shè)置得太小的話)。這將導(dǎo)致在臨時表空間占用更多的內(nèi)存和磁盤I/O。）

HASH JOIN在其中一表的連接列上作散列，因此只有另外一個表做排序合并，理論上比SORT JOIN會快些，需?/td>

'FONT-FAMILY: 宋體; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman''>或FULL。

Object_Owner：擁有此database Object的Schema名或Oracle帳戶名。

Object_name：Database Object名

Object_type：類型，例如：表、視圖、索引等等

ID：指明某一步驟在執(zhí)行計劃中的位置。

PARENT_ID：指明從某一操作中取得信息的前一個操作。通過對與ID和PARENT_ID使用Connect By操作，我們可以查詢整個執(zhí)行計劃樹。

C）EXPLAIN搜索路徑解釋

•全表掃描（Full Table Scans）(無可用索引,大量數(shù)據(jù),小表 ,全表掃描hints,HWM(High Water Mark), Rowid掃描)

•索引掃描

索引唯一掃描（Index Unique Scans）

索引范圍掃描（Index Range Scans）

索引降序范圍掃描（Index Range Scans Descending）

索引跳躍掃描（Index Skip Scans）

全索引掃描（Full Scans）

快速全索引掃描（Fast Full Index Scans）

索引連接（Index Joins）

位圖連接（Bitmap Joins）

• 如何選擇訪問路徑： CBO首先檢查WHERE子句中的條件以及FROM子句，確定有哪些訪問路徑是可用的。然后CBO使用這個訪問路徑產(chǎn)生一組可能的執(zhí)行計劃，再通過索引、表的統(tǒng)計信息評估每個計劃的成本，最后優(yōu)化器選擇成本最低的一個。

• 表的連接方式：

Nested Loops會循環(huán)外表（驅(qū)動表），逐個比對和內(nèi)表的連接是否符合條件。在驅(qū)動表比較小，內(nèi)表比較大，而且內(nèi)外表的連接列有索引的時候比較好。當(dāng)SORT_AREA空間不足的時候，Oracle也會選擇使用NL。基于Cost的Oracle優(yōu)化器(CBO)會自動選擇較小的表做外表。（優(yōu)點：嵌套循環(huán)連接比其他連接方法有優(yōu)勢,它可以快速地從結(jié)果集中提取第一批記錄,而不用等待整個結(jié)果集完全確定下來。缺點：如果內(nèi)部行源表(讀取的第二張表（內(nèi)表）已連接的列上不包含索引,或者索引不是高度可選時, 嵌套循環(huán)連接效率是很低的。如果驅(qū)動行源表(從驅(qū)動表中提取的記錄)非常龐大時,其他的連接方法可能更加有效。）

SORT- merge JOIN，將兩表的連接列各自排序然后合并，只能用于連接列相等的情況，適合兩表大小相若的情況（在缺乏數(shù)據(jù)的選擇性或者可用的索引時,或者兩個源表都過于龐大(超過記錄數(shù)的5%)時,排序合并連接將比嵌套循環(huán)連更加高效。但是,排列合并連接只能用于等價連接(WHERE D.deptno=E.dejptno,而不是WHERE D.deptno>=E.deptno)。排列合并連接需要臨時的內(nèi)存塊,以用于排序(如果SORT_AREA_SIZE設(shè)置得太小的話)。這將導(dǎo)致在臨時表空間占用更多的內(nèi)存和磁盤I/O。）

HASH JOIN在其中一表的連接列上作散列，因此只有另外一個表做排序合并，理論上比SORT JOIN會快些，需要有足夠的內(nèi)存，而且打開了SORT_JOIN_ENABLE參數(shù)。(當(dāng)缺少有用的索引時，哈希連接比嵌套循環(huán)連接更加有效。哈希連接可能比排序合并連接更快，因為在這種情況下只有一張源表需要排序。哈希連接也可能比嵌套循環(huán)連接更快，因為處理內(nèi)存中的哈希表比檢索B_樹索引更加迅速。和排序合并連接、群集連接一樣，哈希連接只能用于等價連接。和排序合并連接一樣，哈希連接使用內(nèi)存資源，并且當(dāng)用于排序內(nèi)存不足時，會增加臨時表空間的I/O（這將使這種連接方法速度變得極慢）。最后，只有基于代價的優(yōu)化器才可以使用哈希連接。)

BNo2: AUTOTRACE

•set autotrace 使用步驟：

1、以system登錄

2、創(chuàng)建plustrace角色; sqlplusadminplustrce.sql

3、向常規(guī)用戶授予權(quán)限：grant plustrace to

4、如果沒有plan_table也要創(chuàng)建: rdbmsadminutlxplan.sql

• set autotrace 選項

on 顯示查詢結(jié)果，執(zhí)行計劃，統(tǒng)計數(shù)據(jù)

on statistics 顯示查詢結(jié)果，統(tǒng)計數(shù)據(jù)，不顯示執(zhí)行計劃

on explain 顯示查詢結(jié)果，執(zhí)行計劃，不顯示統(tǒng)計數(shù)據(jù)

traceonly 顯示執(zhí)行計劃和統(tǒng)計結(jié)果，但不包括查詢結(jié)果

traceonly statistics 僅顯示統(tǒng)計數(shù)據(jù)

recursive calls 在用戶級別和系統(tǒng)級別上生成的遞歸調(diào)用的數(shù)量。Oracle維護了一些用于內(nèi)部處理的表。當(dāng)oracle需要對這些表進行更改時，它就會在內(nèi)部生成一個SQL語句，然后這個語句再生成一個遞歸調(diào)用。

db block gets 請求一個CURRENT塊的次數(shù)

consistent gets 為一塊請求consistent read的次數(shù)

physical reads 從磁盤讀取得數(shù)據(jù)塊總數(shù)。這個數(shù)量等于“直接物理讀取”的值加上讀入緩沖區(qū)的所有數(shù)據(jù)塊

redo size 生成的重做的總數(shù)量（以字節(jié)為單位）

bytes sent via SQL * Net to client 從前臺進程發(fā)送給客戶的總字節(jié)數(shù)

bytes received via SQL * Net from client 通過Oracle Net從客戶接收的總字節(jié)數(shù)

SQL*Net roundtrips to/from client 發(fā)送給客戶和從客戶接收的Oracle Net消息的總數(shù)

sorts (memory) 完全在內(nèi)存中執(zhí)行并且不需要任何磁盤寫入的排序操作的數(shù)量

>

db block gets 請求一個CURRENT塊的次數(shù)

consistent gets 為一塊請求consistent read的次數(shù)

physical reads 從磁盤讀取得數(shù)據(jù)塊總數(shù)。這個數(shù)量等于“直接物理讀取”的值加上讀入緩沖區(qū)的所有數(shù)據(jù)塊

redo size 生成的重做的總數(shù)量（以字節(jié)為單位）

bytes sent via SQL * Net to client 從前臺進程發(fā)送給客戶的總字節(jié)數(shù)

bytes received via SQL * Net from client 通過Oracle Net從客戶接收的總字節(jié)數(shù)

SQL*Net roundtrips to/from client 發(fā)送給客戶和從客戶接收的Oracle Net消息的總數(shù)

sorts (memory) 完全在內(nèi)存中執(zhí)行并且不需要任何磁盤寫入的排序操作的數(shù)量