Virtual Collections

A number of virtual collections are available in the system namespace to monitor the DQL environment & peer information, and to provide statement diagnostics.

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.

SELECT * FROM system:active_requests

Show output

{
  "_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:

When executing in an environment with multiple subscription servers, only data from the servicing server is reported.
It is expected to see any query retrieving data from active_requests in the output in “executing” state.
Operators will record information whilst processing so it is normal to see changes in the content for active_requests.
Zero values (times, counts) are omitted from the output.

SYSTEM:ALL_COLLECTIONS

Reports all collections in all namespaces.

SELECT * FROM system:all_collections

Show output

{
  "_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.

SELECT * FROM system:collections

Show output

{
  "_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.

SELECT * FROM system:data_sync_info

Show output

{
  "_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:DQL_INDEXES

Reports the user defined indexes (Ditto SDK only).

SELECT * FROM system:indexes

Show output

{
  "_id": "my_collection.ix_a",
  "collection": "my_collection",
  "fields": [
    {
      "direction": "asc",
      "key": [
        "a"
      ]
    }
  ]
}

SYSTEM:DUAL

Contains a single document with a single field. It can be used to execute and test DQL statements.

SELECT * FROM system:dual

Show output

{
  "_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.)

SELECT * FROM system:request_history

Show output

{
  "_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.) Some points to note about the content:

When executing in an environment with multiple subscription servers, only data from the servicing server is reported.
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.
Zero values (times, counts) are omitted from the output.

The cache content may be (selectively) cleared with a DELETE statement. See also: Diagnosing problems

SYSTEM:SYSTEM_INFO

Reports a system information dump’s contents.

SELECT * FROM system:system_info

Show output

{
  "key": "identity_service_metadata",
  "namespace": "auth",
  "timestamp": 1767716539,
  "value": {
    "inner": {
      "foo": "bar"
    },
    "user_id": "[email protected]",
    "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.

SELECT * FROM system:transports_info

Show output

{
  "_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.

SELECT * FROM system:vitals

Show output

{
  "_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:

The histograms decay over a 15 minute interval.
“other” is all statements not covered by the individual metrics, e.g. ALTER SYSTEM
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 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.

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:

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.
failed - if a statement’s execution fails. true by default.
system_delete - any delete statement run against a system namespace collection. true by default.
mutation - if a statement is a mutation of any sort. Not active (false) by default.

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:

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.
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.
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.
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.
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:

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.
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.
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 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¹ 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.

PROFILE SELECT make FROM cars WHERE year > 2000

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.

      [...]
      "#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 statements.}

Statements

Types and Functions

Reference

Virtual Collections

SYSTEM:ACTIVE_REQUESTS

SYSTEM:ALL_COLLECTIONS

SYSTEM:COLLECTIONS

SYSTEM:DATA_SYNC_INFO

SYSTEM:DQL_INDEXES

SYSTEM:DUAL

SYSTEM:REQUEST_HISTORY

SYSTEM:SYSTEM_INFO

SYSTEM:TRANSPORTS_INFO

SYSTEM:VITALS

Configuration

Request History

Diagnosing problems

Profile analysis

SYSTEM:ACTIVE_REQUESTS & SYSTEM:REQUEST_HISTORY

Statements

Types and Functions

Reference

​SYSTEM:ACTIVE_REQUESTS

​SYSTEM:ALL_COLLECTIONS

​SYSTEM:COLLECTIONS

​SYSTEM:DATA_SYNC_INFO

​SYSTEM:DQL_INDEXES

​SYSTEM:DUAL

​SYSTEM:REQUEST_HISTORY

​SYSTEM:SYSTEM_INFO

​SYSTEM:TRANSPORTS_INFO

​SYSTEM:VITALS

​Configuration

​Request History

​Diagnosing problems

​Profile analysis

​SYSTEM:ACTIVE_REQUESTS & SYSTEM:REQUEST_HISTORY

SYSTEM:ACTIVE_REQUESTS

SYSTEM:ALL_COLLECTIONS

SYSTEM:COLLECTIONS

SYSTEM:DATA_SYNC_INFO

SYSTEM:DQL_INDEXES

SYSTEM:DUAL

SYSTEM:REQUEST_HISTORY

SYSTEM:SYSTEM_INFO

SYSTEM:TRANSPORTS_INFO

SYSTEM:VITALS

Configuration

Request History

Diagnosing problems

Profile analysis

SYSTEM:ACTIVE_REQUESTS & SYSTEM:REQUEST_HISTORY