DISPLAY_PROFILE is used to display the performance analysis data of an SQL execution plan, allowing you to view the operator-level diagnostic information for the execution corresponding to the specified trace_id.
Syntax
DBMS_XPROFILE.DISPLAY_PROFILE(
trace_id varchar2 default null,
tenant_id integer default 0,
format varchar2 default 'AGGREGATED',
level integer default 1,
svr_ip varchar2 default null,
svr_port integer default 0,
op_id integer default null
)
Parameters
Parameter |
Note |
|---|---|
| trace_id | Optional. Specifies the trace ID to display. trace_id The following profile is omitted or filled in. null If you leave this parameter empty or set it to "", the system will automatically select the last trace ID in the current session. |
| tenant_id | Optional. Specifies the tenant ID. If omitted or set to 0, the current tenant ID is used by default. For user tenants other than the system tenant, an empty result will be returned if a tenant ID is specified. |
| format | Optional. Specifies the display format. Valid values:
|
| level | Optional. Specifies the output level. Each metric has its own level. Metrics with a level higher than specified will not be displayed. Valid values:
|
| svr_ip | Optional. Filter to display only the profiles on a specific IP address. Default value: null, which indicates full output. |
| svr_port | Optional. Filters to display profiles on only a specific port. The default value is 0, which indicates to output all profiles. |
| op_id | Optional. Filter to display only the profiles of operators numbered as ID in the plan. Default value is null, which indicates full output. |
Response parameters
The function returns a profile text. The statistics are for a single query corresponding to the specified trace_id, and do not include the average time or historical trend of multiple executions of the same SQL statement.
Vector search
After executing a vector search SQL statement with the MONITOR hint, the returned results will display the Vector Search subtree under the physical operator that actually performed the vector search. This information is used to analyze the overhead in stages such as filtering, index search, and data loading, without altering the query results, execution plan, or Explain output. For procedure details, see "Single Vector Search Performance Analysis" in Monitor and maintain indexes.
Display hierarchy
SQL Physical Operators
Vector Search
Partition
tablet id:200017
Vector Iter
General vector search metrics
Vector Index HNSW Scan / Vector Index SINDI Scan / Vector Index IVF Scan
Metrics of the corresponding algorithm
Vector Search: The root node for performance analysis information of vector search.Partition: The execution instance of a single partition (or data tablet). Thetablet ididentifies the corresponding data table tablet. The same tablet may have multiple instances due to parallel execution, rescan, or query retry, and this does not equal the number of physical partitions of the table.Vector Iter: Displays the filter path, filtering process, index search time, and number of output rows.- Algorithm node: HNSW, HNSW_SQ, and HNSW_BQ are displayed as
Vector Index HNSW Scan; SINDI and SINDI_SQ are displayed asVector Index SINDI Scan; IVF and IVF_PQ are displayed asVector Index IVF Scan.
The preceding node is only used to display performance analysis information and is not a new Explain operator.
General metrics
field |
Meaning |
|---|---|
try path |
The vector execution path initially selected for this execution instance. Subsequent path switches will not overwrite this value. |
current path |
The final effective vector execution path of this execution instance. |
switch reason |
The reason for the last effective runtime path switch in this execution instance. |
pre filter rows scanned |
Cumulative number of rows actually consumed in the pre-filtering stage. |
pre filter time |
The cumulative time consumed in filtering results before actual consumption. |
iter filter row |
Cumulative number of candidate rows actually subjected to scalar evaluation during the candidate filtering phase. |
iter filter matched row count |
Cumulative number of rows filtered by candidate scalars. |
iter filter loop cnt |
The actual number of times that the candidate filtering phase is entered. Each post-filtering iteration counts as 1, and multiple rounds are accumulated by round. |
iter filter time |
The cumulative time spent on candidate filtering and related iterative processing. |
vec index search time |
Cumulative time consumed in retrieving candidate results from the vector index, excluding the time spent on scalar filtering and reading data from the original table based on the candidate results. |
total search time |
Vector Iter The end-to-end cumulative time from the start of execution to the output of the vector search result, including stages such as search preparation, filtering, vector index search, and result processing. |
output row count |
Vector Iter The cumulative number of rows actually output. In a multi-partition query, each partition can separately output its own top K candidates. |
try path and current path represent the initial path and the final path, respectively. Their possible values are as follows:
Value |
Meaning |
|---|---|
| 0 | The path is not determined yet. The path field is usually not displayed when the vector search has not started. |
| 1 | Pre-filtering: First, filter data based on scalar conditions, then perform vector search within the data that meets the conditions. |
| 2 | Iterative filtering: First, obtain a batch of candidate data through vector search, then filter the results based on scalar conditions. If the number of filtered results is insufficient, continue to fetch more candidate data. |
| 3 | Set filtering: First, obtain a set of primary keys or vector IDs that meet the scalar condition, then limit the vector search range to this set. |
| 4 | Post-filtering: First, perform a vector search, then filter the candidate data obtained from this search based on scalar conditions. |
| 5 | No scalar filtering is performed; the vector index search is executed directly. |
switch reason indicates the reason for the last effective runtime path switch. The possible values are as follows:
Value |
Meaning |
|---|---|
| 0 | No actual path switchover was recorded. |
| 1 | The execution time of the pre-filter reaches the system's path switching threshold. |
| 2 | The data matched by the pre-filter accounts for a high proportion, and the benefit of continuing to use the pre-filter is low. Therefore, the system switches the execution path. |
| 3 | The system evaluates that the pre-filtering cost for the IVF series of indexes has reached the path-switching threshold. |
| 4 | The proportion of candidate data retained after iterative filtering is low, reaching the system's path switching threshold. |
| 5 | The IVF series of indexes requires a vector distance calculation volume that exceeds the system's limit during iterative filtering. |
| 6 | In scenarios where the HNSW series index distributes parallel scan tasks by data blocks, it is forced to switch to pre-filtering. |
| 255 | A switchover has occurred, but it has not been classified under any existing category. |
To determine whether a runtime path switch has occurred, the primary criterion should be a non-zero switch reason, rather than simply comparing the initial and final paths. Filter conditions executed by outer SQL operators are not counted repeatedly in the internal metrics of vector search.
Considerations
- The profile displays fields based on the actual execution path. If a field is not displayed, it usually means that the corresponding phase was not executed or the field is not applicable, not necessarily that its value is 0. When deploying both old and new versions, nodes of the older version may also not display newly added fields.
- Time, row count, and execution count are cumulative within the current
Partitionexecution instance. Multiple rounds of candidate expansion may accumulate to the same node. A rescan or query retry may generate a new execution instance. - Multiple execution instances of the same tablet are displayed separately and are not merged by
tablet id. total search timeis the end-to-end duration. The relationships among other time fields may be inclusive, so you cannot simply add up the durations of all phases.- During parallel execution, time is accumulated per thread. The cumulative time consumed by multiple execution instances may exceed the actual time taken by the query.
- When multiple data shards are involved in this vector search, each execution instance may output its own Top K candidates separately. Therefore,
output row countmay not equal the final number of rows returned by the SQL statement. candidate distance cal timemay be included incandidate scan time, so the two cannot be directly added.
HNSW and SINDI metrics
The following fields are shared by both the HNSW series index and the SINDI series index, and are displayed under the Vector Index HNSW Scan and Vector Index SINDI Scan nodes respectively.
field |
Meaning |
|---|---|
filter mode
Note
|
The actual search modes used by HNSW series indexes are as follows:0 represents image search (GRAPH_SEARCH)1 Represents brute-force distance calculation (BRUTE_FORCE) |
vsag query time |
The cumulative time consumed by the vector search engine to find similar vectors in the index, excluding the time spent organizing and returning candidate results. |
vsag query segment cnt |
The actual number of data segments accessed in the vector search. If an execution instance involves multiple search rounds, the number of data segments accessed in each round is cumulative. The same data segment may be counted multiple times, so this value represents the total count. |
active rows cnt |
The number of incremental vectors processed in this vector search. These vectors are located in the active segment and have not yet been merged into the persistent index data segment. |
active scan time |
The cumulative time consumed by Active Segment vector search, excluding the time for sorting and returning candidate results. |
delta_buffer scan time |
From delta_buffer The cumulative time consumed by tables in reading incremental data not yet merged into persistent indexes, excluding the time spent on post-reading data processing and vector data padding. |
index_id scan time |
From index_id The cumulative time consumed in reading index IDs and data visibility information from the table, excluding the time spent on updating memory states after reading. |
complete data total time |
Cumulative total time consumed for supplementing the index data required for the query, including processing time for incremental vectors, index visibility information, and index snapshots. This field is not displayed if no data supplementation occurs. |
complete delta_buffer time |
The cumulative time consumed in loading the incremental vectors required for this vector search into memory and making them available for search. |
complete index_id time |
Cumulative time consumed for populating the index IDs and their visibility information required for this vector search. The visibility information determines whether an index entry can be included in the search results. |
complete index_snapshot_data time |
The cumulative time spent on reading, verifying, and loading persistent index snapshots as needed. |
The algorithm node may also display the following scan and snapshot loading metrics:
field |
Meaning |
|---|---|
vec hnsw delta buf scan rows |
Actually Read and Processed delta_buffer The number of valid rows. |
vec hnsw index id scan rows |
Actually read and processed index_id Number of valid rows. |
vec hnsw snapshot scan rows |
Actually Read and Processed index_snapshot_data The number of valid rows. |
vec hnsw deserialize snap time |
Cumulative time spent on deserializing persistent index snapshots. Deserialization refers to the process of restoring a persistent index snapshot into a data structure usable in memory. |
SINDI nodes may also display common fields starting with vec hnsw, which does not indicate that an HNSW search was actually performed. The outer fine-tuning time for HNSW_BQ is not repeatedly displayed on this algorithm node. When SINDI performs a one-time filtering on candidate data, iter filter loop cnt is counted as 1, but this does not mean SINDI supports continuously fetching more candidate data and repeating the filtering when the results are insufficient.
IVF metrics
The following fields are common to IVF series indexes. The profile of an IVF series index displays only the metrics from the actual execution phase of the current vector search. Which specific metrics are displayed depends on the index type and whether the cache is available.
field |
Meaning |
|---|---|
search nlist time |
The time consumed in locating the initial cluster centers and expanding more centers during iterative filtering. |
candidate rows scanned |
The actual number of candidate vector data rows scanned in this vector search. The number of rows scanned across multiple rounds is cumulative. |
candidate scan time |
The cumulative time spent reading and processing candidate vectors from the inverted bucket (the set of vectors corresponding to the same cluster center in the IVF index). |
candidate distance cal time |
The cumulative time consumed in calculating the distance between candidate vectors and search vectors. |
The cache-related metrics are as follows:
field |
Meaning |
|---|---|
centroid cache fallback scan time |
Cluster center cache (IVF_CENTROID_CACHE) is unavailable, the cumulative time spent scanning the cluster center tables. |
centroid cache build time |
The current execution instance is being simultaneously built during the vector search. IVF_CENTROID_CACHE The cumulative time consumed. |
pq centroid cache fallback scan time |
IVF_PQ_CENTROID_CACHE When unavailable, queries roll back the cumulative time spent accessing data in the PQ cluster center. |
pq centroid cache build time |
The current execution instance is being simultaneously built during the vector search. IVF_PQ_CENTROID_CACHE The cumulative time consumed. |
pq precompute cache fallback calc time |
IVF_PQ_PRECOMPUTE_TABLE_CACHE When unavailable, it indicates the cumulative time consumed for calculating the path using the regular PQ distance; this field does not represent the table scan time. |
pq precompute cache build time |
The current query is synchronously building the PQ cluster center cache (IVF_PQ_PRECOMPUTE_TABLE_CACHE) cumulative time consumed. |
map centroid cache fallback scan time |
The current instance is in the IVF_MAP_CENTROID_CACHE The cumulative time spent scanning the entire cluster center table when unavailable. |
The value of candidate rows scanned varies across executions due to factors such as data distribution, cluster centroid selection, and the number of candidate expansion rounds. The Map Centroid Cache is not constructed synchronously in the query thread, so there is no corresponding field for construction time. Currently, the number of cache hits or the hit rate is not displayed. These fields are not shown when the cache is available, and no synchronous construction or rollback has occurred.
Query retry information
When a vector search SQL returns OB_SCHEMA_EAGAIN(-5627) and triggers an SQL retry, the failed execution instance displays the vec retry reason on the node that directly triggered the retry. This field may appear on the Vector Iter or the corresponding algorithm node. Successful execution instances do not display this field.
Value |
Meaning |
|---|---|
| 1 | The vector index status was updated later than the query snapshot, so the index status needs to be re-obtained and the query executed again. |
| 2 | The index memory data for other queries has been reloaded. They need to retrieve the status again and execute the query. |
| 3 | The current query has refreshed the index memory data, and the query needs to be executed again. |
| 4 | The version of the index visibility information is later than that of the query or context, and the current snapshot cannot continue to use this information. |
| 5 | The index visibility information has been replaced during the operation and needs to be re-obtained. |
| 6 | The index snapshot indicates that the current query is not visible, as the asynchronous index status has not yet met the query conditions. |
This field is not displayed when no retries have occurred and is not output as 0. 255 is a reserved value and is not currently output.
Parallel execution of hybrid search
After parallel hybrid search is enabled, the returned results may include the following information:
Note
Information about adaptive DOP (Auto DOP) is supported starting from V4.6.2.1.
Output information |
Note |
|---|---|
auto dop cpu pressure pct |
CPU pressure of the tenant used for Auto DOP decision-making; may not exist before the first full sampling window. |
auto dop load cap |
The upper limit of the query target parallelism specified by the CPU load level |
auto dop target |
Query Target Parallelism Before Physical Split |
auto dop actual |
Number of Query Slices Obtained After Physical Sharding |
parallel task count |
Total WorkUnits Generated by This Fusion |
Examples
Viewing the profiles of other tenants in the sys tenant.
obclient> SELECT DBMS_XPROFILE.DISPLAY_PROFILE('Y7A8B64586C10-00063BAC30589818-0-0', tenant_id);Notice
Only the sys tenant can query the profiles of other tenants. You must specify the
tenant_id.A user tenant queries its own profile.
obclient> SELECT DBMS_XPROFILE.DISPLAY_PROFILE('Y7A8B64586C10-00063BAC30589818-0-0');
References
- Overview of DBMS_XPROFILE
- For more information about parallel execution of hybrid searches, see the "Optimize performance through intra-partition parallelism" section in Hybrid search.