Greetings,
Found this in my trace file:
SQL ID: 6qvvpf6tytmk5 Plan Hash: 300247169
SELECT A.AVAILABILITY_TYPE_NAME, S.START_DATE, S.COMPLETION_DATE, CASE WHEN
TO_CHAR(S.START_DATE,'mm') <= 9 THEN TO_CHAR(S.START_DATE, 'YYYY') ELSE
TO_CHAR(ADD_MONTHS(S.START_DATE,12), 'YYYY') END, CASE WHEN
TO_CHAR(S.COMPLETION_DATE,'mm') <= 9 THEN TO_CHAR(S.COMPLETION_DATE, 'YYYY')
ELSE TO_CHAR(ADD_MONTHS(S.COMPLETION_DATE,12), 'YYYY') END
FROM
SHIP_SHEETS S JOIN AVAILABILITY_TYPE A ON A.AVAILABILITY_TYPE_ID =
S.AVAILABILITY_TYPE_ID JOIN HULL H ON H.HULL_ID = S.HULL_ID WHERE S.REQ_ID =
:B4 AND S.HULL_ID = :B3 AND (A.AVAILABILITY_TYPE_NAME IN ('BAS','FFP',
'ROSI','TIMER','SOLO') OR A.AVAILABILITY_TYPE_NAME LIKE 'SEDR%') AND
S.NSA_WORKSITE_ID = :B2 AND ROWNUM = 1 AND :B1 BETWEEN CASE WHEN
TO_CHAR(S.START_DATE,'mm') <= 9 THEN TO_CHAR(S.START_DATE, 'YYYY') ELSE
TO_CHAR(ADD_MONTHS(S.START_DATE,12), 'YYYY') END AND CASE WHEN
TO_CHAR(S.COMPLETION_DATE,'mm') <= 9 THEN TO_CHAR(S.COMPLETION_DATE, 'YYYY')
ELSE TO_CHAR(ADD_MONTHS(S.COMPLETION_DATE,12), 'YYYY') END
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1403 0.48 0.41 0 0 0 0
Fetch 1403 0.85 0.84 0 35900 0 359
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2808 1.34 1.26 0 35900 0 359
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110 (recursive depth: 3)
Number of plan statistics captured: 1403
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 1 COUNT STOPKEY (cr=26 pr=0 pw=0 time=605 us starts=1)
0 0 1 NESTED LOOPS (cr=26 pr=0 pw=0 time=603 us starts=1 cost=12 size=42 card=1)
0 0 2 NESTED LOOPS (cr=25 pr=0 pw=0 time=601 us starts=1 cost=12 size=42 card=1)
0 0 2 TABLE ACCESS BY INDEX ROWID BATCHED SHIP_SHEETS (cr=25 pr=0 pw=0 time=599 us starts=1 cost=11 size=31 card=1)
2 2 5 BITMAP CONVERSION TO ROWIDS (cr=23 pr=0 pw=0 time=591 us starts=1)
1 1 1 BITMAP AND (cr=23 pr=0 pw=0 time=589 us starts=1)
1 1 1 BITMAP CONVERSION FROM ROWIDS (cr=15 pr=0 pw=0 time=197 us starts=1)
1220 1220 1220 INDEX RANGE SCAN NIDX_REQ_ID (cr=15 pr=0 pw=0 time=515 us starts=1 cost=5 size=0 card=1128)(object id 74407)
1 1 1 BITMAP CONVERSION FROM ROWIDS (cr=8 pr=0 pw=0 time=369 us starts=1)
1836 2287 3711 INDEX RANGE SCAN FK_SHIPSHEETS_HULL (cr=8 pr=0 pw=0 time=340 us starts=1 cost=6 size=0 card=1128)(object id 74406)
0 0 2 INDEX UNIQUE SCAN PK_AVAILABILITY_TYPE (cr=0 pr=0 pw=0 time=1 us starts=0 cost=0 size=0 card=1)(object id 74646)
0 0 1 TABLE ACCESS BY INDEX ROWID AVAILABILITY_TYPE (cr=0 pr=0 pw=0 time=1 us starts=0 cost=1 size=11 card=1)
I paid attention because the total CPU is higher than others plan hash.
The query with values doesn't take a lot of time. I search for the code in my database and I found this code part of a function that is been call many times in the code.
Is anything in the code that I can improved?
Thanks, ~Johnny