I've created a stored outline for this query below. Due to the use of the bind variables and especially because of the "scheduled_start_date <= (:3 + 1)", neither explain nor "create outline" command works.
For cases like these, I have successfully resorted to using PL/SQL and the command "alter session set create_stored_outlines = OLCAT" to create the outlines.
However, in this particular case, this does not work. The actual SQL statement was stored in the OL$ in upper case (this is normal), howver, the bind variables are created as :B1, :B2, :B3 and :B4 as opposed to :1, :2, :3, :4 as shown below. I think there may be issue matching what is actually in the SQLAREA.
Questions:
1. What can I do to make this outline work?
2. What are the columns hash_value, signature, and hash_value2? The hash_value apparently does not match the hash_value of the actual SQL in memory.
This particular area is very difficult to troubeshoot since there are not a lot of information out there, therefore, any help to resovle this issue is greatly appreciated.
Thanks.
select t.task_id,
nvl (a.actual_start_date, t.scheduled_start_date) scheduled_start_date,
nvl (a.actual_end_date, t.scheduled_end_date) scheduled_end_date,
decode
(nvl (a.actual_start_date, trunc (sysdate)),
trunc (sysdate), a.sched_travel_duration,
csf_util_pvt.convert_to_min (a.actual_travel_duration,
a.actual_travel_duration_uom
)
) travel_time,
csf_util_pvt.get_task_color (a.task_assignment_id) task_color,
csf_util_pvt.get_tooltip (a.task_assignment_id) tooltip, ''
from jtf_tasks_b t, jtf_task_assignments a
where a.task_id = t.task_id
and assignee_role = 'ASSIGNEE'
and (source_object_type_code = 'SR' or source_object_type_code = 'TASK')
and nvl (t.deleted_flag, 'N') <> 'Y'
and t.scheduled_end_date >= t.scheduled_start_date
and ( exists (
select null
from jtf_task_statuses_b s
where s.task_status_id = t.task_status_id
and nvl (s.cancelled_flag, 'N') <> 'Y')
and exists (
select null
from jtf_task_statuses_b s
where s.task_status_id = a.assignment_status_id
and nvl (s.cancelled_flag, 'N') <> 'Y')
)
and resource_id = :1
and resource_type_code = :2
and scheduled_start_date <= (:3 + 1)
and scheduled_end_date >= :4
