Oracle Database Discussions

Our application uses RAW(16) primary and foreign keys, and we have indexes built on these keys to make queries go fast. Most of the time this works fine. However, we are occasionally seeing very slow CPU-bound behavior from these same queries. When this happens, a query that usually takes 3 or 4 seconds will time out after 10 minutes.

We isolated one of these queries (see below) and ran it through the SQL Tuning Advisor. To our surprise, it recommends that we create new indexes using RAWTOHEX (see below also). This leads us to strongly suspect that Oracle is ignoring the existing RAW-based indexes and converting our RAW values to hex strings when running the query. This would explain why the query becomes CPU-bound in these cases and takes forever to run.

The same query running against nearly the same data in another schema runs fine and the advisor recommends no changes. What could cause the query to switch to hex-based key comparisons in some situations?

Thanks.

-- Brian


Query:
select distinct record2_.EntityFK as col_0_0_, record2_.Timestamp as col_1_0_, datetimeva6_.Value as col_2_0_, record2_.PK as col_3_0_
from Record_ record2_
inner join RecordSingleChoiceValue singlechoi3_ on record2_.PK=singlechoi3_.RecordFK
and (singlechoi3_.FieldFK=hextoraw('ECE925BD6FF24B91AB5B1F0279838BAF')),
Record_ record4_
inner join RecordSingleChoiceValue singlechoi5_ on record4_.PK=singlechoi5_.RecordFK
and (singlechoi5_.FieldFK=hextoraw('0D8B9DD52C2A4F418689B919D5E91D3D'))
inner join RecordDateTimeValue datetimeva6_ on record4_.PK=datetimeva6_.RecordFK
and (datetimeva6_.FieldFK=hextoraw('2C849163731E4B5FB878E92623AD3B61'))
inner join RecordSingleChoiceValue singlechoi7_ on record4_.PK=singlechoi7_.RecordFK
and (singlechoi7_.FieldFK=hextoraw('B6553F43F7614CFFB999D8D945E15576'))
where(singlechoi3_.ChoiceFK in (hextoraw('A9ECF8A5E9BC28D1E040060A2101044D')))
and record4_.EntityFK=record2_.EntityFK
and (singlechoi5_.ChoiceFK in (
hextoraw('A9ECF8AAAC1528D1E040060A2101044D'),
hextoraw('A9ECF8AAAC2928D1E040060A2101044D'),
hextoraw('A9ECF8AAAC3D28D1E040060A2101044D'),
hextoraw('A9ECF8AAAFE828D1E040060A2101044D'),
hextoraw('A9ECF8AAB37528D1E040060A2101044D'),
hextoraw('A9ECF8AAB38928D1E040060A2101044D'),
hextoraw('A9ECF8AAB39D28D1E040060A2101044D'),
hextoraw('A9ECF8AAAFC028D1E040060A2101044D'),
hextoraw('A9ECF8AAAFD428D1E040060A2101044D')
))
and datetimeva6_.Value>=to_date('01/01/2005', 'MM/DD/YYYY')
and datetimeva6_.Value<to_date('12/31/2005', 'MM/DD/YYYY')
and (singlechoi7_.ChoiceFK in (
hextoraw('A9ECF8A2111528D1E040060A2101044D'),
hextoraw('A9ECF8A3D19B28D1E040060A2101044D') ,
hextoraw('A9ECF8A9E24E28D1E040060A2101044D')
))
order by record2_.EntityFK, record2_.Timestamp;


Recommendation:
CREATE INDEX "MYSCHEMA"."RECORDSINGLECHO_IDX$$_072C0005"
ON "MYSCHEMA"."RECORDSINGLECHOICEVALUE"
(RAWTOHEX("FIELDFK"),RAWTOHEX("CHOICEFK"),"RECORDFK")
COMPUTE STATISTICS;