AUDIENCE:   This course is ideal for: Database administrators, Developers, Data warehouse support team members, Application second and third line support. PREREQUISITES:   Attendees must be knowledgeable in Oracle PL/SQL. DURATION:   2 days. Hands 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 minutes 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? Often application support identify a section of the product causing performance problems but lack further details; which PL/SQL program is the cause?, who is the executing user?, what is the SQL that is running? The course investigates methods of sleuthing slow running PL/SQL programs. Essentially there are two main avenues for RDBMS optimisation. Strategies deployed by the DBA, such as cache management and DBWR and LGWR optimisation, is one of them. The other approach is to investigate how the PLSQL and SQL is running, and to optimally change it or its execution environment. While the core subject of this course is the optimisation of the PLSQL component of RDBMS programs, we do touch on areas of RDBMS optimisation that impact PLSQL performance. Expect 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:   Overview of Oracle 10G Performance Tuning Job Roles in Tuning Tuning phases Tuning goals and Service Level Agreements Common performance problems Tuning Methodology The Shared Pool Overview of the shared pool Library cache tuning Reuse statements Using Reserved Space Pinning Objects in the shared pool Data Dictionary Cache (DDC) Tuning Searching the shared pool Parameters:- CURSOR_SHARING, OPEN_CURSORS, CURSOR_SPACE_FOR_TIME, session_cached_cursors Causes of PL/SQL Performance Problems Badly Written SQL Statements in a PL/SQL Program Poor Programming Practices Duplication of Built-in Functions Inefficient Conditional Control Statements Implicit Datatype Conversions Inappropriate Declarations for Numeric Datatypes Unnecessary NOT NULL Constraints Pinned Packages Serially Reusable Packages Identifying PL/SQL Performance Problems DBMS_TRACE DBMS_PROFILER PL/SQL Features for Performance Tuning Tuning PL/SQL Performance with Native Dynamic SQL Tuning PL/SQL Performance with Bulk Binds Tuning PL/SQL Performance with the NOCOPY Compiler Hint Tuning PL/SQL Performance with the RETURNING Clause Tuning PL/SQL Performance with External Routines Improving PL/SQL Performance with Object Types and Collections Compiling PL/SQL Code for Native Execution Inserting PL/SQL record(s) into a table Querying data into collections of records Associative arrays (index by tables) Multiple inserts Table functions (pipelined and parallel) Using ref cursors Diagnosing contention Explicit and implicit data locking Detecting locks as a cause of poor performance Commit point planning in bulk updates DISTRIBUTED_LOCK_TIMEOUT MB07/01