Comparing workloads across databases

Does this question sound familiar? “We are running the exact same workload in UAT as we do in production, but it behaves completely different. Can you help?”

If it does, then you might find the following post useful, provided you are appropriately licensed to use this feature anyway. I have been working on AWR data for a long time, and one of the best ways to do so according to David Kurtz and others, is to do with the AWR data directly. I have seen a number of presentations on how to consolidate AWR data from multiple sources in a single repository, and this is just my write up of this same procedure for my purpose. I don’t even want to pretend it’s elegant, but it works for me-it might work for you too but I can’t guarantee that :)

Setup

To demonstrate the concept I once again use Dominic Giles’s most excellent Swingbench. I am simulating two environments, “development” and “production”. Development is hosted on a virtual machine named server3, with 2 virtual CPUs and 8 GB DRAM. The production environment is my lab server, with 2s12c24t and 64 GB DRAM. To demonstrate a case of “same workload-vastly different performance” I created the swingbench order entry schema (SOE) on development without any indexes. All indexes are present in production.

[oracle@server3 ~]$ ./oewizard -cl -create -cs //server3/NCDB -dba system -dbap … -noindexes -nopart -p soe -scale 1 -ts soe_tbs -u soe -v

If you create the schema on the command line like I just did, make sure to grant execute on DBMS_LOCK to SOE as sys once the procedure is complete.

The command to create the swingbench schema in “production” included all the indexes, for this reason alone there should be a lot more throughput seen in “production” compared to “development”.

The workload has been generated using charbench, and I specified that AWR snapshots should be created at the beginning and end of the test. Each test had a run-time of 10 minutes. To make sure I am using the same setup I used the same swingconfig.xml (with the exception of the connection string of course).

[oracle@lab bin]$ ./charbench -c swingconfig_server3.xml -cs //lab/NCDB
Author  :        Dominic Giles
Version :        2.5.0.971

Results will be written to results.xml.
Hit Return to Terminate Run...

Time            Users   TPM     TPS

16:15:27        5       3838    72                                             
...

Working with AWR data

Working with raw AWR data and how to move it between systems is documented in the Database Performance Guide: chapters 6 covers transporting AWR data and chapter 8 covers the aspect of comparison. On a high level, the procedure in this blog involves only very few steps:

  1. Extract AWR data from each database, production and development
  2. Transfer the resulting AWR data to the AWR repository database. In my case this is a third database that will contain the AWR information about itself, and the AWR extracts to be loaded
  3. Import the data

AWR data export

Here is an example of the procedure that I completed on production to export the AWR data for my swingbench run. CAREFUL: the dump can be very large! Ensure you have the necessary space in your destination directory.

[oracle@lab ~]$ ncdb
The Oracle base remains unchanged with value /u01/app/oracle
environment switched to NCDB
[oracle@lab ~]$ sq

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 23 15:48:42 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @?/rdbms/admin/awrextr
~~~~~~~~~~~~~
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                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     DB Name      Host
------------ ------------ ------------
* 3338715777 NCDB         lab

The default database id is the local one: '3338715777'.  To use this
database id, press <return> to continue, otherwise enter an alternative.

Enter value for dbid: 

Using 3338715777 for Database ID


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 <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots

DB Name        Snap Id    Snap Started
------------ --------- ------------------
NCDB                64 23 Sep 2016 10:21
                    65 23 Sep 2016 10:21
                    66 23 Sep 2016 10:24
                    67 23 Sep 2016 10:24
                    68 23 Sep 2016 10:34
                    69 23 Sep 2016 10:34
                    70 23 Sep 2016 15:51
                    71 23 Sep 2016 16:08
                    72 23 Sep 2016 16:18


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 71
Begin Snapshot Id specified: 71

Enter value for end_snap: 72
End   Snapshot Id specified: 72


Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_FILE_DIR                  /u01/app/oracle/product/12.1.0.2/dbhome_1/demo/sc
                               hema/sales_history/

DATA_PUMP_DIR                  /u01/app/oracle/admin/NCDB/dpdump/
LOG_FILE_DIR                   /u01/app/oracle/product/12.1.0.2/dbhome_1/demo/sc
                               hema/log/

MEDIA_DIR                      /u01/app/oracle/product/12.1.0.2/dbhome_1/demo/sc
                               hema/product_media/

OPATCH_INST_DIR                /u01/app/oracle/product/12.1.0.2/dbhome_1/OPatch

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
OPATCH_LOG_DIR                 /u01/app/oracle/product/12.1.0.2/dbhome_1/QOpatch
OPATCH_SCRIPT_DIR              /u01/app/oracle/product/12.1.0.2/dbhome_1/QOpatch
ORACLE_BASE                    /
ORACLE_HOME                    /
ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/12.1.0.2/dbhome_1/ccr/hos
                               ts/lab/state

ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/12.1.0.2/dbhome_1/ccr/sta
                               te

SS_OE_XMLDIR                   /u01/app/oracle/product/12.1.0.2/dbhome_1/demo/sc

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
                               hema/order_entry/

SUBDIR                         /u01/app/oracle/product/12.1.0.2/dbhome_1/demo/sc
                               hema/order_entry//2002/Sep

XMLDIR                         /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/x
                               ml

XSDDIR                         /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/x
                               ml/schema


Choose a Directory Name from the above list (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_71_72.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for file_name: prod_awrdat_71_72

Using the dump file prefix: prod_awrdat_71_72
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /u01/app/oracle/admin/NCDB/dpdump/
|   prod_awrdat_71_72.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:
|   /u01/app/oracle/admin/NCDB/dpdump/
|   prod_awrdat_71_72.log
|

End of AWR Extract
SQL> 

As a result of that operation you’ll get 2 files: a log file and a dump file. As you can imagine there is some data pump activity involved. Transfer the dump file to your AWR repository database. In a fit of curiosity I named that database AWRDWH.

Repeat this task with the other database, in my case it’s NCDB on server3, selecting the releavant AWR snapshots and transfer the dump file to the AWRDWH database’s DATA_PUMP_DIR.

Loading the AWR extracts

Once the data is available for load, switch environment to the AWR repository database and load it. In this example I’m loading the extract from server3.


[oracle@lab ~]$ awrdwh
The Oracle base remains unchanged with value /u01/app/oracle
environment switched to AWRDWH
[oracle@lab ~]$ sq

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 23 15:54:18 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> @?/rdbms/admin/awrload
~~~~~~~~~~
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           ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u01/app/oracle/admin/AWRDWH/dpdump/
OPATCH_INST_DIR                /u01/app/oracle/product/12.1.0.2/dbhome_1/OPatch
OPATCH_LOG_DIR                 /u01/app/oracle/product/12.1.0.2/dbhome_1/QOpatch
OPATCH_SCRIPT_DIR              /u01/app/oracle/product/12.1.0.2/dbhome_1/QOpatch
ORACLE_BASE                    /
ORACLE_HOME                    /
ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/12.1.0.2/dbhome_1/ccr/hos
                               ts/lab/state

ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/12.1.0.2/dbhome_1/ccr/sta
                               te

XMLDIR                         /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/x
                               ml

XSDDIR                         /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/x
                               ml/schema


Choose a Directory Name from the list above (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:

Enter value for file_name: dev_awrdat_6_7

Loading from the file name: dev_awrdat_6_7.dmp

Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.


The default staging schema name is AWR_STAGE.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for schema_name: 

Using the staging schema name: AWR_STAGE

Choose the Default tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE users's default tablespace.  This is the
tablespace in which the AWR data will be staged.

TABLESPACE_NAME                CONTENTS  DEFAULT TABLESPACE
------------------------------ --------- ------------------
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in the recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: USERS

Using tablespace USERS as the default tablespace for the AWR_STAGE


Choose the Temporary tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporary tablespace.

TABLESPACE_NAME                CONTENTS  DEFAULT TEMP TABLESPACE
------------------------------ --------- -----------------------
TEMP                           TEMPORARY *

Pressing <return> will result in the database's default temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as the temporary tablespace for AWR_STAGE




... Creating AWR_STAGE user

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /u01/app/oracle/admin/AWRDWH/dpdump/
|   dev_awrdat_6_7.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Load Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR load operation can be
|  monitored in the following directory/file:
|   /u01/app/oracle/admin/AWRDWH/dpdump/
|   dev_awrdat_6_7.log
|
... Dropping AWR_STAGE user

End of AWR Load


Repeat the process with the production extract.

Compare

Now that you have both extracts, you can actually compare them!


SQL> @?/rdbms/admin/awrddrpi

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
------------ -------- ------------ ------------ ------------
* 539355091         1 AWRDWH       AWRDWH       lab
  3338715777        1 NCDB         NCDB         lab
  3612449208        1 SLOB         SLOB         lab
  3363997070        1 NCDB         NCDB         server3.exam
                                                ple.com

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for dbid: 3363997070
Using 3363997070 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 <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
NCDB         NCDB                 6 23 Sep 2016 13:17      1
                                  7 23 Sep 2016 13:27      1



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

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




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

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 539355091         1 AWRDWH       AWRDWH       lab
  3338715777        1 NCDB         NCDB         lab
  3612449208        1 SLOB         SLOB         lab
  3363997070        1 NCDB         NCDB         server3.exam
                                                ple.com




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

Enter value for dbid2: 3338715777
Using 3338715777 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 <return> without
specifying a number lists all completed snapshots.


Enter value for num_days2: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
NCDB         NCDB                64 23 Sep 2016 10:21      1
                                 65 23 Sep 2016 10:21      1
                                 66 23 Sep 2016 10:24      1
                                 67 23 Sep 2016 10:24      1
                                 68 23 Sep 2016 10:34      1
                                 69 23 Sep 2016 10:34      1

                                 71 23 Sep 2016 16:08      1
                                 72 23 Sep 2016 16:18      1



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

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



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

Enter value for report_name: 

Using the report name awrdiff_1_6_1_71.txt
...

This will produce the report in text format-not as useful as the HTML counterpart but it helps me copy/paste it here. Let’s have a look at the result:

WORKLOAD REPOSITORY COMPARE PERIOD REPORT

Snapshot Set    DB Id    Instance     Inst Num Release     Cluster Host          Std Block Size
------------ ----------- ------------ -------- ----------- ------- ------------ ---------------
First (1st)   3363997070 NCDB                1 12.1.0.2.0  NO      server3.exam     8192
Second (2nd)  3338715777 NCDB                1 12.1.0.2.0  NO      lab              8192

Snapshot Set  Begin Snap Id Begin Snap Time            End Snap Id End Snap Time                  Avg Active Users           Elapsed Time (min)            DB time (min)
------------ -------------- ------------------------- ------------ ------------------------- -------------------------- -------------------------- --------------------------
1st                       6 23-Sep-16 13:17:32 (Fri)            7 23-Sep-16 13:27:35 (Fri)                       4.7                      10.1                      47.7
2nd                      71 23-Sep-16 16:08:31 (Fri)           72 23-Sep-16 16:18:32 (Fri)                       0.6                      10.0                       5.7
                                                                                      ~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                                                      %Diff:                   -88.0%                      -0.4%                     -88.1%

Host Configuration Comparison
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                     1st                  2nd                 Diff     %Diff
----------------------------------- -------------------- -------------------- -------------------- ---------
Number of CPUs:                                        2                   24                   22   1,100.0
Number of CPU Cores:                                   2                   12                   10     500.0
Number of CPU Sockets:                                 2                    2                    0       0.0
Physical Memory:                                 7725.4M             64135.1M              -56410M     730.2
Load at Start Snapshot:                             2.04                  .95                -1.09     -53.4
Load at End Snapshot:                               3.12                 1.15                -1.97     -63.1
%User Time:                                        95.41                 1.51               -93.89     -98.4
%System Time:                                       3.53                  2.6                 -.93     -26.3
%Idle Time:                                          .94                95.87                94.93  10,098.9
%IO Wait Time:                                       .13                  .17                  .04      30.8
Cache Sizes
~~~~~~~~~~~
                          1st (M)    2nd (M)   Diff (M)    %Diff
---------------------- ---------- ---------- ---------- --------
Memory Target
.....SGA Target             768.0    8,192.0    7,424.0    966.6
..........Buffer Cache      516.0    5,440.0    4,924.0    954.3
..........Shared Pool       200.0    1,152.0      952.0    476.0
..........Large Pool          8.0      640.0      632.0  7,900.0
..........Java Pool           4.0      384.0      380.0  9,500.0
..........Streams Pool                 128.0      128.0    100.0
.....PGA Target             256.0    8,192.0    7,936.0  3,100.0
Log Buffer                    4.9       55.6       50.7  1,024.3
In-Memory Area                             0          0      0.0

Workload Comparison
~~~~~~~~~~~~~~~~~~~                      1st Per Sec          2nd Per Sec      %Diff              1st Per Txn          2nd Per Txn      %Diff
                                     ---------------      ---------------     ------          ---------------      ---------------     ------
                       DB time:                  4.7                  0.6      -88.0                      1.6                  0.0      -99.4
                      CPU time:                  1.9                  0.3      -86.6                      0.6                  0.0     -100.0
           Background CPU time:                  0.0                  0.1      600.0                      0.0                  0.0        0.0
             Redo size (bytes):             16,253.0            201,173.5    1,137.8                  5,461.1              3,354.0      -38.6
         Logical read (blocks):            266,911.0             30,851.5      -88.4                 89,684.0                514.4      -99.4
                 Block changes:                 76.9              1,343.3    1,646.4                     25.8                 22.4      -13.3
        Physical read (blocks):             37,003.5                600.2      -98.4                 12,433.4                 10.0      -99.9
       Physical write (blocks):                  7.5                 90.8    1,113.4                      2.5                  1.5      -39.8
              Read IO requests:                803.9                 65.5      -91.9                    270.1                  1.1      -99.6
             Write IO requests:                  5.6                 42.2      659.0                      1.9                  0.7      -62.6
                  Read IO (MB):                289.1                  4.7      -98.4                     97.1                  0.1      -99.9
                 Write IO (MB):                  0.1                  0.7    1,083.3                      0.0                  0.0      -50.0
                  IM scan rows:                  0.0                  0.0        0.0                      0.0              3,324.7      100.0
       Session Logical Read IM:
                    User calls:                 10.0                192.3    1,815.4                      3.4                  3.2       -4.7
                  Parses (SQL):                 14.7                 71.6      388.3                      4.9                  1.2      -75.9
             Hard parses (SQL):                  2.2                  0.4      -80.6                      0.7                  0.0      -98.6
            SQL Work Area (MB):                  1.3                 20.9    1,558.7                      0.4                  0.3    1,558.7
                        Logons:                  0.1                  0.1        0.0                      0.0                  0.0     -100.0
                Executes (SQL):                 67.6                658.0      873.5                     22.7                 11.0      -51.7
                  Transactions:                  3.0                 60.0    1,912.8

                                               First               Second       Diff
                                     ---------------      ---------------     ------
     % Blocks changed per Read:                  0.0                  4.4        4.3
              Recursive Call %:                 94.5                 79.5      -15.0
    Rollback per transaction %:                  0.0                  0.0        0.0
                 Rows per Sort:                 18.1                 12.8       -5.4
    Avg DB time per Call (sec):                  0.5                  0.0       -0.5

One of the treats of the difference report is that it shows you the difference in environment. It is immediately visible that the hosts are quite different, and as a result, so is the throughput. This is not much of a revelation because I knew from the beginning that my results were going to be different, but if you are seeing the systems for the first time this might be useful information.

Using sections further down in the report you should be able to compare execution statistics for the various SQL IDs (they are identical because of my identical workloads), and other pieces of information that give you a better understanding of what is going on.

Advertisements

One thought on “Comparing workloads across databases

  1. mvelikikh

    Hello Martin,

    I have a custom solution transferring AWR data from different databases into a single database which I had built before Oracle Enterprise Manager AWR Performance Warehouse was announced.
    I utilize the procedures used in awrload/awrextr scripts.
    Once, when I upgraded my database in which AWR data was loaded, I’ve encountered an error with the subsequent AWR loads in it.
    I’ve faced the same errors as described in the MOS note: “Importing AWR Data Fails With: ORA-20105 / ORA-20116: Cannot Move Into AWR, Target DBID Needs Cleanup (Doc ID 2079992.1)”, though DBA team claimed that they didn’t miss any scripts during the upgrade.
    Having implemented the solution from the note, my AWR snapshots were stopped generating at all with an ORA-600 error.
    It has been fixed following the steps from the MOS note: ORA-600 [KEWRSPBR_2: WRONG LAST PARTITION] When Creating AWR snapshot in 12.1.0.2 (Doc ID 2020227.1)
    I’ve faced no similar issues with other databases in my environment after an upgrade.
    I’m not 100% sure that it was related to AWR loads though.

    There is a hidden gem of that solution – we can use several 12c specific reports against 11g AWR data loaded into a 12c database.
    For instance, I generated an ADDM-compare databases report (dbms_addm.compare_databases) against two pairs of snapshots of 11g database.
    I also generated a performance hub report (dbms_perf.report_perfhub) that has been available since 12c.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s