Function Points for Database Administration

Michael J. Hillanbrand II, Dulcian, Inc.

Are they really Function Points?

The International Function Point User Group and others involved in software metrics would definitely take exception to my use of the term. As a basis for measuring the Data Base Administration effort as an ongoing set of tasks, versus a development process, the term conveys the purpose - defining a means of measuring a task. However, I will bow to the purists and refer to my "metric" as a DBA Point from this point forward.

The metric used - or the "value" of the DBA Point is actually easier to translate into everyday usage than what Software Metric Gurus are fond of. A DBA point is equal to one person-hour. This hour translates to one fortieth of a DBA's workweek. Most DBA's that I know do not really work a forty-hour week, with the exception of a few really sharp ones who refuse to allow crises to occur on a regular basis and a few frauds. I still insist on calculating to a forty-hour week, since that is the implied contract in most of our culture.

The metric described in this paper was developed in the quest for an objective means to determine the cost of DBA services for our clients. This would give me a basis for billing on a premium basis versus an hourly rate for outsourcing Data Base Administration. The original DBA Point did not correlate to an hour, simply because a client would not necessarily have a clear idea of just where and how much DBA time they needed.

Measuring the Marigolds

Developing the method of measurement first entailed producing a list of DBA responsibilities and tasks. The second step was to derive a list of complications or quantification for the responsibilities. An example of a complication would be the number of tables in a database. Once the various complications were identified, we needed to define the point at which a "level" of complexity was breached. For example: When does the number of tables change the needed attention?

The next and most difficult step was building a matrix (I hate spreadsheets) using the tasks and complications, and assigning weights to each . The first version of this matrix was very subjective, but since I have quite a number of years of experience doing the job of consultant, I was able to come up with reasonable estimates.

Testing the Measurement

Once the metric had been initially formulated, it needed tuning. I ran several different scenarios through the matrix to fine tune it. I used organizations that I had been associated with in the past. I used first and second hand costing information from these past projects to help define the DBA points. The markets and scope of the operations of the "samples" were so varied, and I used so many that I thought I might just have something that was universal in scope.

The real test came at a local user group presentation. One of the attendees from a prominent University volunteered to allow us to use her site as a test of the matrix. Although we did come up with higher numbers than what that organization had tasked for their DBA, we also interviewed the DBA and found that it reflected her actual working hours, other than the time she also contributed as a developer.

Ready for Use

After adding a few suggestions from the DEVOUG audience the matrix appears ready for use. The matrix is reproduced here with zeroed values for reference.

The matrix is broken into categories that correspond to the list of responsibilities on the x-axis and the "level" of complication or difficulty involved on the y-axis. The fifth level of difficulty normally has a function to account for levels of difficulty beyond the original matrix.

To fill out the matrix, simply enter the appropriate DBA point within the grouping. If you have one production database, then the tally position in the ONE column would change to a 1, and if there were three databases the tally position in the THREE column would change to a 3.

If you want to make this matrix functional, follow the steps listed below:

1. Do a sum above on each tally position in the Subtotal row and sum all of the subtotal cells to the Total cell.
2. Determine the "Annual Cost" (include the cost of benefits) of a DBA at your site and divide this number by 40
3. This hourly cost is then multiplied by the total to achieve the projected cost of DBA Services at your site.

Category	One		Two		Three		Four		Five
# of Production Databases	1	0	2	0	3-4	0	5-8	0	8-10 +1 each 2	0
Avg. # DB/Node	1	0	2	0	---	0	3	0	4	0
# of users	1-16	0	17-32	0	33-48	0	49-64	0	65-128 + 1 every 32	0
# of Tables	<50	0	<100	0	<200	0	<300	0	>300 +1 each 500	0
# of Tablespaces	>5 <12	0	>12 or <5	0	>24	0	>50	0	>100 +1 each 100	0
# of disks	>3<7	0	>7	0	>16	0	>32	0	>64 +1 each 64	0
# of raw devices	----	0	<10	0	<25	0	<50	0	>100 +1 each 50	0
Volume of data	<500MB	0	<2G	0	2-10G	0	10-50G	0	51->100	0
Replication (Snapshots)	----	0	>5	0	>16	0	>32	0	>64 +1 each 32	0
Advanced Replication	----	0	----	0	<10 tables	0	>= 10 tables	0	>50 tables +1 each 25	0
Canned Modules	----	0	----	0	----	0	1-5	0	6-10 +1 each 5	0
P Query Option	----	0	X	0	----	0	----	0	----	0
Parallel DB	----	0	----	0	----	0	----	0	X	0
Ad-hoc tool # of users	----	0	1-16	0	17-32	0	33-64	0	65-128+1 each additional 32	0
Web Server	----	0	----	0	X	0	----	0	----	0
Oracle Office	----	0	----	0	----	0	X	0	>50 users + 1 each 50	0
# Development DB	----	0	1	0	2	0	3	0	4 + 1 each	0
		0		0		0		0		0
Subtotal				0
Total
Cost

The Categories

Although some of the categories are self explanatory, we can touch on some of them.

• Number of Production Databases: This category is to reflect only production databases. Tally the psuedo-production databases such as training ones in with the development databases.
• The average number of databases per node is an interesting level of complexity, since it gets slightly more difficult to manage in a multiple database environment. The DBA needs to remember to constantly check which SID he is pointing to. The main reason for this entry is the sharing of disk space and the attention to balancing the I/O.
• The number of users, tables and tablespaces reflects a reasonably designed environment. If there are six denormalized tables with 40 users logging into one schema and everything stuffed into the System tablespace in each of four databases, then apply a heavy penalty for ignorance. A good DBA will dig you out of this hole, but it will take some time.
• Raw devices can represent a significant advantage in some environments, at the cost of forcing the DBA to become a part-time System Administrator and the System Administrator becoming a part-time DBA.
• The sheer volume of data can present challenges, especially if a significant amount of it changes on a regular basis. Examples of this are the monthly loads to a Data Mart or Warehouse, or a large OLTP system.
• Canned Applications refer to the Financial and Manufacturing packages provided by Oracle and other vendors like SAP and PeopleSoft. These packages can require a lot of extra time from your DBA in order to keep up with the patches.
• Implementing the Parallel Query Option may require significant tuning resources from the DBA to realize its effectiveness.
• The use of Ad-hoc query tools may require significant attention to performance. Once the users start learning how to properly format a query, the pace eases up some. However, there is always turnover in the user ranks.
• The number of developers in a shop can also have a significant impact on the DBA's time. Although familiar with most of the tools from installation, developers often expect the DBA to be THE expert in everything that touches Oracle. This often means that the DBA performs any necessary research and is the primary contact for technical support.
• If a version of Oracle's WebServer is installed, I have rated that product as a three. Add another three for each node supporting an Oracle Web Application Server.
• Oracle's mail/scheduling and GroupWare solution, Oracle Office (and Oracle InterOffice) add an additional level of complexity simply by being an additional product and also by the amount of handholding necessary. You should assign and train a mail administrator other than the DBA. If you follow this strategy, deduct two from the original assignment of 4. In this situation, the responsibility is just another database with a minor twist.

Offsetting Factors

There are several offsetting factors that you may wish to add to your own version of this matrix. First and foremost of these would be the use of automation in the form of scripts. These scripts can relieve much of the day-to-day tedium often associated with DBA tasks and ensure that the DBA is informed and proactive to nascent problems.

Additional factors that must be taken into account include the experience level of your DBA, their personal energy level and their general intelligence. It is not possible to assign weights to these variables.

Summary

DBA points are a step forward in trying to manage the DBA activity in any shop. Utilizing the matrix described above, as a growing entity - not a static model, it is possible to estimate the time and monetary costs of Database Administration. Feel free to customize it for your own use.

About the Author

Michael Hillanbrand is a DBA and Principal with Dulcian, Inc. He has been a Vice President of the Delaware Valley Oracle Users Group for the last five years. He has over twenty years experience in IT and more than ten years in systems and Oracle. Mike is the architect of Dulcian's DBA Guard suite of Database Administration Services. Past presentations have been offered at DEVOUG, NYOUG and IOUG-A.

^© 1999 Dulcian, Inc.