Hi Team,
I am using the query below to check if it utilizes CBO, but the information is not displaying. Additionally, the query returns a maximum of two output. How can I view all the completed NQL-related requests from the CB UI?
SELECT statement, useCBO, n1qlFeatCtrl, requestTime, userAgent, users
FROM system:completed_requests where LOWER(statement) LIKE “%select%”
ORDER BY requestTime DESC
LIMIT 15;
Could you please assist me.
Thanks,
Debasis
Not all queries are recorded. There is a threshold. I don’t know what you mean by “the information is not displaying”. Are you perhaps specifying property names that don’t exist? Why not just “select * …”.
Also - not related to completed_sessions, but predicates of “like ‘%…’ are expensive as they have to evaluate every entry. Why not 'like 'select%’? That will only need to evaluate the range of entries beginning with ‘select’. (Yes it’s possible that there are statements that have a blank or a tab before ‘select’. Possible, but not likely).
Addition to @mreiche
system:active_requests, system:completed_requests
useCBO field appears only when it is true, if false it will not appear(like missing) save resources and transportation
useCBO : true only indicates value is set. doesn’t mean statement used CBO. usage of CBO depends on availability of stats etc. Some part of query can be used CBO other parts may not (like JOIN, subquery etc). Best way is check explain or META().plan look optimizer_estimates on operator
@vsr1 The above system table always shows only one record. Is there anyway we can log all query which ran till CB service got restarted.
Thanks,
Debasis
@Debasis_Mallick please see the linked docs on that one. Only requests that took longer than a threshold (believe it’s 1 second by default) appear in that table, so if you want to see literally every request, set that threshold temporarily to 0, and reset it after you’ve finished debugging.
@vsr1 and @graham.pople thanks for your reply. Now I need help on below issue.
In both my cluster and client level (CB workbench UI), useCBO is set to false. However, when I ran a query, two sections showed under completed requests.
Could you please help me to understand whether CBO is using or not and why it is showing discrepancy.
The query is: “EXPLAIN SELECT COUNT(*) FROM config a WHERE a.__t = ‘sds-svcctrl-app-chain’;”. The statement type is EXPLAIN, and it indicates useCBO is true.
The other query I ran in the Couchbase UI is: “SELECT COUNT(*) FROM config a WHERE a.__t = ‘sds-svcctrl-app-chain’;”. The statement type is SELECT, and it shows useCBO is false.
I am using below curl command to check the details of query execution.
curl -u cbadmin:cbadmin ``http://xx.xx.xx.xx:8093/admin/completed_requests|jq
Thanks,
Debasis
Go to CB workbench UI (Prefrences, upper right coroner gear) uncheck CBO flag, by default it is set true, Each user session this value is set one must uncheck those
It does OR of request level + cluster level
If you need AND use following
Get current queryN1QLFeatCtrl disable cbo via feature control also
curl -s -u Administrator:password http://host:8091/settings/querySettings | jq | grep queryN1QLFeatCtrl
Add 16 to that and set example default 76 + 16 = 92
curl -s -u Administrator:password http://host:8091/settings/querySettings -d ‘queryN1QLFeatCtrl=92’
You can also do via UI Settings ===>Advanced Query Settings and add 16 to existing feature control value
@vsr1 I had already set the CBO flag unchecked in CB workbench UI and cluster lever also. But my concern is why CB still using CBO in explain phase of the query while checking the completed requests.
“statement”: “explain select count(*) from config a where a.__t = ‘sds-svcctrl-app-chain’;”,
“statementType”: “EXPLAIN”,
“useCBO”: true,
Thanks,
Debasis
useCBO true means some one set it
I get useCBO omitted when the box is not checked (top) and useCBO=true when the box is ticked (between statementType and userAgent). Using Enterprise Edition 7.6.6 build 6126
[
{
"completed_requests": {
"clientContextID": "fc012247-832e-4ffc-a505-4b65625921c8",
"cpuTime": "171.983µs",
"elapsedTime": "1.033929ms",
"errorCount": 0,
"errors": [],
"n1qlFeatCtrl": 76,
"namedArgs": {
"$last_prop": "route_00000"
},
"phaseOperators": {
"authorize": 1,
"stream": 1
},
"phaseTimes": {
"authorize": "5.761µs",
"instantiate": "6.786µs",
"parse": "460.227µs",
"plan": "291.235µs",
"plan.index.metadata": "42.979µs",
"plan.keyspace.metadata": "2.829µs",
"run": "200.492µs",
"stream": "39.943µs"
},
"remoteAddr": "127.0.0.1:55387",
"requestId": "fd7515fa-fa7c-4622-8b6a-f7dcead6f24c",
"requestTime": "2025-08-14T08:48:14.745-07:00",
"resultCount": 1,
"resultSize": 2912,
"scanConsistency": "unbounded",
"serviceTime": "973.005µs",
"state": "completed",
"statement": "EXPLAIN SELECT COUNT(*) FROM `travel-sample` a WHERE a.__t = \"sds-svcctrl-app-chain\";",
"statementType": "EXPLAIN",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/138.0.0.0 Safari/537.36",
"users": "builtin:Administrator",
"~qualifier": "threshold"
}
},
{
"completed_requests": {
"clientContextID": "50124fb5-ce55-466b-b62e-d8a553c092e4",
"cpuTime": "243.216µs",
"elapsedTime": "5.672862ms",
"errorCount": 0,
"errors": [],
"n1qlFeatCtrl": 76,
"namedArgs": {
"$last_prop": "route_00000"
},
"phaseOperators": {
"authorize": 1,
"stream": 1
},
"phaseTimes": {
"authorize": "6.421µs",
"instantiate": "6.954µs",
"parse": "494.052µs",
"plan": "4.82238ms",
"plan.index.metadata": "4.499197ms",
"plan.keyspace.metadata": "4.583µs",
"run": "272.486µs",
"stream": "62.669µs"
},
"remoteAddr": "127.0.0.1:55387",
"requestId": "1c8824fd-af18-45e5-8504-359f38834665",
"requestTime": "2025-08-14T08:47:25.472-07:00",
"resultCount": 1,
"resultSize": 4969,
"scanConsistency": "unbounded",
"serviceTime": "5.611137ms",
"state": "completed",
"statement": "EXPLAIN SELECT COUNT(*) FROM `travel-sample` a WHERE a.__t = \"sds-svcctrl-app-chain\";",
"statementType": "EXPLAIN",
"useCBO": true,
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/138.0.0.0 Safari/537.36",
"users": "builtin:Administrator",
"~qualifier": "threshold"
}
}
}
]
FYI: Cluster level CBO set and QWB level CBO unset and when any statement is executed in QWB. QWB does two additional internal (clientContextID will have INTERNAL) explain and advise statements. Those has issue MB-68089
In your case you mentioned it has been unchecked cluster level and QWB level. So it should not been issue. If you tell exact version one can try that and see.