oracle性能sql

⑴ 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資料庫的分析和優化，是一門專門的技術，進一步的分析可查相關資料。

⑵ 如何查詢oracle 資料庫性能，sql資源佔用

作為一個開發/測試人員，或多或少都得和資料庫打交道，而對資料庫的操作歸根到底都是SQL語句，所有操作到最後都是操作數據，那麼對sql性能的掌控又成了我們工作中一件非常重要的工作。下面簡單介紹下一些查看oracle性能的一些實用方法：

1、查詢每台機器的連接數

selectt.MACHINE,count(*)fromv$sessiontgroupbyt.MACHINE

這里所說的每台機器是指每個連接oracle資料庫的伺服器，每個伺服器都有配置連接資料庫的連接數，以websphere為例，在數據源中，每個數據源都有配置其最大/最小連接數。

執行SQL後，可以看到每個伺服器連接oracle資料庫的連接數，若某個伺服器的連接數非常大，或者已經達到其最大連接數，那麼這台伺服器上的應用可能有問題導致其連接不能正常釋放。

2、查詢每個連接數的sql_text

v$session表裡存在的連接不是一直都在執行操作，如果sql_hash_value為空或者0，則該連接是空閑的，可以查詢哪些連接非空閑，web3是機器名，就是WebSphereApplicationServer的主機名。

selectt.sql_hash_value,t.*fromv$sessiontwheret.MACHINE='web3'andt.sql_hash_value!=0

這個SQL查詢出來的結果不能看到具體的SQL語句，需要看具體SQL語句的執行下面的方法。

3、查詢每個活動的連接執行什麼sql

selectsid,username,sql_hash_value,b.sql_text

fromv$sessiona,v$sqltextb

wherea.sql_hash_value=b.HASH_VALUEanda.MACHINE='web3'

orderbysid,username,sql_hash_value,b.piece

orderby這句話的作用在於，sql_text每條記錄不是保存一個完整的sql,需要以sql_hash_value為關鍵id,以piece排序，如圖

Username是執行SQL的資料庫用戶名，一個sql_hash_value下的SQL_TEXT組合成一個完整的SQL語句。這樣就可以看到一個連接執行了哪些SQL。

4、.從V$SQLAREA中查詢最佔用資源的查詢

selectb.usernameusername,a.disk_readsreads,a.executionsexec,

a.disk_reads/decode(a.executions,0,1,a.executions)rds_exec_ratio,

a.sql_textStatement

fromv$sqlareaa,dba_usersb

wherea.parsing_user_id=b.user_id

anda.disk_reads>100000

orderbya.disk_readsdesc;

用buffer_gets列來替換disk_reads列可以得到佔用最多內存的sql語句的相關信息。

V$SQL是內存共享SQL區域中已經解析的SQL語句。

該表在SQL性能查看操作中用的比較頻繁的一張表，關於這個表的詳細信息大家可以去http://apps.hi..com/share/detail/299920#上學習，介紹得比較詳細。我這里主要就將該表的常用幾個操作簡單介紹一下：

1、列出使用頻率最高的5個查詢：

selectsql_text,executions

from(selectsql_text,executions,

rank()over

(orderbyexecutionsdesc)exec_rank

fromv$sql)

whereexec_rank<=5;

該查詢結果列出的是執行最頻繁的5個SQL語句。對於這種實用非常頻繁的SQL語句，我們需要對其進行持續的優化以達到最佳執行性能。

2、找出需要大量緩沖讀取（邏輯讀）操作的查詢：

selectbuffer_gets,sql_text

from(selectsql_text,buffer_gets,

dense_rank()over

(orderbybuffer_getsdesc)buffer_gets_rank

fromv$sql)

wherebuffer_gets_rank<=5;

這種需要大量緩沖讀取（邏輯讀）操作的SQL基本是大數據量且邏輯復雜的查詢中會遇到，對於這樣的大數據量查詢SQL語句更加需要持續的關注，並進行優化。

3、持續跟蹤有性能影響的SQL。

SELECT*FROM(

SELECTPARSING_USER_ID,EXECUTIONS,SORTS,

COMMAND_TYPE,DISK_READS,sql_textFROMv$sqlarea

ORDERBYdisk_readsDESC

)

WHEREROWNUM<10

這個語句在SQL性能查看中用的比較多，可以明顯的看出哪些SQL會影響到資料庫性能。

本文主要介紹了使用SQL查詢方式查看oracle資料庫SQL性能的部分常用方法。此外還有許多工具也能實現SQL性能監控，大家可以在網上搜索相關知識進行學習。

轉載僅供參考，版權屬於原作者

⑶ oracle中怎麼確定性能差的SQL語句

oracle中怎麼確定性能差的SQL語句,當涉及 SQL 時，性能不佳有兩方面：CPU 密集型語句（CPU-intensive statements）和 I/O 密集型語句（I/O-intensive statements）。



oracle中怎麼確定性能差的SQL語句,當涉及 SQL 時，性能不佳有兩方面：CPU 密集型語句（CPU-intensive statements）和 I/O 密集型語句（I/O-intensive statements）。

前者很容易定位。所有的操作系統都可以讓我們查看 CPU 密集型任務。這些任務可以追溯到一個特定用戶，一個特定應用程序模塊。 CPU 密集型模塊一般都是由較差的代碼和/或結構造成，而不是性能差的 SQL。一旦確定模塊，你必須試圖使之更有效率。一個可能的解決方案是將把某些處理移除程序，讓資料庫處理（高明點的 SQL，存儲對象，內聯函數，數組處理等）。

第二個是 I/O 密集型的 SQL 語句。這些語句會導致大量的資料庫 I/O（全表掃描，排序，更新等），並以很高代價運行幾個小時。從 Oracle 7 開始，解決了 SQL 識別問題。通過查詢資料庫共享池區域，我們可以很容易確定大多數 I/O 密集型 SQL 語句。

下面 SQL 語句演示了如何確定 I/O 命中率低於 80%的 SQL 語句。這個命中率是，自從 SQL 語句第一次被解析到共享池，通過所有執行的語句反應整體 I/O。下面可能是最近幾分鍾或幾天的結果：


代碼如下




sql> SELECT executions,

2 disk_reads,

3 buffer_gets,

4 ROUND((buffer_gets - disk_reads) / buffer_gets, 2) hit_ratio,

5 sql_text

6 FROM v$sqlarea

7 WHERE executions > 0

8 AND buffer_gets > 0

9 AND (buffer_gets - disk_reads) / buffer_gets < 0.80

10 order by 4 desc ;

EXECUTIONS DISK_READS BUFFER_GETS HIT_RATIO SQL_TEXT

---------- ---------- ----------- ---------- -----------------------------------------------------------------------

16 180 369 .51 SELECT SKU,PREPACK_IND,CASE_ID,TRANSFER_QTY,UNIT_COST,UNIT_RETAIL,ROWID

FROM TSF_DETAIL WHERE transfer = :1 order by sku

16 30 63 .52 SELECT TRANSFER,TO_STORE,TO_WH FROM TSFHEAD WHERE TRANSFER = :b1 AND

TRANSFER_STATUS = 'A'

2 3 7 .57 SELECT SKU FROM UPC_EAN WHERE UPC = :b1

12 14 35 .60 SELECT SUBSTR(DESC_UP,1,30),DEPT,SYSTEM_IND FROM DESC_LOOK WHERE

SKU = :b1

14 13 35 .63 SELECT UNIT_COST,UNIT_RETAIL,SUBCLASS FROM WIN_SKUS WHERE SKU = :b1

事實上，我們發現對特定的 SQL，上面的數據有些誤導，其實語句沒有問題。考慮下面 v$sqlarea 輸出：

Executions Disk_Reads Buffer_Gets Hit_Ratio Sql_Text

---------- ---------- ----------- --------- --------------------

2 6 19 0.68 SELECT A.EMP_NO, ...



該語句的命中率很低，但事實上它很有效。因為，SQL 是通過 UNIQUE 索引操作的，物理磁碟讀取的數量幾乎與邏輯讀取一樣。UNIQUE 索引顯著減少了整體的物理和邏輯磁碟 I/O 數量，導致了一個令人誤解的低命中率。

下面例子，命中率很好。但是真的很好嗎？


代碼如下




Executions Disk_Reads Buffer_Gets Hit_Ratio Sql_Text

---------- ---------- ----------- --------- --------------------

2 3625 178777 0.98 SELECT A.EMP_NO, ...



這個 SQL 語句看上去很有效。但是， 當我們仔細看時，事情就不是那麼回事了。命中率並沒有透露出，該語句存在五個表連接，並且每次執行進行了超過 3600 個物理磁碟讀取。這是否太多了？是否有效？若不進一步研究，無法回答這兩個問題。事實上，這個實例中，五個表的中其一個錯誤地執行了全表掃描。通過重新構造 SQL，我們可以減少物理磁碟 I/O 到小於 50，同時，也顯著減少邏輯磁碟 I/O。巧合的是，命中率也下降到不到 70％。

我們首選 V$SQLAREA 查詢是每個語句執行的物理磁碟 I/O 的真實報告。命中率是信息性的，但有時會產生誤導。邏輯 I/O 相關的很少。如果語句執行 1,000,000 個邏輯 I/O，但只用了不到十分之一秒，這就沒人在乎了。這是總的物理 I/O，幾乎消耗了所有的時間，和確定潛在不正確的 SQL。例如：


代碼如下




sql> SELECT sql_text, executions,

ROUND(disk_reads / executions, 2) reads_per_run,

disk_reads, buffer_gets,

ROUND((buffer_gets - disk_reads)

/ buffer_gets, 2) hit_ratio,

sql_text

FROM v$sqlarea

WHERE executions > 0

AND buffer_gets > 0

AND (buffer_gets - disk_reads) / buffer_gets < 0.80

ORDER by 3 desc ;



前兩個語句會報告更具啟發性的結果：


代碼如下




Executions Reads_Per_Run Disk_Reads Buffer_Gets Hit_Ratio Sql_Text

---------- ------------- ---------- ----------- --------- ------------

2 3 6 19 0.68 SELECT ...

2 1812.5 3625 178777 0.98 SELECT ...



從視圖 V$SQLAREA 中，我們可以立即隔離所有具有高物理讀取的語句。這些語句可能並不一定低效或寫得不好，但恰恰是它們需要進一步調查或調整。

⑷ Oracle SQL條件順序對性能的影響有哪些

在實際操作中有人會問到關於Oracle資料庫中的Where子句的條件書寫順序的正確與否是否會對SQL性能有影響，我的直覺是沒有影響。 因為如果這個順序有影響，Oracle應該早就能夠做到自動優化，但一直沒有關於這方面的確鑿證據。在網上查到的文章，一般認為在RBO優化器模式下無影響（10G開始，預設為RBO優化器模式），而在CBO優化器模式下有影響，主要有兩種觀點： a.能使結果最少的條件放在最右邊，SQL執行是按從右到左進行結果集的篩選的； b.有人試驗表明，能使結果最少的條件放在最左邊，SQL性能更高。 查過oracle8到11G的在線文檔，關於SQL優化相關章節，沒有任何文檔說過where子句中的條件對SQL性能有影響，到底哪種觀點是對的，沒有一種確切的結論，只好自己來做實驗證明。結果表明，我們大家都知道Oracle SQL條件的相關執行是從右到左的，但條件的順序對SQL性能沒有影響。 實驗一：證明了SQL的語法分析是從右到左的 下面的試驗在9i和10G都可以得到相同的結果: 第1條語句執行不會出錯，第2條語句會提示除數不能為零。 Select'ok'FromDualWhere1/0=1And1=2;Select'ok'FromDualWhere1=2And1/0=1;證明了SQL的語法分析是從右到左的。 實驗二：證明了SQL條件的執行是從右到左的 droptabletemp; createtabletemp(t1varchar2(10),t2varchar2(10)); insertintotempvalues('zm','abcde'); insertintotempvalues('sz','1'); insertintotempvalues('sz','2');commit;select*fromtempwhereto_number(t2)1andt1='sz';select*fromtempwheret1='sz'andto_number(t2)1;在9i上執行, 第1條語句執行不會出錯，第2條語句會提示無效的數字 在10G上執行，兩條語句都不會出錯。 說明：9i上源碼天空 實驗三：證明了在10g上SQL條件的執行是從右到左的 CreateOrReplaceFunctionF1(v_InVarchar2)ReturnVarchar2IsBeginDbms_Output.Put_Line('execF1'); Returnv_In;EndF1;/CreateOrReplaceFunctionF2(v_InVarchar2)ReturnVarchar2IsBeginDbms_Output.Put_Line('execF2'); Returnv_In;EndF2;/SQL setserverouton;SQL