Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

Manage and Monitor Queries

  • Capella Operational
    March 23, 2025
    + 12
    Monitoring and profiling SQL++ queries, query service nodes, and corresponding system resources is very important for smoother operational performance and efficiency of the system. In fact, often it is vital for diagnosing and troubleshooting issues such as query performance, resource bottlenecks, and overloading of various services.

    System keyspaces provide various monitoring details and statistics about individual queries and the Query service. When running on a cluster with multiple query nodes, stats about all queries on all query nodes are collected in the Query management and monitoring system keyspaces.

    For example, this can help identify:

    • The top 10 slow or fast queries running on a particular query node or the cluster.

    • Resource usage statistics of the query service, or resources used for a particular query.

    • Details about the active, completed, and prepared queries.

    • Find long running queries that are running for more than 2 minutes.

    These system keyspaces are transient in nature, and are not persisted to disk or permanent storage. Hence, the information in the keyspaces pertains to the current instantiation of the Query service.

    You can access the Query management and monitoring system keyspaces using any of the following:

    • SQL++ from the cbq shell or the Query tab

    • A monitoring SDK

    Using SQL++ enables you to obtain further insights from the keyspaces.

    Authentication and Client Privileges

    Users must have permission to access restricted system keyspaces. For more details about cluster credentials, see Configure Cluster Access Credentials.

    Monitor System Vitals

    The system:vitals catalog provides data about the running state and health of the query nodes, such as number of logical cores, active threads, queued threads, CPU utilization, memory usage, network utilization, garbage collection percentage, and so on. This information can be very useful to assess the current workload and performance characteristics of a query node.

    Get System Vitals

    To view system vitals, use a SQL++ query.

    To view system vitals with SQL++:

    sql++
    Copy
    SELECT * FROM system:vitals;

    System Vitals Details

    Getting system vitals, as described in Get System Vitals, returns results similar to the following.

    json
    Copy
    {
  "uptime": "7h39m32.668577197s",
  "local.time": "2021-04-30 18:42:39.517208807 +0000 UTC m=+27573.945319668",
  "version": "7.0.0-N1QL",
  "total.threads": 191,
  "cores": 2,
  "gc.num": 669810600,
  "gc.pause.time": "57.586373ms",
  "gc.pause.percent": 0,
  "memory.usage": 247985184,
  "memory.total": 11132383704,
  "memory.system": 495554808,
  "cpu.user.percent": 0,
  "cpu.sys.percent": 0,
  "request.completed.count": 140,
  "request.active.count": 0,
  "request.per.sec.1min": 0.0018,
  "request.per.sec.5min": 0.0055,
  "request.per.sec.15min": 0.0033,
  "request_time.mean": "536.348163ms",
  "request_time.median": "54.065567ms",
  "request_time.80percentile": "981.869933ms",
  "request_time.95percentile": "2.543128455s",
  "request_time.99percentile": "4.627922799s",
  "request.prepared.percent": 0
}

    This catalog contains the following attributes:

    Property Schema

    bucket.IO.stats
    optional

    The number of reads and retries for each bucket.

    Object

    uptime
    optional

    The uptime of the query engine.

    String (duration)

    local.time
    optional

    The local time of the query engine.

    Date (date-time)

    version
    optional

    The version of the query engine.

    String

    total.threads
    optional

    The number of active threads used by the query engine.

    Integer

    cores
    optional

    The maximum number of logical cores available to the query engine.

    Integer

    ffdc.total
    optional

    The total number of times FFDC has been invoked since the last restart.

    Integer

    gc.num
    optional

    The target heap size of the next garbage collection cycle.

    Long (int64)

    gc.pause.time
    optional

    The total time spent pausing for garbage collection since the query engine started (ns).

    String (duration)

    gc.pause.percent
    optional

    The percentage of time spent pausing for garbage collection since the last time the statistics were checked.

    Long (int64)

    healthy
    optional

    False when either the unbounded or plus request queues are full; true otherwise.

    Boolean

    host.memory.free
    optional

    Amount of free memory on the host.

    Long (int64)

    host.memory.quota
    optional

    The host memory quota. This reflects the node-quota setting.

    Long (int64)

    host.memory.total
    optional

    Total memory on the host.

    Long (int64)

    host.memory.value_quota
    optional

    This the total document memory quota on the node.

    Long (int64)

    load
    optional

    A calculation for how busy the server is.

    Integer

    loadfactor
    optional

    The moving 15 minute average of the load calculation.

    Integer

    memory.usage
    optional

    The amount of memory allocated for heap objects (bytes). This increases as heap objects are allocated, and decreases as objects are freed.

    Long (int64)

    memory.total
    optional

    The cumulative amount of memory allocated for heap objects (bytes). This increases as heap objects are allocated, but does not decrease when objects are freed.

    Long (int64)

    memory.system
    optional

    The total amount of memory obtained from the operating system (bytes). This measures the virtual address space reserved by the query engine for heaps, stacks, and other internal data structures.

    Long (int64)

    node
    optional

    The name or IP address and port of the node.

    String

    node.allocated.values
    optional

    The total number of values allocated to contain documents or computations around documents. (This is only of relevance internally.)

    Integer

    node.memory.usage
    optional

    The currently allocated document memory on the node.

    Integer

    cpu.user.percent
    optional

    CPU usage. The percentage of time spent executing user code since the last time the statistics were checked.

    Long (int64)

    cpu.sys.percent
    optional

    CPU usage. The percentage of time spent executing system code since the last time the statistics were checked.

    Long (int64)

    process.memory.usage
    optional

    Current process memory use.

    Integer

    process.percore.cpupercent
    optional

    Average CPU usage per core.

    Big Decimal

    process.rss
    optional

    Process RSS (bytes)

    Integer

    process.service.usage
    optional

    The number of active servicers for the dominant workload — unbound queue servicers or plus queue servicers.

    Integer

    request.completed.count
    optional

    Total number of completed requests.

    Integer

    request.active.count
    optional

    Total number of active requests.

    Integer

    request.per.sec.1min
    optional

    Number of query requests processed per second. 1-minute exponentially weighted moving average.

    Big Decimal

    request.per.sec.5min
    optional

    Number of query requests processed per second. 5-minute exponentially weighted moving average.

    Big Decimal

    request.per.sec.15min
    optional

    Number of query requests processed per second. 15-minute exponentially weighted moving average.

    Big Decimal

    request.queued.count
    optional

    Number of queued requests.

    Integer

    request.quota.used.hwm
    optional

    High water mark for request quota use.

    Integer

    request_time.mean
    optional

    End-to-end time to process a query. The mean value.

    String (duration)

    request_time.median
    optional

    End-to-end time to process a query. The median value.

    String (duration)

    request_time.80percentile
    optional

    End-to-end time to process a query. The 80th percentile.

    String (duration)

    request_time.95percentile
    optional

    End-to-end time to process a query. The 95th percentile.

    String (duration)

    request_time.99percentile
    optional

    End-to-end time to process a query. The 99th percentile.

    String (duration)

    request.prepared.percent
    optional

    Percentage of requests that are prepared statements.

    Integer

    servicers.paused.count
    optional

    Number of servicers temporarily paused due to memory pressure. (Applies to serverless environments only.)

    Integer

    servicers.paused.total
    optional

    Number of times servicers have been temporarily paused. (Applies to serverless environments only.)

    Integer

    temp.hwm
    optional

    High water mark for temp space use directly by query. (Doesn't include use by the GSI and FTS clients.)

    Integer

    temp.usage
    optional

    Current Query temp space use. (Doesn't include use by the GSI and FTS clients.)

    Integer

    Monitor and Manage Active Requests

    The system:active_requests catalog lists all currently executing active requests or queries.

    Get Active Requests

    To view active requests, use a SQL++ query.

    To view active requests with SQL++:

    sql++
    Copy
    SELECT * FROM system:active_requests;

    To get the query plan for active requests, include meta().plan in a SQL++ query. See Query Profiling.

    To view active requests with SQL++, including the query plan:

    sql++
    Copy
    SELECT *, meta().plan FROM system:active_requests;

    Terminate an Active Request

    The DELETE command can be used to terminate an active request, for instance, a non-responding or a long-running query.

    To terminate an active request uuid with SQL++:

    sql++
    Copy
    DELETE FROM system:active_requests WHERE requestId = "uuid";

    Active Request Details

    Getting active requests, as described in Get Active Requests, returns results similar to the following.

    json
    Copy
    [
  {
    "active_requests": {
      "clientContextID": "832adfa0-e9e6-464e-b5e6-b7ec7549d511",
      "cpuTime": "111.877µs",
      "elapsedTime": "77.631814ms",
      "executionTime": "77.517185ms",
      "n1qlFeatCtrl": 76,
      "node": "127.0.0.1:8091",
      "phaseOperators": {
        "authorize": 1,
        "fetch": 1,
        "primaryScan": 1,
        "project": 1,
        "stream": 1
      },
      "phaseTimes": {
        "authorize": "4.998µs",
        "fetch": "69.519µs",
        "instantiate": "16.28µs",
        "parse": "597.435µs",
        "plan": "24.141851ms",
        "plan.index.metadata": "24.005473ms",
        "plan.keyspace.metadata": "2.022µs",
        "primaryScan": "23.496033ms",
        "project": "824ns",
        "stream": "2.242µs"
      },
      "queryContext": "default:travel-sample.inventory",
      "remoteAddr": "127.0.0.1:37506",
      "requestId": "05cc1895-9986-4819-b4d3-8a4311e8f319",
      "requestTime": "2024-05-21T13:29:16.864Z",
      "scanConsistency": "unbounded",
      "state": "running",
      "statement": "SELECT * FROM system:active_requests;",
      "statementType": "SELECT",
      "useCBO": true,
      "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:126.0) Gecko/20100101 Firefox/126.0",
      "users": "builtin:Administrator"
    }
  }
]

    This catalog contains the following attributes:

    Property Schema

    clientContextID
    optional

    The opaque ID or context provided by the client. Refer to the request-level client_context_id parameter for more information.

    String

    elapsedTime
    optional

    The time taken from when the request was acknowledged by the service to when the request was completed. It includes the time taken by the service to schedule the request.

    String (duration)

    errorCount
    optional

    Total number of errors encountered while executing the query.

    Integer

    memoryQuota
    optional

    The memory quota for the request, in MB. This property is only returned if a memory quota is set for the query.

    Integer

    node
    optional

    IP address and port number of the node where the query is executed.

    String

    phaseCounts
    optional

    Count of documents processed at selective phases involved in the query execution, such as authorize, index scan, fetch, parse, plan, run, etc.

    For active requests, this property is dynamic, depending on the documents processed by various phases up to this moment in time. Polling the active requests again may return different values.

    Example: {"fetch":16,"indexScan":187}

    Object

    phaseOperators
    optional

    Indicates the numbers of each kind of query operator involved in different phases of the query processing.

    For instance, a non-covering index path might involve one index scan and one fetch operator. A join would probably involve two or more fetches, one per keyspace. A union select would have twice as many operator counts, one per each branch of the union.

    Example: {"authorize":1,"fetch":1,"indexScan":2}

    Object

    phaseTimes
    optional

    Cumulative execution times for various phases involved in the query execution, such as authorize, index scan, fetch, parse, plan, run, etc.

    For active requests, this property is dynamic, depending on the documents processed by various phases up to this moment in time. Polling the active requests again may return different values.

    Example: {"authorize":"823.631µs","fetch":"656.873µs","indexScan":"29.146543ms","instantiate":"236.221µs","parse":"826.382µs","plan":"11.831101ms","run":"16.892181ms"}

    Object

    remoteAddr
    optional

    IP address and port number of the client application, from where the query is received.

    String

    requestId
    optional

    Unique request ID internally generated for the query.

    UUID (uuid)

    requestTime
    optional

    Timestamp when the query is received.

    Date (date-time)

    resultCount
    optional

    Total number of documents returned in the query result.

    Integer

    resultSize
    optional

    Total number of bytes returned in the query result.

    Integer

    scanConsistency
    optional

    The value of the query setting Scan Consistency used for the query.

    String

    serviceTime
    optional

    Total amount of calendar time taken to complete the query.

    String (duration)

    state
    optional

    The state of the query execution, such as completed, running, cancelled.

    Note that the completed state means that the request was started and completed by the Query service, but it does not mean that it was necessarily successful. The request could have been successful, or completed with errors.

    To find requests that were successful, use this field in conjunction with the errorCount field: search for requests whose state is completed and whose error count is 0.

    String

    statement
    optional

    The query statement being executed.

    String

    useCBO
    optional

    Whether the cost-based optimizer is enabled for the query.

    Boolean

    usedMemory
    optional

    The amount of document memory used to execute the request. This property is only returned if a memory quota is set for the query.

    Integer

    userAgent
    optional

    Name of the client application or program that issued the query.

    String

    users
    optional

    Username with whose privileges the query is run.

    String

    For query plan field names and meanings, see Query Profiling Details.

    Monitor and Manage Prepared Statements

    The system:prepareds catalog provides data about the known prepared statements and their state in a query node’s prepared statement cache. For each prepared statement, this catalog provides information such as name, statement, query plan, last use time, number of uses, and so on.

    A prepared statement is created and stored relative to the current query context. You can create multiple prepared statements with the same name, each stored relative to a different query context. This enables you to run multiple instances of the same application against different datasets.

    When there are multiple prepared statements with the same name in different query contexts, the name of the prepared statement in the system:prepareds catalog includes the associated query context in brackets.

    Get Prepared Statements

    To get a list of all known prepared statements, use a SQL++ query.

    To get a list of all known prepared statements with a SQL++ query:

    sql++
    Copy
    SELECT * FROM system:prepareds;

    To get information about a specific prepared statement, use a SQL++ query.

    To get information about a specific prepared statement example1 with a SQL++ query:

    sql++
    Copy
    SELECT * FROM system:prepareds WHERE name = "example1";

    To get the query plan for prepared statements, include meta().plan in a SQL++ query. See Query Profiling.

    To view prepared statements with SQL++, including the query plan:

    sql++
    Copy
    SELECT *, meta().plan FROM system:prepareds;

    Delete Prepared Statements

    To delete a specific prepared statement, use a SQL++ query.

    To delete a prepared statement p1 with a SQL++ query:

    sql++
    Copy
    DELETE FROM system:prepareds WHERE name = "p1";

    To delete all the known prepared statements, use a SQL++ query.

    To delete all known prepared statements:

    sql++
    Copy
    DELETE FROM system:prepareds;

    Prepared Statement Details

    To try the examples in this section, first create a couple of prepared statements.

    Create a prepared statement with default query context

    For this example, unset the query context. For more information, see Query Context.

    Query
    sql++
    Copy
    PREPARE p1 AS SELECT * FROM `travel-sample`.inventory.airline WHERE iata = "U2";
    Create a prepared statement with specified query context

    For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Query
    sql++
    Copy
    PREPARE p1 AS SELECT * FROM airline WHERE iata = "U2";

    Getting prepared statements, as described in Get Prepared Statements, returns results similar to the following.

    json
    Copy
    {
  "requestID": "d976e59a-d74e-4350-b0df-fa137099d594",
  "signature": {
    "*": "*",
    "plan": "json"
  },
  "results": [
    {
      "prepareds": {
        "encoded_plan": "H4sIAAAAAAAA/6RTUW/TPBT9K9H5XrbJ30QBMcmIhzJ1AjG0qh3wwKbEJLedmWt71061UIXfjpxkndYhENpbYt97z7nn+GxAtnQVVbk3ykICAgtSsWY6djayMwHy6JWAthXdjr3+TBy0s5Avh7N5qewHaoJXJQXIDSpaqNpEGVmtyfwf1MobOtR2TTY6bg6VZqMtQS6UCdQKWLUiSPgR+u9uFOTdIAg4T6yi4zT+v/sfjOt45Vj/IAh41mttaNmTONUhQn7d4FzxkuL9tL/SEpiyXkMepQ/nA+Sz9rIV+FleaVPtMpjTTU22TG19AZPtcP+5aMp6pbhJcr6AwLe6vO54P+CLQfR+n3zLPh/Y576fcleXe3bfqYydYxsMt/k1NZCR66T+9eAdJO4l+L0NoXQ+nWxhIVAHbZeQWAaNVjxc6YRiefWnXZ6EvYs2VayMIYMneXWiTSSGQOlspXvhsLdXDPyKw0KrqIr97E12gU/PL7D/iMh7q6NWZtpLDwGmUJuYR+JV6ADp1qfCQGaRVouKBzsu28s2vbYd4pFJrZDuBFJOtyE8Go0EbmriJqWVbmOfYKab86aTaz45nRyfJxC9tF2skyoHkDhAKzC0TGeT6Xg2yfwoG8+zvic7yE5mZx+z4oFpxePEZF/eTWaTLMmyFeV19zLo+O3ZsNivAAAA//+q+jhuaAQAAA==",
        "featuresControl": 76,
        "indexApiVersion": 4,
        "indexScanKeyspaces": {
          "default:travel-sample.inventory.airline": false
        },
        "name": "p1", (1)
        "namespace": "default",
        "node": "127.0.0.1:8091",
        "statement": "PREPARE p1 AS SELECT * FROM `travel-sample`.inventory.airline WHERE iata = \"U2\";",
        "uses": 0
      }
    },
    {
      "prepareds": {
        "encoded_plan": "H4sIAAAAAAAA/6STT28TMRDFv8rqcWkrExFAVDLiEKpUIIoaJQUOtNqY3Ulq6tju2Bt1iZbPjry7TWmKQKg3/xnPvPk9zwZkC1dSmXujLCQgsCAVK6YjZyM7EyAPXwloW9LNyOvPxEE7C/myP5sVyn6gOnhVUIDcoKSFqkyUkdWazNOgVt7QQNs12ei4HijNRluCXCgTqBGwakWQ8EN06zYV5G0iCDhPrKLjlP7J3QajKl461j8IAp71WhtadiJOdIiQXzc4U7ykeJftn7IEJqzXkIdp4XyAfNZcNAI/i0ttyl0FM7quyBbpWRfAZNu6/x00Yb1SXCecLyDwrSquWt339KKH3vWTb9Xnvfrcd1lu43LP7jsVsXVsg/42v6IaMnKV6F/13kHiDsGfbQiF8+lkWxYCVdB2CYll0GjE/ZaOKRaXf+vlUbV3q00UK2PI4FFeHWsTiSFQOFvqDhz29ua9vvlgrlVU8/3sTXaOT8/Psf9AyHuro1Zm0qGHAFOoTMwj8Sq0BenGp8BAZpFai4p7Oy6aiyb9th3hkUmtkO4E0pxuh/BwOBS4rojrNK108wDy4HevmK7P6pbibHwyPjpLtfXSttOeYB1A4gCNQJ9pMh1PRtNx5ofZaJZ1b7KD7Hh6+jHreWRf3o2n4ywx2RJ53X4LOnp72nf1KwAA////9+bsZQQAAA==",
        "featuresControl": 76,
        "indexApiVersion": 4,
        "indexScanKeyspaces": {
          "default:travel-sample.inventory.airline": false
        },
        "name": "p1(travel-sample.inventory)", (2)
        "namespace": "default",
        "node": "127.0.0.1:8091",
        "statement": "PREPARE p1 AS SELECT * FROM airline WHERE iata = \"U2\";",
        "uses": 0
      }
    }
  ],
  "status": "success",
  "metrics": {
    "elapsedTime": "25.323496ms",
    "executionTime": "25.173646ms",
    "resultCount": 2,
    "resultSize": 7891,
    "serviceLoad": 12
  }
}

    In this example, the names of the prepared statements are identical, but they are associated with different query contexts.

    1 The name of the prepared statement for the default query context
    2 The name of the prepared statement showing the associated query context

    This catalog contains the following attributes:

    Property Schema

    encoded_plan
    required

    The full prepared statement in encoded format.

    String

    featureControls
    optional

    This property is provided for technical support only. It is only returned when retrieving a specific prepared statement, not when retrieving all prepared statements.

    Integer

    indexApiVersion
    optional

    This property is provided for technical support only. It is only returned when retrieving a specific prepared statement, not when retrieving all prepared statements.

    Integer

    name
    required

    The name of the prepared statement. This may be a UUID that was assigned automatically, or a name that was user-specified when the statement was created.

    String

    namespace
    optional

    The namespace in which the prepared statement is stored. Currently, only the default namespace is available.

    String

    node
    optional

    The node on which the prepared statement is stored.

    String

    statement
    required

    The text of the query.

    String

    uses
    required

    The count of times the prepared statement has been executed.

    Integer

    avgElapsedTime
    optional

    The mean time taken from when the request to execute the prepared statement was acknowledged by the service, to when the request was completed. It includes the time taken by the service to schedule the request.

    This property is only returned when the prepared statement has been executed. It is only returned when retrieving a specific prepared statement, not when retrieving all prepared statements.

    String (duration)

    avgServiceTime
    optional

    The mean amount of calendar time taken to complete the execution of the prepared statement.

    This property is only returned when the prepared statement has been executed. It is only returned when retrieving a specific prepared statement, not when retrieving all prepared statements.

    String (duration)

    lastUse
    optional

    Date and time of last use.

    This property is only returned when the prepared statement has been executed.

    Date (date-time)

    maxElapsedTime
    optional

    The maximum time taken from when the request to execute the prepared statement was acknowledged by the service, to when the request was completed. It includes the time taken by the service to schedule the request.

    This property is only returned when the prepared statement has been executed. It is only returned when retrieving a specific prepared statement, not when retrieving all prepared statements.

    String (duration)

    maxServiceTime
    optional

    The maximum amount of calendar time taken to complete the execution of the prepared statement.

    This property is only returned when the prepared statement has been executed. It is only returned when retrieving a specific prepared statement, not when retrieving all prepared statements.

    String (duration)

    minElapsedTime
    optional

    The minimum time taken from when the request to execute the prepared statement was acknowledged by the service, to when the request was completed. It includes the time taken by the service to schedule the request.

    This property is only returned when the prepared statement has been executed. It is only returned when retrieving a specific prepared statement, not when retrieving all prepared statements.

    String (duration)

    minServiceTime
    optional

    The minimum amount of calendar time taken to complete the execution of the prepared statement.

    This property is only returned when the prepared statement has been executed. It is only returned when retrieving a specific prepared statement, not when retrieving all prepared statements.

    String (duration)

    For query plan field names and meanings, see Query Profiling Details.

    Monitor and Manage Completed Requests

    By default, the system:completed_requests catalog maintains a list of the most recent completed requests that have run longer than a predefined threshold of time. (You can also log completed requests that meet other conditions that you define.)

    For each completed request, this catalog maintains information such as requestId, statement text, prepared name (if prepared statement), request time, service time, and so on. This information provides a general insight into the health and performance of the query node and the cluster.

    Get Completed Requests

    To get a list of all logged completed requests, use a SQL++ query.

    To get a list of all logged completed requests using SQL++:

    sql++
    Copy
    SELECT * FROM system:completed_requests;

    Note that the completed state means that the request was started and completed by the Query service, but it does not mean that it was necessarily successful. The request could have been successful, or completed with errors.

    To find requests that completed successfully, search for completed requests whose state is completed and whose errorCount field has the value 0.

    To get a list of all logged completed requests, including only successful requests:

    sql++
    Copy
    SELECT * FROM system:completed_requests
WHERE state = "completed" AND errorCount = 0;

    To get the query plan for completed requests, include meta().plan in a SQL++ query. See Query Profiling.

    To view completed requests with SQL++, including the query plan:

    sql++
    Copy
    SELECT *, meta().plan FROM system:completed_requests;

    Purge the Completed Requests

    To purge a specific completed request, use a SQL++ query.

    To purge a completed request uuid with SQL++:

    sql++
    Copy
    DELETE FROM system:completed_requests WHERE requestId = "uuid";

    To purge completed requests for a given time period, use a SQL++ query.

    To purge the completed requests for a given time period:

    sql++
    Copy
    DELETE FROM system:completed_requests WHERE requestTime LIKE "2015-09-09%";

    Completed Request Details

    To try the examples in this section, first run a query which takes at least 1000ms (the default value of the completed-threshold query setting) to get registered in the system:completed_requests keyspace.

    Run a long query

    For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Query
    sql++
    Copy
    SELECT * FROM route ORDER BY sourceairport LIMIT 5;

    Getting completed requests, as described in Get Completed Requests, returns results similar to the following.

    json
    Copy
    [
  // ...
  {
    "completed_requests": {
      "clientContextID": "a19a61ab-cd9e-46c9-be71-92623ff85741",
      "cpuTime": "912.408423ms",
      "elapsedTime": "3.762926948s",
      "errorCount": 0,
      "errors": [],
      "n1qlFeatCtrl": 76,
      "node": "127.0.0.1:8091",
      "phaseCounts": {
        "fetch": 24023,
        "primaryScan": 24023,
        "primaryScan.GSI": 24023,
        "sort": 24028
      },
      "phaseOperators": {
        "authorize": 1,
        "fetch": 1,
        "primaryScan": 1,
        "primaryScan.GSI": 1,
        "project": 1,
        "sort": 2,
        "stream": 1
      },
      "phaseTimes": {
        "authorize": "15.111µs",
        "fetch": "3.641125449s",
        "instantiate": "332.963µs",
        "parse": "1.04015ms",
        "plan": "602.878µs",
        "plan.index.metadata": "25.849µs",
        "plan.keyspace.metadata": "11.586µs",
        "primaryScan": "101.118572ms",
        "primaryScan.GSI": "101.118572ms",
        "project": "33.273783ms",
        "run": "3.760767643s",
        "sort": "666.364325ms",
        "stream": "1.617688ms"
      },
      "queryContext": "default:travel-sample.inventory",
      "remoteAddr": "127.0.0.1:37684",
      "requestId": "e170bf67-d364-4ed7-9698-784bbb779d18",
      "requestTime": "2024-05-21T14:31:46.882Z",
      "resultCount": 5,
      "resultSize": 17714,
      "scanConsistency": "unbounded",
      "serviceTime": "3.762768429s",
      "state": "completed",
      "statement": "SELECT * FROM route ORDER BY sourceairport LIMIT 5;",
      "statementType": "SELECT",
      "useCBO": true,
      "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:126.0) Gecko/20100101 Firefox/126.0",
      "users": "builtin:Administrator",
      "~qualifier": "threshold"
    }
  }
]

    For field names and meanings, see Active Request Details.

    For query plan field names and meanings, see Query Profiling Details.

    Query Profiling

    Query profiling enables you to obtain more detailed monitoring information and finer execution timings for any query. You can set query profiling to the following levels:

    • off — query profiling is disabled.

    • phases — query profiling is enabled, including information about the phases of query execution.

    • timings — query profiling is enabled, including information about the phases of query execution, and detailed timing information.

    You can set query profiling:

    For more information about Query settings and parameters, see Configure Queries.

    Enable Query Profiling for a Request

    To activate profiling at the request level, you can:

    • Specify the profile setting using the cbq command line tool.

    To set query settings using the cbq shell, use the \SET command.

    The following statement sets the profiling to phases:

    sql++
    Copy
    \set -profile "phases";
SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;

    The following statement sets the profiling to timings:

    sql++
    Copy
    \set -profile "timings";
SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;

    The Query tab automatically enables Query profiling, with detailed timing information. To disable or enable Query profiling with the Query tab, specify the Collect query timings option using the Query Settings.

    Query Profiling Details

    You can access the profiling information:

    When a query executes a user-defined function, profiling information is available for the SQL++ queries within the user-defined function as well.

    Profiling Details in Query Responses

    When profiling is enabled:

    • If you are using the cbq shell, query profiling information is returned with the query results.

    • If you are using the Query tab, query profiling information is not returned with the query results.

    Phases Profile

    If you are using the cbq shell, the following statistics are returned when profile is set to phases:

    json
    Copy
    {
  "requestID": "06d6c1c2-1a8a-4989-a856-7314f9eddee5",
  "signature": {
    "*": "*"
  },
  "results": [
    {
      "airline": {
        "callsign": "MILE-AIR",
        "country": "United States",
        "iata": "Q5",
        "icao": "MLA",
        "id": 10,
        "name": "40-Mile Air",
        "type": "airline"
      }
    }
  ],
  "status": "success",
  "metrics": {
    "elapsedTime": "12.77927ms",
    "executionTime": "12.570648ms",
    "resultCount": 1,
    "resultSize": 254,
    "serviceLoad": 12
  },
  "profile": {
    "phaseTimes": {
      "authorize": "19.629µs",
      "fetch": "401.997µs",
      "instantiate": "147.686µs",
      "parse": "4.545234ms",
      "plan": "409.364µs",
      "primaryScan": "6.103775ms",
      "run": "6.699056ms"
    },
    "phaseCounts": {
      "fetch": 1,
      "primaryScan": 1
    },
    "phaseOperators": {
      "authorize": 1,
      "fetch": 1,
      "primaryScan": 1
    },
    "requestTime": "2021-04-30T18:37:56.394Z",
    "servicingHost": "127.0.0.1:8091"
  }
}
    Timings Profile

    If you are using the cbq shell, the following statistics are returned when profile is set to timings:

    json
    Copy
    {
  "requestID": "268a1240-6864-43a2-af13-ccb8d1e50abf",
  "signature": {
    "*": "*"
  },
  "results": [
    {
      "airline": {
        "callsign": "MILE-AIR",
        "country": "United States",
        "iata": "Q5",
        "icao": "MLA",
        "id": 10,
        "name": "40-Mile Air",
        "type": "airline"
      }
    }
  ],
  "status": "success",
  "metrics": {
    "elapsedTime": "2.915245ms",
    "executionTime": "2.755355ms",
    "resultCount": 1,
    "resultSize": 254,
    "serviceLoad": 12
  },
  "profile": {
    "phaseTimes": {
      "authorize": "18.096µs",
      "fetch": "388.122µs",
      "instantiate": "31.702µs",
      "parse": "646.157µs",
      "plan": "120.427µs",
      "primaryScan": "1.402918ms",
      "run": "1.936852ms"
    },
    "phaseCounts": {
      "fetch": 1,
      "primaryScan": 1
    },
    "phaseOperators": {
      "authorize": 1,
      "fetch": 1,
      "primaryScan": 1
    },
    "requestTime": "2021-04-30T18:40:13.239Z",
    "servicingHost": "127.0.0.1:8091",
    "executionTimings": {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 4,
        "execTime": "1.084µs",
        "servTime": "17.012µs"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:travel-sample.inventory.airline",
            "Priv": 7,
            "Props": 0
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "2.474µs"
        },
        "~children": [
          {
            "#operator": "PrimaryScan3",
            "#stats": {
              "#itemsOut": 1,
              "#phaseSwitches": 7,
              "execTime": "18.584µs",
              "kernTime": "8.869µs",
              "servTime": "1.384334ms"
            },
            "bucket": "travel-sample",
            "index": "def_inventory_airline_primary",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "airline",
            "limit": "1",
            "namespace": "default",
            "scope": "inventory",
            "using": "gsi"
          },
          {
            "#operator": "Fetch",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 10,
              "execTime": "25.64µs",
              "kernTime": "1.427752ms",
              "servTime": "362.482µs"
            },
            "bucket": "travel-sample",
            "keyspace": "airline",
            "namespace": "default",
            "scope": "inventory"
          },
          {
            "#operator": "InitialProject",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 9,
              "execTime": "6.006µs",
              "kernTime": "1.825917ms"
            },
            "result_terms": [
              {
                "expr": "self",
                "star": true
              }
            ]
          },
          {
            "#operator": "Limit",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 4,
              "execTime": "2.409µs",
              "kernTime": "2.094µs"
            },
            "expr": "1"
          },
          {
            "#operator": "Stream",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 6,
              "execTime": "46.964µs",
              "kernTime": "1.844828ms"
            }
          }
        ]
      },
      "~versions": [
        "7.0.0-N1QL",
        "7.0.0-4960-enterprise"
      ]
    }
  }
}

    The profile object contains the following attributes:

    Property Schema

    requestTime
    required

    Timestamp when the query was received.

    Date (date-time)

    servicingHost
    required

    IP address and port number of the node where the query was executed.

    String

    phaseCounts
    required

    Count of documents processed at selective phases involved in the query execution, such as authorize, index scan, fetch, parse, plan, run, etc.

    Example: {"fetch":16,"indexScan":187}

    Object

    phaseOperators
    required

    Indicates the numbers of each kind of query operator involved in different phases of the query processing.

    For instance, a non-covering index path might involve one index scan and one fetch operator. A join would probably involve two or more fetches, one per keyspace. A union select would have twice as many operator counts, one per each branch of the union.

    This is in essence the count of all the operators in the executionTimings object.

    Example: {"authorize":1,"fetch":1,"indexScan":2}

    Object

    phaseTimes
    required

    Cumulative execution times for various phases involved in the query execution, such as authorize, index scan, fetch, parse, plan, run, etc.

    Example: {"authorize":"823.631µs","fetch":"656.873µs","indexScan":"29.146543ms","instantiate":"236.221µs","parse":"826.382µs","plan":"11.831101ms","run":"16.892181ms"}

    Object

    executionTimings
    optional

    Present only if profile was set to "timings" in the Request Parameters.

    The execution details for various phases involved in the query execution, such as kernel and service execution times, number of documents processed at each query operator in each phase, and number of phase switches.

    Execution Timings

    Execution Timings

    Property Schema

    #operator
    required

    Name of the operator.

    Example: "Fetch"

    String

    #stats
    required

    Statistics collected for the operator.

    Statistics

    ~child
    optional

    Further nested operators, each with their own execution timings.

    Object

    Statistics

    Property Schema

    #itemsIn
    optional

    Number of input documents to the operator.

    Example: 187

    Integer (int32)

    #itemsOut
    optional

    Number of output documents after the operator processing.

    Example: 16

    Integer (int32)

    #phaseSwitches
    optional

    Number of switches between executing, waiting for services, or waiting for the goroutine scheduler.

    Example: 413

    Integer (int32)

    execTime
    optional

    Time spent executing the operator code inside SQL++ query engine.

    Example: "128.434µs"

    String (duration)

    kernTime
    optional

    Time spent waiting to be scheduled for CPU time.

    Example: "15.027879ms"

    String (duration)

    servTime
    optional

    Time spent waiting for another service, such as index or data.

    For index scan, it is time spent waiting for GSI/indexer.

    For fetch, it is time spent waiting on the KV store.

    Example: "1.590934ms"

    String (duration)

    The kernTime, servTime, and execTime statistics can be very helpful in troubleshooting query performance issues. For example:

    • A high servTime for a low number of items processed is an indication that the indexer or KV store is stressed.

    • A high kernTime means there is a downstream issue in the query plan or the query server having many requests to process, so the scheduled waiting time will be more for CPU time.

    Profiling Details in System Catalogs

    The system:active_requests and system:completed_requests system catalogs always return profiling information regarding query phases: that is, phase times, phase counts, and phase operators.

    The system:active_requests, system:completed_requests, and system:prepareds system catalogs also support the meta().plan virtual attribute. This captures the whole query plan, and includes profiling information regarding execution timings.

    To get execution timing information from these system catalogs, you must explicitly specify meta().plan in the projection list for the SELECT query.

    Within these system catalogs, not all statements have a meta().plan attribute.

    When request profiling is set to timings, profiling information is likely to use 100KB+ per entry in the system:completed_requests keyspace.

    • Due to the added overhead of running both profiling and logging, turn on both of them only when needed. Running only one of them continuously has no noticeable affect on performance.

    • Profiling does not carry any extra cost beyond memory for completed requests, so it’s fine to run it continuously.
    Plan Details

    Getting the plan for a statement that you ran when the profile was set to timings returns results similar to the following.

    json
    Copy
    [
  {
  // ...
    "plan": {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 4,
        "execTime": "1.725µs",
        "servTime": "21.312µs"
      },
      "privileges": {
        "List": [
          {
            "Priv": 7,
            "Props": 0,
            "Target": "default:travel-sample.inventory.route"
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 2,
          "execTime": "1.499µs"
        },
        "~children": [
          {
            "#operator": "PrimaryScan3",
            "#stats": {
              "#heartbeatYields": 6,
              "#itemsOut": 24024,
              "#phaseSwitches": 96099,
              "execTime": "84.366121ms",
              "kernTime": "3.021901421s",
              "servTime": "69.320752ms"
            },
            "bucket": "travel-sample",
            "index": "def_inventory_route_primary",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "route",
            "namespace": "default",
            "scope": "inventory",
            "using": "gsi"
          },
          {
            "#operator": "Fetch",
            "#stats": {
              "#heartbeatYields": 7258,
              "#itemsIn": 24024,
              "#itemsOut": 24024,
              "#phaseSwitches": 99104,
              "execTime": "70.34694ms",
              "kernTime": "142.630196ms",
              "servTime": "3.021959695s"
            },
            "bucket": "travel-sample",
            "keyspace": "route",
            "namespace": "default",
            "scope": "inventory"
          },
          {
            "#operator": "InitialProject",
            "#stats": {
              "#itemsIn": 24024,
              "#itemsOut": 24024,
              "#phaseSwitches": 96100,
              "execTime": "15.331951ms",
              "kernTime": "3.219612458s"
            },
            "result_terms": [
              {
                "expr": "self",
                "star": true
              }
            ]
          },
          {
            "#operator": "Order",
            "#stats": {
              "#itemsIn": 24024,
              "#itemsOut": 24024,
              "#phaseSwitches": 72078,
              "execTime": "147.889352ms",
              "kernTime": "3.229055752s"
            },
            "sort_terms": [
              {
                "expr": "(`route`.`sourceairport`)"
              }
            ]
          },
          {
            "#operator": "Stream",
            "#stats": {
              "#itemsIn": 24024,
              "#itemsOut": 24024,
              "#phaseSwitches": 24025,
              "execTime": "11.851634134s"
            }
          }
        ]
      },
      "~versions": [
        "7.0.0-N1QL",
        "7.0.0-4960-enterprise"
      ]
    }
  }
]

    For field names and meanings, see Execution Timings.

    Query Profiling Summary

    The following table summarizes Query profiling behavior.

    Profile is …​ Query returns …​ Catalog includes …​

    Query
    workbench

    cbq

    Active
    Requests

    Completed
    Requests

    Prepared
    Statements

    off

    —

    —

    phases

    phases

    timings

    phases

    —

    phases

    phases

    phases

    timings

    timings

    —

    phases
    timings

    phases
    timings

    phases
    timings

    timings