Oracle | STATSPACK

Oracle STATSPACK 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 The Oracle documentation states: "SQL*PLUS command file to create statistics package." At one time that was probably an adequate description. Now, with AWR, the more accurate definition might be that this is a PL/SQL package of utilities support StatsPack and unfortunately also named StatsPack. For information about StatsPack functionality, which was the original AWR and which can still be deployed and used for much of what AWR does ... one of the largest distinguishing characteristics is that StatsPack does not require additional licensing such as Diagnostic and Tuning pack.

We strongly advise installing the PERFSTAT schema and objects in a PDB to avoid having to replicate the PERFSTAT tablespace in every PDB.

AUTHID DEFINER

Dependencies SELET name FROM dba_dependencies WHERE referenced_name = 'STATSPACK' UNION SELECT referenced_name FROM dba_dependencies WHERE name = 'STATSPACK'; Returns 149 rows.

Documented No

Exceptions

Error Code	Reason
`ORA-20100`	Purge Before Date (03-SEP-2021 13:16:40) is in the future
`ORA-20200`	`Database Instance specified (DB Id/Instance 2140826538/1) does not exist in STATS$DATABASE_INSTANCE`
`ORA-20200`	No snapshots exist for DB Id/Instance Number 2140826538/1

First Available Not known

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/spcpkg.sql

conn / as sysdba

      

      ALTER SESSION SET CONTAINER=FREEPDB1;

      

      SQL> set define on

      

      SQL> @?/rdbms/admin/spcusr.sql

      -- PERFSTAT / SYSAUX / TEMP /

      Note: SPCUSR complete. Please check spcusr.lis for any errors

      

      SQL> @?/rdbms/admin/spcreate.sql

      SQL> @?/rdbms/admin/spctab.sql

      

      SQL> @?/rdbms/admin/spcpkg.sql

      Creating Package STATSPACK...

      

      SP2-0808: Package created with compilation warnings

      

      Errors for PACKAGE STATSPACK:

      

      LINE/COL ERROR

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

      1/1 PLW-05018: unit STATSPACK omitted optional AUTHID clause; default

      value DEFINER used

      

      23/9 PLW-07203: parameter 'BBC' may benefit from use of the NOCOPY compiler hint

      

      23/3PLW-07203: parameter 'EBC' may benefit from use of the NOCOPY compiler hint

      

      24/9 PLW-07203: parameter 'BSP' may benefit from use of the NOCOPY compiler hint

      

      24/3PLW-07203: parameter 'ESP' may benefit from use of the NOCOPY compiler hint

      

      25/9 PLW-07203: parameter 'BLB' may benefit from use of the NOCOPY compiler hint

      

      26/9 PLW-07203: parameter 'BS' may benefit from use of the NOCOPY compiler hint

      

      37/9 PLW-07203: parameter 'TIMSTAT' may benefit from use of the NOCOPY compiler hint

      

      37/3PLW-07203: parameter 'STATLVL' may benefit from use of the NOCOPY compiler hint

      

      171/9 PLW-07203: parameter 'O_UCOMMENT' may benefit from use of the NOCOPY compiler hint

      

      186/9 PLW-07203: parameter 'O_ALL_INIT' may benefit from use of the NOCOPY compiler hint

      

      187/9 PLW-07203: parameter 'O_OLD_SQL_CAPTURE_MTH' may benefit from use of the NOCOPY compiler hint

      

      188/9 PLW-07203: parameter 'O_PIN_STATSPACK' may benefit from use of the NOCOPY compiler hint

      

      273/1PLW-06010: keyword "PURGE" used as a defined name

      283/14 PLW-06010: keyword "PURGE" used as a defined name

      292/1PLW-06010: keyword "PURGE" used as a defined name

      

      LINE/COL ERROR

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

       
      301/15 PLW-06010: keyword "PURGE" used as a defined name

       
      309/1  PLW-06010: keyword "PURGE" used as a defined name

       
      317/14 PLW-06010: keyword "PURGE" used as a defined name

       
      324/1  PLW-06010: keyword "PURGE" used as a defined name

       
      332/14 PLW-06010: keyword "PURGE" used as a defined name

      Creating Package Body STATSPACK...

      

      Warning: Package Body created with compilation errors.

      

      Errors for PACKAGE BODY STATSPACK:

      

      LINE/COL ERROR

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

      0/0 PL/SQL: Compilation unit analysis terminated

      260/9 PLW-07203: parameter 'O_UCOMMENT' may benefit from use of the NOCOPY compiler hint

      

      275/9 PLW-07203: parameter 'O_ALL_INIT' may benefit from use of the NOCOPY compiler hint

      

      276/9 PLW-07203: parameter 'O_OLD_SQL_CAPTURE_MTH' may benefit from use of the NOCOPY compiler hint

      

      277/9 PLW-07203: parameter 'O_PIN_STATSPACK' may benefit from use of the NOCOPY compiler hint

      

      303/10 PL/SQL: SQL Statement ignored

      307/17 PL/SQL: ORA-00942: table or view does not exist

      338/12 PL/SQL: SQL Statement ignored

      378/19 PL/SQL: ORA-00942: table or view does not exist

      384/14 PL/SQL: SQL Statement ignored

      384/26 PL/SQL: ORA-00942: table or view does not exist

      484/12 PL/SQL: SQL Statement ignored

      484/19 PL/SQL: ORA-00942: table or view does not exist

      552/16 PL/SQL: SQL Statement ignored

      552/28 PL/SQL: ORA-00942: table or view does not exist

      706/31 PLS-00201: identifier 'STATS$SNAPSHOT.SNAP_ID' must be declared

      

      NOTE:

      SPCPKG complete. Please check spcpkg.lis for any errors.

Subprograms

CLEAR_BASELINE	PURGE	V1024
GET_SNAP_ID	QAM_STATSPACK_PARAMETER	VERIFY_DBID_INSTANCE_NUMBER
MAKE_BASELINE	SNAP	VERIFY_SNAP_ID
MODIFY_STATSPACK_PARAMETER	STAT_CHANGES

CLEAR_BASELINE

Undocumented

Overload 1 statspack.clear_baseline( i_begin_snap IN NUMBER, i_end_snap IN NUMBER, i_snap_range IN BOOLEAN DEFAULT TRUE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL) RETURN INTEGER;

TBD

Overload 2 statspack.clear_baseline( i_begin_snap IN NUMBER, i_end_snap IN NUMBER, i_snap_range IN BOOLEAN DEFAULT TRUE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL);

TBD

Overload 3 statspack.clear_baseline( i_begin_date IN DATE, i_end_date IN DATE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL) RETURN INTEGER;

DECLARE

       retVal INTEGER;

      BEGIN

        retVal := perfstat.statspack.clear_baseline(SYSDATE-60, SYSDATE-31);

      END;

      /

      

      PL/SQL procedure successfully completed.

Overload 4 statspack.clear_baseline( i_begin_date IN DATE, i_end_date IN DATE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL);

TBD

GET_SNAP_ID

Undocumented statspack.get_snap_id( i_snap_time IN DATE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL) RETURN INTEGER;

DECLARE

       retVal INTEGER;

      BEGIN

        retVal := perfstat.statspack.get_snap_id(SYSDATE);

      END;

      /

      

      PL/SQL procedure successfully completed.

MAKE_BASELINE

Undocumented

Overload 1 statspack.make_baseline( i_begin_snap IN NUMBER, i_end_snap IN NUMBER, i_snap_range IN BOOLEAN DEFAULT TRUE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL) RETURN INTEGER;

TBD

Overload 2 statspack.make_baseline( i_begin_snap IN NUMBER, i_end_snap IN NUMBER, i_snap_range IN BOOLEAN DEFAULT TRUE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER);

TBD

Overload 3 statspack.make_baseline( i_begin_date IN DATE, i_end_date IN DATE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL) RETURN INTEGER;

DECLARE

       retVal INTEGER;

      BEGIN

        retVal := perfstat.statspack.make_baseline(SYSDATE-30, SYSDATE-23);

      END;

      /

      

      PL/SQL procedure successfully completed.

Overload 4 statspack.make_baseline( i_begin_date IN DATE, i_end_date IN DATE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL);

TBD

MODIFY_STATSPACK_PARAMETER

Undocumented statspack.dbms_modify_statspack_parameter( i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL, i_snap_level IN NUMBER DEFAULT NULL, i_session_id IN NUMBER DEFAULT NULL, i_ucomment IN VARCHAR2 DEFAULT NULL, i_num_sql IN NUMBER DEFAULT NULL, i_executions_th IN NUMBER DEFAULT NULL, i_parse_calls_th IN NUMBER DEFAULT NULL, i_disk_reads_th IN NUMBER DEFAULT NULL, i_buffer_gets_th IN NUMBER DEFAULT NULL, i_sharable_mem_th IN NUMBER DEFAULT NULL, i_version_count_th IN NUMBER DEFAULT NULL, i_seg_phy_reads_th IN NUMBER DEFAULT NULL, i_seg_log_reads_th IN NUMBER DEFAULT NULL, i_seg_buff_busy_th IN NUMBER DEFAULT NULL, i_seg_rowlock_w_th IN NUMBER DEFAULT NULL, i_seg_itl_waits_th IN NUMBER DEFAULT NULL, i_seg_cr_bks_rc_th IN NUMBER DEFAULT NULL, i_seg_cu_bks_rc_th IN NUMBER DEFAULT NULL, i_all_init IN VARCHAR2 DEFAULT NULL, i_old_sql_capture_mth IN VARCHAR2 DEFAULT NULL, i_pin_statspack IN VARCHAR2 DEFAULT NULL, i_modify_parameter IN VARCHAR2 DEFAULT 'TRUE');

TBD

PURGE

Undocumented

Overload 1 statspack.purge( i_begin_snap IN NUMBER, i_end_snap IN NUMBER, i_snap_range IN BOOLEAN DEFAULT TRUE, i_extended_purge IN BOOLEAN DEFAULT FALSE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL) RETRUN INTEGER;

TBD

Overload 2 statspack.purge( i_begin_snap IN NUMBER, i_end_snap IN NUMBER, i_snap_range IN BOOLEAN DEFAULT TRUE, i_extended_purge IN BOOLEAN DEFAULT FALSE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER);

TBD

Overload 3 statspack.purge( i_begin_date IN DATE, i_end_date IN DATE, i_extended_purge IN BOOLEAN DEFAULT FALSE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL) RETURN INTEGER;

TBD

Overload 4 statspack.purge( i_begin_date IN DATE, i_end_date IN DATE, i_extended_purge IN BOOLEAN DEFAULT FALSE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL);

TBD

Overload 5 statspack.purge( i_purge_before_date IN DATE, i_extended_purge IN BOOLEAN DEFAULT FALSE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL) RETURN INTEGER;

DECLARE

       retVal INTEGER;

      BEGIN

        retVal := perfstat.statspack.purge(SYSDATE);

        dbms_output.put_line(retVal);

      END;

      /

      

      PL/SQL procedure successfully completed.

Overload 6 statspack.purge( i_purge_before_date IN DATE, i_extended_purge IN BOOLEAN DEFAULT FALSE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL,

TBD

Overload 7 statspack.purge( i_num_days IN NUMBER, i_extended_purge IN BOOLEAN DEFAULT FALSE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL) RETURN INTEGER;

TBD

Overload 8 statspack.purge( i_num_days IN NUMBER, i_extended_purge IN BOOLEAN DEFAULT FALSE, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL);

TBD

QAM_STATSPACK_PARAMETER

Undocumented statspack.qam_statspack_parameter( i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL, i_snap_level IN NUMBER DEFAULT NULL, i_session_id IN NUMBER DEFAULT NULL, i_ucomment IN VARCHAR2 DEFAULT NULL, i_num_sql IN NUMBER DEFAULT NULL, i_executions_th IN NUMBER DEFAULT NULL, i_parse_calls_th IN NUMBER DEFAULT NULL, i_disk_reads_th IN NUMBER DEFAULT NULL, i_buffer_gets_th IN NUMBER DEFAULT NULL, i_sharable_mem_th IN NUMBER DEFAULT NULL, i_version_count_th IN NUMBER DEFAULT NULL, i_seg_phy_reads_th IN NUMBER DEFAULT NULL, i_seg_log_reads_th IN NUMBER DEFAULT NULL, i_seg_buff_busy_th IN NUMBER DEFAULT NULL, i_seg_rowlock_w_th IN NUMBER DEFAULT NULL, i_seg_itl_waits_th IN NUMBER DEFAULT NULL, i_seg_cr_bks_rc_th IN NUMBER DEFAULT NULL, i_seg_cu_bks_rc_th IN NUMBER DEFAULT NULL, i_all_init IN VARCHAR2 DEFAULT NULL, i_old_sql_capture_mth IN VARCHAR2 DEFAULT NULL, i_pin_statspack IN VARCHAR2 DEFAULT NULL, i_modify_parameter IN VARCHAR2 DEFAULT 'FALSE', o_snap_level OUT NUMBER, o_session_id OUT NUMBER, o_ucomment OUT VARCHAR2, o_num_sql OUT NUMBER, o_executions_th OUT NUMBER, o_parse_calls_th OUT NUMBER, o_disk_reads_th OUT NUMBER, o_buffer_gets_th OUT NUMBER, o_sharable_mem_th OUT NUMBER, o_version_count_th OUT NUMBER, o_seg_phy_reads_th OUT NUMBER, o_seg_log_reads_th OUT NUMBER, o_seg_buff_busy_th OUT NUMBER, o_seg_rowlock_w_th OUT NUMBER, o_seg_itl_waits_th OUT NUMBER, o_seg_cr_bks_rc_th OUT NUMBER, o_seg_cu_bks_rc_th OUT NUMBER, o_all_init OUT VARCHAR2, o_old_sql_capture_mth OUT VARCHAR2, o_pin_statspack OUT VARCHAR2);

TBD

SNAP

Undocumented

Overload 1 statspack.snap( i_snap_level IN NUMBER DEFAULT NULL, i_session_id IN NUMBER DEFAULT NULL, i_ucomment IN VARCHAR2 DEFAULT NULL, i_num_sql IN NUMBER DEFAULT NULL, i_executions_th IN NUMBER DEFAULT NULL, i_parse_calls_th IN NUMBER DEFAULT NULL, i_disk_reads_th IN NUMBER DEFAULT NULL, i_buffer_gets_th IN NUMBER DEFAULT NULL, i_sharable_mem_th IN NUMBER DEFAULT NULL, i_version_count_th IN NUMBER DEFAULT NULL, i_seg_phy_reads_th IN NUMBER DEFAULT NULL, i_seg_log_reads_th IN NUMBER DEFAULT NULL, i_seg_buff_busy_th IN NUMBER DEFAULT NULL, i_seg_rowlock_w_th IN NUMBER DEFAULT NULL, i_seg_itl_waits_th IN NUMBER DEFAULT NULL, i_seg_cr_bks_rc_th IN NUMBER DEFAULT NULL, i_seg_cu_bks_rc_th IN NUMBER DEFAULT NULL, i_all_init IN VARCHAR2 DEFAULT NULL, i_old_sql_capture_mth IN VARCHAR2 DEFAULT NULL, i_pin_statspack IN VARCHAR2 DEFAULT NULL, i_modify_parameter IN VARCHAR2 DEFAULT 'FALSE');

TBD

Overload 2 statspack.snap( i_snap_level IN NUMBER DEFAULT NULL, i_session_id IN NUMBER DEFAULT NULL, i_ucomment IN VARCHAR2 DEFAULT NULL, i_num_sql IN NUMBER DEFAULT NULL, i_executions_th IN NUMBER DEFAULT NULL, i_parse_calls_th IN NUMBER DEFAULT NULL, i_disk_reads_th IN NUMBER DEFAULT NULL, i_buffer_gets_th IN NUMBER DEFAULT NULL, i_sharable_mem_th IN NUMBER DEFAULT NULL, i_version_count_th IN NUMBER DEFAULT NULL, i_seg_phy_reads_th IN NUMBER DEFAULT NULL, i_seg_log_reads_th IN NUMBER DEFAULT NULL, i_seg_buff_busy_th IN NUMBER DEFAULT NULL, i_seg_rowlock_w_th IN NUMBER DEFAULT NULL, i_seg_itl_waits_th IN NUMBER DEFAULT NULL, i_seg_cr_bks_rc_th IN NUMBER DEFAULT NULL, i_seg_cu_bks_rc_th IN NUMBER DEFAULT NULL, i_all_init IN VARCHAR2 DEFAULT NULL, i_old_sql_capture_mth IN VARCHAR2 DEFAULT NULL, i_pin_statspack IN VARCHAR2 DEFAULT NULL, i_modify_parameter IN VARCHAR2 DEFAULT 'FALSE') RETURN INTEGER;

DECLARE

       retVal INTEGER;

      BEGIN

        retVal := perfstat.statspack.snap;

        dbms_output.put_line(retVal);

      END;

      /

      

      PL/SQL procedure successfully completed.

STAT_CHANGES

Undocumented statspack.stat_changes( bid IN NUMBER, eid IN NUMBER, db_ident IN NUMBER, inst_num IN NUMBER, parallel IN VARCHAR2, lhtr OUT NUMBER, bfwt OUT NUMBER, tran OUT NUMBER, chng OUT NUMBER, ucal OUT NUMBER, urol OUT NUMBER, rsiz OUT NUMBER, phyr OUT NUMBER, phyrd OUT NUMBER, phyrdl OUT NUMBER, phyrc OUT NUMBER, phyw OUT NUMBER, ucom OUT NUMBER, prse OUT NUMBER, hprse OUT NUMBER, recr OUT NUMBER, gets OUT NUMBER, slr OUT NUMBER, rlsr OUT NUMBER, rent OUT NUMBER, srtm OUT NUMBER, srtd OUT NUMBER, srtr OUT NUMBER, strn OUT NUMBER, lhr OUT NUMBER, bbc OUT VARCHAR2, ebc OUT VARCHAR2, bsp OUT VARCHAR2, esp OUT VARCHAR2, blb OUT VARCHAR2, bs OUT VARCHAR2, twt OUT NUMBER, logc OUT NUMBER, prscpu OUT NUMBER, tcpu OUT NUMBER, exe OUT NUMBER, prsela OUT NUMBER, bspm OUT NUMBER, espm OUT NUMBER, bfrm OUT NUMBER, efrm OUT NUMBER, blog OUT NUMBER, elog OUT NUMBER, bocur OUT NUMBER, eocur OUT NUMBER, bpgaalloc OUT NUMBER, epgaalloc OUT NUMBER, bsgaalloc OUT NUMBER, esgaalloc OUT NUMBER, bnprocs OUT NUMBER, enprocs OUT NUMBER, timstat OUT VARCHAR2, statlvl OUT VARCHAR2, bncpu OUT NUMBER, -- OS Stat encpu OUT NUMBER, bpmem OUT NUMBER, epmem OUT NUMBER, blod OUT NUMBER, elod OUT NUMBER, itic OUT NUMBER, btic OUT NUMBER, iotic OUT NUMBER, rwtic OUT NUMBER, utic OUT NUMBER, stic OUT NUMBER, vmib OUT NUMBER, vmob OUT NUMBER, oscpuw OUT NUMBER, dbtim OUT NUMBER, -- time model dbcpu OUT NUMBER, bgela OUT NUMBER, bgcpu OUT NUMBER, prstela OUT NUMBER, sqleela OUT NUMBER, conmela OUT NUMBER, dmsd OUT NUMBER, -- begin RAC dmfc OUT NUMBER, dmsi OUT NUMBER, pmrv OUT NUMBER, pmpt OUT NUMBER, npmrv OUT NUMBER, npmpt OUT NUMBER, dbfr OUT NUMBER, dpms OUT NUMBER, dnpms OUT NUMBER, glsg OUT NUMBER, glag OUT NUMBER, glgt OUT NUMBER, gccrrv OUT NUMBER, gccrrt OUT NUMBER, gccrfl OUT NUMBER, gccurv OUT NUMBER, gccurt OUT NUMBER, gccufl OUT NUMBER, gccrsv OUT NUMBER, gccrbt OUT NUMBER, gccrft OUT NUMBER, gccrst OUT NUMBER, gccusv OUT NUMBER, gccupt OUT NUMBER, gccuft OUT NUMBER, gccust OUT NUMBER, msgsq OUT NUMBER, msgsqt OUT NUMBER, msgsqk OUT NUMBER, msgsqtk OUT NUMBER, msgrq OUT NUMBER, msgrqt OUT NUMBER); -- end RAC

TBD

V1024

Undocumented statspack.v1024( i_num IN NUMBER, i_unit IN VARCHAR2, i_min_unit IN VARCHAR2, i_precision IN INTEGER, i_mag IN INTEGER) RETURN VARCHAR2;

TBD

VERIFY_DBID_INSTANCE_NUMBER

Undocumented statspack.verify_dbid_instance_number( i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL);

exec perfstat.statspack.verify_dbid_instance_number(2140826538, 1);

      BEGIN perfstat.statspack.verify_dbid_instance_number(2140826538, 1); END;

            *

      ERROR at line 1:

      ORA-20200: Database Instance specified (DB Id/Instance 2140826538/1) does not exist in STATS$DATABASE_INSTANCE

      ORA-06512: at "PERFSTAT.STATSPACK", line 773

      ORA-06512: at line 1

      

      SELECT * FROM stats$database_instance;

      

      no rows selected

VERIFY_SNAP_ID

Undocumented statspack.verify_snap_id( i_snap_id IN NUMBER, i_dbid IN NUMBER DEFAULT NULL, i_instance_number IN NUMBER DEFAULT NULL);

exec perfstat.statspack.verify_snap_id(2);

      

      PL/SQL procedure successfully completed.

PERFSTAT Queries

Idle Events SELECT * FROM perfstat.stats$idle_event; EVENT ------------------------------------------ AQ Cross Master idle AQ Proxy Cleanup Wait AQ: 12c message cache init wait AQPC idle ASM background timer ASM cluster membership changes Archiver: redo logs Backup Appliance Comm SGA setup wait Backup Appliance Servlet wait Backup Appliance Surrogate wait Backup Appliance waiting for work Backup Appliance waiting restore start Blockchain apply clean Blockchain reader process idle ... wakeup event for builder wakeup event for preparer wakeup event for reader wakeup time manager watchdog main loop 206 rows selected.

Snap Level Descriptions SELECT * FROM perfstat.stats$level_description; SNAP_LEVEL DESCRIPTION ---------- ------------------------------------------------------------------------ 0 This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information 5 This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels 6 This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels 7 This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels 10 This level includes capturing Child Latch statistics, along with all data captured by lower levels

Tables With Rows SELECT table_name, num_rows FROM dba_tables WHERE owner = 'PERFSTAT' AND num_rows <> 0 ORDER BY 1;