Running an AWR Compare Report Across Databases

Extract the AWR Data from Production Database

There are time when I want to run an AWR compare report from my production activity and my test database load test activity. We can do this by extracting the AWR data from the production database and loading it into the test database and then run a compare report between the 2 sets of snap data.

Oracle provides a utility to extract the AWR data from a database. In $ORACLE_HOME/rdbms the script awrextr.sql is used for this purpose for an interactive extract.

ORACLE_HOME/rdbms/admin/awrextr.sql

** Note: This will extract the AWR data for the snap periods that are selected. This processes uses the data pump utility to extract the AWR data to a data pump dump file. Therefore you must have a defined directory to write the dump file.


Move the AWR Datapump Dump File.

Move the Data Pump dump file created by the awrextr.sql script to the test server so it can be loaded.


Load the AWR Data into the Test Database

Oracle makes the import of AWR date as simple as the extraction of AWR data.

Oracle provides a script awrload.sql in the ORACLE_HOME/rdbms/admin directory.

** Note: Before attempting to load the AWR data from the data pump dump file ensure that an Oracle directory exists for the location of the data pump dump file the needs loaded. Also When loading AWR data into another database make sure the tablespace SYSAUX, the stage tablespace selected, example will use SYSAUX, and the TEMP tablespace have enough room to complete the operation. May want to have another session open to the database to monitor freespace.

ORACLE_HOME/rdbms/admin/awrload.sql


Run the Compare Report Between the 2 databases AWR Data

Now that we have AWR data from multiple databases we can compare across snap time periods from one database, run normal AWR reports over snap time periods, or even compare snap time periods across the different databases. Oracle provides an easy way to execute the compares across the script awrddrpi.sql in the ORACLE_HOME/rdbms/admin directory.

ORACLE_HOME/rdbms/admin/awrddrpi.sql


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified: text



Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
3742289852 1 AWR2 awr2 ORADBPROD01
* 744414708 1 ORCL11G orcl11g MRMESSIN

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for dbid: 3742289852
Using 3742289852 for Database Id for the first pair of snapshots
Enter value for inst_num: 1
Using 1 for Instance Number for the first pair of snapshots


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.


Enter value for num_days: 3

Listing the last 3 days of Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
awr2 AWR2 1 20 Feb 2008 17:42 1
2 20 Feb 2008 19:00 1
3 20 Feb 2008 20:00 1
4 20 Feb 2008 21:00 1
5 20 Feb 2008 22:00 1
6 20 Feb 2008 23:00 1
7 21 Feb 2008 00:00 1
8 21 Feb 2008 01:00 1
9 21 Feb 2008 02:00 1
10 21 Feb 2008 03:00 1
………


Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
First Begin Snapshot Id specified: 1

Enter value for end_snap: 9
First End Snapshot Id specified: 9


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
3742289852 1 AWR2 awr2 ORADBPROD01
* 744414708 1 ORCL11G orcl11g MRMESSIN


Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for dbid2: 744414708
Using 744414708 for Database Id for the second pair of snapshots
Enter value for inst_num2: 1
Using 1 for Instance Number for the second pair of snapshots


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.


Enter value for num_days2: 3

Listing the last 3 days of Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl11g ORCL11G 355 26 Feb 2008 07:34 1
356 26 Feb 2008 09:00 1
357 26 Feb 2008 10:00 1
358 26 Feb 2008 11:00 1
……………
405 02 Mar 2008 00:00 1
406 02 Mar 2008 01:00 1
407 02 Mar 2008 02:00 1
408 02 Mar 2008 03:00 1
409 02 Mar 2008 04:00 1
410 02 Mar 2008 05:00 1
411 02 Mar 2008 06:00 1
412 02 Mar 2008 07:00 1
413 02 Mar 2008 08:00 1
………


Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 405
Second Begin Snapshot Id specified: 405

Enter value for end_snap2: 413
Second End Snapshot Id specified: 413


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_1_1_405.txt To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: awrdiff_awr2_orcl11g.txt

Collaborate 2011

Are you getting ready for collaborate?

I am speaking again this year, hope to see you there.

Oracle 11gR2 Installing CTX Knowledge Base

Summary
The Oracle Text option requires the use of certain context knowledge bases that are not part of the typical installation of the Oracle 11gR2 database software. To be able to create context indexes these knowledge bases may be required.

Example:
DRG-11446: supplied knowledge base file /u01/app/oracle/product/11.2.0/dbhome_1/ctx/data/enlx/droldUS.dat not installed


1. Download the Oracle Examples Installation (formally Companion CD)

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html

2. Unzip the Oracle Examples Installation

$ unzip linux.x64_11gR2_examples.zip

3. Goto the location in the installation media for the CTX Filegroups

$ cd examples/stage/Components/oracle.ctx.companion/11.2.0.1.0/1/DataFiles

4. unzip the CTX Knowledge base files

$ unzip filegroup1.jar
Archive: filegroup1.jar
inflating: ctx/data/enlx/drofdUS.dat
inflating: ctx/data/enlx/drofiUS.dat
inflating: ctx/data/enlx/droldUS.dat
inflating: ctx/data/enlx/droliUS.dat
inflating: ctx/data/enlx/drolkUS.dat
inflating: ctx/data/enlx/drolsUS.dat

5. Goto the location of the knowledge base files we just extracted and copy them to the Oracle Database Home location

$ cd ctx/data/enlx/
$ cp * $ORACLE_HOME/ctx/data/enlx

Oracle Grid Control Exadata Storage Cell Plug-in Installation/Upgrade

The Oracle Enterprise Manager Grid Control Exadata Storage Cell Plug-in allows that management agent to be able to monitor and assist with the basic management of the Exadata Storage Cells. The Exadata Storage Cell Plug-in is compatible with Oracle Enterprise Manager Grid Control version 10.2.0.4 and above.
The installation and upgrade process is basically the same process. The difference is that on an upgrade is the pre-installation setup will not be needed.

Pre-Installation Setup
Prior to initial installation of the Exadata Storage Cell Plug-in you will need to identify a server that will have an agent that will be the monitoring agent for the Storage Cells. The that will have the Grid Control Agent that will monitor the Storage Cells must have connectivity to the Exadata Storage Cells via SSH. For this to happen that server must have a network interface on the management network or the infini-band for the Exadata.

1. Setup Server with Network connectivity to the Exadata Network which has the storage cells.

2. Setup hosts file on the server that will house the agents to see each of the storage cells.

3. Setup ssh connectivity to each of the storage cells
     a. generate the ssh keys on the sever that will house the agent
          i. ssh-keygen -t dsa -f id_dsa

     b. copy the ssh key to each of the storage cell servers as the celladmin user
          i. scp id_dsa.pub celladmin@dm01cel01:~
          ii. repeat for each storage cell

     c. log into the storage cell as the root user
          i. ssh root@dm01cel01

     d. Add contents of the id_dsa file to the authorized_keys file for the cellmonitor user on each of the storage cells.
          i. check if the authorized_keys file exists for the cellmonitor user
               a. ls -l /home/cellmonitor/.ssh/*
          ii. if the authorized keys file exists for the cellmonitor user
               a. cat /home/celladmin/id_dsa.pub >> /home/cellmonitor/.ssh/authorized_keys
          iii. if the authorized keys file does not exist for the cell monitor user
               a. cp /home/celladmin/id_dsa.pub /home/cellmonitor/.ssh/authorized_keys
          iv. repeat for each of the storage cell servers

4. Test ssh connectivity from the server that will house the Grid Control Agent for each of the storage cells.
     a. ssh -l cellmonitor dm01cel01 cellcli -e 'list cell detail'
        ** If you prompted for a password it not not working correctly need to check/repeat setup steps
        ** If you asked to confirm indicates Yes to continue with connection
     b. repeat test for each storage cell server.

5. Install the Oracle Enterprise Manager Grid Control Agent on the server that will serve as the monitoring Exadata Storage Cell monitoring Agent for the Grid Control Plug-in.

** It is ok for the monitoring agent for the Exadata Storage Cells to be the Gird Control Management Server Agent.

Plug-in Installation
For the plug-in installation the plug-in is imported into OEM Grid Control. This is the case for a new installation or a new version for an existing installation. This also assumes that you have an Oracle Grid Control Management Server/Service Setup and accessible.

1. Download the Oracle Enterprise Manager Grid Control Exadata Storage Server Plug-in.
     a. http://www.oracle.com/technetwork/database/exadata/index.html

2. Import the Plug-in into the Oracle Enterprise Manager Grid Control.
     a. Select Setup from the Top Right portion of the Browser Window
     b. Select Management Plug-ins from the list menu on the right hand side of the window.
     c. Select Import Option
     d. Select Browse to locate the Exadata Storage Cell Plug-in
     e. Locate and Select the Exadata Storage Cell Plug-in File Downloaded and Select Open
     f. Select List Archive to Expand out the Plug-in
     g. Select the oracle_cell Plug-in ensuring the check mark is present next to the Plug-in then select OK
     h. Verify the Plug-in Version is now in the list of Plug-ins.
     j. Management Plug-in Installation Complete. 

Plug-in Deployment
The Plug-in deployment is done the same regardless if there is an existing plug-in version deployed. Once the Plug-in is imported you deploy the plug-in the same. This process assumes that the Grid Control Monitoring Agent that will serve as the monitoring agent for the Exadata Storage Cells is installed and registered with you Oracle Management Service.
1. Log into Oracle Enterprise Manager Grid Control Management Service.

2. Set Preferred Credentials for the host of monitoring agent Plug-in will be deployed to as well as the monitoring agent.
     a. Select Preferences from top right of Browser Window displaying management console.
     b. select preferred credentials
     c. select Set Credentials Icon for Host
     d. locate host and enter the credentials for the oracle operating system user credentials
     e. select test credentials to ensure credentials entered are working.
     f. select apply.
     g. select Preferred Credentials in the upper left part of the screen
     h. select Set Credentials Icon for Agent
     i. Locate the agent to set credentials for and enter the oracle operating system user credentials
     j. select apply
     k. Select Home Tab to return to the Home Console Screen

3. Select Setup from the Top Right portion of the Browser Window

4. Select Management Plug-ins from the list menu on the right hand side of the window.

5. For the Exadata Plug-in to Deploy Select the Deploy icon. When Upgrading the Exadata Plug-in Select the new version to deploy.

6. Add Agent to the List of Agents to Deploy Plug-in to.

7. Select the Agent that will serve as the monitoring agent for the Exadata Storage Cells and then press Select.

8. To Add More Agents, repeat the Add Agent Steps, When finished Select Next to Continue with Deploying Plug-in.

9. Select Finish to Deploy Agent.

10. Deployment Completed

Exadata Hybrid Columnar Compression

Summary
Hybrid Columnar Compression was introduced with the Exadata version 2 and is one of the most highlighted advantages of an Exadata as it improves performance and significantly reduces the storage footprint of the data on physical storage. By reducing the foot print of the data on physical storage it also reduces the foot print of that data in the database cache area as well therefore offering overall improvement in system memory resources in addition to physical storage. While you can use the columnar compression options in Exadata it does not prevent you from using the Oracle 11gR2 Advanced Compression as well giving you three different levels of compress based on the data within your database.

There are 2 types of columnar compression with Exadata, compress for query and compress for archive. The compress for query option offers a high level of compression, but not as high as for archive, but offers better query performance then the for archive option. While the OLTP compression in 11gR2 could work well for more online type access.

The advantages to the multiple types of compression is the level of compression based on the type of data. This gives the ability to true start to consider the Information Lifecycle Management of your data in regards to how it is stored and better manage your storage resources. For example let say you have a very large table partitioned by month where the first 24 months of data is subject to regular insert, update, delete and query activity, then from 24 months to 60 months the data is not updated, but query activity is heavy then data over 60 months is rarely accessed but does need to be available. Your best option for the most recent 24 months of database would most likely be OLTP compression. For the data between 24 and 60 months compress for query columnar compression would be your best option and the data over 60 months old would be best suited for compress for archive column compression.

Compress for Query Columnar Compression
Compress for query Columnar Compress offers a good balance between frequent query access and compression level. The compress for query can be high (default) or low. This indicates the level of compression and subsequently impact on CPU resources to handle the compression. The query option has between a 5-10% runtime hot on direct loads over an uncompress table load which in most cases can be considered minimal when compared to the disk savings achieved. The compression significantly reduces the size of the table and at the same time improves the performance of queries without very high impact on other system resources. With the implementation of Hybrid Columnar Compression for query high reduction consistently has between 60-70% . The table compress for query below shows some real world results when using the compress for query high option. With the implementation of Hybrid Columnar Compression with the compress for archive option

Compress for Query High
Uncompressed(MB) Compress      Reduction(MB)    % Reduction
903              344           559              61.90
1088             408           680              62.50
960              361           599              62.40
1088             416           672              61.76
1152             400           752              65.28
1091             400           691              63.33
1216             456           760              62.50

1112             408           704              63.31

Compress for Archive Columnar Compression
The compress for archive columnar compression offers the highest level of compression available, but with the higher level of compression the impact to system CPU resources increases therefore making it a less desired option where consistent query access is needed. The archive compress option averages between 70-80% size reduction over uncompressed tables. The archive option is also a significant hit on direct load runtimes as an average 50% runtime hits have been experienced over query compressed table direct loads. This hit on load time could also make this a less desired option for objects with frequent load activities where load runtimes are critical. As the name indicates it works very well for compressing down data that is archived and needs to be available, but is not accessed frequently or load times are a critical requirement.

Compress for Archive
Uncompressed(MB) Compress     Reduction(MB)    % Reduction
903              264          639              70.76
1088             304          784              72.06
960              272          688              71.67
1088             312          776              71.32
1152             336          816              70.83
1091             328          763              69.94
1216             352          864              71.05
1112             304          808              72.66 

Effectively Utilizing the Hybrid Column Compression              
Query and Archive Hybrid Columnar Compression is most effective for historical tables/date where the data no longer changes.  Being that they are historical tables they are typically quite large we usually will utilize with a partitioned table as best practice for large tables is to partition them.  The most effective use of the Hybrid Columnar Compression is to use them both within your partitioned historical table where the more frequently queries data partitions are query hybrid columnar compressed and the less frequently accessed data is archive columnar compressed.  This creates an effect Information Life Cycle Management that will help you manage your partition sizes based on their age and access frequency.  

Notice in the example below the more recently accessed data is partitioned by month allowing faster access to data through more fine tuned partition elimination.  Space is saved through the query hybrid columnar compression on those partitions.  Now as the data gets older partition maintenance is performed to combined partitions by year and the new partition for the entire year utilizes hybrid columnar compression to save a little more space and still keep the data accessible.

Example

CREATE TABLE ARCHIVE."CLAIM"

   (   "CLAIM_ID"            VARCHAR2(36)   NOT NULL ENABLE,

       "CLAIM_TYPE_CODE"     VARCHAR2(2)    NOT NULL ENABLE,

       "PAYER_PROCESS_STAGE" VARCHAR2(10),

       "SYS_CREATE_DATE"     DATE,

       "SYS_UPDATE_DATE"     DATE)

TABLESPACE ARCHIVE_2004

PARTITION BY RANGE (SYS_CREATE_DATE) interval (NUMTOYMINTERVAL(1,'month'))               

   (PARTITION CLAIM_2004 VALUES LESS THAN (TO_DATE ('1-JAN-2005','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE ARCHIVE_2004 compress for archive high,

    PARTITION CLAIM_2005 VALUES LESS THAN (TO_DATE ('1-JAN-2006','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE ARCHIVE_2005 compress for archive high,

    PARTITION CLAIM_2006 VALUES LESS THAN (TO_DATE ('1-JAN-2007','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE ARCHIVE_2006 compress for archive high,

    PARTITION CLAIM_200701 VALUES LESS THAN (TO_DATE ('1-FEB-2007','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE OTHER_PART_QUERY compress for query,

    PARTITION CLAIM_200702 VALUES LESS THAN (TO_DATE ('1-MAR-2007','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE OTHER_PART_QUERY compress for query,

    PARTITION CLAIM_200703 VALUES LESS THAN (TO_DATE ('1-APR-2007','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE OTHER_PART_QUERY compress for query

   )

compress for archive high;

Hope to see you all at open World
Will be attending the Oracle Exadata SIG
Presenting Sunday and Monday
Spending some time in the Rolta-TUSC Booth Monday
Come by and see me.

Oracle Compression Advisor

Every once and awhile I come across a valuable utility, I have come across a new
Oracle utility that can be utilized starting with 9i release 2 that can help estimate the space savings from compression.

The process creates tables in the default tablespace of the user executing the procedure. It is recommended that you make sure the default tablespace is other then SYSTEM or SYSAUX.

The process is created via a package procedure available via a download from Oracle Technet at

http://www.oracle.com/technology/products/database/compression/compression-advisor.html

Example of the package installation
[oracle@oel54node1 compression_advisor]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 23 22:33:57 2010

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


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

SQL> @dbmscomp.sql

Package created.


Synonym created.


Grant succeeded.

No errors.
SQL> @prvtcomp.plb

Package body created.


Synonym created.


Grant succeeded.

No errors.


Here is an example of its execution

SQL> set serveroutput on
SQL> exec dbms_comp_advisor.getratio('SYS','AUD$','OLTP',20) ;
Sampling table: SYS.AUD$
Sampling percentage: 20%
Compression Type: OLTP
Compression ratio: 1.53

PL/SQL procedure successfully completed.