利用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