Tamil,
I do have the explain plan output for the above query posted on 12/12 based on your earlier request. It seems that the query performance goes down as per the explain plan.
SELECT /*+ LEADING(PIV) */
peop.person_id,
ass.assignment_id,
pee.element_entry_id,
MAX (peop.full_name) AS full_name,
MAX (DECODE (piv.NAME, 'Rank',
peev.screen_entry_value, NULL)
) AS RANK,
MAX (DECODE (piv.NAME, 'Vessel',
peev.screen_entry_value, NULL)
) AS vessel,
MAX (DECODE (piv.NAME, 'Days',
peev.screen_entry_value, NULL)
) AS days_worked,
MAX (DECODE (piv.NAME,
'Date', TO_DATE (peev.screen_entry_value,
'YYYY/MM/DD HH24:MI:SS'),
NULL
)
) AS date_worked
FROM pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_f_tl piv,
( select /*+ NO_MERGE */
person_id, assignment_id,
max(effective_end_date) effective_end_date
from per_all_assignments_f
group by person_id, assignment_id) ass,
(SELECT /*+ NO_MERGE */
person_id
FROM per_all_people_f
WHERE effective_end_date =
TO_DATE ('31-DEC-4712', 'DD-MON-YYYY')) peop
WHERE pee.element_entry_id = peev.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND piv.input_value_id IN (3220, 3221, 3222, 3223)
AND pee.assignment_id = ass.assignment_id
AND ass.person_id = peop.person_id
GROUP BY peop.person_id,
ass.assignment_id,
pee.element_entry_id;
Trace the SQL statement, and attach the trace file and tkprof output here.
The query ran for sometime without returning any rows. The trace file started increasing to its max value. So I had to cancel the query. The result is similar to what I have stated in my original post. Could not attach the trace file because of size limit.
Attached is the tkprof output. I saw the raw trace files and all the db file sequential read event happen on the following:
SQL> select object_name, object_type from dba_objects where object_id in (42063,42061);
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
PAY_ELEMENT_ENTRIES_F_PK
INDEX
PAY_ELEMENT_ENTRY_VALUES_F
TABLE
These are the same objects that I had mentioned in my original post. The raw trace file is full of:
Let me guess, you have cursor_sharing set to force in the database, do you?
You statistics lies to CBO. This magic 31-DEC-4712 valie needs to be null instead. With 9000 real values most likely with in year 2000-2006 range and 18000 values 2700 years away I am surprised Oracle expects to get 28 rows back. I would expect less than that for a single date. Change this magic value to null, analyze PER_ALL_PEOPLE_F table again (make sure you collect column level stats for effective_end_date column, no histograms).
If you cannot change this value to null, then collect histograms on effective_end_date column and run explain plan on this small query.
I spoted this problem based on explain plan cardinality estimate and real number of rows returned by the query. You can use this technique to segment your query and make sure optimizer get the correct picture.
If you have cursor_sharing parameter set to force then you need to change it to exact in your session and run the explain plan. Make sure you either use sql*plus 10.2 to get autotrace or dbms_xplan.display to get the explain plan. This way you will get predicates as well that is really important.