Monitoring N1QL Queries

Monitoring N1QL Queries

Monitoring and profiling N1QL queries, query service engines, 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.

Couchbase Server 4.5 introduced system keyspaces to provide various monitoring details and statistics about individual queries and query service. When running on a cluster with multiple query nodes, stats about all queries on all query nodes are collected in these system keyspaces.

For example, this can help identify:
  • the top 10 slow or fast queries running on a particular query engine 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 like virtual keyspaces that 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 keyspaces using any of the following:
  • N1QL language (from the cbq shell or Query Workbench)
  • REST API
  • Monitoring SDK
    Note: All the power of the N1QL query language can be applied on the keyspaces to obtain various insights.

The following diagnostics are provided:

Authentication and Client Privileges

Client applications must be authenticated with sufficient privileges to access system keyspaces.

  • An Administrator user can access all system keyspaces, and can grant or revoke privileges to and from other users.
  • Other non-Administrator users must have explicitly granted privilege called Query System Catalog to access the restricted system keyspaces. For querying details of system keyspaces, see Getting System Information.
  • The following system keyspaces are considered open, that is, all users can access them without any special privileges:
    • system:dual
    • system:datastores
    • system:namespaces

Query Monitoring Settings

The monitoring settings can be set for each query engine (using the REST API) or for each query statement (using the cbq command line tool). Both settings are actually set via REST endpoints: using the Admin REST API (/admin/settings endpoint) and the Query REST API (/query/service endpoint).

The cbq shell and Query Workbench use the Query REST API to set monitoring at the request level. The Query Workbench automatically enables the profiling and timings. It can be disabled using the Preferences option. For more information refer to the Query Workbench section.

Use the following query parameters to enable, disable, and control the monitoring capabilities, and the level of monitoring and profiling details for each query or globally at a query engine level:

  • profile
  • controls

For more details and examples, refer to the Query Settings section.

Enabling Settings for a Query Engine
You can enable profile settings for each query engine. These examples use local host IP address and default port numbers. You need to provide correct credentials, IP address, and port details of your setup.
  1. Get the current query settings:
    curl -u Administrator:pword -X GET http://localhost:8093/admin/settings > ./query_settings.json
    
    cat  ./query_settings.json
    
    {"completed-limit":4000,"completed-threshold":1000,"controls":false,"cpuprofile":"","debug":false,"keep-alive-length":16384,"loglevel":"INFO","max-index-api":2,"max-parallelism":1,"memprofile":"","pipeline-batch":16,"pipeline-cap":512,"pretty":true,"profile":"off","request-size-cap":67108864,"scan-cap":512,"servicers":16,"timeout":0}
  2. Set current query settings profile:
    1. To set the query settings saved in a file ./query_settings.json, enter the following query:
      curl -u Administrator:pword -XPOST  http://127.0.0.1:8093/admin/settings -d@./query_settings.json
    2. To explicitly specify the settings, enter the following query:
      curl -u Administrator:pword  http://127.0.0.1:8093/admin/settings -XPOST -d '{"profile": "phases"}'
    3. Verify the settings are changed as specified:
      curl -u Administrator:pword -X GET http://localhost:8093/admin/settings 
      {"completed-limit":4000,"completed-threshold":1000,"controls":false,"cpuprofile":"","debug":false,"keep-alive-length":16384,"loglevel":"INFO","max-index-api":2,"max-parallelism":1,"memprofile":"","pipeline-batch":16,"pipeline-cap":512,"pretty":true,"profile":"phases","request-size-cap":67108864,"scan-cap":512,"servicers":16,"timeout":0}
      
Enabling Settings per Session or per Query
You can enable monitoring and profiling settings for each query statement. To set query settings using the cbq shell, run the following query:
cbq> \set -profile "timings";
cbq> \set;
 Query Parameters : 
 Parameter name : profile
 Value : ["timings"]
 ...
To set query settings using the REST API, run the following query:
curl -v http://localhost:8093/query/service -d "profile='timings'&statement=SELECT * FROM default LIMIT 1";

Monitoring and Profiling Details

Couchbase Server 5.0 introduces a collection of detailed query monitoring and profiling information. The profiling and finer query execution timing details can be obtained for any query.

Attribute Profile in Query Response

When profiling is enabled, a query response includes the profile attribute. The attribute details are as follows:
Table 1. Attribute Details
Attribute Example
phaseTimes - Cumulative execution times for various phases involved in the query execution, such as authorise, indexscan, fetch, parse, plan, run etc.,
Note:

This value will be dynamic, depending on the documents processed by various phases up to this moment in time.

A new query on system:active requests will return different values.
"phaseTimes": {
	"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"
}
phaseCounts - Count of documents processed at selective phases involved in the query execution, such as authorise, indexscan, fetch, parse, plan, run etc.,
Note:

This value will be dynamic, depending on the documents processed by various phases up to this moment in time.

A new query on system:active requests will return different values.
"phaseCounts": {
	    "fetch": 16,
	    "indexScan": 187
	}
phaseOperators - Indicates the number of each kind of query operators involved in different phases of the query processing. For instance, this example, one non covering index path was taken, which involves 1 indxeScan and 1 fetch operators.

A join would have probably involved 2 fetches (1 per keyspace)

A union select would have twice as many operator counts (1 per each branch of the union).

This is in essence the count of all the operators in the executionTimings field.
"phaseOperators": {
	    "authorize": 1,
	    "fetch": 1,
	    "indexScan": 2
}
executionTimings - The execution details such as kernel and service execution times, number of documents processed at each query operator in each phase, and number of phase switches, for various phases involved in the query execution.

The following statistics are collected for each operator:

"executionTimings": {
  …
  [{
     "#operator": "Fetch",
     "#stats": {
         "#itemsIn": 187,
         "#itemsOut": 16,
	   "#phaseSwitches": 413,
	   "execTime": "128.434µs",
	   "kernTime": "15.027879ms",
	   "servTime": "1.590934ms",
	   "state": "services"
     },
	"keyspace": "travel-sample",
	"namespace": "default"
  },
  {
     "#operator": "IntersectScan",
     "#stats": {
  	   "#itemsIn": 187,
	   "#itemsOut": 187,
	   "#phaseSwitches": 749,
	   "execTime": "449.944µs",
	   "kernTime": "14.625524ms"
     }
  }, …]
#operator Name of the operator.
#stats

These values will be dynamic, depending on the documents processed by various phases up to this moment in time.

A new query on system:active_requests will return different values.

#itemsIn Number of input documents to the operator.
#itemsOut Number of output documents after the operator processing.

#phaseSwitches

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

execTime - Time spent executing the operator code inside N1QL query engine.

kernTime - Time spent waiting to be scheduled for CPU time.

servTime - 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

These statistics (kernTime, servTime, and execTime) can be very helpful in troubleshooting query performance issues, such as:

  • 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).

Example 1 of Attribute Profile: Show the statistics collected when the profile is set to phases:

Note: The cbq engine must be started with authorization, for example:

$> ./cbq  -engine=http://127.0.0.1:8091/ -u Administrator -p pword

$ ./cbq  -engine=http://localhost:8091/  -u Administrator -p pword
cbq> \set -profile "phases";
cbq> SELECT * FROM `travel-sample` WHERE type = "airline" LIMIT 1;

{
    "requestID": "51da6b92-f978-4083-b3e4-3cccbe91b5ac",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "travel-sample": {
                "callsign": "BEMIDJI",
                "country": "United States",
                "iata": "CH",
                "icao": "BMJ",
                "id": 1442,
                "name": "Bemidji Airlines",
                "type": "airline"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "13.967097ms",
        "executionTime": "13.951263ms",
        "resultCount": 1,
        "resultSize": 306
    },
    "profile": {
        "phaseTimes": {
	    "authorize": "744.299µs",
	    "fetch": "524.3µs",
	    "indexScan": "9.087106ms",
	    "instantiate": "52.443µs",
	    "parse": "271.049µs",
	    "plan": "7.450287ms",
	    "run": "6.130069ms"
	},
        "phaseCounts": {
	    "fetch": 16,
	    "indexScan": 187
	},
        "phaseOperators": {
	    "authorize": 1,
	    "fetch": 1,
	    "indexScan": 2
	}
    }
}
Example 2 of Attribute Profile: Show the statistics collected when profile is set to timings:
cbq> \set -profile "timings";
cbq> SELECT * FROM `travel-sample` WHERE type = "airline" LIMIT 1;

{
…
"results": [ … ]
    "profile": {
        "phaseTimes": {
	    "authorize": "823.631µs",
	    "fetch": "656.873µs",
         ...
	},
        "phaseCounts": {
	    "fetch": 16,
	    "indexScan": 187
	},
        "phaseOperators": {
	    "authorize": 1,
	    "fetch": 1,
	    "indexScan": 2
	},
        "executionTimings": {
	    "#operator": "Sequence",
	    "#stats": {
	        "#phaseSwitches": 2,
	        "execTime": "1.079µs",
	        "kernTime": "16.889187ms"
	    },
	    "~children": [
	        {
	            "#operator": "Authorize",
	            "#stats": {
	                "#phaseSwitches": 4,
	                "execTime": "5.423µs",
	                "kernTime": "16.043784ms",
	                "servTime": "818.208µs"
	            },
			 ...
	            {
	                 "#operator": "Fetch",
	                 "#stats": {
	                                    "#itemsIn": 187,
	                                    "#itemsOut": 16,
	                                    "#phaseSwitches": 413,
	                                    "execTime": "128.434µs",
	                                    "kernTime": "15.027879ms",
	                                    "servTime": "1.590934ms",
	                                    "state": "services"
	                  },
	                  "keyspace": "travel-sample",
	                  "namespace": "default"
	            },
    ...
For the complete output, see Example 1 of Completed Request.
Attribute Meta in System Keyspaces
This is a new virtual attribute in document metadata introduced in Couchbase Server 5.0.

The meta().plan captures the whole query plan, and monitoring stats of various phases and involved query operators. The meta().plan must be explicitly called in the SELECT query projection list.

The meta().plan attribute is enabled only for individual requests that are running (active_requests) or completed (completed_requests) when the profile is set to timings (profile ="timings") for each individual request. If at the engine level, the profile is set to off and individual requests have been run with profile ="timings", then the system keyspaces will return the plan only for those requests.

Since there may be a combination of profile settings for all of the requests reported by the system keyspaces, not all requests returned will have a meta().plan attachment.

Note: For the system:prepareds requests, the meta().plan is available at all times since the PREPARE statement is not dependant on the profile setting.
This attribute is enabled for the following system keyspaces: For a detailed example, see Example 2.

N1QL Cluster Monitoring

Description

Couchbase Server allows you to monitor many aspects of an active cluster, and version 5.0 now has more cluster-aware operations, further diagnostics and more system keyspaces features that cover multiple nodes. New functionalities specifically include:

  • Ability to access active / completed / prepared requests across all N1QL nodes from N1QL.
  • Ability to list nodes by type and with status from N1QL.
  • Ability to list system keyspaces from system:keyspaces.
  • Extra fields in system:active_requests and completed_requests.
  • Extra counters to keep track of specific requests, such as cancelled requests.
  • Extend killing request to CREATE INDEX.
Changes to System Keyspaces
  • System:nodes show the datastore topology information. This is separate from the N1QL clustering view of the world, in that N1QL clustering shows a map of the N1QL cluster, as provided by the cluster manager, while system:nodes shows a view of the nodes and services that make up the actual datastore, which may or may not include N1QL nodes.
  • The dichotomy is important in that N1QL could be clustered by one entity (e.g. Zookeeper) and be connected to a clustered datastore (e.g. Couchbase) such that each does not have visibility of the other.
  • Should N1QL be extended to be able to concurrently connect to multiple datastores, each datastore will report its own topology, so that system:nodes offers a complete view of all the storage nodes, whatever those may be.
  • Ideally, system:nodes will provide a way to report services advertised by each node as well as services that are actually running.
  • This will be datastore dependent.
  • N1QL clustering will still be reported by the /admin endpoints.
  • System:keyspaces can be augmented to list system keyspaces with a static map. The small disadvantage of this is that it has to be maintained as new system keyspaces are added.
  • System:active_requests and system:completed_requests to report scan consistency.
  • System:prepareds to list min and max execution and service times, as well as average times.
cbq-engine-cbauth

(Introduced in Couchbase Server 5.0)

Cbq-engine-cbauth is a new internal user that ns_server uses to allow Query Workbench clients to query across multiple query nodes.

Since Query Workbench can connect to the same node only when cbq-engine is running, Query Workbench cannot do any query-clustered operations.

To get around this block, once the Query Workbench clients connect to a query node, this internal user (cbq-engine-cbauth) will be used to do any further inter-node user verification.

Vitals

The Vitals API provides data about the running state and health of the query engine, 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 engine, and hence load-balance the requests being sent to various query engines.

Get System Vitals
curl -u Administrator:pword http://localhost:8093/admin/vitals
{
    "uptime":"30h0m10.801209195s",
    "local.time":"2016-02-18 17:39:41.612724694 -0800 PST",
    "version":"1.0.0",
    "total.threads":225,
    "cores":8,
    "gc.num":11607200,
    "gc.pause.time":"1.849506783s",
    "gc.pause.percent":0,
    "memory.usage":10019216,
    "memory.total":5628336088,
    "memory.system":29055224,
    "cpu.user.percent":0,
    "cpu.sys.percent":0,
    "request.completed.count":111,
    "request.active.count":0,
    "request.per.sec.1min":0.0007,
    "request.per.sec.5min":0.0107,
    "request.per.sec.15min":0.0091,
    "request_time.mean":"170.870877ms",
    "request_time.median":"733.364µs",
    "request_time.80percentile":"21.85926ms",
    "request_time.95percentile":"1.274152266s",
    "request_time.99percentile":"1.588775188s",
    "request.prepared.percent":0
}         

system:active_requests

This catalog lists all currently executing active requests or queries.

Get Active Requests
To get profiling information about the query plan, use:
curl -u Administrator:pword http://localhost:8093/admin/active_requests
SELECT *, meta().plan FROM system:active_requests;
SELECT *, meta().plan FROM system:active_requests;
[
  {
    "active_requests": {
    "clientContextID": "e6e81ad9-09b6-4c98-987a-a80947cbf4cd",
    "elapsedTime": "22.361384ms",
    "executionTime": "22.336681ms",
    "node": "127.0.0.1:8091",
    "phaseCounts": {
    "primaryScan": 1
  },
  "phaseOperators": {
    "authorize": 1,
    "fetch": 1,
    "primaryScan": 1
  },
  "phaseTimes": {
    "authorize": "582.108µs",
    "fetch": "20.525µs",
    "instantiate": "27.358µs",
    "parse": "346.188µs",
    "plan": "31.911µs",
    "primaryScan": "18.043218ms"
  },
  "remoteAddr": "127.0.0.1:65430",
  "requestId": "6b0bff60-7d2c-459b-aafa-942c96c12770",
  "requestTime": "2017-08-08 09:44:07.401941318 -0700 PDT",
  "scanConsistency": "unbounded",
  "state": "running",
  "statement": "SELECT *, meta().plan FROM system:active_requests;",
  "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36 (Couchbase Query Workbench (5.0.1-4603-enterprise))",
  "users": "Administrator"
},
"plan": {                         // newly added feature
  "#operator": "Sequence",
  "#stats": {
  "#phaseSwitches": 1,
  "execTime": "1.102µs",
  "kernTime": "22.539451ms",
  "state": "kernel"
},
"~children": [
  {
    "#operator": "Authorize",
    "#stats": {
    "#phaseSwitches": 3,
    "execTime": "3.372µs",
    "kernTime": "21.961146ms",
    "servTime": "578.736µs",
    "state": "kernel"
  },
...plan": {
      "#operator": "Sequence",
      "#stats": {
        "#phaseSwitches": 1,
        "execTime": "13.638µs",
        "kernTime": "37.408896ms",
        "state": "kernel"
      },
      "~children": [
        {
          "#operator": "Authorize",
          "#stats": {
            "#phaseSwitches": 3,
            "execTime": "5.219µs",
            "kernTime": "35.954967ms",
            "servTime": "1.460405ms",
            "state": "kernel"
          },
          "privileges": {
            "List": [
              {
                "Priv": 4,
                "Target": "#system:active_requests"
              }
            ]
          },
          "~child": {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "2.273µs",
              "kernTime": "36.083024ms",
              "state": "kernel"
            },
            "~children": [
              {
                "#operator": "PrimaryScan",
                "#stats": {
                  "#itemsOut": 1,
                  "#phaseSwitches": 7,
                  "execTime": "18.137103ms",
                  "kernTime": "4.827µs",
                  "servTime": "16.576598ms"
                },
                "index": "#primary",
                "keyspace": "active_requests",
                "namespace": "#system",
                "using": "system"
              },
              {
                "#operator": "Fetch",
                "#stats": {
                  "#itemsIn": 1,
                  "#phaseSwitches": 7,
                  "execTime": "14.894µs",
                  "kernTime": "34.727264ms",
                  "servTime": "1.407022ms",
                  "state": "services"
                },
                "keyspace": "active_requests",
                "namespace": "#system"
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 1,
                  "execTime": "1.913µs",
                  "kernTime": "36.296279ms",
                  "state": "kernel"
                },
                "~children": [
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#phaseSwitches": 1,
                      "execTime": "12.445µs",
                      "kernTime": "36.282886ms",
                      "state": "kernel"
                    },
                    "result_terms": [
                      {
                        "expr": "self",
                        "star": true
                      },
                      {
                        "expr": "(meta(`active_requests`).`plan`)"
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject",
                    "#stats": {
                      "#phaseSwitches": 1,
                      "execTime": "17.767µs",
                      "kernTime": "36.332512ms",
                      "state": "kernel"
                    }
                  }
                ]
              }
            ]
          }
        },
        {
          "#operator": "Stream",
          "#stats": {
            "#phaseSwitches": 1,
            "execTime": "725ns",
            "kernTime": "37.932713ms",
            "state": "kernel"
          }
        }
      ]
    }
  }
]
In the above example, note the new plan section. It contains a tree of operators that combine to execute the N1QL query. The root operator is a Sequence, which itself has a collection of child operators like Authorize, PrimaryScan, Fetch, and possibly even more Sequences.
Option Description
clientContextID The opaque ID or context provided by the client.
elapsedTime 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.
errorCount Total number of errors encountered while executing the query.
executionTime The duration of the query from when it started executing to when it completed.
node IP address and port of the query engine node in the Couchbase Cluster.
remoteAddr IP address and port number of the client application, from where the query is received.
requestId Unique request ID internally generated for the query.
requestTime Timestamp when the query is received.
resultCount Total number of documents returned in the query result.
resultSize Total number of bytes returned in the query result.
scanConsistency The value of the query setting Scan Consistency used for the query.
serviceTime Total amount of calendar time taken to complete the query.
state The state of the query execution, such as completed, in progress, cancelled.
statement The N1QL query statement being executed.
userAgent Name of the client application or program that issued the query.
users Username with whose privileges the query is run.

For the profile related attributes, see Attribute profile in Query Response.

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.

DELETE http://localhost:8093/admin/active_requests/request_ID
DELETE FROM system:active_requests [ WHERE expression ]   

system:prepareds

This catalog provides data about the known prepared statements and their state in a query engine’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.

Create and Execute Prepared Statements

Create a prepared statement with the syntax PREPARE [ name ( FROM | AS ) ] statement.

Example 1 of Prepare: PrimaryScan PREPARE statement in cbq:
cbq> PREPARE example1 FROM SELECT * FROM `travel-sample` LIMIT 1 ;

Result:
{
  "requestID": "1c67946d-3759-4736-be61-ec6c9e6e522b",
  "signature": "json",
  "results": [
    {
      "encoded_plan": "H4sIAAAAAAAA/6SSQYsTQRCF/8rwvIX2kJPQnrwIwh4C8SZLtpipTEp7etrq6pC4jL9derIhZBQR99jMN6/ee1XP4NiOHXe7FCjCAw6RBoZHX85rOIyJlWxU+Ge8uT2w5e+FY8tw+NkeJHTKEf7LPfSh2GFU+VGppHKUwD3nKvUg2Wb8M2nPBo+O91SCeVM6cnibaUih/rdROcK/mx6n66T/svJP0EZlID1vW4pwkNjx6eJsly5f4PCNzzlRWytaWg0ySI2yfmnxyr1Eg0PJEvtabhZM7n74R7b28PcBfxJdymxIKQQOeFVdn6KYUNjo+JXb6lw5l2A7Yx3yTPMpVTBz2MMhGym8aeG6qEUwiTel6XH6nXiYi3NXzfVMLZitKdOA+Q6y9JGsKNdsK3isMDkYn2r7STmRclMPuNnrODSZA7fWrC6vp7tWn5p5a836PaZfAQAA//8FwAdzDgMAAA==",
      "name": "guy1",
      "operator": {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "Authorize",
            "privileges": {
              "List": [
                {
                  "Priv": 7,
                  "Target": "default:travel-sample"
                }
              ]
            },
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Sequence",
                  "~children": [
                    {
                      "#operator": "PrimaryScan",
                      "index": "def_primary",
                      "keyspace": "travel-sample",
                      "limit": "1",
                      "namespace": "default",
                      "using": "gsi"
                    },
                    {
                      "#operator": "Fetch",
                      "keyspace": "travel-sample",
                      "namespace": "default"
                    },
                    {
                      "#operator": "Parallel",
                      "~child": {
                      "#operator": "Sequence",
                      "~children": [
                        {
                          "#operator": "InitialProject",
                          "result_terms": [
                            {
                              "expr": "self",
                              "star": true
                            }
                          ]
                        },
                        {
                          "#operator": "FinalProject"
                        }
                      ]
                    }
                  }
                ]
              },
              { 
                "#operator": "Limit",
                "expr": "1"
              }
            ]
          }
        },
        {
          "#operator": "Stream"
        }
      ]
    },
    "signature": {
      "*": "*"
    },
    "text": "prepare guy1 from select * from `travel-sample` limit 1;"
  }
],
"status": "success",
"metrics": {
"elapsedTime": "1.550053ms",
"executionTime": "1.534731ms",
"resultCount": 1,
"resultSize": 3922
}
}
Then execute the prepared statement in cbq:
cbq> execute example1;
{
    "requestID": "54eca299-3798-47e3-b53e-b53002b56315",
    "signature": null,
    "results": [
        {
            "travel-sample": {
                "callsign": "MILE-AIR",
                "country": "United States",
                "iata": "Q5",
                "icao": "MLA",
                "id": 10,
                "name": "40-Mile Air",
                "type": "airline"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "7.255525ms",
        "executionTime": "7.226054ms",
        "resultCount": 1,
        "resultSize": 300
    }
}
Example 2 of Prepare: SecondaryScan PREPARE statement with WHERE clause, created by cbq:
cbq> PREPARE example2 FROM SELECT * FROM `travel-sample` LIMIT 1 ;

Result:
cbq> PREPARE example2 FROM SELECT * FROM `travel-sample` WHERE signature="json" {IMIT 1 ;
    "requestID": "9d0d35aa-1708-4fa9-ac6c-78abecf5d929",
    "signature": "json",
    "results": [
        {
            "encoded_plan": "H4sIAAAAAAAA/6STT2vbQBDFv8ryeknMthAoFLbkEIpMDQ4VtqGHJtiLNJEnXa3U2ZVxGtTPXlaOE6xc+ue4zI/33jyNHkG+aEoq162zHgbQ8LYmGNDe1q2j99BoWhIbG4F5xJuXB5b0oyNfEDR+FVt2pZCH+XYKXXVx2wj/TFQrvGNHFYUkNecQB3xlpaIIg5LubOeiiWJ35N6GIQE0cuEdzIf+tj86/VOUP4Jy4drKw7KwHhrsS9ofkq3bwwQa3+khtLZINY2jpvaOs6d1oNEF9hUMqsDo9anhlGKx/XvRsUxuxTpHDv9V0ZRdJIFG0fiSIzfpKM7ONieJNu82gStvYye0OVeX6gb3ofE3OH+VauY5snW5NPdUpCaEQufiOpLUYXCnfZvAQO4OGiFagYnSUfrY42z+Ram/7V8Tc645mTxpXgzUiFlGIVtjuKXnLVJXExhM0GtE2qdjzBdZfrXI1PFHUNPFl2u1zObZp5WaHF6jXtTXz9kiU8+yl8di1Hx2PVupi4/ofwcAAP//rRBGTXMDAAA=",
            "name": "example4",
            "operator": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Authorize",
                        "privileges": {
                            "List": [
                                {
                                    "Priv": 7,
                                    "Target": "default:travel-sample"
                                }
                            ]
                        },
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "Sequence",
                                    "~children": [
                                        {
                                            "#operator": "PrimaryScan",
                                            "index": "def_primary",
                                            "keyspace": "travel-sample",
                                            "namespace": "default",
                                            "using": "gsi"
                                        },
                                        {
                                            "#operator": "Fetch",
                                            "keyspace": "travel-sample",
                                            "namespace": "default"
                                        },
                                        {
                                            "#operator": "Parallel",
                                            "~child": {
                                                "#operator": "Sequence",
                                                "~children": [
                                                    {
                                                        "#operator": "Filter",
                                                        "condition": "((`travel-sample`.`signature`) = \"json\")"
                                                    },
                                                    {
                                                        "#operator": "InitialProject",
                                                        "result_terms": [
                                                            {
                                                                "expr": "self",
                                                                "star": true
                                                            }
                                                        ]
                                                    },
                                                    {
                                                        "#operator": "FinalProject"
                                                    }
                                                ]
                                            }
                                        }
                                    ]
                                },
                                {
                                    "#operator": "Limit",
                                    "expr": "1"
                                }
                            ]
                        }
                    },
                    {
                        "#operator": "Stream"
                    }
                ]
            },
            "signature": {
                "*": "*"
            },
            "text": "PREPARE example2 FROM SELECT * FROM `travel-sample` WHERE signature=\"json\" LIMIT 1;"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "3.06242ms",
        "executionTime": "3.04637ms",
        "resultCount": 1,
        "resultSize": 4271
    }
}
Get Prepared Statements
To get a list of all known prepared statements, you can use REST or a Query statement:
curl -u Administrator:pword http://localhost:8093/admin/prepareds
SELECT * FROM system:preparedsprojection-list-expression FROM system:prepareds 
              [ WHERE predicate-expression ]
To get information about a specific prepared statement p1, you can use REST or a Query statement:
curl -u Administrator:pword http://localhost:8093/admin/prepareds/p1prepared-statement-name
Example 1 of Get Prepared
PREPARE p1 AS SELECT * FROM default WHERE foo = 42;


Result:
{
  "requestID": "87520590-8932-4be8-8d08-6bf4a0aad0d5",
  "signature": "json",
  "results": [
    {
      "encoded_plan": "H4sIAAAAAAAA/6RSXWsUMRT9K+H0pV2CUBGEiA++FAQfFtY3Kd0wc2Y2mknizZ26tYy/XbKuLN0+aSEQcjmcj5z7CKYu9+zvSvQJDrBIfiIcyjUscqF4zQL3iIvTAxt+n5k6wuJXtwuxFya4L09BH2bdZQk/G6pIuA+RI2uj+hSqHuCfvYxUOPQc/BzVHW9YrCXcw71dbpe/Gv9lYi1h8vKw6XyCRUg993C4KH/GsPjGh1p8x5OJ4x88n841pBEOYw1Y7FOdG2q3+xe6c4K1Fx8jI14U9yZEpcCiy6kPGnIr9fJye1TdvtoOOW+vzHvz5vXVMw8fU9Dg41ryV3bNuLDOUe+UMtWDFvelASvjAIuqXuBUZraazp2kE9Nyu7RzBtmo0E84VFzDmLzOwhZ7BYcVFgvlvm1HERYvNOXa+GoqIzs1KzNInswxmfmxo9AMOR+yvcPyOwAA//+dg3Nd3AIAAA==",
      "name": "p1",
      "operator": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Authorize",
            "privileges": {
              "List": [
                {
                  "Priv": 7,
                  "Target": "default:default"
                }
              ]
            },
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "PrimaryScan",
                  "index": "#primary",
                  "keyspace": "default",
                  "namespace": "default",
                  "using": "gsi"
                },
                {
                  "#operator": "Fetch",
                  "keyspace": "default",
                  "namespace": "default"
                },
                {
                  "#operator": "Parallel",
                  "~child": {
                    "#operator": "Sequence",
                    "~children": [
                      {
                        "#operator": "Filter",
                        "condition": "((`default`.`foo`) = 42)"
                      },
                      {
                        "#operator": "InitialProject",
                        "result_terms": [
                          {
                            "expr": "self",
                            "star": true
                          }
                        ]
                      },
                      {
                        "#operator": "FinalProject"
                      }
                    ]
                  }
                }
              ]
            }
          },
          {
            "#operator": "Stream"
          }
        ] 
      },
      "signature": {
        "*": "*"
      },
      "text": "prepare p1 as select * from default where foo = 42;"
    }
  ],
  "status": "success",
  "metrics": {
    "elapsedTime": "34.712303ms",
    "executionTime": "34.68522ms",
    "resultCount": 1,
    "resultSize": 3443
  }
}


SELECT *, meta().plan FROM system:prepareds;
{
  "requestID": "e37ab60b-b616-478f-8cf3-cdc16e39283d",
  "signature": {
    "*": "*",
    "plan": "json"
  },
  "results": [
    {
      "plan": {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "Authorize",
            "privileges": {
              "List": [
                {
                  "Priv": 7,
                  "Target": "default:default"
                }
              ]
            },
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "PrimaryScan",
                  "index": "#primary",
                  "keyspace": "default",
                  "namespace": "default",
                  "using": "gsi"
                },
                {
                  "#operator": "Fetch",
                  "keyspace": "default",
                  "namespace": "default"
                },
                {
                  "#operator": "Parallel",
                  "~child": {
                    "#operator": "Sequence",
                    "~children": [
                      {
                        "#operator": "Filter",
                        "condition": "((`default`.`foo`) = 42)"
                      },
                      {
                        "#operator": "InitialProject",
                        "result_terms": [
                          {
                            "expr": "self",
                            "star": true
                          }
                        ]
                      },
                      {
                        "#operator": "FinalProject"
                      }
                    ]
                  }
                }
              ]
            }
          },
          {
            "#operator": "Stream"
          }
        ]
      },
      "prepareds": {
        "encoded_plan": "H4sIAAAAAAAA/6RSXWsUMRT9K+H0pV2CUBGEiA++FAQfFtY3Kd0wc2Y2mknizZ26tYy/XbKuLN0+aSEQcjmcj5z7CKYu9+zvSvQJDrBIfiIcyjUscqF4zQL3iIvTAxt+n5k6wuJXtwuxFya4L09BH2bdZQk/G6pIuA+RI2uj+hSqHuCfvYxUOPQc/BzVHW9YrCXcw71dbpe/Gv9lYi1h8vKw6XyCRUg993C4KH/GsPjGh1p8x5OJ4x88n841pBEOYw1Y7FOdG2q3+xe6c4K1Fx8jI14U9yZEpcCiy6kPGnIr9fJye1TdvtoOOW+vzHvz5vXVMw8fU9Dg41ryV3bNuLDOUe+UMtWDFvelASvjAIuqXuBUZraazp2kE9Nyu7RzBtmo0E84VFzDmLzOwhZ7BYcVFgvlvm1HERYvNOXa+GoqIzs1KzNInswxmfmxo9AMOR+yvcPyOwAA//+dg3Nd3AIAAA==",
        "name": "p1",
        "node": "127.0.0.1:8091",
        "statement": "prepare p1 as select * from default where foo = 42;",
        "uses": 0
      }
    }
  ],
  "status": "success",
  "metrics": {
    "elapsedTime": "122.056934ms",
    "executionTime": "122.03689ms",
    "resultCount": 1,
    "resultSize": 3499
  }
}
Delete Prepared Statement
The DELETE command can be used to delete a specific p1 prepared statement:
DELETE http://localhost:8093/admin/prepareds/p1prepared-statement-name
DELETE FROM system:prepareds 
WHERE name = "p1";
To delete all the known prepared statements, use
DELETE http://localhost:8093/admin/prepareds

system:completed_requests

This catalog maintains a list of the most recent completed requests that have run longer than a predefined threshold of time. 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 engine and the cluster. Note that most field names and meanings match exactly those of system:active_requests.

Note: Couchbase Server version 5.0 has an extra feature which affects completed_requests: request profiling in the following ways:
  • When the feature is turned on, we will store the execution plan with timings to completed requests.
  • Profiling information is likely to use 100KB+ per entry.
  • Due to the added overhead of running both profiling and logging, we recommend turning 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 complete_requests, so it's fine to run it continuously.
Get Completed Requests
To get profiling information about the query plan, use:
curl -u Administrator:pword http://localhost:8093/admin/completed_requests
SELECT *, meta().plan FROM system:completed_requests;
Select *, meta().plan from system:completed_requests LIMIT 1;
[
  {
    "completed_requests": {
      "clientContextID": "9b79305d-b67c-4b04-8312-978c9c77f3b8",
      "elapsedTime": "3.18228764s",
      "errorCount": 0,
      "node": "192.168.1.7",
      "phaseCounts": {
        "fetch": 23568,
        "primaryScan": 24139
      },
      "phaseOperators": {
        "fetch": 1,
        "primaryScan": 1
      },
      "phaseTimes": {
        "authorize": "1.26817ms",
        "fetch": "758.740363ms",
        "instantiate": "55.679µs",
        "parse": "713.903µs",
        "plan": "489.134µs",
        "primaryScan": "245.557631ms"
      },
      "requestId": "91a651d6-a782-4e43-a249-2f54c55b2553",
      "resultCount": 19477,
      "resultSize": 60651657,
      "scanConsistency": "unbounded",
      "serviceTime": "3.182261202s",
      "state": "closed",
      "statement": "select * from `travel-sample`",
      "time": "2017-02-27 11:02:59.008480274 -0800 PST"
    },
    "plan": {
      "#operator": "Sequence",
      "#stats": {
        "#phaseSwitches": 2,
        "execTime": "4.613µs",
        "kernTime": "3.181146983s"
      },
      "~children": [
        {
          "#operator": "Authorize",
          "#stats": {
            "#phaseSwitches": 4,
            "execTime": "3.717µs",
            "kernTime": "3.17986639s",
            "servTime": "1.264453ms"
          },
          "privileges": {
            "List": [
              {
                "Priv": 1,
                "Target": "default:travel-sample"
              },
              {
                "Priv": 7,
                "Target": "default:travel-sample"
              }
            ]
          },
          "~child": {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 3,
              "execTime": "2.741µs",
              "kernTime": "3.179859494s"
            },
            "~children": [
              {
                "#operator": "PrimaryScan",
                "#stats": {
                  "#itemsOut": 24593,
                  "#phaseSwitches": 98377,
                  "execTime": "24h49m9.233250854s",
                  "kernTime": "2.960383961s",
                  "servTime": "136.104883ms",
                  "state": "running"
                },
                "index": "def_primary",
                "keyspace": "travel-sample",
                "namespace": "default",
                "using": "gsi"
              },
              {
                "#operator": "Fetch",
                "#stats": {
                  "#itemsIn": 23569,
                  "#itemsOut": 22544,
                  "#phaseSwitches": 92279,
                  "execTime": "89.328839ms",
                  "kernTime": "2.417774851s",
                  "servTime": "700.949543ms"
                },
                "keyspace": "travel-sample",
                "namespace": "default"
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 5,
                  "execTime": "8.647µs",
                  "kernTime": "3.179847023s"
                },
                "~children": [
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#itemsIn": 21521,
                      "#itemsOut": 21520,
                      "#phaseSwitches": 86087,
                      "execTime": "20.284388ms",
                      "kernTime": "3.159569346s"
                    },
                    "result_terms": [
                      {
                        "expr": "self",
                        "star": true
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject",
                    "#stats": {
                      "#itemsIn": 20496,
                      "#itemsOut": 20496,
                      "#phaseSwitches": 81991,
                      "execTime": "11.143936ms",
                      "kernTime": "3.168705526s"
                    }
                  }
                ]
              }
            ]
          }
        },
        {
          "#operator": "Stream",
          "#stats": {
            "#itemsIn": 19478,
            "#itemsOut": 19477,
            "#phaseSwitches": 38961,
            "execTime": "6.789206ms",
            "kernTime": "3.174331007s"
          }
        }
      ]
    }
  }
]
Purging the Completed Requests
To purge the completed requests for a given time period, use
DELETE FROM system:completed_requests 
requests WHERE requests.time LIKE "2015-09-09%";requests 
              WHERE requests.Time LIKE "2015-09-09%";

Configuring the system:completed_requests keyspace

You can configure the system:completed_requests keyspace by specifying the parameters as command line options for the cbq-engine.
  • completed-threshold: Sets the minimum request duration after which requests are added to the system:completed_requests catalog. The default value is 1000ms. Specify 0 to log all requests and -1 to not log any requests to the keyspace.
    To specify a different value, use:
    curl http://localhost:port/admin/settings -d '{"completed-threshold":0}' -u user:pword
  • completed-limit: Sets the number of most recent requests to be tracked in the system:completed_requests catalog. The default value is 4000. Specify 0 to not track any requests and -1 to set no limit.
    To specify a different value, use:
    curl http://localhost:port/admin/settings -d '{"completed-limit":1000}' -u user:pword
You can also set these parameters through the Admin API settings endpoint:
curl -X POST 'http://localhost:8093/admin/settings'  -u Administrator:pword -d '{ ... }'
The JSON parameter accepts two new fields: completed-threshold and completed-limit.

Example 1 of Completed Request: Set the profile to timings

cbq> \set -profile "timings";
cbq> SELECT * FROM `travel-sample` WHERE type = "airline" LIMIT 1;
{
    "requestID": "874a6c27-b514-42c7-b057-caf07067db65",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "travel-sample": {
                "callsign": null,
                "country": "United States",
                "iata": null,
                "icao": "XSR",
                "id": 18257,
                "name": "Executive AirShare",
                "type": "airline"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "29.855421ms",
        "executionTime": "29.838097ms",
        "resultCount": 1,
        "resultSize": 304
    },
    "profile": {
        "phaseTimes": {
	    "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"
	},
        "phaseCounts": {
	    "fetch": 16,
	    "indexScan": 187
	},
        "phaseOperators": {
	    "authorize": 1,
	    "fetch": 1,
	    "indexScan": 2
	},
        "executionTimings": {
	    "#operator": "Sequence",
	    "#stats": {
	        "#phaseSwitches": 2,
	        "execTime": "1.079µs",
	        "kernTime": "16.889187ms"
	    },
	    "~children": [
	        {
	            "#operator": "Authorize",
	            "#stats": {
	                "#phaseSwitches": 4,
	                "execTime": "5.423µs",
	                "kernTime": "16.043784ms",
	                "servTime": "818.208µs"
	            },
	            "privileges": {
	                "List": [
	                    {
	                        "Target": "default:travel-sample",
	                        "Priv": 1
	                    },
	                    {
	                        "Target": "default:travel-sample",
	                        "Priv": 7
	                    }
	                ]
	            },
	            "~child": {
	                "#operator": "Sequence",
	                "#stats": {
	                    "#phaseSwitches": 3,
	                    "execTime": "2.694µs",
	                    "kernTime": "16.036719ms"
	                },
	                "~children": [
	                    {
	                        "#operator": "Sequence",
	                        "#stats": {
	                            "#phaseSwitches": 2,
	                            "execTime": "1.418µs",
	                            "kernTime": "16.028217ms"
	                        },
	                        "~children": [
	                            {
	                                "#operator": "IntersectScan",
	                                "#stats": {
	                                    "#itemsIn": 187,
	                                    "#itemsOut": 187,
	                                    "#phaseSwitches": 749,
	                                    "execTime": "449.944µs",
	                                    "kernTime": "14.625524ms"
	                                },
	                                "limit": "1",
	                                "scans": [
	                                    {
	                                        "#operator": "IndexScan",
	                                        "#stats": {
	                                            "#phaseSwitches": 3,
	                                            "execTime": "160.085µs",
	                                            "kernTime": "14.464239ms"
	                                        },
	                                        "index": "def_type",
	                                        "index_id": "7cc0b964c9b812d2",
	                                        "keyspace": "travel-sample",
	                                        "namespace": "default",
	                                        "spans": [
	                                            {
	                                                "Exact": true,
	                                                "Range": {
	                                                    "High": [
	                                                        "\"airline\""
	                                                    ],
	                                                    "Inclusion": 3,
	                                                    "Low": [
	                                                        "\"airline\""
	                                                    ]
	                                                }
	                                            }
	                                        ],
	                                        "using": "gsi",
	                                        "~children": [
	                                            {
	                                                "#operator": "IndexScan",
	                                                "#stats": {
	                                                    "#itemsOut": 187,
	                                                    "#phaseSwitches": 753,
	                                                    "execTime": "164.676µs",
	                                                    "kernTime": "64.172µs",
	                                                    "servTime": "14.228826ms"
	                                                },
	                                                "index": "def_type",
	                                                "index_id": "7cc0b964c9b812d2",
	                                                "keyspace": "travel-sample",
	                                                "namespace": "default",
	                                                "spans": [
	                                                    {
	                                                        "Exact": true,
	                                                        "Range": {
	                                                            "High": [
	                                                                "\"airline\""
	                                                            ],
	                                                            "Inclusion": 3,
	                                                            "Low": [
	                                                                "\"airline\""
	                                                            ]
	                                                        }
	                                                    }
	                                                ],
	                                                "using": "gsi"
	                                            }
	                                        ]
	                                    },
	                                    {
	                                        "#operator": "DistinctScan",
	                                        "#stats": {
	                                            "#phaseSwitches": 4,
	                                            "execTime": "15.725µs",
	                                            "kernTime": "20.232µs",
	                                            "servTime": "14.597529ms"
	                                        },
	                                        "scan": {
	                                            "#operator": "IndexScan",
	                                            "#stats": {
	                                                "#phaseSwitches": 3,
	                                                "execTime": "113.002µs",
	                                                "kernTime": "14.486802ms"
	                                            },
	                                            "index": "univ_idx2",
	                                            "index_id": "75533bc1a5efe19e",
	                                            "keyspace": "travel-sample",
	                                            "namespace": "default",
	                                            "spans": [
	                                                {
	                                                    "Exact": true,
	                                                    "Range": {
	                                                        "High": [
	                                                            "[\"type\", \"airline\"]"
	                                                        ],
	                                                        "Inclusion": 3,
	                                                        "Low": [
	                                                            "[\"type\", \"airline\"]"
	                                                        ]
	                                                    }
	                                                }
	                                            ],
	                                            "using": "gsi",
	                                            "~children": [
	                                                {
	                                                    "#operator": "IndexScan",
	                                                    "#stats": {
	                                                        "#phaseSwitches": 4,
	                                                        "execTime": "18.384µs",
	                                                        "kernTime": "1.681µs",
	                                                        "servTime": "14.46157ms"
	                                                    },
	                                                    "index": "univ_idx2",
	                                                    "index_id": "75533bc1a5efe19e",
	                                                    "keyspace": "travel-sample",
	                                                    "namespace": "default",
	                                                    "spans": [
	                                                        {
	                                                            "Exact": true,
	                                                            "Range": {
	                                                                "High": [
	                                                                    "[\"type\", \"airline\"]"
	                                                                ],
	                                                                "Inclusion": 3,
	                                                                "Low": [
	                                                                    "[\"type\", \"airline\"]"
	                                                                ]
	                                                            }
	                                                        }
	                                                    ],
	                                                    "using": "gsi"
	                                                }
	                                            ]
	                                        }
	                                    }
	                                ]
	                            },
	                            {
	                                "#operator": "Fetch",
	                                "#stats": {
	                                    "#itemsIn": 187,
	                                    "#itemsOut": 16,
	                                    "#phaseSwitches": 413,
	                                    "execTime": "128.434µs",
	                                    "kernTime": "15.027879ms",
	                                    "servTime": "1.590934ms",
	                                    "state": "services"
	                                },
	                                "keyspace": "travel-sample",
	                                "namespace": "default"
	                            },
	                            {
	                                "#operator": "Sequence",
	                                "#stats": {
	                                    "#phaseSwitches": 5,
	                                    "execTime": "10.29µs",
	                                    "kernTime": "16.009655ms"
	                                },
	                                "~children": [
	                                    {
	                                        "#operator": "Filter",
	                                        "#stats": {
	                                            "#itemsIn": 16,
	                                            "#itemsOut": 16,
	                                            "#phaseSwitches": 69,
	                                            "execTime": "284.572µs",
	                                            "kernTime": "15.693579ms"
	                                        },
	                                        "condition": "((`travel-sample`.`type`) = \"airline\")"
	                                    },
	                                    {
	                                        "#operator": "InitialProject",
	                                        "#stats": {
	                                            "#itemsIn": 16,
	                                            "#itemsOut": 16,
	                                            "#phaseSwitches": 69,
	                                            "execTime": "7.202µs",
	                                            "kernTime": "15.984416ms"
	                                        },
	                                        "result_terms": [
	                                            {
	                                                "expr": "self",
	                                                "star": true
	                                            }
	                                        ]
	                                    },
	                                    {
	                                        "#operator": "FinalProject",
	                                        "#stats": {
	                                            "#itemsIn": 16,
	                                            "#itemsOut": 16,
	                                            "#phaseSwitches": 71,
	                                            "execTime": "24.641µs",
	                                            "kernTime": "15.980887ms"
	                                        }
	                                    }
	                                ]
	                            }
	                        ]
	                    },
	                    {
	                        "#operator": "Limit",
	                        "#stats": {
	                            "#itemsIn": 2,
	                            "#itemsOut": 1,
	                            "#phaseSwitches": 11,
	                            "execTime": "5.884µs",
	                            "kernTime": "16.014442ms"
	                        },
	                        "expr": "1"
	                    }
	                ]
	            }
	        },
	        {
	            "#operator": "Stream",
	            "#stats": {
	                "#itemsIn": 1,
	                "#itemsOut": 1,
	                "#phaseSwitches": 9,
	                "execTime": "1.788µs",
	                "kernTime": "16.883589ms"
	            }
	        }
	    ]
	}
    }
}

Example 2: Completed Request example

Query 1 - First, we set profile = "timings" and run a long query which takes at least 1000ms (the default value of the completed-threshold query setting) to get registered in thesystem:completed_requests keyspace:

cbq> \set -profile "timings";
cbq> SELECT * FROM `travel-sample` ORDER BY name;
Query 2 - Now, we change the profile setting to “phases” and rerun another long query:
cbq> \set -profile "phases";
cbq> SELECT * FROM `travel-sample` ORDER BY title;
Query 3 - Run a query system:completed_requests keyspace with meta().plan. The following example shows:
  • meta().plan with all detailed statistics collected for Query 1.

  • Only profile attribute with all phases-related statistics for the Query 2.

  • The profile attribute with all phases-related statistics for this query itself (which is querying the system:completed_requests keyspace)

    cbq> SELECT meta().plan, * from system:completed_requests;
    {
        "requestID": "33ed0271-6f30-48e0-9932-eea97b3d41fa",
        "signature": {
            "*": "*",
            "plan": "json"
        },
        "results": [
            {
                "completed_requests": {
                    "elapsedTime": "6.404801894s",
                    "errorCount": 0,
                    "node": "10.17.5.233",
                    "phaseCounts": {
                        "fetch": 31592,
                        "primaryScan": 31592,
                        "sort": 31592
                    },
                    "phaseOperators": {
                        "authorize": 1,
                        "fetch": 1,
                        "primaryScan": 1,
                        "sort": 1
                    },
                    "phaseTimes": {
                        "authorize": "576.717µs",
                        "fetch": "605.983982ms",
                        "instantiate": "625.544µs",
                        "parse": "262.266µs",
                        "plan": "254.921µs",
                        "primaryScan": "64.168512ms",
                        "run": "6.402307626s",
                        "sort": "432.85835ms"
                    },
                    "remoteAddr": "192.168.0.109:51540",
                    "requestId": "bd0cfa57-2189-4f22-93d5-ddeca3325c3f",
                    "resultCount": 31592,
                    "resultSize": 107818622,
                    "scanConsistency": "unbounded",
                    "serviceTime": "6.40478582s",
                    "state": "completed",
                    "statement": "select * from `travel-sample` order by name;",
                    "time": "2017-03-10 20:19:02.495406865 -0800 PST",
                    "userAgent": "Go-http-client/1.1",
                    "users": "Administrator"
                },
    
                // Query1 results (begin) ––––––––––––––––––––––––––--
                "plan": {
                    "#operator": "Sequence",
                    "#stats": {
                        "#phaseSwitches": 2,
                        "execTime": "849ns",
                        "kernTime": "6.402305271s"
                    },
                    "~children": [
                        {
                            "#operator": "Authorize",
                            "#stats": {
                                "#phaseSwitches": 4,
                                "execTime": "3.452µs",
                                "kernTime": "6.401387075s",
                                "servTime": "573.265µs"
                            },
                            "privileges": {
                                "List": [
                                    {
                                        "Priv": 1,
                                        "Target": "default:travel-sample"
                                    },
                                    {
                                        "Priv": 7,
                                        "Target": "default:travel-sample"
                                    }
                                ]
                            },
                            "~child": {
                                "#operator": "Sequence",
                                "#stats": {
                                    "#phaseSwitches": 3,
                                    "execTime": "2.667µs",
                                    "kernTime": "6.401379511s"
                                },
                                "~children": [
                                    {
                                        "#operator": "Sequence",
                                        "#stats": {
                                            "#phaseSwitches": 2,
                                            "execTime": "3.757µs",
                                            "kernTime": "638.158784ms"
                                        },
                                        "~children": [
                                            {
                                                "#operator": "PrimaryScan",
                                                "#stats": {
                                                    "#itemsOut": 31592,
                                                    "#phaseSwitches": 126371,
                                                    "execTime": "43.239957ms",
                                                    "kernTime": "557.221483ms",
                                                    "servTime": "20.928555ms"
                                                },
                                                "index": "def_primary",
                                                "keyspace": "travel-sample",
                                                "namespace": "default",
                                                "using": "gsi"
                                            },
                                            {
                                                "#operator": "Fetch",
                                                "#stats": {
                                                    "#itemsIn": 31592,
                                                    "#itemsOut": 31592,
                                                    "#phaseSwitches": 126499,
                                                    "execTime": "37.4027ms",
                                                    "kernTime": "32.132249ms",
                                                    "servTime": "568.581282ms"
                                                },
                                                "keyspace": "travel-sample",
                                                "namespace": "default"
                                            },
                                            {
                                                "#operator": "Sequence",
                                                "#stats": {
                                                    "#phaseSwitches": 5,
                                                    "execTime": "2.14µs",
                                                    "kernTime": "638.143448ms"
                                                },
                                                "~children": [
                                                    {
                                                        "#operator": "InitialProject",
                                                        "#stats": {
                                                            "#itemsIn": 31592,
                                                            "#itemsOut": 31592,
                                                            "#phaseSwitches": 126375,
                                                            "execTime": "3.887302ms",
                                                            "kernTime": "634.243391ms"
                                                        },
                                                        "result_terms": [
                                                            {
                                                                "expr": "self",
                                                                "star": true
                                                            }
                                                        ]
                                                    }
                                                ]
                                            }
                                        ]
                                    },
                                    {
                                        "#operator": "Order",
                                        "#stats": {
                                            "#itemsIn": 31592,
                                            "#itemsOut": 31592,
                                            "#phaseSwitches": 126373,
                                            "execTime": "432.85835ms",
                                            "kernTime": "5.925425699s"
                                        },
                                        "sort_terms": [
                                            {
                                                "expr": "(`travel-sample`.`name`)"
                                            }
                                        ]
                                    },
                                    {
                                        "#operator": "FinalProject",
                                        "#stats": {
                                            "#itemsIn": 31592,
                                            "#itemsOut": 31592,
                                            "#phaseSwitches": 126375,
                                            "execTime": "16.142571ms",
                                            "kernTime": "6.385221481s"
                                        }
                                    }
                                ]
                            }
                        },
                        {
                            "#operator": "Stream",
                            "#stats": {
                                "#itemsIn": 31592,
                                "#itemsOut": 31592,
                                "#phaseSwitches": 63191,
                                "execTime": "4.397577ms",
                                "kernTime": "6.397903458s"
                            }
                        }
                    ]
                }
            },
            // Query1 results (end) ––––––––––––––––––––––––––--
    
            // Query2 results (begin) ––––––––––––––––––––––––––--
            {
                "completed_requests": {
                    "elapsedTime": "6.404801894s",
                    "errorCount": 0,
                    "node": "192.168.0.109",
                    "phaseCounts": {
                        "fetch": 31592,
                        "primaryScan": 31592,
                        "sort": 31592
                    },
                    "phaseOperators": {
                        "authorize": 1,
                        "fetch": 1,
                        "primaryScan": 1,
                        "sort": 1
                    },
                    "remoteAddr": "192.168.0.109:51540",
                    "requestId": "bd0cfa57-2189-4f22-93d5-ddeca3325c3f",
                    "requestTime": "2017-03-10T20:19:02.495406865-08:00",
                    "resultCount": 31592,
                    "resultSize": 107818622,
                    "scanConsistency": "unbounded",
                    "serviceTime": "6.40478582s",
                    "state": "completed",
                    "statement": "select * from `travel-sample` order by name;",
                    "userAgent": "Go-http-client/1.1",
                    "users": "Administrator"
                }
            }
           // Query2 results (end) ––––––––––––––––––––––––––--
    
        ],
        "status": "success",
        "metrics": {
            "elapsedTime": "74.124629ms",
            "executionTime": "74.112155ms",
            "resultCount": 4,
            "resultSize": 14608
        },
        "profile": {
            "phaseTimes": {
    	    "authorize": "551.089µs",
    	    "fetch": "34.260218ms",
    	    "instantiate": "20.455µs",
    	    "parse": "297.811µs",
    	    "plan": "36.651µs",
    	    "primaryScan": "35.703598ms",
    	    "run": "70.693651ms"
    	},
            "phaseCounts": {
    	    "fetch": 4,
    	    "primaryScan": 4
    	},
            "phaseOperators": {
    	    "authorize": 1,
    	    "fetch": 1,
    	    "primaryScan": 1
    	}
       }
     }                  

system:my_user_info

This catalog maintains a list of all information of your profile.

To see your current information, use:
SELECT * FROM system:my_user_info;
This will result in a list similar to:
[
  {
    "my_user_info": {
      "domain": "ns_server",
      "id": "Administrator",
      "name": "Administrator",
      "roles": [
        {
          "role": "admin"
        }
      ]
    }
  }
]

system:user_info

This catalog maintains a list of all current users in your bucket and their information.

To see the list of all current users, use:
SELECT * FROM system:user_info;
This will result in a list similar to:
[
  {
    "user_info": {
      "domain": "local",
      "id": "anil",
      "name": "Anil",
      "roles": [
        {
          "role": "replication_admin"
        },
        {
          "bucket_name": "travel-sample",
          "role": "select"
        },
        {
          "bucket_name": "*",
          "role": "select"
        }
      ]
    }
  },
  {
    "user_info": {
      "domain": "ns_server",
      "id": "Administrator",
      "name": "Administrator",
      "roles": [
        {
          "role": "admin"
        }
      ]
    }
  }
]

system:applicable_roles

This catalog maintains a list of all applicable roles and grantee of each bucket.

To see the list of all current applicable role information, use:
SELECT * FROM system:applicable_roles;
This will result in a list similar to:
[
  {
    "applicable_roles": {
      "grantee": "anil",
      "role": "replication_admin"
    }
  },
  {
    "applicable_roles": {
      "bucket_name": "travel-sample",
      "grantee": "anil",
      "role": "select"
    }
  },
  {
    "applicable_roles": {
      "bucket_name": "*",
      "grantee": "anil",
      "role": "select"
    }
  }
]