Hi All ,
I am a frequent user of Oracle Community forum , but this is my first question to all , I have a query like this which needs to be tuned
SELECT c.*,
cmdstcc.stcc_cd_desc stccDef,
CASE c.CARHIRE_PYMT_CD
WHEN 'J' THEN 'HAULAGE-J'
WHEN 'N' THEN ''
ELSE ''
END AS a1,
c.EQUIP_INITIAL ||' ' ||c.EQUIP_NUM AS a2,
CASE
WHEN c.TRAN_FILE_ID IS NOT NULL THEN --'TOL'
(SELECT CASE WHEN report_type= 86 THEN 'XXXX'
WHEN report_type= 96 THEN 'YYYY'
ELSE 'ZZZZ'
END AS tol_sts
FROM cont.TRAN_FILE t28
WHERE t28.TRAN_FILE_ID=c.TRAN_FILE_ID )
WHEN c.IS_HAULAGE = 'Y' THEN 'DDDD'
WHEN c.DDCT_TTRAN_FILE_ID IS NOT NULL THEN 'DDDD'
ELSE ''
END AS TOL_STATUS,
irf.f_get_equip_attribute(c.EQUIP_INITIAL,c.EQUIP_NUM,'UMET') AS CAR_TYPE,
irf.f_get_equip_attribute(c.EQUIP_INITIAL,c.EQUIP_NUM,'UMMD') AS MECH_DES,
CASE
WHEN p.PERIOD_IS_CLOSED = 'Y' THEN 'N'
ELSE 'Y'
END AS EDITABLE,
TO_CHAR(c.TRAIN_II_TRANSMITTED_DATETIME,'yyyy-mm-dd hh24:mi') AS TRAIN_II_TRANS_DATETIME_1,
TO_CHAR(c.EVENT_DATETIME,'yyyy-mm-dd hh24:mi:SS') AS EVENT_DATETIME_1,
TO_CHAR(c.CAR_HIRE_RULE5_PROCESS_DATE,'yyyy-mm-dd hh24:mi') AS CAR_HIRE_R5_PRO_DATE_1,
TO_CHAR(c.MODIFIED_DATETIME,'yyyy-mm-dd hh24:mi') AS MODIFIED_DATETIME_1,
CASE
WHEN c.EVENT_TYPE_CD = 'ICR' THEN c.INTERCHANGE_ROAD
ELSE c.MOVE_ROAD
END AS FROM_ROAD,
CASE
WHEN c.EVENT_TYPE_CD = 'ICH' THEN c.INTERCHANGE_ROAD
ELSE c.MOVE_ROAD
END AS TO_ROAD,
CASE
WHEN c.RECL_CD IS NOT NULL
AND c.RECL_POOL IS NOT NULL THEN c.RECL_CD || ' - ' ||c.RECL_POOL
WHEN c.RECL_CD IS NOT NULL
AND c.RECL_POOL IS NULL THEN c.RECL_CD || ' - '
WHEN c.RECL_CD IS NULL
AND c.RECL_POOL IS NOT NULL THEN ' - ' ||c.RECL_POOL
ELSE ''
END AS RECL
FROM HIRE_EVENTS c
INNER JOIN ORG_ACCOUNTING_PERIODS p ON p.ROAD = c.ROAD
AND p.ACCOUNTING_PERIOD = c.SERVICE_PERIOD
LEFT JOIN abs.stcc_codes cmdstcc ON c.stcc =cmdstcc.stcc_cd
WHERE (c.ROAD =:roadMark)
AND (c.SERVICE_PERIOD BETWEEN :periodFrom AND :periodTo)
AND c.ROAD = c.move_road
ORDER BY c.EQUIP_INITIAL,
c.EQUIP_NUM,
c.EVENT_DATETIME
this the TKPROF output
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'TRAN_FILE'
(TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'SYS_C0017279' (INDEX
(UNIQUE))
0 SORT (ORDER BY)
0 FILTER
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ORG_ACCOUNTING_PERIODS' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'SYS_C0017326'
(INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'HIRE_EVENTS' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'HIRE_EVENTS_IX6' (INDEX)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'STCC_CODES' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'STCC_CODES_IX1'
(INDEX)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.01 18.66 55 176 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.01 18.66 55 176 0 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=88 pr=28 pw=0 time=9332372 us)
2672 2672 2672 TABLE ACCESS SAMPLE HIRE_EVENTS (cr=88 pr=28 pw=0 time=342380 us cost=19 size=72387 card=2681)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: HINT: ALL_ROWS
1 SORT (AGGREGATE)
2672 TABLE ACCESS MODE: ANALYZED (SAMPLE) OF 'HIRE_EVENTS'
(TABLE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 55 1.50 17.09
The actions i have taken so far
1. With order by clause , Execution time goes to 46 seconds for 50 rows in SQL developer , and without order by it takes around 5 seconds for 50 rows in SQL developer, from there i came to a conclusion order by clause reason for this slowness
2. Created an Index for the sorting columns in the same order , no change
3. Created a Materialized view (on Demand tyoe) , it gave a huge performance boost , but MV has its own performance impact as well on other operations , i had 2nd thoughts about it
4. Sorted using only one column , without 3 of them still no change (just for the testing purpose only)
5. Increased the SORT_AREA_SIZE value for the session , it did not have an impact , since we have automatic memory management is turned on in the DB
Can any one , give me an advice is there anything else that I can try out ?
Kind Regards,