This report is usually run when you need to tune a SQL or you want to investigate performance bottlenecks in your Oracle database.

The report can be run from Oracle Enterprise Manager Database Control or Grid Control (OEM). It can also be run from command line.

The steps below shows how to run the ASH report using OEM

1. Login to Oracle Enterprise Manager 11g Grid Control
2. Go to Targets and then from the menu below select All Targets.
3. Find the Database Instance where you want to run ASH report
4. Click on the database Instance Link. Now the Database instance page will be displayed.
5. Click on the tab called Performance
6. It should ask for username/password. If you want to can select to login as SYSDBA
7. When the performance Tab is displayed find the Run ASH Report link from top right corner on the screen and click on it.
8. Now Run Ash Report will be displayed
9. Here you select the date and time for the period you want to run the ASH report for. Select Date and Time.
10. There is no need to enter a value for SID but if you want you can enter if you want to run this for a particular SID.
11. Then click on the Generate Report button.
12. You will see a message saying that the report is now running. After a few seconds the report will be displayed.

The ASH report contains a wide range of information which is very useful for performance tuning. Basically based on the purpose of your running the ASH report you can directly go to relevant sections. For example if you are looking for tuning SQL performance then you can go directly to Top SQL section.

In Top SQL sections you can identify top five SQL statements which are

The first column shows the SQL IDs. Click on that link will display the actual statement that is being run.

The report also identified the following:
Top Events
Load Profile
Top SQL
Top PL/SQL
Top Java
Top Call Types
Top Sessions
Top Objects/Files/Latches
Activity Over Time
and much more

The …/Middleware/gc_inst/em/EMGC_OMS1/sysman/log/emoms.trc file show the error as

java.sql.SQLDataException: ORA-01882: timezone region not found
ORA-06512: at “SYSMAN.MGMT_GLOBAL”, line 8

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:79)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1035)

fter looking on the internet and Oracle metalink I could not find a satisfactory answer. I searched for
ORA-01882 and ORA-01882: timezone region not found. But this error code ORA-01882 looks like can appear in other
Oracle products too.

The metalink id 1148503.1 does exactly the same issue. But for me the solution did not work.

My OS time zone shows like this:


cat /etc/sysconfig/clock
ZONE="Etc/GMT"
UTC=true
ARC=false

And Oracle also understand this time zone as shown below:

$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Dec 7 17:07:16 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT CAST(FROM_TZ(CAST('12-JUL-2010 08:00:00' as TIMESTAMP), 'GMT') AT TIME ZONE 'Etc/GMT' AS DATE) from dual;

CAST(FROM
---------
12-JUL-10

SQL>

Resolution:

To solve the issue add “-Duser.timezone=Etc/GMT” (without the quotes) in JAVA_PROPERTIES of your file
…/Middleware/gc_inst/user_projects/domains/GCDomain/bin/setDomainEnv.sh

Then do:


cd OMS_HOME/bin
emctl stop oms
emctl start oms


Then try. It should now work.

But we must also be aware that it can also have significantly detrimental effect on your production database if you do not control usage.

If you maintain a database in a corporate environment it may be sometime necessary  to give access to your Oracle Enterprise Manager 11g to management stuff who may not be too tech savvy or at least with Oracle technologies. What they may be looking for is just to monitor activities or trends. But if they start running different advisors then it may impact a production database.

Today I was running Segment Advisor for just two tablespaces. One of them was 125G and another 32G. While running the segment advisor from one Oracle Enterprise Manager session I monitored the impact of this activity from another session through the database instance performance tab.

The impact looks significant to me. Note that this database is a non production database and was quite (meaning no serous activity happening at the time of running).

This is how looks in Top Activity screen

Looking at the I/O usage looks much scarier:

Whenever I run the segment advisor (twice in the above screenshot) the I/O MB per sec and I/O Requests per sec increases significantly.

Imagine running this type of activity in an OLTP production database. It will seriously impact the database performance as normal database operations will need to compete with non significant advisor related activity for CPU time as well as memory.

So it is advisable to run these advisors in quiet times whenever possible unless it is really urgent.

Install Oracle Enterprise Linux
Prepare Linux for Oracle 11gR2 installation.
Install Java SDK 6 update 18
Install Oracle 11gR2 database software
Create Oracle 11gR2 database
Create Oracle Net services for Oracle 11gR2 database
Install Oracle WebLogic Server 11gR1 (10.3.2)
Install Oracle Enterprise Manger 11g Grid Control

Hardware/Software:
DELL – Intel Core 2 Duo CPU
RAM: 2GB
Hard Disk: 147GB
OS: Oracle enterprise Linux 5.3.
Oracle validated rpms

Directory Structure:
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_SID=gdb

Pre-Requisites:

Before running through this tutorial make sure you have complete the following tasks:

1. Oracle Enterprise Linux 5.3 installed
2. Linux box prepared for Database installation. See here for details instructions
3. Java SDK 6 update 8 installed
4. Oracle Database 11g R2 software is installed.
5. Oracle Database gdb created.
6. Oracle Net configured

Installation instructions and options:

Step/Screen Name	Action
1	Login as oracleThen chage the PATH to include the java home directory. In my server PATH is PATH=$PATH:/usr/sbin:$ORACLE_HOME/bin:/usr/java/jdk1.6.0_18/bin
2	Download wls1032_generic.jarto /u01/app/oracle/
3	Open X-Windows session to the server as oracle user
4	#cd /u01/app/oracle
5	#java -Xmx1024m -jar wls1032_generic.jar
The Oracle Weblogic installation will start. Follow the steps below to install Weblogic
Welcome	Click Next
Choose middleware Home Directory	Set Middleware home directory to /u01/app/oracle/Middleware Click Next
Register for Security updates	Uncheck “I wish to receive..” Click Next
Register for Security updates	When you get this screen Click Yes
Register for Security updates	When you get this screen Click Yes
Choose Install Type	Select Typical and click Next
JDK Selection	Click Next
Choose Product Installation Directories	Make sure the directroy paths looks like above. Click Next
Installation Summary	Click Next
Installation Progress Screen
Installation Complete	Uncheck “Run Quickstart” Click Done
Oracle Weblogic server is now installed.

Install Oracle Enterprise Linux
Prepare Linux for Oracle 11gR2 installation.
Install Java SDK 6 update 18
Install Oracle 11gR2 database software
Create Oracle 11gR2 database
Configure Oracle Net for Oracle 11gR2 database
Install Oracle WebLogic Server 11gR1 (10.3.2)
Install Oracle Enterprise Manger 11g Grid Control

Hardware/Software:
DELL – Intel Core 2 Duo CPU
RAM: 2GB
Hard Disk: 147GB
OS: Oracle enterprise Linux 5.3.
Oracle validated rpms

Directory Structure:
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_SID=gdb

Pre-Requisites:

Before running through this tutorial make sure you have complete the following tasks:

1. Oracle Enterprise Linux 5.3 installed
2. Linux box prepared for Database installation. See here for details instructions
3. Java SDK 6 update 8 installed
4. Oracle Database 11g R2 software is installed.
5. Oracle Database gdb created.

Installation instructions and options:

Step/Screen Name	Action
1	Open X-Windows session to the server as oracle user
2	#cd /u01/app/oracle
3	#netca
The Network Configuration Assistant will now start. You will use the netca to create a Oracle Net Listener. For this excercise we will create a listener with default name i.e. LISTENER
Welcome	Click Next
Listener Configuration, Listener	Select “Add” and click Next
Listener Configuration, Listener Name	Click Next (or if you wish you can change the listener name)
Listener Configuration, Select Protocols	Select TCP and Click Next
Listener Configuration, TCP/IP Protocol	Click Next
Listener Configuration, Mosre Listeners ?	Select “No” and Click Next
Listener Configuration, Done	Click Next
Welcome	Click Finish
Your Oracle Net is now configured. The Listener name is LISTENER and it is listening on 1521.