Oracle | DBMS_AUTO

Oracle DBMS_AUTO_SQLSET Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Autonomous Database Utilities for scheduling and creating AWR Snapshots

AUTHID DEFINER

Dependencies

DBA_AUTOSQLSET_SQLPLAN

DBMS_SQLTUNE_LIB

SQL_PLAN_TABLE_TYPE

Documented No

First Available 20c

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);

Security Model Owned by SYS with EXECUTE granted to the DBA role.

Source {ORACLE_HOME}/rdbms/admin/dbmssqls.sql
{ORACLE_HOME}/rdbms/admin/prvtsqls.plb

Subprograms

CREATE_SNAPSHOT	PLAN_DATA2TABLE	PURGE_SNAPSHOT
MODIFY_SNAPSHOT_SETTINGS

CREATE_SNAPSHOT

Creates a current AWR snapshot dbms_auto_sqlset.create_snapshot;

col other format a55

      

      SELECT * FROM dba_autosqlset_snapshot ORDER BY 1;

      

      SNAP_TIME INST_NUM END_SNAP_ STATUS OTHER

      --------- -------- --------- ------ ------------------------------------------

      26-JAN-23        1 26-JAN-23      0 {"s":{"t":[

                                        {"i":0,"p":1,"e":4870,"c":4194,"r":0,"n":0,"x":0}

                                        {"i":1,"p":1,"e":3730,"c":4139,"r":0,"n":0,"x":0}

                                        {"i":2,"p":1,"e":10260,"c":10350,"r":0,"n":0,"x":0}

                                        {"i":3,"p":1,"e":2839,"c":2786,"r":0,"n":0,"x":0}

                                        {"i":4,"p":1,"e":2683,"c":2667,"r":0,"n":0,"x":0}]}}

      

      exec dbms_auto_sqlset.create_snapshot;

      

      PL/SQL procedure successfully completed.

      

      SELECT * FROM dba_autosqlset_snapshot ORDER BY 1;

      

      SNAP_TIME INST_NUM END_SNAP_ STATUS OTHER

      --------- -------- --------- ------ ------------------------------------------

      26-JAN-23        1 26-JAN-23      0 {"s":{"t":[

                                          {"i":0,"p":1,"e":4870,"c":4194,"r":0,"n":0,"x":0}

                                          {"i":1,"p":1,"e":3730,"c":4139,"r":0,"n":0,"x":0}

                                          {"i":2,"p":1,"e":10260,"c":10350,"r":0,"n":0,"x":0}

                                          {"i":3,"p":1,"e":2839,"c":2786,"r":0,"n":0,"x":0}

                                          {"i":4,"p":1,"e":2683,"c":2667,"r":0,"n":0,"x":0}]}}

      

      26-JAN-23        1 26-JAN-23      0 {"s":{"t":[

                                          {"i":0,"p":1,"e":238,"c":238,"r":0,"n":0,"x":0}

                                          {"i":1,"p":1,"e":168,"c":0,"r":0,"n":0,"x":0}

                                          {"i":2,"p":1,"e":184,"c":0,"r":0,"n":0,"x":0}

                                          {"i":3,"p":1,"e":117,"c":75,"r":0,"n":0,"x":0}

                                          {"i":4,"p":1,"e":93,"c":0,"r":0,"n":0,"x":0}]}}

MODIFY_SNAPSHOT_SETTINGS

Modifies AWR retention and interval settings

Currently unable to locate where this information is retained in the data dictionary dbms_auto_sqlset.modify_snapshot_settings( retention IN NUMBER := 31, interval IN NUMBER := 900);

exec dbms_auto_sqlset.modify_snapshot_settings(20);

      

      PL/SQL procedure successfully completed.

PLAN_DATA2TABLE

Converts a SQLSET BLOB to an array (table) data type dbms_auto_sqlset.plan_data2table(plan_data IN BLOB) RETURN sys.sql_plan_table_type;

TBD

PURGE_SNAPSHOT

In theory, purges an AWR snapshot

Testing does indicates it does not purge one or more snapshots dbms_auto_sqlset.purge_snapshot;

exec dbms_auto_sqlset.purge_snapshot;

      

      PL/SQL procedure successfully completed.