This is an article which i had prepared for one of my clients in using OEM for some quick tuning and as a starting point for configuring , using OEM & its tuning packs.
Unfortunately i couldn't post the pictures here ..would be posting it shortly.
Guide to Real Time Performance Tuning Of Oracle
Version 1.0
Jun 26, 2005
Guide to Real Time Performance Tuning Of Oracle. 1
CONTENTS. 2
INTRODUCTION. 3
OEM Tuning Pack. 3
OMS and OEM Repository. 3
Configuring the OEM console for using the Oracle Management Server (OMS) option. 4
Index Tuning Wizard. 10
Oracle Expert. 10
SQL Analyze. 10
Oracle Enterprise Edition provides a lot of features for Real Time Tuning of an Oracle Database which can be used efficiently to serve as an aid in the tuning process and can help the DBA in identifying the performance bottlenecks in any Oracle system which undergoes constant changes as a result of business changes and also in improving the response time of the application to the satisfaction of the end users.
Oracle Enterprise Edition provides a lot of GUIs in its Tuning Packs that can be launched from OEM console with the Oracle Management Server (OMS) login option.
OEM Tuning Packs include the Index Tuning Wizard, Oracle Expert and SQL Analyze as some of the important tools which can help the DBA in accelerate the tuning and also more importantly identify the workload pattern to get the accurate tuning recommendations for the Oracle Database under tuning. These tuning packs require the OEM console to be configured for using with an OEM repository under the OMS option.
The Oracle Management Server (OMS) uses an OEM Repository to store all the tuning session details and many other DB related task details in it. OMS uses this repository to store all the tuning statistics and Job details(if jobs are configured) apart from many other things in the OEM repository for all the target databases to which one connects from the OEM console using the OMS option.
OEM console needs to be configured properly for using the Oracle Management Server option. This can be done using the Enterprise Manager Configuration Assistant(EMCA) that can be launched in Oracle for Windows NT platform from the Configuration and Migration Tools menu.
The following steps would illustrate the use of EMCA to configure OEM console for using the OMS option:
STEP 1:
STEP 2:
If you are configuring the OMS to used an OEM repository for the first time, then select the first option. Selecting this option enables one to configure the local OMS on the server. This configuration would create an additional Windows NT service with the naming convention OracleManagementServer. This would then be the NT service responsible for OMS afterwards and needs to be in “Started” state at the end of this configuration process.
STEP 3:
This step would help in creating a repository for the OMS in the server. This OEM repository for the local OMS is just another Oracle Schema which can be configures either in one of the existing databases or in a new database exclusively for the OEM repository.
If you are creating a repository for the first time use the first option here.
STEP 4:
Depending on whether you need to create a new database exclusively for hosting your OEM repository or use one of your existing databases for storing the OEM repository choose either the “Typical” or “Custom” options here.
STEP 5:
If you have chosen the “Typical” option here then you would end up in the following screen which would help you in creating a new database with the default naming conventions, a new Schema (a Repository user along with the configuration data in tables and associated objects under the user).
This new database would be like any other database except for the fact that it would be hosting the OEM repository and everytime one would be connecting to this database whenever you are launching OEM console using the OMS option.
In case you want a “Custom” configuration of OEM repository then you would be presented with the following screen from where you can choose to create a new database( as above) or use an existing database for hosting the OEM repository as below:
One would be prompted to give the OEM repository name and password details later. In case you are choosing an existing database option then you would be prompted to furnish the details of an user having DBA rights in the existing database so that using this DBA login credentials the OEM repository schema can be created in the existing database.
The screens which follow from hereon are self explanatory and any user having a basic understanding of the Oracle DB should be able to finish the configuration process.
In case you run into any problems in configuring the OMS you can always restart the whole process at any time and even if you have ended up with a wrong configuration , one can always drop the repository and recreate a new one.
This wizard helps us in arriving at a proper and effective indexing strategy. This Index tuning wizard can go a long way in helping the DBA arrive at a right combination of the indexes for the application and helps in improving the response time of the SQLs which consume more system resources by identifying new possible indexes.
This wizard also gives justifications for the recommended indexes and also generates the script to implement the recommended indexing changes. These recommended changes and the script can then be reviewed by the DBA to arrive at a final Indexing recommendations. Thus the tool helps the DBA in Indexing strategy and thereby accelerating the tuning process
This is a very powerful tool which can automate the process of collecting statistics at database, instance, schema and workload levels and help in arriving at recommendations at a more generic level. With real time workload and actual CPU, Memory details of the live system, Oracle Expert recommendations would be extremely effective in performance tuning to the DBA. The manual way of collecting Statspack reports and then using them to arrive at tuning recommendations can be partially automated using this Oracle Expert and also help the DBA in speeding up the tuning process by arriving at recommendations not only at the SQL level but also at the Instance level.
SQL Analyze is a powerful SQL tuning tool which comes along with many other wizards like Virtual Indexing wizard, Hint Wizard etc.
SQL Analyze helps the DBA in automating the tuning of resource intensive SQLs by identifying the SQLs under different categories similar to the SQL reports sections in Statspack reports and helps the DBA in tuning them with many supporting utilities like Virtual Indexing wizard.
Virtual Indexing Wizard in particular is one of the most useful and effective tool which helps in identifying the performance improvement without actually creating those recommended indexes.
Conclusion
Oracle tuning of existing systems/already running systems needs to be done with extra care so as to minimize those changes which need more development/maintenance effort and also possibly those inevitable testing efforts. At the same time tuning should also meet the end users objectives in the shortest possible time. Oracle’s tuning tools described here come very handy for the DBA to meet the above requirements.
