X

Everything about Table Scans including offload to Exadata, Big Data SQL, and External Tables

  • August 16, 2018

Create Quarantine

Roger MacNicol
Software Architect

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

 

Join the discussion

Comments ( 2 )
  • Randolf Eberle-Geist Monday, June 29, 2020
    Hi Roger,

    thanks a lot for the information provided.

    Which OBJECT_ID can be specified in the case of (sub)partitioned objects and I want to quarantine the whole segment, which means all (sub)partitions?

    So is it possible to specify the OBJECT_ID of the global table object for example and this will apply to all sub-objects of it, or do I need to specify each and every actual object_id of all sub-objects, which would render this almost useless since partitions might be dropped and added, apart from the point that this could apply to thousands of objects in case of sub-partitioning in use?

    Kind regards,
    Randolf
  • Roger Wednesday, December 9, 2020
    Offload doesn't know much about tables, partitions, and sub-partitions - these are syntactic sugar to help the DBA control and understand storage of their data. What offload sees is a scan of segment as described by its dobj# rather than its obj#.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.