Understanding OCI Database with PostgreSQL Performance using Query Insights

Use query insights to gain valuable information about performance and monitoring for tasks such as capacity planning, forecasting, query tuning, and general database administration.

Query insights is a PostgreSQL feature that provides visibility into database query performance. The feature helps you identify long-running or resource-intensive SQL statements and understand session activity over time. You can use these insights for query tuning, capacity planning, forecasting, and daily database administration.

Query insights provides the following capabilities to help you evaluate and manage PostgreSQL performance:

Identify performance bottlenecks by finding SQL statements with high resource usage.
Improve application performance by tuning queries and data models.
Support capacity planning and scaling decisions using recent activity trends.

Note

Enabling query insights uses computing resources and might affect your system's performance.

Enabling Query Insights When Creating a Database System

Create a PostgreSQL database system as described in Creating a Database System.
In the Network Configuration step of the database creation workflow, enable Query insights.
No other configuration is required.
Continue with the database creation.

After the database system is created, you can view its details and select Query insights to open the Query insights page. The page indicates that Query insights is enabled, and you can start using it as described in the following sections.

Enabling Query Insights on an Existing Database System

Open the database system's details page as described in Getting a Database System's Details.
Select Query insights.
The Query insights page opens.
Select the Query insights toggle to enable that feature.
Confirm that you want to enable the feature in the Enable query log dialog box.

The available query insight tools appear.

Disabling Query Insights

Open the database system's details page as described in Getting a Database System's Details.
Select Query insights.
The Query insights page opens.
From the Actions menu, select Disable query log.
Confirm the disabling in the Disable query log dialog box.

Query insight components no longer displays information.

Viewing Average Active Sessions Over Time

Use the Average active sessions over time chart to visualize the average number of active database sessions over different preset time ranges. Active sessions represent sessions actively processing work, such as running on CPU or waiting for a resource.

Hover over the chart to view wait event types and values, such as Extension, IO, and Timeout, at specific times. The chart displays a legend for the different wait event types on the right side.

You can filter the time range from the past 15 minutes up to 7 days, or specify a custom period.

Viewing Top Queries

Use the Top queries list to identify SQL statements that contribute most to database load.

You can filter the following categories to narrow the range of top queries shown:

Database name
Wait event types
DB instance ID
Role

You can sort these categories by highest to lowest or the reverse:

Average active sessions
Query count
Mean execution time (ms)

From the Actions menu (three dots) for each top query, select one of the following tasks:

Copy query: Filter the top queries by name.
Copy DB instance ID: Filter the top queries by database instance ID.

Expand a query to show a circular chart of the wait breakdown by query. This chart displays the distribution of wait events, helping you understand time allocation during query execution. Use this information to identify bottlenecks and improve performance.

Hover over a wait event segment in the chart to see the percentage of time used by that wait event. The legend on the right lists the wait events. To hide a wait event in the chart, select its entry in the legend.