-------------------------------------------------------------------------------
Schema Name: UTOPTEA
SQL ID : 7q72j7a1591mq
SQL Text : select /*+ INDEX(p IDX_TF_TT_PERFORMANCE_ID_TIME) USE_NL(p,m)
LEADING(m) */ p.mo_id,avg(p.VARCHAR_0),avg(p.VARCHAR_1) from
tf_tt_performance p ,tf_tt_mo m where m.type=:1 and p.mo_id =
m.id and p.time >= trunc(sysdate) + :2 /24 and p.time <
trunc(sysdate) + :3 /24 and p.month=to_char(trunc(sysdate)+:4
/24,'mm') group by p.mo_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 92.31%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_80391',
replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3711078517
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4106 | 13 (8)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 1 | 4106 | 13 (8)| 00:00:01 | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 4106 | 12 (0)| 00:00:01 | | |
| 4 | TABLE ACCESS BY INDEX ROWID| TF_TT_MO | 22 | 1012 | 5 (0)| 00:00:01 | | |
|* 5 | INDEX RANGE SCAN | IDX_TF_TT_MO_1 | 22 | | 3 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE SINGLE | | 1 | 4060 | 0 (0)| 00:00:01 | KEY | KEY |
|* 7 | TABLE ACCESS FULL | TF_TT_PERFORMANCE | 1 | 4060 | 0 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!)+:2/24
5 - access("M"."TYPE"=:1)
7 - filter("P"."MO_ID"="M"."ID" AND "P"."TIME">=TRUNC(SYSDATE@!)+:2/24 AND
"P"."TIME"
2- Using SQL Profile
--------------------
Plan hash value: 3169445354
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4106 | 1 (0)| 00:00:01 | | |
| 1 | SORT GROUP BY NOSORT | | 1 | 4106 | 1 (0)| 00:00:01 | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 4106 | 1 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE SINGLE | | 1 | 4060 | 0 (0)| 00:00:01 | KEY | KEY |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID| TF_TT_PERFORMANCE | 1 | 4060 | 0 (0)| 00:00:01 | KEY | KEY |
|* 6 | INDEX FULL SCAN | IDX_TF_TT_PERFORMANCE_MO | 1 | | 0 (0)| 00:00:01 | KEY | KEY |
|* 7 | TABLE ACCESS BY INDEX ROWID | TF_TT_MO | 1 | 46 | 1 (0)| 00:00:01 | | |
|* 8 | INDEX UNIQUE SCAN | PK_TF_TT_MO | 1 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!)+:2/24
5 - filter("P"."MONTH"=TO_NUMBER(TO_CHAR(TRUNC(SYSDATE@!)+:4/24,'mm')))
6 - access(SYS_OP_DESCEND("TIME")>SYS_OP_DESCEND(TRUNC(SYSDATE@!)+:3/24) AND
SYS_OP_DESCEND("TIME")<=SYS_OP_DESCEND(TRUNC(SYSDATE@!)+:2/24))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("TIME"))>=TRUNC(SYSDATE@!)+:2/24 AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("TIME"))
7 - filter("M"."TYPE"=:1)
8 - access("P"."MO_ID"="M"."ID")
-------------------------------------------------------------------------------