Re: dba_extents slow workaround

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Wed, 29 Apr 2009 19:06:15 -0500
Message-ID: <3Y5Kl.20039$as4.8421_at_nlpi069.nbdc.sbc.com>

HansP wrote:
> On some large 10.2.0... databases dba_extents can be very slow.
>
> 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);
>

Are you using DMT or LMT?
ASM? Raw? Cooked? Received on Wed Apr 29 2009 - 19:06:15 CDT