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:
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.
Just went through your introduction to sql tuning. Very well done.
I’ve been doing oracle for about the same time as you have. Dug very deep into tuning, feel very inadequate!
We’ve got peoplesoft also, (university) plus a boatload of other applications. Found you doing a google search on peoplesoft tuning.
With what “I think” I know I am amazed that Oracle can return complicated queries as fast as it does. However, you never know if you will come in tomorrow and find the optimizer making different decisions.
Peoplesoft has complicated queries (from the small amount I’ve actually delved into it) and so does an alumni fund raising system I work with. Some queries I just shake my head at because I cannot even get to first base figuring out what’s going on. (800 line plans).
Then with Oracle making changes every release (cardinality feedback really shook me in 11.2). “direct path reads” is another game changer that makes me wonder if just some more sga without it will be better.
Heck I even threw 4 gig (from 2 gig) at a database and the “smart” oracle memory mgt gave only a total of 1 gig to db cache and like 2.8 to the shared pool. I’ve just recently learned how to force more db cache and still use amm.
Lynn, thanks for your comment. Tuning is a deep subject for sure and Oracle is constantly changing not to mention the platforms it runs on (virtualization, Exadata, etc.) I don’t think anyone can know it all but I’ve enjoyed the things I’ve had a chance to dig into. My employer has a wide variety of systems including a number of datawarehouse and transactional systems so I see a lot of different things now unlike earlier in my career which was all PeopleSoft. But, it can be overwhelming and there is never enough time to go as deep as I’d like.