Are DBAs Really Necessary?

1. Are DBAs Really Necessary? A DBA Retrospective Mike Ault Burleson Consulting

2. A DBA Retrospective • Began working with Oracle 1990, version 6.0.22 • VLDB was 10 Gigabytes and 500 Users • No Web! (At least it was very limited) • PCLink • AOL (For Mac users only!!!) • Compuserve had Oracle forum

3. A DBA Retrospective • There were web crawling tools • Web was mostly academics • Most systems where Dumb Terminals (VT100, VT200, Wyse) • Used A central computing node – DEC VAX, HPUX, SUN, Sequent • PCs were expensive

4. A DBA Retrospective • Systems were 16 Bit, very limited memory • Disks were just starting to get to gigabyte size • Winchester Technology meant 400 pound rack mount systems for 90 megabytes

5. A DBA Retrospective • Began working with Oracle for ASRM (Advanced Solid Rocket Motor) project with Aerojet in Iuka, Mi. (Indian word for No-Where!) • Had a DEC/VAX system with 100 megabytes of solid state drive and a couple hundred of regular drives.

6. DBA Retrospective The biggest version 6 tuning items were: • File placement on the individual disks • Defragmentation of tablespaces, tables and indexes • Determining when to use indexes and when to defeat them (remember null concatenation? Adding zero? Multiplying by 1?) • Tuning the data dictionary with the 22 or so different data dictionary cache parameters, some sites had DBAs whose sole job was monitoring and tuning these. • Tuning rollback segments and redo logging • Managing backups and exports

7. DBA Retrospective In version 6 we had: • 3 system level grants • DBA • CONNECT • RESOURCE • 12 object level privileges • About 112 initialization parameters.

8. DBA Retrospective • Around 1991/1992 Oracle 7 was introduced. • Oracle7 offered a number of improvements • Some said it would spell the doom of the DBA job. • Consolidated the data dictionary parameters so you couldn't adjust them anymore • Added something called a CBO that would take tuning away! • Added hints so you could tell the SQL what to do • What the heck are these statistics?

9. DBA Retrospective • Left Aerojet just ahead of the closing of the project by Congress to save money. (Actually cost more to shut it down than to keep it running, go figure) • Went to work for a drug company, Marion Merrill DOW • Got to travel to Canada, England, Italy and France as a part of my job. • Managed about 20 Oracle 6 instances in 5 countries, all from Kansas City, Ks.

10. DBA Retrospective • Began my first book, "Oracle 7.0 Administration and Management" • I was tired of having to search through 10 books to get a single answer. • The book was completed in 1993 and published in 1994 and was one of the first third-party books about Oracle administration. • Kevin Loney and I disagree about who was first, but such is life.

11. DBA Retrospective • 32 Bit was here • PL/SQL was also coming into its own. • Also added was the optimal parameter for rollback segments. • From the proceedings of the 1992 IOUW (International Oracle Users World: • "Macintosh, UNIX and Oracle a Winning Combination" (Right) • "Using WordPerfect Through Oracle" (Another technology with a future) • "Fragmentation - A DBA Nightmare!"

12. DBA Retrospective Couldn't help but pull this quote describing a large system: "One of our database servers is a Sequent Multi-Processor computer with roughly 240 megabytes of memory, 12-50 MHz 486 processors, 12 smc disk drives and 18 qcic disk drives...with a mix of 0.5, .75, 1.5 and 1.6 gigabyte drives...100M SYSTEM, 200M TEMP, 200M Rollback tablespaces..."

13. DBA Retrospective • A number of presentations about SQLForms, SQLMenu, SQLReports and SQLCase. Also a number about something called constraints... • I can remember arguing that any SGA over 10 megabytes was probably oversized on the Oracle Compuserve forum. • It was obviously the days of wooden computers (at least the first Apple had a wooden case) and iron DBAs...to bad they rusted.

14. DBA Retrospective • Got an additional 80 database level privileges to manage, they deconstructed the DBA, CONNECT and RESOURCE grants to give us more control over who had what privilege. • Even in 10g however, DBA, RESOURCE and CONNECT are the predominant database level grants I see. • Object level grants jumped to 23, with multiple sub-grants each. • 154 initialization parameters as of 7.3.

15. DBA Retrospective In my book, on page 9, I defined a DBAs job as consisting of 13 basic tasks: • Installing and upgrading Oracle server and application tools. • Allocating system storage and planning future storage requirements for the database • Creating primary database storage structures once developers have designed an application • Creating primary database objects (tables, views, indexes) once the application designers have designed an application • Modifying the database structure as necessary, from information given by application developers. • Enrolling users and maintaining system security

16. DBA Retrospective • Ensuring compliance with Oracle licensing agreements • Controlling and monitoring user access to the database • Monitoring and optimizing the performance of the database • Planning for backup and recovery of database information. • Maintaining archived data on appropriate storage devices. • Backing up and restoring the database • Contacting Oracle Corporation for technical support

17. DBA Retrospective • The most excited I have been over a book was when I saw the Oracle 7.0 book on display in a cabinet at the 1994 EOUG. • I helped Buffy Emsley by getting her some additional copies from the local Wiley jobber.

18. DBA Retrospective Some titles from that conference: • “Database Triggers” • “Adopting Open Systems – And Succeeding!” • “Successful Deployment of Parallel Server” • “The Parallel Query Option in Oracle7, Release 7.1” • “The Oracle7 Query Optimizer: Experiences, Tips and Techniques” • “The Impact of the Trend of Outsourcing”

19. DBA Retrospective • The EOUG conference was held in Maastricht, Netherlands. • The first time I remember seeing Larry Ellison, he was demonstrating the revolutionary Network Computer that was going to replace all desktops…it’s descendents are the set top boxes used for getting at the web and email. • The up and coming technology was client server. • We had discussions on Compuserve about exposing databases on this thing called the Web. I was against it…

20. DBA Retrospective • Went to Rome Italy in 1995 on a Parallel Server 7.1 project to do one of the first Play –on–demand services for movies and content. • Consisted of an Oracle7.1 front end on Sequents that managed the content and billing and a NCube2 processor with (I believe) 256 processors on the backend that served the digitized movies to the DTA converters that fed the signal through the phone lines back to the client. • Parallel server was used primarily for failover and backup using a mirror split.

21. DBA Retrospective • In 1995-96 they introduced 8, followed quickly by 8i. • This release was called the Object Oracle release because they provided fledging support of objects through the creation of types and object tables. • They also added VARRAYs, nested tables, index only tables, reverse key indexes, and a plethora of stored packages. • There was no polymorphism or inheritance.

22. DBA Retrospective • They also added support for LOBs. • They also added in many tablespace options targeting the many tasks around the fragmentation issues with tablespaces. • They gave us support for JAVA. • One of my presentations was called "Going Out for the LOB". I still get requests for copies of it. • I wrote "Oracle8 Administration and Management", "The Oracle8 Black Book" and the Exam Cram series (1998 or so). • On page xxxiv of the Administration book I list the same 13 responsibilities for the Oracle DBA.

23. DBA Retrospective However, I believe we need to add to that list at this point: • Management of object related features • Determination of LOB storage options • Assistance with RAID configuration • Determination of proper index strategy (normal, reverse, IOT, bitmapped) • Education of Developers in Oracle features and their use • Management of distributed environments • Management of parallel server and parallel query • Oracle8 also included partitioning and a bunch more hints. The Cost based optimizer was also improved in 8.

24. DBA Retrospective Some titles from the 1997 IOUG-Alive conference, Dallas, Texas: • “Database Without Users: Administering a Database for the Web” • “Performance Comparisons Using Bitmap Indexes, Hash Clusters and Histograms” • “Disaster Recovery “Are you Prepared?” • “Getting Started With Java” • “Hiring (or being hired as) an Oracle DBA” • “The RDB7 Path to Network Computing” (What?) • “100,001 Users, a Test Odyssey”

25. DBA Retrospective In 2000 I wrote “Oracle8i Administration and Management” • Introduced the concept of table and index partitions. • Prior to 8 there were partitioned views which allowed you to treat a collection of identically structured tables as a single logical table, it was kludgey and didn’t work very well. • They expanded the partition concept to include sub partitions and added several column types to the partition capabilities such as LOBs. • Introduced the concept of OUTLINES to control SQL execution. • Added concept of resource groups as well as row level security. • We were also given temporary tables in 8i. • In 8i we jumped to 194 initialization parameters.

26. DBA Retrospective So in 8i we add to the mix of responsibilities: • Determine and manage partitions and sub-partitions • Determine proper use of outlines • Create, manage and maintain resource groups • Create manage and maintain global temporary tables • If CBO is used, manage and maintain statistics gathering processes • Create and manage materialized views, summaries and snapshots

27. DBA Retrospective Tuning was now more complex. • Add outlines, the CBO, resource groups and all of the new index and table types • The CBO, while making some things easier also made getting stable execution plans more difficult. • The shear number of hints made choosing the correct one difficult. • While the 8/8i release made some things easier, they made many parts of the DBA job more complex. • What is this thing called cache fusion? • What happened to OPS? • What the heck is a materialized view? DBAs were needed more than ever!

28. DBA Retrospective In 2001-2002 Oracle9i was released. • I was a part of the Beta and wrote the “Oracle Administration and Management” book for Wiley • Was the last book I have wrote for them • Oracle added many enhancements to the CBO, tablespaces, partitioning, added external tables, new table management options, expanded object orientation of the DB, added internalized Java. • They also added contexts and operators to the list of objects you could create. • The concept of spfiles was introduced and the ability to dynamically alter many initialization parameters. • Also added: fine grained auditing, bitmap join indexes, domain and functional indexes.

29. DBA Retrospective • Oracle provided the ability to automatically adjust sort area size, hash area size and their associated IO parameters. • Through use of the max_sga_size you could dynamically (i.e. the DBA resets them on the fly) alter various memory constructs. • Concept of automatic UNDO (replacing rollback segments) is introduced.

30. DBA Retrospective • Also enhanced in 9i was RAC, the offspring of Oracle parallel server. • With its concept of cache fusion and use of the high speed interconnect it revolutionized the concept of parallel databases almost single-handedly. • XML and HTML are essentially native data types. • Java is firmly rooted in the kernel. Materialized views are main stream.

31. DBA Retrospective “Miss Jones, we need Oracle RAC. Please find out what RAC is?”

32. DBA Retrospective I attended an ECO conference in 2000 where one of the keynote speakers, an Oracle VP, spent an hour telling a room full of DBAs how they were going to be eliminated by 9i… Adding to our list of tasks:

33. DBA Retrospective • Monitoring and managing the automatic and dynamic sizing parameters • Monitoring and managing the automated UNDO (it isn’t set and forget) • Monitoring and tuning RAC environments, especially the cluster interconnect • Manage and maintain fine grained auditing • Manage and maintain row level security • Manage and maintain fine grained access controls

34. DBA Retrospective So, as of Oracle9i what is our list of DBA tasks? • Installing and upgrading Oracle server and application tools. • Allocating system storage and planning future storage requirements for the database • Creating primary database storage structures once developers have designed an application • Creating primary database objects (tables, views, indexes) once the application designers have designed an application • Modifying the database structure as necessary, from information given by application developers. • Enrolling users and maintaining system security • Ensuring compliance with Oracle licensing agreements

35. DBA Retrospective • Controlling and monitoring user access to the database • Monitoring and optimizing the performance of the database • Planning for backup and recovery of database information. • Maintaining archived data on appropriate storage devices. • Backing up and restoring the database • Contacting Oracle Corporation for technical support • Management of object related features • Determination of LOB storage options • Assistance with RAID configuration

36. DBA Retrospective • Determination of proper index strategy (normal, reverse, IOT, bitmapped) • Education of Developers in Oracle features and their use • Management of distributed environments • Management of parallel server and parallel query • Determine and manage partitions and sub-partitions • Determine proper use of outlines • Create, manage and maintain resource groups • Create manage and maintain global temporary tables

37. DBA Retrospective • If CBO is used, manage and maintain statistics gathering processes • Create and manage materialized views, summaries and snapshots • Monitoring and managing the automatic and dynamic sizing parameters • Monitoring and managing the automated UNDO (it isn’t set and forget) • Monitoring and tuning RAC environments, especially the cluster interconnect • Manage and maintain fine grained auditing • Manage and maintain row level security • Manage and maintain fine grained access controls

38. DBA Retrospective I began to worry if they eliminated us anymore, I would never get another vacation…

39. DBA Retrospective Now we have Oracle10g. • With OEM you can (stress on you) determine badly performing SQL and have Oracle, at your request, provide suggestions as to the tuning and after your approval, apply changes. • With OEM we now have almost single button backups (assuming you have been assimilated into the RMAN Borg collective). • With ADDM, AMM, ASSM the monitoring of statistics has been automated and the Oracle database will give suggestions as to what to tune, you still have to tell it to do the operations.

40. DBA Retrospective • With AMM memory will be managed up to SGA_TARGET settings automatically. • ASSM removes the need to set freelists, freelist groups and PCTFREE. • PGA_AGGREGATE_TARGET removes the need to monitor various sort and hash parameters. • If you use ASM tablespace management virtually disappears. • The new outline feature for SQL provides nearly automatic tuning for bad SQL. In 10g Statistics gathering is now internalized.

41. DBA Retrospective Are we doomed!!!??? Not quite. Let’s look at the list with the items we assume Oracle can now do for us removed.

42. DBA Retrospective • Installing and upgrading Oracle server and application tools. • Allocating system storage and planning future storage requirements for the database • Creating primary database storage structures once developers have designed an application • Creating primary database objects (tables, views, indexes) once the application designers have designed an application • Modifying the database structure as necessary, from information given by application developers. • Enrolling users and maintaining system security • Ensuring compliance with Oracle licensing agreements

43. DBA Retrospective • Controlling and monitoring user access to the database • Planning for backup and recovery of database information. • Maintaining archived data on appropriate storage devices. • Contacting Oracle Corporation for technical support • Management of object related features • Determination of LOB storage options • Assistance with RAID configuration • Determination of proper index strategy (normal, reverse, IOT, bitmapped)

44. DBA Retrospective • Education of Developers in Oracle features and their use • Management of distributed environments • Management of parallel server and parallel query • Determine and manage partitions and sub-partitions • Determine proper use of outlines • Create, manage and maintain resource groups • Create manage and maintain global temporary tables

45. DBA Retrospective • Create and manage materialized views, summaries and snapshots • Monitoring and managing the automatic and dynamic sizing parameters • Monitoring and managing the automated UNDO (it isn’t set and forget) • Monitoring and tuning RAC environments, especially the cluster interconnect • Manage and maintain fine grained auditing • Manage and maintain row level security • Manage and maintain fine grained access controls

46. DBA Retrospective Add to the above: • Monitor and maintain application servers, web servers, connection managers, LDAP and other servers as well as the entire client to database environment • In many shops the DBA does it all!

47. DBA Retrospective • Yes, Oracle has taken some of the more mundane tasks and automated them. Quite frankly most DBAs set many of the “automated” parameters and forgot them unless a problem came up. • What actual percentage of time have the new features saved a fully qualified and competent DBA? Not much I am afraid.

48. DBA Retrospective As Don Burleson has said: • “The new automated features of 10g are aimed at the mom and pop shops that can’t afford a full time DBA, or for shops that have hundreds of instances and can’t afford to have DBAs monitoring and maintaining any but the most important ones, they can’t replace a fully qualified Oracle DBA”

49. DBA Retrospective • I have no doubt they will automate the complete management of SQL, tables and indexes and tablespaces as well as some memory and tuning parameters. • So give up on items 12, 13, 24 and 25. • Gee, how will the other 25 (26 if you include the final one added above) items fill our time?

50. DBA Retrospective The death of the DBA has been greatly overstated!