--
-- Script for concurrent statistics gathering for a subset of objects in a schema
-- Author: Maria Colgan maria.colgan@oracle.com (@SQLMaria)

connect sh/sh

set pages 9999
set lines 150
set echo on

-- Setup
-- Run setup.sql to create multiple copies of the SALES and COSTS table in the SH schema


--step 0. Delete stats
BEGIN
  DBMS_STATS.DELETE_SCHEMA_STATS('SH');
END;
/


SELECT table_name, to_char(last_analyzed, 'yyyy/mm/dd hh:mi:ss') LA
FROM   user_tables
WHERE  table_Name in ('SALES','COSTS','SALES2','COSTS2','SALES3');

-- step 1. Enable concurrent statistics.

BEGIN
  DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
END;
/

SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;


-- Step 2. Enable incremental statistics on the 5 partitioned tables
BEGIN
  DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');
END;
/

BEGIN
  DBMS_STATS.SET_TABLE_PREFS('SH','COSTS','INCREMENTAL','TRUE');
END;
/

BEGIN
  DBMS_STATS.SET_TABLE_PREFS('SH','SALES2','INCREMENTAL','TRUE');
END;
/

BEGIN
  DBMS_STATS.SET_TABLE_PREFS('SH','COSTS2','INCREMENTAL','TRUE');
END;
/

BEGIN
  DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');
END;
/

-- Step 3. Gather schema statsitcs using the filte_obj_list parameter


DECLARE
      filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
      obj_lst     DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
BEGIN
  filter_lst.extend(5);
  filter_lst(1).ownname := 'SH';
  filter_lst(1).objname := 'SALES';
  filter_lst(2).ownname := 'SH';
  filter_lst(2).objname := 'COSTS';
  filter_lst(3).ownname := 'SH';
  filter_lst(3).objname := 'SALES2';
  filter_lst(4).ownname := 'SH';
  filter_lst(4).objname := 'COSTS2';
  filter_lst(5).ownname := 'SH';
  filter_lst(5).objname := 'SALES3';
 DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SH',objlist=>obj_lst, obj_filter_list=>filter_lst);
END;
/

-- Step 4. Show only the 5 tables have statsitcs gathered on them
SELECT systimestamp FROM DUAL;

SELECT table_name, to_char(last_analyzed, 'yyyy/mm/dd hh:mi:ss') LA
FROM   user_tables;

--