Square DBA - Oracle DBA Talk

RAID and Oracle

RAID is the acronym for Redundant Arrays of Inexpensive Disks, and there are many different types or RAID (called RAID “levels”), and each has its own relative advantages and disadvantages. For Oracle9i databases, many of the RAID schemes do not posses the high performance required for an Oracle database, and moist Oracle professionals choose a RAID scheme that combines mirroring and block-level striping.

It is critical to note that the use of RAID does not guarantee against catastrophic disk failure. Oracle specifically recommends that all production databases be run in archivelog mode regardless of the RAID architecture, and that periodic Oracle backups should be performed. Remember that there are many components to I/O subsystems—including controllers, channels, disk adapters, SCSI adapters—and a failure of any of these components could cause unrecoverable disk failures of your database.

Below are the most commonly used RAID architectures for Oracle databases:

RAID 0

RAID 0 is commonly referred to as block-level striping. This is an excellent method for performing load balancing of the Oracle database on the disk devices, but it does nothing for high availability since none of the data is duplicated. Unlike manual datafile striping, where the Oracle professional divides an Oracle tablespace into small datafiles, with RAID 0, the Oracle datafile is automatically striped one block at a time across all of the disk devices. In this fashion, every datafile has pieces residing on each disk, and the disk I/O load will become very well balanced.

RAID 1

RAID 1 is commonly called disk mirroring. Since the disks are replicated, RAID 1 may involve double or triple mirroring. The RAID 1 architecture is designed such that a disk failure will cause the I/O subsystem to switch to one of the replicated disks with no service interruption. RAID 1 is use when high availability is critical, and with triple mirroring, the mean time to failure (MTTF) for an Oracle database is measured in decades.

RAID 0+1 (RAID 10)

Raid 0+1 is the combination of block-level striping and disk mirroring. The advent of RAID 0+1 has made Oracle-level striping obsolete since RAID 0+1 stripes at the block level, dealing out the table blocks, one block per disk, across each disk device. RAID 0+1 is also a far better striping alternative since it distributes the load evenly across all of the disk devices, and the load will rise and fall evenly across all of the disks. This relieves the Oracle administrator of the burden of manually striping Oracle tables across disks.

RAID 5

Some of the newer hardware based Raid 5 storage does extremely well in performance in data warehouses. RAID 5 is a good approach for Oracle data warehouses where the load speeds are not important and where the majority of the system I/O is read-only activity.

RAID  50

RAID 50 should have been called “RAID 03″ because it was implemented as a striped (RAID level 0) array whose segments were RAID 3 arrays (during mid-90s).  RAID 50 is more fault tolerant than RAID 5 but has twice the parity overhead.  High data transfer rates are achieved thanks to its RAID 5 array segments.  High I/O rates for small requests are achieved thanks to its RAID 0 striping.  Maybe a good solution for sites who would have otherwise gone with RAID 5 but need some additional performance boost.

Shrink table segment that experienced large delete

Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.

Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space. With Oracle Database 10g, you can now shrink segments directly, without taking the tablespaces or the objects offline. The process of shrinking a segment :

1. Enable row movement : ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
2. Recover space and amend the high water mark (HWM): ALTER TABLE scott.emp SHRINK SPACE; OR
3. Recover space, but don’t amend the high water mark (HWM) : ALTER TABLE scott.emp SHRINK SPACE COMPACT;
4. Recover space for the object and all dependant objects : ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Managing Oracle with an iPhone

My next (personal) project will be managing my Oracle databases with an iPhone. Yes I do need to still buy an iPhone first, but that is just a small detail. I already built and Oracle DBA application that I can use from my current phone so I will just take some tweaking to work on an iPhone. Yet I am thinking about a complete recoding of the application. My current application is custom built and not really designed to handle all Oracle databases. It is specific to many of the jobs running in my data warehouse. From any web browser I can see what is running on my database and perform simple DBA tasks. While there are some nice applications out there to manage a database remotely I want to have something a bit more custom.

My current application is built with an Oracle backend (of course) and running ASP as the front end. I think my new application will be running PL/SQl pages or PHP. I may even move to Oracle Express as my backend database with Oracle Application Express (Oracle APEX). This would allow me to freely distribute the full application. No matter how I go with this new application it will be slow going. This will be a personal project and with kids, a wife, and a full-time job my new iPhone Oracle application will not be on the top of my list.

ReiserFS vs. ext3

Today while waiting for a new server to be setup I received a call from the system admin. He was at the point of setting up some storage shelves on the new server and had a question. ReiserFS or EXT3? In my experience, EXT3 has been the most stable and easy to recover file system. ReiserFS is supposed to be the fastest with small files. ReiserFS is only fast in Namesys benchmarks. The problem with reiser is that readdir operations don’t return the filenames in the same order they were written to disk (inode order). So if you pack thousands of small files into a directory, then try to back them up with tar, or copy them, or search through them for a string, Your IO throughput drops to nearly 0 while the hard drive furiously seeks randomly across the whole platter for each and every file. JFS and XFS don’t have this problem, and were much faster for hosting large directories of small files (the Maildir test.)

Moving tables between database

Today I needed to move a table from a 9i database to a 10g database. What do you suppose is the quickest way to get the move completed? The table is 59+ million rows with 2 indexes. The indexes were easy. I knew I would not move the indexes and simply rebuild them on the new database.

To move the actual table my first thought was to use the export and import utilities. Unfortunately I did not have enough local storage to export the data locally. So I mounted via NFS a storage array I already had online. I started the export with the following command:

exp tables=localizer DIRECT=Y INDEXES=N STATISTICS=NONE BUFFER=2000000

Over an hour later and I am still waiting, so I started to look at my other methods. Exporting to an NFS mount point can be painfully slow. Here are a few options I considered.

1. Spool to Flat File, Load with SQL*Loader or External Tables: SQL*Plus does a reasonable job of unloading. Tom Kyte has a C utility for unloading data also.
2. SQL*Plus COPY command: Sadly deprecated, and without support for all data types, but a handy tool for basic data transfers. Allows arbitrary SQL on the data selection.
3. Database Links: Direct movement from one database to another. Aside from the network latency this is basically the same as reading data from your local database. If you combine database links and materialized views, then you can replicate remote data to your local database through the databae link.
4. Export/Import: Sometimes mistaken for part of a backup and recovery strategy!
5. Transportable Tablespaces: Closely related to Export/Import, allowing movement of entire tablespaces without the need for unloading and reloading data.
6. Data Pump: Export/Import for the new millenium - and with many new features.
7. Oracle Streams: Powerful functionality here. Non-trivial, but probably the modern tool of choice for the regular propagation of data around your corporate network.

In the end since this was simply a single table I went with just insert into as a select statement across database links. I still had to deal with some network latency but it was much faster than dealing with my NFS mounted storage.

Make your data warehouse not scalable.

I’ve got 10 of them. Just in case you are wondering, you should avoid these. Unfortunately some DBAs find their data warehouse suffering from performance problems, not because there is a platform issue, but often because the features are not used or are not used correctly. The following is meant to be sarcastic and read with a bit of humor. Consider it the “Comedy of Errors” data warehouse edition. More »