|
Provided by: 123-CBT Computer Based Training Oracle8 Performance Tuning Managing Memory and Disk I O |
 |
Training
Provided by 123-CBT Computer Based Training
Course Overview
This course is the second in a three-part Oracle8 Performance Tuning series. This course introduces the learner to the application tuning issues that will impact database performance. In addition, participants will be introduced to tuning components and functions of the shared pool and the buffer cache. Students will learn how to monitor the contents and usage of the Redo Log buffer. Finally, participants will identify database configuration and I/O issues, including tablespace usage and guidelines.
Learn To:
- Identify application tuning issues that will impact database performance.
- Identify the contents and functions of the shared pool.
- Identify how the Buffer Cache is managed and tuned.
- Learn how to tune the Redo Log Buffer.
- Learn about database configuration and I/O issues.
Content Emphasis
Skills-Based
Audience
Database administrators, system administrators, applications designers and developers, and technical support professionals. Prior to taking this course, participants should be familiar with administering an Oracle8 database, or have taken the Oracle8 Database Administration series (courses 61311-61315). In addition, they should understand key strategies and techniques involved in tuning an Oracle8 database, or have taken the first part in this Oracle8 Performance Tuning series (course 61331).
Total Learning Time
6 - 8 Hour(s)
Course Contents
Unit 1: Tuning SQL
Duration: 1.5 - 2 Hour(s)- Identify the characteristics of a star query.
- Sequence the steps of a hash join process when a hash join is built.
- Use the EXPLAIN PLAN statement to explain the plan for a query.
- Perform the procedure to set trace up at the session level to look at the performance of a plan.
- Perform the steps to use TKPROF to report the trace statistics in readable form and interpret the output file.
- Perform the procedure to generate an execution plan at the system level for a SQL statement using AUTOTRACE.
- Identify the symptoms of inefficient SQL statements.
- Identify the characteristics of optimizer modes.
- Perform the steps to set the optimizer mode at the instance level using the OPTIMIZER_MODE parameter.
- Perform the steps to register a module using the SET_MODULE procedure.
- Perform the steps to track a module using the READ_MODULE procedure.
- Perform the steps to view statistics for the current session using the V$MYSTAT view.
Unit 2: Tuning the Shared Pool
Duration: 1.5 - 2 Hour(s)- Identify the functions of the Library Cache.
- Identify the function of the Data Dictionary Cache.
- Identify the function of the User Global Area.
- Match the diagnostic views of the Library Cache with their respective definitions.
- Perform the steps to determine the percentage of parse calls that find a cursor to share using GETHITRATIO column of the V$LIBRARYCACHE view
- Perform the steps to determine the reloads-to-pins ratio from the V$LIBRARYCACHE view.
- Identify the steps used to size the Library Cache.
- Perform the steps to compute the amount of sharable memory that has been used using SELECT SUM query.
- Perform the steps to keep large objects using the supplied DBMS_SHARED_POOL package and the KEEP procedure.
- Perform the steps to view the reserved pool space within the shared pool using the V$SHARED_POOL_RESERVED view.
- Identify the guidelines for tuning the shared pool reserved space.
- Perform the procedure to size the user global area and view results in the V$MYSTAT and V$STATNAME views.
- Perform the steps to query the amount of cache misses in the Data Dictionary Cache using the GETMISSES command column of the V$ROWCACHE view
- Perform the steps to determine the hit ratio for the Data Dictionary Cache using the appropriate SQL statements.
Unit 3: Tuning the Database Buffer Cache
Duration: 1 - 2 Hour(s)- Identify the characteristics of the Buffer Cache.
- Sequence the steps used to manage the Buffer Cache.
- Select the tuning technique used to monitor the Buffer Cache.
- Perform the steps to view the statistics used to calculate the cache hit ratio using the V$SYSSTAT view.
- Perform the steps to calculate the hit ratio for the Buffer Cache with the hit ratio formula.
- Identify the steps for collecting statistics to estimate how many buffers to add to your cache.
- Perform the steps to calculate the impact of adding some given number of buffers by querying the V$RECENT BUCKET view.
- Identify the steps for collecting statistics to estimate how many buffers to remove from the cache.
- Perform the procedure for predicting buffer cache performance based on a smaller cache size.
- Identify the data access methods that can cause extremes of hit ratios.
- Select the appropriate guidelines in evaluating the cache hit ratio.
- Match the buffer pool names with their functions.
- Identify the initialization parameters used when creating multiple buffer pools.
- Perform the steps to define the number of buffers for an instance using the DB_BLOCK_BUFFERS initialization parameter.
- Select the proper clause to obtain the size of each object.
- Sequence the steps to determine the number of blocks required for objects in the RECYCLE pool.
- Perform the steps to determine if there have been waits for buffers using the V$SESSION_WAIT view.
- Perform the steps to enable caching during full table scans using the CACHE clause.
- Perform the steps for calculating the hit ratio for multiple pools.
Unit 4: Tuning the Redo Log Buffer
Duration: 1 Hour(s)- Identify the contents of the Redo Log Buffer.
- Match the correct function to the server process in the Redo Log Buffer.
- Perform the steps to size the Redo Log Buffer using the LOG_BUFFER parameter.
- Perform the steps to tune the Redo Log Buffer by displaying the number of all redo log space requests and redo entries using $VSYSSTAT.
- Perform the procedure to find the hit ratio of log space requests to redo entries.
- Perform the steps to specify the NOLOGGING option, as an attribute used to reduce redo operations.
- Identify the purpose of a latch.
- Match the types of latches with their definitions.
Unit 5: Configuring and I/O Issues
Duration: 1 Hour(s)- Identify the recommended tablespaces and divisions of data for an Oracle database.
- Identify the advantages of partitioned tables.
- Select the two items needed to choose the correct stripe size.
- Perform the steps of a query to determine how many full table scans are taking place.
- Identify the reasons to set the DB_FILE_MULTIBLOCK_READ_COUNT parameter when tuning full table scans.
- Perform the steps of a query to find out the number of disk I/O per disk file using the V$FILESTAT query.
- Perform the steps to determine how well the I/O load is distributed across the disk devices by using the REPORT.txt file.
- Configure the Online Redo Log File and the Archive Log File.
- Identify the functions of checkpoints.
- Perform the steps to tune checkpoints by recording the beginning and end of checkpoints using the LOG_CHECKPOINTS_TO_ALERT parameter.
- Identify the functions of I/O slaves.
- Perform the steps to deploy multiple DBWR processes using the DB_WRITER_PROCESSES parameter.
|
|
||||||||||
Oracle8 Performance Tuning Managing Memory and Disk I O
--
About The Training Provider: 123-CBT Computer Based Training
123-CBT Computer Based Training - 123-CBT offers discount pricing on top quality eLearning
programs from leading computer based training providers. Many of the training courses are available both online or on CD so that you can study at home at your own pace:
E-Learning available for
- ABAP 6.10
- Access 2003
- Acrobat 6.0
- ASP
- ASP.NET
- C
- Captivate 1.0
- Crystal Reports 8.5
- Crystal Reports v10
- Dreamweaver MX
-...
