--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.26%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_18580',
task_owner => 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 64 will improve its response time
98.26% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 11.11% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate TRUNC("F"."ACC_DATE")=:B1 used at line ID 2 of the execution
plan contains an expression on indexed column "ACC_DATE". This expression
prevents the optimizer from efficiently using indices on table
"FLOW"."TB_FLOW".
Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3675585382
-------------------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2381 | 253K| 312K (0)|999:59:59 |
| |
| 1 | PARTITION RANGE ALL| | 2381 | 253K| 312K (0)|999:59:59 | 1 | 82 |
|* 2 | TABLE ACCESS FULL | TB_FLOW | 2381 | 253K| 312K (0)|999:59:59 | 1 |
82 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PR_URL" IS NOT NULL AND SUBSTR("PR_URL",INSTR("PR_URL",:SYS_B_0),:1)<>:S
YS_B_1 AND TRUNC(INTERNAL_FUNCTION("ACC_DATE"))=:2)
2- Using Parallel Execution
---------------------------
Plan hash value: 1016406201
---------------------------------------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ
|IN-OUT| PQ Distrib |
----------------------------