Skip to content

Personal tools
You are here: Home » Blogs » Chris Foot Blog » Chris Foot's Oracle10g Blog » Oracle 10G OEM Grid Control Tips, Tricks and Techniques
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3605

Oracle 10G OEM Grid Control Tips, Tricks and Techniques Oracle 10G OEM Grid Control Tips, Tricks and Techniques

After spending 20 hours testing Enterprise Manager, I'm happy to report that everything works as advertised. The tool is rock-solid. I thought for sure the product would have at least a few problems that I could report on. So instead of a bug discussion, this blog contains some of the tips and tricks I learned during the execution of my Enterprise Manager test plan.

10G OEM is really 10G Grid Control
Jim Dojonovic, who just returned from 10G training, tells me that I have been using the wrong name for 10G's enterprise management tool.   He stated that the correct name is Grid Control or Enterprise Manager Grid Control.  So to keep in synch with the Oracle Corporation, I'll use Grid Control instead of OEM when I talk about Oracle's enterprise management tool.

Tips and Tricks
There is no mechanism to let you temporarily navigate to another area of Grid Control and then return to your current unit of work.  I had to open up a new browser session to look for the desired information and then switch to the original Grid Control session to continue.  Once you become used to it, it seems fairly straightforward.

You will need to establish alert notification rules in Grid Control.  The rules establish the criteria that Grid Control will use to determine if it should generate a notification when a particular type of problem occurs.   For example, you may want to be notified when a database becomes unavailable and not be notified when the number of writes to LOB segments exceeds the default threshold.   To establish the notification rules, log on to the tool's home page and navigate to:

 Preferences ---> Rules

Grid Control allows you to clone a set of predefined notification rules to simplify the rule creation process.   During our testing, we cloned the following predefined rules:

    • Agent Upload Problems - Notifications for agents having problems uploading data to the management server
    • Agents Unreachable - Notifications for agents that lose their connection to the management server
    • Database Availability and Critical States - Database outages and metric alert notifications (out-of-space conditions, performance thresholds, ORA-0600s in alert log, etc.).
    • Host Availability and Critical States - Host server outages and performance metrics (CPU, memory, etc.) notifications
    • Listener Availability - Listener outage notifications

Be very selective when activating the metrics that will generate alerts.  We are still in the process of tailoring the alerts to our environment.  Currently, I have 75 Grid Control alert notifications in my E-mail's in-box.  This is a large number considering we only have 5 databases currently being monitored.   We intend to spend dedicated time reducing these alerts to a smaller and more manageable number.

Each target (host, database, listener, application server) has its own set of metrics that can be customized to address each application's unique monitoring requirements.   In addition, Enterprise Manager allows you to copy the current target's metrics to other targets to facilitate the metric creation process.   Until we become more familiar with the tool, we are setting the metrics for a master target and then copying that target's metrics to all targets of the same type.   Once we become more accustomed to using Enterprise Manager, we'll consider customizing each individual target's metrics to better address its unique monitoring needs. 

I thought the administration panel that allows you to change database initialization parameters was very well thought out.  It allows users to search for parameters based on their category grouping (i.e. performance, logging, archiving, etc).  In addition, Grid Control allows you to order initialization parameters on name, value, type and category.   The groupings allowed me to view all of the parameters related to the topic I was interested in.  For example, the archive grouping allowed me to see all of the parameters related to log archiving which made it easy for me to configure multiple archiving destinations.

If you want to change an initialization parameter for both the current running instance and in the SPFILE, navigate to the SPFILE administration panel and check the "Apply changes in SPFile mode to the current running instance(s)." radio button.  If you use the SHOW SQL button, you will notice SCOPE=BOTH at the end of the ALTER SYSTEM statement.  SCOPE=BOTH tells Oracle to make the parameter change to the current running instance and in the SPFILE.

If you are looking for session information, go to the target database home page and click on number next to the word "Active Sessions".  The link will be displayed under  the pie chart titled "Active Sessions".  Clicking on the link activates the Top Sessions screen.  If you would like to display all of the sessions, click on the CUSTOMIZE button.  The customization form contains a radio button that allows you to display information for all sessions. To search for a specific session, use the following links:

 Performance ---> Additional monitoring links ---> Search Sessions

To find locking information (including database lockout conditions), go to the target database and follow these links:

Performance ---> Additional monitoring links ---> Database Locks

Choose BLOCKING SESSIONs to identify sessions having locking problems.

You want to make sure your browser has the latest SVT plug in.  If you don't, you won't be able to activate the drill downs on the target database's performance page.  If you see a message that states "Best Viewed Using Lastest SVT Plugin" displayed on a database's  performance page, you don't have the most up-to-date version.  That means you will not be able to use Grid Control's drill down capabilities.  Download it immediately to take advantage of this very helpful functionality.

The new database-wide DEFAULT tablespace works.  If you don't specify the DEFAULT tablespace during user creation, it will default to the database's DEFAULT tablespace.

The CREATE TABLE and CREATE INDEX screens provide buttons that help you to estimate the size of the object being created.  The form prompts you for the number of rows and returns an estimate on the amount of space it thinks the object will consume.   I tested this several times and the estimated size was always within a few MEGs of the actual size of the segment.  Not bad considering some of our tables are hundreds (and hundreds) of GIGs in size.

When adding a foreign key to a table, the parent table form doesn't designate which columns are in the primary or unique key.  You must have those columns previously identified.  If you don't know what they are and don't want to lose your current unit of work, open another Grid Control browser session to identify the primary key or unique key columns.  Note to Oracle: do us a favor and tell us what the columns are in the primary or unique key constraints on the parent table form.
During tablespace creation, Enterprise Manager automatically sets the out-of-space warning and error thresholds for you.  The tool does allow you to change them if you prefer to create the tablespace with non-default threshold values.   This ensures that all tablespaces are monitored immediately upon creation.

To simplify administration, Enterprise Manager often combines a series of commands into command sets.  This allows administrators to make changes that used to require multiple executions in one step.  For example, the temporary tablespace form allows administrators to create a new temporary tablespace and make it the database's default temporary tablespace in one execution (which used to require multiple command executions).

When you add a column to a table, Grid Control will ask you if you want to add 5 columns.  If you only fill out one column specification, rest assured you will only add one column.

The table administration form provides a radio button that allows administrators to remove columns from a table.  After you select DELETE COLUMN from the drop down, the screen will refresh and the column will no longer appear on the form.  That doesn't mean the column was deleted.  You must click on the GO button to execute the ALTER statement that removes the column from the table.

The table, index and tablespace reorganization wizards check the target tablespace's freespace to determine if there is sufficient freespace available to hold the reorganized object(s).

If you would like to move a table or index to a different tablespace, select REORGANIZE from the drop down list in the object's administration panel to activate the reorganization wizard.   The second panel of the reorganization wizard will display a button titled "SET ATTRIBUTES" which allows you to specify a new tablespace for the object being reorganized.

Enterprise Manager automatically analyzes all indexes when they are created.

I liked being able to view the alert log from Enterprise Manager. 

EM, unlike previous versions, allows you to select a parallel worker process and navigate back to the SQL statement that is generating the parallel workload.

XML File Processing Bug
If your OMS server is having problems processing the XML files the agents are uploading, you may be hitting bug #4018344.   There is a job that contacts Metalink that gets hung up.   It also locks the tables used by the XML file loader.  Navigate to the jobs panel and search for JOBTYPE=RefreshFromMetalink, OWNER=all, STATUS=all.   Suspend the REFRESH_FROM_METALINK_JOB that shows up and then recycle OMS using the EMCTL utility.   For more information, go to Metalink and search for the number 4018344.

Final Recommendation
If you want to learn how to use 10G Grid Control, execute my test plan.  I now feel much more comfortable using the tool to perform day-to-day administrative functions.   I'll be honest, I like this version MUCH BETTER than the previous one.  I also feel that I can now assist my fellow DBAs when they have problems using the tool.

Although we have finished the bulk of our Grid Control testing, we are still not complete.  Our entire unit must understand how to use Grid Control to tune the Oracle environment.   My team is very comfortable using the previous version of the tool to tune both the database and individual SQL statements.  

But, it is imperative that we come up to speed as quickly as we can on this new version.  With tables having hundreds of millions and billions of rows, being able to quickly identify and correct performance problems is a VERY high priority here at Giant Eagle. 

When I first started learning Grid Control's new tuning toolsets, I was somewhat intimidated by their radically different look and feel.    Learning the new interface takes time and patience.  After testing day-to-day functionality, I spent 15 hours performing mock tuning drills using Enterprise Manager.  I once again feel that Enterprise Manager's tools are far superior to any tool (third party or Oracle provided) that I have ever used.   We'll be spending dedicated time in the future learning Grid Control's Automatic Database Diagnostic Monitor and becoming more comfortable with the other database performance monitoring tools that are available to us.

I am not as concerned about SQL tuning because we still have the same tools available to us as we always have.  We'll continue to use Explain, SQLPLUS AUTOTRACE, Statspack and SQLTRACE until we come up to speed on 10G's SQL Tuning and SQL Access advisors.  

Next Up
An interview with Bob George on Oracle10G's impact on data warehouse application performance.

Thursday, February 17, 2005  |  Permalink |  Comments (0)
trackback URL:

Powered by Plone