Roy -- Thanks for the question regarding "Drop or Rebuild Indexes with MANY MANY extents", version 10.2.0

wouldn't convert hit uet$ and fet$ too?   February 3, 2008 - 10pm Central time zone

Bookmark | Bottom | Top

Reviewer: Kirill from Richmond, VA

> ... uet$ and fet$ to be hammered ...
> ... consider converting to a LMT first, then dropping,
> you'll find the extent deallocation to be MUCH faster. 

Just curious, wouldn't converting to lmt hit uet$ and fet$ just the same?

sys%DMT> exec sys.dbms_space_admin.tablespace_migrate_to_local( 'DMT' );
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.08


sys%DMT> select count(*) from dba_extents where tablespace_name = 'DMT';

  COUNT(*)
----------
    100000
Elapsed: 00:00:00.20


sys%DMT> drop table ops$tkyte.t;
Table dropped.
Elapsed: 00:00:00.71


sys%DMT> select count(*) from dba_extents where tablespace_name = 'DMT';

  COUNT(*)
----------
         0
Elapsed: 00:00:00.02

  February 3, 2008 - 10pm Central time zone

Bookmark | Bottom | Top

Reviewer: Roy from MD

Thanks Tom. We are attempting the cleanup for 2 reasons. First, we are having intermittent 
performance issues, and are trying to rule this out. Statspack frequently shows SQL against 
uet$/fet$ in the Top SQL, and Support has identified SMON as getting hammered during some of the 
system state dumps we have uploaded. And second, we eventually will need to do partition 
maintenance on this table and don't want it to take forever trying to drop partitions once the 
system is in production.

Regarding the conversion to LMT, based on your experience and the environment as I've described, 
any SWAGs on how long the conversion to LMT (and then the subsequent drop) might take?  It's a 4 
cpu HP server.

  February 4, 2008 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Chris from Glasgow

>> Just curious, wouldn't converting to lmt hit uet$ and fet$ just the same?

If you use dbms_space_admin to convert the tablespace to loccally managed the updates to uet$ and 
fet$ happen as a single transaction rather than 1.1 million seperate transactions as the extents 
are deallocated.

  February 5, 2008 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Roy from MD

Hi Tom,

I conducted a similar test and did not see encouraging results.  I created a table with 100,000 4k 
extents in a DMT. I then created 2 compound indexes on the table, each had 100,000 4k extents, but 
I interleaved them (used a pl/sql block to alter index allocate extent, alternatingly).  I then 
dropped 1 index, it took 2 hrs 7 minutes. Next I ran the migrate_to_local on the tablespace, it 
took 2 hrs 43 minutes.  The last drop index then took just 4 seconds.  However, the migration took 
35% longer than dropping the index.  This was in 8i on a 2 CPU AIX box. I'm going to look for a 10g 
install to test on, but so far this doesn't seem as though it will help.

Any other thoughts?  Also, is there a way to check the progress of the tablespace migration 
process?  v$session_longops didn't show anything. Thanks.

HASH JOIN
   FULL SCAN T1
   FULL SCAN T2

nested loops
   full scan t1
   index range scan t2_idx
       table access by index rowid t2

  February 5, 2008 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Roy from MD

okay thanks for your help. I'm trying to find a 10g that doesn't have a LM system tablespace to I 
can create a DMT.

  February 5, 2008 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: Roy from MD

Well ... same here, had to create a new DB with DM system tablespace. Didn't make a difference 
though. Must be some significant difference in our environments (last test 10.2.0.3 on Linux) as my 
10g test yielded similar results to my 8i test (just faster overall).  Index drop - DMT - 37 
minutes; TBS conversion - 43 minutes; Index drop - LMT - 18 seconds.  Doesn't look like any way to 
get this done by Saturday so we'll have to resort to partition-by-partition online rebuilds.


SQL> @chk_size

SEGMENT_NAME                EXTENTS         MB
------------------------ ---------- ----------
T1                           100000        781
IDX2                         100000        781
IDX1                         100000        781

Elapsed: 00:00:00.01
SQL> drop index idx1;

Index dropped.

Elapsed: 00:37:51.72
SQL> @chk_size

SEGMENT_NAME                EXTENTS         MB
------------------------ ---------- ----------
T1                           100000        781
IDX2                         100000        781

Elapsed: 00:00:00.06
SQL> exec dbms_space_admin.tablespace_migrate_to_local('ROY');

PL/SQL procedure successfully completed.

Elapsed: 00:43:07.76
SQL> @chk_size

SEGMENT_NAME                EXTENTS         MB
------------------------ ---------- ----------
T1                           100000        781
IDX2                         100000        781

Elapsed: 00:00:00.08
SQL> drop index idx2;

Index dropped.

Elapsed: 00:00:18.99


Just curious ... in your test case, are you allocating all your extents contiguously? (Should that 
even matter?)

  February 5, 2008 - 10pm Central time zone

Bookmark | Bottom | Top

Reviewer: Roy from MD

I had to tweak it a bit between versions but I think this should work. 

set timing on

create tablespace roy datafile '/exports/roy01.dbf' size 5001m extent management dictionary segment 
space management manual;

create table t1 (x date, y number, z varchar2(20)) tablespace roy 
storage (initial 4k next 4k minextents 1 maxextents unlimited pctincrease 0);

alter table t1 add (a number, b varchar2(20));

create index idx1 on t1(x,y,z) tablespace roy
storage (initial 4k next 4k minextents 1 maxextents unlimited pctincrease 0);

create index idx2 on t1(x,a,b) tablespace roy
storage (initial 4k next 4k minextents 1 maxextents unlimited pctincrease 0);

declare
x number :=1;
begin
while x < 20000
loop
execute immediate 'alter table t1 allocate extent';
execute immediate 'alter index idx1 allocate extent';
execute immediate 'alter index idx2 allocate extent';
x:=x+1;
end loop;
end;
/

declare
x number :=1;
begin
while x <= 40000
loop
execute immediate 'alter table t1 allocate extent';
execute immediate 'alter index idx1 allocate extent';
execute immediate 'alter index idx2 allocate extent';
x:=x+1;
end loop;
end;
/

declare
x number :=1;
begin
while x <= 40000
loop
execute immediate 'alter table t1 allocate extent';
execute immediate 'alter index idx1 allocate extent';
execute immediate 'alter index idx2 allocate extent';
x:=x+1;
end loop;
end;
/

drop index idx1;

exec dbms_space_admin.tablespace_migrate_to_local('ROY');

drop index idx2;

ops$tkyte%DMT> drop index idx1;

Index dropped.

Elapsed: 00:56:13.05
ops$tkyte%DMT>
ops$tkyte%DMT> exec dbms_space_admin.tablespace_migrate_to_local('DMT');

PL/SQL procedure successfully completed.

Elapsed: 00:07:49.29
ops$tkyte%DMT>
ops$tkyte%DMT> drop index idx2;

Index dropped.

Elapsed: 00:00:22.22
ops$tkyte%DMT> spool off

  February 6, 2008 - 8pm Central time zone

Bookmark | Bottom | Top

Reviewer: Roy from MD

Yes that is very strange. I retested by migrating the tablespace back to dictionary and repeating 
the test case. My results were very similar to yours. Then, I dropped and recreated the tablespace 
from scratch and started over, and my results went back to what I had seen before.

SQL> drop index idx1;

Index dropped.

Elapsed: 00:40:20.64
SQL>
SQL> execute dbms_space_admin.tablespace_migrate_to_local('ROY');

PL/SQL procedure successfully completed.

Elapsed: 00:55:05.45
SQL>
SQL>
SQL> drop index idx2;

Index dropped.

Elapsed: 00:00:18.34


Who knows ... could be that I have some other activity going on, could be some params different ... 
don't know. Anyway, thanks a lot for all the help Tom.

work around?   March 31, 2008 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Ke Qiu from Omaha, NE

As follow up, I ran some more tests with SQL trace turned on and found out the time it takes to 
convert a dictionary managed tablespace to locally managed depends more on the number of rows in 
fet$ than uet$. When I compare the tkprof output between a fast conversion (seconds) and a slow 
conversion (hours), the difference was due to the CPU time of following SQL,

select length from  fet$ where file#=:1 and block#=:2 and ts#=:3

Here is the tkprof stats of the fast run,

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       94      0.00       0.00          0          0          0           0
Execute     94      0.00       0.00          0          0          0           0
Fetch       94      0.01       0.00          0        318          0          48
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      282      0.01       0.00          0        318          0          48
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS CLUSTER FET$ (cr=2 pr=0 pw=0 time=16 us)
      1   INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=5 us)(object id 7)


And corresponding stats from slow run,

 call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     1084      0.03       0.02          0          0          0           0
Execute   1084      0.08       0.08          0          0          0           0
Fetch     1083    325.09     355.85          0     890912          0        1060
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3251    325.20     355.96          0     890912          0        1060
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS CLUSTER FET$ (cr=2947 pr=0 pw=0 time=1065592 us)
      1   INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=12 us)(object id 7)

Althoug the access path are the same for both runs, it taks much longer to access the clustered 
table FET$ in the case of slow run. Using a script from your book "Expert Oracle Database 
Architecture", I found out there are over 2.3 million rows in FET$ the tablespace to convert, or 
2946 chained blocks for that specific tablespace.

I suppose that if I can create an index on FET$(file#, block#, ts#, length), then I don't need to 
scan those chained blocks at all. However, I am not allowed to create indexes on a data dinctionary 
table. Is there any way to get around this?