Re: Why my index is not used?

From: Ana Ribeiro <ana.ribeiro_at_reflective.com>
Date: 17 Jan 2007 06:43:37 -0800
Message-ID: <1169045017.573746.269200@11g2000cwr.googlegroups.com>

sybrandb wrote:

> On Jan 17, 1:54 pm, "Ana Ribeiro" <ana.ribe..._at_reflective.com> wrote:
> > Hi all,
> > I have the following query:
> >
> > select rs.runid, rs.instanceid, rs.scriptid, sc.scriptname,
> > rs.processnumber, rs.threadid,
> > min(st.steporder) as steporder, rs.cycleid, min(rs.startstamp) as
> > startstamp,
> > sum(rs.measurement) as measurement, sum(rs.errorcount) as errorcount
> > from step st, script sc, result rs
> > where
> > RS.RUNID=0 AND
> > rs.scriptid = sc.scriptid and
> > st.scriptid = rs.scriptid and
> > st.steporder = rs.steporder and
> > st.stepgroup <> '-1' and
> > st.stepgroup is not null and
> > sc.pluginid <> 2
> > group by
> > rs.runid, rs.instanceid, rs.processnumber, rs.scriptid, rs.threadid,
> > rs.cycleid, st.stepgroup, sc.scriptname;
> >
> > I have Statistics for my schema and the Optimizer_mode = ALL_ROWS.
> > There are also 4 non-unique indexes in the RESULT table, which is the
> > biggest one, with more than 100.000 rows:
> >
> > INDEX_NAME COLUMN_NAME COLUMN_POSITION
> > ---------------------------------------------------------------------------­---------
> > INDX_RESULT_1 RUNID 1
> > INDX_RESULT_1 SCRIPTID 2
> > INDX_RESULT_1 STARTSTAMP 3
> > INDX_RESULT_1 ERRORCOUNT 4
> >
> > INDX_RESULT_2 RUNID 1
> > INDX_RESULT_2 SCRIPTID 2
> > INDX_RESULT_2 STARTSTAMP 3
> >
> > INDX_RESULT_3 RUNID 1
> > INDX_RESULT_3 SCRIPTID 2
> > INDX_RESULT_3 STARTSTAMP 3
> > INDX_RESULT_3 STEPORDER 4
> >
> > IX_RESULTSTARTSTAMP STARTSTAMP 1
> >
> > This is the query plan:
> >
> > PLAN_TABLE_OUTPUT
> > ---------------------------------------------------------------------------­--------------
> > Plan hash value: 1866148270
> >
> > ---------------------------------------------------------------------------­------------
> > | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> > (%CPU)| Time |
> > ---------------------------------------------------------------------------­------------
> > | 0 | SELECT STATEMENT | | 64701 | 4802K| | 2397
> > (4)| 00:00:08 |
> > | 1 | HASH GROUP BY | | 64701 | 4802K| 14M| 2397
> > (4)| 00:00:08 |
> > |* 2 | HASH JOIN | | 109K| 8121K| | 429
> > (10)| 00:00:02 |
> > |* 3 | TABLE ACCESS FULL | STEP | 742 | 8904 | | 8
> > (0)| 00:00:01 |
> > |* 4 | HASH JOIN | | 113K| 7122K| | 415
> > (8)| 00:00:02 |
> > |* 5 | TABLE ACCESS FULL| SCRIPT | 8 | 160 | | 4
> > (0)| 00:00:01 |
> > |* 6 | TABLE ACCESS FULL| RESULT | 121K| 5222K| | 404
> > (7)| 00:00:02 |
> > ---------------------------------------------------------------------------­------------
> >
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> >
> > 2 - access("ST"."SCRIPTID"="RS"."SCRIPTID" AND
> > "ST"."STEPORDER"="RS"."STEPORDER")
> > 3 - filter("ST"."STEPGROUP" IS NOT NULL AND "ST"."STEPGROUP"<>'-1')
> > 4 - access("RS"."SCRIPTID"="SC"."SCRIPTID")
> > 5 - filter("SC"."PLUGINID"<>2)
> > 6 - filter("RS"."RUNID"=0)
> >
> > My question is: If in the query I set the RUNID value (RS.RUNID=0), why
> > an index is not used?
> >
> > Many thanks for the help!
> > Ana

>

> Remarks
> Please always post your exact version
> Questions:
> did you change optimizer_index_cost_adj and/or optimizer_index_caching
> from their disastrous defaults and/or did you gather system stats?
> Do you have accurate histograms on those tables.
> How many records in result have runid 0?

>

> Further remarks
> all_rows favors full table scans and hash joins. Try first_rows. Try
> gathering system stats or change the opt* parameters to something
> sensible (90 for index caching and 40 for cost_adj)
> Step is the driving table. There are no predicates which can use an
> index op Step.
> Likewise for script.
> Consequently it isn't strange the optimizer goes for a full table scan
> on results too, especially as the leading column of almost all indices
> (indices need to be revised too) doesn't participate in the join.

>

> Hth
> --
> Sybrand Bakker
> Senior Oracle DBA

Thanks Sybrand!
My DB version is 10.2.
I changed the optimizer_index_cost_adj and optimizer_index_caching to 40 and 90 (as you advised).
I realized that the table ONLY had records with runid=0. I inserted a few records with runid=1 and ran the query again, this is the new explain plan (using index this time!):

---

| Id  | Operation                      | Name          | Rows  | Bytes
| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |     1 |    76
|     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY                 |               |     1 |    76
|     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                 |               |     1 |    76
|     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |               |     1 |    64
|     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| RESULT        |     1 |    44
|     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | INDX_RESULT_1 |     1 |
|     1   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| SCRIPT        |     1 |    20
|     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN         | PK_SCRIPT     |     1 |
|     1   (0)| 00:00:01 |
|*  8 |    TABLE ACCESS BY INDEX ROWID | STEP          |     1 |    12
|     1   (0)| 00:00:01 |
|*  9 |     INDEX UNIQUE SCAN          | U_STEP        |     1 |
|     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Looks like my problem was solved - this RESULT table was not supposed to only have runids=0 (I am in the test environment), my appologies. In the production environment it certainly has several different runids.

I agree, the indexes should be revised, this will be my next task.

Please explain me one more thing: even with no predicates which can use an index on the tables Step and Script now we can see that indexes are being used ... Do you understand why?

Many thanks,
Ana Received on Wed Jan 17 2007 - 08:43:37 CST