【Database】AIX系統下跟蹤開銷大的Oracle資料庫程式

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE本文介紹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

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

7，檢視格式化後的跟蹤檔案

p550a:/home/oracle$vi trace41.txt

TKPROF: Release 10.2.0.4.0 - Production on Thu May 31 15:02:18 2012

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

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/，如需轉載，請註明出處，否則將追究法律責任。

【Database】AIX系統下跟蹤開銷大的Oracle資料庫程式

相關文章