Oracle Database 10g: Advanced PL/SQL Programming & Tuning

Course details

The objective of this course is to allow experienced PL/SQL developers to write efficient programs. Advanced techniques which not only improve execution speed but also enhance the functionality of the programs are also discussed. Objectives: -Exploiting and managing cursor sharing. -Utilizing cursor variables. -Invoking external procedures. -Including dynamic SQL and other advanced programming techniques. -Efficiently executing dynamic SQL using bulk bind techniques. -Tuning with the DBMS_PROFILER() system-supplied package. -Debugging with the DBMS_TRACE() system-supplied package. -Performance tuning and analysis of PL/SQL applications.

• about em & pl/sql
• managing invalid objects

• about cursor sharing
• bind variables & cursor peeking
• using the cursor_sharing parameter

• about collections
• bulk bind using collections
• collection methods

• why retrieve object definitions?
• retrieving default metadata
• retrieving customized metadata

• set_transform_param()
• get_query()

• about table redefinition
• using the dbms_redefinition() package
• Using other system-supplied packages :
• dbms_describe()
• utl_mail
• dbms_scheduler()

• about external procedures
• registering with the database
• oracle net configuration
• calling external procedures

• about the tuning effort
• the difficulty of tuning
• the sql & pl/sql execution environment
• the impact of the systems infrastructure

• plsql_warnings
• plsql_optimize_level
• pl/sql native execution

• about dbms_profiler()
• installing dbms_profiler()
• using dbms_profiler() to manage runs
• examining the profiler tables

• about the profiler data
• user queries of the cross run tables
• system-supplied cross run tables queries

• autonomous transactions
• using nocopy for parameters
• choosing the optimum datatype
• useful pl/sql coding techniques

• about the trace facility
• installing dbms_trace()
• using dbms_trace() to manage runs
• examining the plsql_trace_... Tables