Monitoring
查找 APL 堆表插入的原始碼和詳細資訊
我一直在使用以下命令在 HP-UX（安騰）上的 Sybase ASE 15.7（SP139）上執行系統監視器（sysmon）：
sp_sysmon "00:10:00" go觀察到一天中的任何時間插入次數都很高，無法找到它的來源和詳細資訊，例如表名、數據庫名、程序名等。下面是 sysmon 在凌晨時的範例輸出空載：
Transaction Profile ------------------- Transaction Summary per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Committed Xacts 10.7 n/a 6390 n/a Transaction Detail per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Inserts Fully Logged APL Heap Table 58665.7 5508.5 35199448 100.0 % APL Clustered Table 0.0 0.0 7 0.0 % Data Only Lock Table 13.2 1.2 7918 0.0 % Fast Bulk Insert 0.0 0.0 0 0.0 % Fast Log Bulk Insert 0.0 0.0 0 0.0 % Minimally Logged APL Heap Table 0.0 0.0 0 0.0 % APL Clustered Table 0.0 0.0 0 0.0 % Data Only Lock Table 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total Rows Inserted 58679.0 5509.8 35207373 100.0 %看起來插入正在 tempdb 中發生，但是這些插入有點麻煩，感謝專家對此的建議。
我找到了解決問題中發布的問題的間接方法，因為我找不到任何可以監視數據庫級別發生的所有插入的工具或腳本，我分析了每天 24 小時執行並正在使用的過程（查詢）生產數據庫中的最大資源。為了模擬，我在測試環境中使用其所有參數手動執行了該過程，並使 sysmon 在後台執行的時間與執行過程所需的時間相同。我設法在測試環境中收集了接近相同數量的插入。
為了證明這是唯一每秒執行如此大量插入的程序，我還在有問題的程序之前和之後執行了 sysmon 並擷取了該數量的插入，它是數百甚至不是數千。
Sysmon 在過程執行期間：
Transaction Profile ------------------- Transaction Summary per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Committed Xacts 0.1 n/a 9 n/a Transaction Detail per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Inserts Fully Logged APL Heap Table 89575.5 895754.9 8061794 100.0 % APL Clustered Table 0.0 0.0 0 0.0 % Data Only Lock Table 0.0 0.0 0 0.0 % Fast Bulk Insert 0.0 0.0 0 0.0 % Fast Log Bulk Insert 0.0 0.0 0 0.0 % Minimally Logged APL Heap Table 0.0 0.0 0 0.0 % APL Clustered Table 0.0 0.0 0 0.0 % Data Only Lock Table 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total Rows Inserted 89575.5 895754.9 8061794 100.0 %Sysmon 程序執行前/後（未執行時）：
Transaction Profile ------------------- Transaction Summary per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Committed Xacts 0.1 n/a 13 n/a Transaction Detail per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Inserts Fully Logged APL Heap Table 127.6 883.7 11488 100.0 % APL Clustered Table 0.0 0.0 0 0.0 % Data Only Lock Table 0.0 0.0 0 0.0 % Fast Bulk Insert 0.0 0.0 0 0.0 % Fast Log Bulk Insert 0.0 0.0 0 0.0 % Minimally Logged APL Heap Table 0.0 0.0 0 0.0 % APL Clustered Table 0.0 0.0 0 0.0 % Data Only Lock Table 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total Rows Inserted 127.6 883.7 11488 100.0 %這樣，我設法找到了每秒進行 60k 插入的程序。另外要補充的是，這些插入是由於表的不正確連接（包含大量記錄），並且插入的數量是工作表的形式，如下所示：
Table: Worktable1 scan count 1, logical reads: (regular=88517 apf=0 total=88517), physical reads: (regular=52316 apf=0 total=52316), apf IOs used=0 Table: Worktable1 scan count 0, logical reads: (regular=8092124 apf=0 total=8092124), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable2 scan count 1, logical reads: (regular=11 apf=0 total=11), physical reads: (regular=2 apf=0 total=2), apf IOs used=0 Table: Worktable2 scan count 0, logical reads: (regular=10 apf=0 total=10), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable33 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable9 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable7 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable4 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable1 scan count 0, logical reads: (regular=8180641 apf=0 total=8180641), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable17 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable15 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable12 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable31 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable29 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable27 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable25 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable23 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable21 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable19 scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable2 scan count 0, logical reads: (regular=21 apf=0 total=21), physical reads: (regular=0 apf=0 total=0), apf IOs used=0我希望以上內容可以幫助其他人，以防他們遇到我遇到的類似問題。