Performance Tuning

This section summarizes techniques for analyzing and improving performance when you use Oracle Database API for MongoDB. Review section Indexes for complementary guidance.

$native Hint

Use the $native hint to append a hint to the SQL generated by Oracle Database API for MongoDB. Use $native with any MongoDB command that honors hints, including find and aggregate. Specify the hint as {"$native":<hint>}, where <hint> is the SQL hint applied to the generated statement.

Example:

db.employees.aggregate(
  [{"$count":"cnt"}], 
  {"hint" : {"$native":"PARALLEL"}}
);

In this example, the SQL hint PARALLEL is appended to the generated SQL to request parallel execution.

$service Hint

Use the $service hint only with the Autonomous Database. By default, MongoDB commands run in the LOW consumer group. Specify $service to switch the consumer group for the command. Internally, Oracle Database API for MongoDB invokes CS_SESSION.SWITCH_SERVICE() to change the consumer group. Provide the hint as {"$service":<service_name>}, where <service_name> is HIGH, MEDIUM, LOW, TP, or TPURGENT. The connected user requires EXECUTE on CS_SESSION. If the privilege is not granted, the command continues in the default LOW service.

Example:

db.employees.aggregate(
  [{"$count":"cnt"}], 
  {"hint" : {"$service":"HIGH"}}
);

This example elevates the consumer group to HIGH before the command executes.

SQL Monitoring

SQL Monitoring provides detailed insight into SQL issued by the Oracle Database API for MongoDB. Monitoring reports capture execution plans, I/O statistics, durations, and related diagnostics. A SQL statement is monitored when it runs longer than five seconds, includes the MONITOR hint, or executes in parallel. Monitored statements appear in the Performance Hub.

Before using SQL Monitoring, ensure that your user has the necessary roles:

grant select on "V$SQL" to your_user;
grant select on "V$SQL_MONITOR" to your_user;
grant advisor to your_user; 
grant execute on dbms_sql_monitor to your_user;

To explicitly monitor a MongoDB command, combine $native with the MONITOR hint:

db.employees.aggregate([{"$match" : {"name":"SMITH"}}], {"hint" :{"$native" : "MONITOR"}});

db.employees.find({"name" : "SMITH"}).hint({$native : "MONITOR"});

If you can access Performance Hub, locate the monitoring report for commands executed with the MONITOR hint there. Otherwise, you can generate the HTML report from mongosh as follows.

Determine the SQL_ID of the statement that was executed:

db.aggregate([{$sql:`
       select sql_fulltext, sql_id 
       from v$sql 
       where sql_text like '%MONITOR%' and
             sql_text not like '%v$sql%'
       order by last_active_time desc
       fetch first 1 rows only
   `}]);
 [
   {
     SQL_FULLTEXT: '...',
     SQL_ID: '6xy9y446n1ha6'
   }
 ]

Generate an HTML SQL monitoring report:

var html = 
   db.aggregate([{$sql:`
     select dbms_sql_monitor.report_sql_monitor(
       sql_id => '6xy9y446n1ha6',
       report_level => 'ALL',
       type => 'ACTIVE'
     ) as "html"
     from dual
   `}]).toArray()[0].html;

require('fs').writeFileSync('out.html', html);