Oracle 19c : The QUARANTINE hint

Here is some interesting stuffs that i discovered when playing around with one the new 19c feature known as SQL Quarantine.  Beside controlling  it the usual way such as using DBMS_SQLQ as explained in the documentation we can also use a special undocumented quarantine HINT.

As i’m playing with an exadata binary on my local machine, i need to set the parameter “_exadata_feature_on”=true  to get SQL QUARANTINE feature to work (As Rodrigo Jorge already pointed out).

Capture 01

When analyzing the package ” dbms_sqlq_internal” i spotted something interesting

Capture 02

Capture 03

We are building a HINT here named “QUARANTINE” which can take this parameters :

  • ‘CPU_TIME’
  • ‘ELAPSED_TIME’
  • ‘IO_MEGABYTES’
  • ‘IO_REQUESTS’
  • ‘IO_LOGICAL’
  • ‘PHV’

We can also extract the specified hint using a similar query on SYS.SQLOBJ$ and SYS.SQLOBJ$DATA  tables :

Capture 04

And here is a quick test using a special value in this case “ALWAYS_QUARANTINE” :

Capture 05

Ex : 1

Capture 06

Ex 2 :

Capture 07

That’s it 😀

3 thoughts on “Oracle 19c : The QUARANTINE hint

  1. Hi have set parameter “_exadata_feature_on”=true buy can`t limit the sql execution time
    Is there any other parameter should to be set
    .e.g. resource_manager_plan

    thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s