-
Why does Optimizer not pick up indexes?
The sql in our database is log and complicated. There were a few report sql statements getting so much slower in these a few weeks. It only took about 5 minutes before, but now it needs 2 hours.
I checked explain plan, there are a lot full table scan, even though there are indexes on the tables for those columns.
What could be the cause for the sql being so slow? Why does the optimizer not picking up the indexes?
Thanks!
-
You must put more details about the problem SQL statements. The reasons can be many.
-
-
Perhaps it is still using too many indexes.
-
Are you using CBO ?
Are your stats for the tables and indexes up to date ?
-
1. Here is the sql that is having problems.
select a12.DIAGNOSTIC_TROUBLE_CODE_STRING
DIAGNOSTIC_TROUBLE_CODE_STRING,
a12.DTC_DESC DTC_DESCRIPTION,
count(a11.DIAGNOSTIC_TROUBLE_CODE_KEY) WJXBFS1,
count(distinct a11.VEHICLE_KEY) WJXBFS2
from QIS2OWNER.FACT_DTC_INCIDENCE a11,
QIS2OWNER.DIAGNOSTIC_TROUBLE_CODE a12,
(select pa1.DIAGNOSTIC_TROUBLE_CODE_STRING
DIAGNOSTIC_TROUBLE_CODE_STRING
from (select a15.DIAGNOSTIC_TROUBLE_CODE_STRING
DIAGNOSTIC_TROUBLE_CODE_STRING,
rank () over( order by
count(a11.DIAGNOSTIC_TROUBLE_CODE_KEY) desc nulls last) WJXBFS1
from QIS2OWNER.FACT_DTC_INCIDENCE a11,
QIS2OWNER.CLAIM_DTC_MAP a12,
QIS2OWNER.FACT_CLAIM a13,
QIS2OWNER.DATE_DIMENSION a14,
QIS2OWNER.DIAGNOSTIC_TROUBLE_CODE a15,
QIS2OWNER.GMPT_RPOS a16,
QIS2OWNER.VU_ENGINE_RPO a17,
QIS2OWNER.LABOR_CODE a18
where a11.DTC_INCIDENCE_KEY = a12.DTC_INCIDENCE_KEY and
a12.CLAIM_KEY = a13.CLAIM_KEY and
a12.MODEL_YEAR = a13.MODEL_YEAR and
a13.SERVICE_CASE_PROCESS_DATE_KEY = a14.DATE_KEY and
a11.DIAGNOSTIC_TROUBLE_CODE_KEY =
a15.DIAGNOSTIC_TROUBLE_CODE_KEY and
a11.MODEL_YEAR = a16.MODEL_YEAR and
a11.VEHICLE_KEY = a16.VEHICLE_KEY and
a16.ENGINE_RPO_KEY = a17.RPO_KEY and
a13.LABOR_CODE_KEY = a18.LABOR_CODE_KEY
and (a11.MODEL_YEAR in (2005)
and a17.ENGINE_RPO_CODE in ('L52')
and a18.SECTOR in ('ENGINE')
and a11.SCAN_SOURCE_KEY < 9
and a14.START_DATE_OF_THE_WEEK >= To_Date('18-Jul-05'))
group by a15.DIAGNOSTIC_TROUBLE_CODE_STRING
) pa1
where (pa1.WJXBFS1 <= 10.0)
) pa2,
QIS2OWNER.CLAIM_DTC_MAP a13,
QIS2OWNER.FACT_CLAIM a14,
QIS2OWNER.DATE_DIMENSION a15,
QIS2OWNER.GMPT_RPOS a16,
QIS2OWNER.VU_ENGINE_RPO a17,
QIS2OWNER.LABOR_CODE a18
where a11.DIAGNOSTIC_TROUBLE_CODE_KEY = a12.DIAGNOSTIC_TROUBLE_CODE_KEY and
a12.DIAGNOSTIC_TROUBLE_CODE_STRING =
pa2.DIAGNOSTIC_TROUBLE_CODE_STRING and
a11.DTC_INCIDENCE_KEY = a13.DTC_INCIDENCE_KEY and
a13.CLAIM_KEY = a14.CLAIM_KEY and
a13.MODEL_YEAR = a14.MODEL_YEAR and
a14.SERVICE_CASE_PROCESS_DATE_KEY = a15.DATE_KEY and
a11.MODEL_YEAR = a16.MODEL_YEAR and
a11.VEHICLE_KEY = a16.VEHICLE_KEY and
a16.ENGINE_RPO_KEY = a17.RPO_KEY and
a14.LABOR_CODE_KEY = a18.LABOR_CODE_KEY
and (a11.MODEL_YEAR in (2005)
and a17.ENGINE_RPO_CODE in ('L52')
and a18.SECTOR in ('ENGINE')
and a11.SCAN_SOURCE_KEY < 9
and a15.START_DATE_OF_THE_WEEK >= To_Date('18-Jul-05'))
group by a12.DIAGNOSTIC_TROUBLE_CODE_STRING,
a12.DTC_DESC
2. Does too many indexes stop optimizer to use index?
3. The optimizer_mode = CHOOSE.
As usual, I analyze all tables every week. I newly created some more indexes on one of the major tables with compute statistics.
Thanks for the help!
-
Do you analyze columns (gathering histograms) as well as the tables and indexes?
-
Try setting OPTIMIZER_INDEX_COST_ADJ to a low value (10).
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Alter session set optimizer_index_cost_adj = 1
and then check the explain plan for the sql. It did pick up the indexes. But the problem is the query is still running very slow! What could have happened to the database? This query was running more than ten times faster a couple of weeks ago!
-
In retrospect, it would have been a good idea to store execution plans for queries while they were running well, for comparison when things go badly. Not too late to start n that now though.
You can have a shot at event 10046 tracing, to see what is delaying the result, but it seems as if something has changed and that prompted a change to the execution plan.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|