Modification and Tuning

I have already mentioned processes like increasing tablespaces, and adding and dropping tables. All DBAs do a lot more. The database is in your hands, and you will have to keep doing whatever it takes to keep it running smoothly. As you've seen by now, given all the typing mistakes you have undoubtedly already made, much caution and care must be given to working with the internals of the database. Hence usually only the DBA has the rights to create users, add or drop tables, change system parameters, and so forth. Although you may give your developers an instance or two to work in, you will keep the production instance and most others untouchable.

Most of the system changes you will make will revolve around tuning for performance improvements. These changes include things like deciding whether to use rollback segments or rollback tables, and how many archive logs to keep.

Let me put it another way. When the system really starts to slow down, or when backups start taking significantly longer than usual, the DBA is the person who will be called. I've mentioned creating temporary tablespaces for sorts as one way to improve performance, and there are others. However, the first thing the DBA does is analyze what's happening using the various Oracle tools and data dictionary views. With that information, the DBA can take actions to reduce processing bottlenecks, reduce seek times, and so forth. For example, the DBA might discover that one of the most used tables has an overabundance of integrity constraints, and upon analyis, the DBA may determine that some or many of them are not needed. Integrity constraints have the downside of reducing performance, and perhaps in some cases they should be minimized.

The same consideration applies to indexes. The existence of too many indexes on a table can have serious effects on performance when insert, update, and delete operations are carried out because Oracle must update the indexes at the same time. Performance analysis by the DBA can show whether there are too many indexes, and appropriate action can be taken.

The area of tuning is a true specialty. All I want to say here is that you must do your homework before installation to determine which installation parameters are best for your site. Do this by tapping expertise either within or without your organization. Also attend tuning classes; get some of the excellent , detailed technical books on tuning (Heidi Thorpe's Oracle 8i Tuning and Administration [Addison-Wesley, 2000] is a good example); and network with other DBAs and Oracle professionals by joining a local Oracle users group and attending conferences.

There is no magic wand I can give you to wave over your server. All I can say is to make sure you start out with enough disk space and memory, have a decent network in place, and make sure to put limits on your users so that they don't create runaway processes that will drag your system to a halt. After that, you just have to address each problem as it arises, analyzing it and then deciding what to do.

Note

Here's an important nugget of information for those who use Internet Explorer to download files: IE currently has the habit of downloading files first to a temporary file on your C: drive, and then copying the file over to the location you originally requested . For example, in the following scenario we started a download from Oracle, telling Oracle to send the file 92010NT_Disk3.zip to the directory D:\delete this folder :

Although your disk light will be blinking during downloading, and the status bar above will seem to make things clear, beware that IE is really writing the file to the C: drive.

When the status bar reaches about 99 percent, you'll suddenly see this message:

What this means is that IE is now copying the file from the temporary file on the C: drive (notice the internal name "K7LHY ") to the destination that you wanted.

Here's the problem with the way that IE does its downloading: If you check the space on your C: drive while this is going on, you will not see any changes in available space. If you do not have enough space on C:, IE will simply keep writing over what it has been able to download until it is finished. It will not tell you that you don't have any room. The net result is that you could spend hours downloading software, then run into a problem when you try to unzip it (you'll get the message that it is not a valid file type):

So if you use IE to download, beware that space on your C: drive is important !

Finally, here's one more thing for you to consider. In practice, as a DBA you will have very serious responsibilities for the ongoing successful operations of your database installation. You will need your own checklist for daily, weekly, monthly, quarterly, and so forth activities. You will be seen as the superoperator, troubleshooter, and all-around good person as you oil the wheels and air out the rooms to keep your shop running well. Your list of mandatory activities will include monitoring backups daily, checking logs for any problems, and checking the various relevant Web sites for alerts, new releases, warnings, and so forth. You will also have to spend time with your manuals because Oracle is constantly changing.