Tracking Down Performance Tuning Issues

Now that your organization’s database is functioning, it’s time to make certain it is performing optimally.

The first step in making any improvement is closely watching the full extent of its different operations. What performance issues are you and your colleagues observing?

Are you noticing sluggish processing times? Do you continually get “out of storage” messages? Have you or another database administrator evaluated the SQL? What happens if everyone in a particular group is running a different query at the same time?

We have found that, sometimes, only a few adjustments are needed to improve database performance.

We’ve discovered that as few as a quarter of the issues may account for 75% of a database’s diminished performance.

Those adjustments you’ve made in the tuning process can change the dynamic environment of the database so you need to constantly review the system performance before beginning each performance-tuning phase.

Performance improvement is an ongoing challenge. Sam Lucido, manager of Oracle global marketing at EMC, said during an Oracle OpenWorld 2013 panel that most database administrators spend a good bit of their time with performance tuning for Oracle databases.

An important first step to take well before any changes are made to the system is to carefully evaluate the various components, including software, system resources, and hardware. It may be the issue is not what you originally expected. There are, Lucido says, 50 or more performance metrics collected in an Oracle environment every five to 10 minutes, or on demand. This can include information about the disk or operating environment, such as memory I/O or caching.

Undertaking a comprehensive system review before instituting any changes should alleviate wasted time and better orient you on the path and processes needed to pinpoint the performance issues.

Of course, one factor that may be overlooked is the database design or relational data model. Resolving this issue may be complex and time consuming, if this is indeed the source of your performance problems.

It is also important to remember that performance issues often change over time. For example, atypical scenarios may arise. These can change how the database responds to queries.

Understanding what questions to ask and where to look for both the typical and atypical problems you may encounter are integral to performance tuning.

This is why we developed the 5S Model. Our performance-tuning framework addresses each stage of the process, as well as key performance issues:

• SQL: Slicing, dicing, and selecting the data.
• Space: Addressing where the data lives.
• Sessions: Who, what, where?
• Statistics: Judging a book by its cover.
• Scheduled Processes: Developing the best scheduling plans.

These five areas help organize the tuning approach and define the major concerns beyond the architecture, setup, and data model. The key to remember is that performance-tuning becomes less of a mystery if it can be measured, documented, affected, and improved.

Need some experienced assistance with evaluating and fine-tuning your database performance? Let the Datavail team work with you to explore, identify, and resolve performance issues with the experience and expertise you need. Our experts can answer your questions by phone, but we are also available 24x7x365 through our online chat system.