get explain plan

要先cd到/u01/oracle/DV11/dv11db/9.2.0/admin/DV11_prcsgisol03/udump目录下，再执行tkprof tracefile outputfile

 

appenv.sh所在目录为：/rdbms/apps1159

 

 

使用tkprof

命令在DV11

要先运行一下环境变量的文件

. ./appenv.sh  $env  $bash

 

  

query1：Select value from v$parameter
 where name = 'user_dump_dest';
 
query2：  Select oracle_process_id
 from fnd_concurrent_requests
 where request_id = 2754278

 

command：tkprof dv11_ora_7830_OPERATIONS_CR2759648.trc dv11_ora_7830_OPERATIONS_CR2759648.tkp sort=prsdsk,exedsk,fchdsk

 

1，在并发程序定义界面勾上trace选项

2，执行并发程序

3，query1找到生成的trc文件的路径，query2找到文件名中的processing_id

4，tkprof生成tkp文件

5，选取tkp文件中的前5个query在Toad里Explain Plan

 

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

appenv.sh的内容：

cd /u01/oracle/DV11/dv11appl
. ./APPSORA.env
echo $ORACLE_HOME

所以实际上执行的是/u01/oracle/DV11/dv11appl/APPSORA.env

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

如果已有trc文件，执行以下过程可得tkp文件：

. /u01/oracle/DV11/dv11appl/APPSORA.env

cd /u01/oracle/DV11/dv11db/9.2.0/admin/DV11_prcsgisol03/udump

tkprof betsyd6_ora_12677_APPS.trc betsyd6_ora_12677_APPS.tkp sort=prsdsk,exedsk,fchdsk

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

 

--Get the Oracle proccess id

 

SELECT oracle_process_id --=25773

FROM fnd_concurrent_requests

WHERE request_id = '26552464'

 

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

--Get the trace file location

 

SELECT vpar.VALUE

|| '/'

|| INSTANCE

|| '_ora_'

|| LTRIM (TO_CHAR (vp.spid, 'fm99999'))

|| '.trc'

FROM v$process vp, v$session vs, v$parameter vpar, v$thread vthr

WHERE vp.addr = vs.paddr

AND vs.audsid = USERENV ('sessionid')

AND vpar.NAME = 'user_dump_dest'