Oracle 死鎖與慢查詢總結(jié)

說明：如果lockwait值不為空(形如0000001F83D6C748)，并且status為ACTIVE，則說明存在死鎖event 最近等待或正在等待的資源/事件：enq: TX - row lock contention ：按模式6等待TX：當(dāng)會話等待另一個會話已持有的行級鎖時發(fā)生該事件，即某個用戶正在更新、刪除另一個會話希望更新、刪除的行時，會發(fā)生這種情況。這種類型的TX排隊等待對應(yīng)于等待事件 enq:TX - row lock contention。解決方案：已經(jīng)持有鎖的第一個會話執(zhí)行提交或回

查詢執(zhí)行最慢的SQL

說明：為什么不從v$sql統(tǒng)計信息？這是因?yàn)榧幢阆嗤腟QL，每次執(zhí)行耗時也可能不一樣，所以，考慮求平均值，所以需要對SQL分組統(tǒng)計，SQL_TEXT相同，大概率為同一條SQL，所以考慮從按SQL_TEXT分組統(tǒng)計的v$sqlarea讀取信息。當(dāng)然，出于嚴(yán)謹(jǐn)?shù)目紤]，也可以不分組統(tǒng)計，把v$sqlarea替換成v$sql就好了。

查詢SQL執(zhí)行次數(shù)，按次數(shù)降序排序

注意：之所以從v$sql獲取統(tǒng)計數(shù)據(jù)，是因?yàn)檫@里未對SQL_TEXT做GROUP BY（SQL_TEXT是完整SQL文本前1000個字符，存在截斷的可能，按在這個統(tǒng)計可能不準(zhǔn)確），就針對每條SQL(不管是否相同)單獨(dú)統(tǒng)計，當(dāng)然，也可以考慮按SQL_TEXT分組統(tǒng)計，把v$sql改成v$sqlarea就好了。

查看存在TABLE ACCESS FULL行為的SQL

V$SQL列出了關(guān)于共享SQL區(qū)，不含GROUP BY子句的統(tǒng)計，輸入的原始SQL文本的每個子項(xiàng)各占一行。通常在查詢執(zhí)行結(jié)束時更新V$SQL中展示的統(tǒng)計信息，然而針對長耗時查詢，每5秒更新一次。這樣更容易在運(yùn)行期間查看長時間運(yùn)行的SQL語句帶來的影響

Column

Datatype

Description

SQL_TEXT

VARCHAR2(1000)

當(dāng)前游標(biāo)的SQL文本的前1000個字符

SQL_FULLTEXT

CLOB

以方式展示的SQL語句全文?？墒褂迷摿袡z索SQL語句全文，而無需連接V$SQLTEXT動態(tài)性能視圖。

SQL_ID

VARCHAR2(13)

類庫緩存中父游標(biāo)的SQL標(biāo)識。

SHARABLE_MEM

NUMBER

子游標(biāo)使用的共享內(nèi)存量(字節(jié)為單位)

PERSISTENT_MEM

NUMBER

子游標(biāo)生存周期內(nèi)使用的固定內(nèi)存量(字節(jié)為單位)

RUNTIME_MEM

NUMBER

子游標(biāo)運(yùn)行期間所需的固定內(nèi)存量(字節(jié)為單位)

SORTS

NUMBER

子游標(biāo)完成的排序次數(shù)

LOADED_VERSIONS

NUMBER

指示是否已加載上下文堆，1表示已加載，0表示未加載。

OPEN_VERSIONS

NUMBER

指示子游標(biāo)是否被鎖定，1表示被鎖定，0表示未被鎖定

USERS_OPENING

NUMBER

任意子游標(biāo)打開的用戶數(shù)。

FETCHES

NUMBER

與SQL語句關(guān)聯(lián)的FETCHES的次數(shù)

EXECUTIONS

NUMBER

自從對象被加載到類庫緩存后，該對象被執(zhí)行次數(shù)。

PX_SERVERS_EXECUTIONS

NUMBER

并行執(zhí)行服務(wù)器執(zhí)行的總次數(shù) ( 當(dāng)語句從未被并行執(zhí)行時為0)

END_OF_FETCH_COUNT

NUMBER

游標(biāo)被加載到類庫緩存后，被完整執(zhí)行的次數(shù)。當(dāng)游標(biāo)部分執(zhí)行時，此統(tǒng)計值不會增加，不管是因?yàn)樵趫?zhí)行過程中失敗，還是在關(guān)閉或重新執(zhí)行游標(biāo)之前只提取了此游標(biāo)生成的前幾行。根據(jù)定義，END_of_FETCH_COUNT列的值應(yīng)小于或等于EXECUTIONS列的值。

USERS_EXECUTING

NUMBER

執(zhí)行語句的用戶數(shù)

LOADS

NUMBER

對象被加載或者重新加載的次數(shù)

FIRST_LOAD_TIME

VARCHAR2(19)

父游標(biāo)的創(chuàng)建時間

INVALIDATIONS

NUMBER

子游標(biāo)無效的次數(shù)

PARSE_CALLS

NUMBER

子游標(biāo)的解析調(diào)用次數(shù)

DISK_READS

NUMBER

子游標(biāo)的磁盤讀取次數(shù)

DIRECT_WRITES

NUMBER

子游標(biāo)的直接寫次數(shù)

BUFFER_GETS

NUMBER

子游標(biāo)的獲取緩存區(qū)次數(shù)

APPLICATION_WAIT_TIME

NUMBER

應(yīng)用等待時間(微秒為單位)

CONCURRENCY_WAIT_TIME

NUMBER

并發(fā)等待時間(微秒為單位)

CLUSTER_WAIT_TIME

NUMBER

集群等待時間(微秒為單位)

USER_IO_WAIT_TIME

NUMBER

用戶I/O等待時間(微秒為單位)

PLSQL_EXEC_TIME

NUMBER

PL/SQL執(zhí)行時間(微秒為單位)

JAVA_EXEC_TIME

NUMBER

Java執(zhí)行時間(微秒為單位)

ROWS_PROCESSED

NUMBER

已解析SQL語句返回的總行數(shù)

COMMAND_TYPE

NUMBER

Oracle命令類型定義

OPTIMIZER_MODE

VARCHAR2(10)

SQL語句執(zhí)行模式(優(yōu)化器模型)

OPTIMIZER_COST

NUMBER

優(yōu)化器給出的查詢成本

PARSING_USER_ID

NUMBER

最初構(gòu)建此子游標(biāo)的用戶ID

PARSING_SCHEMA_ID

NUMBER

最初構(gòu)建子游標(biāo)使用的模式ID

PARSING_SCHEMA_NAME

VARCHAR2(30)

最初構(gòu)建子游標(biāo)使用的模式名稱

SERVICE

VARCHAR2(64)

服務(wù)名稱

SERVICE_HASH

NUMBER

SERVICE列展示的服務(wù)名稱的哈希值

MODULE

VARCHAR2(64)

SQL語句第一次被解析時正在執(zhí)行的模塊名稱，該名稱可通過調(diào)用

DBMS_APPLICATION_INFO.SET_MODULE進(jìn)行設(shè)置。

MODULE_HASH

NUMBER

MODULE列展示的模塊名稱的哈希值

SERIALIZABLE_ABORTS

NUMBER

每個游標(biāo)事務(wù)序列化失敗并產(chǎn)生ORA-08177錯誤的次數(shù)

CPU_TIME

NUMBER

此游標(biāo)用于解析，執(zhí)行，獲取(fetch)的CPU耗時 (以微秒為單位)

ELAPSED_TIME

NUMBER

此游標(biāo)用于解析，執(zhí)行，獲取的占用時間(以微秒為單位)。如果游標(biāo)采用并行執(zhí)行，則ELAPSED_TIME 為查詢協(xié)調(diào)器及所有并行查詢slave進(jìn)程的累計占用時間。

OBJECT_STATUS

VARCHAR2(19)

游標(biāo)的狀態(tài)：VALID - 有效，無錯誤授權(quán) VALID_AUTH_ERROR - 有效，有授權(quán)錯誤授權(quán)。VALID_COMPILE_ERROR - 有效, 有編譯錯誤授權(quán)。VALID_UNAUTH - 有效，未授權(quán)。INVALID_UNAUTH - 無效，未授權(quán)。INVALID - 無效，未授權(quán)，但保留時間戳。

LAST_LOAD_TIME

VARCHAR2(19)

查詢計劃被加載到類庫緩存的時間

IS_OBSOLETE

VARCHAR2(1)

指示游標(biāo)是否已過時，是(Y) 或者否(N)。如果子游標(biāo)的數(shù)量太大，則可能發(fā)生這種情況。

LAST_ACTIVE_TIME

DATE

查詢計劃最后活躍時間(即完成SQL解析的時間，可以當(dāng)做SQL最后執(zhí)行的時間)

IO_INTERCONNECT_BYTES

NUMBER

Oracle 數(shù)據(jù)庫和存儲系統(tǒng)之間交換的I/O字節(jié)數(shù)。

PHYSICAL_READ_REQUESTS

NUMBER

被監(jiān)控SQL發(fā)起的物理讀I/O請求

PHYSICAL_READ_BYTES

NUMBER

被監(jiān)控SQL從磁盤讀取的字節(jié)數(shù)。

PHYSICAL_WRITE_REQUESTS

NUMBER

被監(jiān)控SQL發(fā)起的物理寫I/O請求。

PHYSICAL_WRITE_BYTES

NUMBER

被監(jiān)控SQL寫入磁盤的字節(jié)數(shù)

OPTIMIZED_PHY_READ_REQUESTS

NUMBER

被監(jiān)控SQL從數(shù)據(jù)庫智能閃存緩存發(fā)起的物理讀取I/O請求數(shù)

LOCKED_TOTAL

NUMBER

子游標(biāo)被鎖定的總次數(shù)

V$SQLAREA顯示共享SQL區(qū)域的統(tǒng)計信息，每條SQL字符串為一行。它提供內(nèi)存中、已解析并準(zhǔn)備執(zhí)行的SQL語句的統(tǒng)計信息。V$SQLAREA和V$SQL兩個視圖的不同之處在于，V$SQL中為每一條SQL保留一個條目，而V$SQLAREA中根據(jù)SQL_TEXT進(jìn)行GROUP BY，通過VERSION_COUNT計算子指針的個數(shù)

V$SESSION 顯示當(dāng)前會話的會話信息，常見視圖字段及字段描述說明如下：

列

描述

SID

會話ID

SERIAL#

會話序列號。用于唯一標(biāo)識會話的對象。如果會話結(jié)束，而另一個會話以相同的會話ID開始，則保證將會話級命令應(yīng)用于當(dāng)前會話的對象。

USER#

Oracle用戶ID

USERNAME

Oracle用戶名稱

COMMAND

正在執(zhí)行的命令（解析的最后一條語句）?？梢酝ㄟ^運(yùn)行以下SQL查詢來查找此COMMAND列中返回的任何值 n 的命令名：SELECT Command_name FROM v$sqlcommand WHERE command_type=n COMMAND”列值為 0，則表示該命令未記錄在V$SESSION中。

LOCKWAIT

會話正在等待的鎖的地址。NULL值表示沒有等待鎖。

STATUS

會話狀態(tài)：ACTIVE-會話當(dāng)前正在執(zhí)行SQL，INACTIVE-處于非活動狀態(tài)且沒有配置限制或尚未超過配置的限制的會話。KILLED-標(biāo)記為被終止的會話。CACHED-為Oracle XA臨時緩存的會話。SNIPED-超出某些配置限制(例如，為資源管理器消費(fèi)者組指定的資源限制或用戶配置文件中指定的idle_time)的非活動會話。此類會話將不允許再次激活。

SCHEMA#

Schema用戶ID

SCHEMANAME

Schema用戶名稱

OSUSER

操作系統(tǒng)客戶端用戶名稱

PROCESS

操作系統(tǒng)客戶端進(jìn)程ID

MACHINE

操作系統(tǒng)機(jī)器名稱

PORT

客戶端進(jìn)程端口號

TERMINAL

操作系統(tǒng)終端名稱

PROGRAM

操作系統(tǒng)進(jìn)程名稱

TYPE

會話類型

SQL_HASH_VALUE

配合 SQL_HASH_VALUE使用，用于標(biāo)識當(dāng)前正在執(zhí)行的SQL語句。

SQL_ID

當(dāng)前正在執(zhí)行的SQL語句的ID

SQL_EXEC_START

會話當(dāng)前執(zhí)行的SQL開始執(zhí)行的時間；如果SQL_ID為NULL，則為NULL

SQL_EXEC_ID

SQL執(zhí)行標(biāo)識。 如果SQL_ID為NULL或者該SQL執(zhí)行還未啟動，則為NULL

LAST_CALL_ET

如果會話STATUS當(dāng)前為ACTIVE，則該值表示自會話變?yōu)榛顒訝顟B(tài)以來經(jīng)過的時間（以秒為單位），如果會話STATUS當(dāng)前為INACTIVE，則該值表示自會話變?yōu)榉腔顒訝顟B(tài)以來經(jīng)過的時間（以秒為單位）

EVENT

如果會話當(dāng)前正在等待，則為會話正在等待的資源或事件。如果會話不在等待中，則為會話最近等待的資源或事件。查閱: "Oracle Wait Events"

V$LOCKED_OBECT列出了系統(tǒng)上每個事務(wù)獲取的所有鎖。它顯示了哪些會話在什么對象上以及在什么模式下持有DML鎖（即TM類型的隊列）。視圖常見字段及描述如下：

列

描述

OBJECT_ID

正被鎖住的對象ID

SESSION_ID

會話ID

ORACLE_USERNAME

Oracle用戶名

OS_USER_NAME

操作系統(tǒng)用戶名

PROCESS

操作系統(tǒng)進(jìn)程ID

LOCKED_MODE

鎖模式。此列的數(shù)值映射到表鎖的鎖模式的這些文本值：0-無：請求但尚未獲得的鎖；1-NULL；2-ROWS_S（SS）：行共享鎖；3-Row_X（SX）：行排它鎖；4-Share（S）：共享表鎖；5-S/Row-X（SSX）：共享行排它鎖；6-獨(dú)占（X）：排它表鎖。另請參閱：Oracle數(shù)據(jù)庫概念，以獲取有關(guān)表鎖鎖模式的更多信息

下一篇：Oracle 遷移至 MySQL、PG等分布式數(shù)據(jù)庫，可能遇到的12個典型問題