oracle/vg/stdby_sp_queries.sql

/****************************
 Standby Statspack 
****************************/

-- Set Schema name
alter session set current_schema = STDBYPERF;

-- Instance config
SELECT * FROM STATS$STANDBY_CONFIG;

-- Instance specific Packages
SELECT * FROM dba_objects where owner  = SYS_CONTEXT('USERENV','CURRENT_SCHEMA') and object_Type = 'PACKAGE';

-- Instance specific DB Links
SELECT * FROM dba_db_links where owner  = SYS_CONTEXT('USERENV','CURRENT_SCHEMA') ;

-- Tables
SELECT * FROM dba_objects where owner  = SYS_CONTEXT('USERENV','CURRENT_SCHEMA') and object_Type = 'TABLE';

-- Statspack parameters
SELECT * FROM STATS$STATSPACK_PARAMETER;

-- Snapshot Levels
SELECT * FROM STATS$LEVEL_DESCRIPTION;

SELECT * FROM STATS$DATABASE_INSTANCE ORDER BY db_unique_name, instance_name ;

-- Snapshots
SELECT * FROM STATS$SNAPSHOT where db_unique_name like '%' and instance_name like '%' order by  snap_id desc;

-- Instance init.ora parameters
SELECT * FROM STATS$PARAMETER;


SELECT * FROM STATS$BG_EVENT_SUMMARY;
SELECT * FROM STATS$WAITSTAT;
SELECT * FROM STATS$TIME_MODEL_STATNAME;

SELECT * FROM STATS$SYSTEM_EVENT;
SELECT * FROM STATS$SYSSTAT;
SELECT * FROM STATS$SYS_TIME_MODEL;

SELECT * FROM STATS$SESSION_EVENT;
SELECT * FROM STATS$SESSTAT;
SELECT * FROM STATS$SESS_TIME_MODEL;

SELECT * FROM STATS$EVENT_HISTOGRAM;
SELECT * FROM STATS$IDLE_EVENT;

SELECT * FROM STATS$FILESTATXS;
SELECT * FROM STATS$FILE_HISTOGRAM;

SELECT * FROM STATS$INSTANCE_CACHE_TRANSFER;
SELECT * FROM STATS$INSTANCE_RECOVERY;

SELECT * FROM STATS$JAVA_POOL_ADVICE;

SELECT * FROM STATS$LATCH;
SELECT * FROM STATS$LATCH_CHILDREN;
SELECT * FROM STATS$LATCH_MISSES_SUMMARY;
SELECT * FROM STATS$LATCH_PARENT;

SELECT * FROM STATS$LIBRARYCACHE;
SELECT * FROM STATS$LOCK_TYPE;
SELECT * FROM STATS$MANAGED_STANDBY;
SELECT * FROM STATS$MUTEX_SLEEP;
SELECT * FROM STATS$OSSTAT;
SELECT * FROM STATS$OSSTATNAME;

SELECT * FROM STATS$PGASTAT;
SELECT * FROM STATS$PGA_TARGET_ADVICE;
SELECT * FROM STATS$SGA;
SELECT * FROM STATS$SGASTAT;
SELECT * FROM STATS$SGA_TARGET_ADVICE;
SELECT * FROM STATS$SHARED_POOL_ADVICE;

SELECT * FROM STATS$PROCESS_MEMORY_ROLLUP;
SELECT * FROM STATS$PROCESS_ROLLUP;

SELECT * FROM STATS$SQLTEXT;
SELECT * FROM STATS$SQL_PLAN;
SELECT * FROM STATS$SQL_PLAN_USAGE;
SELECT * FROM STATS$SQL_STATISTICS;
SELECT * FROM STATS$SQL_SUMMARY;
SELECT * FROM STATS$SQL_WORKAREA_HISTOGRAM;

SELECT * FROM STATS$SEG_STAT;
SELECT * FROM STATS$SEG_STAT_OBJ;

SELECT * FROM STATS$TEMPSTATXS;
SELECT * FROM STATS$TEMP_SQLSTATS;
SELECT * FROM STATS$UNDOSTAT;



SELECT * FROM STATS$PROPAGATION_RECEIVER;
SELECT * FROM STATS$PROPAGATION_SENDER;
SELECT * FROM STATS$RECOVERY_PROGRESS;
SELECT * FROM STATS$RESOURCE_LIMIT;
SELECT * FROM STATS$ROLLSTAT;
SELECT * FROM STATS$ROWCACHE_SUMMARY;
SELECT * FROM STATS$RULE_SET;

SELECT * FROM STATS$THREAD;
SELECT * FROM STATS$BUFFERED_QUEUES;
SELECT * FROM STATS$BUFFERED_SUBSCRIBERS;
SELECT * FROM STATS$BUFFER_POOL_STATISTICS;
SELECT * FROM STATS$CR_BLOCK_SERVER;
SELECT * FROM STATS$CURRENT_BLOCK_SERVER;
SELECT * FROM STATS$DB_CACHE_ADVICE;
SELECT * FROM STATS$DLM_MISC;
SELECT * FROM STATS$DYNAMIC_REMASTER_STATS;
SELECT * FROM STATS$ENQUEUE_STATISTICS;

SELECT * FROM STATS$STREAMS_APPLY_SUM;
SELECT * FROM STATS$STREAMS_CAPTURE;
SELECT * FROM STATS$STREAMS_POOL_ADVICE;


---------------------------------------------------------
--  System Statistics
---------------------------------------------------------
--SELECT * FROM (
SELECT /*+ FULL(s) FULL(ss) */ 
       s.snap_time
     , s.db_unique_name
     , s.instance_name
	  , ss.statistic#
     , ss.name
     , ss.value - LAG(ss.value) OVER (PARTITION BY ss.db_unique_name, ss.instance_name, ss.name ORDER BY ss.snap_id ASC) value_diff 
FROM STATS$SNAPSHOT s
     JOIN STATS$SYSSTAT ss ON s.db_unique_name = ss.db_unique_name and ss.instance_name = s.instance_name and ss.snap_id = s.snap_id
where 1=1
and s.db_unique_name = 'stby02_pr01pimi' 
and s.instance_name = 'pr01pimi1'
and ss.name like '%%'
and s.snap_time between TO_DATE('20-OCT-15 02:00:00') and TO_DATE('20-OCT-15 09:00:00')
order by s.snap_time
   , s.db_unique_name
   , s.instance_name
   , ss.name
--) WHERE waits is not null
;


---------------------------------------------------------
--  System Events
---------------------------------------------------------

--SELECT * FROM (
SELECT /*+ FULL(s) FULL(e) */ 
     s.snap_time
   , s.db_unique_name
   , s.instance_name
   , e.event
   , e.total_waits - LAG(e.total_waits) OVER (PARTITION BY e.db_unique_name, e.instance_name, e.event ORDER BY e.snap_id ASC) waits 
   , ROUND((e.total_timeouts - LAG(e.total_timeouts) OVER (PARTITION BY e.db_unique_name, e.instance_name, e.event ORDER BY e.snap_id ASC))/100000) timeouts 
   , ROUND((e.time_waited_micro - LAG(e.time_waited_micro) OVER (PARTITION BY e.db_unique_name, e.instance_name, e.event ORDER BY e.snap_id ASC))/100000) time_waited_sec 
   , e.total_waits_fg - LAG(e.total_waits_fg) OVER (PARTITION BY e.db_unique_name, e.instance_name, e.event ORDER BY e.snap_id ASC) waits_fg
   , ROUND((e.total_timeouts_fg - LAG(e.total_timeouts_fg) OVER (PARTITION BY e.db_unique_name, e.instance_name, e.event ORDER BY e.snap_id ASC))/100000) timeouts_fg 
   , ROUND((e.time_waited_micro_fg - LAG(e.time_waited_micro_fg) OVER (PARTITION BY e.db_unique_name, e.instance_name, e.event ORDER BY e.snap_id ASC))/100000) time_waited_fg_sec 
FROM STDBYPERF.STATS$SNAPSHOT s
     JOIN STDBYPERF.STATS$SYSTEM_EVENT e ON s.db_unique_name = e.db_unique_name and e.instance_name = s.instance_name and e.snap_id = s.snap_id
where 1=1
and s.db_unique_name = 'stby02_pr01pimi' 
and s.instance_name = 'pr01pimi1'
and e.event like 'library cache lock'
and s.snap_time between TO_DATE('20-OCT-15 02:00:00') and TO_DATE('20-OCT-15 09:00:00')
order by s.snap_time
   , s.db_unique_name
   , s.instance_name
   , e.event
--) WHERE waits is not null
;