Issue with range scan on compound index

[Jocsta]

I have a query that in certain conditions exhibits unusual behaviour when performing an index range scan.
For example, I have 3 tables (simplified below, but note that its just an example to illustrate the problem and not my actual tables).

Table RECORDS has 2 columns,
ID number(20), STATUS char(1)
Primary key is on ID, a second compound index is on [ID,STATUS]

Table GENRES has 2 columns,
ID number(20), GENRE char(1)
Primary key (compound) on [ID,GENRE]
Foreign key on ID, linked to RECORDS ID

Table REVIEWS has 2 columns
ID number(20), REVIEW varchar2(20)
Foreign key on ID, linked to RECORDS ID


Now, I use a basic query which returns 3 rows...

select *
from RECORDS R
left outer join REVIEWS RV on R.ID=RV.ID
inner join join GENRES G on R.ID=G.ID
where rv.review= 'good'
and r.status='A'
and g.genre='A'

I would expect this query to return in under a second, instead its taking over 40! What im seeing in the autotrace plan (using sqldeveloper) is a RANGE SCAN on one of the compound indexes has a LAST_CR_BUFFER_GETS of over 9 million(im guessing bytes?). Removing either of the last 2 "AND" conditions in the query above drops this value to about 20 !

I dont really know much about Oracle so can't understand why this is happening, or where to look next, and am struggling even to explain the problem.

My best guess is that the range scan is (for some reason) using the whole compound key rather than just the ID part of it (e.g. looking for a range between [1,'good'] AND [12345678,'good'] ) - this is the only thing I can think of that would explain the huge amount of fetches?

Oracle is a complicated old beast, and this problem is appearing at a clients site (so access for prodding around is very limited), so any suggestions/ideas where to start looking are greatly appreciated !

Thanks,
JOC

 

[SantaMufasa]

First things to check are:[ul][li]Primary Key or unique index on REVIEWS.ID, RECORDS.ID, and GENRES.ID[/li][li]Indexes on REVIEW.REVIEW, RECORDS.STATUS, and GENRE.GENRE[/li][li]Recent gathering of statistics on all three tables.[/li][/ul]Let us know these initial findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cmmrfrds]

What happens if you make an equal join of
REVIEWS RV

It is effectively an equal join anyway because of this
where rv.review= 'good'

What are the results of this?

 

[Jocsta]

Thanks for the replies.

All the indexes and primary keys mentioned in the example exist, and the explain plan says they are being used. In the plan, it shows its using the compound indexes directly without a table scan - only the reviews table part uses a table scan. Im assured the statistics on the tables are up to date, and that appears to be the case - the database says they were last analyzed between the 15th/17th of feb (the performance problem was reported to me just after then, but has been down my "to do" list, and I just got to it now )

Making the rv.review join equal makes no difference. Should mention that in the real version its a like condition, but thats just fine. The query is dynamically generated by a java application based on a mix of 2

The example posted here was just illustrative of the setup we have. Im at home now so dont have access to the systems, but will post the actual table details tomorrow along with the explain plans and autotrace info, I was just trying to get a head start on the problem.

I do have a copy of a plan from sqldeveloper which I'll try to recreate here

SELECT
SORT
NESTED LOOPS
+ NESTED LOOPS
| + TABLE ACCESS PROP_CLIENT_GEN
| | + INDEX IX_FN_PROP_CLIENT_GEN_NAME
| | + ACCESS PREDICATE
| | | + UPPER(NAME) LIKE U'BRIAN%'
| | + FILTER PREDICATE
| | + UPPER(NAME) LIKE U'BRIAN%'
| + INDEX IX_ENTITYTABLE_IDSTAT
| + ACCESS PREDICATE
| + AND
| + ROOT.ENTITY_ID=CLIENT_GEN.REFERENCE
| + ROOT.STATUS=U'Y'
+ INDEX PK_LK_ENTITY_ROLE
+ ACCESS PREDICATE
+ AND
+ ROOT.ENTITY_ID=LK_ENTITY_ROLE.ENTITY_ID
+ LK_ENTITY_ROLE.ROLE_ID=116

 

[Dagon]

My best guess would be that it's doing a skip scan on the (ID,STATUS) column of RECORDS. That is, since ID isn't specified, it is skipping it and trying to do a scan on the STATUS column. Since STATUS has few distinct values, this will give bad performance.

To be honest, having an index on (ID, STATUS) when ID is unique on its own is pretty silly. How much extra differentiation are you going to get by adding something to a column that is already unique ? I would get rid of that index altogether. Failing that, the best thing would be to make sure you have collected your histogram stats using the "FOR ALL INDEXED COLUMNS" option of the DBMS_STATS.GATHER_TABLE_STATS package. With that extra information, Oracle should be able to work out that using an index on STATUS would be silly.

You can look in SYS.DBA_TAB_HISTOGRAMS to see if this information has already been gathered.

 

[Jocsta]

I see what you're saying about the skip scan, and that does kinda match my mumblings from above, but none of the traces mention a SKIP SCAN (would you expect to see that in the plan, or is it hidden?). I will look into the stats stuff you mention in a bit as it made little sense to me so I will need to read up a bit on it.

I think I caused a lot of confusion by trying to create a simple example initially tho. In the actual query from the plan above, my understanding of it is that the innermost nested loop would do the high selective fetch from the PROP_CLIENT_GEN table first, and use the "reference" columns from that combined with the desired status to lookup the row in the ID,STATUS index (its actually ENTITY_ID,STATUS - sorry!) as shown by the ROOT.ENTITY_ID=CLIENT_GEN.REFERENCE AND ROOT.STATUS=U'Y' which it lists as an ACCESS PREDICATE. So why a RANGE SCAN when its a unique index (or can UNIQUE SCAN not be applied to compound indexes)?

The ID,STATUS index has actually been added by the client themselves, and hasnt been a problem before, and I guess the main thinking behind this is that it only has to scan the index rather than an index scan and table lookup as root.id + root.status is present in almost all queries generated. I guess that would be better if they just replaced the primary key with ENTITY_ID,STATUS. As you say, status is highly un-selectable tho and im not convinced of the benefits myself - infact, this whole problem goes away if you drop that index, and then it uses UNIQUE SCANs instead of the RANGE SCANS - cost drops to 0 and consistent gets drops to about 70 (from a cost of 1 but with 9 million gets for the range scan), but they (the client from hell!) won't have that

We used Oracle enterprise managers SQL tuning advisor to analyse the query. It came back with "I can make this better by 99%", and it did. Execution time dropped from 45 seconds to .01 seconds, but the execution plan looked identical to the original, no differences whatsoever. However, whatever it does didn't stick, the application still runs at just the same slow speed, and doesn't seem to use the revised plan. ?? !! ??

We got the client to rebuild the indexes and statistics today, and that made no change. So we got them to send us the data from the tables in the simple example - which we just recieved and set up exactly the same table structures and imported it all. There is no problem here, the plan looks the same, and its using RANGE SCANS - but runs in the expected .01 seconds.

Heres the actual tables....
CREATE TABLE "ENTITY_TABLE"
( "ENTITY_ID" NUMBER(16,0) NOT NULL ENABLE,
"STATUS" NCHAR(1) NOT NULL ENABLE,
"CREATEDDATE" DATE NOT NULL ENABLE,
"CREATED_BY" NUMBER(16,0) NOT NULL ENABLE,
"UPDATEDDATE" DATE,
"UPDATED_BY" NUMBER(16,0),
PRIMARY KEY ("ENTITY_ID")
)
CREATE INDEX "IX_ENTITYTABLE_IDSTAT" ON "ENTITY_TABLE" ("ENTITY_ID", "STATUS");

CREATE TABLE "LK_ENTITY_ROLE"
( "ENTITY_ID" NUMBER(16,0) NOT NULL ENABLE,
"ROLE_ID" NUMBER(16,0) NOT NULL ENABLE,
"DEFAULT_ROLE" NCHAR(1),
"DEFAULT_VALUE" NVARCHAR2(100),
CONSTRAINT "PK_LK_ENTITY_ROLE" PRIMARY KEY ("ENTITY_ID", "ROLE_ID")
)
CREATE INDEX "IX_LKENTITYROLE_ROLEID" ON "LK_ENTITY_ROLE" ("ROLE_ID");
CREATE UNIQUE INDEX "PK_LK_ENTITY_ROLE" ON "LK_ENTITY_ROLE" ("ENTITY_ID", "ROLE_ID");


CREATE TABLE "PROP_CLIENT_GEN"
( "BISUNIQUEID" NUMBER(16,0),
"REFERENCE" NUMBER(16,0),
"NAME" NVARCHAR2(80),
...lots of columns... ,
PRIMARY KEY ("BISUNIQUEID"),
CONSTRAINT "FKEY_CLIENT_GEN_INV_CONTACT" FOREIGN KEY ("INV_CONTACT")
REFERENCES "V11PGROUP"."ENTITY_TABLE" ("ENTITY_ID") ENABLE,
CONSTRAINT "FKEY_CLIENT_GEN_DEF_RATE" FOREIGN KEY ("DEF_RATE")
REFERENCES "V11PGROUP"."ENTITY_TABLE" ("ENTITY_ID") ENABLE,
CONSTRAINT "FKEY_CLIENT_GEN_DEF_INVP" FOREIGN KEY ("DEF_INVP")
REFERENCES "V11PGROUP"."ENTITY_TABLE" ("ENTITY_ID") ENABLE,
CONSTRAINT "FKEY_CLIENT_GEN_REFERENCE" FOREIGN KEY ("REFERENCE")
REFERENCES "V11PGROUP"."ENTITY_TABLE" ("ENTITY_ID") ENABLE
)
CREATE INDEX "IDX_PROP_CLIENT_GEN_NM" ON "PROP_CLIENT_GEN" ("NAME");
CREATE INDEX "IX_CLIENT_GEN_DEF_INVP" ON "PROP_CLIENT_GEN" ("DEF_INVP");
CREATE INDEX "IX_CLIENT_GEN_DEF_RATE" ON "PROP_CLIENT_GEN" ("DEF_RATE");
CREATE INDEX "IX_CLIENT_GEN_INV_CONTACT" ON "PROP_CLIENT_GEN" ("INV_CONTACT");
CREATE INDEX "IX_CLIENT_GEN_REF_STATUS" ON "PROP_CLIENT_GEN" ("REFERENCE", "STATUS");
CREATE INDEX "IX_FN_PROP_CLIENT_GEN_NAME" ON "PROP_CLIENT_GEN" (UPPER("NAME"));

Once again, thanks for your help !