Date: Tuesday 07 July
Time: 18:00 - 21:00
Sponsor: Red Stack Technology
Location: Innovation Birmingham, Faraday Wharf, Holt St, Birmingham B7 4BB
Five Hints for Optimizing SQL - Jonathan Lewis
Adding hints to production code is a practice to be avoided if possible, though it's easy to make the case for emergency patching, and also as the basis for a method of generating SQL Plan Baselines. However, notwithstanding (and sometimes because of) the continuing enhancements to the optimizer, there are cases where the only sensible option is to constrain the broad brush strategy that the optimizer can take in a way that allows it to find a reasonable execution plan in a reasonable time. In this presentation we examine five of the "classic" hints that I believe are reasonable tactical weapons in the battle to beat the optimizer - (no_)merge, (no)_unnest, (no_)_subq, (no_)push_pred, and driving_site(). We will also glance briefly at a couple of other useful hints along the way.
Creating Test Data To Model Production - Jonathan Lewis
In this presentation we cover a few topics that you need to consider when setting up a framework for testing. First, how to generate large amounts of data quickly and repeatably; then how to ensure that the data content and pattern is a reasonable match to the system you want to emulate. We’ll examine a couple of cases to show that you can be caught out by such simple errors as creating indexes at the wrong point in your testing, and the differences between using CTAS (create table as select) and PL/SQL loops (especially run concurrently) to generate data.
Although it takes a lot of effort to create realistic models of production activity, we’ll also look at simple ways of modelling some aspects of concurrency, and show how concurrency can make a massive difference to what goes on inside the database – time permitting we will take a look at a few dumps of internal structures (disk and memory) to highlight some of the effects of concurrency.
Jonathan is a well-known figure in the Oracle world with more than 25 years experience of using the database (and several years on other products). He has published three books, contributed to three others, runs a couple of websites and contributes fairly regularly to newsgroups, forums, User Group magazines, as well as speaking at events around the world. He has been self-employed for most of his time in the IT industry. He specialises in short-term assignments, typically of a design, review, or trouble-shooting nature.