Viewing Full SQL Statistics Using SQL Explorer

Scenarios

SQL Explorer collects and stores all SQL statements executed on your GaussDB instance. This allows you to review SQL statement metrics, execution details, and other related information.

You can configure SQL Explorer to collect data at the instance level, for example, determine whether to filter out system users, specify the default data retention period, and select the types of SQL statements to be collected.

You can also identify table names in SQL text using table definition query or query the table definition based on the table name, schema name, and database name.

Prerequisites

• You have logged in using an account assigned the gaussdb:instance:listFullSql and gaussdb:instance:operateFullSql actions.
• The login account must be assigned the API actions lts:groups:*, lts:topics:*, and lts:logs:list of Log Tank Server (LTS).
• The full SQL statistics and table definition functions are only available for authorized users.
• LTS is running properly.
• The CCMS service has been connected for managing built-in accounts of LTS.

Constraints

• To enable SQL Explorer, the DB engine version of GaussDB instances must be V2.0-8.0.1 or later, and the kernel version must be 505.0.0 or later.
• The GaussDB instance and all its nodes to be collected must be in the available state.
• Batch collection and reporting of full SQL data to LTS may be delayed by up to 2 minutes and is not real-time. High concurrency may result in some minor data loss.
• You can query full SQL data generated in the past 30 days.
• Collected data will be automatically deleted upon expiration. You can set the retention period according to your business needs, from a minimum of 1 day to a maximum of 30 days. Expired data cannot be queried.
• Enabling SQL Explorer may temporarily affect database performance.
• Enabling SQL Explorer will automatically connect your instance to LTS. All collected SQL records are continuously stored in LTS (100% indexed fields), which will continuously incur costs. For pricing details of LTS, see Product Pricing Details.
• Ensure that the time (the timestamp of UTC-0) of the management plane node is synchronized with that of each instance node. The time difference cannot exceed 10 seconds. Otherwise, the functionality will be affected.
• Before deleting a GaussDB instance, manually disable SQL Explorer. If you do not need the collected full SQL data, log in to LTS and manually delete the corresponding log group or log stream.
• Table definition query is available for instances whose DB engine version is V2.0-3.208.0 or later and kernel version is 503.1.0.SPC1200 or later. For details about how to query the version, see Checking the DB Engine and Kernel Engine Version of a GaussDB Instance.

Enabling SQL Explorer

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > GaussDB.
4. On the Instances page, click the name of the target instance to go to the Basic Information page.
5. Choose Diagnostics and Optimization > SQL Diagnosis.
6. Click the SQL Explorer tab. On the displayed page, click next to Enable SQL Explorer. In the displayed dialog box, configure the parameters for collecting full SQL data.
   Figure 1 Enabling SQL Explorer
   
   • Retention Period of Full SQL Data: Set the number of days for storing collected full SQL data in LTS. Expired data will be automatically deleted. The default value is 7 days. You can set a value from 1 to 30.
   • Filter System Users: Toggle this option on if you want to ignore all SQL statements executed by system users during data collection. It is toggled on by default.
   • LTS Log Group: Select the log group dedicated to intelligent O&M. Each instance is configured independently as required. When SQL Explorer is enabled, a log group is created automatically. After an instance is deleted, if the log group is no longer needed, you can manually delete it on the LTS console. For details, see Deleting a Log Group.
   • LTS Log Stream: Select the log stream dedicated to SQL Explorer. Each instance is configured independently as required. When SQL Explorer is enabled, a log stream is created and configured automatically.
   • SQL Types: Select the types of SQL statements to be collected. You can add custom types with the Custom option.

7. Click OK.

   Wait until the system displays a message indicating that SQL Explorer is enabled.

Viewing Full SQL Statistics

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > GaussDB.
4. On the Instances page, click the name of the target instance to go to the Basic Information page.
5. Choose Diagnostics and Optimization > SQL Diagnosis.
6. On the Full SQL Statistics tab of the SQL Explorer page, click to query statistics of normalized SQL records. By default, data from the last hour is displayed.
   You can adjust search criteria to narrow down results and precisely target specific records. For example, you can modify the time range and combine filters such as SQL text (up to 5 entries), normalized SQL ID, and other extended fields.

   Figure 2 Adding search criteria
   

7. Expand a row to view the aggregated SQL execution records of the specified normalized SQL ID.
   Figure 3 Querying all SQL execution records of a normalized SQL ID
   
   • You can apply secondary filtering in the expanded list to narrow down and accurately locate specific records.
   • A maximum of 1,000 records can be displayed on this page. If the actual number exceeds this limit, narrow your search criteria to locate target records.
   • The current sorting function is based on cached sorting of the records displayed on the page (up to 1,000 records).

8. Click View SQL in the Operation column to view the collected SQL text and the parsed original SQL text.

   When track_stmt_parameter is enabled, variables in the collected SQL text are replaced with their actual values, so you can see the original SQL statement. However, for SQL text collected when track_stmt_parameter is disabled, the original values of the variables cannot be retrieved, and no content will be displayed here. For details about how to modify parameters, see Modifying Instance Parameters.

9. Click More and choose View Details in the Operation column to view other parameter details of full SQL records, including the details about SQL execution on components.
   Figure 4 Querying SQL execution details
   

10. Click More in the Operation and choose Link or End-to-End Trace to view link information of the SQL statement. For more link information, see Performing Full-Link Analysis for a SQL Record.

Viewing Full Data by SQL Statement

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > GaussDB.
4. On the Instances page, click the name of the target instance to go to the Basic Information page.
5. Choose Diagnostics and Optimization > SQL Diagnosis.
6. Click the Full Data by SQL Statement tab on the SQL Explorer page. On the displayed tab, click to query all SQL records in a specified period. By default, data from the last hour is displayed.
   Figure 5 Querying full data by SQL statement
   

   You can adjust search criteria to narrow down results and precisely target specific records. For example, you can modify the time range or apply combined filters such as SQL text and valid DB time.

   • SQL text: You can enter up to five SQL statement snippets at a time.
   • Valid DB time, CPU time, I/O time, and executor execution time: You can specify a time range to filter results.
   Figure 6 Adding search criteria
   

7. Click View SQL in the Operation column to view the collected SQL text and the parsed original SQL text.

   When track_stmt_parameter is enabled, variables in the collected SQL text are replaced with their actual values, so you can see the original SQL statement. However, for SQL text collected when track_stmt_parameter is disabled, the original values of the variables cannot be retrieved, and no content will be displayed here. For details about how to modify parameters, see Modifying Instance Parameters.

8. Click More and choose View Details in the Operation column to view other parameter details of full SQL records, including the details about SQL execution on components.
   Figure 7 Querying SQL execution details
   

9. Click More in the Operation and choose Link or End-to-End Trace to view link information of the SQL statement. For details, see Performing Full-Link Analysis for a SQL Record.

Modifying Default Settings

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > GaussDB.
4. On the Instances page, click the name of the target instance to go to the Basic Information page.
5. Choose Diagnostics and Optimization > SQL Diagnosis.
6. On the SQL Explorer page, click Modify Default Settings. In the displayed dialog box, modify the SQL Explorer configuration parameters.
   Figure 8 Modifying the default settings of SQL Explorer
   

7. Click OK.
8. Wait until the system displays a message indicating that the modification is successful.

Disabling SQL Explorer

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > GaussDB.
4. On the Instances page, click the name of the target instance to go to the Basic Information page.
5. Choose Diagnostics and Optimization > SQL Diagnosis.
6. On the SQL Explorer page, click . In the displayed dialog box, click OK.
7. Wait until the system displays a message indicating that SQL Explorer is disabled.

Creating a SQL Patch

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > GaussDB.
4. On the Instances page, click the name of the target instance to go to the Basic Information page.
5. Choose Diagnostics and Optimization > SQL Diagnosis.
6. On the Full SQL Statistics tab of the SQL Explorer page, click to expand the target row or click the Full Data by SQL Statement tab.
7. Locate a target SQL statement and click SQL Patch in the Operation column. The SQL Patch Details page is displayed.
   • If no SQL patch is created, enter the patch name and content, and click OK to create one.
     Figure 9 SQL patch
     
   • If a SQL patch has been created, the SQL patch information is displayed.
     Figure 10 Viewing SQL patch details
     
     • Status: Click to enable or disable the SQL patch. If a SQL patch is disabled, the status is Disabled. If it is enabled, the status is Available.
     • To delete a SQL patch, click Delete.

Querying Table Definitions

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > GaussDB.
4. On the Instances page, click the name of the target instance to go to the Basic Information page.
5. Choose Diagnostics and Optimization > SQL Diagnosis.
6. On the Full SQL Statistics tab of the SQL Explorer page, click to expand the target row or click the Full Data by SQL Statement tab.
7. In the Operation column of a specified record, click More and choose Table Definition. On the Table Definition page, view information in the Detected Tables area.

   If no table is detected, click Add Table and enter a table name manually. If any detection result is incorrect, click Edit to modify, and then save the result.

8. Click next to a table name to view its table definition.
   Figure 11 Table Definition
   

9. Click the SQL statement in the table definition to view the definition details.
   Figure 12 Table definition details