Hi All,
Concurrent program taking very long time to complete and I have generated the trace and tkprof file for that program. Can anyone please guide how to understand this tkprof file?
The following is the tkprof file.
TKPROF: Release 12.1.0.2.0 - Development on Thu Apr 4 16:17:00 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Trace file: TEST_ora_13205.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
********************************************************************************
Error in CREATE TABLE of EXPLAIN PLAN table: APPS.prof$plan_table
ORA-00955: name is already used by an existing object
parse error offset: 18
EXPLAIN PLAN option disabled.
********************************************************************************
SQL ID: 9j7scbm4n2xqq Plan Hash: 2097519442
SELECT MEANING
FROM
FND_LOOKUP_VALUES_VL FLV WHERE FLV.LOOKUP_TYPE = 'XXFASCOR_WAREHOUSE_LKUP'
AND FLV.ENABLED_FLAG = 'Y' AND SYSDATE BETWEEN NVL (START_DATE_ACTIVE,
SYSDATE - 1) AND NVL (END_DATE_ACTIVE, SYSDATE + 1) AND LOOKUP_CODE = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 420 0.12 0.04 0 0 0 0
Fetch 420 0.00 0.00 0 1260 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 840 0.12 0.05 0 1260 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 229 (recursive depth: 2)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 293 0.01 0.22
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 420 0.12 0.04 0 0 0 0
Fetch 420 0.00 0.00 0 1260 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 840 0.12 0.05 0 1260 0 0
Misses in library cache during parse: 0
1 user SQL statements in session.
0 internal SQL statements in session.
1 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: TEST_ora_13205.trc
Trace file compatibility: 11.1.0.7
Sort options: fchela
1 session in tracefile.
1 user SQL statements in trace file.
0 internal SQL statements in trace file.
1 SQL statements in trace file.
1 unique SQL statements in trace file.
4794 lines in trace file.
848 elapsed seconds in trace file.