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',
'REPORT_TYPE',
'SVR_IP',
'SVR_PORT',
'TENANT_ID');
obclient [SYS]> call dbms_workload_repository.ash_report(
BTIME => 'xxxx',
ETIME => 'xxxx',
SQL_ID => 'xxxx',
TRACE_ID => 'xxxx',
WAIT_CLASS => 'xxxx',
REPORT_TYPE => 'xxxx',
SVR_IP => 'xxxx',
SVR_PORT=> 'xxxx',
TENANT_ID => 'xxxx');
The following table describes the parameters.
| Parameter | Description |
|---|---|
| BTIME | The sampling start time. This parameter is required. |
| ETIME | The sampling end time. This parameter is required. |
| SQL_ID | The SQL ID of the SQL statement to be sampled. If you do not specify this parameter or set the value to NULL, the SQL ID of the statement to be sampled is not limited. |
| TRACE_ID | The trace ID of the SQL statement to be sampled. If you do not specify this parameter or set the value to NULL, the trace ID of the SQL statement to be sampled is not limited. |
| WAIT_CLASS | The type of the wait event to be sampled. Specify WAIT_CLASS in accordance with the value of the WAIT_CLASS column in the GV$OB_ACTIVE_SESSION_HISTORY.WAIT_CLASS view. If you do not specify this parameter or set it to NULL, the wait event class is not limited. |
| REPORT_TYPE | The type of the report. At present, the HTML and TEXT types are supported. If you do not specify this parameter or set it to NULL, the TEXT type is used by default. |
| SVR_IP | The IP address of the OBServer node where the ASH report is generated. If you do not specify this parameter or set the value to NULL, the IP address of the OBServer node where the ASH report is generated is not limited. |
| SVR_PORT | The port number of the OBServer node where the ASH report is generated. If you do not specify this parameter or set the value to NULL, the port number of the node where the ASH report is generated is not limited. |
| TENANT_ID | The ID of the tenant for which the ASH report is generated. If you do not specify this parameter or set the value to NULL, the ID of the tenant for which the ASH report is generated is not limited. |
Notice
- The behaviors are as follows when the values of the
SVR_IP,SVR_PORT, andTENANT_IDparameters are not limited in different tenants:- For the
systenant, related data of all IP addresses, ports, and tenants is collected. - For a user tenant, related data of all IP addresses and ports in the tenant is collected.
- For the
- In MySQL mode, if an optional parameter is explicitly set to a value, other optional parameters must be explicitly set to
NULLand cannot be left empty.
You can query the GV$OB_ACTIVE_SESSION_HISTORY view for relevant information in the package. 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 the GV$OB_ACTIVE_SESSION_HISTORY view, see GV$OB_ACTIVE_SESSION_HISTORY (MySQL mode) or GV$OB_ACTIVE_SESSION_HISTORY (Oracle mode).
Procedure
To generate an ASH report, perform the following steps:
Log in to a MySQL tenant of the OceanBase cluster as the administrator of the tenant.
$ obclient -h172.xx.xxx.xxx -Pxxxx -uroot@mysql -pxxxx -ADisplay the ASH report in the console.
Call the ASH PL package and enter the diagnostics start time and end time and report type to generate the report content.
Note
We recommend that you set the report type to
TEXTwhen viewing the report in the console.Display the report content in a TXT file.
The following example sets the diagnostics start time to
2024-02-22 19:26:47, the end time to2024-06-22 19:27:07, and the report type toTEXT. You can also specify other parameters as needed.obclient [test]> CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2023-09-22 10:26:47', '2024-06-22 20:27:07', NULL, NULL, NULL, 'TEXT', NULL, NULL, NULL);Here is a sample of the generated report:
ASH Report Cluster Name: test424 Observer Version: OceanBase 4.2.4.0 (200000342024061210-c4c0c18741e45a1d40889b6147c3132574xxxxxx) Operation System Info: Linux(3.10.0-327.ali2019.alios7.x86_64)_x86_64 User Input Begin Time: 2023-09-22 10:26:47 User Input End Time: 2024-06-22 20:27:07 Analysis Begin Time: 2024-06-13 18:24:34 Analysis End Time: 2024-06-14 16:42:55 Elapsed Time: 80300 Num of Sample: 16433 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| +---------+------------+------------------+-----------------------+-------------------+--------------------+-----------+ | 1002| BACKGROUND| 16401| 1951| 14450| 0.20| 99.81%| | 1002| FOREGROUND| 32| 22| 10| 0.00| 0.20%| +---------+------------+------------------+-----------------------+-------------------+--------------------+-----------+Display the report content in an HTML file.
The following example sets the diagnostics start time to
2024-02-22 19:26:47, the end time to2024-06-22 19:27:07, and the report type toHTML. You can also specify other parameters as needed.obclient [test]>CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2023-09-22 10:26:47', '2024-06-22 20:27:07', NULL, NULL, NULL, 'HTML', NULL, NULL, NULL);Here is a sample of the generated report:
Observer Version: OceanBase 4.2.4.0 (200000342024061210-c4c0c18741e45a1d40889b6147c3132574xxxxxx) Operation System Info: Linux(3.10.0-327.ali2019.alios7.x86_64)_x86_64 User Input Begin Time: 2024-02-22 19:26:47 User Input End Time: 2024-06-22 19:27:07 Analysis Begin Time: 2024-06-13 18:24:34 Analysis End Time: 2024-06-14 17:04:21 Elapsed Time: 81586 Num of Sample: 16814 Average Active Sessions: 0.21 </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 from your local storage.
Save the report as an HTML or TXT file for viewing. The following example shows how to view the report in an HTML file.
Log in to the physical server.
[admin@xxxxxx.eu95sqa /home/admin]Create a script named
ash_report.htmlon the server where the OBServer node resides.[admin@xxxxxx.eu95sqa /home/admin] $vim ash_report_my.htmlGenerate the script content.
tee ash_report_my.html CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2023-09-22 10:26:47', '2024-06-22 20:27:07', NULL, NULL, NULL, 'HTML', NULL, NULL, NULL); noteeLog in to the database and execute the
ash_report_my.htmlscript.$ obclient -h172.xx.xxx.xxx -Pxxxx -uroot@mysql -pxxxx -Aobclient [test]> source ash_report_my.html;The content of the HTML report is displayed after the script is executed.
Log out of the database and query the path of the
ash_report_my.htmlfile from the physical server.[admin@k08j13249.eu95sqa /home/admin] $ls ALTER ash_report_my.htmlThe query result shows that the path of the
ash_report_my.htmlfile ishome/admin/ash_report_my.html.Export the file from the physical server to your local server.
Open a new shell and go to the directory where you want to save the file.
PS C:\Users\xxx\Desktop\ASH>Run the
scpcommand to import theash_report_my.htmlfile to your local server from the physical server.scp admin@xx.xx.xx.xx:/home/admin/ash_report_my.html /Open the
ash_report_my.htmlfile in the local directory to view the report.
Log in to an Oracle tenant of the OceanBase cluster as the
SYSuser.$ obclient -h172.xx.xxx.xxx -Pxxxx -usys@oracle -pxxxx -AExecute the
SET SERVEROUTPUT ONstatement to generate the report content.obclient [SYS]> SET SERVEROUTPUT ON;Display the ASH report in the console.
Call the ASH PL package and enter the diagnostics start time and end time and report type to generate the report content.
Note
We recommend that you set the report type to
TEXTwhen viewing the report in the console.Display the report content in a TXT file.
The following example sets the diagnostics start time to
2024-02-22 19:26:47, the end time to2024-06-22 19:27:07, and the report type toTEXT. You can also specify 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 sample 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 in an HTML file.
The following example sets the diagnostics start time to
2024-02-22 19:26:47, the end time to2024-06-22 19:27:07, and the report type toHTML. You can also specify 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 sample 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 from your local storage.
Save the report as an HTML or TXT file for viewing. The following example shows how to view the report in an HTML file.
Log in to the physical server.
[admin@xxxxxx.eu95sqa /home/admin]Create a script named
ash_report.htmlon the server where the OBServer node resides.[admin@xxxxxx.eu95sqa /home/admin] $vim ash_report.htmlGenerate 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' ); noteeLog in to the database and execute the
ash_report.htmlscript.$ obclient -h172.xx.xxx.xxx -Pxxxx -usys@oracle -pxxxx -Aobclient [SYS]> source ash_report.html;The content of the HTML report is displayed after the script is executed.
Log out of the database and query the path of the
ash_report.htmlfile from the physical server.[admin@k08j13249.eu95sqa /home/admin] $ls ALTER ash_report.htmlThe query result shows that the path of the
ash_report.htmlfile ishome/admin/ash_report.html.Export the file from the physical server to your local server.
Open a new shell and go to the directory where you want to save the file.
PS C:\Users\xxx\Desktop\ASH>Run the
scpcommand to import theash_report.htmlfile to your local server from the physical server.scp admin@xx.xx.xx.xx:/home/admin/ash_report.html /Open the
ash_report.htmlfile in the local directory to view the report.