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!

SQL tune - functions

RobeenJan 28 2020 — edited Jan 28 2020

Oracle DB 12.1.0.2

Solaris 11.4

Hello Team,

I would like to get some help on tuning the SQL below which is taking time to run. Is there a way I can tune the predicate as per explain plan below?

(select RS.ORDER_ID LAST_ORDER_ID,

RS.CMOT LAST_CMOT,

RS.CMOT_DESC LAST_CMOT_DESC,

TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY HH24:MI:SS') LAST_CMOT_DATE_CHAR,

RS.CMOT_DATE LAST_CMOT_DATE

from request_status RS

where TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY HH24:MI:SS') = (SELECT MAX(TO_CHAR(RS2.CMOT_DATE,'DD/MM/YYYY HH24:MI:SS')) FROM request_status RS2 WHERE RS.ORDER_ID = RS2.ORDER_ID)

group by

RS.ORDER_ID,

RS.CMOT,

RS.CMOT_DESC,

TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY HH24:MI:SS'),

RS.CMOT_DATE);

SQL_ID 9095ncupvavb2, child number 0

-------------------------------------

(select RS.ORDER_ID LAST_ORDER_ID, RS.CMOT LAST_CMOT,

RS.CMOT_DESC LAST_CMOT_DESC, TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY

HH24:MI:SS') LAST_CMOT_DATE_CHAR, RS.CMOT_DATE LAST_CMOT_DATE from

request_status RS where TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY HH24:MI:SS') =

(SELECT MAX(TO_CHAR(RS2.CMOT_DATE,'DD/MM/YYYY HH24:MI:SS')) FROM

request_status RS2 WHERE RS.ORDER_ID = RS2.ORDER_ID) group by

RS.ORDER_ID, RS.CMOT, RS.CMOT_DESC, TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY

HH24:MI:SS'), RS.CMOT_DATE)

Plan hash value: 2719448966

----------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | | 34254 (100)| | | | |

| 1 | HASH GROUP BY | | 32950 | 1995K| 2480K| 34254 (1)| 00:00:03 | 770K| 770K| |

|* 2 | HASH JOIN | | 32950 | 1995K| 17M| 33941 (1)| 00:00:03 | 45M| 4931K| 43M (0)|

| 3 | VIEW | VW_SQ_1 | 566K| 11M| | 16417 (2)| 00:00:02 | | | |

| 4 | HASH GROUP BY | | 566K| 9952K| 88M| 16417 (2)| 00:00:02 | 46M| 4826K| 42M (0)|

| 5 | TABLE ACCESS FULL| REQUEST_STATUS | 3295K| 56M| | 9620 (1)| 00:00:01 | | | |

| 6 | TABLE ACCESS FULL | REQUEST_STATUS | 3295K| 128M| | 9620 (1)| 00:00:01 | | | |

----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("VW_COL_1"=TO_CHAR(INTERNAL_FUNCTION("RS"."CMOT_DATE"),'DD/MM/YYYY HH24:MI:SS') AND

"RS"."ORDER_ID"="ITEM_1")

Note

-----

- this is an adaptive plan

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

SQL_ID 9095ncupvavb2, child number 1

-------------------------------------

(select RS.ORDER_ID LAST_ORDER_ID, RS.CMOT LAST_CMOT,

RS.CMOT_DESC LAST_CMOT_DESC, TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY

HH24:MI:SS') LAST_CMOT_DATE_CHAR, RS.CMOT_DATE LAST_CMOT_DATE from

request_status RS where TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY HH24:MI:SS') =

(SELECT MAX(TO_CHAR(RS2.CMOT_DATE,'DD/MM/YYYY HH24:MI:SS')) FROM

request_status RS2 WHERE RS.ORDER_ID = RS2.ORDER_ID) group by

RS.ORDER_ID, RS.CMOT, RS.CMOT_DESC, TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY

HH24:MI:SS'), RS.CMOT_DATE)

Plan hash value: 2719448966

----------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | | 34254 (100)| | | | |

| 1 | HASH GROUP BY | | 32950 | 1995K| 2480K| 34254 (1)| 00:00:03 | 770K| 770K| |

|* 2 | HASH JOIN | | 32950 | 1995K| 17M| 33941 (1)| 00:00:03 | 45M| 4931K| 43M (0)|

| 3 | VIEW | VW_SQ_1 | 566K| 11M| | 16417 (2)| 00:00:02 | | | |

| 4 | HASH GROUP BY | | 566K| 9952K| 88M| 16417 (2)| 00:00:02 | 46M| 4826K| 42M (0)|

| 5 | TABLE ACCESS FULL| REQUEST_STATUS | 3295K| 56M| | 9620 (1)| 00:00:01 | | | |

| 6 | TABLE ACCESS FULL | REQUEST_STATUS | 3295K| 128M| | 9620 (1)| 00:00:01 | | | |

----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("VW_COL_1"=TO_CHAR(INTERNAL_FUNCTION("RS"."CMOT_DATE"),'DD/MM/YYYY HH24:MI:SS') AND

"RS"."ORDER_ID"="ITEM_1")

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

- this is an adaptive plan

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

SQL_ID 9095ncupvavb2, child number 2

-------------------------------------

(select RS.ORDER_ID LAST_ORDER_ID, RS.CMOT LAST_CMOT,

RS.CMOT_DESC LAST_CMOT_DESC, TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY

HH24:MI:SS') LAST_CMOT_DATE_CHAR, RS.CMOT_DATE LAST_CMOT_DATE from

request_status RS where TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY HH24:MI:SS') =

(SELECT MAX(TO_CHAR(RS2.CMOT_DATE,'DD/MM/YYYY HH24:MI:SS')) FROM

request_status RS2 WHERE RS.ORDER_ID = RS2.ORDER_ID) group by

RS.ORDER_ID, RS.CMOT, RS.CMOT_DESC, TO_CHAR(RS.CMOT_DATE,'DD/MM/YYYY

HH24:MI:SS'), RS.CMOT_DATE)

Plan hash value: 2719448966

----------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | | 34254 (100)| | | | |

| 1 | HASH GROUP BY | | 32950 | 1995K| 2480K| 34254 (1)| 00:00:03 | 770K| 770K| |

|* 2 | HASH JOIN | | 32950 | 1995K| 17M| 33941 (1)| 00:00:03 | 45M| 4931K| 43M (0)|

| 3 | VIEW | VW_SQ_1 | 566K| 11M| | 16417 (2)| 00:00:02 | | | |

| 4 | HASH GROUP BY | | 566K| 9952K| 88M| 16417 (2)| 00:00:02 | 46M| 4826K| |

| 5 | TABLE ACCESS FULL| REQUEST_STATUS | 3295K| 56M| | 9620 (1)| 00:00:01 | | | |

| 6 | TABLE ACCESS FULL | REQUEST_STATUS | 3295K| 128M| | 9620 (1)| 00:00:01 | | | |

----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("VW_COL_1"=TO_CHAR(INTERNAL_FUNCTION("RS"."CMOT_DATE"),'DD/MM/YYYY HH24:MI:SS') AND

"RS"."ORDER_ID"="ITEM_1")

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

- this is an adaptive plan

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

Regards,

Joe

Added on Jan 28 2020

9 comments

174 views