利用Trace file查找程序发出的SQL

Trace file(追踪文件)是以trc为后续的文本文件,它记录了各种sql操作及所消耗的时

间等,根据trace文件我们就可以了解哪些sql导致了系统的性能瓶颈,进而采取恰当的

方式调优.

 

查询生产报表—所有制程的WIP;

技术分享

 

show parameter sql_trace;

(如果value是false表示系统当前不会产生trace文件.采取如下操作让系统产生trace

文件。)

alter session set sql_trace=true;

EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE(true, true);

EXEC DBMS_MONITOR.DATABASE_TRACE_DISABLE();

(开启数据库级的trace,目的是为了跟踪SQL。)

 

利用everything搜索.trc找到Trace file。

技术分享

udump中一般放置sql trace之后session的trace文件,按时间排列找打最近生成

的.trc文件,用notpad++打开,查找对应SQL语句,把值替换到SQL语句中,在

PLSQL Developer执行SQL语句。

技术分享

Select D.Process_Name "Process Name",

D.Process_Code "Process Code",

count(A.WIP_QTY) QTY

From SAJET.G_SN_STATUS A,

SAJET.G_WO_BASE B,

SAJET.SYS_PDLINE C,

SAJET.SYS_PROCESS D,

SAJET.SYS_PART E,

sajet.sys_stage f,

sajet.sys_terminal g,

SAJET.SYS_FACTORY L

WHERE L.FACTORY_CODE = ‘HE‘

AND C.PDLINE_NAME = ‘FAB1‘

AND B.WORK_ORDER = ‘0001302E0002‘

AND E.PART_NO = ‘10000015‘

AND f.stage_name = ‘C‘

AND D.PROCESS_NAME = ‘激光扫边‘

AND g.TERMINAL_NAME = ‘LAS3-001‘

AND a.OUT_PROCESS_TIME <=

to_date(‘2015-4-1 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)

AND C.FACTORY_ID = L.FACTORY_ID

and A.WORK_ORDER = B.WORK_ORDER

and A.WIP_PROCESS <> 0

and A.PART_ID = E.PART_ID

and A.PDLINE_ID = C.PDLINE_ID

and A.WIP_PROCESS = D.PROCESS_ID

AND a.STAGE_ID = f.STAGE_ID

AND a.TERMINAL_ID = g.TERMINAL_ID

and A.WORK_FLAG = ‘0‘

and B.WO_STATUS < ‘5‘

Group By D.Process_Name, D.Process_Code

Order By D.Process_Code, D.Process_Name

技术分享

 

同理可查细项。

Select A.WORK_ORDER "Work Order",

A.SERIAL_NUMBER "Serial Number",

A.CUSTOMER_SN "Customer SN",

Decode(A.CURRENT_STATUS, ‘1‘, ‘NG‘, ‘‘) "Status",

C.PDLINE_NAME "Production Line",

D.PROCESS_NAME "WIP Process",

A.OUT_PROCESS_TIME "Out Process Time",

F.EMP_NAME "Employee"

From SAJET.G_SN_STATUS A,

SAJET.G_WO_BASE B,

SAJET.SYS_PDLINE C,

SAJET.SYS_PROCESS D,

SAJET.SYS_EMP F,

SAJET.SYS_FACTORY L

where L.FACTORY_ID = C.FACTORY_ID

and D.PROCESS_NAME = ‘激光扫边‘

and C.PDLINE_NAME = ‘FAB1‘

and L.FACTORY_CODE = ‘HE‘

and B.WORK_ORDER = ‘0001302E0002‘

and a.OUT_PROCESS_TIME <=

to_date(‘2015-4-1 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)

and A.WORK_ORDER = B.WORK_ORDER

and A.PDLINE_ID = C.PDLINE_ID

and A.WIP_PROCESS = D.PROCESS_ID

and B.WO_STATUS < ‘5‘

and A.WORK_FLAG = ‘0‘

and A.EMP_ID = F.EMP_ID(+)

Order by "Serial Number"aa

技术分享

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。