Source Workload (Oracle, SQL Server, PostgreSQL, MySQL)

The Workload section shows an analysis that focuses on the workloads detected on the current source.

Using this section helps to understand the nature of the work the source performs. This can be instrumental in selecting what target is most suitable for the migration, as some targets are more suitable for certain workloads.

The section includes the OLTP vs. Analytic Queries, Query Complexity, and Workload Statistics sections. Those sections in turn contain the Database Level Proportion, Access Statistics, Schema Level Proportion Database Level Complexity, and Complexity Statistics.

The information powering the workload analysis for Oracle is based on AWR. For Other RDBMS sources, the information is gleaned from various performance-related queries against source-specific metrics and metadata. Some of the analysis makes use of mMC’s Extended Scan mode.

Workload analysis data should be reviewed in context. The accuracy of the findings is impacted by the available data, varying calculation methods, and the fact that the scan is a point-in-time (or relatively short) sampling strategy. Customers are advised to augment the analysis with other sources of information such as external monitoring domain specific knowledge prior to final target selection.

A tooltip with important caveats appears when hovering over the section title’s Tooltip Hint element. The content of the tooltip changes based on the source and the collection method. It is advisable to review the tooltip content when inspecting workload analysis.

OLTP vs. Analytic Queries Section

The various subsections in this section show an analysis of the workload mix. These reflect whether the source trends toward OLTP-type stores or towards an OLAP / Analytics store.

Database Level Proportion

The Database Level Proportion gauge shows what proportion of workloads seem to be analytic in nature, vs. those that are OLTP. The relative portion is reflected in the position of the gauge, as well as percentage numbers over to the line, to the right and left of the center of the gauge.

Access Statistics

The Access Statistics table lists metrics detected during the scan which contribute to the workload analysis.

The fields show the number of index scans and the number of full table scans detected.

See Limitations below for important notes about these statistics

Schema Level Proportion

The Schema Level Proportion table breaks down the data contributing to the Database Level Proportion by a schema.

For each non-excluded schema, the proportion of queries falling into an OLTP or Analytics category is shown.

This section appears for Oracle and PostgreSQL sources only, since schemas are part of their architecture.

Query Complexity Section

The two subsections in this section attempt to measure the query complexity. The detection is partially based on the measurement of query plans and other stored statistics. A low complexity indicates a shallow or short query plan and a high complexity reflects a longer and more robust plan.

Database Level Complexity

This bar display shows the overall mean complexity across all detected queries. The bar display shows shaded segments that begin green for low complexity and end up red for high complexity.

Database Level Complexity only appears for Oracle and SQL Server sources.

Complexity Statistics

The Complexity Statistics table shows the count of unique queries detected during the scan, and the overall mean execution plan length. These two metrics are components in the assessment of the Database Level Complexity.

The Avg. Execution Palan Length column is shown for Oracle and SQL Server only.

Workload Statistics

The Workload Statistics section shows metrics that can be useful in understanding the load imposed on the current source. These metrics require using the mMC’s Extended Scan mode. This section shows only when sources were scanned using this mode.

The Workload Statistics Section is available only for MySQL and PostgreSQL sources.

See Limitations below for important notes about these statistics

Values shown in this section reflect statistics collected during the extended scan period. All sample points for each measure are considered when aggregating the value for display. The extended scan’s interval marshals the number of samples collected.

MySQL Statistics

For MySQL, the fields shown are:

	Field	Description
1	Avg Query Execution CPU Time Ratio	Measures the percentage of time the CPU was busy with processing a command, relative to the total command execution duration. Low ratio may indicate under-provisioned engine resources.
2	Avg Memory Utilization by Queries	The average amount of memory consumed by queries across the sampling periods. This measurement can be used to help determine if source is sized well for the apparent workload.
3	Current Allocated Memory	The average amount of memory being used for SQL command execution (applications), measured in bytes. This measurement does not include other memory usage by the DB engine for its functional operation. If this value is approaching the allocated memory for the whole instance, it may indicate that the queries are unoptimized or that the instance is under-provisioned.
4	BINLOG Size	The average amount of data written to the `binlog` in bytes per second. This measurement can help understand the rate of change in the source database.
5	Max Transaction Age	Measures the maximum transaction wait-time, over the collection period, in seconds. Well optimized workloads are typically associated with small transaction ages. A high value may indicate performance issues
6	Connection Lock Wait Count	Counts the average number of locks taken by commands during the command lifetime. Non-zero or larger counts may indicate either: Long running queries Contention on resources causing concurrent demand for some data. This may benefit from schema optimization or application design that reduces contention.
7	SQL Operations/Sec	Measures the overall SQL operations per second over the collection period. High numbers may indicate large concurrent demand on the database
8	SQL Operation Rows/Sec	Measures the average number of rows per second affected by the current flux of operations. A high value may indicate write-intensive applications, or those that are batch-oriented in nature. A small value might indicate read-oriented behavior. Comparing this to the SQL Operations/Sec measurement can help understand the nature of the workload.

PostgreSQL Statistics

For PostgreSQL, the fields shown are:

	Field	Description
1	Avg Query Execution CPU Time Ratio	Measures the percentage of time the CPU was busy with processing a command, relative to the total command execution duration. Low ratio may indicate under-provisioned engine resources.
2	Current Allocated Memory	Measures the maximum number of bytes used for data blocks in buffer cache, and for sorting for the sessions (work memory). This measure does not include other memory the DB engine may use for other operational purposes.
3	Commit Ratio	The ratio of transactions resulting in commit vs. all resolutions (commit + rollback). A low ratio can indicate resource contention or application design issues preventing transactions from fully resolving. A high ratio is generally associated to a healthy database.
4	Extended Connections Waiting for Lock	Measures the average number of processes currently awaiting a lock. Non-zero or larger counts may indicate either: Long running queries Contention on resources causing concurrent demand for some data. This may benefit from schema optimization or application design that reduces contention. Complex operations requiring large numbers of locks
5	Max Transaction Age	Measures the maximum transaction age in seconds Long-running transactions prevent routine vacuum operations. This may in turn cause a shutdown of the database due to transaction ID (xid) wraparound. If the value is high (more than an hour typically) consider mitigation as described here: https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
6	Insert Row Count/Sec	The average number of rows per second affected by `INSERT` statements. High values indicate write-intensive application while smaller number might indicate read-oriented behavior.
7	Update Row Count/Sec	The average number of rows per second affected by `UPDATE` statements. High values indicate write-intensive application while smaller number might indicate read-oriented behavior.
8	Delete Row Count/Sec	The average number of rows per second affected by `DELETE` statements. High values indicate write-intensive application while smaller number might indicate read-oriented behavior.
9	Queries per second	The average amount of queries per second. Higher values indicate highly concurrent demand.
10	Transactions Per Second	The average number of transactions per second. Higher values indicate highly concurrent demand. These may also indicate some transactions are locked or stuck

Limitations

Metrics shown for the Workload section are based on a combination of:

Instantaneous readings are taken at a certain point in time.
A certain number of samples taken at an interval over a limited period.
Calculations based on indirect metrics.
Certain assumptions regarding how certain metrics can be interpreted as an indicator of workloads.

It is advised that the findings shown in this section be manually validated prior to using them for final target selection. Domain knowledge and extensive monitoring can help paint a more accurate picture of the various workloads the source supports or would need to support in the future.