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:
- Extract AWR data from each database, production and development
- 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
- 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.
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.