Re: dba_extents slow workaround

From: ddf <oratune_at_msn.com>
Date: Thu, 30 Apr 2009 05:22:29 -0700 (PDT)
Message-ID: <cda4c37e-e650-40da-a342-89c5931ea5e6_at_v1g2000prd.googlegroups.com>

On Apr 29, 2:49 am, HansP <hans-peter.sl..._at_atosorigin.com> 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);

Read here:

http://oratips-ddf.blogspot.com/2008/01/lies-damned-lies-and-statistics.html

David Fitzjarrell Received on Thu Apr 30 2009 - 07:22:29 CDT