Today, smon has been running at the top of topsessions, and disk iowait according to TOP (Unix tool on Sun 2.7) was 65%. The I/O went down to 7% just a few minutes ago, and I'm not sure why but think it's tied to SMON. Last night, I used 'alter table move' to move a bunch of tables to another space and eliminate extents. I also rebuilt 20+ indexes residing in the tablespaces.
I have three fragmentation reports: 1) before I started moving tables, 2) after all the tables were moved, and 3) one today for grins. These are the reports. The CHUNKS_FREE increased drastically. If anyone can explain what SMON was doing, more importantly why, I'd be most appreciative. PCTINCREASE is 0 on all tablespaces except SYSTEM which is 50. Oracle is 8.1.6 64bit.
Before any changes:
TABLESPACE_NAME TOT_SIZE TOT_FREE PCT_FREE MAX_FREE CHUNKS_FRE
------------------------- ---------- ---------- ---------- ---------- ----------
EPOXDATA 2147483648 2053771264 95.6361771 1885413376 23
EPOXINDEX 4294967296 4148482048 96.5893745 2098917376 17 <-
EPOXTRACK 4294967296 3570479104 83.1316948 1307656192 361 <<-
EPOXUSER 2147483648 1547571200 72.0643997 975638528 182 <<<-
QRXHISTORY 8192 4096 50 4096 1
QRXTRACK 6447693824 4300197888 66.6935808 2147479552 6
RBS 734003200 560349184 76.3415179 506568704 349
SYSTEM 933232640 861003776 92.2603581 610861056 14
TEMP 1094713344 1065955328 97.3730095 7602176 995
After changes:
TABLESPACE_NAME TOT_SIZE TOT_FREE PCT_FREE MAX_FREE CHUNKS_FRE
------------------------- ---------- ---------- ---------- ---------- ----------
EPOXDATA 2147483648 2053771264 95.6361771 1885413376 23
EPOXINDEX 4294967296 4113629184 95.7778931 2098917376 75 <-
EPOXTRACK 4294967296 3555098624 82.7735901 1276198912 391 <<-
EPOXUSER 2147483648 1429749760 66.5779114 792137728 1412 <<<-
QRXHISTORY 8192 4096 50 4096 1
QRXTRACK 6447693824 4300197888 66.6935808 2147479552 6
RBS 734003200 560349184 76.3415179 506568704 349
SYSTEM 933232640 761487360 81.5967346 380989440 18
TEMP 1094713344 1065955328 97.3730095 7602176 995
Today:
TABLESPACE_NAME TOT_SIZE TOT_FREE PCT_FREE MAX_FREE CHUNKS_FRE
------------------------- ---------- ---------- ---------- ---------- ----------
EPOXDATA 2147483648 1136574464 52.9258728 960536576 98
EPOXINDEX 4294967296 4113629184 95.7778931 1994059776 80 <-
EPOXTRACK 4294967296 3833606144 89.2580986 1276198912 679 <<-
EPOXUSER 2147483648 1627095040 75.7675171 555593728 2633 <<<-
QRXHISTORY 8192 4096 50 4096 1
QRXTRACK 6447693824 4300197888 66.6935808 2147479552 6
RBS 734003200 560349184 76.3415179 506568704 349
SYSTEM 933232640 861003776 92.2603581 610861056 14
TEMP 1094713344 1065955328 97.3730095 7602176 995
