Oracle 9i 数据库性能调优技术-20

• •
Sort area size Assorted
20-9
Copyright © Oracle Corporation, 2002. All rights reserved.
Collecting Information
To perform a physical investigation of your workshop database environment, collect statistics by using: • The v$ dynamic performance views • • • Data dictionary views Table statistics Statspack
Βιβλιοθήκη Baidu
Summary
In this lesson, you should have learned how to: • Follow the Oracle tuning methodology:
– Collect and review statistics. – List the objectives for enhanced performance before modifications. – Modify the instance and the database. – Re-collect and review new statistics. – Compare the new results with the objectives.
20-6
Copyright © Oracle Corporation, 2002. All rights reserved.
Workshop Procedure
1. 2. 3. 4. 5. 6. 7.
Choose a scenario. Create a Statspack report. Run the workload generator. Create a Statspack report. Determine what changes should take place. Implement the changes. Return to the second step to check that the changes have made an improvement. 8. When the changes have improved performance, choose another scenario.
•
Implement Oracle architectural options for enhancing performance.
20-13
Copyright © Oracle Corporation, 2002. All rights reserved.
– Four users (current) – Twenty Users (future)
20-11
Copyright © Oracle Corporation, 2002. All rights reserved.
Results
• •
Present your conclusions and findings. Demonstrate the effectiveness of the tuning strategy and what effect the changes to the instance and database parameters had on overall performance.
20-10
Copyright © Oracle Corporation, 2002. All rights reserved.
Generating a Workshop Load
To run the workshop load: • Open the WORKSHOP group • Select the desired workload:
Physical Workshop Configuration
Each client account is set up in the following manner: • All workshop files are located in E:\Labs\Wkshop • All the data files are located at E:\ORANT\ORA92\ORADATA\ORCL The E:\ORANT\ORA92\ADMIN\ORCL\BDUM directory is used for instance-specific trace files.
20-3
Copyright © Oracle Corporation, 2002. All rights reserved.
Company Information
•
Small startup company
– Shares a Sun server with 10 other companies – Presently has four employees that use the database
20-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Approach to Workshop
• • •
Group-oriented and interactive Intensive hands-on diagnosis and problem resolution Proactive participant involvement
20-7
Copyright © Oracle Corporation, 2002. All rights reserved.
Choosing a Scenario
• • • •
Open the WORKSHOP group. Select the icon for the chosen scenario. Script then makes the relevant changes. The database is left up and running.
– – – – What was done and why? What were the results? Are there still any issues pending? What would you do differently?
20-12
Copyright © Oracle Corporation, 2002. All rights reserved.
Workshop Overview
Copyright © Oracle Corporation, 2002. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following: • Use the Oracle tuning methodology to diagnose and resolve performance problems • Use Oracle tools to diagnose performance problems • Understand the goals of the workshop
20-8
Copyright © Oracle Corporation, 2002. All rights reserved.
Workshop Scenarios
Choose from the following scenarios: • Shared pool • Buffer cache • Redo log buffer • Indexes • Rollback segments / undo tablespace
•
20-5
Copyright © Oracle Corporation, 2002. All rights reserved.
Workshop Database Configuration
• • • • • •
The database consists of a sample schema. Users log in as oltp or dss depending on the nature of their work. End users have access to sample schema objects. There are seven tablespaces: system, undotbs, temp, users, indx, sample, tools. The DBA account is system/oracle. The sys account is sys/oracle.
• • •
Looking to expand shortly to 20 database users System was set up by a part-time trainee DBA System performance is very slow
20-4
Copyright © Oracle Corporation, 2002. All rights reserved.