This topic describes how to use OceanBase Diagnostic Tool (obdiag) that is independently deployed to display the information about top SQL statements in a cluster in a terminal.
Syntax
obdiag display scene run --scene=observer.topsql [options]
The following table describes the options.
Option |
Required |
Data type |
Default value |
Description |
|---|---|---|---|---|
| -c | No | string | ~/.obdiag/config.yml |
The path of the configuration file. |
| --inner_config | No | string | Empty | The configurations of obdiag. |
| --config | No | string | Empty | The configurations of the cluster diagnosed by obdiag, in the format of --config key1=value1 --config key2=value2.
NoteFor information about the parameters supported by this option, see Configure obdiag. |
| --env | Yes | string | Empty | Additional parameters required for the obdiag display command, in the format of --env tenant_name=test --env mtime=10. |
Note
In the
--envoption,tenant_name=testspecifies the name of the business tenant whose top SQL statements are to be displayed.In the
--envoption,mtimeindicates the recent period for which top SQL statements are to be queried, in minutes.
Examples
Method 1: Use the command out-of-the-box without a configuration file
# The --env options are passed to specify the target tenant for the query and to specify querying top SQL statements within the last 10 minutes.
$ obdiag display scene run --scene=observer.topsql --env tenant_name=test --env mtime=10 \
--config db_host=xx.xx.xx.xx \
--config db_port=xxxx \
--config tenant_sys.user=root@sys \
--config tenant_sys.password=***
Method 2: Use the command with a configuration file
Before you use this command, make sure that you have configured the login information of the target nodes in the config.yml configuration file of obdiag. For more information, see Configure obdiag.
# The --env option is passed to specify the target tenant for the query.
obdiag display scene run --scene=observer.topsql --env tenant_name=test --env mtime=10
Results
After the command is executed, the execution result is displayed in the current terminal.
$ obdiag display scene run --scene=observer.topsql --env tenant_name=sys --env mtime=10
display_scenes_run start ...
execute tasks: observer.topsql
[obdiag display]: Top SQL time consumption in the last 10 minutes
+-------------+----------------------------------+----------------------+----------+-------------------+-------------------+-----------------+--------------------+--------------------+
| tenant_name | SQL_ID | query_sql | count(1) | avg(ELAPSED_TIME) | avg(EXECUTE_TIME) | avg(QUEUE_TIME) | avg(AFFECTED_ROWS) | avg(GET_PLAN_TIME) |
+-------------+----------------------------------+----------------------+----------+-------------------+-------------------+-----------------+--------------------+--------------------+
| sys | 549981E00A3D95F03DD5A9EF6044AA20 | SHOW DATABASES | 494 | 2597.0445 | 160.7753 | 15.7206 | 0.0000 | 2393.9534 |
| sys | 24D486F20861EBD9E0217692DB0E9C61 | update __all_we | 1980 | 322.5197 | 249.1025 | 0.0000 | 1.0000 | 73.4172 |
| sys | 1532BA78C664771E7113567D8E951B51 | SELECT FIELD FROM `o | 393 | 766.3639 | 736.8550 | 0.0000 | 0.0000 | 29.5089 |
| sys | B6F2A13C4C81145FFAA2F2A5CF9587A9 | select /*+query_time | 133 | 857.4135 | 731.4436 | 0.0000 | 0.0000 | 125.9699 |
| sys | 735537F7B5DB7C4E0E946C9B26108560 | SELECT * FROM __all_ | 397 | 160.9194 | 99.8866 | 0.0000 | 0.0000 | 61.0327 |
| sys | 91FD35069BC21287826257AFF83A850F | select a.tenant_id a | 397 | 151.4584 | 99.4584 | 0.0000 | 0.0000 | 52.0000 |
| sys | 32AB97A0126F566064F84DDDF4936F82 | SELECT * FROM __all_ | 397 | 143.4559 | 91.8363 | 0.0000 | 0.0000 | 51.6196 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 395 | 106.7696 | 56.3949 | 11.0101 | 0.0000 | 28.0734 |
| sys | 1D0BA376E273B9D622641124D8C59264 | COMMIT | 380 | 100.3184 | 81.4342 | 2.6395 | 0.0000 | 15.2474 |
| sys | 95C9A6346A2B1546D243358F32684FAB | SELECT *, time_to_us | 278 | 109.2770 | 70.1223 | 0.0000 | 0.0000 | 39.1547 |
| sys | 91C89692ADAF4C644A7BE9ABF335152F | SELECT * FROM __all_ | 199 | 148.9497 | 75.0151 | 0.0000 | 0.0000 | 73.9347 |
| sys | 3468E7114C438006DD0574C9657EF3BF | select * from __all_ | 160 | 158.3938 | 73.3938 | 0.0000 | 0.0000 | 85.0000 |
| sys | 7E63F1D55520F7F0FAC4F1CD0DDC928C | UPDATE `task_maintai | 99 | 242.0808 | 173.3636 | 9.1616 | 1.0000 | 40.4141 |
| sys | 858015B92843B43D99CA77541C4C2592 | SELECT VALUE FROM __ | 79 | 295.7468 | 202.9114 | 0.0000 | 0.0000 | 92.8354 |
| sys | 4BC4AF5CEA94EC33ABCDA867BCA6F702 | select * from __all_ | 198 | 114.2929 | 54.9141 | 0.0000 | 0.0000 | 59.3788 |
| sys | A5CC9FA9C8AAB2A2A93A722831E3FE6C | SELECT * FROM __all_ | 133 | 157.6842 | 78.9323 | 0.0000 | 0.0000 | 78.7519 |
| sys | 17605A1DA6B6A2150E9FBCA5D4C7653A | SELECT row_id, colum | 134 | 144.5149 | 119.9851 | 0.0000 | 0.0000 | 24.5299 |
| sys | ABBD34D4E1A757B0EC70910F5E10E2BC | select /*+ workload_ | 1 | 18895.0000 | 15633.0000 | 0.0000 | 0.0000 | 3262.0000 |
| sys | 69D4EC82CE11CB6BA481591FD96467E2 | SELECT value from oc | 119 | 155.7479 | 88.3529 | 0.0000 | 0.0000 | 67.3950 |
| sys | 1B5159E5A7962D8F5A091276DFAB5B9F | INSERT /*+ WORKLOAD_ | 1 | 18137.0000 | 17774.0000 | 0.0000 | 0.0000 | 363.0000 |
+-------------+----------------------------------+----------------------+----------+-------------------+-------------------+-----------------+--------------------+--------------------+
[obdiag display]: Top-N SQL queries ranked by request count in the last 10 minutes
+-------------+----------------------------------+----------------------+------+-----------+
| tenant_name | SQL_ID | query_sql | QPS | RT |
+-------------+----------------------------------+----------------------+------+-----------+
| sys | 24D486F20861EBD9E0217692DB0E9C61 | update __all_we | 1980 | 322.5197 |
| sys | 549981E00A3D95F03DD5A9EF6044AA20 | SHOW DATABASES | 494 | 2597.0445 |
| sys | 735537F7B5DB7C4E0E946C9B26108560 | SELECT * FROM __all_ | 397 | 160.9194 |
| sys | 32AB97A0126F566064F84DDDF4936F82 | SELECT * FROM __all_ | 397 | 143.4559 |
| sys | 91FD35069BC21287826257AFF83A850F | select a.tenant_id a | 397 | 151.4584 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 395 | 106.7696 |
| sys | 1532BA78C664771E7113567D8E951B51 | SELECT FIELD FROM `o | 393 | 766.3639 |
| sys | 1D0BA376E273B9D622641124D8C59264 | COMMIT | 380 | 100.3184 |
| sys | 95C9A6346A2B1546D243358F32684FAB | SELECT *, time_to_us | 278 | 109.2770 |
| sys | 7F33FD22651F99E8AB2BAC5428623BCD | START TRANSACTION WI | 201 | 35.5622 |
| sys | 1032E4307F4B68B0548C5016BF927151 | SELECT job_id FROM _ | 200 | 54.5650 |
| sys | 91C89692ADAF4C644A7BE9ABF335152F | SELECT * FROM __all_ | 199 | 148.9497 |
| sys | FA5F24F77A8CD9A570B277492D6FD7C4 | SELECT zone FROM __a | 199 | 70.7337 |
| sys | 31F2D49F8660DCD961B5C66C4DFB1B07 | select distinct(zone | 199 | 87.7588 |
| sys | E784E48A4BE516FCBBF5792E1F1ABBBA | select * from __all_ | 198 | 63.7879 |
| sys | 4BC4AF5CEA94EC33ABCDA867BCA6F702 | select * from __all_ | 198 | 114.2929 |
| sys | B7A6FA97FEC98C06F9586D23935AC4C6 | START TRANSACTION | 179 | 77.4190 |
| sys | 3468E7114C438006DD0574C9657EF3BF | select * from __all_ | 160 | 158.3938 |
| sys | 17605A1DA6B6A2150E9FBCA5D4C7653A | SELECT row_id, colum | 134 | 144.5149 |
| sys | A5CC9FA9C8AAB2A2A93A722831E3FE6C | SELECT * FROM __all_ | 133 | 157.6842 |
+-------------+----------------------------------+----------------------+------+-----------+
[obdiag display]: The SQL that consumes the most CPU among all SQLs in the last 10 minutes
+-------------+----------------------------------+----------------------+----------+------+--------------------+-------------------+
| tenant_name | sql_id | query_sql | cpu_time | cnt | avg(get_plan_time) | avg(execute_time) |
+-------------+----------------------------------+----------------------+----------+------+--------------------+-------------------+
| sys | 549981E00A3D95F03DD5A9EF6044AA20 | SHOW DATABASES | 1275174 | 494 | 2393.9534 | 160.7753 |
| sys | 24D486F20861EBD9E0217692DB0E9C61 | update __all_we | 638589 | 1980 | 73.4172 | 249.1025 |
| sys | 1532BA78C664771E7113567D8E951B51 | SELECT FIELD FROM `o | 301181 | 393 | 29.5089 | 736.8550 |
| sys | B6F2A13C4C81145FFAA2F2A5CF9587A9 | select /*+query_time | 114036 | 133 | 125.9699 | 731.4436 |
| sys | 735537F7B5DB7C4E0E946C9B26108560 | SELECT * FROM __all_ | 63885 | 397 | 61.0327 | 99.8866 |
| sys | 91FD35069BC21287826257AFF83A850F | select a.tenant_id a | 60129 | 397 | 52.0000 | 99.4584 |
| sys | 32AB97A0126F566064F84DDDF4936F82 | SELECT * FROM __all_ | 56952 | 397 | 51.6196 | 91.8363 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 37825 | 395 | 28.0734 | 56.3949 |
| sys | 1D0BA376E273B9D622641124D8C59264 | COMMIT | 37118 | 380 | 15.2474 | 81.4342 |
| sys | 95C9A6346A2B1546D243358F32684FAB | SELECT *, time_to_us | 30379 | 278 | 39.1547 | 70.1223 |
| sys | 91C89692ADAF4C644A7BE9ABF335152F | SELECT * FROM __all_ | 29641 | 199 | 73.9347 | 75.0151 |
| sys | 3468E7114C438006DD0574C9657EF3BF | select * from __all_ | 25343 | 160 | 85.0000 | 73.3938 |
| sys | 858015B92843B43D99CA77541C4C2592 | SELECT VALUE FROM __ | 23364 | 79 | 92.8354 | 202.9114 |
| sys | 7E63F1D55520F7F0FAC4F1CD0DDC928C | UPDATE `task_maintai | 23059 | 99 | 40.4141 | 173.3636 |
| sys | 4BC4AF5CEA94EC33ABCDA867BCA6F702 | select * from __all_ | 22630 | 198 | 59.3788 | 54.9141 |
| sys | A5CC9FA9C8AAB2A2A93A722831E3FE6C | SELECT * FROM __all_ | 20972 | 133 | 78.7519 | 78.9323 |
| sys | 17605A1DA6B6A2150E9FBCA5D4C7653A | SELECT row_id, colum | 19365 | 134 | 24.5299 | 119.9851 |
| sys | ABBD34D4E1A757B0EC70910F5E10E2BC | select /*+ workload_ | 18895 | 1 | 3262.0000 | 15633.0000 |
| sys | 69D4EC82CE11CB6BA481591FD96467E2 | SELECT value from oc | 18534 | 119 | 67.3950 | 88.3529 |
| sys | 1B5159E5A7962D8F5A091276DFAB5B9F | INSERT /*+ WORKLOAD_ | 18137 | 1 | 363.0000 | 17774.0000 |
+-------------+----------------------------------+----------------------+----------+------+--------------------+-------------------+
[obdiag display]: Check whether there have been a large number of unreasonable remote execution requests for SQL executions in the past 10 minutes
+----------+-----------+
| count(*) | plan_type |
+----------+-----------+
| 997 | 1 |
| 322 | 0 |
+----------+-----------+
[obdiag display]: SQL for querying a full table scan
+-------------+----------------------------------+----------------------+--------------+
| tenant_name | SQL_ID | query_sql | elapsed_time |
+-------------+----------------------------------+----------------------+--------------+
| sys | 60AFB7A8BC31178F580ACBB064AEC077 | select /*+read_consi | 9715 |
| sys | 1C76556BC0AC0497E55B57AAE65A1AA6 | select /*+read_consi | 8930 |
| sys | A434D08FC628433CD3BE1230D0453F84 | select /*+read_consi | 8575 |
| sys | 27035962BF2891B45EF08DD6CCC4D07B | select /*+read_consi | 8425 |
| sys | B7E2B2EB900D06AB1166B0E70925B286 | select /*+read_consi | 7385 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 161 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 156 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 151 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 148 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 147 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 146 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 144 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 141 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 140 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 139 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 139 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 138 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 138 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 137 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 136 |
+-------------+----------------------------------+----------------------+--------------+
Trace ID: 8907f7a2-acb9-11ef-bd8d-00163e01c7ce