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.