Changing performance strategies for modern operations

Three important factors to take into account in performance tuning of database operations:

· Automation

· Methodology

· Collaboration

Automation

By automation I mean continuous performance monitoring across all the databases in the enterprise accessible from a single interface. Systems are too complex and DBAs are responsible for too many databases to efficiently monitor and maintain these systems without automation. Automation reduces costs and alarms notifications let DBAs quickly find and correct problematic SQL statements before they impact the bottom line.

Methodology

A standardized intelligent methodology that clearly prioritizes issues will simplifies and streamlines the processes of problem identification and solution, otherwise effort can be wasted on inconsequential issues.

Collaboration

Effective collaboration and healthy communication is essential to the efficiency of the enterprise. Development, QA and DBAs work together using Embarcadero’s products to easily share information (like profiling snapshots) for faster problem identification and assignment of work

Changing Trends for Modern Operations

I think most people would agree on the importance of automation and have either implemented automated systems or want to implement an automated system. On the other hand the areas of methodology and collaboration are often ignored. Without a methodology, DBAs can be struck with what my colleague Gaja wonderfully coined “compulsive tuning disorder” meaning they try to tune “nonexistent or irrelevant problems” as Christian Antognini put it in his book Troubleshooting Oracle Performance.

Methodology

Our methodology at Embarcadero with DB Optimizer is to first look at overall database performance with the system load chart which displays “Average Active Sessions” (AAS) as coined by my esteemed colleague John Beresniewicz) . If AAS goes above the max CPU line then there is a bottleneck. If there is a bottleneck, identify whether it is from

1. Database configuration Issue

2. Machine Sizing Issue

3. SQL inefficiencies

A database configuration issue is visible if the load in the load chart is coming from wait events that represent configuration issues such as redo log file switch or log file sizes.

A machine sizing problem for example would be visible if the CPU spiked above the max CPU line, showing a bottleneck on CPU, and all the CPU usage was evenly distributed of all executing SQL, ie there is no resource intensive SQL. In this case the machine is undersized for the application.

SQL inefficiencies would be visible, On the other hand, if there is a SQL statement that clearly more resource intensive than the average SQL on the system, then tune that SQL. In this final case of an inefficient SQL we would want to check

1. Correct execution plan is chosen

2. Indexes necessary exist

3. Schema correctly defined such as unique constraints, FK/PK and not null constraints that help the optimizer

Our tools, Performance Center and DB Optimizer provide this information quickly and easily.

Collaboration

A common toolset enables development, QA and DBAs to work together and share information more easily for faster problem identification and assignment of work

Effective collaboration and healthy communication is essential to the efficiency of the enterprise. Too often teams such as Dev and DBAs use different tools and speak a different language. By using a common tool for performance tuning can Bridge the gap between developers and DBAs to quickly and clearly identify the root cause of the problem and who’s responsible

With a common tool whose analysis can be save to a flat file, emailed it’s easy to share information.

For example there might be a problem in the way the database is configured. If the configuration issue is found by a developer, then the developer can send that DB Optimizer session to the DBA and have it addressed. On the other hand, the DBA finds a problem with the query. The DBA can send that session to the developer responsible for that query. Finally, Q/A can run DB Optimizer and look for spikes in the load chart. If there is spike in load, then QA can mail the saved session to the DBA or developer.