Re: Strange 'gc cr multi block request' during dictionary queries

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Tue, 16 Oct 2012 11:53:48 -0700 (PDT)
Message-ID: <1350413628.44622.YahooMailNeo_at_web121601.mail.ne1.yahoo.com>

The output from tkprof can include the plan; have you checked what the optimizer is doing with that query?  If you will post the current execution plan we'll get a better feel for what Oracle 10.2.0.3 is doing with your query.  Also I've found that the stats on user tables can be off in 10.2.0.x depending on the sample size and whether or not you have tables with a single-valued, non-null column.  (that last condition can create meaningless histograms that cause optimizer calculations to go awry).

David Fitzjarrell

---

From: GG <grzegorzof_at_interia.pl>
To: oracle-l_at_freelists.org
Sent: Tuesday, October 16, 2012 12:42 PM Subject: Strange 'gc cr multi block request' during dictionary queries

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#$34788 
class#=1 obj#8 tim18733123446958
WAIT #6: nam='gc cr multi block request' ela= 69 file#=1 block#$34788 
class#=1 obj#8 tim18733123447083
WAIT #6: nam='gc cr multi block request' ela= 60 file#=1 block#$34788 
class#=1 obj#8 tim18733123447220
WAIT #6: nam='gc cr multi block request' ela= 99 file#=1 block#$34788 
class#=1 obj#8 tim18733123447347
WAIT #6: nam='gc cr multi block request' ela= 111 file#=1 block#$34788 
class#=1 obj#8 tim18733123447482
WAIT #6: nam='gc cr multi block request' ela= 193 file#=1 block#$34788 
class#=1 obj#8 tim18733123447704
WAIT #6: nam='gc cr multi block request' ela= 84 file#=1 block#$34788 
class#=1 obj#8 tim18733123447820
WAIT #6: nam='gc cr multi block request' ela= 81 file#=1 block#$34788 
class#=1 obj#8 tim18733123447931
WAIT #6: nam='gc cr multi block request' ela= 108 file#=1 block#$34788 
class#=1 obj#8 tim18733123448065
WAIT #6: nam='gc cr multi block request' ela= 111 file#=1 block#$34788 
class#=1 obj#8 tim18733123448199
WAIT #6: nam='gc cr multi block request' ela= 105 file#=1 block#$34788 
class#=1 obj#8 tim18733123448328
WAIT #6: nam='gc cr multi block request' ela= 100 file#=1 block#$34788 
class#=1 obj#8 tim18733123448458
WAIT #6: nam='gc cr multi block request' ela= 151 file#=1 block#$34788 
class#=1 obj#8 tim18733123448639
WAIT #6: nam='gc cr multi block request' ela= 84 file#=1 block#$34788 
class#=1 obj#8 tim18733123448750
WAIT #6: nam='gc cr multi block request' ela= 90 file#=1 block#$34788 
class#=1 obj#8 tim18733123448867
WAIT #6: nam='gc cr multi block request' ela= 98 file#=1 block#$34788 
class#=1 obj#8 tim18733123448994

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
--

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 16 2012 - 20:53:48 CEST