oracle数据库看开销,【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

USERPID %CPU %MEMSZRSSTTY STATSTIMETIME COMMAND

root573725.80.0384384- AApr 12 32949:41 wait

root166805.70.0384320- AApr 12 32737:24 wait

root614705.70.0384384- AApr 12 32521:24 wait

root207785.70.0384384- AApr 12 32329:11 wait

root81965.70.0384384- AApr 12 32328:37 wait

root2885.60.0384320- AApr 12 32191:06 wait

root532745.60.0384384- AApr 12 32005:49 wait

root125825.60.0384320- AApr 12 31925:35 wait

oracle5859360.61.0 96668 51780- A13:08:564:56 oracleTCSF21 (LO

oracle6391200.31.0 97340 52452- A14:26:200:47 oracleTCSF21 (LO

oracle5410080.21.0 104144 59256- A13:49:290:57 oracleTCSF21 (LO

root1886460.10.0 56724 7288- AApr 12 716:38 /oracle/product/

oracle3277580.11.0 107644 55236- AApr 12 418:13 ora_lms0_TCSF21

oracle3279900.11.0 107648 55256-

AApr 12 415:57 ora_lms1_TCSF21

root5613180.00.0 1648 1668- A11:34:160:47 /usr/dt/bin/dtte

root1397060.00.0784472- AApr 12 172:23 /bin/sh /etc/ini

oracle3195620.01.0 107168 46120- AApr 12 85:43 ora_lmon_TCSF21

oracle1599960.00.0 13620 3424- AApr 12 79:37 /oracle/product/

oracle3033640.01.0 107168 46144- AApr 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

2sessions in tracefile.

0userSQL statements in trace file.

0internal SQL statements in

trace file.

0SQL statements in trace file.

0unique SQL statements in trace

file.

48lines in trace file.

0elapsed 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

USERPID %CPU %MEMSZRSSTTY STATSTIMETIME COMMAND

root573725.80.0384384- AApr 12 32970:07 wait

root166805.70.0384320- AApr 12 32758:07 wait

root614705.70.0384384- AApr 12 32541:32 wait

root207785.70.0384384- AApr 12 32349:41 wait

root81965.70.0384384- AApr 12 32348:53 wait

root2885.60.0384320- AApr 12 32211:46 wait

root532745.60.0384384- AApr 12 32025:52 wait

root125825.60.0384320- AApr 12 31946:04 wait

oracle6680640.21.0 96696 51848- A15:07:020:33 oracleTCSF21 (LO

oracle5447880.11.0 104296 59448- A15:36:580:04 oracleTCSF21 (LO

root1886460.10.0 56724 7288- AApr 12 717:04 /oracle/product/

oracle5410080.11.0 104144 59296- A13:49:290:57 oracleTCSF21 (LO

oracle3277580.11.0 107644 55276-

AApr 12 419:10 ora_lms0_TCSF21

oracle3279900.11.0 107648 55296- AApr 12 416:54 ora_lms1_TCSF21

root5613180.00.0 1684 1704- A11:34:160:57 /usr/dt/bin/dtte

oracle6517480.01.0 94348 49500- A15:38:050:01 ora_j000_TCSF21

oracle6226000.01.0 98168 53320- A15:20:080:04 oracleTCSF21 (LO

root1397060.00.0784472- AApr 12 172:29 /bin/sh /etc/ini

oracle3195620.01.0 107168 46160- AApr 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

2WHERE (a.hash_value,

a.address) IN (

3SELECT 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

456WHERE b.paddr = (SELECT addr

FROM v$process

c

WHERE c.spid =

'&pid'))

ORDER BY piece ASC789;

Enter value for pid: 668064

old8:WHERE c.spid = '&pid'))

new8:WHERE c.spid = '668064'))

SQL_TEXT

----------------------------------------------------------------

SELECT 1 FROM DUAL