-- testcase5 :
--
-- check if gather stats incremental works
--
-- Rainer Hartwig
-- 2017-11-26
--


set timing on echo on pages 5000 lines 150
set serveroutput on

spool testcase5


col PARTITION_NAME 		format a10
col PUBLISH			format a8
col INCREMENTAL_STALENESS	format a35
col INCREMENTAL_LEVEL		format a12
col INCREMENTAL			format a8
col STALE_PERCENT		format a4
col METHOD_OPT			format a22

col TABNAME			format a12
col PARTNAME			format a10
col COLNAME			format a10
col SYNOPSIS_CREATED		format a18

col NAME			format a15
col INCREMENTAL			format a15

col TABLE_NAME			format a8
col GLOBAL_STATS		format a3
col USER_STATS			format a3
col HISTOGRAM			format a10

col LAST_ANALYZE		format a18

drop table INCSTATS
/

CREATE TABLE INCSTATS
PARTITION BY RANGE (val)
(
PARTITION P_100 VALUES LESS THAN (100) ,
PARTITION P_200 VALUES LESS THAN (200) ,
PARTITION P_300 VALUES LESS THAN (300) ,
PARTITION P_400 VALUES LESS THAN (400) ,
PARTITION P_500 VALUES LESS THAN (500) ,
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
)
ENABLE ROW MOVEMENT
LOGGING
PARALLEL 8
as SELECT rownum as VAL
FROM dual CONNECT BY level < 450
/


-- Enable Incremental feature 
BEGIN
	DBMS_STATS.SET_TABLE_PREFS (
		ownname  => USER, 
		tabname  => 'INCSTATS', 
		pname    => 'INCREMENTAL', 
		pvalue   => 'TRUE');
END;
/

-- no histograms
BEGIN
	DBMS_STATS.SET_TABLE_PREFS (
		ownname  => USER, 
		tabname  => 'INCSTATS', 
		pname    => 'METHOD_OPT', 
		pvalue   => 'FOR ALL COLUMNS SIZE 1');
END;
/

-- granularity=>'APPROX_GLOBAL AND PARTITION'
BEGIN
	DBMS_STATS.SET_TABLE_PREFS (
		ownname  => USER, 
		tabname  => 'INCSTATS', 
		pname    => 'GRANULARITY', 
		pvalue   => 'APPROX_GLOBAL AND PARTITION');
END;
/

-- Set synopsis creation at partition level
BEGIN
	DBMS_STATS.SET_TABLE_PREFS (
		ownname  => USER, 
		tabname  => 'INCSTATS', 
		pname    => 'INCREMENTAL_LEVEL', 
		pvalue   => 'PARTITION');	
END;
/


BEGIN
	DBMS_STATS.SET_TABLE_PREFS (
		ownname  => USER, 
		tabname  => 'INCSTATS', 
		pname    => 'PUBLISH', 
		pvalue   => 'TRUE');
END;
/

BEGIN
	DBMS_STATS.SET_TABLE_PREFS (
		ownname  => USER,
		tabname  => 'INCSTATS', 
		pname    => 'INCREMENTAL_STALENESS', 
		pvalue   => 'USE_STALE_PERCENT, USE_LOCKED_STATS');
END;
/

BEGIN
	DBMS_STATS.SET_TABLE_PREFS (
		ownname  => USER,
		tabname  => 'INCSTATS', 
		pname    => 'STALE_PERCENT', 
		pvalue   => '15');
END;
/



SELECT dbms_stats.get_prefs('PUBLISH',USER,'INCSTATS')				"PUBLISH"
	,  dbms_stats.get_prefs('INCREMENTAL_STALENESS',USER,'INCSTATS')	"INCREMENTAL_STALENESS"
	,  dbms_stats.get_prefs('INCREMENTAL_LEVEL',USER,'INCSTATS')		"INCREMENTAL_LEVEL"
	,  dbms_stats.get_prefs('INCREMENTAL',USER,'INCSTATS')			"INCREMENTAL"
	,  dbms_stats.get_prefs('STALE_PERCENT',USER,'INCSTATS')		"STALE_PERCENT"
	,  dbms_stats.get_prefs('METHOD_OPT',USER,'INCSTATS')			"METHOD_OPT"
FROM dual
/


-- gather all stats
--
BEGIN
  dbms_stats.flush_database_monitoring_info;
  dbms_stats.gather_table_stats(USER,'INCSTATS');
END;
/


-- gather partition stats for all partitions
--
BEGIN
  dbms_stats.flush_database_monitoring_info;
  dbms_stats.gather_table_stats(USER,'INCSTATS');
  for i in (SELECT partition_name pname
            FROM user_tab_partitions
	    WHERE table_name = 'INCSTATS' 
	    order by partition_position) loop
	dbms_output.put_line('P-Name: '||i.pname);
	dbms_stats.gather_table_stats(ownname => USER
				, tabname => 'INCSTATS'
				, partname => i.pname
				, granularity=>'PARTITION');
	-- wait 2 sec so every part. will have different timestamp
	dbms_lock.sleep(2);
  end loop;
END;
/


-- show synopsis
--
SELECT o.name         "TABNAME",
       p.subname      "PARTNAME",
       c.name         "COLNAME",
       TO_CHAR(h.analyzetime, 'DD.MM.YY HH24:MI:SS')  "SYNOPSIS_CREATED"
FROM   sys.wri$_optstat_synopsis_head$ h,
       sys.obj$ o,
       sys.user$ u,
       sys.col$ c,
       ( ( SELECT tabpart$.bo#  BO#,
                  tabpart$.obj# OBJ#
           FROM   sys.tabpart$ tabpart$ )
         UNION ALL
         ( SELECT tabcompart$.bo#  BO#,
                  tabcompart$.obj# OBJ#
           FROM   sys.tabcompart$ tabcompart$ ) ) tp,
       sys.obj$ p
WHERE  u.name = USER AND
       o.name = 'INCSTATS' AND
       tp.obj# = p.obj# AND
       h.bo# = tp.bo# AND
       h.group# = tp.obj# * 2 AND
       h.bo# = c.obj#(+) AND
       h.intcol# = c.intcol#(+) AND
       o.owner# = u.user# AND
       h.bo# = o.obj#
ORDER  BY 4,1,2,3
/


SELECT partition_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tab_partitions
WHERE table_name = 'INCSTATS'
union all 
SELECT table_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tables
WHERE table_name = 'INCSTATS'
ORDER BY 2,1
/

SELECT table_name
	, global_stats
	, user_stats
	, histogram
FROM user_tab_col_statistics 
WHERE table_name like 'INCSTATS%' 
ORDER BY 1
/


-- gather table stats
-- no DML => no re-gather stats on PARTITION, only on GLOBALS
--
BEGIN
  dbms_stats.flush_database_monitoring_info;
  dbms_stats.gather_table_stats(ownname => USER
  			, tabname => 'INCSTATS'
			, granularity=>'APPROX_GLOBAL AND PARTITION');
END;
/

SELECT partition_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tab_partitions
WHERE table_name = 'INCSTATS'
union all 
SELECT table_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tables
WHERE table_name = 'INCSTATS'
ORDER BY 2,1
/


-- show if incremental is on
--
SELECT o.name as NAME
	, decode(bitand(h.spare2, 8), 8, 'yes', 'no') INCREMENTAL
FROM   sys.hist_head$ h
	, sys.obj$ o
WHERE  h.obj# = o.obj#
AND    o.name = 'INCSTATS'
AND    o.subname is null
/



-- wait 11 secs
--
BEGIN
  dbms_lock.sleep(11);
END;
/

-- re-gather stats w/o any changes
-- no DML => no re-gather stats on PARTITION, only on GLOBALS
--
BEGIN
  dbms_stats.flush_database_monitoring_info;
  dbms_stats.gather_table_stats(ownname => USER
  			, tabname => 'INCSTATS'
			, granularity=>'APPROX_GLOBAL AND PARTITION');
END;
/


SELECT partition_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tab_partitions
WHERE table_name = 'INCSTATS'
union all 
SELECT table_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tables
WHERE table_name = 'INCSTATS'
ORDER BY 2,1
/

SELECT table_name
	, global_stats
	, user_stats
	, histogram
FROM user_tab_col_statistics 
WHERE table_name like 'INCSTATS%' 
ORDER BY 1
/


-- insert new rows 
-- into partition P_MAX
--
BEGIN
  for i in (SELECT 999 + rownum as NR
	    FROM dual CONNECT BY level < 21) loop
    INSERT INTO INCSTATS VALUES (i.nr);
  end loop;
  commit;
END;
/


-- re-gather stats
-- => should only the latest and GOBALS be changed
--
BEGIN
  dbms_stats.flush_database_monitoring_info;
  dbms_stats.gather_table_stats(ownname => USER
  			, tabname => 'INCSTATS'
			, granularity=>'APPROX_GLOBAL AND PARTITION');
END;
/


SELECT partition_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tab_partitions
WHERE table_name = 'INCSTATS'
union all 
SELECT table_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tables
WHERE table_name = 'INCSTATS'
ORDER BY 2,1
/

SELECT table_name
	, global_stats
	, user_stats
	, histogram
FROM user_tab_col_statistics 
WHERE table_name like 'INCSTATS%' 
ORDER BY 1
/


-- update less than stale_percent rows
-- => no gather stats on partition level
--
update INCSTATS
set val=val+1
where val between 101 and 109
/
commit
/

BEGIN
  dbms_stats.flush_database_monitoring_info;
  dbms_stats.gather_table_stats(ownname => USER
  			, tabname => 'INCSTATS'
			, granularity=>'APPROX_GLOBAL AND PARTITION');
END;
/

SELECT partition_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tab_partitions
WHERE table_name = 'INCSTATS'
union all 
SELECT table_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tables
WHERE table_name = 'INCSTATS'
ORDER BY 2,1
/

SELECT table_name
	, global_stats
	, user_stats
	, histogram
FROM user_tab_col_statistics 
WHERE table_name like 'INCSTATS%' 
ORDER BY 1
/


-- update greater than stale_percent rows
-- => x gather stats on only ONE partition AND GLOBALS
--
update INCSTATS
set val=val+1
where val between 101 and 160
/
commit
/

BEGIN
  dbms_stats.flush_database_monitoring_info;
  dbms_stats.gather_table_stats(ownname => USER
  			, tabname => 'INCSTATS'
			, granularity=>'APPROX_GLOBAL AND PARTITION');
END;
/

SELECT partition_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tab_partitions
WHERE table_name = 'INCSTATS'
union all 
SELECT table_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tables
WHERE table_name = 'INCSTATS'
ORDER BY 2,1
/

SELECT table_name
	, global_stats
	, user_stats
	, histogram
FROM user_tab_col_statistics 
WHERE table_name like 'INCSTATS%' 
ORDER BY 1
/

-- no dml occured
-- => no partition should re-gathered
-- => ONLY GLOBALS
--
BEGIN
  dbms_stats.flush_database_monitoring_info;
  dbms_stats.gather_table_stats(ownname => USER
  			, tabname => 'INCSTATS'
			, granularity=>'APPROX_GLOBAL AND PARTITION');
END;
/

SELECT partition_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tab_partitions
WHERE table_name = 'INCSTATS'
union all 
SELECT table_name
	, TO_CHAR(last_analyzed, 'DD.MM.YY HH24:MI:SS' ) last_analyze
	, num_rows
FROM user_tables
WHERE table_name = 'INCSTATS'
ORDER BY 2,1;

SELECT table_name
	, global_stats
	, user_stats
	, histogram
FROM user_tab_col_statistics 
WHERE table_name like 'INCSTATS%' 
ORDER BY 1;


-- show if incremental is on
--
SELECT o.name as NAME
	, decode(bitand(h.spare2, 8), 8, 'yes', 'no') INCREMENTAL
FROM   sys.hist_head$ h
	, sys.obj$ o
WHERE  h.obj# = o.obj#
AND    o.name = 'INCSTATS'
AND    o.subname is null;


-- show synopsis
--
SELECT o.name         "TABNAME",
       p.subname      "PARTNAME",
       c.name         "COLNAME",
       TO_CHAR(h.analyzetime, 'DD.MM.YY HH24:MI:SS')  "SYNOPSIS_CREATED"
FROM   sys.wri$_optstat_synopsis_head$ h,
       sys.obj$ o,
       sys.user$ u,
       sys.col$ c,
       ( ( SELECT tabpart$.bo#  BO#,
                  tabpart$.obj# OBJ#
           FROM   sys.tabpart$ tabpart$ )
         UNION ALL
         ( SELECT tabcompart$.bo#  BO#,
                  tabcompart$.obj# OBJ#
           FROM   sys.tabcompart$ tabcompart$ ) ) tp,
       sys.obj$ p
WHERE  u.name = USER AND
       o.name = 'INCSTATS' AND
       tp.obj# = p.obj# AND
       h.bo# = tp.bo# AND
       h.group# = tp.obj# * 2 AND
       h.bo# = c.obj#(+) AND
       h.intcol# = c.intcol#(+) AND
       o.owner# = u.user# AND
       h.bo# = o.obj#
ORDER  BY 4,1,2,3
/

spool off