Performane issue with Mass Timecard Update in Timkeeper after upgrade to R12 (Doc ID 1382630.1)

Last updated on OCTOBER 31, 2016

Applies to:

Oracle Time and Labor - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Performance issue with Mass Timecard Update via Timekeeper. Seeing high waits in "db file sequential read" for the following statements.

********************************************************************************

SQL ID: b4x89chvd7x0y
Plan Hash: 2929659602
SELECT HA.ATTRIBUTE4, HA.ATTRIBUTE5, HA.ATTRIBUTE3
FROM
HXC_TIME_ATTRIBUTE_USAGES HAU, HXC_TIME_ATTRIBUTES HA, HXC_LATEST_DETAILS
HLD WHERE HLD.RESOURCE_ID = :B3 AND TRUNC(HLD.START_TIME) >= :B2 AND
TRUNC(HLD.STOP_TIME) <= TRUNC(:B1 ) AND HAU.TIME_BUILDING_BLOCK_ID =
HLD.TIME_BUILDING_BLOCK_ID AND HAU.TIME_BUILDING_BLOCK_OVN =
HLD.OBJECT_VERSION_NUMBER AND HAU.TIME_ATTRIBUTE_ID = HA.TIME_ATTRIBUTE_ID
AND ATTRIBUTE_CATEGORY = 'SECURITY' ORDER BY HLD.LAST_UPDATE_DATE DESC


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 110074 45.77 45.80 0 0 0 0
Fetch 110074 1281.00 4772.41 838747 16826277 0 109656
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 220149 1326.78 4818.21 838747 16826277 0 109656

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT ORDER BY (cr=105 pr=42 pw=0 time=0 us cost=70 size=80 card=1)
12 12 12 NESTED LOOPS (cr=105 pr=42 pw=0 time=3282 us)
36 36 36 NESTED LOOPS (cr=69 pr=41 pw=0 time=58 us cost=69 size=80 card=1)
36 36 36 NESTED LOOPS (cr=55 pr=39 pw=0 time=19 us cost=51 size=468 card=9)
12 12 12 TABLE ACCESS BY INDEX ROWID HXC_LATEST_DETAILS (cr=37 pr=35 pw=0 time=3062 us cost=24 size=333 card=9)
12 12 12 INDEX RANGE SCAN HXC_LATEST_DETAILS_N1 (cr=33 pr=32 pw=0 time=16 us cost=22 size=0 card=9)(object id 673874)
36 36 36 TABLE ACCESS BY INDEX ROWID HXC_TIME_ATTRIBUTE_USAGES (cr=18 pr=4 pw=0 time=107 us cost=3 size=15 card=1)
36 36 36 INDEX RANGE SCAN HXC_TIME_ATTRIBUTE_USAGES_FK2 (cr=17 pr=3 pw=0 time=38 us cost=2 size=0 card=2)(object id 241744)
36 36 36 INDEX UNIQUE SCAN HXC_TIME_ATTRIBUTES_PK (cr=14 pr=2 pw=0 time=0 us cost=1 size=0 card=1)(object id 2408731)
12 12 12 TABLE ACCESS BY INDEX ROWID HXC_TIME_ATTRIBUTES (cr=36 pr=1 pw=0 time=0 us cost=2 size=28 card=1)

error during execute of EXPLAIN PLAN statement
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

parse error offset: 291

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 838747 0.45 3537.30
latch: object queue header operation 2 0.00 0.00
latch: shared pool 18 0.00 0.01
latch: cache buffers chains 1 0.00 0.00
********************************************************************************

SQL ID: 2fvyhfjb5u1gw
Plan Hash: 1766401419
SELECT TIMECARD_ID, TIMECARD_OVN, START_TIME, STOP_TIME, DATE_TO
FROM
( SELECT TIME_BUILDING_BLOCK_ID TIMECARD_ID, OBJECT_VERSION_NUMBER
TIMECARD_OVN, START_TIME, STOP_TIME, DATE_TO, RANK() OVER ( PARTITION BY
RESOURCE_ID, START_TIME, STOP_TIME ORDER BY DATE_TO DESC,
TIME_BUILDING_BLOCK_ID DESC, OBJECT_VERSION_NUMBER DESC ) RANK FROM
HXC_TIME_BUILDING_BLOCKS WHERE RESOURCE_ID = :B3 AND SCOPE = 'TIMECARD' AND
TRUNC(START_TIME) BETWEEN :B2 AND :B1 AND TRUNC(STOP_TIME) BETWEEN :B2 AND
:B1 ) WHERE RANK = 1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2513 1.45 1.45 0 0 0 0
Fetch 2513 23.59 935.75 174071 213403 0 110087
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5027 25.05 937.20 174071 213403 0 110087

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 VIEW (cr=6 pr=0 pw=0 time=0 us cost=15 size=66 card=1)
0 0 0 WINDOW SORT PUSHED RANK (cr=6 pr=0 pw=0 time=0 us cost=15 size=37 card=1)
0 0 0 FILTER (cr=6 pr=0 pw=0 time=0 us)
0 0 0 TABLE ACCESS BY INDEX ROWID HXC_TIME_BUILDING_BLOCKS (cr=6 pr=0 pw=0 time=0 us cost=14 size=37 card=1)
0 0 0 INDEX RANGE SCAN HXC_TIME_BUILDING_BLOCKS_FK2 (cr=6 pr=0 pw=0 time=0 us cost=13 size=0 card=1)(object id 5081232)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 VIEW
0 WINDOW (SORT PUSHED RANK)
0 FILTER
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'HXC_TIME_BUILDING_BLOCKS' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'HXC_TIME_BUILDING_BLOCKS_FK2' (INDEX)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 174071 0.61 922.10
********************************************************************************

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms