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

We can also   generate   historical sql  monitoring report  using dbms_auto_report 

==>  Getting Details 

SQL> select distinct COMPONENT_NAME from DBA_HIST_REPORTS;

COMPONENT_NAME

-----------------------------------------------------------

sqlmonitor

perf

COL SQL_EXEC_START for a25

col sql_exec_id form a20

col  sql_id for a20

select key1 as sql_id ,  to_char( key2) as sql_exec_id ,   

to_char( to_date (key3 , 'mm:dd:yyyy hh24:mi:ss')  , 'yyyy-mm-dd hh24:mi:ss' ) sql_exec_start , 

report_id , instance_number , session_id , session_serial# , period_start_time , period_end_time 

from dba_hist_reports where key1='&sql_id'  order by sql_exec_start ;

SELECT report_id,

       EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_id') sql_id,

       EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_id') sql_exec_id,

       EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_start') sql_exec_start

  FROM dba_hist_reports

 WHERE component_name = 'sqlmonitor'

SELECT /*+ NO_XML_QUERY_REWRITE */ t.report_id, x1.sql_id, x1.plan_hash, x1.sql_exec_id, x1.elapsed_time/1000000 ELAP_SEC

FROM dba_hist_reports t    

   , xmltable('/report_repository_summary/sql'    

       PASSING xmlparse(document t.report_summary)    

       COLUMNS    

         sql_id                path '@sql_id'     

       , sql_exec_start        path '@sql_exec_start'    

       , sql_exec_id           path '@sql_exec_id'      

       , status                path 'status'    

       , sql_text    path 'sql_text'

       , first_refresh_time    path 'first_refresh_time'

       , last_refresh_time     path 'last_refresh_time'

       , refresh_count         path 'refresh_count'

       , inst_id               path 'inst_id'

       , session_id            path 'session_id'

       , session_serial        path 'session_serial'

       , user_id               path 'user_id'

       , username              path 'user'

       , con_id                path 'con_id'

       , con_name              path 'con_name'

       , modul                 path 'module'

       , action                path 'action'

       , service               path 'service'

       , program               path 'program'

       , plan_hash             path 'plan_hash'

       , is_cross_instance     path 'is_cross_instance'

       , dop    path 'dop'

       , instances             path 'instances'

       , px_servers_requested  path 'px_servers_requested'

       , px_servers_allocated  path 'px_servers_allocated'

       , duration              path 'stats/stat[@name="duration"]'  

       , elapsed_time          path 'stats/stat[@name="elapsed_time"]'  

       , cpu_time              path 'stats/stat[@name="cpu_time"]'  

       , user_io_wait_time     path 'stats/stat[@name="user_io_wait_time"]'

       , application_wait_time path 'stats/stat[@name="application_wait_time"]'

       , concurrency_wait_time path 'stats/stat[@name="concurrency_wait_time"]'

       , cluster_wait_time     path 'stats/stat[@name="cluster_wait_time"]'

       , plsql_exec_time       path 'stats/stat[@name="plsql_exec_time"]'

       , other_wait_time       path 'stats/stat[@name="other_wait_time"]'

       , buffer_gets           path 'stats/stat[@name="buffer_gets"]'

       , read_reqs             path 'stats/stat[@name="read_reqs"]'

       , read_bytes            path 'stats/stat[@name="read_bytes"]'

     ) x1 

where x1.elapsed_time/1000000 > 200

and   t.COMPONENT_NAME = 'sqlmonitor'

order by 5

/

==> Report Generation 

set long 1000000

set longsize 330

set longchunksize 1000000

set pagesize 0

set trim on 

set echo off

set feedback off 

set trimspool on 

select dbms_auto_report.report_repository_detail( RID=> &report_id , type=> 'TEXT' ) as  report  from dual ; 

or 

select dbms_auto_report.report_repository_detail( RID=> &report_id , type=> 'active' ) as  report  from dual ; 

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)