Create Quarantine

Posted on by Posted in cell_offload, oracle, SmartScan

First if you want don’t know what an Exadata Quarantine is read this.

Someone asked whether you can create your own Exadata Cell quarantine and, if you can, why you might ever want to do it? 

The first step when you don’t know how to do something is try HELP in cellcli

CellCLI> HELP
...
ALTER QUARANTINE
...
CREATE QUARANTINE
...
DROP QUARANTINE
...
LIST QUARANTINE

So we see we can create a quarantine, so we use HELP again:

CellCLI> HELP CREATE QUARANTINE
  Usage: CREATE QUARANTINE quarantineType=value, quarantinePlan="SYSTEM", dbUniqueName=value[,attributeName=value]...

  Purpose: Define the attributes for a new quarantine entity

  Arguments:
    <attributeName>: attribute to be set.
    <value>: new attribute value.

  Examples:
    CREATE QUARANTINE  quarantineType="SQLID", quarantinePlan="SYSTEM", dbUniqueName="DB1", sqlid="5xnjp4cutc1s7"

Creating a Coarse quarantine

Coarse quarantines are where we quarantine a statement or data store without regard to any specific aspect of its processing that needed a quarantine. The supported values for quarantineType are:

  • SQL_ID – quarantine a single SQL statement
  • OBJID – quarantine a single table or index
  • CDBID – quarantine a single container database
  • DATABASE – quarantine a single database
  • SQL_PLAN – quarantine an aspect of a plan (see Fine Grained Quarantine below)

In the example above we quarantine a single SQLID by using quarantineType=”SQLID” with the operand sqlid=”{sql_id}”.

An OBJID to be quarantined should be specified using the operand objectID=”{object #}”,

The quarantinePlan should be set to the SYSTEM plan unless requested by support to set it to a user plan.

The CDB to be quarantined should be specified using conDbUniqueName=”{container database unique name}” and the database to be quarantined should be specified using dbUniqueName=”{db unique name}”.

I would strongly advise that if you manually create a quarantine you add a comment so you remember why using comment=”{explanation}”

Creating a Fine-Grained Quarantine (FGQ)

We added CREATE QUARANTINE mostly for test purposes and I can see few reasons for a DBA to use this feature but one case is where you need a work around for a wrong results issue (since these don’t create a quarantine) and are unable to use a statement hint on the problem statement or set an event in the session.  For wrong results generally a Fine Grained Quarantine is a more useful tool. The reason we created Fine Grained Quarantines was exactly for dealing with problems with some aspect of processing so that we could restrict offload of a specific feature that affected multiple sql_ids and prevent escalation to a database quarantine and also to let all non-problematical queries still use Smart Scan.

Syntax:
create quarantine quarantineType="database",quarantinePlan="{plan_name}",fineGrainControl="{attribute}", fineGrainValue="{fine_grain_vaue}^",dbUniqueName="{db name}",comment="{notes}"

So for example there was a problem several years ago that could cause the decompression stack in OLTP compression to overflow (that partially led to the creation of FGQ).  With this new syntax a DBA could now stop blocks which needed OLTP decompression from being processed in Smart Scan but let HCC and uncompressed blocks still be offloaded. An FGQ rule may be restricted to database, cdb, objid, or sql_id scope just as coarse quarantines can. 

create quarantine ... comment="test for quarantine",fineGrainControl="OLTP COMPRESSION", fineGrainValue="ENABLE"

The most important of the supported values for fineGrainControl are: 

  • “EHCC COMPRESSION”
  • “OLTP COMPRESSION”
  • “BLOOM FILTER”
  • “CHAINED ROW”
  • “DECRYPTION”

Why might you want to do this?

This command mainly exists mainly for testing purposes and was not intended for general use. The ability to create fine grained quarantines was introduced to give support one more tool in their arsenal to help a DBA work around an issue and either prevent escalation to a database quarantine or to avoid a class of wrong result issues. I hope none of you ever need to use this feature – it is there “just in case”.

I hope to find time soon to show some worked examples of FGQ in action.

Roger