Submitted two talks to the East Cost Oracle Users Conference

Submitted two talks to the East Cost Oracle Users Conference:

http://www.eastcoastoracle.org/ssl/presentations.asp – NO LONGER EXISTS

First one is my Exadata talk that I gave at work.  Second is new Intro to SQL Tuning talk I’m preparing for my coworkers.  I should know some time in July if one of these is accepted.

Here are the abstracts:

Exadata Distinctives

This presentation focuses on the new features of Exadata for improving Oracle query performance.  It explains Exadata smart scans. We include diagrams that compare and contrast normal Oracle database servers and the flow of data from disk through memory buffers and networking to the end user with these same things on Exadata.  In particular we show that smart scans bypass the high speed memory of the buffer cache on the database server but use the lower speed flash memory on the cell servers as a form of buffer cache which has tradeoffs in terms of the sorts of queries that work best in each case.

This talk includes observations from real world experience with a large custom written datawarehouse and an implementation of OBIA including tuning this packaged PeopleSoft datawarehouse with minimal application change.  The query tuning focuses on encouraging SQL queries to use full scans to take advantage of the automatic tuning features of the smart scans.  It also shows how to bypass the Exadata features where normal Oracle query processing is more efficient.

The presentation concludes with notable concerns for any new adopter of Exadata including issues with tables that have more than 255 columns, performance problems of sort segments in RAC databases with many nodes, and stability issues that come with a new platform.

Introduction to Oracle SQL Tuning

At the core of SQL tuning are three fundamental concepts – join order, join method, and access method. This presentation will explain these three concepts and show that tuning SQL boils down to making the proper choice in each category. It is hard to imagine an area of SQL tuning that doesn’t touch on one or more of these three areas and in any case the presenter knows from experience that tuning the join order, join methods, and access methods consistently leads to fantastic results.

We will demonstrate how to tune an Oracle SQL query by examining and manipulating the join order, join methods, and access methods used in the query’s execution plan. First you must query the tables to determine the true number of rows returned from each table
and from each likely join. Then you identify which join order, join method, or access method chosen by the optimizer is inefficient. Then you use one of the suggested
methods to change the optimizer’s choice to be the efficient one. These methods include
gathering optimizer statistics, hints, setting init parameters, and adjusting the parallel
degree. Lastly, you must test your changed plan to make sure the new combination of join order, join methods and access methods actually improve the performance.

– Bobby