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

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

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

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

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

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

查看存在TABLE ACCESS FULL行為的SQL

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

Column

Datatype

Description

SQL_TEXT

VARCHAR2(1000)

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

SQL_FULLTEXT

CLOB

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

SQL_ID

VARCHAR2(13)

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

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)打開(kāi)的用戶數(shù)。

FETCHES

NUMBER

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

EXECUTIONS

NUMBER

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

PX_SERVERS_EXECUTIONS

NUMBER

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

END_OF_FETCH_COUNT

NUMBER

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

USERS_EXECUTING

NUMBER

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

LOADS

NUMBER

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

FIRST_LOAD_TIME

VARCHAR2(19)

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

INVALIDATIONS

NUMBER

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

PARSE_CALLS

NUMBER

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

DISK_READS

NUMBER

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

DIRECT_WRITES

NUMBER

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

BUFFER_GETS

NUMBER

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

APPLICATION_WAIT_TIME

NUMBER

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

CONCURRENCY_WAIT_TIME

NUMBER

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

CLUSTER_WAIT_TIME

NUMBER

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

USER_IO_WAIT_TIME

NUMBER

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

PLSQL_EXEC_TIME

NUMBER

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

JAVA_EXEC_TIME

NUMBER

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

ROWS_PROCESSED

NUMBER

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

COMMAND_TYPE

NUMBER

Oracle命令類型定義

OPTIMIZER_MODE

VARCHAR2(10)

SQL語(yǔ)句執(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語(yǔ)句第一次被解析時(shí)正在執(zhí)行的模塊名稱，該名稱可通過(guò)調(diào)用

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

MODULE_HASH

NUMBER

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

SERIALIZABLE_ABORTS

NUMBER

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

CPU_TIME

NUMBER

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

ELAPSED_TIME

NUMBER

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

OBJECT_STATUS

VARCHAR2(19)

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

LAST_LOAD_TIME

VARCHAR2(19)

查詢計(jì)劃被加載到類庫(kù)緩存的時(shí)間

IS_OBSOLETE

VARCHAR2(1)

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

LAST_ACTIVE_TIME

DATE

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

IO_INTERCONNECT_BYTES

NUMBER

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

PHYSICAL_READ_REQUESTS

NUMBER

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

PHYSICAL_READ_BYTES

NUMBER

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

PHYSICAL_WRITE_REQUESTS

NUMBER

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

PHYSICAL_WRITE_BYTES

NUMBER

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

OPTIMIZED_PHY_READ_REQUESTS

NUMBER

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

LOCKED_TOTAL

NUMBER

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

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

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

列

描述

SID

會(huì)話ID

SERIAL#

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

USER#

Oracle用戶ID

USERNAME

Oracle用戶名稱

COMMAND

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

LOCKWAIT

會(huì)話正在等待的鎖的地址。NULL值表示沒(méi)有等待鎖。

STATUS

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

SCHEMA#

Schema用戶ID

SCHEMANAME

Schema用戶名稱

OSUSER

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

PROCESS

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

MACHINE

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

PORT

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

TERMINAL

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

PROGRAM

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

TYPE

會(huì)話類型

SQL_HASH_VALUE

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

SQL_ID

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

SQL_EXEC_START

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

SQL_EXEC_ID

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

LAST_CALL_ET

如果會(huì)話STATUS當(dāng)前為ACTIVE，則該值表示自會(huì)話變?yōu)榛顒?dòng)狀態(tài)以來(lái)經(jīng)過(guò)的時(shí)間（以秒為單位），如果會(huì)話STATUS當(dāng)前為INACTIVE，則該值表示自會(huì)話變?yōu)榉腔顒?dòng)狀態(tài)以來(lái)經(jīng)過(guò)的時(shí)間（以秒為單位）

EVENT

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

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

列

描述

OBJECT_ID

正被鎖住的對(duì)象ID

SESSION_ID

會(huì)話ID

ORACLE_USERNAME

Oracle用戶名

OS_USER_NAME

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

PROCESS

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

LOCKED_MODE

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

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