Notes

Outline

Tuning Workshop

Objectives 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

Workshop Methodology Group-oriented and interactive Intensive hands-on diagnosis and problem resolution Instructor-led discussions on findings and actions Proactive participant involvement

Tuning Scope Use Oracle tools to tune the following areas: Memory I/O Resource contention

Slide 5

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.

Information Gathering 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.

Generate Statistics

Review Statistics 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

Review Statistics 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

Example 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 Application analysis: Online transaction processing separated from batch Explain plan indicates no use of indexes High number of disk sorts Trace files

Regenerate Statistics 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.

Results

Example 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.

Additional Concerns 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

Summary 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.