E-MapReduce:Business Insight

Query latency

Query latency statistics are derived from daily audit data and cover the last seven days, helping you spot performance trends before they affect users.

P99 latency measures the response time within which 99% of requests complete. If P99 latency is elevated, consider adding compute resources or optimizing the queries with the longest execution times.

DB Lock

The DB Lock section monitors lock contention during database access — conditions that arise when multiple transactions compete to read or modify the same data. Prolonged lock waits slow down overall system throughput. Use the lock contention data here to identify which transactions are causing delays and adjust their concurrency accordingly.

SQL query analysis

SQL Query Analysis ranks SQL statements by query time, CPU consumption, and memory consumption. The Slow SQL Top10 view shows the ten statements with the longest execution time on a selected date. To investigate a slow statement further, use the details in Profile Query Analysis. For more information, see Introduction to Query Profile.

The following table describes the main fields.

Parameterized SQL analysis

Parameterized SQL groups similar statements into a single template by replacing literal constants with ? placeholders, preserving the syntax structure while stripping comments and normalizing whitespace.

For example, the following two statements map to a single parameterized form:

• Original SQL:

  SELECT * FROM orders WHERE customer_id=10 AND quantity>20
  SELECT * FROM orders WHERE customer_id=20 AND quantity>100

• Parameterized SQL:

  SELECT * FROM orders WHERE customer_id=? AND quantity>?

Parameterized SQL analysis ranks statement groups by execution frequency, total execution time, execution time variance, CPU consumption, memory consumption, and failure frequency. This lets you:

• Get an overview of the SQL workload on the StarRocks database.

• Prioritize optimization for statements that run frequently, take a long time, or consume large amounts of CPU and memory.

• Use the Coefficient of Variation (CV) of Query Time to detect execution time instability. A high CV for a parameterized SQL group — especially combined with high frequency — indicates potential data skew or resource contention. A CV value close to zero means execution time is stable; a high CV means similar statements are taking significantly different amounts of time, which warrants investigation.

• Track failures using Execution Failure Count to find statements with recurring errors.

Fields for execution time analysis:

Fields for failure analysis:

Too many versions

When import frequency is too high, the Compaction Score can exceed 1,000, causing StarRocks to return a "Too many versions" error. Import Insight identifies the affected tables by analyzing "too many versions" log entries. To resolve this, reduce the concurrency and frequency of imports for those tables.

Top analysis

How it works

A higher cache hit ratio means faster queries, because data is served from local disk rather than remote storage. Cache Insight derives its metrics from Query Profile data. Each table scan in a Query Profile corresponds to a CONNECTOR_SCAN operator, which exposes the following cache-related metrics:

From these six raw metrics, three derived ratios are calculated:

• Cache hit ratio = Cache hit volume / (Cache hit volume + Cache miss volume)

• Local IO time percentage = Local IO time / (Local IO time + Remote IO time)

• Local IO count percentage = Local IO count / (Local IO count + Remote IO count)

Each day's Query Profile records are processed to extract these metrics for all CONNECTOR_SCAN operators, then grouped by the following dimensions:

Table dimension

This section ranks tables by access count, cache hit ratio, cache hit volume, and cache miss volume.

A table that appears in multiple top-20 lists — high access frequency, low cache hit ratio, and high average cache miss volume — is the best candidate for cache prefetching. For example, a table with 6,411 accesses but a cache hit ratio of only 24.2% would benefit significantly from prefetching.

Parameterized SQL dimension

This section ranks parameterized SQL groups by scan count, cache hit ratio, cache hit volume, and cache miss volume.

Use the two views together to prioritize optimization:

• A group ranked seventh by scan count (1,086 scans) with an average query time of 42.5 seconds and a cache hit ratio of 6.5% is a high-priority candidate — it's frequent, slow, and cache-cold.

  

• A group ranked first by average query time (201 seconds) but with only 4 scans is a cold query. Prioritize optimization based on business impact.

  

• Groups with low cache hit ratios and high cache miss volumes: weigh against scan count and average query time before deciding to prefetch.

  

The following table describes the main fields.

SQL dimension

This section ranks individual SQL statements by query time, cache hit ratio, cache hit volume, and cache miss volume. Use it to investigate specific statements with long execution times or poor cache efficiency.

The following table describes the main fields.