Generate an ASH report

Log in to an Oracle tenant of the cluster as the SYS user and enter OceanBase Database.

$ obclient -h172.xx.xxx.xxx -Pxxxx -usys@oracle -pxxxx -A

Execute the SET SERVEROUTPUT ON statement to enable the console to display the output content.

obclient [SYS]> SET SERVEROUTPUT ON;

Display the report information on the console.

Call the ASH PL package and specify the start and end times of the diagnostic period and the format for displaying the report.

• Display the report content on the console in TEXT format.

  Set the diagnostic start time to 2024-02-22 19:26:47, the diagnostic end time to 2024-06-22 19:27:07, and the report display format to TEXT. Set other parameters as needed.

  obclient [SYS]>CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT(TO_DATE('2024-02-22 19:26:47', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-06-22 19:27:07', 'YYYY-MM-DD HH24:MI:SS'), REPORT_TYPE=> 'TEXT');
  

  Here is a portion of the generated report:

          ASH Report
              Cluster Name: test424
          Observer Version: OceanBase 4.2.4.0 (200000342024061210-c4c0c18741e45a1d40889b6147c313257xxxxxxxxx)
      Operation System Info: Linux(3.10.0-327.ali2019.alios7.x86_64)_x86_64
      User Input Begin Time: 2024-02-2219:26:47
        User Input End Time: 2024-06-2219:27:07
        Analysis Begin Time: 2024-06-1318:26:41
          Analysis End Time: 2024-06-1414:51:12
              Elapsed Time: 73471
              Num of Sample: 14750
    Average Active Sessions: 0.20
    Top Active Tenants:
      - this section lists top active tenant information
      - Total Count: num of records during ash report analysis time period
      - Wait Event Count: num of records when session is on wait event
      - On CPU Count: num of records when session is on cpu
      - Avg Active Sessions: average active sessions during ash report analysis time period
      - % Activity: activity(cpu + wait) percentage for given tenant
    +---------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
    |Tenant ID|Session Type|       Total Count|       Wait Event Count|       On CPU Count| Avg Active Sessions| % Activity|
    +---------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
    |     1004|  BACKGROUND|             14725|                   1670|              13055|                0.20|     99.83%|
    |     1004|  FOREGROUND|                25|                     11|                 14|                0.00|      0.17%|
    +---------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
  

• Display the report content on the console in HTML format.

  Set the diagnostic start time to 2024-02-22 19:26:47, the diagnostic end time to 2024-06-22 19:27:07, and the report display format to HTML. Set other parameters as needed.

  obclient [SYS]>CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT(TO_DATE('2024-02-22 19:26:47', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-06-22 19:27:07', 'YYYY-MM-DD HH24:MI:SS'), REPORT_TYPE=> 'HTML');
  

  Here is a portion of the generated report:

          Observer Version: OceanBase 4.2.4.0 (200000342024061210-c4c0c18741e45a1d40889b6147c313257xxxxxxxxx)
      Operation System Info: Linux(3.10.0-327.ali2019.alios7.x86_64)_x86_64
      User Input Begin Time: 2024-02-2219:26:47
        User Input End Time: 2024-06-2219:27:07
        Analysis Begin Time: 2024-06-1318:26:41
          Analysis End Time: 2024-06-1414:54:29
              Elapsed Time: 73668
              Num of Sample: 14806
    Average Active Sessions: 0.20
    </pre><ul id=110></ul><a class="ash_html" name='Top Active Tenants'></a>          <h2 class="ash_html">Top Active Tenants<br></h2><ul><li class='ash_html'>this section lists top active tenant information</li>
    <li class='ash_html'>Total Count: num of records during ash report analysis time period</li>
    <li class='ash_html'>Wait Event Count: num of records when session is on wait event</li>
    <li class='ash_html'>On CPU Count: num of records when session is on cpu</li>
    <li class='ash_html'>Avg Active Sessions: average active sessions during ash report analysis time period</li>
    <li class='ash_html'>% Activity: activity(cpu + wait) percentage for given tenant</li>
  

(Optional) View the report information on the local machine.

You can view the report information in TXT file or HTML page format. Here, we use the HTML page format as an example.

1. Log in to the physical server.

   [admin@xxxxxx.eu95sqa /home/admin]
   

2. Create an ASH report script file named ash_report.html on the OBServer node.

   [admin@xxxxxx.eu95sqa /home/admin]
   $vim ash_report.html
   

3. Generate the script content.

   set serveroutput on;
   tee ash_report.html
   CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT(        TO_DATE('2024-02-22 19:26:47', 'YYYY-MM-DD HH24:MI:SS'),        TO_DATE('2024-06-22 19:27:07', 'YYYY-MM-DD HH24:MI:SS'), REPORT_TYPE=> 'HTML'   );
   notee
   

4. Log in to the database and run the ash_report.html script.

   $ obclient -h172.xx.xxx.xxx -Pxxxx -usys@oracle -pxxxx -A
   

   obclient [SYS]> source ash_report.html;
   

   After the script is successfully executed, the HTML report content will be displayed on the console.

5. Exit the database and go to the physical server to find the ash_report.html file.

   [admin@k08j13249.eu95sqa /home/admin]
   $ls
   ALTER                              ash_report.html
   

   The path of the ash_report.html file is found to be home/admin/ash_report.html.

6. Export the file from the physical server to the local machine.

   1. Open a new shell window and go to the directory where you want to save the file.

      PS C:\Users\xxx\Desktop\ASH>
      

   2. Use the scp command to import the ash_report.html file from the physical server to the local machine.

      scp admin@xx.xx.xx.xx:/home/admin/ash_report.html  /
      

   3. Open the ash_report.html file in the local directory to view the report information.