1，>select s.sid,s.serial#,s.username,p.spid from v$session s,v$process p where s.paddr=p.addr and s.username is not null ;
这里 ncv5的 sid和serial#这是针对当前窗口的当前会话，ncv5即使在当前窗口，但是exit重新登陆（事件结束），还是会变。
2，show parameter sql_trace;
oracle对sql的跟踪都是关闭的
3，打开针对特定用户的特定对话sql跟踪，是一个存储过程
execute dbms_system.set_sql_trace_in_session('133','17',true);
此后对ncv5的登陆下的session操作进行跟踪
4，ncv5登陆窗口执行一个sql
5，跟踪文件在user_dump_dest下 show parameter dump
根据上面查到的spid 3204 找到
6，使用tkprof对上面的跟踪trc文件解析 （win下）
>tkprof C:\app\dell\diag\rdbms\ncbeta\ncbeta\trace\ncbeta_ora_3204.trc c:\sen.trc.txt aggregate=yes sys=no waits=yes sort=fchela
sen.trc.txt生成在c盘
7，关闭跟踪execute dbms_system.set_sql_trace_in_session('133','17',false);(不关闭会如何？)
TKPROF: Release 11.2.0.1.0 - Development on 星期五 12月 14 11:33:24 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Trace file: C:\app\dell\diag\rdbms\ncbeta\ncbeta\trace\ncbeta_ora_3204.trc Sort options: fchela ******************************************************************************** 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 ******************************************************************************** select 收款单位名称,付款单位数目,wmsys.wm_concat(付款单位||'(' ||付款总金额 || '元'||','||付款次数||'次)') 详情 from( select unitname "付款单位", custcode "收款单位编码", custname "收款单位名称", sum_bbye "付款总金额", cnt1 "付款次数", cnt2 "付款单位数目" from (select substr(bd_corp.unitname,3,2) unitname, bd_cubasdoc.custcode, bd_cubasdoc.custname, sum(arap_djfb.bbye) sum_bbye, count(bd_corp.unitcode) cnt1, count(bd_corp.unitname) over(partition by bd_cubasdoc.custname) cnt2 from bd_cubasdoc, arap_djfb, bd_cumandoc, arap_djzb, bd_corp where arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc and bd_cumandoc.pk_cubasdoc = bd_cubasdoc.pk_cubasdoc and arap_djfb.vouchid = arap_djzb.vouchid and bd_corp.pk_corp = arap_djzb.dwbm and arap_djfb.payflag in ('1', '2') and arap_djfb.dr = '0' group by unitname, bd_cubasdoc.custname, bd_cubasdoc.custcode) where cnt2 >= 2) group by 收款单位名称,付款单位数目 order by 收款单位名称 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.06 0.19 7 180 0 0 Execute 1 0.00 0.01 0 0 0 0 Fetch 41 2.03 8.16 90155 90218 0 592 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 43 2.09 8.36 90162 90398 0 592 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 85 Rows Row Source Operation ------- --------------------------------------------------- 592 SORT GROUP BY (cr=90435 pr=90193 pw=0 time=52746 us cost=34937 size=600640 card=9385) 1420 VIEW (cr=90212 pr=90155 pw=0 time=9460 us cost=34747 size=1101184 card=17206) 8850 WINDOW BUFFER (cr=90212 pr=90155 pw=0 time=10871 us cost=34747 size=3389582 card=17206) 8850 SORT GROUP BY (cr=90212 pr=90155 pw=0 time=3792 us cost=34747 size=3389582 card=17206) 33620 HASH JOIN (cr=90212 pr=90155 pw=0 time=7011287 us cost=34006 size=3389582 card=17206) 9538 TABLE ACCESS FULL BD_CUBASDOC (cr=375 pr=372 pw=0 time=3941 us cost=103 size=515052 card=9538) 33620 HASH JOIN (cr=89837 pr=89783 pw=0 time=6973577 us cost=33903 size=2460458 card=17206) 206 VIEW index$_join$_007 (cr=8 pr=16 pw=0 time=717 us cost=3 size=5150 card=206) 206 HASH JOIN (cr=8 pr=16 pw=0 time=615 us) 206 INDEX FAST FULL SCAN I_BD_CORP_2 (cr=4 pr=2 pw=0 time=102 us cost=1 size=5150 card=206)(object id 80787) 206 INDEX FAST FULL SCAN PK_BD_CORP (cr=4 pr=14 pw=0 time=205 us cost=1 size=5150 card=206)(object id 80788) 33620 HASH JOIN (cr=89829 pr=89767 pw=0 time=6937785 us cost=33899 size=2030308 card=17206) 33620 HASH JOIN (cr=3877 pr=3870 pw=0 time=27738 us cost=1182 size=1307656 card=17206) 33620 TABLE ACCESS FULL ARAP_DJFB (cr=3641 pr=3638 pw=0 time=44100 us cost=996 size=860300 card=17206) 37941 INDEX FAST FULL SCAN I_ARAP_DJZB_003 (cr=236 pr=232 pw=0 time=12263 us cost=64 size=986466 card=37941)(object id 80577) 3544800 TABLE ACCESS FULL BD_CUMANDOC (cr=85952 pr=85897 pw=0 time=7526330 us cost=23559 size=148881600 card=3544800) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.06 0.19 7 180 0 0 Execute 1 0.00 0.01 0 0 0 0 Fetch 41 2.03 8.16 90155 90218 0 592 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 43 2.09 8.36 90162 90398 0 592 Misses in library cache during parse: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 116 0.00 0.01 0 0 0 0 Execute 543 0.06 0.05 0 0 0 0 Fetch 1352 0.03 0.33 102 2139 0 2145 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2011 0.09 0.40 102 2139 0 2145 Misses in library cache during parse: 35 Misses in library cache during execute: 35 1 user SQL statements in session. 543 internal SQL statements in session. 544 SQL statements in session. ******************************************************************************** Trace file: C:\app\dell\diag\rdbms\ncbeta\ncbeta\trace\ncbeta_ora_3204.trc Trace file compatibility: 11.1.0.7 Sort options: fchela 1 session in tracefile. 1 user SQL statements in trace file. 543 internal SQL statements in trace file. 544 SQL statements in trace file. 37 unique SQL statements in trace file. 5021 lines in trace file. 11 elapsed seconds in trace file.
