HomeMogDBMogDB StackUqbar
Ecological Tools
Doc Tools
中文
v5.1

Documentation:v5.1

Supported Versions:
Current(v5.1)/
v5.0/
v4.1/
v4.0

SCA Result

The SCA result includes:

  • Collection result: The data collection result in the source database (Oracle) will be automatically packaged into a ZIP file, and a clear file position prompt will be provided at the end of the collection.
  • Analysis result: An analysis report is generated after the analysis is complete in the target MogDB/openGauss database. The report is stored in an independent folder, in the offline HTML format, and can be copied for reading at will.

Collection Result

The data collection result is automatically packaged into a ZIP file and stored in the current directory of the program by default.

The collection result prompts the following information:

2022-02-15 19:20:40.301126 INFO [runMe.py:356] +==================== [ Summary Information ] ====================+
2022-02-15 19:20:40.301184 INFO [runMe.py:357] | Task Name                                  File Name  File Size |
2022-02-15 19:20:40.301222 INFO [runMe.py:358] | --------------------- ------------------------------ ---------- |
2022-02-15 19:20:40.301260 INFO [runMe.py:360] | SCA_SESSION_SQL              sca_sql_information.dat    3.65 KB |
2022-02-15 19:20:40.301294 INFO [runMe.py:360] | SCA_SESSION_SQL_PERF         sca_sql_performance.dat    3.29 KB |
2022-02-15 19:20:40.301326 INFO [runMe.py:360] | SCA_MYSQL_USER_HOST          sca_mysql_user_host.dat    1815  B |
2022-02-15 19:20:40.301357 INFO [runMe.py:360] | SCA_DATABASE                        sca_database.dat     163  B |
2022-02-15 19:20:40.301387 INFO [runMe.py:361] +=================================================================+

 >>> Final Result is:
 >>> ----------------------------------------------
 >>> /Users/hongyedba/Desktop/SCA_MySQL_test.zip

Analysis Report

Both compatibility analysis and SQL performance simulation will generate an analysis report, which are stored in specified data directories by default. You can also use -r to specify the output directory of the report.

Report Entry

In the analysis report directory, you can click index.html to view the analysis report through the default browser Google Chrome.

img

img

DB Potrait

From SCA Release v5.5.0, we can collect related objects and performance data in Oracle, then make DB potrait shown in SCA HTML report.

In DB Potrait pages, following contents will shown:

  • Basic Information: Including host and database basic information
  • Performance Tendence: DB performance related statistics, including: DB time, CPU time, connections, TPS, QPS, Redo Size, Logical Read, Physical Read and so on.
  • Object Statistics: Object related statistics, including: object type statistics, non-stardard/key object list, Big segment statistics and so on.
  • Table Statistics: Table related statistics, including: table type statistics, table rows statistics, column count distribution, char length distribution, special column statistics, non-stardard/key column list, Lob size distribution, DML frequency distribution and so on.
  • Partition Statistics: Seperate partition statistics from table, including: partition type statistics, partition count statistics and so on.
  • Index Statistics: Index related statistics, including: index type statistics, table indexes statistics, index columns statistics and so on.
  • Constraint Statistics: Constraint related statistics, including: constraint type statistics, No PK/UK table statistics and so on.
  • PL/SQL Statistics: User-defined PL/SQL related statistics, including: PL/SQL rows statistics, Special SQL in PL/SQL statistics and so on.
  • View Statistics: View related statistics, including: View query length statistics, materialized view list and so on.
  • SQL Statistics: User SQL related statistics, including: SQL performance distribution, SQL text length distribution, Special SQL statistics and so on.

img

img

img

Object Compatibility Summary

The object compatibility summary page displays the result of the objects compatibility analysis. The table on the page is summarized by user name, object type, and status, and shows the total number of objects in the relevant categories, as well as the compatibility results.

Note: The content of this table varies slightly from database to database.

img

SQL Compatibility Summary

The SQL compatibility summary page shows the compatibility analysis result. The table lists all SQLs collected from the system and whether they are supported in MogDB by user name, program name, and module name.

Note: The content of this table varies slightly from database to database.

img

SQL Rewrite Rule

The SQL rewrite rule page shows the SQL rewrite rules involved in the analysis.

The usage field shows the trigger situation of a rule.

  • Match indicates the number of rule hits in a SQL.
  • Count indicates the number of SQLs that match a rule.

img

SQL Complexity Distribution

The SQL complexity distribution page shows the SQL complexity distribution. The current judgment standard of complexity distribution is as follows:

  1. The more the number of tables that a SQL involves, the higher the complexity.
  2. The more the number of times for using the connect by syntax in a SQL, the higher the complexity. In this situation, the execution performance problem occurs probably.
  3. The more the number of user-defined functions used in a SQL, the higher the complexity. Because the logic complexity in a user-defined function is not clear, the SQL complexity is high if many user-defined functions are used in a SQL.
  4. The longer the time taken for executing a function in Oracle, the higher the SQL complexity.

The complexity of each SQL is determined according to the above four standards. If the SQL complexity is higher, the SQL execution performance needs to be paid more attention to after migration. Only in this way can service faults be avoided due to performance problems.

img

SQL Performance Comparison (Performance Comparison Summary)

The SQL performance comparison summary page shows the following information:

  1. Basic performance comparison information, basic configuration related to performance comparison, and related thresholds used in comparison
  2. SQL performance is summarized by such dimensions as general, up, down, not supported, and timeout to analyze the impact of all kinds of SQLs on the overall workload.

img

SQL Performance Comparison (Top by Workload/SQL, Timeout)

In SQL performance comparison, the content formats of the Top by Workload, Top by SQL, and Timeout pages are similar. The following uses the Top by Workload page as an example. The table lists 100 SQLs that affect the performance most. The SQL FMS field uses the hyperlink format, which can be clicked to view the SQL analysis details. The SQL performance impact can be assessed from two dimensions:

  1. SQL impact: the proportion of SQLs that has performance impact when single SQL is executed
  2. Load impact: the impact of the current SQL on the performance change of the overall SQL workload and the overall SQL performance in terms of the total number of its execution times.

img

SQL Performance Comparison (SQL Details)

The SQL details page shows the following information:

  1. SQL execution information: SQL execution information in Oracle and MogDB

    Oracle execution information is from the dynamic performance view, and MogDB execution information is from actual SQL execution.

  2. SQL text: SQL execution text in Oracle and actual execution text in MogDB

  3. SQL binding variable: SQL binding variable in Oracle

    The binding variable will be applied to the SQL execution text in MogDB to simulate service execution in MogDB.

  4. Oracle execution plan: SQL execution plan in Oracle, which is from the dynamic performance view

  5. MogDB execution plan: SQL execution plan in MogDB, which is from actual execution.

    The program automatically analyzes the MogDB execution plan and marks out the potential performance problems.

  6. MogDB object information: structures and statistics of SQL-involved objects in MogDB

img

img

img

Supported SQL List

In the root directory where the report is located, the SCA_Data_Report_<db_name>_<data_id>.xlsx file records all supported SQLs involved in the SQL compatibility evaluation, Summary of SQL compatibility, automatic rewriting policies of partial SQLs during the evaluation, and rewritten SQL script.

Excel data report have following sheets:

  1. Cover page: the first page of Excel data report, records basic information: db name, versions, data id, etc.
  2. Summary Page: the second page of Excel data report, records summary data, SQL summary, and summary of rewrite SQLs
  3. Detail Page: the third page of Excel data report, records detail information of all the SQLs
  4. Rewrite pages: the pages starts from forth in the Excel data report，each sheet shows a rewrite rule and related SQLs, the rewritten parts of the SQL are marked in red and bold

The content of the fields in the file varies slightly, mainly depending on the database.

Oracle

  1. user name: specifies the schema of SQLs to be executed. In Oracle, this field refers to the user.
  2. sql type: specifies the SQL type. You need to make it clear whether the SQLs collected are system SQLs or bussiness SQLs. The values are: USER, SYSTEM_CATALOG, SYSTEM_COMMAND.
  3. support category: specifies SQL support category in MogDB: direct support, rewrite support, no support
  4. module: specifies the client module for executing SQL statements.
  5. action: specifies the client action for executing SQL statements.
  6. mogdb error code: specifies the SQL execution error code in MogDB.
  7. mogdb error message: specifies the SQL execution error message in MogDB.
  8. original sql: specifies the original SQL script.
  9. transform rules: specifies the automatic rewriting rules of SQLs.
  10. sql rewrite: specifies the rewritten SQL script.

MySQL

  1. database name (schema): specifies the schema of SQLs to be executed. In MySQL, this field refers to the database.
  2. sql type: specifies the SQL type. You need to make it clear whether the SQLs collected are system SQLs or bussiness SQLs. The values are: USER, SYSTEM_CATALOG, SYSTEM_COMMAND.
  3. support category: specifies SQL support category in MogDB: direct support, rewrite support, no support
  4. user host: specifies the host information of the MySQL client user who executes SQLs and the corresponding execution client.
  5. mysql error code: specifies the SQL execution situation in MySQL. If the execution result is 0, the SQL execution is successful. Otherwise, the execution error is reported.
  6. mogdb error code: specifies the SQL execution error code in MogDB.
  7. mogdb error message: specifies the SQL execution error message in MogDB.
  8. original sql: specifies the original SQL script.
  9. transform rules: specifies the automatic rewriting rules of SQLs.
  10. sql rewrite: specifies the rewritten SQL script.

DB2

  1. schema: specifies the schema of SQLs to be executed.
  2. sql type: specifies the SQL type. You need to make it clear whether the SQLs collected are system SQLs or bussiness SQLs. The values are: USER, SYSTEM_CATALOG, SYSTEM_COMMAND.
  3. support category: specifies SQL support category in MogDB: direct support, rewrite support, no support
  4. statement type: specifies the types of SQL statements recorded in DB2.
  5. mogdb error code: specifies the SQL execution error code in MogDB.
  6. mogdb error message: specifies the SQL execution error message in MogDB.
  7. original sql: specifies the original SQL script.
  8. transform rules: specifies the automatic rewriting rules of SQLs.
  9. sql rewrite: specifies the rewritten SQL script.

PostgreSQL

  1. schema: specifies the schema of SQLs to be executed.
  2. sql type: specifies the SQL type. You need to make it clear whether the SQLs collected are system SQLs or bussiness SQLs. The values are: USER, SYSTEM_CATALOG, SYSTEM_COMMAND.
  3. support category: specifies SQL support category in MogDB: direct support, rewrite support, no support
  4. mogdb error code: specifies the SQL execution error code in MogDB.
  5. mogdb error message: specifies the SQL execution error message in MogDB.
  6. original sql: specifies the original SQL script.
  7. transform rules: specifies the automatic rewriting rules of SQLs.
  8. sql rewrite: specifies the rewritten SQL script.

Informix

  1. user: specifies the user of SQLs to be executed.
  2. sql type: specifies the SQL type. You need to make it clear whether the SQLs collected are system SQLs or bussiness SQLs. The values are: USER, SYSTEM_CATALOG, SYSTEM_COMMAND.
  3. support category: specifies SQL support category in MogDB: direct support, rewrite support, no support
  4. statement type: specifies the types of SQL statements recorded in Informix.
  5. mogdb error code: specifies the SQL execution error code in MogDB.
  6. mogdb error message: specifies the SQL execution error message in MogDB.
  7. original sql: specifies the original SQL script.
  8. transform rules: specifies the automatic rewriting rules of SQLs.
  9. sql rewrite: specifies the rewritten SQL script.
HOME

Issue
Copyright © 2011-2024 www.enmotech.com All rights reserved.