Manual AWR Baselinje

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 49,
    end_snap_id   => 50,
    baseline_name => 'AWR_21AUG_1_130PM');
END;
/

Display Plan for SQL from STS(Manual)

select sql_id from dba_sqlset_statements
where sqlset_name = '&STS_Name'
order by sql_id;

SELECT * FROM table ( DBMS_XPLAN.DISPLAY_SQLSET( '&STS_NAME','&SQL_ID'));

schema Stat Refresh and print objects

DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_schema_stats(ownname=>'VIDSDDL',objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/

Manual STS

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'MY_SQL_TUNING_SET_TEST',
    description  => 'Testing');
END;
/

DECLARE
 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN baseline_cursor FOR
    SELECT VALUE(p)
    FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
                  'peak baseline',
                   NULL, NULL,
                   'elapsed_time',
                   NULL, NULL, NULL,
                   30)) p;

    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'my_sql_tuning_set_test',
             populate_cursor => baseline_cursor);
END;
/



-- TO select sql statements within snapshot range
declare
   baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
    open baseline_ref_cursor for
     select VALUE(p) from             table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(10, 20,
   'executions > 1 and disk_reads >   10000',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
 
 DBMS_SQLTUNE.LOAD_SQLSET('MY_SQL_TUNING_SET_TEST',baseline_ref_cursor);
 
end;
/

Generate Stat script for a schema

set lines 350
set pages 9999
set head off
set feedback off
set verify off
set echo off
prompt "Enter Schema Name"
accept OWNER
spool &owner-stat.sql
select 'execute dbms_stats.gather_table_stats(OWNNAME=>''&OWNER'', TABNAME=>'''||table_name||''''||',method_opt=>''FOR ALL COLUMNS SIZE AUTO'',degree=>DBMS_STATS.DEFAULT_DEGREE,cascade=>DBMS_STATS.AUTO_CASCADE);' from dba_tables where owner = '&OWNER' order by table_name;
spool off

Find Index

column table_name format a30

column index_name format a30

column column_name format a20

break on table_name,index_name

set lines 132

select table_name,index_name,column_name from dba_ind_columns where index_owner='&OWNER' and table_name = '&tab_name' order by 1,2,column_position;

SGA info

show parameter db_cache_size
show parameter shared_pool_size
show parameter large_pool_size
show parameter java_pool_size

show parameter pga_aggregate_target

select * from v$sqainfo;


v$sga - Displays summary information about the system global area (SGA).

v$sgainfo - Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.

v$sgastat - Displays detailed information about the SGA.

v$sga_dynamic_components - Displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup.

v$sga_dynamic_free_memory - Displays information about the amount of SGA memory available for future dynamic SGA resize operations.

v$sga_resize_ops - Displays information about the last 100 completed SGA resize operations.

v$sga_current_resize_ops - Displays information about SGA resize operations which are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.

Schema Stat

execute dbms_stats.gather_schema_stats( ownname=>'&SCHEMA_NAME', options=>'GATHER AUTO', method_opt=>'FOR ALL COLUMNS SIZE AUTO', cascade=>TRUE);

Stale Stat

Below query displays if stat is stale for a table

 select OWNER,TABLE_NAME,num_rows,STALE_STATS from dba_tab_statistics where table_name = 'TEST1';

dba_tab_modifications keeps track of number of inserts/updates/deletes for a table. Around 10% of changes, causes stat for a table to be stale.

exec dbms_stats.flush_database_monitoring_info; 

Above would flush stat related monitroing info in dictionary tables. e.g stale_stat flag.

Tablespace Usage

        set term on
        set echo on
        column dummy noprint
        column  pct_used format 999.9       heading "%|Used"
        column  name    format a26      heading "Tablespace Name"
        column  Mbytes   format 999,999,999    heading "MBytes"
        column  used    format 999,999,999   heading "Used"
        column  free    format 999,999,999  heading "Free"
        column  largest    format 999,999,999  heading "Largest"
        column  max_size format 999,999,999 heading "MaxPoss|Mbytes"
        column  pct_max_used format 999.9       heading "%|Max|Used"
        break   on report
        compute sum of Mbytes on report
        compute sum of free on report
        compute sum of used on report
        set linesize 110
        set pagesize 100
        set feed off
        select (select decode(extent_management,'LOCAL','*',' ') from dba_tablespaces
        where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name,
        mbytes_alloc mbytes,mbytes_alloc-nvl(mbytes_free,0) used,nvl(mbytes_free,0) free,
        ((mbytes_alloc-nvl(mbytes_free,0))/mbytes_alloc)*100 pct_used,
        nvl(largest,0) largest,nvl(mbytes_max,mbytes_alloc) Max_Size,
        decode( mbytes_max, 0, 0, (mbytes_alloc/mbytes_max)*100) pct_max_used
        from ( select sum(bytes)/(1024*1024) Mbytes_free,max(bytes)/(1024*1024) largest,tablespace_name
        from  sys.dba_free_space group by tablespace_name ) a, (select sum(bytes)/(1024*1024) Mbytes_alloc,
        sum(maxbytes)/(1024*1024) Mbytes_max, tablespace_name
        from sys.dba_data_files  group by tablespace_name
        union all
        select sum(bytes)/(1024*1024) Mbytes_alloc,sum(maxbytes)/(1024*1024) Mbytes_max,tablespace_name
        from sys.dba_temp_files group by tablespace_name )b
        where a.tablespace_name (+) = b.tablespace_name order by a.tablespace_name asc;

dbms_stats AUTO job

To check if dbms_stat is scheduled to run in Maint Window.
==========================================

SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

select job_name, job_type, program_name, schedule_name, job_class
  from dba_scheduler_jobs
  where job_name = 'GATHER_STATS_JOB'; 

select  PROGRAM_ACTION 
  from dba_scheduler_programs 
  where PROGRAM_NAME = 'GATHER_STATS_PROG';  

select * 
  from DBA_SCHEDULER_WINGROUP_MEMBERS
  where WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'; 

select window_name, repeat_interval, duration
  from dba_scheduler_windows
  where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW');

To Disable the automatic statistics collection in 10G , you can execute the following procedure as sysdba
======================================================================

exec dbms_scheduler.disable('GATHER_STAT_AUTO');