Oracle Database Performance Hub report using DBMS_PERF

  When we  have performance issues normally we have to login to  oem  to get  performance details . 

  We can get quick snap of  performance using DBMS_PERF.REPORT_PERFHUB    as mentioned below .   Its old tool  introduced in 12c but  not frequently used . 

Command : 

SET  LONG   10000000 ;

 SET LONGC 10000000 ;

 SET LINESIZE 32767 ;

 SET PAGESIZE 0 ;

 SET TRIMSPOOL ON ;

SPOOL perfhub.html

SELECT DBMS_PERF.REPORT_PERFHUB FROM DUAL;

SPOOL OFF;

.Other option used :

SET HEADING OFF LINESIZE 32767 PAGESIZE 0 TRIMSPOOL ON TRIMOUT ON LONG 9999999 VERIFY OFF LONGCHUNKSIZE 100000 FEEDBACK OFF

SET TERMOUT OFF

spool perfhub.html

SELECT DBMS_PERF.REPORT_PERFHUB (

    is_realtime         => 0           -- (  0 = dba_hist, 1 = v$ash  )

  , outer_start_time    => sysdate-1

  , outer_end_time      => sysdate

  , selected_start_time => TIMESTAMP'2023-05-15 20:15:00'

  , selected_end_time   => TIMESTAMP'2023-05-15 20:45:00'

  , type=>'ACTIVE'

)

FROM dual ;

spool off

SET TERMOUT ON HEADING ON PAGESIZE 5000 LINESIZE 999 FEEDBACK ON 

 

Historical SQL monitoring report can be extracted for particular SQL statement using SQL Details report.

Example: To generate SQL details report to get the SQL monitoring information for particular SQL statement in historical mode.

SQL> set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000

SQL> spool sql_details_history.html

SQL> select dbms_perf.report_sql(sql_id=>'9vkyyg1xj6fgc',is_realtime=>0,type=>'active',selected_start_time=>to_date('10-SEP-18 04:00:00','dd-MON-YY hh24:mi:ss'),selected_end_time=>to_date('10-SEP-18 05:00:00','dd-MON-YY hh24:mi:ss')) from dual;

SQL> spool off

Note in 19c or higher it is better to add "outer_start_time" and  "outer_end_time" to get the desired results:

SQL> set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000

SQL> spool sql_details_history.html

SQL> select dbms_perf.report_perfhub( is_realtime=>0, outer_start_time=>to_date('06-SEP-2022 12:00:00','dd-MON-YYYY hh24:mi:ss'), outer_end_time=>to_date('06-SEP-2022 13:00:00','dd-MON-YYYY hh24:mi:ss'), selected_start_time=>to_date('06-SEP-2022 12:00:00','dd-MON-YYYY hh24:mi:ss'), selected_end_time=>to_date('06-SEP-2022 13:00:00','dd-MON-YYYY hh24:mi:ss')) from dual;

SQL> spool off

Generating Database Performance Hub Report :

@?/rdbms/admin/perfhubrpt.sql

Syntax

DBMS_PERF.REPORT_PERFHUB (
   is_realtime          IN NUMBER   DEFAULT NULL,
   outer_start_time     IN DATE     DEFAULT NULL,
   outer_end_time       IN DATE     DEFAULT NULL,
   selected_start_time  IN DATE     DEFAULT NULL,
   selected_end_time    IN DATE     DEFAULT NULL,
   inst_id              IN NUMBER   DEFAULT NULL,
   dbid                 IN NUMBER   DEFAULT NULL,
   monitor_list_detail  IN NUMBER   DEFAULT NULL,
   workload_sql_detail  IN NUMBER   DEFAULT NULL,
   addm_task_detail     IN NUMBER   DEFAULT NULL,
   report_reference     IN VARCHAR2 DEFAULT NULL,
   report_level         IN VARCHAR2 DEFAULT NULL,
   type                 IN VARCHAR2 DEFAULT 'ACTIVE',
   base_path            IN VARCHAR2 DEFAULT NULL);
 RETURN CLOB;

 REPORT_PERFHUB Function Parameters

Parameter	Description
is_realtime	If 1, then real-time. If NULL (default) or 0, then historical mode.
outer_start_time	Start time of outer period shown in the time selector. If NULL (default): If is_realtime=0 (historical), then 24 hours before outer_end_time. If is_realtime=1 (realtime mode), then 1 hour before outer_end_time.
outer_end_time	End time of outer period shown in the time selector. If NULL (default), then latest AWR snapshot. If is_realtime=0 (historical), then the latest AWR snapshot If is_realtime=1 (realtime mode), this is the current time (and any input is ignored)
selected_start_time	Start time period of selection. If NULL (default) If is_realtime=0, then 1 hour before selected_end_time If is_realtime=1, then 5 minutes before selected_end_time
selected_end_time	End time period of selection. If NULL (default) If is_realtime=0, then latest AWR snapshot If is_realtime=1, then current time
inst_id	Instance ID to for which to retrieve data If -1, then current instance If number is specified, then for that instance If NULL (default), then all instances
dbid	DBID to query. If NULL, then current DBID. If is_realtime=1, then DBID must be the local DBID.
monitor_list_detail	Top N in SQL monitor list for which to retrieve SQL monitor details. If NULL (default), then retrieves top 10 If 0, then retrieves no monitor list details
workload_sql_detail	Top N in Workload Top SQL list to retrieve monitor details, If NULL (default), then retrieves top 10 If 0, then retrieves no monitor list details
addm_task_detail	Maximum N latest ADDM tasks to retrieve If NULL (default), retrieves available data but no more than N If 0, then retrieves no ADDM task details
report_reference	Must be NULL when used from SQL*Plus.
report_level	'typical' will get all tabs in performance hub
type	Report type: 'ACTIVE' (default) 'xml' returns XML
base_path	URL path for HTML resources since flex HTML requires access to external files. This is only valid for type='ACTIVE' and is typically not used. Default value will retrieve the required files from OTN.

 

Reference : 

Monitoring Database Performance Using Performance Hub Report (Doc ID 2436566.1)

https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_PERF.html#GUID-85CD8AB9-B6E0-444F-91A5-762EB92A74E9 

How To Get Historical SQL Monitor Report For SQL Statements (Doc ID 2555350.1)