Hotsos Symposium Speaker – Karen Morton

Karen Morton is a returning Hotsos Symposium presenter.

Biography

For more than 25 years, Karen has worked in information technology starting out as a mainframe programmer, developer, DBA, data architect, author, researcher, educator and consultant. Having used Oracle since the early '90s, she began teaching others how to use Oracle over a decade ago.

Karen is the co-author of three books from Apress: Beginning Oracle SQL, Expert Oracle Practices: Database Administration from the Oak Table and Pro Oracle SQL. Karen is a frequent speaker at conferences and user groups, a member of the OakTable Network, and an Oracle ACE .

Karen is also a member of the "Experts Panel" on both of the following Oracle user sites: SearchOracle and AllThingsOracle. In both forums, she answers questions posted by users about SQL Performance Optimization, writes articles and blog posts, and holds occasional webinars to help promote knowledge and information sharing within the Oracle community.

Presentation Title

SQL Performance Solutions: Refactor Mercilessly, Index Wisely

Abstracts

Achieving good SQL performance can often seem like a magic act where you never know if the trick is going to work or not. Sometimes it works (performance is good), and sometimes it doesn't (performance is bad). What can you do to eliminate the guesswork and create SQL that consistently performs as desired? It's not magic, but simply a matter of understanding how to best write (or rewrite) your SQL to help the Optimizer produce the best executions possible. It's also a matter of supporting our SQL with an effective set of indexes.

Part of the reason producing SQL that "just works" seems so hard is that we, as developers, often hold onto our coding styles long after they have become outdated or unwieldy. Particularly when maintaining SQL that was written by someone else, we often continue to use/reuse code that is no longer maintainable because it still works in some way, and we are afraid to modify it. But is that really effective? Obviously, the answer is "no". What we need to do is to remove redundancy, eliminate unused functionality, and rejuvenate obsolete designs and constructs in our SQL through refactoring. Refactoring shouldn't happen just once but instead should happen every time the code is reviewed or modified in order to eliminate inefficiencies and increase quality. The original formulation can be a good guide post, but may not be obsolete or simply proven to be inefficient when measured against the requirements of your production environment.

It may also be necessary to evaluate indexing strategy in order to achieve the best performance footprint. Just like your SQL, index effectiveness needs to be evaluated again and again over time. Indexes that worked well for one set of SQL may need to be adjusted to provide the best performance as SQL is modified and added. How do you know which indexes are "best"? How do you determine when to use single column indexes or multi-column indexes?

In this session, you will learn:

Common ways to rewrite SQL that make it perform better and more consistently

How to make your SQL easy to identify and test

How and when to add or modify indexes

How to determine the best choice of columns to include in an index and what order to place them