Hi Gurus,
I am working on Oracle 11g R2 and have run into a performance problem and was hoping someone could help me out in understanding why CBO is behaving this way. Here is the background:
1. A stage table has about 60 columns and is used for staging incoming file data from clients
2. An Oracle package is used to read this table data - process it and then insert or update target tables
3. Here are some of the relevant columns:
id - surrogate primary key, populated using a sequence
person_id - id of person from the incoming file
insert_datetime - datetime when record was inserted into the staging table
source - incoming source name, has only 2 values (Eg: ADV or EMD)
4. Table has over 10 million records
5. SQL in consideration:
SELECT col1, col2, col3
FROM
(
SELECT DISTINCT col1, col2, col3
FROM stage
WHERE person_id = :B1
AND source = :B2
AND insert_datetime < :B3
ORDER BY insert_datetime DESC
)
WHERE ROWNUM = 1;
This SQL finds the last record which came in a file for a particular person before a new file is loaded and is used for comparison.
6. Index on the stage table: stage_idx1 (person_id, source, insert_datetime)
Problem:
Initially when the above code went live, CBO was doing a range scan descending on the index stage_idx1 and it was using all three columns as access predicates and it was running very quickly. However I started noticing that now a days it's taking too long and when I looked at the plan in the background when the process is running - it's doing a index skip scan and only using insert_datetime and source for access and then uses person_id for filter. This is a horrible way to go and it's taking very long for the process to run.
I gathered stats on the table and indexes but even then CBO only does index skip scan and uses insert_datetime and source as access predicates and not index range scan descending. I also tried dropping the index altogether and creating an index as stage_idx1 (person_id) and in this case CBO does a full-table scan, for some reason it doesn't use person_id as an access predicate. How do I make CBO use person_id as an access predicate?
Hope this helps. Has anyone run into issues like these? and can please tell me why would Oracle do a index skip scan even though all 3 columns present in the index are being used in the SELECT.
Thanks