|
|
这几天没做trace，下面的是前段时间的文档，使用的命令为：
tkprof <filename.trc> <output_filename> sys=no explain=apps/<password>
sort='(prsela,exeela,fchela)'
TKPROF: Release 10.1.0.5.0 - Production on Tue Oct 20 11:57:58 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: prod_ora_1232984_ANONYMOUS.trc
Sort options: prsela exeela 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
********************************************************************************
insert /*+ parallel(a,8) */ into gl_balances a
(code_combination_id,
period_name,
set_of_books_id,
currency_code,
period_year,
period_num,
period_type,
actual_flag,
last_updated_by,
last_update_date,
translated_flag,
period_net_dr,
period_net_cr,
period_net_dr_beq,
period_net_cr_beq,
quarter_to_date_dr,
quarter_to_date_cr,
project_to_date_dr,
project_to_date_cr,
begin_balance_dr,
begin_balance_cr,
begin_balance_dr_beq,
begin_balance_cr_beq,
quarter_to_date_dr_beq,
quarter_to_date_cr_beq,
project_to_date_dr_beq,
project_to_date_cr_beq,
template_id)
select /*+ parallel(b,8) */ code_combination_id,
period_name,
set_of_books_id,
currency_code,
period_year,
period_num,
period_type,
actual_flag,
1110,
sysdate,
decode(currency_code,'RMB',NULL,'STAT',NULL,'R'),
0,0,0,0,
quarter_to_date_dr,
quarter_to_date_cr,
project_to_date_dr,
project_to_date_cr,
begin_balance_dr,
begin_balance_cr,
decode(currency_code,'STAT',0,begin_balance_dr_beq),
decode(currency_code,'STAT',0,begin_balance_cr_beq),
quarter_to_date_dr_beq,
quarter_to_date_cr_beq,
project_to_date_dr_beq,
project_to_date_cr_beq,
template_id
from gl_open_interim b
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.21 1828.22 78 259 198 284349
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.22 1828.22 78 259 198 284349
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 44 (HXT)
Rows Row Source Operation
------- ---------------------------------------------------
0 INDEX MAINTENANCE (object id 0)
0 LOAD AS SELECT
0 TABLE ACCESS FULL GL_OPEN_INTERIM
error during execute of EXPLAIN PLAN statement
ORA-00942: table or view does not exist
parse error offset: 1439
********************************************************************************
insert into gl_open_interim (code_combination_id, period_name,
set_of_books_id, currency_code, period_year, period_num,
period_type, actual_flag, translated_flag, period_net_dr,
period_net_cr, period_net_dr_beq, period_net_cr_beq,
quarter_to_date_dr, quarter_to_date_cr, project_to_date_dr,
project_to_date_cr, begin_balance_dr, begin_balance_cr,
begin_balance_dr_beq, begin_balance_cr_beq, template_id) select
b.code_combination_id, decode(b.set_of_books_id , 2002,
'2009-09-23', 'ERROR'), b.set_of_books_id, b.currency_code,
decode(b.set_of_books_id, 2002, 2009 ,-1),
decode(b.set_of_books_id, 2002, 266 ,-1),
decode(b.set_of_books_id, 2002, '43' ,'ERROR'),
b.actual_flag,decode(b.set_of_books_id,2002,decode(b.currency_code,'RMB',
NULL,'STAT',NULL,'R'),0), 0 , 0,0,0, decode(b.set_of_books_id
, 2002, nvl(b.quarter_to_date_dr,0) + nvl(b.period_net_dr,0) ,
-1), decode(b.set_of_books_id, 2002,
nvl(b.quarter_to_date_cr,0) + nvl(b.period_net_cr,0) ,-1),
nvl(b.project_to_date_dr,0) + nvl(b.period_net_dr,0),
nvl(b.project_to_date_cr,0) + nvl(b.period_net_cr,0),
nvl(b.begin_balance_dr,0) + nvl(b.period_net_dr,0), nvl(b.begin_balance_cr,
0) + nvl(b.period_net_cr,0), decode(b.set_of_books_id,2002,
decode(b.currency_code,'STAT',0,nvl(b.begin_balance_dr_beq,0)
+nvl(b.period_net_dr_beq,0)),0),decode(b.set_of_books_id,2002,
decode(b.currency_code,'STAT',0,nvl(b.begin_balance_cr_beq,0)
+nvl(b.period_net_cr_beq,0)),0), b.template_id from gl_balances b,
gl_sets_of_books sob where b.actual_flag = 'A' and (
b.currency_code in (sob.currency_code,'STAT') or translated_flag='R' )
and b.set_of_books_id = sob.set_of_books_id and b.period_name =
sob.latest_opened_period_name and (sob.set_of_books_id = 2002 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 1 15.42 123.94 88065 113785 1030900 284349
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 15.44 123.95 88065 113785 1030900 284349
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 44 (HXT)
Rows Row Source Operation
------- ---------------------------------------------------
284349 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID GL_SETS_OF_BOOKS
1 INDEX UNIQUE SCAN GL_SETS_OF_BOOKS_U2 (object id 48849)
284349 PARTITION RANGE ITERATOR PARTITION: KEY KEY
284349 TABLE ACCESS FULL GL_BALANCES PARTITION: KEY KEY
error during execute of EXPLAIN PLAN statement
ORA-00904: "B"."SET_OF_BOOKS_ID": invalid identifier
parse error offset: 1928
********************************************************************************
update gl_concurrency_control set concurrency_entity_id=to_char((-1))
where
(concurrency_class='OPEN_PERIOD' and concurrency_entity_name=:b0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 3.65 2 1 5 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 3.65 2 1 5 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 44 (HXT)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE
1 INDEX RANGE SCAN GL_CONCURRENCY_CONTROL_U1 (object id 45851)
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT MODE: CHOOSE
0 UPDATE OF 'GL_CONCURRENCY_CONTROL'
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'GL_CONCURRENCY_CONTROL_U1' (INDEX (UNIQUE))
******************************************************************************** |
|
楼主: liangxinf
IP卡
狗仔卡
发表于 2009-10-23 10:16
显身卡
楼主