Generate ASH reports

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

SQL syntax

An ASH PL package consists of the following parameters:

obclient [oceanbase]> call dbms_workload_repository.ash_report(
  'BTIME', 
  'ETIME', 
  'sql id', 
  'trace id', 
  'wait class');

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

The following table describes the parameters.

You can query the GV$ACTIVE_SESSION_HISTORY view for relevant information in the package. 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, see GV$ACTIVE_SESSION_HISTORY in MySQL mode and GV$ACTIVE_SESSION_HISTORY in Oracle mode.

Procedure

To generate an ASH report in Oracle mode, perform the following steps:

1. Log in 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 diagnostics 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'),
       -- Sample the 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 parameters must be set.
     • The REPORT_TYPE parameter can be set to TEXT only.
     • Set the SQL_ID, TRACE_ID, and WAIT_CLASS parameters 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'),
                  ## Sample the 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'.
        notee
        

     2. Log in to the database and run 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