dba_extents slow workaround
HansP
Like
select *
from
dba_extents where file_id = 87 and 253384 between block_id and
block_id +
blocks - 1
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 2 0.80 0.79 0 0
0 0
Execute 2 0.01 0.00 0 0
0 0
Fetch 4 506.72 9529.81 1058790 22323409
639 2
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 8 507.53 9530.61 1058790 22323409
639 2
So 2.6 hours elapsed time.
Metalink gives 2 workarounds with bug 5259025:
1.DBMS_STATS.GATHER_TABLE_STATS ('SYS', 'X$KTFBUE')
2.DBMS_STATS.SET_TABLE_STATS ('SYS', 'X$KTFBUE', NUMROWS => 100000,
NUMBLOCKS => 10000)
#1 did not work for me since it gave an error ORA-01422
#2 did not work either for me.
What works for me is:
1. create table <someuser>.ktfbue as select * from x$ktfbue;
2. gather stats on table created in step 1.
3. export the statistics of table created in step 1 to a stats table
exec dbms_stats.EXPORT_TABLE_STATS
('SOMEUSER,'KTFBUE',null,'STATS','KTF',true,'SOMEUSER')
3. update the data in the stats table so that the
update stats set C1='X$KTFBUE', c5='SYS' where STATID='KTF';
4. import the statistics in the stats table
exec dbms_stats.IMPORT_TABLE_STATS('SYS','X
$KTFBUE',null,'STATS','KTF',TRUE,'SOMEUSER',FALSE);
Michael Austin
Are you using DMT or LMT?
ASM? Raw? Cooked?
ddf
Read here:
http://oratips-ddf.blogspot.com/2008/01/lies-damned-lies-and-statistics.html
David Fitzjarrell
Mladen Gogala
> Are you using DMT or LMT?
> ASM? Raw? Cooked?
Probably not DMT in 10.2.
Mladen Gogala
Hans, thanks for bringing this up. This is precious, because the
"Fixed in Product Version" information for the bug 7430745, related
to bug 5259025 says "11.2". When will the version 11.2 be out, anybody?
johnb...@sbcglobal.net
snip
> Hans, thanks for bringing this up. This is precious, because the
> "Fixed in Product Version" information for the bug 7430745, related
> to bug 5259025 says "11.2". When will the version 11.2 be out, anybody?
OOW 2009 or just before ... is the rumour.
Michael Austin
> On Wed, 29 Apr 2009 19:06:15 -0500, Michael Austin wrote:
>
>
>> Are you using DMT or LMT?
>> ASM? Raw? Cooked?
>
> Probably not DMT in 10.2.
>
from 8i->9i->10.2.0.4 and was still using DMT even though I all but beat
them up to get it updated.
>
>
HansPeter
>
> - Tekst uit oorspronkelijk bericht weergeven -
All tablespaces are locally managed on ASM.
Michael Austin
This is the real question I should have asked... what problem are you
really trying to solve because while LMT uses an "extent" term, its
definition and usage is very different from that of the traditional
Oracle definition. The way the Area BitMap and SPAceManagement pages
work, worrying about extent coalescing is a moot point. Which is one
reason why some of those traditional tablespace qualifiers are ignored.
HansPeter
>
> - Tekst uit oorspronkelijk bericht weergeven -
Well, have you never had to query dba_extents to see in what extent
and what segment a corrupted block belong?
I had to do a block dump to get the object id and then search
dba_objects.
But it is much easier to see how the extents are allocated using
dba_extents.
Michael Austin
Not sure why you would really need to do this... unless you are trying
to restore an entire file. I prefer to just get the block from corrupt
blocks, feed it into RMAN and let it restore just the corrupt block. It
will then perform a recovery to ensure that block had not been updated
since last backup. This feature is one of the reasons why coalescing
extents is no longer necessary and could potentially screw your backup
methods. ie if the blocks were coalesced and moved, they are no longer
the same blocks - and redo does not cache this sort of ddl.