Viewing File Details

Scenarios

You can view details about the uploaded SQL files.

Prerequisites

A file has been uploaded.

Procedure

1. Log in to the UGO console.
2. In the navigation pane on the left, choose SQL Audit > Statement Audit. Click the SQL from Files tab.
3. Locate a task and click View Details in the Operation column. The file audit information, index recommendation information, and collected SQL statements are displayed. You can export SQL statements.
4. In the File Audit Information area, you can view the file name, audit status, and data source information.
   Figure 1 File Audit Information
   
   • To modify the task description, click next to Description.
   • Click the value next to Rule Template to view template information.

5. Check index recommendation information. You can click tabs to view recommended indexes, redundant indexes, and risky SQL statements.
   • Tables: tables with recommended indexes in the uploaded file
   • Recommended Indexes: recommended indexes in all tables in the uploaded file = indexes not found + available indexes + inconsistent indexes + others
   

   • Indexes are recommended only for GaussDB.
   • The index recommendation information is displayed only when Recommend Index is enabled during file audit task creation.
   • Click the Recommended Indexes tab to view indexes recommended based on SQL statements in the uploaded file. The table names, recommended index columns, statuses, and CREATE statements are displayed in the list.
     Figure 2 Recommended index list
     
     

     • The status can be Index available, Index not found, Inconsistent, or Table not found.
       • Index available: There are same indexes in a database.
       • Index not found: Indexes in a database are different.
       • Inconsistent: An index consistent with the first column of the current index is in a database.
       • Table not found: The table with the current index is not in a database.
     • After you select schema and table names from the drop-down lists, the SQL statement of the table is displayed in the SQL list in 6.
     • If no data source is selected during task creation, -- is displayed in the schema drop-down list.
   • Click the Redundant Indexes tab to view redundant indexes based on SQL statements in the uploaded file. Table names, index names, and index fields are displayed in the list.
     Figure 3 Redundant index list
     
     

     • If no data source is selected during task creation or no index is created for the selected data source, no data is displayed in the redundant index list.
     • If no data source is selected during task creation, -- is displayed in the schema drop-down list.
   • Click the Risky SQL tab to view condition columns specified in risky SQL statements in the uploaded file. These condition columns cannot improve query efficiency even if indexes are created for them. Table names and condition columns are displayed.
     Figure 4 Risky SQL list
     
     

     • After you select schema and table names from the drop-down lists, the SQL statement of the table is displayed in the SQL list.
     • If no data source is selected during task creation, -- is displayed in the schema drop-down list.
     • After you select before a sequence number, the SQL statement of the table is displayed in the SQL list in 6.

6. Check the SQL list, including SQL IDs, SQL statements, descriptions, risk levels, and statuses.
   Figure 5 SQL list
   

   Status indicates the audit status of the current statement. The status can be New, Completed, Analysis_error, Error, or Ignore.

   • New: The collected SQL statement is waiting for audit.
   • Completed: The audit is complete.
   • Analyze_error: The SQL statement failed to be parsed.
   • Error: There is an exception during the audit.
   • Ignore: The SQL statement cannot be audited, or the size of a single SQL statement exceeds the threshold. The default threshold is 100 KB.

   Parsing failure cause:

   • line:1, position:14, token:table indicates a SQL statement contains table.
   • line:1, position:3, token:<EOF>: indicates a SQL statement is incomplete.

   Export SQL: Select one or more SQL statements to be exported and click Export SQL to export them to a .tar.gz file.

   • By default, all SQL files are exported. You can also select SQL files or set search criteria to export desired SQL files.
   • By default, The exported Excel file contains existing SQL statements is selected. If the existing SQL statements are too large, do not select this option.
   • If the existing SQL statements in the exported file are too large, save them in a separate file whose name will be displayed in the SQL Statement column in the Excel file.
   • The exported Excel file contains a maximum of 5000 records. If there are more than 5000 records, they are saved in multiple Excel files.
   • If table structure information which the SQL statement to be audited depends on contains more than 30,000 characters, the information is exported to a SQL file.

7. Locate a SQL statement and click View Details in the Operation column. The basic information, violated rules, and table structure of the SQL statement are displayed.
   • If multiple SQL statements are collected, you can click Previous or Next.
     Figure 6 Viewing SQL audit details
     
   • Basic Information: The specific SQL statement and description are displayed.
   • Violated Rules: Based on the selected rule template, the system provides the violated rule name, risk level, and suggestions.

     Click the content in the Problem Segment column to highlight the problem segment in the text. To cancel the highlight, click the content again. The PL/SQL code may have multiple question fragments. Click to display all question fragments.

   • Unmatched Rules: The rules related to the source database are not matched because no data source is selected, the data source connection fails, user permissions are insufficient, schemas are incorrectly selected, or a table is not found. The displayed information includes the rule name, risk level, and unmatched reason.
   • Table Structure: Table structure information which the SQL statement to be audited depends on is displayed, including the object name, object type, and owner.
   • Execution Plans: Execution plans of the SELECT, UPDATE, DELETE, and INSERT statements are displayed.
     

     To obtain the table structure, you must have the permissions to read system catalogs and system views.

     • GaussDB system catalogs: tables, pg_class, pg_namespace, relnamespace, pgxc_class, pg_settings, pg_get_viewdef, columns, pg_tables, pg_index, table_constraints, pg_partition, pg_get_tabledef, check_constraints, key_column_usage, pg_get_keywords, gs_package, db_constraints, and adm_ind_columns.
     • MySQL system catalogs: information_schema.tables, information_schema.columns, information_schema.statistics, and information_schema.table_constraints
     • PostgreSQL system catalogs: pg_class, pg_namespace, information_schema, pg_index, pg_inherits, and pg_attribute