Oracle DBMS_AUTO_SQLSET
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
Security Model Owned by SYS with EXECUTE granted to the DBA role.
Source {ORACLE_HOME}/rdbms/admin/
Subprograms
 
CREATE_SNAPSHOT (new 20c)
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-NOV-20        1 26-NOV-20      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-NOV-20        1 26-NOV-20      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-NOV-20        1 26-NOV-20      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 (new 20c)
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,
interval  IN NUMBER);
exec dbms_auto_sqlset.modify_snapshot_settings(20);

PL/SQL procedure successfully completed.
 
PLAN_DATA2TABLE (new 20c)
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 (new 20c)
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.

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_WORKLOAD_REPOSITORY
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx