AUDIENCE:   This course is ideal for: Database administrators, Developers, Power end users proficient in basic SQL, Data warehouse support team, First line support, BusinessObjects “managers”, SQL support, Central Oracle support team members. PREREQUISITES:   General familiarity with Oracle as an SQL end user. DURATION:   2 days. Hand on. OBJECTIVES:   The Oracle RDBMS is typically deployed throughout a business, from the corporate mainframe, to enterprise Unix servers, down to departmental level intel platforms. For a database of any significant size, performance immediately becomes an issue. Users may resent a query taking 3.5h, for instance. Or on the other hand, an update locks a table for 30 mins while it modifies data, which denies application access to the table(s) concerned. There will always be a platform ceiling set by hardware and o/s kernel performance but wouldn’t it be nice to use the gap between what we are getting today, and what we could get from our platform investment? Essentially there are two main avenues for RDBMS optimisation. Strategies deployed by the DBA, such as cache management and DBWR and LGWR optimisation. The other approach is to investigate how the SQL is running, to change either the SQL or its execution environment and see if that made a difference. Enlightened end users and developers can do much to get the best out of their system without necessarily involving the DBA in system change. If the SQL approach to optimisation is what you need to learn, then this is just the course for you. The approach taken is to understand what Oracle does with your SQL and why. Then to research the system to find out how things are at the moment. To change the SQL approach and finally, the inevitable question, “did it work?”. Expects to get lots of hands-on practice, to generate and see problems and finally to solve them. The training is strongly lab based and takes a real world view of everyday business computing problems. COURSE CONTENT:   1. Introduction to Tuning Causes of performance problems A tuning methodology Tools for tuning Timing techniques 2. Oracle memory Structures Overview Block buffer cache Shared pool SQL statement processing Minimizing parsing Bind variables 3. Tracking SQL EXPLAIN PLAN AUTOTRACE EXPLAIN Output Invoking the SQL trace facility Formatting trace files with TKPROF ANALYZE table/index Histograms 5. The optimiser Rule-Based Optimization Cost-Based Optimization Influencing the optimizer 6. Indexes Identifying row access methods Review of index types Index usage in execution plans Effects of expressions on index usage 7. Sorting and Joining Sorting Guidelines Nested Loops Joins Sort/Merge Joins Outer Joins Star Joins Hash Joins Effects of Wildcards Implicit Data Type Conversion NULL Values and Negations 8. Sorting, Aggregation, and Set Operators Group Functions order by select distinct group by, having union, minus, intersect, and union all 9. Joins Nested loops joins with and without indexes Sort-Merge Joins Outer Joins Hash Joins 10. Subqueries Regular subqueries Correlated subqueries Antijoins and semijoins MB07/01