Strange 'gc cr multi block request' during dictionary queries

From: GG <grzegorzof_at_interia.pl>
Date: Tue, 16 Oct 2012 20:42:13 +0200
Message-ID: <507DAA85.4070502_at_interia.pl>

Hi,
  here goes the case :
I've got 4 node 10.2.0.3 RAC , DDL intensive (CTAS, DROP/TRUNCATE) database .
It's like 20TB of data, milions of partitions and objects . When I'm doing queries agains dictionary tables like select * from dba_segments where owner = 'A' and segment_name = 'B' I'm observing 'gc cr multi block request' , as far as I know its kind of scattered reads but using interconnect to gather data . Whats bothering me is profile of that 'gc cr multi block requests' , here goes some lines from 10046 trace:

WAIT #6: nam='gc cr multi block request' ela= 861 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123446958
WAIT #6: nam='gc cr multi block request' ela= 69 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123447083
WAIT #6: nam='gc cr multi block request' ela= 60 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123447220
WAIT #6: nam='gc cr multi block request' ela= 99 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123447347
WAIT #6: nam='gc cr multi block request' ela= 111 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123447482
WAIT #6: nam='gc cr multi block request' ela= 193 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123447704
WAIT #6: nam='gc cr multi block request' ela= 84 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123447820
WAIT #6: nam='gc cr multi block request' ela= 81 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123447931
WAIT #6: nam='gc cr multi block request' ela= 108 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123448065
WAIT #6: nam='gc cr multi block request' ela= 111 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123448199
WAIT #6: nam='gc cr multi block request' ela= 105 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123448328
WAIT #6: nam='gc cr multi block request' ela= 100 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123448458
WAIT #6: nam='gc cr multi block request' ela= 151 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123448639
WAIT #6: nam='gc cr multi block request' ela= 84 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123448750
WAIT #6: nam='gc cr multi block request' ela= 90 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123448867
WAIT #6: nam='gc cr multi block request' ela= 98 file#=1 block#=2434788 
class#=1 obj#=168 tim=1318733123448994

and that pattern repeats with different block#

Question is why Oracle is requesting the same block , over and over (16 times) , I thinks 16 comes from MBRC which is 16 and I've got 16kb blocksize .
Looks like a bug :) isnt it ?
Generally all queries agains dictionary tables are slow (minutes) , I'm not observing any lost block issues so its probably bad plans issue . Any comments ?
Regards
GregG

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 16 2012 - 20:42:13 CEST