Generate ASH reports

You can call the Active Session History (ASH) PL package to generate corresponding ASH reports.

Limitations

OceanBase Database supports ASH PL packages only in the Oracle mode.

Syntax:

An ASH PL package consists of the following fields:

obclient [SYS]>call dbms_workload_repository.ash_report(     
  BTIME => 'xxxx',
  ETIME => 'xxxx',  
  SQL_ID => 'xxxx',
  TRACE_ID => 'xxxx',
  WAIT_CLASS => 'xxxx',
  );

• BTIME: the sampling start time.
• ETIME: the sampling end time.
• SQL_ID: the ID of the SQL statement to be sampled. If you do not specify this field or set it to NULL, the SQL ID is not limited.
• TRACE_ID: the trace ID of the SQL statement to be sampled. If you do not specify this field or set it to NULL, the trace ID is not limited.
• WAIT_CLASS: the type of the event to be sampled. Specify WAIT_CLASS in accordance with the value of the WAIT_CLASS field in the GV$ACTIVE_SESSION_HISTORY view. If you do not specify this field or set it to NULL, the wait event type is not limited.
• REPORT_TYPE: the type of the report. Currently, only the text type is supported. This field can be left empty.

You can obtain the relevant information in the package by querying the GV$ACTIVE_SESSION_HISTORY view. The GV$ACTIVE_SESSION_HISTORY view provides information about session activities. Any database access session that is queued is considered an active session. Each session is sampled based on a set of row data returned by the GV$ACTIVE_SESSION_HISTORY view. All sessions in the session sampling period are displayed in the view. For more information about this view in the Oracle mode, see GV$ACTIVE_SESSION_HISTORY.

Procedure

To generate an ASH report, perform the following steps:

1. Log on to an Oracle tenant of the OceanBase cluster as the SYS user.

   $ obclient -h172.30.xxx.xxx -Pxxxx -usys@oracle -pxxxx -A
   obclient [SYS]>
   

2. Call set serveroutput on; to print the output.

   obclient [SYS]> set serveroutput on;
   

3. Call the ASH PL package and enter the diagnosis start time and end time to generate the report content.

   • Display the report content on the screen.

     obclient [SYS]>call dbms_workload_repository.ash_report(     
       --The sampling start time.
       to_date('2023-02-22 19:26:47', 'yyyy-MM-dd HH24:mi:ss'),
       --The sampling end time.
       to_date('2023-02-22 19:27:07', 'yyyy-MM-dd HH24:mi:ss'),
       -- Samples SQL statement whose SQL ID is 195E74D0ACE24FF470774DF649D62921.
       sql_id=>'195E74D0ACE24FF470774DF649D62921');
       -- Set TRACE_ID and WAIT_CLASS in a similar way as SQL_ID. Example: trace_id='xxxxxx'.
     Query OK, 0 rows affected (29.49 sec)
     

• Display the report content in a TXT file.

  1. Generate the report.sql script file.

     vim report.sql
     

     set serveroutput on;
     tee report.txt
     call dbms_workload_repository.ash_report(
               ## The sampling start time.
               to_date('2022-01-01 11:11:11', 'yyyy-MM-dd HH24:mi:ss'),
               ## The sampling end time.
               to_date('2022-11-10 12:18:36', 'yyyy-MM-dd HH24:mi:ss'),
               ## Samples SQL statements whose SQL ID is 4AB32EAB00C8F7C1D29E9EDC62FFF310.
               sql_id=>'195E74D0ACE24FF470774DF649D62921');
               ## Set TRACE_ID and WAIT_CLASS in a similar way as SQL_ID. Example: trace_id='xxxxxx'.
     notee
     

  2. Log on to the database and execute the start report.sql command. The results are printed to the report.txt file.

     $ obclient -h172.30.xxx.xxx -P2881 -usys@oracle -pxxxx -A
     obclient [SYS]> source report.sql;
     

More information

• ASH
• Analyze ASH reports