SQL优化，oracle trace使用

/*--==================================

启用请求追踪功能根据请求号追踪SQL

--==================================*/

--追踪请求,通过请求的时候通过请求号查询当前请求的SQL

 SELECT vp.spid , --top进程pid

       vs.sid, --数据库session_id

       vs.SERIAL# ,

       vs.MACHINE ,

       vs.PROCESS 应用process ,

       vs.PROGRAM ,

       vs.inst_id ,

       fcr.request_id ,--请求id

       vs.USERNAME ,

       FCR.Oracle_Session_Id ,

        command

  FROM gv$session              vs ,

       gv$process              vp ,

       fnd_concurrent_requests fcr

 WHERE vp.addr (+) = vs.paddr

   AND fcr.oracle_session_id (+) = vs.audsid

   AND vs.inst_id = vp.inst_id (+)

AND fcr.request_id in (84885491 );

 

--追踪请求，查看当前请求正在运行的SQL:

Select A.Sid, A.Serial#, B.Sql_Text

From V$session a, V$sqltext b, Apps.Fnd_Concurrent_Requests Req, Gv$process Proc

Where A.Sql_Address = B.Address

And Req.Request_Id = 31841418

And Req.Oracle_Process_Id = Proc.Spid(+)

And Proc.Addr = A.Paddr(+)

Order By B.Piece ;

或者:

SELECT a.sql_text

FROM v$sqltext a

,v$session b

WHERE b.audsid = &audsid --对应Oracle_Session_Id 

AND a.hash_value = b.sql_hash_value

AND a.address = b.sql_address

ORDER BY a.piece;

 

/*--======================

获取Trace文件路径

--======================*/

SELECT fcr.request_id,

fcr.enable_trace,

vp1.value || '/' || lower(vp2.value) || '_ora_' ||

fcr.oracle_process_id || '.trc' trace_path

FROM fnd_concurrent_requests fcr,

v$parameter vp1,

v$parameter vp2

WHERE vp1.name = 'user_dump_dest'

AND vp2.name = 'db_name'

AND fcr.request_id = &request_id;

 

/*--======================

trace文件解析

--======================*/

tkprof /export/home/appdev/test/DEV_ora_3448.trc /export/home/appdev/test/test_wx.txt aggregate=yes sys=no waits=yes sort=fchela

 

/*--======================

trace文件解析

--======================*/

Trace文件解析:tkprof TEST_ora_37730_HAND_LZ_CR442321.trc output=$HOME/test_lin.txt explain=apps/apps sort =fchela (目录定义在数据库参数里面) 解析trc文件.

tkprof dv21_ora_53206_JOB_USER_CR5797217.trc /home/dv21adm/trace/dv21_ora_53206_JOB_USER_CR5797217.txt sys=no sort=exeela,prsela,fchela 

 

/*--======================

编译FORM命令

--======================*/

编译form命令:

export FORMS_PATH=.:$FORMS_PATH:$AU_TOP/forms/ZHS

frmcmp_batch $CUX_TOP/forms/ZHS/CUXFUTCAIM.fmb apps/apps output_file=$CUX_TOP/forms/ZHS/CUXFUTCAIM.fmx

cp $CUX_TOP/forms/ZHS/CUX00IMPMAPDEF.fmx

       $CUX_TOP/forms/US/CUX00IMPMAPDEF.fmx