Infrastructure

Jonathan Lewis's picture

Fragmentation 4

This note is part four of a four-part series, and covers Index fragmentation. The whole series is as follows  Introduction Disk and Tablespace Fragmentation Table Fragmentation Index Fragmentation – this bit 4. Index “fragmentation”. The multiple extent and ASSM “fragmentation” that I described in the previous about table fragmentaiton applies equally well to indexes, of course, [...]

Jonathan Lewis's picture

Fragmentation 3

This note is part three of a four-part series, and covers Table fragmentation. The whole series is as follows Introduction Disk and Tablespace Fragmentation Table Fragmentation – this bit Index Fragmentation 3. Table “fragmentation”. In the introduction we discussed one type of table fragmentation that doesn’t (usually) matter – the fragmentation of a table into [...]

Jonathan Lewis's picture

Fragmentation 2

This note is part two of a four-part series, and covers Disk and Tablespace fragmentation. The whole series is as follows Introduction Disk and Tablespace Fragmentation – this bit Table Fragmentation Index Fragmentation 2.1 Disk “fragmentation”. Tablespaces are made up of files, and files are stored on discs – which are often “logical volumes” rather than [...]

Jonathan Lewis's picture

Changing UNDO

From time to time people run into problems with UNDO tablespaces that have grown much larger than expected (perhaps due to a rogue process doing far too much work) and refuse to shrink. The workaround is to create a new undo tablespace and switch the instance to use it – but even this simple procedure [...]

Jonathan Lewis's picture

Fragmentation 1

This note started life as a nutshell until I realised that it was going to be more of a coconut than a hazel nut and decided to turn it into a short series instead. I should manage to  post  four parts over the next two weeks: Introduction – this bit Disk and Tablespace Fragmentation Table [...]

Jonathan Lewis's picture

Unrecoverable

A recent question on the OTN database forum asked: “What’s the difference between index rebuild unrecoverable and nologging?” The most important difference, of course, is that unrecoverable is a deprecated option so you shouldn’t be using it even though it still works. Another important difference is that unrecoverable tells you exactly the risk you are [...]

Jonathan Lewis's picture

Index branches

Some time ago I published a little script that showed you how to read an index treedump and summarise the leaf block usage in the logical order of the index leaf blocks – allowing you to see fairly easily if the index had any areas where the blocks were poorly filled. Here’s another  way of looking [...]

Jonathan Lewis's picture

Coalesce

The following question came up in an email conversation a little while ago: Are you aware of any problems a large oltp site might have with running index coalesce during production hours, as opposed to doing index rebuilds in a maintenance window? The main overhead with index coalesce is that it generates a lot of [...]

Jonathan Lewis's picture

Memory

A client recently upgraded from 32-bit Oracle to 64-bit Oracle because this would allow a larger SGA. At the same time they increased their SGA from about 2GB to 3GB hoping to take more advantage of their 8GB of RAM. The performance of their system did not get better – in fact it got worse. [...]

Jonathan Lewis's picture

Locks

I usually think about locks (and the meaning of the lmode and request columns in v$lock) in terms of numbers, and never seem to remember which number goes with which name – apart from mode 6 = exclusive. So I’ve finally put up a little list somewhere I’ll always be able to find it if [...]

To prevent automated spam submissions leave this field empty.
Syndicate content