Example of Performance analysis

Introduction

Using IBSurgeon Performance Analysis from cc.ib-aid.com (or, embedded in HQbird), you can generate a performance report from Firebird trace logs.

Such report is a powerful diagnostic tool that captures detailed information about SQL query execution in Firebird databases. This guide explains how to interpret and use trace reports to identify and resolve performance bottlenecks systematically.

1. Performance Report Structure

┌─────────────────────────────────────────┐
│         Performance Report              │
├─────────────────────────────────────────┤
│ 1. Performance Summary Graphs           │
│    ┌────────────────────────┐           │
│    │  Top queries			  │           │
│    │     Top summary 		  │           │
│    │     Top frequency	  │           │
│    │     Durations 		  │           │
│    │     Fetches  		  │           │
│    │     Reads	  		  │           │
│    │     Writes	  		  │           │
│    │  Time Series Chart     │           │
│    │     Durations          │           │
│    │     Count of queries   │           │
│    │     Fetches            │           │
│    │     Reads/Writes       │           │
│    └────────────────────────┘           │
├─────────────────────────────────────────┤
│ 2. Top Queries Analysis                 │
│    ┌────────────────────────┐           │
│    │ Query Rankings         │           │
│    │                        │           │
│    │  By Duration───┐       │           │
│    │                │       │           │
│    │  By Time   ────┤       │           │
│    │  Summary       │       │           │
│    │                │       │           │
│    │  By Plan   ────┤       │           │
│    │  Summary       │       │           │
│    │                │       │           │
│    │  By Frequency ─┤       │           │
│    │                │       │           │
│    │  By Plan    ───┤       │           │
│    │  Frequency     │       │           │
│    │                │       │           │
│    │  By Fetches ───┤       │           │
│    │                │       │           │
│    │  By Reads   ───┤       │           │
│    │                │       │           │
│    │  By Writes  ───┘       │           │
│    │                        │           │
│    └────────────────────────┘           │
├─────────────────────────────────────────┤
│ 3. Process Summary                      │
│    ┌────────────────────────┐           │
│    │ Per Process Stats      │           │
│    │ - Execution counts     │           │
│    │ - Fetches, etc	      │           │
│    │ - Duration metrics     │           │
│    └────────────────────────┘           │
├─────────────────────────────────────────┤
│ 4. Address Summary                      │
│    ┌────────────────────────┐           │
│    │Per Client Address Stats│           │
│    │ - Connection counts    │           │
│    │ - Durations   		  │           │
│    │ - Fetches,etc          │ 	   	  │
│    │ - Process names        │ 		  │
│    └────────────────────────┘           │
└─────────────────────────────────────────┘

Query Details Structure:
┌────────────────────┐
│ Query Information  │
├────────────────────┤
│ - SQL Text         │
│ - Transaction info │
│ - Execution Plan   │
│ - Duration Stats   │
│ - Resource Stats   │
│   * Fetches        │
│   * Reads          │
│   * Writes         │
│   * Marks          │
│ - Client Info      │
└────────────────────┘

The performance report provides a hierarchical view of database activity:

Performance Summary Graphs
- Visual representation of key metrics over time
- Helps identify patterns and anomalies
- Shows resource utilization trends
Top Queries Analysis
- Multiple ranking perspectives for comprehensive analysis
- Each dimension reveals different optimization opportunities
- Detailed statistics for each query including:
  - Duration metrics (min, max, avg, median)
  - Resource consumption
  - Execution patterns
Process Summary
- Groups statistics by executing process
- Helps identify problematic applications
- Shows resource consumption patterns per process
Address Summary
- Groups statistics by client connection
- Reveals load distribution across clients
- Helps identify connection-specific issues

Each section supports performance analysis at different levels:

System-wide patterns (Graphs)
Individual query optimization (Top Queries)
Application-level issues (Process Summary)
Client-level problems (Address Summary)

2. Time Summary and Plan-Summary Analysis

Time Summary aggregates the total execution time for each unique SQL statement pattern. Think of it as a "cost center" report that shows which queries are consuming the most database resources over time.

If queries are not parametrized, i.e., explicitly contain paramaters' values inside the SQL text instead parameters placeholder (:myparam1), it is necessary to use section "Plan-Summary" to identify queries with the top frequency.
Example of non parametrized query: 'SELECT * FROM COUNTRY WHERE COUNTRYID=2'
Example of parametrized query: 'SELECT * FROM COUNTRY WHERE COUNTRYID=:paramid'

Key Components:

Total Time Percentage: Shows what portion of overall database time a query consumes
Frequency Count: How many times the query pattern appears
Resource Metrics: Aggregated fetches, reads, writes, and marks

For example, if there is

Summary: 19.08% (3920272 of 20541791 ms)

This tells us this query pattern is consuming almost 20% of total database time - a significant portion that warrants immediate attention.

2.1. How to Use Time Summary:

First identify queries consuming disproportionate time (they are top 3 of this section - #1, 2, 3)
Compare time consumption against frequency
See average execution time (total time / frequency) in the bottom of query’s section (see below)
Look for patterns where:
- High time + Low frequency = Inefficient individual queries
- High time + High frequency = Potentially inefficient but heavily used queries

3. Frequency Analysis: Frequency and Plan-Frequency

Frequency analysis looks at how often queries run. Think of it like counting how many times a particular road gets used during rush hour.

If queries are not parametrized, i.e., explicitly contain paramaters' values inside the SQL text instead parameters placeholder (:myparam1), it is necessary to use section "Plan-Summary" to identify queries with the top frequency.
Example of non parametrized query: 'SELECT * FROM COUNTRY WHERE COUNTRYID=2'
Example of parametrized query: 'SELECT * FROM COUNTRY WHERE COUNTRYID=:paramid'

3.1. Understanding Frequency Impact

High-frequency queries are like busy intersections - even if each car (query) moves quickly, the sheer volume can cause congestion. This affects:

Database connections (like parking spaces - limited in number)
Network bandwidth (like road capacity)
CPU usage (like traffic controllers getting overwhelmed)

Cache efficiency (like having to repeatedly access the same information)

To estimate impact of high-frequency queries, collect trace with parameter threshold = 0.

3.2. Frequency Impact Categories

Executions/second	Impact Level	Potential Issues
>1000	Critical	Like rush hour traffic - system resources get overwhelmed
100-1000	High	Similar to steady traffic flow - significant but manageable load
10-100	Medium	Like occasional traffic - monitor for patterns
<10	Low	Light traffic - minimal impact unless queries are very slow

Executions/second

Impact Level

Potential Issues

>1000

Critical

Like rush hour traffic - system resources get overwhelmed

100-1000

High

Similar to steady traffic flow - significant but manageable load

10-100

Medium

Like occasional traffic - monitor for patterns

<10

Low

Light traffic - minimal impact unless queries are very slow

High frequency isn’t always bad - if queries are well-optimized, they can run frequently without issues. The key is ensuring they’re as efficient as possible. Practilly, it means that query median execution time for top 3 most frequent queries should be 0 milliseconds (i.e., less than 1ms), and do not exceed 50% of total executions of queries.

3.3. Example Analysis

Let’s examine a real case from our trace report:

Frequency: 4,428 executions (24.43% of total)
Impact: Critical - high volume of VENDAS table queries
Root Cause: Repetitive customer balance checks
Optimization Priority: High

Explanation: This query is running thousands of times, similar to a
busy intersection. Even though each execution might be quick, the
cumulative impact is significant. The application might be checking
balances more often than necessary.

4. Analysis of statistics of top queries in xx-Summary and Frequency sections

When analyzing Firebird trace reports, each query grouping contains detailed aggregate statistics that provide crucial insights into performance patterns. Let’s break down each metric and understand its significance for database optimization.

4.1. Aggregate Statistics Analysis

Let’s examine this example set of statistics:

Total: 4428 items:
Durations: min: 351; max: 3919; avg: 457.70; median: 455.00; sum: 2026710 (20.29%);
Fetches: min: 7135; max: 7168; avg: 7146.86; median: 7147.00; sum: 31646289 (0.75%);
Writes: min: 0; max: 0; avg: 0.00; median: 0.00; sum: 0 (0.00%);
Reads: min: 0; max: 6995; avg: 3.13; median: 0.00; sum: 13856 (8.22%);
Marks: min: 0; max: 0; avg: 0.00; median: 0.00; sum: 0 (0.00%);
From 1 unique addresses: TCPv6:::1 (4428)

4.2. Execution Count Analysis

4.2.1. Total Items

Total: 4428 items

This represents the number of times this particular query pattern was executed during the trace period.

Understanding this number helps you:

Calculate per-execution resource usage
Determine if query caching might be beneficial (or simply run it less frequently)

High execution counts might indicate opportunities for:

Implementing prepared statements (and parametrized) - the same query with the same frequency, when parametrized and prepared for repetitive execution, will require less resources
Adding result caching - caching resulted value for the use during the long operation or even longer, for user’s session, can reduce necessity to frequently execute query
Batching operations - consider to execute query to return or process many records at once, it will eliminate the overhead for executing query (preparation, network transmission, etc).

4.3. Duration Metrics

4.3.1. Duration Components Example

Durations: min: 351; max: 3919; avg: 457.70; median: 455.00; sum: 2026710 (20.29%);

Metric

Value

Significance

Minimum

351ms

Best-case execution time, useful for understanding optimal conditions

Maximum

3919ms

Worst-case execution time, helps identify potential problems

Average

457.70ms

Typical execution time, but can be skewed by outliers

Median

455.00ms

Middle value, often more representative than average for skewed distributions

Sum (%)

2026710 (20.29%)

Total time consumed and percentage of overall trace duration

4.3.2. Duration Analysis

Close median and average (457.70 vs 455.00) suggests consistent performance
Max/min ratio (~11x) indicates some variability
20.29% of total time is significant - is this query in the top 3 in Frequency or Plan-Frequency section?

4.4. Resource Usage Metrics

4.4.1. Fetch Operations

Fetches: min: 7135; max: 7168; avg: 7146.86; median: 7147.00; sum: 31646289 (0.75%);

Fetches represent row retrievals:

Consistent fetch counts (min/max difference of only 33) suggest stable result sets
Relatively high fetch counts (>7000 per execution) might indicate:
- Need for result set limiting and/or pagination, if there are many records returned.
- Potential for query optimization - especially makes sense if query is in top 3 of Frequency/Plan-Frequency.

4.5. Read Operations

Reads: min: 0; max: 6995; avg: 3.13; median: 0.00; sum: 13856 (8.22%);

Physical reads indicate disk access:

Zero median with non-zero maximum suggests occasional cache misses
8.22% of total reads indicates moderate I/O impact
Large gap between min (0) and max (6995) suggests variable cache effectiveness

4.6. Write Operations

Writes: min: 0; max: 0; avg: 0.00; median: 0.00; sum: 0 (0.00%);

If query performs no writes, usually it’s a read-only operation.

4.7. Mark Operations

Marks: min: 0; max: 0; avg: 0.00; median: 0.00; sum: 0 (0.00%);

Mark operations relate to data pages cache management:

Zero marks indicate no data page was marked for flushing, common for simple SELECT queries
Non-zero marks operation with cache

4.8. Client Connection Analysis

From 1 unique addresses: TCPv6:::1 (4428)

This shows query source distribution:

Single client address suggests application-specific query
Local connection (::1 is IPv6 localhost)
All 4428 executions from same source

4.9. Using These Metrics for Optimization

4.9.1. Performance Pattern Analysis

Execution Consistency

Compare min/max durations
Look for outliers in resource usage
Check median vs average for variability

Resource Usage Patterns

High fetches → Review result set size
High reads → Check index coverage
High marks → Examine lock contention

Client Impact Analysis

Multiple clients → Connection pool sizing
Single client → Application optimization

4.9.2. Optimization Priorities

Based on these metrics, prioritize:

Result Set Size
- 7000 fetches per execution
- Consider adding LIMIT/OFFSET
- Review SELECT column list

Caching Strategy

Frequent execution (4428 times)
Consistent result size

No writes involved

Probably, this query can be executed less frequently.

Index Usage

Variable read counts
Zero median reads but high maximum
Review index coverage

5. Practical Application

For this specific example:

Short-term Improvements:

Implement result caching (high execution count, consistent fetches)
Review result set size (>7000 fetches per execution)

Medium-term Optimization:

Analyze index usage patterns
Consider prepared statement usage
Review application logic for execution frequency

Long-term Considerations:

Monitor execution patterns over time
Plan index maintenance strategy
Consider data access pattern changes

Remember that these metrics should be analyzed together, not in isolation. A high number in one category might be acceptable if other metrics are optimal. This comprehensive understanding of trace metrics enables informed decision-making for database optimization strategies.

6. 3. Duration Analysis

Duration analysis examines how long individual queries take to execute. Think of duration like a stopwatch timing each query - the longer a query takes, the more likely it is to cause performance issues.

6.1. Understanding Duration Metrics

Duration metrics are crucial because they directly affect user experience. Just as customers get frustrated waiting in a long line, users become frustrated when queries take too long to complete. Long-running queries cause:

Poor user experience when screens take too long to load
System resource tied up for extended periods
Other queries waiting in line behind slow ones
Potential timeout issues in applications

6.2. Impact Categories

Duration Range	Impact Level	Recommended Action
>10 seconds	Critical	These queries are like traffic accidents on a highway - they block everything behind them and need immediate attention
1-10 seconds	High	Like yellow traffic lights, these queries are warning signs that need attention soon
100ms-1 second	Medium	Similar to slow-moving traffic, these queries need monitoring but aren’t critical
<100ms	Low	These queries are flowing smoothly and only need attention if they occur very frequently

Duration Range

Impact Level

Recommended Action

>10 seconds

Critical

These queries are like traffic accidents on a highway - they block everything behind them and need immediate attention

1-10 seconds

High

Like yellow traffic lights, these queries are warning signs that need attention soon

100ms-1 second

Medium

Similar to slow-moving traffic, these queries need monitoring but aren’t critical

<100ms

Low

These queries are flowing smoothly and only need attention if they occur very frequently

6.3. Example Analysis

Duration: 77,793ms
Impact: Critical - single query consuming 77.7 seconds
Root Cause: Complex aggregation in PRC_GV_RANKMARCA
Optimization Priority: Immediate

Explanation: This query is taking over a minute to execute, which is like
a complete traffic stoppage. The stored procedure is likely processing
too much data or using inefficient algorithms.

7. Resource Utilization Analysis

Resource utilization is like monitoring different types of vehicles on the road - some need more space and attention than others.

7.1. Understanding Resource Metrics

7.1.1. Fetch Operations

Fetch Count	Impact	Action Required
>1M per query	Severe	Like moving an entire warehouse at once - break it into smaller shipments
100K-1M	High	Similar to a large convoy - needs special handling
10K-100K	Medium	Regular delivery truck - optimize the route
<10K	Low	Small vehicle - efficient but watch for frequency

Fetch Count

Impact

Action Required

>1M per query

Severe

Like moving an entire warehouse at once - break it into smaller shipments

100K-1M

High

Similar to a large convoy - needs special handling

10K-100K

Medium

Regular delivery truck - optimize the route

<10K

Low

Small vehicle - efficient but watch for frequency

High fetch counts often indicate that the query is retrieving more data than necessary. Think of it like ordering an entire catalog when you only need a few items.

Read Operations

Think of read operations like retrieving items from a warehouse. The more organized your warehouse (indexes), the faster you can find what you need.

Read Count	Impact	Action Required
>10K	Severe	Like searching through multiple warehouses without a map - extremely inefficient. Need better organization (indexes).
1K-10K	High	Similar to searching multiple aisles - need better item location system.
100-1K	Medium	Like checking a few shelves - could be optimized but not critical.
<100	Low	Quick, focused retrieval - well-organized system.

Read Count

Impact

Action Required

>10K

Severe

Like searching through multiple warehouses without a map - extremely inefficient. Need better organization (indexes).

1K-10K

High

Similar to searching multiple aisles - need better item location system.

100-1K

Medium

Like checking a few shelves - could be optimized but not critical.

<100

Low

Quick, focused retrieval - well-organized system.

High read counts often indicate missing or inefficient indexes. It’s like having to check every box in a warehouse instead of going directly to the right shelf.

7.2. Example Analysis

Fetches: 31,646,289
Reads: 13,856
Impact: Critical - excessive data retrieval
Root Cause: Missing covering index
Optimization Priority: High

Explanation: This query is retrieving far more data than necessary,
like ordering an entire warehouse's inventory when you only need
specific items. The high number of reads suggests we're checking
too many locations to find our data.

8. Execution Plan Analysis

Think of an execution plan as a delivery route map. Just as a delivery service plans the most efficient route, the database plans the most efficient way to retrieve data.

8.1. Plan Evaluation Criteria

Index Usage Efficiency: Like having organized storage locations
Join Order Optimization: Similar to planning multiple pickup stops
Sort Operations: Like sorting packages at a distribution center
Temporary Space Usage: Storage areas needed during operations

8.2. Common Plan Issues and Solutions

Issue	Impact	Solution
Full Table Scan	Like checking every house on every street to find an address	Add a street directory (index) Improve address system (WHERE clauses)
Sort Operation	Similar to unloading everything to sort packages	Pre-sort items (indexed columns) Reduce sorting needs
Nested Loops	Checking each item against every other item	Create better matching system Use more efficient comparison methods
Index Skip Scan	Like checking every third house because you know the pattern	Improve address organization Make search more precise

Issue

Impact

Solution

Full Table Scan

Like checking every house on every street to find an address

Add a street directory (index) Improve address system (WHERE clauses)

Sort Operation

Similar to unloading everything to sort packages

Pre-sort items (indexed columns) Reduce sorting needs

Nested Loops

Checking each item against every other item

Create better matching system Use more efficient comparison methods

Index Skip Scan

Like checking every third house because you know the pattern

Improve address organization Make search more precise

A good execution plan is like a well-planned delivery route - it gets you to your destination with minimum stops and backtracking.

9. Implementation Strategy

Think of optimization like improving a transportation system - you need to identify problems, plan solutions, and implement changes carefully.

9.1. Prioritization Matrix

This matrix helps you decide what needs attention first, like triaging traffic problems in a city:

Metric	High Impact	Medium Impact	Low Impact
Duration	Traffic jam (>10s)	Slow traffic (1-10s)	Flowing smoothly (<1s)
Frequency	Rush hour (>1000/hr)	Steady traffic (100-1000/hr)	Light traffic (<100/hr)
Fetches	Moving warehouse (>1M)	Large shipment (100K-1M)	Small delivery (<100K)
Reads	City-wide search (>10K)	Neighborhood search (1K-10K)	Street search (<1K)

Metric

High Impact

Medium Impact

Low Impact

Duration

Traffic jam (>10s)

Slow traffic (1-10s)

Flowing smoothly (<1s)

Frequency

Rush hour (>1000/hr)

Steady traffic (100-1000/hr)

Light traffic (<100/hr)

Fetches

Moving warehouse (>1M)

Large shipment (100K-1M)

Small delivery (<100K)

Reads

City-wide search (>10K)

Neighborhood search (1K-10K)

Street search (<1K)

9.2. Step-by-Step Optimization Process

Identify Critical Queries
- Look for the biggest traffic jams (slow queries)
- Find the busiest intersections (high-frequency queries)
- Spot inefficient routes (high resource usage)
Analyze Execution Plans
- Study current routes (index usage)
- Examine traffic patterns (join methods)
- Check bottlenecks (sort operations)
Implement Optimizations
- Build new roads (indexes)
- Redesign routes (restructure queries)
- Add shortcuts (caching)
Verify Improvements
- Measure new traffic flow (new trace report)
- Compare before/after metrics
- Document what worked