Reorganisation of Oracle Database Files

[Roomer]

Hi all,

not had much exp. with reorganising files - can anybody advise how I go about it?

Have run filemon and fileplace and several large database files look pretty fragmented.

Can I run a reorg on the files themselves, and can I do this whilst the database is running???

Thanks for your time..

 

[bi]

I'm wondering why you are thinking about doing this. Are you having performance issues? Have you run any of the Oracle utilities on these files to compress them?

 

[Roomer]

bi,

yes were are experiencing performance issues, the box seems to be slowing down during office hours.

We do have a 3rd party Oracle DBA arrangement - as we have no Oracle skills in house.

What utilities do you refer to? Maybe I can prompt them to carry these out.

As far as "reorgvg" is concerned, would you recommand this - and as asked in my original post, can this be done whilst the system is "in-use"?

Thanks...

 

[KenCunningham]

Probably export/import is an option, but for more detail (and at the risk of being accused of encouraging cross-posting) I suggest you pose the same question (perhaps with more detail) in one of the Oracle forums on Tek-Tips.

 

[rneve]

Roomer,

You say the system is slowing down..
Do you know for sure that is diskaccess?
Or is your system Memory/CPU/Network bound?

Can you post the output of vmstat/iostat or other tools.

Richard

 

[Roomer]

kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
0 0 329433 120 0 1 1 107 243 0 163 696 775 15 5 69 10
6 2 329433 127 0 2 0 348 574 0 794 10008 66103 58 40 1 0
8 2 327957 1296 0 0 0 145 246 0 628 9859 69916 54 44 1 0
7 3 326707 1039 0 55 0 0 0 0 698 8858 81148 49 47 3 1
9 3 326668 389 0 1 0 0 0 0 709 8774 88193 48 50 1 1
7 3 326668 126 0 1 0 204 408 0 649 7952 87858 46 50 3 1
7 3 326668 124 0 5 0 328 1032 0 742 9732 76811 56 41 2 0
12 5 326668 123 0 0 0 309 664 0 680 8615 84769 51 46 3 1
4 3 326668 122 0 12 0 337 778 0 719 8711 87714 48 46 4 2
4 3 326668 128 0 10 0 335 789 0 719 8530 80640 46 47 4 2

 

[Roomer]

tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 4.5 15.4 5.2 69.0 10.3

Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk1 2.7 26.3 3.7 4684368 30931483
hdisk0 4.6 43.2 7.3 26489391 32045319
hdisk5 4.1 199.7 7.8 167617035 102654872
hdisk4 3.2 72.1 5.0 11720715 85835220
hdisk6 0.0 0.0 0.0 39111 180
hdisk7 14.4 763.4 59.8 984087061 49224624
hdisk3 13.7 693.8 56.5 902073268 37009724
hdisk2 1.5 153.5 3.6 117074896 90621804
cd0 0.0 0.0 0.0 0 0

tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 396.5 57.6 41.3 0.4 0.7

Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk1 4.0 17.9 4.5 32 4
hdisk0 6.9 43.7 10.9 84 4
hdisk5 3.0 19.9 3.0 0 40
hdisk4 2.0 35.7 2.5 8 64
hdisk6 0.0 0.0 0.0 0 0
hdisk7 28.8 543.9 88.8 1056 40
hdisk3 43.7 952.9 119.1 1856 64
hdisk2 0.0 0.0 0.0 0 0
cd0 0.0 0.0 0.0 0 0

tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 396.0 53.7 45.5 0.5 0.2

Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk1 14.0 409.0 19.5 0 820
hdisk0 17.0 415.0 20.9 12 820
hdisk5 1.0 4.0 0.5 0 8
hdisk4 0.5 4.0 0.5 0 8
hdisk6 0.0 0.0 0.0 0 0
hdisk7 39.4 722.2 110.7 1440 8
hdisk3 29.4 586.5 85.8 1176 0
hdisk2 0.0 0.0 0.0 0 0
cd0 0.0 0.0 0.0 0 0

tty: tin tout avg-cpu: % user % sys % idle % iowait
0.0 396.5 51.9 45.8 1.4 0.9

Disks: % tm_act Kbps tps Kb_read Kb_wrtn
hdisk1 6.5 177.8 12.5 0 356
hdisk0 9.5 183.8 14.0 12 356
hdisk5 0.5 4.0 0.5 0 8
hdisk4 0.5 6.0 1.0 0 12
hdisk6 0.0 0.0 0.0 0 0
hdisk7 40.0 811.0 124.8 1616 8
hdisk3 39.0 533.3 92.4 1068 0
hdisk2 0.0 0.0 0.0 0 0
cd0 0.0 0.0 0.0 0 0

 

[Roomer]

Topas Monitor for host: hqax00 EVENTS/QUEUES FILE/TTY
Thu Mar 11 09:49:03 2004 Interval: 2 Cswitch 22120 Readch 106715592
Syscall 12115 Writech 4126
Kernel 34.0 |########## | Reads 13142 Rawin 0
User 65.2 |################## | Writes 59 Ttyout 750
Wait 0.3 | | Forks 1 Igets 0
Idle 0.2 | | Execs 1 Namei 107
Runqueue 2.1 Dirblk 0
Interf KBPS I-Pack O-Pack KB-In KB-Out Waitqueue 2.6
en0 15.6 67.8 56.3 8.4 7.2
lo0 0.4 3.9 3.9 0.2 0.2 PAGING MEMORY
Faults 626 Real,MB 5119
Disk Busy% KBPS TPS KB-Read KB-Writ Steals 534 % Comp 23.0
hdisk7 43.4 872.3 123.2 872.3 0.0 PgspIn 3 % Noncomp 77.0
hdisk3 38.9 521.0 81.8 521.0 0.0 PgspOut 14 % Client 0.0
hdisk0 9.4 71.7 12.4 13.9 57.8 PageIn 353
hdisk1 6.9 57.8 8.9 0.0 57.8 PageOut 14 PAGING SPACE
hdisk5 0.9 5.9 1.4 5.9 0.0 Sios 226 Size,MB 2048
% Used 40.2
oracle (44574) 47.5% PgSp: 2.3mb oracle8 % Free 59.7
oracle (41142) 26.9% PgSp: 3.9mb oracle8
oracle (75322) 24.0% PgSp: 5.5mb oracle8
oracle (15798) 19.5% PgSp: 5.9mb oracle8 Press "h" for help screen.
oracle (58140) 19.3% PgSp: 6.7mb oracle8 Press "q" to quit program.
oracle (44864) 16.8% PgSp: 5.8mb oracle8
oracle (74234) 12.7% PgSp: 3.8mb oracle8
oracle (63194) 9.2% PgSp: 6.1mb oracle8
oracle (86850) 7.9% PgSp: 6.1mb oracle8
oracle (58432) 7.3% PgSp: 7.5mb oracle8
oracle (45408) 6.8% PgSp: 3.1mb oracle8
oracle (79084) 6.0% PgSp: 4.6mb oracle8
oracle (66386) 5.0% PgSp: 5.8mb oracle8
oracle (76228) 4.3% PgSp: 3.3mb oracle8
oracle (48500) 4.0% PgSp: 3.0mb oracle8
oracle (86708) 4.0% PgSp: 1.2mb oracle8

 

[Roomer]

rneve,

thanks for the reply.. I've posted info here...
Thanks.,

 

[rneve]

Roomer,
Looks more like a memory problem.
I see some paging activity. I/O activity is not high.
You also have a lot of waiting threads.
I/O activity is not high.

So I think it is a memory problem and not a I/O problem.

Regards

 

[Roomer]

nmon output ... any further assumptions???


CPU Utilisation +-------------------------------------------------+
CPU User% Sys% Wait% Idle|0 |25 |50 |75 100|
0 59.0 37.5 2.0 1.5|UUUUUUUUUUUUUUUUUUUUUUUUUUUUUssssssssssssssssssW>|
1 41.0 55.5 1.5 2.0|UUUUUUUUUUUUUUUUUUUUsssssssssssssssssssssssssss>>|
2 49.3 44.8 4.5 1.5|UUUUUUUUUUUUUUUUUUUUUUUUssssssssssssssssssssssWW>|
3 64.0 27.0 1.5 7.5|UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUsssssssssssss > |
+-------------------------------------------------+
53.4 41.1 2.4 3.1|UUUUUUUUUUUUUUUUUUUUUUUUUUssssssssssssssssssssW >
+-------------------------------------------------+
Memory Use Physical Virtual Paging pages/sec In Out VM parameters
% Used 100.0% 59.6% to Paging Space 70.9 7.5 numperm 77.4%
% Free 0.0% 40.4% to File System 185.6 6.0 minperm 20.0%
MB Used 5119.4MB 1219.6MB Page Scans 1138.7 maxperm 79.9%
MB Free 0.5MB 828.4MB Page Cycles 0.0 minfree 120
Total(MB) 5120.0MB 2048.0MB Page Reclaim 0.0 maxfree 128
Disk I/O
DiskName Busy Read Write |0 |25 |50 |75 100|
hdisk1 11% 31.9 29.9 kB|

http://WWWRRR

|
hdisk0 51% 251.5 29.9 kB|

http://WWWRRRRRRRRRRRRRRRRRRRRRRR

|R
hdisk5 1% 8.0 4.0 kB|WR > |
hdisk4 1% 2.0 16.0 kB|WR > |
hdisk6 0% 0.0 0.0 kB|> |
hdisk7 20% 347.3 4.0 kB|WRRRRRRRRRRR |
hdisk3 25% 385.2 0.0 kB|RRRRRRRRRRRRR |
hdisk2 0% 0.0 0.0 kB| > |
cd0 0% 0.0 0.0 kB|> |

 

[bi]

Roomer, I agree with what you have been told by the folks here, especially what KenCunningham suggested about posting the question on one of the Oracle forums (either the 8/8i forum or the 9i forum). Don't worry about cross-posting, but be sure to say you have also posted the question from an OS point of view on the AIX forum.

I'm just wondering if you have lots of rows that are not taking up contiguous space in the tables, and therefore on the actual disk, if that would use up a lot of memory when users are doing lookups or writing to the rows? I vaguely remember a DBA reorganizing or defragging tables for better performance.

 

[mjldba]

if Oracle functions similar to an Informix environment, tables with 8 or more extents will cause a performance hit.

Fragmented tables also means fragmented indexes & it'll only get worse unless you do something.

Must be some suggestions in one of the Oracle forums.

Good luck

 

[alexhu]

mjldba - it doesn't - the number of extents has no effect on performance (assuming we are talking about Oracle 8 or 9 )

Alex

 

[Roomer]

kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
3 12 371434 188813 0 19 42 969 1065 0 1438 7789 35439 27 31 5 37
0 10 371463 188797 0 68 68 1442 1514 0 1737 5004 10479 20 9 8 62
0 8 371463 188679 0 90 31 1546 1642 0 1796 5523 12834 33 11 8 48
0 9 371468 188720 0 43 62 1520 1714 0 1770 4701 10001 18 11 11 60
3 10 371470 188687 0 105 72 1543 1720 0 1903 5768 15936 22 15 6 58
1 11 372335 188051 0 14 115 1538 1817 0 1788 5773 18550 28 15 8 48
2 9 372335 187913 0 132 54 1531 1733 0 1857 5486 15918 27 14 5 54
7 8 372335 187797 0 113 52 1879 2054 0 2258 7596 17694 30 16 3 50
3 11 372335 187373 0 282 58 1637 1893 0 2161 5925 12068 16 12 6 66
2 11 372335 187434 0 50 71 1537 1774 0 1818 5454 16349 23 14 8 55
1 11 372335 187500 0 14 47 1541 1887 0 1945 7292 23943 31 18 8 44
2 5 372335 187546 0 45 55 1553 1881 0 1662 6365 16228 16 17 8 59
3 10 372339 187254 0 187 39 1423 1725 0 1877 5975 16306 21 14 6 59
0 11 372349 187390 0 10 78 1664 2010 0 1846 5470 16640 20 13 5 62
0 8 372349 187389 0 20 19 1266 1504 0 1512 5207 15889 20 10 9 61
3 13 372352 187361 0 43 30 1165 1372 0 1441 7093 42621 29 24 9 39
6 6 372397 187086 0 124 9 1124 1409 0 1453 7861 43566 29 28 8 35
5 5 372726 186294 0 223 1 1317 1692 0 1590 9095 25641 32 21 4 43
10 7 372739 185842 0 228 9 1614 2024 0 2040 8007 24665 31 21 4 44
2 9 372739 185506 0 188 16 1465 1913 0 1889 8227 41333 36 26 4 33

 

[alexhu]

What does your 3rd party Oracle DBA say ?

Alex