dba_extents slow workaround

From: HansP <hans-peter.sloot_at_atosorigin.com>
Date: Wed, 29 Apr 2009 00:49:55 -0700 (PDT)
Message-ID: <5fd2df7e-81db-47f4-9944-6ebcf9b1206c_at_y6g2000prf.googlegroups.com>

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); Received on Wed Apr 29 2009 - 02:49:55 CDT