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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 16 Oct 2012 15:37:55 -0400
Message-ID: <007501cdabd5$bdd92200$398b6600$_at_rsiz.com>

Also, you might examine what cluster, table, or index that block is part of, as well as the other repeated blocks. Where it fits in the composition of dba_segments might be illuminating.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Fitzjarrell
Sent: Tuesday, October 16, 2012 2:54 PM
To: grzegorzof_at_interia.pl; oracle-l_at_freelists.org Subject: Re: Strange 'gc cr multi block request' during dictionary queries

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 16 2012 - 21:37:55 CEST