> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.
# Virtual Collections
> Virtual collections for monitoring and reporting in DQL.
A number of virtual collections are available in the system namespace to monitor the DQL environment, peer information, and provide statement diagnostics. They exist solely for local inspection and debugging, providing runtime visibility into the current peer's state, active connections, and query execution details.
These collections are local-only and are not synced to other peers or the cloud.
## system:active\_requests
`ACTIVES` is an alias for `ACTIVE_REQUESTS`.
Reports information on currently executing requests. The information is dynamic and will change as request processing takes place.
```sql theme={null}
SELECT * FROM system:active_requests
```
```json theme={null}
{
"_id": "d43c3bc5-2b79-403c-b5dc-5619f731c77b",
"app_id": "baf481c4-58be-46f1-8a93-eedf382dc371",
"featureFlags": "0x3a",
"plan": {
"#operator": "sequence",
"children": [
{
"#operator": "scan",
"#stats": {
"phaseTimes": {
"exec": 2405,
"recv": 392997
}
},
"collection": "active_requests",
"datasource": "system"
}
]
},
"queryType": "select",
"requestType": "SDK",
"state": "executing",
"text": "select * from system:active_requests",
"times": {
"elapsed": 1058291,
"parse": 170228,
"plan": 81813,
"start": "2026-01-06T16:27:23.069+00:00"
}
}
```
The output includes dynamic timing information and the query execution plan which can be useful when diagnosing issues.
Some points to note about the content:
1. When executing in an environment with multiple subscription servers, only data from the servicing server is reported.
2. It is expected to see any query retrieving data from `active_requests` in the output in "executing" state.
3. Operators will record information whilst processing so it is normal to see changes in the content for `active_requests`.
4. Zero values (times, counts) are omitted from the output.
## system:all\_collections
Reports all collections in all namespaces.
```sql theme={null}
SELECT * FROM system:all_collections
```
```json theme={null}
{
"_id": "default:__presence",
"datasource": "default",
"name": "__presence"
}
{
"_id": "system:active_requests",
"alias": "actives",
"datasource": "system",
"name": "active_requests"
}
{
"_id": "system:all_collections",
"datasource": "system",
"name": "all_collections"
}
{
"_id": "system:collections",
"datasource": "system",
"name": "collections"
}
{
"_id": "system:data_sync_info",
"datasource": "system",
"name": "data_sync_info"
}
{
"_id": "system:dual",
"datasource": "system",
"name": "dual"
}
{
"_id": "system:indexes",
"datasource": "system",
"name": "indexes"
}
{
"_id": "default:my_collection",
"datasource": "default",
"name": "my_collection"
}
{
"_id": "system:request_history",
"datasource": "system",
"name": "request_history"
}
{
"_id": "system:system_info",
"datasource": "system",
"name": "system_info"
}
{
"_id": "default:t1",
"datasource": "default",
"name": "t1"
}
{
"_id": "system:transports_info",
"datasource": "system",
"name": "transports_info"
}
{
"_id": "system:vitals",
"datasource": "system",
"name": "vitals"
}
```
## system:collections
Reports all collections in the `default` namespace.
```sql theme={null}
SELECT * FROM system:collections
```
```json theme={null}
{
"_id": "default:t1",
"datasource": "default",
"name": "t1"
}
{
"_id": "default:__presence",
"datasource": "default",
"name": "__presence"
}
{
"_id": "default:my_collection",
"datasource": "default",
"name": "my_collection"
}
```
## system:data\_sync\_info
Reports statistics related to data sync operations.
```sql theme={null}
SELECT * FROM system:data_sync_info
```
```json theme={null}
{
"_id": "pkAocCgkMCRsRhue6kCR0eIYFYxT3wqVl5-BxJLqCMVgF7H9WOL5I",
"documents": {
"last_update_received_time": -1,
"sync_session_status": "Not Connected",
"synced_up_to_local_commit_id": 545
},
"is_ditto_server": true
}
```
## system:indexes
Reports the user defined indexes (Ditto SDK only).
```sql theme={null}
SELECT * FROM system:indexes
```
```json theme={null}
{
"_id": "my_collection.ix_a",
"collection": "my_collection",
"fields": [
{
"direction": "asc",
"key": [
"a"
]
}
]
}
```
**Format Change in SDK 5.0+**: The index information now includes the `direction` field for each index key, indicating whether the index is sorted in ascending (`"asc"`) or descending (`"desc"`) order. Earlier versions did not include this field.
## system:shared\_statements
Available in SDK version 5.0+
Reports information about statements in the shared statement cache. The shared statement cache stores prepared query plans to avoid re-planning identical queries, improving query performance.
```sql theme={null}
SELECT * FROM system:shared_statements
```
This virtual collection provides visibility into which queries are being cached and can help diagnose query planning behavior.
**Related Configuration:**
* Use the `#reprepare` [directive](/dql/directives) to bypass the cache for specific queries
* Set `dql_default_directives` with `#reprepare` to control caching behavior globally
See [Directives - Default Directives](/dql/directives#default-directives) for more information on controlling statement cache behavior.
## system:dual
Contains a single document with a single field. It can be used to execute and test DQL statements.
```sql theme={null}
SELECT * FROM system:dual
```
```json theme={null}
{
"_id": "1",
"dummy": "X"
}
```
## system:request\_history
Records information about requests that have completed. This is the same basic information as reported by `active_requests`. Information is only recorded when some aspect of the request execution meets the user-configurable qualifiers. (See [configuration](#configuration).)
This is an in-memory cache and is *not* persisted across processes.
```sql theme={null}
SELECT * FROM system:request_history
```
```json theme={null}
{
"_id": "98752fca-f1c7-4970-86ad-127a36712b12",
"app_id": "baf481c4-58be-46f1-8a93-eedf382dc371",
"featureFlags": "0x3a",
"plan": {
"#operator": "sequence",
"children": [
{
"#operator": "indexScan",
"#stats": {
"documentsOut": 100,
"phaseTimes": {
"exec": 1175507,
"recv": 6743617,
"send": 4049802
}
},
"alias": "my_collection",
"collection": "my_collection",
"datasource": "default",
"desc": {
"covering": true,
"index": "ix_a",
"spans": [
[
{
"index_key": {
"direction": "asc",
"include_missing": true,
"key": [
"a"
]
},
"range": {
"high": {
"included": true,
"value": 0
},
"low": {
"included": true,
"value": 0
}
}
}
]
]
}
},
{
"#operator": "filter",
"#stats": {
"documentsIn": 100,
"documentsOut": 100,
"phaseTimes": {
"exec": 449139,
"send": 3478062
}
},
"condition": "(`my_collection`.`a` = 0)"
},
{
"#operator": "groupBy",
"#stats": {
"documentsIn": 100,
"documentsOut": 1,
"phaseTimes": {
"exec": 231392,
"recv": 11864674,
"send": 30878
}
},
"aggregates": [
{
"expr": "true",
"name": "count(true)"
}
],
"keys": []
},
{
"#operator": "projection",
"#stats": {
"documentsIn": 1,
"documentsOut": 1,
"phaseTimes": {
"exec": 8115,
"recv": 12222233,
"send": 33743
}
},
"projections": [
{
"alias": "($1)",
"expression": "count(true)"
}
]
}
]
},
"queryType": "select",
"requestType": "SDK",
"resultCount": 1,
"state": "completed",
"text": "select count(*) from my_collection where a = 0",
"times": {
"elapsed": 14476181,
"parse": 361256,
"plan": 939337,
"start": "2026-01-06T16:33:35.709+00:00"
},
"~qualifier": "threshold"
}
```
The output includes complete timing information and the query execution plan, along with the qualifier that resulted in the request information being captured, all of which can be useful when diagnosing issues.
`request_history` has a configurable maximum number of documents it will keep with oldest being dropped when attempting to add beyond the limit. (See [configuration](#configuration).)
Some points to note about the content:
1. When executing in an environment with multiple subscription servers, only data from the servicing server is reported.
2. There should not be any requests in `request_history` in "executing" state
1. The state should reflect the final state of the request: successful completion or failure.
3. Zero values (times, counts) are omitted from the output.
The cache content may be (selectively) cleared with a `DELETE` statement.
See also: [Diagnosing problems](#diagnosing-problems)
## SYSTEM:SYSTEM\_INFO
Reports a system information dump's contents.
```sql theme={null}
SELECT * FROM system:system_info
```
```text theme={null}
{
"key": "identity_service_metadata",
"namespace": "auth",
"timestamp": 1767716539,
"value": {
"inner": {
"foo": "bar"
},
"user_id": "dev@ditto.live",
"values": [
1,
2,
3
]
}
}
[...]
{
"key": "ditto_sdk_version",
"namespace": "core",
"timestamp": 1767716539,
"value": "0.0.0"
}
[...]
{
"key": "fs_usage_auth",
"namespace": "core",
"timestamp": 1767718398,
"value": 16102
}
[...]
```
## SYSTEM:TRANSPORTS\_INFO
Reports available transports information.
```sql theme={null}
SELECT * FROM system:transports_info
```
```json theme={null}
{
"_id": "discovery_hint",
"value": "Q2CG092BKw"
}
```
## SYSTEM:VITALS
Reports overview statistics about the query engine. It is typically the starting point for general query engine diagnostics.
When executing in an environment with multiple subscription servers, only data from the servicing server is reported.
```sql theme={null}
SELECT * FROM system:vitals
```
```json theme={null}
{
"_id": "1",
"execution_time": {
"max": 5766201,
"mean": 3194134,
"median": 3535687,
"min": 1305173
},
"failed": 1,
"inserts": 11,
"other": 2,
"parse_time": {
"max": 220142,
"mean": 146785,
"median": 166271,
"min": 112400
},
"plan_time": {
"max": 84748,
"mean": 72935,
"median": 73898,
"min": 63048
},
"requests": 31,
"selects": 16,
"updates": 1
}
```
Some points to note about the content:
1. The histograms decay over a 15 minute interval.
2. "other" is all statements not covered by the individual metrics, e.g. `ALTER SYSTEM`
3. All timings are in nanoseconds
## Configuration
Where appropriate, the virtual collections content is determined by configuration settings allowing a user some control over what information is available.
### Request History
The [system:request\_history](#system%3Arequest-history) collection's size is determined by the `DQL_REQUEST_HISTORY_SIZE` system parameter, defaulting to 4096, indicating the number of entries to retain. The larger the size and the larger the statements captured, the greater the memory use.
```sql theme={null}
ALTER SYSTEM SET dql_request_history_size = 5000
```
Which statements qualify for inclusion in the `system:request_history` collection is determined by the `DQL_REQUEST_HISTORY_QUALIFIERS` system parameter which can be set to an object listing all desired qualifiers. Qualifiers not included in the object are removed (or reset).
The following qualifiers can be set:
1. `threshold` - the total execution time in milliseconds over which qualifies the request for inclusion. This is set by default to 1000 (1 second) and is always active. It can be set to 0 to include all statements.
2. `failed` - if a statement's execution fails. `true` by default.
3. `system_delete` - any delete statement run against a `system` namespace collection. `true` by default.
4. `mutation` - if a statement is a mutation of any sort. Not active (`false`) by default.
```sql theme={null}
ALTER SYSTEM SET dql_request_history_qualifiers = { "threshold": 500, "failed": false }
```
## Diagnosing problems
### Profile analysis
The profile (`#stats` elements added to the plan) information permits assessment of where in the execution the time was spent and is therefore useful in directing next steps.
`request_history` entries show how many results the statement and each of its operators processed. This may give insight into what may need to be changed in a statement.
Analysis of the profile from multiple runs of the same statement may highlight things like selectivity-skew in filter values.
Any definition of "high" times should be with respect to the volume of data processed.
Some possible indications from profile analysis include:
1. Stalled execution: If a request appears in the `active_requests` output but doesn't appear to be making any progress it likely indicates an issue with the earliest (apart from `sequence`) operator in the plan.
1. Commonly indicated by unchanging document counts anywhere in the profile.
2. Excessive processing: An operator with a high document count potentially indicates this.
1. Possibly a filter isn't as selective as thought, etc.
2. It may also show where "dam" operators are requiring all prior plan processing to complete before the remainder of the plan can run.
1. e.g. a `GROUP BY` needs all input records before it can begin emitting the groups.
3. Data access issues: Underlying collection access issues may be indicated by high scan operator receive times.
1. High `scan` or `fetch` times are potentially indicative of collection access issues.
2. High index scan receive times are potentially indicative of index access issues.
4. Selectivity issues: Indications of index selectivity as compared to the entire predicate's selectivity may be highlighted with a large number of documents being excluded by the `filter` operator.
1. The difference in document in & out counts for the `filter` operator indicate the filtering taking place.
1. A large amount of filtering may not indicate a problem if there are filters that cannot be applied by index scans.
2. A large amount of filtering may indicate the query would benefit from different or additional indexes.
5. Memory requirements: Document counts for operators that must process the entire prior pipeline - such as sorting and grouping - may give an indication of which statements will have larger memory requirements.
Further:
1. Bottlenecks downstream in a plan may be indicated by a high operator `send` time.
1. This may be observed for any operator and attention should be focused on later operators.
2. Bottlenecks upstream in a plan may be indicated by a high operator `recv` time.
1. This may be observed for any operator and attention should be focused on earlier operators.
3. Possible problems in an operator may be highlighted by a high `exec` time.
1. This ***must*** be considered in conjunction with the number of documents being processed.
2. Operator issues should be reported via support channels.
### SYSTEM:ACTIVE\_REQUESTS & SYSTEM:REQUEST\_HISTORY
When initially developing DQL statements the [explain](/dql/explain) statement is used to determine the execution plan a query will use. Once deployed it may not always be possible to revert to examination of such a plan or there might be concern that the plan has materially changed. The `system:active_requests` and `system:request_history` collections contain[1](#diagnose1) instances of the actual execution plan used by a request, annotated with timing and document count information. These can be used to discover where in the execution a statement is taking time and can highlight things like unexpectedly low selectivity of filters or failure to find a suitable index.
Sometimes it may be possible to execute a statement directly when diagnosing issues. The `PROFILE` keyword appends the `request_history` entry (without having to pass qualification) to the statement's results. This allows dynamic access all the same information as is included in `request_history` without having to capture a live instance of its execution.
```sql DQL theme={null}
PROFILE SELECT make FROM cars WHERE year > 2000
```
```json Output theme={null}
{
"make": "Honda"
}
{
"~request_profile": {
"_id": "da44cc08-4df1-4599-a52f-0d91a6728008",
"app_id": "baf481c4-58be-46f1-8a93-eedf382dc371",
"featureFlags": "0x3a",
"plan": {
"#operator": "sequence",
"children": [
{
"#operator": "scan",
"#stats": {
"documentsOut": 1,
"phaseTimes": {
"exec": 27852,
"recv": 1971169,
"send": 103113
}
},
"alias": "cars",
"collection": "cars",
"datasource": "default"
},
{
"#operator": "filter",
"#stats": {
"documentsIn": 1,
"documentsOut": 1,
"phaseTimes": {
"exec": 36368,
"send": 58399
}
},
"condition": "(`cars`.`year` > 2000)"
},
{
"#operator": "projection",
"#stats": {
"documentsIn": 1,
"documentsOut": 1,
"phaseTimes": {
"exec": 16981,
"recv": 2229883,
"send": 53440
}
},
"projections": [
{
"alias": "make",
"expression": "`cars`.`make`"
}
]
}
]
},
"queryType": "select",
"requestType": "SDK",
"resultCount": 1,
"state": "completed",
"text": "PROFILE SELECT make FROM cars WHERE year > 2000",
"times": {
"elapsed": 4596233,
"parse": 387966,
"plan": 1092304,
"start": "2026-01-07T14:03:45.602+00:00"
}
}
}
```
Producer operators are executed in parallel with their consumers and may show a much higher `"documentsOut"` count than the consumer's `"documentsIn"`. For completed requests, this is an indication that the consumer has stopped processing early and has not processed all of the documents sent by the producer. This might be perfectly normal, for instance when a LIMIT operator has hit the document count and has therefore stopped processing, or it may be that a downstream operator has encountered an error and has stopped the statement execution as a result.
```json theme={null}
[...]
"#operator": "sequence",
"children": [
{
"#operator": "scan",
"#stats": {
[1] "documentsOut": 11,
"phaseTimes": {
"exec": 21792,
"recv": 2595000,
"send": 594043
}
},
"collection": "all_collections",
"datasource": "system"
},
{
"#operator": "limit",
"#stats": {
[1] "documentsIn": 2,
"documentsOut": 1,
"phaseTimes": {
"exec": 9333,
"recv": 3547625,
"send": 145375
}
},
"limit": 1
}
]
},
[...]
```
1. Currently only available for [select](/dql/select) statements.