Daniel Westermann's Blog

the situation: a customer faces issues with his oracle database. you cannot go on-site for some reasons and you cannot connect to the database in question. what options do you have to support them ?

• request a current awr/ash report and try find an issue from there
• request execution plans if the issue is with bad performing statements and try if you might see anything there without knowing the data
• try to support them by phone

there is another option some people might not know or are not aware of: dumping the awr data and importing it to your own database for further analysis. as this will include only awr data no user data will be submitted ( except for literals in sql statements, if there are any ) and you don’t need to worry about data privacy.

first of all you need to ask for an awr extract. there is a script which does all the work and you just need to execute it:

SQL> @?/rdbms/admin/awrextr.sql

the header displayed already tells you what the script will do and how to use it:

~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the	   ~
~  following information:					   ~
~     (1) database id						   ~
~     (2) snapshot range to extract				   ~
~     (3) name of directory object				   ~
~     (4) name of dump file					   ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

the parameters the script will ask for are the same as when creating awr reports except you’ll additionally need a directory to store the dump file ( DATA_PUMP_DIR, by default ).

that’s it. the script will start the dump process:

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /opt/oracle/product/base/11.2.0.3/rdbms/log/
|   demo.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Extract Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   /opt/oracle/product/base/11.2.0.3/rdbms/log/
|   rman.log

End of AWR Extract

… and once finished the dump file can be copied from the location provided:

SQL> !ls /opt/oracle/product/base/11.2.0.3/rdbms/log/
rman.dmp  rman.log  dp.log

so far for the dump part. once you received the dump file how to import it into your own database ? not a big deal:

SQL> @?/rdbms/admin/awrload.sql

again, the header will tell you pretty much everything you’ll need to know:

~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object			      ~
~     (2) name of dump file				      ~
~     (3) staging schema name to load AWR data into	      ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

a few things to take care of:

• when you get asked for the dumpfile pass the filename without the file extension ( in my case “rman” instead of “rman.dmp” )
• the load will temporarily create a staging schema ( AWR_STAGE ) and will drop it once the load finished

so, now you have the remote databases’ awr data available on your private database. what to do next ? maybe, in the first step you would like to create a set of awr reports to get an overview of the remote system. now, your are able to do this.

in a first step you’ll need the dbid and instance id for the remote database:

SQL> select DBID,INSTANCE_NUMBER,DB_NAME,INSTANCE_NAME,HOST_NAME 
               from DBA_HIST_DATABASE_INSTANCE;

	 DBID INSTANCE_NUMBER DB_NAME	INSTANCE_NAME	 HOST_NAME
------------- --------------- --------- ---------------- --------------------
   3551282359		    1 DEMO	demo		 ol6ora.local
   1664148667		    1 RMAN	rman		 ol6ora2.local

in may case “RMAN” is the remote database for which the awr statistics where imported to my local database “DEMO”. let’s see what snapshots are available for this dbid:

SQL> select SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME 
           from dba_hist_snapshot where DBID=1664148667;

SNAP_ID BEGIN_INTERVAL_TIME	  END_INTERVAL_TIME
------- ------------------------- -------------------------
   8096 16-FEB-13 04.00.52.846 AM 16-FEB-13 05.00.54.990 AM
   8097 16-FEB-13 05.00.54.990 AM 16-FEB-13 06.00.57.548 AM
   8098 16-FEB-13 06.00.57.548 AM 16-FEB-13 07.00.59.883 AM
   8099 16-FEB-13 07.00.59.883 AM 16-FEB-13 08.00.01.952 AM
   8100 16-FEB-13 08.00.01.952 AM 16-FEB-13 09.00.04.200 AM
   8101 16-FEB-13 09.00.04.200 AM 16-FEB-13 10.00.06.321 AM
   8102 16-FEB-13 10.00.06.321 AM 16-FEB-13 11.00.09.183 AM
   8103 16-FEB-13 11.00.09.183 AM 16-FEB-13 12.00.11.274 PM
...

that’s enough information to produce some reports:

SQL> define inst_num     = 1;
SQL> define inst_name    = 'rman';
SQL> define db_name      = 'RMAN';
SQL> define dbid         = 1664148667;
SQL> define begin_snap   = 8098;
SQL> define end_snap     = 8100;
SQL> define report_type  = 'html';
SQL> define report_name  = /tmp/first_awr_report.html
SQL> @?/rdbms/admin/awrrpti.sql

maybe the reports you generated are sufficient for giving the right hints. but there is even more you could do with all the statistic data available:

as an example, let’s say you want to display the load of the server for a period of two snapshots. for this there is statistic called “LOAD” which you can query:

SELECT to_char(t.begin_interval_time, 'DD.MM.YYYY-HH24:MI:SS' )
     , o.value
  FROM dba_hist_osstat o
     , dba_hist_snapshot t
 WHERE o.dbid = 1664148667
   AND o.dbid = t.dbid
   AND o.snap_id = t.snap_id
   AND o.snap_id BETWEEN 8098 AND 8110
   AND o.stat_name = 'LOAD'
 ORDER BY o.snap_id;

TO_CHAR(T.BEGIN_INT	 VALUE
------------------- ----------
16.02.2013-06:00:57 .829101563
16.02.2013-07:00:59 .629882813
16.02.2013-08:00:01 .309570313
16.02.2013-09:00:04   .1796875
16.02.2013-10:00:06 .329101563
16.02.2013-11:00:09 .279296875
16.02.2013-12:00:11 .119140625
16.02.2013-13:00:13 .649414063
16.02.2013-14:00:15 .919921875
16.02.2013-15:00:17 .459960938
16.02.2013-16:00:20 .889648438
16.02.2013-17:00:22 .629882813
16.02.2013-18:00:24 .549804688

wouldn’t it be nice to display theses values in a picture ? put these numbers to a file:

echo "16.02.2013-06:00:57 .829101563
16.02.2013-07:00:59 .629882813
16.02.2013-08:00:01 .309570313
16.02.2013-09:00:04   .1796875
16.02.2013-10:00:06 .329101563
16.02.2013-11:00:09 .279296875
16.02.2013-12:00:11 .119140625
16.02.2013-13:00:13 .649414063
16.02.2013-14:00:15 .919921875
16.02.2013-15:00:17 .459960938
16.02.2013-16:00:20 .889648438
16.02.2013-17:00:22 .629882813
16.02.2013-18:00:24 .549804688" > /tmp/graph1.lst

define a gnuplot definition:

echo "set terminal png 
set xlabel \"Time\"
set xdata time
set timefmt "%d.%m.%Y-%H:%M:%S" 
set title 'OS-Load'
set format y \"%10.1f\" 
plot \"/tmp/graph1.lst\" using 1:2 with lines title 'OS Load'" > /tmp/graph1.plot

.. create the png file:

gnuplot /tmp/graph1.plot > graph1.png

… and have a look at it:

display graph1.png

server load

for those who don’t know: dbms_xplan also works with dbid, so you may generate the execution plans for statements from the remote instance ( although the below is not a very useful example ):

SELECT * 
 FROM TABLE ( dbms_xplan.display_awr ( '3s1hh8cvfan6w'
                                     , '2137789089'
                                     , 1664148667     -- remote_dbid
                                     , 'ADVANCED' ) );
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id  | Operation			  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		  |	    |	    |	    |	 30 (100)|	    |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |	 30   (4)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1 / KOKBF$0@SEL$2

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('query_rewrite_enabled' 'false')
      ALL_ROWS
      FORCE_XML_QUERY_REWRITE
      XML_DML_RWT_STMT
      XMLINDEX_REWRITE
      XMLINDEX_REWRITE_IN_SELECT
      NO_COST_XML_QUERY_REWRITE
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$F5BB74E1" "KOKBF$0"@"SEL$2")
      END_OUTLINE_DATA
  */

combine all this into a set of scripts and you are ready to do some analysis even if you do not have access to the database. together with the power of gnuplot for creating nice graphs you are in a position for doing an initial troubleshooting.

two more sample images generated out of the awr data:

most people know that they require a license for using all that nice reports which can be generated out of the statistical data stored in the advanced workload repository ( AWR ). probably less people know that there still is an alternative which is free to use: statspack. although statspack is not that much automated as awr and therefore requires a bit more work to do it still can be used to generate reports which can point you to the right direction in case you face some performance issues.

so how do you work with it ?
as usual, the scripts are located in the rdbms/admin directory of your $ORACLE_HOME. statspack needs an initial setup to create all the objects required:

SQL>@?/rdbms/admin/spcreate.sql

the script will ask a few questions:

• the password for the perfstat user
• the tablespace to store the statistical data ( you should probably create a separate tablespace for this )
• the temporary tablespace to use

that’s it. statspack is installed and ready to use, almost. in awr snapshots are created automatically ( usually every hour if you didn’t adjust the interval ). with statspack the snapshots need to be created manually:

SQL> exec perfstat.statspack.snap;

there is even a little script which creates a job for you if you want to automate the snapshots ( hourly, by default ):

SQL>@?/rdbms/admin/spauto.sql

of course you might want to adjust the script if hourly snapshots do not fit in your case.

so, once you have at least two snapshots available you are ready to create a report:

SQL>@?/rdbms/admin/spreport.sql

similar to the awr report the script will ask for the begin and end snapshots as well as a name for the report and then will create the report for you. you’ll notice the similarities to awr immediately, e.g. the top 5 timed events:

Top 5 Timed Events						      Avg %Total
~~~~~~~~~~~~~~~~~~						     wait   Call
Event						 Waits	  Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
CPU time							24	    42.3
db file async I/O submit			   364		11     30   19.1
control file parallel write			   232		 8     36   15.0
db file sequential read 			   577		 4	6    6.6
log file parallel write 			    78		 3     41    5.7
	  -------------------------------------------------------------

ok, the output is not as nice the html reports of awr, but this is still a lot of information to work with, and it’s free.

there are some other scripts around which assist in maintaining the statspack repository:

• spdrop.sql: for droppping statspack
• sppurge.sql: for dropping a range a snapshots
• sprepins.sql: for reporting differences between snapshots
• sptrunc.sql: for truncating the statspack repository

… to name a few of them. there are some more which you might look at ( they all start with sp* ).