If this chapter has convinced you that proper physical design should be your number one performance goal as a database administrator, then it is time to get serious about how you manage your physical design lifecycle.
So, how do you get started in making a noticeable difference in the physical designs of the databases currently under your care and those you are destined to encounter and/or build in the future?
The first step to take is a mental one and involves making the commitment to pay more attention to excellent physical design. It should be mentioned that all project management personnel need to make this same commitment, as the effort required to guarantee a solid physical design foundation will take more up-front resources. But, make no mistake; to say that it is time well spent is an understatement.
The next step involves education of the database designer. Of course, the best way to become a design guru is to put time in the trenches and work with every style of database: heavy OLTP, data warehousing, and cross-platform data mart designs. You will learn very quickly which designs stand and which physical foundations crack when you go up
ｏＲＡＣＬＥ ｐＥＲＦＯＲＭＡＮＣＥ ｔＲＯＵＢＬＥＳＨＯＯＴＩＮＧ
against heavy-duty web-based and mega-user systems. Of course, there are also a variety of good educational classes and books on the subject of physical design to aid in the learning process.
Creating robust, efficient physical designs can be difficult and intricate work. You will need to arm yourself with some serious power tools that have the capability to slice through the difficulties involved in building and retrofitting complex physical database designs. Long gone are the days when a DBA or modeler could handle most of their work with a SQL query interface and a drawing tool. Today, relational databases are just too robust and contain too many complexities for such primitive aids.
At a minimum, you will need two things flanking both ends of your arsenal:
a serious data modeling tool and a robust performance-monitoring product. It has already been established that performance monitoring is really the validation of a database's physical design. When foundational cracks are identified with the monitor, you will need a high-quality design tool to aid in rectifying the situation.
For those physical DBAs who do not like to use data modeling tools, two other software products will be needed: a feature-rich database administration tool and a change control product. The database administration tool will be used to create new objects for a database, as well as modify properties of existing objects. This tool is normally used in an ad-hoc manner and is great for graphically redesigning a database in real-time mode.
The change control product is a different animal. If you will not use a data modeling tool to capture and version control the designs of your databases, then you will need another method for protecting designs that are in place and working. Having such "snapshot backups" of your database's schemas will prove invaluable when disaster strikes.
A seasoned DBA, who was managing a large packaged financial application, once learned the value of a change control tool. She had to make a complex change to one of the database's critical tables and had thought she had built the right script to do the job. Unfortunately, she did not have everything in place, and when she ran her change job, she ended up losing a number of important indexes that existed on the table.
ｏＲＡＣＬＥ ｐＥＲＦＯＲＭＡＮＣＥ ｔＲＯＵＢＬＥＳＨＯＯＴＩＮＧ
Worse yet, since her table and data looked okay, she thought all was well and did not know she had lost the necessary indexes. The next day, many parts of the application slowed down to a snail's pace as queries that used to complete in an instant were now taking forever.
The changed table was identified as the source of the problem, but while the DBA discovered that the table now had no indexes, she did not know which columns had been indexed (something not uncommon in huge financial applications). Through trial and error, she was able to get her indexing scheme back in place, but not before a lot of time had been lost.
This is one case where a good change control tool can save you. Nearly every good tool in this category offers a synchronization feature that allows a DBA to compare an up-and-running database with a saved snapshot of that database's, object definitions. Once differences are identified, a click of the mouse can restore any missing objects.
But, a change control tool can also help you in your physical design iterations. By periodically capturing changes you make to the physical design of your database, you can learn what worked and what did not.
And, if you make an "oops" and actually cause more harm than good, you can instruct your change control tool to automatically put things back to the way they were.
Now, if your company does not have the budget to purchase such a tool, you may be able to get by with taking full Oracle exports on a periodic basis with the ROWS parameter set to NO and backing up the export dump files. Doing exports in this manner will capture the DDL for all structures in the database without backing up the actual data. By doing exports in this way, you can build a basic change control process where your table structures, indexes, procedures, etc., are protected in such a way that you can, in an emergency, restore only certain object definitions that have accidentally been dropped.
However, there are drawbacks to using this approach because some objects (like procedures, packages, etc.) cannot be easily restored, and object definition changes cannot be as easily fixed as they can with a smart change control tool. However, it might be better than having no
ｏＲＡＣＬＥ ｐＥＲＦＯＲＭＡＮＣＥ ｔＲＯＵＢＬＥＳＨＯＯＴＩＮＧ
change control process at all.