經驗分享:如何確認還有SESSION執行交易

問題描述

當Oracle執行DML時，我要怎麼確認交易尚未結束(Commit Or Rollback)

解決方式

1.建立兩個session，並針對資料表執行Insert

2.使用語法查詢交易狀態，查詢到有兩個Session正進行交易

select t1.sid, t1.username, t2.xidusn, t2.used_urec, t2.used_ublk

from v$session  t1, v$transaction t2

where t1.saddr = t2.ses_addr;

3.COMMIT再次查詢，已無任何交易

使用DMV(Dynamic Management View)找出目前正在執行的查詢

執行以下程式碼可以找出的資訊：

1.      是誰在執行，參照[主機名稱]與[登入名稱]。

2.      執行SQL指令，參照[SQL指令]。

3.      執行SQL指令的應用程式名稱，參照[執行程式名稱]。

4.      SQL指令目前執行多久，參照[執行時間]。

5.      BLOCK與Transaction的資訊，參照[目前執行SQL的transaction數目]與[等待類別]等。

SELECT

 b.session_id

,b.host_name  [主機名稱]

,b.login_name [登入名稱]

,a.status  [執行狀態]

,DB_NAME(database_id) AS [資料庫名稱]

,c.text AS [SQL指令]

,b.program_name [執行程式名稱]

,a.start_time   [SQL開始執行時間]

,a.wait_type    [等待類別]

,a.total_elapsed_time [執行時間]

,a.cpu_time           [CPU時間]

,a.logical_reads      [邏輯讀取]

,a.open_transaction_count [目前執行SQL的transaction數目]

,a.last_wait_type       [上次等待類別]

FROM sys.dm_exec_requests AS a

INNER JOIN sys.dm_exec_sessions AS b ON b.session_id = a.session_id

CROSS APPLY sys.dm_exec_sql_text( a.sql_handle) AS c

WHERE b.is_user_process=1 AND b.session_Id <> (@@SPID)

ORDER BY b.session_id

執行結果：

使用(Dynamic Management View)找出執行最久的查詢

使用sys.dm_exec_query_stats與sys.dm_exec_sql_text找出被Block最久的SQL指令，如果有些SQL指令Block太久，會早成其他SQL指令要等待它釋放資源才能完成工作，這樣會大大影響資料庫的效能，甚至引起死結，要快速找出Block最久的SQL指令使用DMV是一個快速的方法，以下的程式碼就是使用DMV找出執行最久的SQL指令。

SELECT TOP 10 [執行時間(秒)]=CAST((a.total_elapsed_time - a.total_worker_time) /1000000.0 AS DECIMAL(16,2))

,[執行次數]= a.execution_count

,[SQL指令]= b.text

FROM sys.dm_exec_query_stats A

CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b

WHERE a.total_elapsed_time > 0 AND b.text  NOT LIKE '%SCHEMA%'

ORDER BY 1 DESC

執行結果：

使用(Dynamic Management View)找出執行時間最久的查詢

找出執行時間最久的SQL指令有助於我們減少資料庫的負擔，因為執行時間過長的SQL指令占用資料庫的資源也很長，所出這些SQL指令後加以修改可以不但可以縮短執行時間也可以增加資料庫的效能。

程式碼如下：

SELECT TOP 10

  [總執行時間(秒)]               =CAST(a.total_elapsed_time / 1000000.0 AS DECIMAL(16, 2)) 

, [執行次數]                            =a.execution_count

, [平均執行時間(秒)]     =CAST(a.total_elapsed_time / 1000000.0 / a.execution_count AS DECIMAL(16, 2))

, [SQL指令]                            =SUBSTRING (b.text,(a.statement_start_offset/2) + 1,500)

FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b

WHERE a.total_elapsed_time > 0 AND B.[text] NOT LIKE '%SCHEMA_NAME%'--去除一些系統的SQL指令

ORDER BY [平均執行時間(秒)] DESC

執行結果：

使用DMV(Dynamic Management View)找出最消耗IO的查詢

通常SQL指令的效能不佳的原因有許多，其中的一個原因是因為大量的IO的緣故，所以增加SQL指令的效能方法之一就是減少IO的產生，為了要減少IO的產生第一件要務就是找處最花費IO的SQL指令，以下就是使用DMV找出最消耗IO的SQL指令。

--使用dmv找出最消耗IO的查詢

/*

logical_reads:查詢時從DATA CACHE讀取PAGE

physical_reads:查詢時從DATA CACHE讀取PAG從硬碟讀取資料

*/

SELECT TOP 10

 [IO總和] = (A.total_logical_reads + A.total_physical_reads+ A.total_physical_reads)

,[平均IO] = (A.total_logical_reads + A.total_physical_reads)/A.execution_count

,A.execution_count [執行次數]

,B.[text] [SQL指令]

FROM sys.dm_exec_query_stats A

CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) as B

WHERE B.[text] NOT LIKE '%SCHEMA_NAME(%'--去除系統的SQL指令

ORDER BY [IO總和] DESC

執行結果：

使用DMV(Dynamic Management View)監控記憶體

當資料庫有問題時第一個動作就是要先尋找問題點在哪裡。在SQL 2005之後的版本都有DMV，使用DMV(Dynamic Management View)可以幫助我們輕易的獲得資料庫的資訊讓我們可以更容易的管理資料庫。

管理SQL SERVER第一件事情就是要取得作業系統所擁有資源，如記憶體、CPU、硬碟(DISK)等，在瞭解作業系統所擁有資源後才可以精準地分析所擁有資源是否足夠，再來就是觀察每一個資料使用資源的情況，在這裡我使用DMV取得作業系統可用的資源，接著在取得每一個資料庫正在使用的記憶體資訊以及每一個SQL 指令所需要的記憶體資訊。

1.      使用dm_os_sys_info取得作業系統的CPU與記憶體。

--取得作業系統的cpu和記憶體的資訊

SELECT

cpu_count AS [邏輯CPU數],

hyperthread_ratio AS [邏輯和實體處理器數目的比率],

cpu_count/hyperthread_ratio AS [實體CPU數],

physical_memory_in_bytes/(1024*1024) AS [實體記憶體MB]

FROM sys.dm_os_sys_info;

執行結果：

2.      取得每一個資料庫所用到的記憶體。

--每個資料庫用到的記憶體

--sys.dm_os_buffer_descriptors :SQL Server 在buffer pool中所有data pages的資訊

--一個row就是一個page，一個page為kb。

SELECT

CASE database_id WHEN 32767 THEN 'ResourceDb'

ELSE db_name(database_id)    END AS [資料庫名稱],

CAST(COUNT(*) * 8.0 / (1024.0) AS  DECIMAL(16,2)) AS [記憶體使用量(MB)]

FROM sys.dm_os_buffer_descriptors

GROUP BY database_id

ORDER BY 2 DESC

執行結果：

3.      每個SQL指令所用到的記憶體

--每個SQL指令所用到的記憶體

SELECT DB_NAME() [資料庫名稱]

, a.required_memory_kb/1024.0 AS [執行此查詢所需的最小記憶體MB]

, a.max_used_memory_kb/1024.0 AS [到目前為止使用的最大實體記憶體MB]

, a.granted_memory_kb/1024.0  AS [實際授與的記憶體總數MB]

, a.used_memory_kb/1024.0 AS [目前使用的實體記憶體MB]

, a.query_cost [估計的查詢成本]

, a.dop [CPU平行處理程度]

, SQLCMD.[text] AS [SQL指令]

FROM sys.dm_exec_query_memory_grants AS a

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS SQLCMD

ORDER BY a.requested_memory_kb DESC ;

執行結果：