Re: dba_extents slow workaround

From: Mladen Gogala <mladen_at_bogus.email.com>
Date: Fri, 1 May 2009 11:19:47 +0000 (UTC)
Message-ID: <gtelsj$nmm$2_at_solani.org>



On Wed, 29 Apr 2009 00:49:55 -0700, 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);

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?

-- 
http://mgogala.freehostia.com
Received on Fri May 01 2009 - 06:19:47 CDT

Original text of this message