【Database】AIX系統下跟蹤開銷大的Oracle資料庫程式
方法一、
如果ORACLE的程式開銷比較大,我們可以用如下的方法來查詢具體的程式在幹什麼事情,例如我們要查詢程式ora_j000_ora92,PID=344612,可以使用下面的方法:
$su – oracle
SQL>sqlplus “/as sysdba”
SQL>oradebug setospid 344612
SQL>oradebug event 10046 trace name context forever, level 8
SQL>oradebug tracefile_name –這個命令我們獲得輸出檔案的絕對路徑和檔名
SQL>oradebug event 10046 trace name context off
$tkprof /opt/oracle/app/oracle/admin/ora92/bdump/ora92_j000_344612.trc tracepid.txt
$more tracepid.txt
在tracepid.txt中,我們就可以看到這個程式中具體執行的語句、過程等,以及所有的SQL的cpu消耗、物理讀、邏輯讀、執行計劃等資訊。
具體案例:
1,查詢CPU開銷最大的前20個程式
p550a:/#ps aux |head -20
USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND
root 57372 5.8 0.0 384 384 - A Apr 12 32949:41 wait
root 16680 5.7 0.0 384 320 - A Apr 12 32737:24 wait
root 61470 5.7 0.0 384 384 - A Apr 12 32521:24 wait
root 20778 5.7 0.0 384 384 - A Apr 12 32329:11 wait
root 8196 5.7 0.0 384 384 - A Apr 12 32328:37 wait
root 288 5.6 0.0 384 320 - A Apr 12 32191:06 wait
root 53274 5.6 0.0 384 384 - A Apr 12 32005:49 wait
root 12582 5.6 0.0 384 320 - A Apr 12 31925:35 wait
oracle 585936 0.6 1.0 96668 51780 - A 13:08:56 4:56 oracleTCSF21 (LO
oracle 639120 0.3 1.0 97340 52452 - A 14:26:20 0:47 oracleTCSF21 (LO
oracle 541008 0.2 1.0 104144 59256 - A 13:49:29 0:57 oracleTCSF21 (LO
root 188646 0.1 0.0 56724 7288 - A Apr 12 716:38 /oracle/product/
oracle 327758 0.1 1.0 107644 55236 - A Apr 12 418:13 ora_lms0_TCSF21
oracle 327990 0.1 1.0 107648 55256 - A Apr 12 415:57 ora_lms1_TCSF21
root 561318 0.0 0.0 1648 1668 - A 11:34:16 0:47 /usr/dt/bin/dtte
root 139706 0.0 0.0 784 472 - A Apr 12 172:23 /bin/sh /etc/ini
oracle 319562 0.0 1.0 107168 46120 - A Apr 12 85:43 ora_lmon_TCSF21
oracle 159996 0.0 0.0 13620 3424 - A Apr 12 79:37 /oracle/product/
oracle 303364 0.0 1.0 107168 46144 - A Apr 12 71:36 ora_lmon_TCSF1
2,準備跟蹤開銷最大的Oracle資料庫程式585936 的具體執行語句等資訊
p550a:/#su - oracle
p550a:/home/oracle$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 31 15:00:22 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> oradebug setospid 585936
Oracle pid: 41, Unix process pid: 585936, image: oracle@p550a
3,啟用10046跟蹤事件
SQL> oradebug event 10046 trace name context forever, level 8
Statement processed.
4,獲得引數檔案的絕對路徑和檔名
SQL> oradebug tracefile_name
/oracle/admin/TCSF2/udump/tcsf21_ora_585936.trc
5,關閉10046跟蹤事件
SQL> oradebug event 10046 trace name context off
Statement processed.
6,使用Oracle tkprof工具格式化跟蹤檔案
p550a:/home/oracle$tkprof /oracle/admin/TCSF2/udump/tcsf21_ora_585936.trc trace41.txt
TKPROF: Release 10.2.0.4.0 - Production on Thu May 31 15:02:18 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
7,檢視格式化後的跟蹤檔案
p550a:/home/oracle$vi trace41.txt
TKPROF: Release 10.2.0.4.0 - Production on Thu May 31 15:02:18 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: /oracle/admin/TCSF2/udump/tcsf21_ora_585936.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
Trace file: /oracle/admin/TCSF2/udump/tcsf21_ora_585936.trc
Trace file compatibility: 10.01.00
Sort options: default
2 sessions in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
48 lines in trace file.
0 elapsed seconds in trace file.
方法二、
另外,我們也可以執行下面的語句檢視程式具體執行的SQL語句的文字:
SELECT /*+ ORDERED */ sql_text FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value),
DECODE (sql_hash_value,0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid'))
ORDER BY piece ASC
具體案例:
1,查詢CPU開銷最大的前20個程式
p550a:/#ps aux |head -20
USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND
root 57372 5.8 0.0 384 384 - A Apr 12 32970:07 wait
root 16680 5.7 0.0 384 320 - A Apr 12 32758:07 wait
root 61470 5.7 0.0 384 384 - A Apr 12 32541:32 wait
root 20778 5.7 0.0 384 384 - A Apr 12 32349:41 wait
root 8196 5.7 0.0 384 384 - A Apr 12 32348:53 wait
root 288 5.6 0.0 384 320 - A Apr 12 32211:46 wait
root 53274 5.6 0.0 384 384 - A Apr 12 32025:52 wait
root 12582 5.6 0.0 384 320 - A Apr 12 31946:04 wait
oracle 668064 0.2 1.0 96696 51848 - A 15:07:02 0:33 oracleTCSF21 (LO
oracle 544788 0.1 1.0 104296 59448 - A 15:36:58 0:04 oracleTCSF21 (LO
root 188646 0.1 0.0 56724 7288 - A Apr 12 717:04 /oracle/product/
oracle 541008 0.1 1.0 104144 59296 - A 13:49:29 0:57 oracleTCSF21 (LO
oracle 327758 0.1 1.0 107644 55276 - A Apr 12 419:10 ora_lms0_TCSF21
oracle 327990 0.1 1.0 107648 55296 - A Apr 12 416:54 ora_lms1_TCSF21
root 561318 0.0 0.0 1684 1704 - A 11:34:16 0:57 /usr/dt/bin/dtte
oracle 651748 0.0 1.0 94348 49500 - A 15:38:05 0:01 ora_j000_TCSF21
oracle 622600 0.0 1.0 98168 53320 - A 15:20:08 0:04 oracleTCSF21 (LO
root 139706 0.0 0.0 784 472 - A Apr 12 172:29 /bin/sh /etc/ini
oracle 319562 0.0 1.0 107168 46160 - A Apr 12 85:48 ora_lmon_TCSF21
2,準備跟蹤開銷最大的Oracle資料庫程式668064的具體執行語句等資訊
p550a:/home/oracle$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 31 15:43:38 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SELECT /*+ ORDERED */ sql_text FROM v$sqltext a
2 WHERE (a.hash_value, a.address) IN (
3 SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value),
DECODE (sql_hash_value,0, prev_sql_addr, sql_address)
FROM v$session b
4 5 6 WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid'))
ORDER BY piece ASC 7 8 9 ;
Enter value for pid: 668064
old 8: WHERE c.spid = '&pid'))
new 8: WHERE c.spid = '668064'))
SQL_TEXT
----------------------------------------------------------------
SELECT 1 FROM DUAL
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/14359/viewspace-731938/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle資料庫跟蹤SQLOracle資料庫SQL
- aix下oracle資料庫管理AIOracle資料庫
- sql server跟蹤資料庫SQLServer資料庫
- 資料庫資料跟蹤記錄資料庫
- AIX下使用dbca建立oracle資料庫AIOracle資料庫
- 新增時--sqlserver資料庫跟蹤SQLServer資料庫
- AIX系統崩潰後oracle資料庫的恢復方法AIOracle資料庫
- Dapper,大規模分散式系統的跟蹤系統APP分散式
- Windows下開源缺陷跟蹤系統mantis安裝指南Windows
- HP下對程式的跟蹤
- 分散式系統的跟蹤系統分散式
- 【LOG】Oracle資料庫清理日誌、跟蹤檔案利器Oracle資料庫
- Oracle中如何跟蹤SQL或資料庫操作 [final]OracleSQL資料庫
- Zipkin開源分散式跟蹤系統分散式
- ORACLE 系統統計資料和CPU開銷模型Oracle模型
- Linux系統下的Oracle資料庫程式設計詳解LinuxOracle資料庫程式設計
- AIX 下oracle 資料庫記憶體優化AIOracle資料庫記憶體優化
- Dapper:谷歌的大規模分散式跟蹤系統APP谷歌分散式
- 檢查資料庫是否啟用了跟蹤資料庫
- Oracle事件跟蹤及結構資料dumpOracle事件
- 檢視Oracle Database 10g 的資料庫支援的作業系統OracleDatabase資料庫作業系統
- 清理Oracle資料庫大量的SYS使用者審計跟蹤檔案Oracle資料庫
- Oracle大型資料庫系統在AIX UNIX上的實戰詳解 答疑五 AIX中的磁碟Oracle資料庫AI
- ATC系統跟蹤事項
- Aix下使用rman備份Oracle RAC資料庫(轉)AIOracle資料庫
- oracle之 oracle database vault(資料庫保險庫)OracleDatabase資料庫
- oracle 10g 程式跟蹤命令Oracle 10g
- ORACLE 跟蹤工具Oracle
- [zt] oracle跟蹤檔案與跟蹤事件Oracle事件
- oracle跟蹤檔案與跟蹤事件(zt)Oracle事件
- oracle跟蹤檔案和跟蹤事件(zt)Oracle事件
- 利用Sql server 2005的資料庫觸發器開發的資料庫稽核追蹤系統SQLServer資料庫觸發器
- 高效利用/資源,選擇在AIX的檔案系統中安裝oracle資料庫。AIOracle資料庫
- 進銷存系統資料庫設計資料庫
- 使用10046跟蹤Oracle前映象資料讀Oracle
- ORACLE開啟自動跟蹤SQL 功能。OracleSQL
- Linux, AIX系統下批量kill程式LinuxAI
- 搭建資料追蹤系統