Using AWR metrics for Workload Classification

Part 1

The purpose of this blog is to help customers and colleagues identify candidate Oracle workloads which are a fit for CockroachDB. This can be achieved by reviewing a few statistics captured in an Oracle Automatic Workload Repository report (AWR). Start off by collecting an AWR following the guidelines below:

• Collect reports over one snapshot internal
• Snapshot intervals should be no longer than 60 minutes, ideally in 10 to 15 minutes intervals
• Collect a report per snapshot over a given period of time which represents the workload such as a peak window
• Collect the reports in both html and txt formats
• Collect a report per RAC instance or collect a RAC report

This is a great script for running across snapshots which can produce both html and txt formats. Once you have collected the reports focus on the following sections of the report to understand the workload type. Specifically looking for patterns which match OLTP workloads such as patterns which match

• Singleton lookups and DMLs which would include short ranges queries and transactions
• Contention events
• Read and write sizes . logical versus physical reads
• Types of data scans
• Wait events

While these may not be all the metrics required for workload tuning of Oracle, it is sufficient to qualify the workload for a fit for CockroachDB. Before digging into the above metrics in the AWR let’s take a refresh on CockroachDB workload capabilities. While CockroachDB has a very sophisticated distributed query processing engine, Cost Based Optimizer and Vectorized executions to improve scans for larger rows sets, joins and aggregations it is still limited to how well or fast it can scan larger data sets such as Full segment or Full table scans. Scans are supported but not comparable to performance of parallel scans across partitions, intra-partition parallel scans or Columnar execution such aggregate join index offloading. Here is the Cockroach FAQ,

So you want to be sure workloads are a fit leading to success with the product. Again Cockroach is a great fit for OLTP workloads and OLTP workloads usually display more logical reads, few physical reads, and many user calls, parses, and executes, as well as rollbacks and transactions. While reporting/OLAP environments have fewer, longer transactions that utilize the Work Area, while OLTP environments tend to have numerous small transactions with many commits and rollbacks. Now let’s tackle the AWR metrics to identify the workload pattern. Let’s take another detour and cover some AWR basics. The AWR report provides detailed statistics on wait events, locks, resources usage such as CPU, memory and IO across a given period of time. Collecting a snapshot report over a time period represents a time view of all activity in Oracle. What we care about is the time spent on IO and the types of IO which will give us a close approximation of the type of workload. Under the wait events section, you can observe time spent on the types of IO waits and we care about db file sequential and scattered reads which translates to index scans or full table scans. If index scans are a higher ratio than full scans, the system is more than likely processing small transactions. Let’s look at an example,

Notice User I/O accounts for roughly 40% of all calls across the reports snapshot time and a majority of the file is db file sequential reads which indicates small reads. Next, confirm the assumption by checking the IOstats and SQL stats to confirm the observation. Under the heading “Segment Statisitcs”

You can look at the breakdown IO statistics across object type which can help you confirm the smaller reads are across indexes rather than tables or control files. Next check the IO type types across physical files by checking “IOSTAT by Function and File Type”, to confirm IO’s are going to DATA volumes and the are Buffer gets. The below table indicates, most IO reads are to data files we do however see write IO to TEMP which indicates sorting of data or temp used for SQL refactoring so we will need to check how my SQL statements may be analytical.

After reviewing the above metrics, Focus on the “SQL Statistics” section,

Focusing on TOP sql executions, which will have a hyperlink to the SQL ID sections which includes the complete SQL text

From here, SQL statements and SQL stats you can derive whether the SQL pattern is OLTP or OLAP and confirm early observations based on wait events. Up next I will review in detail and example AWR workload..