Generate ASH reports

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

The parameters are described as follows:

You can obtain the relevant information in the package by querying the GV$OB_ACTIVE_SESSION_HISTORY view. The GV$OB_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$OB_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$OB_ACTIVE_SESSION_HISTORY.

Procedure

Using the Oracle mode as an example, the steps to generate an ASH report are as follows:

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 15B18561C66FCF844D00386749C0DA49.
       sql_id=>'15B18561C66FCF844D00386749C0DA49');
       -- 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)
     

     Note

     • The BTIME and ETIME fields must be set.
     • The REPORT_TYPE field can be set to text only.
     • Set the SQL_ID, TRACE_ID, and WAIT_CLASS fields as required.

   • 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 15B18561C66FCF844D00386749C0DA49.
                  sql_id=>'15B18561C66FCF844D00386749C0DA49');
                  ## 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;
        

References

• ASH
• Analyze ASH reports