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

landf發表於2012-06-05
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE本文介紹AIX系統下跟蹤開銷大的Oracle資料庫程式的具體執行語句等資訊的方法。

方法一、

如果ORACLE的程式開銷比較大,我們可以用如下的方法來查詢具體的程式在幹什麼事情,例如我們要查詢程式ora_j000_ora92PID=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中,我們就可以看到這個程式中具體執行的語句、過程等,以及所有的SQLcpu消耗、物理讀、邏輯讀、執行計劃等資訊。

 

具體案例:

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

相關文章