Thursday, December 18, 2025
HomeDatabase management
Database management

Utilizing $sql in Oracle Database as an alternative of clarify(“executionStats”)

Malik
By Malik
0
3
Utilizing $sql in Oracle Database as an alternative of clarify(“executionStats”)


As application-driven knowledge fashions and doc databases—made fashionable by MongoDB—proceed to realize traction, Oracle Database has added MongoDB emulation capabilities on high of its SQL question engine. It is solely the logical mannequin and uncovered API that resemble it, as bodily paperwork are saved in relational tables and fixed-size blocks. This provides one other abstraction on high of SQL, and when efficiency is completely different from what you anticipated, you want to have a look at the bodily execution behind the logical question. The $sql stage of an aggregation pipeline may also help troubleshoot. Let’s take an instance.

I create a one-million-document assortment and index it with the ESR (Equality, Type, Vary) Guideline in thoughts:

// create the gathering
db.oneMillion.drop();
db.oneMillion.createIndex({ e: 1, s: 1, r: 1 });
// insert paperwork
for (let i = 0; i < 1e2; i++) {  
  void db.oneMillion.insertMany( Array.from(  
    { size: 1e4 },  
    (_, i) => ({ e: i%3, s: new Date(), r: Math.random(), x: UUID() })  
  ) )
}
// verify rely
db.oneMillion.countDocuments();
Enter fullscreen mode

Exit fullscreen mode

I run a question that completes in 1 millisecond on MongoDB however takes seconds within the Oracle Database emulation. It’s a easy pagination question combining:

  • An equality filter: { e: 2 }, which returns one-third of the gathering
  • A variety filter: { r: { $gt: 0.5 } }, which returns half of these paperwork
  • A form with pagination: kind({ s: 1, r: 1 }).restrict(10), which returns the final ten paperwork by date and worth

This question runs a lot quicker on MongoDB than within the Oracle emulation. To get execution statistics, I add trace({"$native":"MONITOR"}) in order that the underlying SQL question is run with the /*+ MONITOR */ trace:

db.oneMillion.discover( 
 { e:2, r: { $gt:0.5 } },
 { s:1, r:1, _id:0 }
).kind({ s:1, r:1 }).restrict(10).trace({"$native":"MONITOR"});
Enter fullscreen mode

Exit fullscreen mode

The question is executed and returns the outcome:

[
  { 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 }
]
Enter fullscreen mode

Exit fullscreen mode

After working it, I can get the SQL Monitor report for the final question in my session by calling the dbms_sqltune perform by means of the $sql aggregation stage:

db.combination([{ $sql : `select 
 dbms_sqltune.report_sql_monitor(report_level=>'all',type=>'text') as "text"
`}]).forEach(row => print(row.textual content))
Enter fullscreen mode

Exit fullscreen mode

The output reveals the underlying SQL question generated by the emulation, with its execution plan and execution statistics:

SQL Monitoring Report

SQL Textual content
------------------------------
choose /*+ FIRST_ROWS(10) MONITOR */ json_patch("DATA",:1 mission ),rawtohex("RESID"),"ETAG" from "ORA"."oneMillion" the place JSON_EXISTS("DATA",'$?( (@.e.numberOnly() == $B0) && (@.r.numberOnly() > $B1) )' passing :2 as "B0", :3 as "B1" sort(strict)) order by JSON_QUERY("DATA", '$.s[*].min()') asc nulls first, JSON_QUERY("DATA", '$.r[*].min()') asc nulls first fetch subsequent 10 rows solely

World Data
------------------------------
 Standing              :  DONE (ALL ROWS)
 Occasion ID         :  4
 Session             :  ORA (36619:4028)
 SQL ID              :  420n5y2ytx6zh
 SQL Execution ID    :  67108867
 Execution Began   :  12/17/2025 09:04:51
 First Refresh Time  :  12/17/2025 09:04:51
 Final Refresh Time   :  12/17/2025 09:04:52
 Period            :  1s
 Module/Motion       :  ORDS_ADBS_Managed/-
 Service             :  CQWRIAXKGYBKVNX_O23_low.adb.oraclecloud.com
 Program             :  ORDS_ADBS_Managed
 Fetch Calls         :  1

Binds
========================================================================================================================
| Title | Place |  Sort  |                                           Worth                                           |
========================================================================================================================
| :2   |        2 | NUMBER | 2                                                                                         |
| :3   |        3 | NUMBER | .5                                                                                        |
========================================================================================================================

World Stats
=================================================
| Elapsed |   Cpu   |  Different   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Will get  |
=================================================
|    1.18 |    1.18 |     0.00 |     1 |  98194 |
=================================================

SQL Plan Monitoring Particulars (Plan Hash Worth=2462396944)
==============================================================================================================================================================================
| Id |                 Operation                 |            Title             |  Rows   | Value |   Time    | Begin  | Execs |   Rows   |  Mem  | Exercise | Exercise Element |
|    |                                           |                             | (Estim) |      | Energetic(s) | Energetic |       | (Precise) | (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 |     167Okay |     . |          |                 |
|  5 |       TABLE ACCESS BY INDEX ROWID BATCHED | oneMillion                  |      52 |   38 |         1 |     +1 |     1 |     167Okay |     . |    50.00 | Cpu (1)         |
|  6 |        HASH UNIQUE                        |                             |      52 |      |         1 |     +1 |     1 |     167Okay |     . |          |                 |
|  7 |         INDEX RANGE SCAN (MULTI VALUE)    | $ora:oneMillion.e_1_s_1_r_1 |      38 |   27 |         1 |     +1 |     1 |     167Okay |     . |          |                 |
==============================================================================================================================================================================
Enter fullscreen mode

Exit fullscreen mode

Returning these 10 rows used 1.18 seconds of CPU as a result of it used the index just for the filters, returning 166,666 rows that needed to be deduplicated (HASH UNIQUE), and sorted (SORT ORDER BY STOPKEY) earlier than returning the outcome.

Oracle has highly effective hints, and you should use them with the trace({"$native": }) (to not be confused with trace({"$pure":1})) of MongoDB. For instance, I can attempt to keep away from this HASH UNIQUE that does not protect the ordering from the index:

db.oneMillion.discover( 
 { e:2, r: { $gt:0.5 } },
 { s:1, r:1, _id:0 }
).kind({ s:1, r:1 }).restrict(10).trace(
 {"$native":'NO_USE_HASH_AGGREGATION MONITOR'}
);
Enter fullscreen mode

Exit fullscreen mode

It makes use of a SORT UNIQUE however nonetheless does not protect the index ordering as a result of the deduplication is on the ROWID, so lastly it is simply an extra kind:

|  3 |     SORT ORDER BY STOPKEY                 |                             |       1 |    3 |         1 |     +1 |     1 |       10 |  4096 |          |                 |
|  4 |      FILTER                               |                             |         |      |         1 |     +1 |     1 |     167Okay |     . |          |                 |
|  5 |       TABLE ACCESS BY INDEX ROWID BATCHED | oneMillion                  |       1 |    2 |         1 |     +1 |     1 |     167Okay |     . |   100.00 | Cpu (1)         |
|  6 |        SORT UNIQUE                        |                             |       1 |      |         1 |     +1 |     1 |     167Okay |  10MB |          |                 |
|  7 |         INDEX RANGE SCAN (MULTI VALUE)    | $ora:oneMillion.e_1_s_1_r_1 |       1 |    2 |         1 |     +1 |     1 |     167Okay |     . |          |                 |
Enter fullscreen mode

Exit fullscreen mode

If you do not have the license for all choices (Enterprise Version, Diagnostic Pack, and Tuning Pack), do not use SQL Monitor. You may nonetheless view the execution plan with DBMS_XPLAN. To acquire execution statistics, use the GATHER_PLAN_STATISTICS trace:

db.oneMillion.discover( 
 { e:2, r: { $gt:0.5 } },
 { s:1, r:1, _id:0 }
).kind({ s:1, r:1 }).restrict(10).trace({"$native":"GATHER_PLAN_STATISTICS"});
Enter fullscreen mode

Exit fullscreen mode

The question to get all execution plan sections is:

db.combination( [ { $sql : `
 select * 
 from dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST')
` } ] ).forEach(row => print(row.PLAN_TABLE_OUTPUT));
Enter fullscreen mode

Exit fullscreen mode 

SQL_ID  66jyw5hxfx4zh, little one quantity 0
-------------------------------------
choose /*+ FIRST_ROWS(10) GATHER_PLAN_STATISTICS */
json_patch("DATA",:1 mission ),rawtohex("RESID"),"ETAG" from
"ORA"."oneMillion" the place JSON_EXISTS("DATA",'$?( (@.e.numberOnly() ==
$B0) && (@.r.numberOnly() > $B1) )' passing :2 as "B0", :3 as "B1"
sort(strict)) order by JSON_QUERY("DATA", '$.s[*].min()') asc nulls
first, JSON_QUERY("DATA", '$.r[*].min()') asc nulls first fetch subsequent 10
rows solely

Plan hash worth: 2462396944

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Title                        | Begins | E-Rows |E-Bytes| Value (%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 |        |       |            |          |    166Okay|00:00:00.32 |   98194 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| oneMillion                  |      1 |      1 |   179 |     2   (0)| 00:00:01 |    166Okay|00:00:00.30 |   98194 |       |       |          |
|   6 |       HASH UNIQUE                       |                             |      1 |      1 |   179 |            |          |    166Okay|00:00:00.11 |    2048 |   772Okay|   772Okay|          |
|*  7 |        INDEX RANGE SCAN (MULTI VALUE)   | $ora:oneMillion.e_1_s_1_r_1 |      1 |      1 |       |     2   (0)| 00:00:01 |    166Okay|00:00:00.08 |    2048 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Question Block Title / Object Alias (recognized 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"

Define Knowledge
-------------

  /*+
      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 Data (recognized by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   4 - filter(HEXTORAW('04')>SYS_CONS_ANY_SCALAR(:3, 3))
   7 - entry("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 Data (recognized 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]

Trace Report (recognized by operation id / Question Block Title / Object Alias):
Whole hints for assertion: 1
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  FIRST_ROWS(10)

Question Block Registry:
---------------------

  SEL$1 (PARSER) [FINAL]
  SEL$2 (PARSER) [FINAL]
Enter fullscreen mode

Exit fullscreen mode

The explanation a kind is unavoidable right here is indicated within the Column Projection Data for line 6, on the HASH UNIQUE step:

   6 - (#keys=2) "oneMillion".ROWID[ROWID,10], SYSVARCOL[8]
Enter fullscreen mode

Exit fullscreen mode

Oracle applied a MULTI VALUE index sort to emulate MongoDB’s multi-key indexes. In contrast to MongoDB, nevertheless, deduplication is just not carried out through the index scan. As an alternative, it happens in a separate operation, much like GROUP BY ROWID, MIN(), which retains just one entry per doc. This extra step signifies that the ordering of index entries is just not preserved.

Consequently, whenever you use MongoDB emulation on Oracle, you can not apply the ESR (Equality, Type, Vary) Guideline. With Oracle’s MongoDB API, indexes are restricted to equality and vary predicates. They can’t be used to optimize pagination queries. Nonetheless, you may nonetheless use the complete vary of Oracle SQL instrumentation to know the underlying causes for this conduct.

Previous article
What builders name themselves | InfoWorld
Next article
Most Downloaded Hugging Face Datasets and Their Use-cases
Malik
Malikhttps://www.malikinnovations.com

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest Articles

Load more

Malik Innovations is your technology, cloud computing and AI tools related website. We provide you with the latest breaking news and videos straight from these industries.

© Copyright 2025 - malikinnovations.com. All rights reserved.