As application-driven data models and document databases—made popular by MongoDB—continue to gain traction, Oracle Database has added MongoDB emulation capabilities on top of its SQL query engine. It's only the logical model and exposed API that resemble it, as physical documents are stored in relational tables and fixed-size blocks. This adds another abstraction on top of SQL, and when performance is different from what you expected, you need to look at the physical execution behind the logical query. The $sql stage of an aggregation pipeline can help troubleshoot. Let's take an example.
I create a one-million-document collection and index it with the ESR (Equality, Sort, Range) Guideline in mind:
// create the collection
db.oneMillion.drop();
db.oneMillion.createIndex({ e: 1, s: 1, r: 1 });
// insert documents
for (let i = 0; i < 1e2; i++) {
void db.oneMillion.insertMany( Array.from(
{ length: 1e4 },
(_, i) => ({ e: i%3, s: new Date(), r: Math.random(), x: UUID() })
) )
}
// check count
db.oneMillion.countDocuments();
I run a query that completes in 1 millisecond on MongoDB but takes seconds in the Oracle Database emulation. It’s a simple pagination query combining:
- An equality filter:
{ e: 2 }, which returns one-third of the collection - A range filter:
{ r: { $gt: 0.5 } }, which returns half of those documents - A sort with pagination:
sort({ s: 1, r: 1 }).limit(10), which returns the last ten documents by date and value
This query runs much faster on MongoDB than in the Oracle emulation. To get execution statistics, I add hint({"$native":"MONITOR"}) so that the underlying SQL query is run with the /*+ MONITOR */ hint:
db.oneMillion.find(
{ e:2, r: { $gt:0.5 } },
{ s:1, r:1, _id:0 }
).sort({ s:1, r:1 }).limit(10).hint({"$native":"MONITOR"});
The query is executed and returns the result:
[
{ s: ISODate('2025-12-17T08:52:50.475Z'), r: 0.5222276191239983 },
{ s: ISODate('2025-12-17T08:52:50.475Z'), r: 0.7565247894880116 },
{ s: ISODate('2025-12-17T08:52:50.476Z'), r: 0.6099160713187135 },
{ s: ISODate('2025-12-17T08:52:50.476Z'), r: 0.765542699487576 },
{ s: ISODate('2025-12-17T08:52:50.476Z'), r: 0.8144790402364248 },
{ s: ISODate('2025-12-17T08:52:50.476Z'), r: 0.8328191789951023 },
{ s: ISODate('2025-12-17T08:52:50.476Z'), r: 0.8356551175440483 },
{ s: ISODate('2025-12-17T08:52:50.476Z'), r: 0.9779607167502489 },
{ s: ISODate('2025-12-17T08:52:50.477Z'), r: 0.5236033088481526 },
{ s: ISODate('2025-12-17T08:52:50.477Z'), r: 0.5290926931399482 }
]
After running it, I can get the SQL Monitor report for the last query in my session by calling the dbms_sqltune function through the $sql aggregation stage:
db.aggregate([{ $sql : `select
dbms_sqltune.report_sql_monitor(report_level=>'all',type=>'text') as "text"
`}]).forEach(row => print(row.text))
The output shows the underlying SQL query generated by the emulation, with its execution plan and execution statistics:
SQL Monitoring Report
SQL Text
------------------------------
select /*+ FIRST_ROWS(10) MONITOR */ json_patch("DATA",:1 project ),rawtohex("RESID"),"ETAG" from "ORA"."oneMillion" where JSON_EXISTS("DATA",'$?( (@.e.numberOnly() == $B0) && (@.r.numberOnly() > $B1) )' passing :2 as "B0", :3 as "B1" type(strict)) order by JSON_QUERY("DATA", '$.s[*].min()') asc nulls first, JSON_QUERY("DATA", '$.r[*].min()') asc nulls first fetch next 10 rows only
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 4
Session : ORA (36619:4028)
SQL ID : 420n5y2ytx6zh
SQL Execution ID : 67108867
Execution Started : 12/17/2025 09:04:51
First Refresh Time : 12/17/2025 09:04:51
Last Refresh Time : 12/17/2025 09:04:52
Duration : 1s
Module/Action : ORDS_ADBS_Managed/-
Service : CQWRIAXKGYBKVNX_O23_low.adb.oraclecloud.com
Program : ORDS_ADBS_Managed
Fetch Calls : 1
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :2 | 2 | NUMBER | 2 |
| :3 | 3 | NUMBER | .5 |
========================================================================================================================
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 1.18 | 1.18 | 0.00 | 1 | 98194 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=2462396944)
==============================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
==============================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 10 | . | | |
| 1 | COUNT STOPKEY | | | | 1 | +1 | 1 | 10 | . | | |
| 2 | VIEW | | 52 | 316 | 1 | +1 | 1 | 10 | . | | |
| 3 | SORT ORDER BY STOPKEY | | 52 | 316 | 2 | +1 | 1 | 10 | 4096 | 50.00 | Cpu (1) |
| 4 | FILTER | | | | 1 | +1 | 1 | 167K | . | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | oneMillion | 52 | 38 | 1 | +1 | 1 | 167K | . | 50.00 | Cpu (1) |
| 6 | HASH UNIQUE | | 52 | | 1 | +1 | 1 | 167K | . | | |
| 7 | INDEX RANGE SCAN (MULTI VALUE) | $ora:oneMillion.e_1_s_1_r_1 | 38 | 27 | 1 | +1 | 1 | 167K | . | | |
==============================================================================================================================================================================
Returning those 10 rows used 1.18 seconds of CPU because it used the index only for the filters, returning 166,666 rows that had to be deduplicated (HASH UNIQUE), and sorted (SORT ORDER BY STOPKEY) before returning the result.
Oracle has powerful hints, and you can use them with the hint({"$native": }) (not to be confused with hint({"$natural":1})) of MongoDB. For example, I can try to avoid this HASH UNIQUE that doesn't preserve the ordering from the index:
db.oneMillion.find(
{ e:2, r: { $gt:0.5 } },
{ s:1, r:1, _id:0 }
).sort({ s:1, r:1 }).limit(10).hint(
{"$native":'NO_USE_HASH_AGGREGATION MONITOR'}
);
It uses a SORT UNIQUE but still doesn't preserve the index ordering because the deduplication is on the ROWID, so finally it's just an additional sort:
| 3 | SORT ORDER BY STOPKEY | | 1 | 3 | 1 | +1 | 1 | 10 | 4096 | | |
| 4 | FILTER | | | | 1 | +1 | 1 | 167K | . | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | oneMillion | 1 | 2 | 1 | +1 | 1 | 167K | . | 100.00 | Cpu (1) |
| 6 | SORT UNIQUE | | 1 | | 1 | +1 | 1 | 167K | 10MB | | |
| 7 | INDEX RANGE SCAN (MULTI VALUE) | $ora:oneMillion.e_1_s_1_r_1 | 1 | 2 | 1 | +1 | 1 | 167K | . | | |
If you don't have the license for all options (Enterprise Edition, Diagnostic Pack, and Tuning Pack), don't use SQL Monitor. You can still view the execution plan with DBMS_XPLAN. To obtain execution statistics, use the GATHER_PLAN_STATISTICS hint:
db.oneMillion.find(
{ e:2, r: { $gt:0.5 } },
{ s:1, r:1, _id:0 }
).sort({ s:1, r:1 }).limit(10).hint({"$native":"GATHER_PLAN_STATISTICS"});
The query to get all execution plan sections is:
db.aggregate( [ { $sql : `
select *
from dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST')
` } ] ).forEach(row => print(row.PLAN_TABLE_OUTPUT));
SQL_ID 66jyw5hxfx4zh, child number 0
-------------------------------------
select /*+ FIRST_ROWS(10) GATHER_PLAN_STATISTICS */
json_patch("DATA",:1 project ),rawtohex("RESID"),"ETAG" from
"ORA"."oneMillion" where JSON_EXISTS("DATA",'$?( (@.e.numberOnly() ==
$B0) && (@.r.numberOnly() > $B1) )' passing :2 as "B0", :3 as "B1"
type(strict)) order by JSON_QUERY("DATA", '$.s[*].min()') asc nulls
first, JSON_QUERY("DATA", '$.r[*].min()') asc nulls first fetch next 10
rows only
Plan hash value: 2462396944
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 10 |00:00:00.98 | 98194 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 10 |00:00:00.98 | 98194 | | | |
| 2 | VIEW | | 1 | 1 | 18314 | 3 (34)| 00:00:01 | 10 |00:00:00.98 | 98194 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 1 | 179 | 3 (34)| 00:00:01 | 10 |00:00:00.98 | 98194 | 4096 | 4096 | 4096 (0)|
|* 4 | FILTER | | 1 | | | | | 166K|00:00:00.32 | 98194 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| oneMillion | 1 | 1 | 179 | 2 (0)| 00:00:01 | 166K|00:00:00.30 | 98194 | | | |
| 6 | HASH UNIQUE | | 1 | 1 | 179 | | | 166K|00:00:00.11 | 2048 | 772K| 772K| |
|* 7 | INDEX RANGE SCAN (MULTI VALUE) | $ora:oneMillion.e_1_s_1_r_1 | 1 | 1 | | 2 (0)| 00:00:01 | 166K|00:00:00.08 | 2048 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2
2 - SEL$1 / "from$_subquery$_002"@"SEL$2"
3 - SEL$1
5 - SEL$1 / "oneMillion"@"SEL$1"
7 - SEL$1 / "oneMillion"@"SEL$1"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
OPT_PARAM('_fix_control' '20648883:0 26552730:1 27175987:0 29972495:0 22387320:0 30195773:0 31945701:1 32108311:1 33659818:3 34092979:1 35495824:1 33792497:1 36554842:1 36283175:1
31720959:1 36004220:1 36635255:1 36675198:1 36868551:1 37400112:1 37346200:0 37626161:1')
FIRST_ROWS(10)
FORCE_XML_QUERY_REWRITE
FORCE_JSON_TABLE_TRANSFORM
XML_DML_RWT_STMT
XMLINDEX_REWRITE
XMLINDEX_REWRITE_IN_SELECT
NO_COST_XML_QUERY_REWRITE
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
INDEX_RS_ASC(@"SEL$1" "oneMillion"@"SEL$1" "$ora:oneMillion.e_1_s_1_r_1")
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "oneMillion"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1" UNIQUE)
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
4 - filter(HEXTORAW('04')>SYS_CONS_ANY_SCALAR(:3, 3))
7 - access("oneMillion"."SYS_NC00005$"=SYS_CONS_ANY_SCALAR(:2, 3) AND "oneMillion"."SYS_NC00007$">SYS_CONS_ANY_SCALAR(:3, 3) AND "oneMillion"."SYS_NC00007$"<HEXTORAW('04'))
filter(("oneMillion"."SYS_NC00007$"<HEXTORAW('04') AND "oneMillion"."SYS_NC00007$">SYS_CONS_ANY_SCALAR(:3, 3)))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "from$_subquery$_002"."JSON_PATCH("DATA",:1PROJECT)"[JSON,32600], "from$_subquery$_002"."RAWTOHEX("RESID")"[VARCHAR2,4000], "from$_subquery$_002"."ETAG"[RAW,16]
2 - "from$_subquery$_002"."JSON_PATCH("DATA",:1PROJECT)"[JSON,32600], "from$_subquery$_002"."RAWTOHEX("RESID")"[VARCHAR2,4000], "from$_subquery$_002"."ETAG"[RAW,16]
3 - (#keys=2) JSON_VALUE( /*+ QJSNMD5_TC_JCMP_JV */ JSON_QUERY("DATA" /*+ LOB_BY_VALUE */ FORMAT OSON , '$.s[*].min()' RETURNING JSON WITHOUT ARRAY WRAPPER NULL ON ERROR
TYPE(LAX) ) FORMAT OSON , '$' RETURNING ANY ORA_RAWCOMPARE(32767) ERROR ON ERROR TYPE(LAX) )[32767], JSON_VALUE( /*+ QJSNMD5_TC_JCMP_JV */ JSON_QUERY("DATA" /*+ LOB_BY_VALUE */
FORMAT OSON , '$.r[*].min()' RETURNING JSON WITHOUT ARRAY WRAPPER NULL ON ERROR TYPE(LAX) ) FORMAT OSON , '$' RETURNING ANY ORA_RAWCOMPARE(32767) ERROR ON ERROR TYPE(LAX)
)[32767], "DATA" /*+ LOB_BY_VALUE */ [JSON,8200], "oneMillion"."RESID"[RAW,2000], "ETAG"[RAW,16]
4 - "DATA" /*+ LOB_BY_VALUE */ [JSON,8200], "oneMillion"."RESID"[RAW,2000], "ETAG"[RAW,16]
5 - "DATA" /*+ LOB_BY_VALUE */ [JSON,8200], "oneMillion"."RESID"[RAW,2000], "ETAG"[RAW,16]
6 - (#keys=2) "oneMillion".ROWID[ROWID,10], SYSVARCOL[8]
7 - "oneMillion".ROWID[ROWID,10], "oneMillion"."SYS_NC00005$"[RAW,4000], "oneMillion"."SYS_NC00007$"[RAW,4000], SYSVARCOL[8]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - STATEMENT
- FIRST_ROWS(10)
Query Block Registry:
---------------------
SEL$1 (PARSER) [FINAL]
SEL$2 (PARSER) [FINAL]
The reason a sort is unavoidable here is indicated in the Column Projection Information for line 6, at the HASH UNIQUE step:
6 - (#keys=2) "oneMillion".ROWID[ROWID,10], SYSVARCOL[8]
Oracle implemented a MULTI VALUE index type to emulate MongoDB’s multi-key indexes. Unlike MongoDB, however, deduplication is not performed during the index scan. Instead, it occurs in a separate operation, similar to GROUP BY ROWID, MIN(), which keeps only one entry per document. This additional step means that the ordering of index entries is not preserved.
Consequently, when you use MongoDB emulation on Oracle, you cannot apply the ESR (Equality, Sort, Range) Guideline. With Oracle's MongoDB API, indexes are limited to equality and range predicates. They cannot be used to optimize pagination queries. However, you can still use the full range of Oracle SQL instrumentation to understand the underlying reasons for this behavior.
Top comments (0)