2024年3月5日 星期二
2021年8月12日 星期四
學習心得 : 查詢Oracle當前交易處理時間
問題描述
查詢Oracle當前交易處理時間
解決方式
1. 正執行交易指令
SELECT T1.SID,T1.SQL_ID, T1.USERNAME, T2.XIDUSN, T2.USED_UREC, T2.USED_UBLK,T2.START_TIME,SQL_TEXT
FROM V$SESSION T1, V$TRANSACTION T2 , V$SQL T3
WHERE T1.SADDR = T2.SES_ADDR AND T1.SQL_HASH_VALUE=T3.HASH_VALUE;
2. 交易語法執行完成但尚未COMMIT或ROLLBACK
SELECT T1.SID,T1.USERNAME, T2.XIDUSN, T2.USED_UREC, T2.USED_UBLK,T2.START_TIME
FROM V$SESSION T1, V$TRANSACTION T2
WHERE T1.SADDR = T2.SES_ADDR;
此外SQL DEVELOPER的Real-Time SQL Monitoring報表可查詢執行時間超過5秒的SQL指令，不論是否有交易，執行完成或與執行中都會紀錄於V$SQL_MONITOR，並保留一段時間。
2021年7月7日 星期三
經驗分享:如何確認還有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再次查詢，已無任何交易
難忘的經驗：建立INDEX後，讓SELECT效能變慢
1.情況說明：
朋友急call，告知我他的系統變得超慢，看了一下IO高的SQL，有大量的IO發生，查看一下執行計畫發現大量使用WorkTable(TempTable)讓效能低落。
2.溝通詢問：
Q1 詢問朋友有沒有修改程式碼?
A1 朋友說沒有
Q2 最近table schema有沒有異動
A2 朋友說沒有
Q3 table schema是除了異動欄位外，包含contact,Index都算
A3 朋友:有，Index我加了Index
3.如何解決：
記得之前看過一篇文章 ，文中有提到建立Index後影響其他語法查詢效能，再次確認這個Index是查詢報表用砍掉對業務沒有影響，就大膽的砍掉該Index，問題就瞬間解決了。
4.問題重現:
- 查詢語法
- 先建立IX_FIRST索引語法
- 執行查詢語法(IO為608)
- 建立IX_SECOND索引語法
- 再次執行查詢語法(IO為368495)
- 刪除IX_SECOND後IO又恢復為608(整體IO差了大概606倍)
2019年3月30日 星期六
筆記_如何使用SQL Profiler找出死結
一、 問題
當執行SQL指令產生1205的ERROR時，我要如何協助AP找到產生deadlock的SQL指令？
二、 解決方案
使用SQL
Profiler錄製，當死結發生時，SQL Profiler會提供Deadlock Graph提供Deadlock相關資訊。
三、 執行
1.
建立測試資料
建立兩個資料表DATA01與DATA02並產生測試資料。
2.
模擬死結發生
在SESSION1建立一筆交易，先更新DATA01資料表後的
15秒後才更新DATA02的資料表。
在SESSION2建立一筆交易，先更新DATA02資料表後的
15秒後才更新DATA01的資料表。
3.
使用SQL PROFILER錄製死結。
點選Locks的Deadlock Graph等事件。
最後死結發生，SESSION2被當作此次的犧牲者。
此時SQL PROFILERT偵測到死結產生Deadlock Graph
4.
解讀Deadlock Graph內容
檢視Deadlock Graph做邊藍色打X的部分是被犧牲的SESSION，中間長方形的是被鎖定的Resource，可能是資料表、PAGE或是單一資料列，而左右箭頭代表Resource被取得(Hold)與要求(Request)。
將Deadlock Graph使用XML的格式打開
可以看到更詳細的資訊，其中最下方的inputbuf代表與死結有關的SQL指令，而deadlock victim代表被犧牲的SESSION，有時候如果SQL指令太長(例如STORED
PROCEDURE)可以使用sqlhandle加上sys.dm_exec_sql_text產出完整的SQL語法。
小結：
當死結發生時可用SQL PROFILER去錄製死結，產生出來的Deadlock Graph可透過圖形或XML的格式取得產生死結的原因，通常inputbuf會顯示產生死結的SQL指令，如果SQL指令太長可以使用sys.dm_exec_sql_text找出完整的SQL指令。
2019年1月3日 星期四
筆記_SQL Server交易(Transaction)特性與交易隔離層級(Isolation Level)
上次討論到SQL Server使用的鎖定類型(lock types)有Shared(S)、Exclusive(X)與Update(U)，這次我們要討論這些鎖定類型與交易(Transaction)的關係，我們先討論一下甚麼是交易？交易可以看成一個工作單位，這個單位會完成多個行為，例如ATM的轉帳是一個交易單位，這個交易有兩個行為，從A帳號轉出500元給B帳號與B帳號收到500元，除此之外交易還必須符合ACID的特性(ACID為縮寫)。
1. 交易的特性摘要如下：
(1) Atomicity：交易內的行為只有兩種結果，全成功或全失敗，SQL SERVER要達成Atomicity時需將XACT_ABORT設定為ON。
(2) Consistency：SQL SERVER提供constraints 完成資料的完整性，例如：PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, and NOT NUL。
(3) Isolation：針對正在進行交易的資料，其他交易是不可讀取與異動的，簡單來說就是該筆資料會與其他交易隔離。例如：A交易發生時要將王小明年齡改從19歲改為20歲時，此時B交易是看不到也摸不到王小明這筆資料。
(4) Durability：交易完成後，資料無論如何都會留在資料庫內，SQL Server透過Write-ahead log的方式來實現Durability，也就是先將資料寫入LOG檔後再寫入資料檔，所以當發生意外時，可以靠著LOG的內容依序還原資料。
2. 常見的交易迷思：
Q1:當我們執行Insert/Update/Delete指令時沒有使用begin tran或commit等關鍵字時是不會有交易的。
ANS1：SQL SERVER預設是implicit transaction，所以是有交易的。implicit transaction在某些情境下是相當耗資源的，最常見的例子就是使用insert
into table values匯入10萬筆資料，implicit
transaction耗費資源的時間與log檔上都會多很多。
Q2:Select指令是沒有交易存在的？
ANS2：Ｓelect是有交易的喔，這部份會在後面的筆記說明。
Q3:如果使用NOLOCK等HINT是沒有交易的
ANS3：交易還是有的，只是我們將isolation層級降為read uncommitted。
3. Isolation Level
每個交易都有自己的Isolation Level，SQL SERVER有4種悲觀（Pessimistic）的isolation level，如下表：
Isolation層級
|
Share Locks 行為
|
Table Hint
|
Read uncommitted,
|
不需要Share locks
|
NoLock
|
read committed,
|
Share locks在Commit完後釋放
|
READ committed
|
repeatable read
|
Share locks在交易結束後釋放
|
repeatableread
|
serializable
|
Share Locks的範圍變大(RANGE locks)，且直到交易結束才釋放。
|
HOLDLOCK
|
除了Read uncommitted之外，每個Isolation Level在資料寫入時會鎖住(BLOCK)讀取行為。此外不論Isolation Level為何，資料異動時的互斥鎖(exclusive lock)都會在持續到交易結束，所以上述四種Isolation Level的差異主要是在於Share Locks的行為。
Read uncommitted：沒有shared locks，所以當其他還沒有COMMIT交易在修改資料時，其他交易是可以讀取該筆資料的，所以當A交易異動資料且尚未commit時，B交易是可以讀取A交易異動過的資料。
Read committed：shared locks會在讀取完資料後就釋放，當A交易讀取資料時，B交易是無法修改被A交易資料的正在讀取的資料，但是一旦讀取完成shared locks 就會釋放(此時A交易尚未完成)，B交易就可以修改A交易之前讀取的資料。
Repeatable read：shared locks交易結束後才會釋放，所以只要A交易讀取資料時，除非到整個交易結束，否則B交易無法法修改被讀取的資料。
Serializable ：shared locks會持續到交易結束，而且shared locks範圍會變大。所以只要A交易讀取資料時，除非整個交易結束，否則B交易無法修改讀取的資料與被讀取資料的特定範圍是無法進行新增的行為。
範例語法1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION Transaction2
SELECT *
FROM TestTable
WHERE Value='C'
GO
COMMIT
範例語法2
SELECT * FROM TestTable WITH (HOLDLOCK)
WHERE Value='C'
4.結論：
交易的Isolation Level其實是很容易理解的，我們只要專注在不同Level上shared locks的差異即可，此外我們目前只討論悲觀(Pessimistic)的 Isolation Level，至於SQL SERVER的2種樂觀(optimisitic)的Isolation Level：Snapshot and read
committed snapshot又是另一個故事了。
參考：
2019年1月1日 星期二
Python_讀取股票歷年經營績效_以遠東新1402為例_04_使用pandas畫直線圖
接著試驗pandas的繪圖功能，花了我好久的時間，原來要吃圖形的X與Y軸要吃資料形態要吃數字的Series，畫起圖來才會比較順手。
使用了pandas的plot繪製直線圖，程式碼修改如下：
# -*- coding: utf-8 -*-
"""
Created on Wed Jan 2 13:38:28 2019
@author: ryoliu
"""
import requests
from bs4 import BeautifulSoup
import pandas as pd
url = 'https://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=1402'
headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) \
Chrome/65.0.3325.181 Safari/537.36'}
r = requests.get(url, headers = headers)
r.encoding = 'utf-8'
soup = BeautifulSoup(r.text, 'html.parser')
rows = soup.find('div', {"id": "divFinDetail"}).find('table', {"class": "solid_1_padding_4_0_tbl"})
dfs = pd.read_html(str(rows))
df=pd.DataFrame(dfs[0]).iloc[3:19,0:3]
df.columns= ['years','stockcost','summarize']
df.stockcost=pd.Series(pd.to_numeric(df.stockcost))
df.years=pd.Series(pd.to_numeric(df.years))
df.plot(x='years',y='stockcost', kind='line')
執行結果如下：
訂閱：
文章 (Atom)