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.

Monitoring for Developers

Developers can monitor SQL statements that they execute themselves. No additional privileges are required. This makes it easy to inspect the SQL generated by Oracle Database API for MongoDB during development and troubleshooting.

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"});

The V$ALL_SQL_MONITOR view contains all monitored statements that are visible to the current user within its security realms and still cached in memory. To get the execution plan details for any statement that was monitored, locate its SQL_ID with the appropriate filter conditions. For example, if you want the last statement that was executed using the MONITOR hint, you could use the following statement.

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

It will return the SQL_ID for the statement in question:

[{
    SQL_TEXT: '...',
    SQL_ID: '374bk6zwnn8d6'
  }
]

Then generate the report for the desired statement using its SQL_ID:

var html =
   db.aggregate([{$sql:`
     SELECT dbms_sqltune.report_sql_monitor(sql_id=> '374bk6zwnn8d6', type => 'ACTIVE') as "html"
   `}]).toArray()[0].html;

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

Monitoring for Administrators

Database administrators typically need to inspect SQL executed by multiple users or use Oracle Database Performance Hub. These tasks require additional privileges.

Monitored statements appear in the Performance Hub.

Before using these administrator features, ensure that your user has the necessary privileges:

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 locate a monitored statement, determine its SQL_ID from V$SQL:

db.aggregate([{$sql:`
|        select sql_text, sql_id
|        from v$sql_monitor
|        where sql_text like '%MONITOR%' and
|              sql_text not like '%v$sql_monitor%'
|        order by sql_exec_start desc
|        fetch first 1 rows only
|    `}])

This will return you the SQL_ID of your statement if its monitoring information is available, e.g.

[
  {
    SQL_TEXT: '...',
    SQL_ID: '6xy9y446n1ha6'
  }
]

Generate the HTML SQL Monitoring report:

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

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