| After completing this lesson, you should be able to | ||
| do the following: | ||
| Use a tuning methodology for diagnosing and resolving performance issues | ||
| Use Oracle tools for diagnosing performance problems | ||
| Tune memory structures, file I/O, and contention | ||
| Group-oriented and interactive | ||
| Intensive hands-on diagnosis and problem resolution | ||
| Instructor-led discussions on findings and actions | ||
| Proactive participant involvement | ||
| Use Oracle tools to tune the following areas: | ||
| Memory | ||
| I/O | ||
| Resource contention | ||
Workshop Database Configuration
| One schema is created under scott/tiger. | ||
| There are six end users (user01-05, scott). | ||
| End users have access to scott’s objects. | ||
| Four tablespaces: system, rbs, user_data, temp | ||
| The DBA account is system/manager. | ||
| The sys account is sys/change_on_install. | ||
| Ask the instructor questions regarding performance tuning issues as they apply to the simulated database environment. | ||
| Formulate questions that will enable you to familiarize yourself with external factors that may affect performance and will aid you in establishing a tuning methodology. | ||
| Review statistics regarding specific areas: | ||
| Shared pool diagnostics, errors, and sizing | ||
| Rollback segments placement, sizing, and numbering | ||
| Buffer cache diagnostics and sizing | ||
| Redo log buffer contention | ||
| Files organization, sizing, and I/O distribution | ||
| Segment differentiation | ||
| Storage management issues, diagnostics, and resolution | ||
| Row migration and chaining diagnostics and resolution | ||
| Sort operation diagnostics and configuration | ||
| Lock, latch, free list, and rollback segments contention and configuration | ||
| Physical investigation: | |||
| Memory structures: | |||
| Buffer cache hit ratio low | |||
| Library cache hit ratio low | |||
| V$SYSSTAT | |||
| Contention: | |||
| Rollback segment contention | |||
| Latch contention—>V$LATCH | |||
| Application analysis: | ||
| Online transaction processing separated from batch | ||
| Explain plan indicates no use of indexes | ||
| High number of disk sorts | ||
| Trace files | ||
| Restart the instance with the new initbad.ora parameters. | ||
| Run the refresh.sql script to simulate an instance that has been running for some time. | ||
| Rerun the utlbstat script. | ||
| Run the growdb shell script. | ||
| Rerun the utlestat script. | ||
| Shut down the instance. | ||
| Review the new statistics. | ||
| Library cache hit ratio increased from 53% to 81%. | ||
| Database buffer cache hit ratio increased from 67% to 92%. | ||
| Sorts (disk) decreased, sorts (memory) increased. | ||
| Undo header waits decreased. | ||
| Distribution of hot files evened out. | ||
| Monitor paging and swapping using OS utilities because of larger SGA. | ||
| Consider increasing the size of database blocks. | ||
| Export/import to reduce fragmentation. | ||
| Continue monitoring using utlbstat/utlestat to measure results against the baseline. | ||
| Separate index segments from data segments. | ||
Pending Performance Tuning Issues
| In this lesson, you should have learned how to: | |||
| Follow a tuning methodology: | |||
| 1. Collect and review statistics. | |||
| 2. List the objectives for enhanced performance | |||
| before modifications. | |||
| 3. Modify the instance and the database. | |||
| 4. Recollect and review new statistics. | |||
| 5. Compare the new results with the objectives. | |||
| Implement Oracle architectural options for enhancing performance. | |||