More selective means slower?

  • From: stephen booth <stephenbooth.uk@xxxxxxxxx>
  • To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 20 Jan 2005 19:16:58 +0000

I'm running into a problem on one of our systems where a query on
dba-segments is running incredibly slow.

The full query is:

select 'alter index diamond'||segment_name||' rebuild tablespace tmp_d_indx;'
from dba_segments
where owner='DIAMOND'
and
segment_type='INDEX'
and 
tablespace_name='DM_DIAMOND_INDEXES';

and it takes ages (killed it after 2 hours).  I know that there are
307 indexes (on less than 150 tables, I think the people (an external
supplier) who wrote the app might have gone a little overboard, still
it gives me something to work on when they start complaining that
updates are too slow) that satisfy the where clause.

If I remove the last where clause (tablespace_name) it runs much
faster (but obviously that's not the answer I want), if I remove the
next clause (segment_type) then it runs faster still (a few seconds).

I would have thought that making a query more selective would, if
anything, speed up the response time, not slow it down significantly. 
The only thing I can think of is that the optimizer is choosing really
bad execution plans and using an index that it really should be or
doing a crappy join due to the extra where clause.

I haven't done an explain plan as I seem to recall that it doesn't
work for dictionary objects.

I've now substituted dba_indexes for dba_segments in the query and
edited appropriately  which works nice and quickly but I'm still
curious as to whether anyone has come accross something similar
before.

Incidentally the reason I'm doing this, in case anyone is wondering,
is that the bright spark who set up the system in the first place
misplaced a couple of zeros when sizing the tablespaces so files that
were supposed to be 300Mb are now 30Gb and won't fit on the backup
media.  I have to move the segments off to staging tablespaces, drop
the old tablespaces, recreate the old tablespaces with the correct
sizes and then move the objects back (for some reason the app
suppliers wrote it so it won't work if the segments aren't in
tablespaces with certain specific names, I didn't actually think that
that was possible).  Of course they only deemed to tell me this 4
minutes before I was due to go home.

Thankyou for your attention.

Stephen

-- 
It's better to ask a silly question than to make a silly assumption.
--
//www.freelists.org/webpage/oracle-l

Other related posts: