oraclesql參考手冊
A. Oraclesql性能優化調整的幾點基礎事項
1. 選擇最有效率的表名順序 SQL的解析器按照從右到左的順序處理FROM子句中的表名, FROM子句中包含多個表的情況下,你必須選擇記錄
1. 選擇最有效率的表名順序
SQL的解析器按照從右到左的順序處理FROM子句中的表名, FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。
例如:
表 TAB1 16,384條記錄 表 TAB2 1條記錄
選擇TAB2作為基礎表 (最好的方法):
select count(*) from tab1,tab2 ----執行時間0.96秒
選擇TAB1作為基礎表 (不佳的方法):
select count(*) from tab2,tab1 ----執行時間26.09秒
2. 3個以上的表連接查詢
如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表。
例如:
EMP表描述了LOCATION表和CATEGORY表的交集
SELECT * FROM LOCATION L , CATEGORY C, EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
，
B. SQLServer和Oracle資料庫分析(oraclesql性能分析)
分析原則：
1、具體問題具體分析（這是由於不同的應用系統，不同的測試目的，不同的性能關注點）
2、查找瓶頸時按以下順序，由易到難。
伺服器硬體瓶頸-〉網路瓶頸（對區域網，可以不考慮）-〉伺服器操作系統瓶頸（參數配置）-〉中間件瓶頸（參數配置，資料庫，web伺服器等）-〉應用瓶頸（SQL語句、資料庫設計、業務邏輯、演算法等）註：以上過程並不是每個分析中都需要的，要根據測試目的和要求來確定分析的深度。對一些要求低的，我們分析到應用系統在將來大的負載壓力（並發用戶數、數據量）下，系統的硬體瓶頸在哪兒就夠了。分段排除法很有效。
分析的信息來源：1、根據場景運行過程中的錯誤提示信息；
2、根據測試結果收集到的監控指標數據。
一、錯誤提示分析
分析實例：
1、Error:「10.10.10.30:8080〃:[10060]Connection
Error::Server「10.10.10.30〃
分析：
A、應用服務死掉（小用戶時：程序上的問題。程序上處理資料庫的問題）
B、應用服務沒有死（應用服務參數設置問題）
例：在許多客戶端連接Weblogic應用伺服器被拒絕，而在伺服器端沒有錯誤顯示，則有可能是Weblogic中的server元素的AeptBacklog屬性值設得過低。如果連接時收到消息，說明應提高該值，每次增加25%
C、資料庫的連接（1、在應用服務的性能參數可能太小了；2、資料庫啟動的最大連接數（跟硬體的內存有關）。）
分析：可能是以下原因造成
A、譽叢應用服務參慶掘櫻數設置太大導致伺服器的瓶頸；B、頁面中圖片太多；C、在程序處理表的時候檢查欄位太大多。
二．監控指標數據分析
1、最大並發用戶數：
應用系統在當前環境（硬體環境、網路環境、軟體環境（參數配置））下能承受的最大並發用戶數。在方案運行中，如果出現了大於3個用戶的業務操作失敗，或出現了伺服器shutdown的情況，則說明在當前環境下，系統承受不了當前並發用戶的負載壓力，那麼最大並發用戶數就是前一個沒有出現這種現象的並發用戶數。如果測得的最大並發用戶數到達了性能要求，且各伺服器資源情況良好，業務操作響應時間也達到了用戶要求，那麼可行。否則，再根據各伺服器的資源情況和業務操作響應時間進一步分析原因所在。
2、業務操作響應時間：
分析方案運行情況應從平均事務響應時間圖和事務性能摘要圖開始。使用「事務性能摘要」圖，可以確定在方案執行期間響應時間過長的事務。細分事務並分析每個頁面組件的性能。如果伺服器耗時過長，請使用相應的伺服器圖確定有問題的伺服器度量並查明伺服器性能下降的原因。如果網路耗時過長，請使用「網路監視器」圖確定導致性能瓶頸的網路問題
3、伺服器資源監控指標：內存：
1、UNIX資源監控中指標內存頁交換速率（Pagingrate），如散衡果該值偶爾走高，表明當時有線程競爭內存。如果持續很高，則內存可能是瓶頸。也可能是內存訪問命中率低。
2、Windows資源監控中，如果Process計數器和ProcessWorkingSet計數器的值在長時間內持續升高，同時Memory計數器的值持續降低，則很可能存在內存泄漏。
內存資源成為系統性能的瓶頸的徵兆：很高的換頁率()；進程進入不活動狀態；交換區所有磁碟的活動次數可高；可高的全局系統CPU利用率；內存不夠出錯()。
處理器：
1、UNIX資源監控（Windows操作系統同理）中指標CPU佔用率（），如果該值持續超過95%，表明瓶頸是CPU。可以考慮增加一個處理器或換一個更快的處理器。如果伺服器專用於SQLServer，可接受的最大上限是80-85%合理使用的范圍在60%至70%。
2、Windows資源監控中，如果System大於2，而處理器利用率（）一直很低，則存在著處理器阻塞。
CPU資源成為系統性能的瓶頸的徵兆：很慢的響應時間()；CPU空閑時間為零()；過高的用戶佔用CPU時間()；過高的系統佔用CPU時間()；長時間的有很長的運行進程隊列()。
磁碟I/O：
1、UNIX資源監控（Windows操作系統同理）中指標磁碟交換率（Diskrate），如果該參數值一直很高，表明I/O有問題。可考慮更換更快的硬碟系統。
2、Windows資源監控中，如果DiskTime和Avg.DiskQueueLength的值很高，而PageReads/sec頁面讀取操作速率很低，則可能存在磁碟瓶徑。
I/O資源成為系統性能的瓶頸的徵兆：過高的磁碟利用率(highdiskutilization)；
太長的磁碟等待隊列(largediskqueuelength)；
等待磁碟I/O的時間所佔的百分率太高(/O)；
太高的物理I/O速率：largephysicalI/Orate(notsufficientinitself)；
過低的緩存命中率(lowbuffercachehitratio(notsufficientinitself))；
太長的運行進程隊列，但CPU卻空閑(largerunqueuewithidleCPU)。
4、資料庫伺服器：
SQLServer資料庫：
1、SQLServer資源監控中指標緩存點擊率（CacheHitRatio），該值越高越好。如果持續低於80%，應考慮增加內存。
2、如果FullScans/sec（全表掃描/秒）計數器顯示的值比1或2高，則應分析你的查詢以確定是否確實需要全表掃描，以及SQL查詢是否可以被優化。
3、NumberofDeadlocks/sec(死鎖的數量/秒)：死鎖對應用程序的可伸縮性非常有害，並且會導致惡劣的用戶體驗。該計數器的值必須為0。
4、LockRequests/sec(鎖請求/秒)，通過優化查詢來減少讀取次數，可以減少該計數器的值。
Oracle資料庫：
1、如果自由內存接近於0而且庫快存或數據字典快存的命中率小於0.90，那麼需要增加SHARED_POOL_SIZE的大小。
快存（共享SQL區）和數據字典快存的命中率：select(sum(pins-reloads))/sum(pins)fromv$librarycache；
select(sum(gets-getmisses))/sum(gets)fromv$rowcache；
自由內存：select*fromv$sgastatwherename=『freememory』。
2、如果數據的緩存命中率小於0.90，那麼需要加大DB_BLOCK_BUFFERS參數的值（單位：塊）。
緩沖區高速緩存命中率：selectname,valuefromv$sysstatwherenamein(『dbblockgets』，『consistentgets』『physicalreads』)HitRatio=1-(physicalreads/(dbblockgetsconsistentgets))。
3、如果日誌緩沖區申請的值較大，則應加大LOG_BUFFER參數的值。
日誌緩沖區的申請情況：selectname，valuefromv$sysstatwherename=『redologspacerequests』。
4、如果內存排序命中率小於0.95，則應加大SORT_AREA_SIZE以避免磁碟排序。
內存排序命中率：selectround((100*b.value)/decode((a.valueb.value)，0，1，(a.valueb.value)),2)fromv$sysstata，v$sysstatbwherea.name=』sorts(disk)』andb.name=』sorts(memory)』
註：上述SQLServer和Oracle資料庫分析，只是一些簡單、基本的分析，特別是Oracle資料庫的分析和優化，是一門專門的技術，進一步的分析可查相關資料。
C. oracle 區別大小寫嗎
不區分大小寫，關鍵字、用戶、密碼 、欄位名 、表名、序列名 、觸發器名等是不區分的，我們平時進入都是大小寫隨便輸入的表名,欄位名,關鍵字大小寫忽略表名,欄位名不能使用關鍵字，表名,欄位名不可以使用數字開頭,中間不能出現特殊符號表名,欄位名長度不能超過30個字元表名,欄位名定義需要有含義。具體欄位值區分大小寫。而scott是給初學者學習的用戶，學習者可以用Scott登錄系統，注意scott用戶登錄後，就可以使用Oracle提供的資料庫和數據表，這些都是oracle提供。顫纖辯
(3)oraclesql參考手冊擴展閱讀
Oracle資料庫中使用的都是sql語句，語法差異並不大。而sql語句對於大小寫並不敏感，也就是說sql語句的語法可以任意使用。除了sql語句以外，我們還有自己的元素，比如表名、欄位名，還有我們需要存入的數據，它們的大小寫是需要DBM來管理的。
system、sys、scott，當中system和sys的差別在與是否能創建資料庫，sys用戶登錄才幹夠豎襲創建資料庫，而scott是給剛開始學習的人茄缺學習的用戶。學習者能夠用Scott登錄系統，注意scott用戶登錄後，就能夠使用Oracle提供的資料庫和數據表，這些都是oracle提供的。
D. Oraclesql
前言
sql_trace 是我在工作中經常要用到的調優工具 相比較statspack 我更願意用這個工具
因為數據逗搜庫慢原因的 %以上是由於sql問題造成的 statspack沒有sql的執行計劃 顯示沒有它直觀 方便 對想要針對性不強
介紹資料庫調優需要經常會用到的工具 可以很精確地跟抓取相關session正在運行的sql 再通過tkprof分析出來sql的執行計劃等相關信息 從而判斷那些sql語句存在問題
統計如下信孝鄭息（摘字官方文檔）
Parse execute and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Misses on the library cache
Username under which each parse occurred
Each mit and rollback
使用
使用前需要注意的地方
初始化參數timed_statistics=true 允許sql trace 和其他的一些動態性能視圖收集與時間（cpu elapsed）山慎歷有關的參數 一定要打開 不然相關信息不會被收集 這是一個動態的參數 也可以在session級別設置
SQL>alter session set titimed_statistics=true
MAX_DUMP_FILE_SIZE跟蹤文件的大小的限制 如果跟蹤信息較多可以設置成unlimited 可以是KB MB單位 I開始默認為unlimited這是一個動態的參數 也可以在session級別設置
SQL>alter system set max_mp_file_size=
SQL>alter system set max_mp_file_size=unlimited
USER_DUMP_DEST指定跟蹤文件的路徑 默認路徑實在$ORACLE_BASE/admin/ORA_SID/ump這是一個動態的參數 也可以在session級別設置
SQL>alter system set user_mp_dest=/oracle/trace
資料庫級別
設置slq_trace參數為true會對整個實例進行跟蹤 包括所有進程 用戶進程和後台進程 會造成比較嚴重的性能問題 生產環境一定要慎用
SQL>alter system set sql_trace=true;
Session級別
當前會話
SQL>alter session set sql_trace=true;
SQL>alter session set sql_trace=false;
其他會話
通過oracle提供的系統包 DBMS_SYSTEM SET_SQL_TRACE_IN_SESSION來實現
SQL>execute dbms_system set_sql_trace_in_session(sid serial# true);
SQL>execute dbms_system set_sql_trace_in_session(sid serial# false);
注
sid serial#從v$session視圖中獲得
DBMS_SYSTEM包里還可以對其他用戶的參數（如 timed_statistics max_mp_file）進行設置 在這不做介紹了 很少用到 想了解dbms_system里的程序包可以desc dbms_system看一下
得到trace文件後我們要用tkprof他進行格式化 通過sql語句快速定位到相應的trace文件
Tkprof
tkprof的目的是將sql trace生成的跟蹤文件轉換成用戶可以理解的格式
格式
tkprof tracefile outputfile [optional | parameters ]
參數和選項（這里只介紹最常用的 也是最實用的）
explain=user/password執行explain命令將結果放在SQL trace的輸出文件中
sys=[yes/no]確定系統是否列出由sys用戶產生或重調的sql語句
sort=sort_option按照指定的方法對sql trace的輸出文件進行降序排序
sort_option選項
prscnt按解析次數排序
prscpu按解析所花cpu時間排序
prsela按解析所經歷的時間排序
prsdsk按解析時物理的讀操作的次數排序
prsqry按解析時以一致模式讀取數據塊的次數排序
prscu按解析時以當前讀取數據塊的次數進行排序
execnt按執行次數排序
execpu按執行時花的cpu時間排序
exeela按執行所經歷的時間排序
exedsk按執行時物理讀操作的次數排序
exeqry按執行時以一致模式讀取數據塊的次數排序
execu按執行時以當前模式讀取數據塊的次數排序
exerow按執行時處理的記錄的次數進行排序
exemis按執行時庫緩沖區的錯誤排序
fchcnt按返回數據的次數進行排序
fchcpu按返回數據cpu所花時間排序
fchela按返回數據所經歷的時間排序
fchdsk按返回數據時的物理讀操作的次數排序
fchqry按返回數據時一致模式讀取數據塊的次數排序
fchcu按返回數據時當前模式讀取數據塊的次數排序
fchrow按返回數據時處理的數據數量排序
注
這些排序中我經常用到的是fchdsk fckchela fchqry 因為有問題的sql一般都是大的查詢造成的 當然更新 插入 刪除時也會存在全表掃描 這就需要:exedsk exeqry exeela等選項 根據具體情況具體分析
Cpu時間和Elapsed時間都是以秒為單位 而且兩個值基本上一樣 但我比較常用elapsed 他是反映的用戶相應時間 從運行sql到用戶得到結果的時間 會更實際些
tkprof輸出文件各列的含義 （理解下面的含義對我們快速定位問題很有幫助）
parse:
將sql語句轉換成執行計劃 包括檢查是否有正確的授權 需要到得表 列及其他引用到得對象是否存在 這些信息分別存在v$librarycache v$rowcache
execute
oracle實際執行的語句 如 insert update delete 這些會修改數據 對於select操作 這部只是確定選擇的行數
fetch
返回查詢獲得的行數 只有執行select會被收集
Count
這個語句被parse execute fetch的次數的統計
Cpu
這個語句所有的parse execute fetch所用的cpu總的時間 以秒為單位 如果TIMED_STATISTICS 關閉的話 值為
Elapsed
這個語句所有的parse execute fetch所消耗的總的時間 以秒為單位 如果TIMED_STATISTICS 關閉的話 值為
Disk
這個語句所有的parse execute fetch從磁碟上的數據文件中讀取的數據塊的數量
Query
在一致性讀的模式下 這個語句所有的parse execute fetch所獲取的buffer數量（這部分是從內存讀取的也就是邏輯讀取的 相當於執行計劃里的consistent gets）
Current
在current模式下 這個語句所有的parse execute fetch所獲取的buffer數量 一般是current模式下發生的delect insert update的操作都會獲取buffer
Rows
語句返回的行數 不包括子查詢中返回的記錄數目 對於select語句 返回在fetch這步 對於insert delete update操作 返回記錄是在execute這步
分析
我一般的思路步驟是
先找磁碟多的sq l(sort= fchdsk ) 意味著全表掃描 找運行時間長的(sort= fchela) 意味著sql可能寫的不好或磁碟 邏輯讀較多 找出一致性讀較多的(sort= fchqry) 當表不是很大的時候（可能全部緩存住了） 沒有發生磁碟讀 但不意味著不需要建立索引 或者sql需要優化 找出當前模式從緩沖區獲得數據的數量(sort=exedsk exeela exeqry) 這些主要集中在dml語句里的操作 看是否有必要優化sql或建立索引之所以排序是為了在sql很多的時候快速定位sql 如果sql比較少的話就沒必要排序了 但我們要有分析問題的思路
舉例
我自己建立了一個表
create table t (id int);
begin
for v in loop
insert into t values(v );
end loop
mit;
end;
下面是sql_trace所抓到得sql
不正常狀態
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache ring parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
TABLE ACCESS FULL T
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (FULL) OF T
首先這是一個select語句 它走了全部掃描
磁碟讀（ ）和邏輯讀（ ）都很多
運行了 次（Execute） 分析了 次（Parse） 一共用了將近 秒（elapsed）
我只是選擇表的一行的數據的結果 就發生這么大的成本 很顯然是全表掃描的結果造成的
正常狀態
在做跟蹤前我為這個表建立了一個索引
Create index t on t (id);
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache ring parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
INDEX RANGE SCAN T (object id )
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
INDEX (RANGE SCAN) OF T (NON UNIQUE)
*******************************************************************************
同樣的語句
它走了索引 物理讀 這個 其實是開始讀索引時需要第一次讀入的 以後運行就沒有了
邏輯讀 （平均這個sql一次 個邏輯讀）
同樣運行了 次（Execute）
分析了 次（Parse） 運行次數越多 分析次數越少越好一共只用了 秒（elapsed）
lishixin/Article/program/Oracle/201311/17866