Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help with result from trace file

Johnny B23 hours ago

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

Comments
Post Details
Added 23 hours ago
2 comments
32 views