hint to use global index instead of local indexes?

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Wed, 20 Jul 2011 20:18:39 -0500
Message-ID: <CA+fnDAbtP4RUnwmhk50_9b_eMsJj0Gx6m3P=FXsa5P95iYT-FQ_at_mail.gmail.com>



anyone know if there's a way to hint oracle to use a global index instead of local indexes?

i have an extract from a trace file here... i think that the nested loop is getting 649772 rows as input. so the outer part of the loop should be executing this many times. the outer is doing an index range scan on a partitioned index with a single value for search key (the index is non-unique, and this key is the leading field of the 5-field index). a typical index partition has 6986 leaf blocks and there are 830761 unique id's for this search key, so I don't think it should ever have to scan more than one leaf block to check for the existance of this ID. (it's driven by a NOT EXISTS clause.)

however i noticed that the index range scan is doing 8826189 consistent reads for 649772 loops. since the local indexes have a max blevel of 2, i hypothesized that perhaps it needs to scan two or three index partitions to check for the search key. however the global index has a blevel of 3 -- so i'm thinking, why doesn't oracle just use the global index?

the time in this index range scan accounts for 50% of a 6 hour batch run
(about 9% of these I/Os go to disk). if using the global index reduces
logical gets per row from 7 to 4 then that could take an hour or two off the runtime. am i missing anything? why doesn't oracle use the global index instead of scanning a few local indexes (if that's what it's doing)? can i hint it?

-Jeremy

PS... here are the relevant lines from the trace:

STAT #1 id=35 cnt=1305528 pid=34 pos=1 obj=0 op='NESTED LOOPS (cr=38716413 pr=781204 pw=0 time=10852870565 us)'
STAT #1 id=36 cnt=649772 pid=35 pos=1 obj=0 op='BUFFER SORT (cr=29890224 pr=0 pw=0 time=456569392 us)'
STAT #1 id=37 cnt=649772 pid=36 pos=1 obj=0 op='PX RECEIVE (cr=29890224 pr=0 pw=0 time=433619359 us)'
STAT #1 id=38 cnt=649772 pid=37 pos=1 obj=0 op='PX SEND BROADCAST :TQ10000
(cr=29890224 pr=0 pw=0 time=431151440 us)'
STAT #1 id=39 cnt=649772 pid=38 pos=1 obj=0 op='NESTED LOOPS (cr=29890224 pr=0 pw=0 time=429355854 us)'
STAT #1 id=40 cnt=649772 pid=39 pos=1 obj=1234 op='TABLE ACCESS BY INDEX ROWID CLNT_PARAMS (cr=1300256 pr=0 pw=0 time=28789235 us)'

STAT #1 id=41 cnt=649772 pid=40 pos=1 obj=2345 op='INDEX RANGE SCAN
CLNT_PARAMS_IND (cr=650484 pr=0 pw=0 time=11759527 us)'
STAT #1 id=42 cnt=649772 pid=39 pos=2 obj=0 op='PARTITION RANGE ALL
PARTITION: 1 33 (cr=28589968 pr=0 pw=0 time=397961045 us)' STAT #1 id=43 cnt=649772 pid=42 pos=1 obj=0 op='PARTITION LIST ALL PARTITION: 1 LAST (cr=28589968 pr=0 pw=0 time=376194117 us)' STAT #1 id=44 cnt=649772 pid=43 pos=1 obj=3456 op='INDEX RANGE SCAN BIG_TABLE_INDEX_2 PARTITION: 1 44 (cr=28589968 pr=0 pw=0 time=300442749 us)'
STAT #1 id=45 cnt=632410 pid=35 pos=2 obj=0 op='PX PARTITION LIST ITERATOR PARTITION: KEY KEY (cr=8826189 pr=781204 pw=0 time=10432193366 us)' STAT #1 id=46 cnt=632410 pid=45 pos=1 obj=4567 op='INDEX RANGE SCAN BIG_TABLE_INDX PARTITION: KEY 44 (cr=8826189 pr=781204 pw=0 time=10364472880 us)'

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production System name: AIX
Release: 3
Version: 5

-- 
http://www.ardentperf.com
+1 312-725-9249

Jeremy Schneider
Chicago

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 20 2011 - 20:18:39 CDT

Original text of this message