XP_STAT - Custom statistics

You may create the object with SYSTEM and execute as SYS

In any case be sure to grant these privileges to the owner

grant ANALYZE ANY to "USR";
grant CREATE ANY PROCEDURE to "USR";
grant CREATE PROCEDURE to "USR";
grant CREATE PUBLIC SYNONYM to "USR";
grant CREATE SESSION to "USR";
grant CREATE TABLE to "USR";
grant SELECT ANY DICTIONARY to "USR";
grant SELECT ANY TABLE to "USR";

Table XP_LAST_OPERATIONS

CREATE TABLE XP_LAST_OPERATIONS
(
  NAME             VARCHAR2(50 BYTE),
  DESCRIPTION      VARCHAR2(500 BYTE),
  DATETIME         DATE,
  SEGMENT          VARCHAR2(200 BYTE),
  XP_SESSION_NAME  VARCHAR2(32 BYTE),
  OWNER            VARCHAR2(20 BYTE)
);

ALTER TABLE XP_LAST_OPERATIONS ADD (
  UNIQUE (NAME)
    USING INDEX);

CREATE PUBLIC SYNONYM XP_LAST_OPERATIONS FOR XP_LAST_OPERATIONS;

Table XP_STATS

CREATE TABLE XP_STATS
(
  OWNER                        VARCHAR2(15 BYTE) NOT NULL,
  SEGMENT_NAME                 VARCHAR2(30 BYTE) NOT NULL,
  PARTITION_NAME               VARCHAR2(30 BYTE),
  BLOCK_CHANGES_WHEN_ANALYZED  NUMBER,
  LAST_DB_BLOCK_CHANGES        NUMBER           DEFAULT 0,
  CURR_DB_BLOCK_CHANGES        NUMBER,
  STATS_DATE                   DATE,
  LAST_CHECK_DATE              DATE,
  SEGMENT_TYPE                 VARCHAR2(50 BYTE) NOT NULL,
  ANALYZED_IN_LAST_SESSION     INTEGER          DEFAULT 0                     NOT NULL,
  JUST_INSERTED                INTEGER,
  CURR_BLOCKS                  NUMBER,
  XP_SESSION_NAME              VARCHAR2(32 BYTE),
  H_LAST_DATE                  DATE,
  H_LAST_SESSION               VARCHAR2(32 BYTE),
  H_ANALYZED_IN_LAST_SESSION   INTEGER          DEFAULT 0                     NOT NULL
);

CREATE INDEX CUSTOM_STATS_TUNING ON XP_STATS
(OWNER, SEGMENT_NAME);

CREATE UNIQUE INDEX CUSTOM_STATS_UNIQUE ON XP_STATS
(OWNER, PARTITION_NAME, SEGMENT_NAME);

ALTER TABLE XP_STATS ADD (
  CONSTRAINT JUST_INSERTED_NOT_NULL CHECK (not just_inserted is null));

CREATE PUBLIC SYNONYM XP_STATS FOR XP_STATS;

Table XP_STAT_LOG

CREATE TABLE XP_STAT_LOG
(
  MSG              VARCHAR2(2000 BYTE),
  DATA             DATE                         DEFAULT sysdate,
  LOG_LEVEL        NUMBER,
  PROG             TIMESTAMP(6),
  SEGMENT          VARCHAR2(200 BYTE),
  XP_SESSION_NAME  VARCHAR2(32 BYTE),
  OWNER            VARCHAR2(20 BYTE)
);

CREATE PUBLIC SYNONYM XP_STAT_LOG FOR XP_STAT_LOG;

Table XP_COLUMN_HISTOGRAMS

CREATE TABLE XP_COLUMN_HISTOGRAMS
(
  OWNER           VARCHAR2(15 BYTE)             NOT NULL,
  SEGMENT_NAME    VARCHAR2(30 BYTE)             NOT NULL,
  PARTITION_NAME  VARCHAR2(30 BYTE),
  METHOD_OPT      VARCHAR2(400 BYTE)            NOT NULL
);

CREATE INDEX XP_HIST_TUNING ON XP_COLUMN_HISTOGRAMS
(OWNER, SEGMENT_NAME);

CREATE UNIQUE INDEX XP_HIST_UNIQUE ON XP_COLUMN_HISTOGRAMS
(OWNER, SEGMENT_NAME, PARTITION_NAME);

CREATE PUBLIC SYNONYM XP_COLUMN_HISTOGRAMS FOR XP_COLUMN_HISTOGRAMS;

View XPV_STATS

CREATE OR REPLACE VIEW XPV_STATS AS
                                                            select x.*
                                    --, (10*curr_blocks/100) "10_PERC_OF_BLOCKS"
                                    --, (10*curr_blocks/100)+ curr_db_block_changes REQ_BLOCKS_CHNG_FOR_10
                                    --, sign(curr_db_block_changes - ((10*curr_blocks/100)+ curr_db_block_changes)) STATS_FOR_10
                                    , owner||'.'||segment_name||'.'||partition_name own_seg_part
                                    , xp_stat.getLogForSegment(owner||'.'||segment_name||'.'||partition_name, xp_session_name) segment_log
                                    from xp_stats x;

View XPV_STATS_NOW

CREATE OR REPLACE VIEW XPV_STATS_NOW AS
                                                            select a.*
                                , (10*blocks/100) "10_PERC_OF_BLOCKS"
                                , (10*blocks/100)+ curr_block_changes REQ_BLOCKS_CHNG_FOR_10
                                , sign(curr_block_changes - ((10*blocks/100)+ curr_block_changes)) STATS_FOR_10
                                from (
                               
                	select s.*,
                               
                	nvl((select value from v$segment_statistics where statistic#=2 and owner=s.owner and object_name=s.segment_name
                               
                	and nvl(subobject_name, ' ')=nvl(s.partition_name, ' ')), 0) curr_block_changes
                               
                	from dba_segments s
                                ) a;

CREATE PUBLIC SYNONYM XPV_STATS_NOW FOR SYSTEM.XPV_STATS_NOW;

View XPV_ANALYZABLE_HISTOGRAMS

CREATE OR REPLACE VIEW XPV_ANALYZABLE_HISTOGRAMS AS
                                                            select nvl(x.owner, h.owner) owner, nvl(x.segment_name, h.segment_name) segment_name,
                                nvl(x.partition_name, h.partition_name) partition_name,
                                nvl(x.owner, h.owner)||'.'||nvl(x.segment_name, h.segment_name)||'.'||nvl(x.partition_name, h.partition_name) name,

                                h.method_opt, segment_exists, partition_exists, x.analyzed_in_last_session,
                                mispelled_partition
                                from XPV_COLUMN_HISTOGRAMS h, xp_stats x
                                where x.owner (+)= h.owner and x.segment_name (+)= h.segment_name
                                and nvl(x.partition_name, ' ') = nvl(h.partition_name, nvl(x.partition_name, ' '));CREATE PUBLIC SYNONYM XPV_ANALYZABLE_HISTOGRAMS FOR SYSTEM.XPV_ANALYZABLE_HISTOGRAMS;

View XPV_COLUMN_HISTOGRAMS

CREATE OR REPLACE FORCE VIEW XPV_COLUMN_HISTOGRAMS
                            AS
                            select a.*
                            , decode(partition_name, null, 0, decode(partition_exists, 0, 1, 0)) mispelled_partition
                            from (
                           
            	select h.owner||'.'||h.segment_name||'.'||h.partition_name name, method_opt
                           
            	, nvl((select 1 from xp_stats x where owner = h.owner and segment_name = h.segment_name and rownum = 1),0) segment_exists
                           
            	, nvl((select 1 from xp_stats x where owner = h.owner and segment_name = h.segment_name and
                           
            	    partition_name = h.partition_name),0) partition_exists
                           
            	, h.owner, h.segment_name, h.partition_name
                           
            	from xp_column_histograms h
                            ) a;

CREATE PUBLIC SYNONYM XPV_COLUMN_HISTOGRAMS FOR SYSTEM.XPV_COLUMN_HISTOGRAMS;

View XPV_REAL_TIME_LAST_ANALYZED

CREATE OR REPLACE VIEW XPV_REAL_TIME_LAST_ANALYZED AS
select a.owner, a.segment_name, a.partition_name, a.segment_type, a.owner||'.'||a.segment_name||'.'||a.partition_name name,
nvl(nvl(nvl(last_analyzed_index, last_analyzed_table), last_analyzed_ind_part), last_analyzed_tab_part) last_analyzed
 from (
	select s.owner, s.segment_name, s.partition_name, s.segment_type
	, (select last_analyzed from dba_indexes where s.segment_type='INDEX' and owner = s.owner and index_name = s.segment_name) last_analyzed_index
	, (select last_analyzed from dba_ind_partitions where s.segment_type='INDEX PARTITION' and index_owner = s.owner and index_name = s.segment_name and partition_name = s.partition_name) last_analyzed_ind_part
	, (select last_analyzed from dba_tables where s.segment_type='TABLE' and owner = s.owner and table_name = s.segment_name) last_analyzed_table
	, (select last_analyzed from dba_tab_partitions where s.segment_type='TABLE PARTITION' and table_owner = s.owner and table_name = s.segment_name and partition_name = s.partition_name) last_analyzed_tab_part
	from dba_segments s
) a;

CREATE PUBLIC SYNONYM XPV_REAL_TIME_LAST_ANALYZED FOR SYSTEM.XPV_REAL_TIME_LAST_ANALYZED;

View XPV_JOIN_REDO

CREATE OR REPLACE FORCE VIEW XPV_JOIN_REDO AS
select xp.*, dml_count, blocks, num_rows
, abs(curr_db_block_changes -block_changes_when_analyzed) blocks_diff
from (
	select owner, segment_name, sum(block_changes_when_analyzed) block_changes_when_analyzed
	, sum(last_db_block_changes) last_db_block_changes
	, sum(curr_db_block_changes) curr_db_block_changes
	, max(stats_date) max_stats_date, max(last_check_date) max_last_check_date
	, sum(curr_blocks) curr_blocks
	from xp_stats
	where owner = 'VPO' and segment_type ='TABLE' or segment_type ='TABLE PARTITION'
	group by owner, segment_name
) xp,  (
	select st.seg_owner, st.seg_name, count(*) dml_count, sum(t.blocks) blocks
	, sum(t.num_rows) num_rows
	from  storico.log_storico_data st, dba_tables t
	where st.seg_owner=t.owner
	and st.SEG_NAME=t.table_name
	and st.seg_owner='VPO'
	group by st.seg_owner, st.seg_name
) enrico
where enrico.seg_owner = xp.owner and enrico.seg_name = xp.segment_name
and block_changes_when_analyzed <> curr_db_block_changes;

package xp_stat

CREATE OR REPLACE package xp_stat authid current_user is	
DEBUG   number := 0;
INFO    number := 1;
WARNING number := 2;
ERROR 	number := 3;
ALWAYS	number := 4;

minimalLogLevel number := INFO;

module_name varchar2(50) := 'XP_STAT';
action_name_during_stats varchar2(50) := 'Taking stats for schema ';
--begin
--xp_stat.PRINTSAMPLEPERCFORBLOCKCHANGES(100, 2, 1.2);
--end;
--A 2 blocks segment will only be analyzed when all its blocks will be changed, while for a
--largest segment a smaller percentual of changed blocks will be needed to take stats
procedure printSamplePercForBlockChanges(percBase number, blocksMultipl number, logBase number);
procedure takeStats(m_owner varchar2, percentual_sample number default 49,
		  m_degree number default null,
		  percForTables number default 100,
		  percForIndexes number default 100,
		  logBaseTables number default 1.2,
		  logBaseIndexes number default 1.175,
		  m_minimalLogLevel number default INFO,
		  m_truncateLogTable boolean default false,
		  m_simulation_only boolean default false,
		  m_stop_on_exception boolean default false,
		  m_log_retention_days number default 3,
		  m_launch_notes varchar2 default null);
function getLogForSegment(m_segment varchar2, m_session varchar2 default null,
		 out_to_console boolean default false, logDate boolean default false) return varchar2;
end xp_stat;

package body xp_stat

CREATE OR REPLACE package body xp_stat is

cr varchar2(4) := chr(13)||chr(10);

function boolToString(b boolean) return varchar2 is
begin
	 if b then
	 	return 'True';
	 else return 'False';
	 end if;
end;

procedure outLongString(newString varchar2) is
		  i integer;
begin
	 i := 1;
	loop
		dbms_output.put_line(substr(newString, i, 255));
		i := i + 255;
		exit when i > length(newString);
	end loop;
end;

procedure msg(text varchar2, logLevel number, m_owner varchar2, m_segment varchar2, m_xp_session_name varchar2) is
    pragma autonomous_transaction;
	t date;
	n_owner varchar2(50);
begin
	 t := sysdate;
	 n_owner := m_owner;
	 if (m_owner is null) then
	 	n_owner := '';
	 end if;
	if logLevel >= minimalLogLevel then 
	   insert into xp_stat_log(msg, data, log_level, prog, segment, xp_session_name, owner) values
	   (text, t, logLevel, LOCALTIMESTAMP, m_segment, m_xp_session_name, n_owner); 		
	end if;

	update xp_last_operations set description = text, datetime = t,
	   segment = m_segment, xp_session_name = m_xp_session_name
	   where name = 'XP_STAT_LAST_LOG'
	   and owner = n_owner;
	  
    if sql%rowcount = 0 then
   	  insert into xp_last_operations (name, owner, description, datetime, segment, xp_session_name)
		values('XP_STAT_LAST_LOG', n_owner, text, t, m_segment, m_xp_session_name);
	end if;
	commit;
end;

procedure clearLogTable(until_date date, m_xp_session_name varchar2, m_owner varchar2) is
pragma autonomous_transaction;
  m_until_date date;
begin
	 if until_date is null then
	 	select max(data) into m_until_date from xp_stat_log;
	 else m_until_date := until_date;
	 end if;
	delete from xp_last_operations where owner = m_owner;
	delete from xp_stat_log where data <= m_until_date and owner = m_owner;
	commit;
	msg('Older logs deleted until '|| to_char(m_until_date, 'dd/mm/yyyy hh24.mi.ss'), warning, m_owner, null, m_xp_session_name);
	commit;
end;

function numc(n number) return varchar2 is
begin
	 return to_char(n, 'FM999,999,999,990, 00');
end;

function c(n in number) return varchar2 is
	   			mask varchar2(30);
				result varchar2(50);
begin
    		--if hasDecimali(n) > 0 then
   --   mask :='FM999,999,999,999.99';
   --else mask := 'FM999,999,999,999';
   --end if;
   mask := 'FM999,999,999,990';
    		result := to_char(n, mask);
   result := replace(result, ',', '*');
   result := replace(result, '.', ',');
   result := replace(result, '*', '.');
   return result;
   --return substr(result, length(result), 1);
end;
 
procedure takeStatsForTable(m_owner varchar2, m_tabName varchar2, m_partName varchar2,
		  percentual_sample number, m_degree number, m_xp_session_name varchar2
		  , m_method_opt VARCHAR2, m_granularity varchar2) is
num_rows NUMBER;
num_blocks NUMBER;
avg_row_len NUMBER;
error_message varchar2(4000);
BEGIN
	 msg('Taking stats for table at ' || percentual_sample || '%, degree ' || m_degree ||
	   ', method_opt ' || m_method_opt || ', granularity ' || m_granularity
	   , warning,m_owner,
	   m_owner||'.'||m_tabName||'.'||m_partName, m_xp_session_name);
	 --Remember Oracle may change default values with newest releases,
	 --so this approach avoid any troubles
	 begin
	 	  --Per gli istogrammi
	 	  if (not m_method_opt is null)and(length(m_method_opt)>0) then
		  	DBMS_STATS.GATHER_TABLE_STATS (
			ownname=>m_owner,
			tabname=>m_tabName,
			partname=>m_partName,
			estimate_percent=>percentual_sample,
			--block_sample BOOLEAN DEFAULT FALSE,
			method_opt=>m_method_opt,
			degree=>m_degree,
			granularity=>m_granularity,
		    --granularity VARCHAR2 DEFAULT 'DEFAULT',
			cascade=>true
			--stattab VARCHAR2 DEFAULT NULL,
			--statid VARCHAR2 DEFAULT NULL,
			--statown VARCHAR2 DEFAULT NULL,
			--no_invalidate BOOLEAN DEFAULT FALSE
			);
		  else
			DBMS_STATS.GATHER_TABLE_STATS (
			ownname=>m_owner,
			tabname=>m_tabName,
			partname=>m_partName,
			estimate_percent=>percentual_sample,
			--block_sample BOOLEAN DEFAULT FALSE,
			--method_opt VARCHAR2 DEFAULT ?FOR ALL COLUMNS SIZE 1?,
			degree=>m_degree,
			granularity=>m_granularity,
			cascade=>true
			--stattab VARCHAR2 DEFAULT NULL,
			--statid VARCHAR2 DEFAULT NULL,
			--statown VARCHAR2 DEFAULT NULL,
			--no_invalidate BOOLEAN DEFAULT FALSE
			);
		  end if;
	 exception
  		   when others then
		   error_message := 'Exception while taking stats for table segment.';
		   if (not m_method_opt is null)and(length(m_method_opt)>0) then
		   	  error_message := error_message || 'And histograms using: ' || m_method_opt;
		   end if;
		   error_message := error_message || sqlerrm(sqlcode);
		   msg(error_message, ERROR,m_owner, m_owner||'.'||m_tabName||'.'||m_partName, m_xp_session_name);
			  raise;
	 end;
end;

procedure takeStatsForIndex(m_owner varchar2, m_indexName varchar2, m_partName varchar2,
  			percentual_sample number, m_degree number, m_xp_session_name varchar2) is
begin
	 msg('Taking stats for index at ' || percentual_sample || '%, degree ' || m_degree 
	   , warning,m_owner,
	   m_owner||'.'||m_indexName||'.'||m_partName, m_xp_session_name);	
  	
	 if(not m_partName is null)or(length(m_partName)>0) then
		DBMS_STATS.GATHER_INDEX_STATS (
		ownname=>m_owner,
		indname=>m_indexName,
		partname=>m_partName,
		estimate_percent=>percentual_sample,
		--stattab VARCHAR2 DEFAULT NULL,
		--statid VARCHAR2 DEFAULT NULL,
		--statown VARCHAR2 DEFAULT NULL,
		degree=>m_degree
		--granularity VARCHAR2 DEFAULT ?DEFAULT?,
		--no_invalidate BOOLEAN DEFAULT FALSE
		);
  	 else
	 	 DBMS_STATS.GATHER_INDEX_STATS (
		 ownname=>m_owner,
		 indname=>m_indexName,
		 estimate_percent=>percentual_sample);
  	 end if;
 
  exception
  		   when others then
		   msg('Exception while taking stats for index: '||sqlerrm(sqlcode), ERROR,m_owner,
		     m_owner||'.'||m_indexName||'.'||m_partName, m_xp_session_name);
			  raise;
  end;

procedure takeStatsForObject(m_owner varchar2, m_object_name varchar2, m_subobject_name varchar2,
    segment_type varchar2, percentual_sample number, m_degree number,
		  m_simulation_only boolean, m_xp_session_name varchar2,
		  m_granularity varchar2) is
 begin
 	 if not m_simulation_only then
  	 if (segment_type = 'TABLE')or(segment_type = 'TABLE PARTITION') then
	 	--msg('Going to table', 'INFO');
	   takeStatsForTable(m_owner, m_object_name, m_subobject_name, percentual_sample, m_degree,
	   					 m_xp_session_name, null, m_granularity);
	 elsif (segment_type = 'INDEX')or(segment_type = 'INDEX PARTITION') then
	   takeStatsForIndex(m_owner, m_object_name, m_subobject_name, percentual_sample,
	     m_degree, m_xp_session_name);
	 elsif (segment_type = 'LOBINDEX') then
	   msg('Segment is LOBINDEX no stats allowed', info, m_owner, m_owner||'.'||m_object_name||'.'||m_subobject_name,
	     m_xp_session_name);
	 elsif (segment_type = 'LOBSEGMENT') then
	   msg('Segment is LOBSEGMENT no stats allowed', info, m_owner, m_owner||'.'||m_object_name||'.'||m_subobject_name,
	     m_xp_session_name);
	 else msg('****Segment type for is '||segment_type||' what is it?****', ERROR, m_owner, m_owner||'.'||m_object_name||'.'||m_subobject_name,
	   m_xp_session_name);
	 end if;
 else
 	 msg('SIMULATION MODE, stats would be caught for segment '||segment_type, warning, m_owner, m_owner||'.'||m_object_name||'.'||m_subobject_name,
	   m_xp_session_name);
 end if;
 end;

procedure checkForDroppedSegments(m_xp_session_name varchar2, m_owner varchar2) is
begin
msg('Deleting dropped segments from cust table...', INFO, m_owner, null, m_xp_session_name);
	  delete from xp_stats where owner||'.'||segment_name||'.'||partition_name in (
		select owner||'.'||segment_name||'.'||partition_name from xp_stats where owner = upper(m_owner)
		minus
		select owner||'.'||segment_name||'.'||partition_name from dba_segments where owner = upper(m_owner));
	  msg('...done', INFO, m_owner, null, m_xp_session_name);
exception
		 when others then
		 	  msg('Exception while checking for dropped segments: '||sqlerrm(sqlcode), ERROR, m_owner, null,
			    m_xp_session_name);
			  raise;
end;

function getAlreadyRunningStatus(m_schema varchar2) return varchar2 is
		 c varchar2(50);
begin
	 begin
		 select status||', SPID:'||p.spid into c from v$session s,v$process p
		 where module = module_name
		 and p.addr = s.paddr
		 		and action = action_name_during_stats||upper(m_schema)
				and rownum = 1;
	 exception
	     when no_data_found then
		 	  return null;
	 end;
	 return c;
end;

function calcPercForBlockChanges(percBase number, blocks number, logBase number
, m_segment varchar2 default null, m_xp_session_name varchar2 default null,
m_owner varchar2 default '') return number is
 printLog boolean := false;
 result number;
begin
	 printLog := (not m_segment is null)and (not m_xp_session_name is null);
	 if printLog then
	 	msg('Calculating percentual of block changes for segment ' || nvl(m_segment, 'null') || ' having '||
		    blocks || ' blocks. Base percentual is ' || percBase ||
			' and base logarithm is ' || logBase, INFO,m_owner, m_segment, m_xp_session_name);
	 end if;
	 result := percBase - log(logBase, blocks);
	 if printLog then
	 	msg('Calculated percentual is '||percBase ||' - log('||logBase||', '||blocks||') = '||result,
		INFO, m_owner, m_segment, m_xp_session_name);
	 end if;
	 return result;
end;

procedure printSamplePercForBlockChanges(percBase number, blocksMultipl number, logBase number) is
		  i number := 1;
begin
	 loop
	 	 dbms_output.put_line('%'|| to_char(calcPercForBlockChanges(percBase, i, logBase, ''), '99.99') ||' Blck: '|| c(i));
	 	 exit when i > 100000000;
		 i := i * blocksMultipl;
	 end loop;
end;

procedure calculateHistograms(m_owner varchar2, percentual_sample number
		  , m_degree number
		  , m_simulation_only boolean
		  , m_stop_on_exception boolean
		  , m_xp_session_name varchar2) is
cursor c_xpv_column_histograms(m_owner varchar2) is
  		 select *
		 from xpv_analyzable_histograms where owner = upper(m_owner);
  mc_xpv_column_histograms c_xpv_column_histograms%rowtype;
  last_analyzed boolean;
  granularity varchar2(50);
begin
  --*************************
  --* H I S T O G R A M M I *
  --*************************
  msg('Checking user data input for histograms '||m_owner||' schema...', INFO, m_owner, null,
    m_xp_session_name);
  open c_xpv_column_histograms(m_owner);
  fetch c_xpv_column_histograms into mc_xpv_column_histograms;
  loop
  	  begin
	  	  exit when c_xpv_column_histograms%notfound;
		 
		  if mc_xpv_column_histograms.mispelled_partition > 0 then
		  	 msg('The partition specified for histograms calculation does not exists', ERROR, m_owner,
			   mc_xpv_column_histograms.name, m_xp_session_name);
		  elsif mc_xpv_column_histograms.segment_exists <= 0 then
		     msg('The segment specified for histograms calculation does not exists', ERROR,m_owner,
			   mc_xpv_column_histograms.name, m_xp_session_name);
		  elsif mc_xpv_column_histograms.analyzed_in_last_session > 0 then
			begin
				msg('Calculating histograms using: '|| mc_xpv_column_histograms.method_opt, Warning, m_owner,
								 mc_xpv_column_histograms.name, m_xp_session_name);
				if m_simulation_only then
					msg('SIMULATION MODE, histograms stats should be taken now using '||
					  mc_xpv_column_histograms.method_opt, warning, m_owner,
									mc_xpv_column_histograms.name, m_xp_session_name);
				else
					--Se ho una partizione allora la granularita e PARTITION,
					--e le statistiche globali non sono aggiornate
					--altrimenti va in granularita DEFAULT che di fatto ricalcola
					--le statistiche per tutta la tabella da capo
					if (not mc_xpv_column_histograms.partition_name is null) and
					   (length(mc_xpv_column_histograms.partition_name) > 0) then
					   granularity := 'PARTITION';
					else
					   granularity := 'DEFAULT';
					end if;
					takeStatsForTable(m_owner=>mc_xpv_column_histograms.owner,
						m_tabName=>mc_xpv_column_histograms.segment_name,
						m_partName=>mc_xpv_column_histograms.partition_name,
						percentual_sample=>percentual_sample,
						m_degree=>m_degree,
						m_xp_session_name=>m_xp_session_name,
						m_method_opt=>mc_xpv_column_histograms.method_opt,
						m_granularity=>granularity);
				end if;
				update xp_stats set h_last_date = sysdate, h_last_session = m_xp_session_name
					, h_analyzed_in_last_session = 1 where owner = mc_xpv_column_histograms.owner
					and segment_name = mc_xpv_column_histograms.segment_name and
					nvl(partition_name, ' ') = nvl(mc_xpv_column_histograms.partition_name, ' ');
				if SQL%rowcount >0 then
				   msg('xp_stats table updated', INFO, m_owner, mc_xpv_column_histograms.name, m_xp_session_name);
				else
					msg('No matching on were clause while updating segment on xp_stats table', ERROR,m_owner,
					mc_xpv_column_histograms.name, m_xp_session_name);
				end if;
				fetch c_xpv_column_histograms into mc_xpv_column_histograms;
			exception
				when others then
					 msg('Exception while calculating histograms for segment.' ||sqlerrm(sqlcode), ERROR, m_owner,
					 mc_xpv_column_histograms.name, m_xp_session_name);
				if m_stop_on_exception then
				   raise;
				end if;
			end;
		  end if;
		  fetch c_xpv_column_histograms into mc_xpv_column_histograms;
	  exception
		  		   when others then
				   		msg('Exception while calculating histograms for segment.' ||sqlerrm(sqlcode), ERROR,m_owner,
						  mc_xpv_column_histograms.name, m_xp_session_name);
				   if m_stop_on_exception then
				   	  raise;
				   end if;
	  end;
  end loop;
  close c_xpv_column_histograms;
  msg('...done', INFO, m_owner, null, m_xp_session_name);
 
--   msg('Taking histograms for '||m_owner||' schema...', INFO, null,
--     m_xp_session_name);
--  open mc_xpv_column_histograms(m_owner);
--   fetch c_h into mc_h;
--   loop
--   	  begin
-- 	  	  exit when c_h%notfound;
-- 		  msg('Calculating histograms using: '|| mc_xpv_column_histograms.method_opt, INFO,
-- 				   mc_xpv_column_histograms.name, m_xp_session_name);
-- 		 if m_simulation_only then
-- 		 	msg('SIMULATION MODE, histograms stats should be taken now using '|| mc_xpv_column_histograms.method_opt, INFO,
-- 		   	  mc_h.name, m_xp_session_name);
-- 		 else
-- 	  	   takeStatsForTable(m_owner=>mc_h.owner,
-- 		   m_tabName=>mc_h.segment_name,
-- 		   m_partName=>mc_h.partition_name,
-- 	  	   percentual_sample=>percentual_sample, m_degree=>m_degree,
-- 		   m_xp_session_name=>m_xp_session_name,
-- 	  	   m_method_opt=>mc_h.method_opt);
-- 		 end if;
-- 		  fetch c_h into mc_h;
-- 	  exception
--   		   when others then
-- 		   		msg('Exception while calculating histograms for segment.' ||sqlerrm(sqlcode), ERROR,
-- 				  mc_h.name, m_xp_session_name);
-- 		   if m_stop_on_exception then
-- 		   	  raise;
-- 		   end if;
-- 	  end;
--   end loop;
--   close c_h;
--   msg('Histograms taken', INFO, null, m_xp_session_name);
end;

procedure takeStats(m_owner varchar2, percentual_sample number default 49,
		  m_degree number default null,
		  percForTables number default 100,
		  percForIndexes number default 100,
		  logBaseTables number default 1.2,
		  logBaseIndexes number default 1.175,
		  m_minimalLogLevel number default INFO,
		  m_truncateLogTable boolean default false,
		  m_simulation_only boolean default false,
		  m_stop_on_exception boolean default false,
		  m_log_retention_days number default 3,
		  m_launch_notes varchar2 default null) is
  i varchar2(200);
  cursor c_new_segments(m_owner varchar2) is
    select * from (
	    select owner, segment_name, partition_name, segment_type
			   from dba_segments
			   where owner = upper(m_owner)
		minus
	    select owner, segment_name, partition_name, segment_type
			   from xp_stats
			   where owner = upper(m_owner)
	)
	--where not segment_type in ('INDEX', 'INDEX PARTITION')
	order by owner, segment_name;
  mc_new_segments c_new_segments%rowtype;

  cursor c_xp_stats is
    select xp_stats.*, xp_stats.rowid from xp_stats where owner = (upper(m_owner));
  mc_xp_stats c_xp_stats%rowtype;
  mc_xp_stats_rowid UROWID;

  actualDbBlockChanges number;
  custDbBlockChanges number;
  m_existsInCust boolean;
  --m_dba_segments dba_segments%rowtype;
  diffBlocksChanged number;
  requiredBlocksForStats number;
 
  TYPE r_segment_statistics  is record ( value number, just_inserted number);
  TYPE t_segment_statistics IS TABLE OF r_segment_statistics INDEX BY VARCHAR2(150);
  mt_segment_statistics t_segment_statistics;
  cursor c_segment_statistics(m_owner varchar2) is
  		 select owner||'.'||object_name||'.'||subobject_name as name, value
		 from v$segment_statistics where statistic#=2 and
		 owner = upper(m_owner);
  mc_segment_statistics c_segment_statistics%rowtype;
 
  TYPE r_dba_segments  is record (owner varchar2(20), segment_name varchar2(50),
    partition_name varchar2(50), segment_type varchar2(30),
    name varchar2(120), bytes number, blocks number,
    will_take_stats boolean, block_changes_when_analyzed number,
	curr_db_block_changes number, stats_date date, last_check_date date,
	analyzed_in_last_session integer, just_inserted integer,
	last_db_block_changes number, rowidForXp_stats rowid);
  mr_dba_segments r_dba_segments;
  cursor c_dba_segments(m_owner varchar2) is
  		 select owner, segment_name, partition_name, segment_type,
		 			   owner||'.'||segment_name||'.'||partition_name name, bytes, blocks
		 		from dba_segments
				where owner = upper(m_owner)
					  --and not segment_type in ('INDEX', 'INDEX PARTITION')
					  order by owner, segment_name;
  mc_dba_segments c_dba_segments%rowtype;
  TYPE t_dba_segments IS TABLE OF r_dba_segments INDEX BY VARCHAR2(150);
  mt_dba_segments t_dba_segments;
 
  TYPE r_part_tables_analyzed is record(owner varchar2(20), table_name varchar2(50));
  mr_part_tables_analyzed r_part_tables_analyzed;
  TYPE t_part_tables_analyzed IS TABLE OF r_part_tables_analyzed INDEX BY VARCHAR2(150);
  mt_part_tables_analyzed t_part_tables_analyzed;
 
  cursor c_XPV_REAL_TIME_LAST_ANALYZED(m_owner varchar2) is
  		 select name, last_analyzed
		 from XPV_REAL_TIME_LAST_ANALYZED where owner = upper(m_owner);
  mc_XPV_REAL_TIME_LAST_ANALYZED c_XPV_REAL_TIME_LAST_ANALYZED%rowtype;
 
  instance_startup date;
  will_take_stats number;
  msg_program_already_running varchar2(500);
  m_xp_session_name varchar2(32);
  session_status varchar2(50);
  actual_db_block_changes number;
  m_just_inserted number;
  tNow date;
  percBlocksChanged number;
  beginMsg varchar2(500);
  realTimeLastAnalyzed date;
  msgRealTimeLastAnalyzed varchar2(500);
  cascade_stats boolean;
  max_blocks_to_force_cascade integer := 64;
  m_granularity varchar2(50);
 
  --LONGOPS
  totBlocksToAnalyze number := 0;
  totObjectsToAnalyze number := 0;
  rindexObjectsToAnalyze BINARY_INTEGER;
  rindexBlocksToAnalyze BINARY_INTEGER;
  slnoBlocksToAnalyze BINARY_INTEGER;
  slnoObjectsToAnalyze BINARY_INTEGER;
  sofarBlocksToAnalyze number;
  sofarObjectsToAnalyze number;
  objbLOCKSToAnalyze BINARY_INTEGER;
  objObjectsToAnalyze BINARY_INTEGER;
begin
  m_xp_session_name := sys_guid();
  minimalLogLevel := m_minimalLogLevel;
 
  session_status := getAlreadyRunningStatus(m_owner);
  if (not session_status is null) and (length(session_status) >0) then
  	 msg_program_already_running := 'A session with same module('||module_name||') and same action is already opened, exiting now.' ||
	   ' Anyway this program may be run concurrently with different schema. The session is marked as '|| session_status;
  	 msg(msg_program_already_running, WARNING, m_owner, null, m_xp_session_name);
	 raise_application_error(-20001, msg_program_already_running);
  end if;
 
  DBMS_APPLICATION_INFO.SET_MODULE(
       module_name => module_name,
       action_name => action_name_during_stats||upper(m_owner));
 
  if (m_truncateLogTable) then
  	 clearLogTable(null, m_xp_session_name, m_owner);
  else clearLogTable(sysdate - m_log_retention_days, m_xp_session_name, m_owner);
  end if;
 
  select startup_time into instance_startup from v$instance;

  beginMsg := 'Begin procedure.';
  if (not m_launch_Notes is null) and (not length(m_launch_Notes)<=0) then
  	 beginMsg := beginMsg || ' ' || m_launch_Notes;
  end if;
  msg(beginMsg, ALWAYS, m_owner, null, m_xp_session_name);
  checkForDroppedSegments(m_xp_session_name, m_owner);
 
  msg('Program session is ' ||m_xp_session_name, INFO, m_owner, null, m_xp_session_name);
 
  msg('Creating a memory structure for all current block changes...', INFO, m_owner, null, m_xp_session_name);
  open c_segment_statistics(m_owner);
  fetch c_segment_statistics into mc_segment_statistics;
  loop
  	  exit when c_segment_statistics%notfound;
	  mt_segment_statistics(mc_segment_statistics.name).value := mc_segment_statistics.value;
	  msg('Storing current block changes (' ||mc_segment_statistics.value|| ') for segment',
	    DEBUG, m_owner, mc_segment_statistics.name, m_xp_session_name);
	  fetch c_segment_statistics into mc_segment_statistics;
  end loop;
  close c_segment_statistics;
  msg('...done', INFO, m_owner, null, m_xp_session_name);
 
  msg('Creating a memory structure for all segments of '||m_owner||' schema...', INFO, m_owner, null,
    m_xp_session_name);
  open c_dba_segments(m_owner);
  fetch c_dba_segments into mc_dba_segments;
  loop
  	  exit when c_dba_segments%notfound;
	  mr_dba_segments.owner := mc_dba_segments.owner;
	  mr_dba_segments.segment_name := mc_dba_segments.segment_name;
	  mr_dba_segments.partition_name := mc_dba_segments.partition_name;
	  mr_dba_segments.segment_type := mc_dba_segments.segment_type;
	  mr_dba_segments.name := mc_dba_segments.name;
	  mr_dba_segments.bytes := mc_dba_segments.bytes;
	  mr_dba_segments.blocks := mc_dba_segments.blocks;
	  mr_dba_segments.will_take_stats := false;
	  mt_dba_segments(mc_dba_segments.name) := mr_dba_segments;
	  msg('Storing segment data for segment', DEBUG, m_owner, mc_dba_segments.name, m_xp_session_name);
	  fetch c_dba_segments into mc_dba_segments;
  end loop;
  close c_dba_segments;
  msg('...done', INFO, m_owner, null, m_xp_session_name);

  msg('Inserting new segments in cust table', INFO, m_owner, null, m_xp_session_name);
  open c_new_segments(m_owner);
  fetch c_new_segments into mc_new_segments;
  loop
  	  begin
	  	  exit when c_new_segments%notfound;
	   	  begin
		  	   actualDbBlockChanges := mt_segment_statistics(mc_new_segments.owner||'.'||mc_new_segments.segment_name||'.'
	   	  	   ||mc_new_segments.partition_name).value;
	      exception
	     			when no_data_found then
	   				actualDbBlockChanges := 0;
	      end;
	      if actualDbBlockChanges is null then
	    	 actualDbBlockChanges := 0;
	      end if;
	   
		  msg('Inserting new segment ', INFO, m_owner, mc_new_segments.owner||'.'||mc_new_segments.segment_name||'.'||
		    mc_new_segments.partition_name, m_xp_session_name);
	      insert into xp_stats (owner, segment_name, partition_name, curr_db_block_changes, block_changes_when_analyzed,
	        segment_type, just_inserted, curr_blocks, xp_session_name)
	        values (mc_new_segments.owner, mc_new_segments.segment_name, mc_new_segments.partition_name, actualDbBlockChanges, actualDbBlockChanges,
	   	    mc_new_segments.segment_type, 1,
			mt_dba_segments(mc_new_segments.owner||'.'||mc_new_segments.segment_name||'.'
	   	    ||mc_new_segments.partition_name).blocks,
	   	    m_xp_session_name);
		  msg('Segment inserted ', INFO, m_owner, mc_new_segments.owner||'.'||mc_new_segments.segment_name||'.'||
		    mc_new_segments.partition_name, m_xp_session_name);
		    mt_segment_statistics(mc_new_segments.owner||'.'||mc_new_segments.segment_name||'.'
	   	  	   ||mc_new_segments.partition_name).just_inserted := 1;
		 
      exception
	      when others then
		      msg('Exception while inserting new segment : ' ||sqlerrm(sqlcode), ERROR,m_owner,
			      mc_new_segments.owner||'.'||mc_new_segments.segment_name||'.'
	   	    ||mc_new_segments.partition_name, m_xp_session_name);
		  if m_stop_on_exception then raise;
		  end if;
	  end;
	  fetch c_new_segments into mc_new_segments;
  end loop;
  close c_new_segments;
  msg('Done', INFO, m_owner, null, m_xp_session_name);

  open c_xp_stats;
  fetch c_xp_stats into mc_xp_stats;
  loop
  	  exit when c_xp_stats%notfound;
	  begin
		  mr_dba_segments := mt_dba_segments(mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||
		  				  mc_xp_stats.partition_name);
		  msg('Segment informations retrieved from memory structure', DEBUG, m_owner,
		    mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||
		  				  mc_xp_stats.partition_name, m_xp_session_name);
		  begin
		  	   actual_db_block_changes := nvl(mt_segment_statistics(mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||
		  				  mc_xp_stats.partition_name).value, 0);
		  exception	
		  			when no_data_found then
						 actual_db_block_changes := 0;
		  end;
		  msg('Real time block changes retrieved from memory structure', DEBUG, m_owner, mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||
		  				  mc_xp_stats.partition_name, m_xp_session_name);		 
		  diffBlocksChanged := ABS(actual_db_block_changes - nvl(mc_xp_stats.block_changes_when_analyzed, 0));
		 
		  if (mc_xp_stats.segment_type = 'TABLE')or((mc_xp_stats.segment_type = 'TABLE PARTITION')) then
			 percBlocksChanged := calcPercForBlockChanges(percForTables, mr_dba_segments.blocks, logBaseTables, mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||
		  				  mc_xp_stats.partition_name, m_xp_session_name, m_owner);
		  elsif (mc_xp_stats.segment_type = 'INDEX')or(mc_xp_stats.segment_type = 'INDEX PARTITION')then
		  	 percBlocksChanged := calcPercForBlockChanges(percForIndexes, mr_dba_segments.blocks, logBaseIndexes, mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||
		  				  mc_xp_stats.partition_name, m_xp_session_name, m_owner);
		  else
		  	  msg('Segment type is ' || mc_xp_stats.segment_type ||
			    ', unable to determine the percentual gap', ERROR, m_owner, 
			    mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||mc_xp_stats.partition_name, m_xp_session_name);
		  end if;
		  msg('Segment type is ' || mc_xp_stats.segment_type || ', percentual is '||
		    percBlocksChanged, INFO,m_owner,
		  	mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||mc_xp_stats.partition_name, m_xp_session_name);
		 
		  requiredBlocksForStats := percBlocksChanged * mr_dba_segments.blocks / 100;
		  msg('Current Block Changed:' || actual_db_block_changes||cr
		    ||', Block Changed since last analyzed:'||nvl(mc_xp_stats.block_changes_when_analyzed, 0)||cr||
			', abs(Current Block Changed - Block Changed since last analyzed ):'|| diffBlocksChanged||cr||
			', Perc of Blocks to be Changed:'||percBlocksChanged ||cr||
			', Total Blocks now for segment:'||mr_dba_segments.blocks||cr||
			', Required Blocks to take Stats:'||requiredBlocksForStats,INFO,m_owner, 
			mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||mc_xp_stats.partition_name, m_xp_session_name);
		 
		  begin
		  	   m_just_inserted := nvl(mt_segment_statistics(mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'
   	  	                        ||mc_xp_stats.partition_name).just_inserted, 0);
		  exception
		  		   when no_data_found then
				   		m_just_inserted := 0;
		  end;
		  msg('Just_insert for segment is '|| m_just_inserted, debug, m_owner,
		    mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'
   	  	                        ||mc_xp_stats.partition_name, m_xp_session_name);
		  tNow := sysdate;
		 
		  if (m_just_inserted>0) then
			  --*SE E' UN NUOVO SEGMENTO, GLI PRENDO LA DATA DI STATISTICA DA CATALOGO
			  begin
			  	   select last_analyzed into realTimeLastAnalyzed from XPV_REAL_TIME_LAST_ANALYZED
				   where owner = mc_xp_stats.owner and segment_name = mc_xp_stats.segment_name and
				   nvl(partition_name, ' ') = nvl(mc_xp_stats.partition_name, ' ');
			  exception
			  		   when others then
					   		msg('Exception while retrieving last_analyzed from XPV_REAL_TIME_LAST_ANALYZED',
							ERROR, m_owner, mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'
	   	  	                        ||mc_xp_stats.partition_name, m_xp_session_name);
					   realTimeLastAnalyzed := null;
			  end;
			  msgRealTimeLastAnalyzed := 'Based on system catalog, the segment was last analyzed ';
			  if realTimeLastAnalyzed is null then
			  	 msgRealTimeLastAnalyzed := msgRealTimeLastAnalyzed || ' never before';
			  else
			  	 msgRealTimeLastAnalyzed := msgRealTimeLastAnalyzed || to_char(realTimeLastAnalyzed, 'dd/mm/yyyy hh24.mi.ss');
			  end if;
			  msg(msgRealTimeLastAnalyzed, debug, m_owner, mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'
	   	  	                        ||mc_xp_stats.partition_name, m_xp_session_name);
		  else
		  	   realTimeLastAnalyzed := null;
		  end if;
		 
		  will_take_stats := 0;
		  if instance_startup > mc_xp_stats.last_check_date then
		  	 msg('Instance was started ('||to_char(instance_startup, 'dd/mm/yyyy hh24.mi.ss')||') after last segment check('||
			     mc_xp_stats.last_check_date ||'), skipping statistis in any case', INFO, m_owner,
				 mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||mc_xp_stats.partition_name, m_xp_session_name);
			 will_take_stats := -1;
		  end if;
-- 		  if (will_take_stats = 0)and(m_just_inserted > 0) then
-- 		  	 msg('The segment has just been inserted, stats will never be taken', INFO,m_owner,
-- 				 mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||mc_xp_stats.partition_name, m_xp_session_name);
-- 			 will_take_stats := -1;		
-- 		  end if;
		  if (will_take_stats = 0)and(mc_xp_stats.block_changes_when_analyzed is null) then
		  	 msg('There is not a last value of block changes for comparison, stats may be taken', INFO,m_owner,
			   mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||mc_xp_stats.partition_name, m_xp_session_name);
			 will_take_stats := 1;
		  end if;
		  if (will_take_stats = 0)and(diffBlocksChanged >= requiredBlocksForStats) then
		  	 msg('The difference of blocks changed('|| actual_db_block_changes ||' - ' || nvl(mc_xp_stats.block_changes_when_analyzed, 0) ||
			     ' = '|| diffBlocksChanged ||'), as an absolute value, is greater than the required value calculated, stats may be taken', INFO,m_owner,
				 mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||mc_xp_stats.partition_name, m_xp_session_name);
			 will_take_stats := 1;		 
		  end if;
		  if (will_take_stats = 0)and(m_just_inserted >0)and(realTimeLastAnalyzed is null)then
		     msg('The segment is new and has never been analyzed, by querying system catalog', INFO,m_owner,
				 mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||mc_xp_stats.partition_name, m_xp_session_name);
			 will_take_stats := 1;	
		  end if;
		  if will_take_stats = 0 then
		  	 msg('No conditions have been applied, stats will not be taken by default', INFO,m_owner,
			 mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||mc_xp_stats.partition_name, m_xp_session_name);
			 will_take_stats := -1;
		  end if;
		  if will_take_stats = 1 then
		  	 msg('Stats will be taken', INFO, m_owner, mr_dba_segments.name, m_xp_session_name);
		  end if;
		 
		  msg('Updating memory record of dba_segments', DEBUG, m_owner, mr_dba_segments.name, m_xp_session_name);
		  mr_dba_segments.rowidForXp_stats := mc_xp_stats.rowid;
		  mr_dba_segments.block_changes_when_analyzed := actual_db_block_changes;
		  mr_dba_segments.curr_db_block_changes := actual_db_block_changes;
		  mr_dba_segments.stats_date := tNow;
		  mr_dba_segments.last_check_date := tNow;
		  mr_dba_segments.just_inserted := m_just_inserted;
		  msg('Just_inserted has been stored in memory as ' || mr_dba_segments.just_inserted, debug, m_owner, mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||mc_xp_stats.partition_name, m_xp_session_name);
		  mr_dba_segments.last_db_block_changes := mc_xp_stats.curr_db_block_changes;
		  msg('Memory record of dba_segments updated', DEBUG, m_owner, mr_dba_segments.name, m_xp_session_name);
		  if will_take_stats > 0 then
		  	 mr_dba_segments.will_take_stats := true;
			 mr_dba_segments.analyzed_in_last_session := 1;
		  	 mt_dba_segments(mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||
		  				  mc_xp_stats.partition_name) := mr_dba_segments;
		  else
		  	 mr_dba_segments.will_take_stats := false;
			 mr_dba_segments.analyzed_in_last_session := 0;
		  	 mt_dba_segments(mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||
		  				  mc_xp_stats.partition_name) := mr_dba_segments;
		  end if;
	  exception
		  		   when others then
				   		msg('Exception while processing segment : ' ||sqlerrm(sqlcode), ERROR,m_owner,
						  mc_xp_stats.owner||'.'||mc_xp_stats.segment_name||'.'||mc_xp_stats.partition_name,
						  m_xp_session_name);
				   if m_stop_on_exception then
				   	  raise;
				   end if;
	  end;
	  fetch c_xp_stats into mc_xp_stats;
  end loop;
  close c_xp_stats;
 
  msg('Calculating the number of total blocks to be analyzed', DEBUG, m_owner, null, m_xp_session_name);
  i := mt_dba_segments.FIRST; -- get subscript of first element
  WHILE i IS NOT NULL LOOP
  		mr_dba_segments := mt_dba_segments(i);
		if mr_dba_segments.will_take_stats then
		   totBlocksToAnalyze := totBlocksToAnalyze + mr_dba_segments.blocks;
		   totObjectsToAnalyze := totObjectsToAnalyze + 1;
		end if;
  		i := mt_dba_segments.NEXT(i); -- get subscript of next element
  END LOOP;
  msg(totBlocksToAnalyze || ' blocks will be analyzed in this session', INFO, m_owner, null, m_xp_session_name);
 
  --**************************
  --* AGGIORNAMENTO XP_STATS *
  --**************************
  rindexObjectsToAnalyze := dbms_application_info.set_session_longops_nohint;
  sofarObjectsToAnalyze := 0;												
  rindexBlocksToAnalyze := dbms_application_info.set_session_longops_nohint;
  sofarBlocksToAnalyze := 0;												
 
  msg('Cicling memory table for stats', DEBUG, m_owner, null, m_xp_session_name);
  i := mt_dba_segments.FIRST; -- get subscript of first element
  WHILE i IS NOT NULL LOOP
  		msg('Retrieving memory record '|| i, debug, m_owner, i, m_xp_session_name);
  		mr_dba_segments := mt_dba_segments(i);
		msg('Memory record retrieved '|| i, debug, m_owner, mr_dba_segments.name, m_xp_session_name);
		msg('Just_inserted for memory record is '||mr_dba_segments.just_inserted, debug, m_owner, mr_dba_segments.name, m_xp_session_name);
		if mr_dba_segments.will_take_stats then
		  sofarObjectsToAnalyze := sofarObjectsToAnalyze + 1;
		  sofarBlocksToAnalyze := sofarBlocksToAnalyze + mr_dba_segments.blocks;
		  dbms_application_info.set_session_longops(rindex=>rindexBlocksToAnalyze,
		       slno=>slnoBlocksToAnalyze, op_name=>'XP-Stat, blocks view',
			   target=>objBLocksToAnalyze, context=>0,
			   sofar=>sofarBlocksToAnalyze, totalwork=>totBlocksToAnalyze,
			   target_desc=>'Schema ' || m_owner ||', '|| totObjectsToAnalyze ||' segm. to analyze',
			   units=>'Blocks');
		  if mr_dba_segments.segment_type in ('TABLE', 'TABLE PARTIION') then
			  cascade_stats := mr_dba_segments.blocks <= max_blocks_to_force_cascade;
			  msg('Segment is a table or a table partition, force cascade is '||boolToString(cascade_stats)||', because block limit to force cascade is '||max_blocks_to_force_cascade||
			    ' and total blocks for segment is '||mr_dba_segments.blocks, INFO, m_owner, mr_dba_segments.name, m_xp_session_name);
		  else
		  	  cascade_stats := false;
		  end if;
		 
		  --se la tabella e partizionata e questa e la prima partizione per la tabella,
		  --allora prendo le statistiche senza specificare la partizione con granularity global
		  if(not mr_dba_segments.partition_name is null)and(length(mr_dba_segments.partition_name)>0) then
		  		 --verifico che questo non sia gia stato fatto
				 begin
				 	  mr_part_tables_analyzed := mt_part_tables_analyzed(mr_dba_segments.owner||'.'||mr_dba_segments.segment_name);
				 exception
		  		   when no_data_found then
				   		msg('Taking global stats for table ', warning, m_owner, mr_dba_segments.name, m_xp_session_name);
				   		takeStatsForObject(mr_dba_segments.owner, mr_dba_segments.segment_name,
			   			    null,
			   			    mr_dba_segments.segment_type, percentual_sample, m_degree,
			   			    m_simulation_only, m_xp_session_name, 'GLOBAL');
						mt_part_tables_analyzed(mr_dba_segments.owner||'.'||mr_dba_segments.segment_name).owner := mr_dba_segments.owner;
						mt_part_tables_analyzed(mr_dba_segments.owner||'.'||mr_dba_segments.segment_name).table_name := mr_dba_segments.segment_name;
				 end;
		  end if;
		 
		  if(not mr_dba_segments.partition_name is null)and(length(mr_dba_segments.partition_name)>0) then
		  	 m_granularity := 'PARTITION';
	      else
		  	 m_granularity := 'DEFAULT';
		  end if;
		 
		  msg('Taking stats for segment, granularity is ' || m_granularity, warning, m_owner, mr_dba_segments.name, m_xp_session_name);
		  takeStatsForObject(mr_dba_segments.owner, mr_dba_segments.segment_name,
			   	 mr_dba_segments.partition_name,
			     mr_dba_segments.segment_type, percentual_sample, m_degree,
			     m_simulation_only, m_xp_session_name, m_granularity);
				
			 update xp_stats set
			   block_changes_when_analyzed = mr_dba_segments.block_changes_when_analyzed,
			   curr_db_block_changes = mr_dba_segments.curr_db_block_changes,
			   stats_date = sysdate,
			   last_check_date = mr_dba_segments.last_check_date,
			   analyzed_in_last_session = mr_dba_segments.analyzed_in_last_session,
			   just_inserted = mr_dba_segments.just_inserted,
			   curr_blocks = mr_dba_segments.blocks,
			   xp_session_name = m_xp_session_name,
			   last_db_block_changes = mr_dba_segments.last_db_block_changes,
			   h_analyzed_in_last_session = 0
			   where rowid = mr_dba_segments.rowidForXp_stats;
		else
			msg('Stats will not be taken', INFO, m_owner, mr_dba_segments.name, m_xp_session_name);
		  	  update xp_stats set
			    --block_changes_when_analyzed = actual_db_block_changes,
			    curr_db_block_changes = mr_dba_segments.curr_db_block_changes,
				--stats_date = tNow,
				last_check_date = mr_dba_segments.last_check_date,
				analyzed_in_last_session = mr_dba_segments.analyzed_in_last_session,
			    just_inserted = mr_dba_segments.just_inserted,
				curr_blocks = mr_dba_segments.blocks,
			    xp_session_name = m_xp_session_name,
			    last_db_block_changes = mr_dba_segments.last_db_block_changes,
			    h_analyzed_in_last_session = 0
			    where rowid = mr_dba_segments.rowidForXp_stats;
		end if;
		if SQL%rowcount >0 then
			 msg('xp_stats table updated', INFO, m_owner, mr_dba_segments.name, m_xp_session_name);
		else
			msg('No matching on where clause while updating segment on xp_stats table', ERROR,m_owner,
			  mr_dba_segments.name, m_xp_session_name);
		end if;
		--***************
	    --* C O M M I T *
	    --***************
	    commit;
		  i := mt_dba_segments.NEXT(i); -- get subscript of next element
  END LOOP;
  msg('Cicling memory table for stats terminated', DEBUG, m_owner, null, m_xp_session_name);
 
  calculateHistograms(m_owner, percentual_sample, m_degree, m_simulation_only,
    m_stop_on_exception, m_xp_session_name);
 
  msg('Procedure completed, '||totBlocksToAnalyze||' blocks analyzed.',
    ALWAYS, m_owner, null, m_xp_session_name);
    DBMS_APPLICATION_INFO.SET_ACTION(action_name =>'...done');
exception
		 when others then
		 	  msg(sqlerrm(sqlcode), ERROR, m_owner, null, m_xp_session_name);
		 DBMS_APPLICATION_INFO.SET_ACTION(action_name =>'...done');
			  raise;
end;

function getLogForSegment(m_segment varchar2, m_session varchar2 default null,
		 out_to_console boolean default false, logDate boolean default false) return varchar2 is
		 cursor c(m_segment varchar2, m_session varchar2) is
		 		select * from xp_stat_log where segment like upper(m_segment)
				and xp_session_name = decode(m_session, null, xp_session_name, upper(m_session))
				order by prog;
		 mc c%rowtype;
		 result varchar2(32767);
		 newString varchar2(500);
		 i integer;
begin
	 open c(m_segment, m_session);
	 fetch c into mc;
  	 loop
  	  exit when c%notfound;
	  if (not out_to_console)and(length(result) > 0) then
	  	 result := result || cr;
	  end if;
	  if (logDate) then
	  	  newString := to_char(mc.data, 'yyyy/mm/dd hh24.mi.ss') || ':';
	  else
	  	  newString := '';	 
	  end if;
	  newString := newString || mc.log_level|| ': ' || mc.msg;
	  if (not out_to_console) then
	  	 result := result || newString;
	  else
	  	   outLongString(newString);
	  end if;
	  fetch c into mc;
	 end loop;
	 close c;
	
	 if (not out_to_console)then
	 	return result;
	 else
	 	return 'Output to console';
	 end if;
end;

end xp_stat;
/

Execution

IBM AIX

file xp_take_stats.sh

#!/bin/ksh
#
# script di lancio xp_stat - calcolo statistiche
#
export PATH=/bin:/usr/bin:/etc
export ORACLE_BASE=/home/oracle
unset  ORACLE_SID
unset  ORACLE_HOME
ORATAB=/etc/oratab
USAGE=' '
p_sid=$1
p_schema=$2
p_launch_notes=$3
if [[ "$p_sid" = "" ]]; then
    echo "Wrong parameters: $*"
    echo "Usage: $USAGE"
    exit 99
fi
if [[ "$p_schema" = "" ]]; then
    echo "Wrong parameters: $*"
    echo "Usage: $USAGE"
    exit 99
fi
cat $ORATAB | while read LINE
do
    case $LINE in
        \#*)            ;;      # comment-line in oratab
        *)
# check if the sid parameter is in
        if [[ "`echo $LINE | awk -F: '{print $1}' -`" = "$p_sid" ]] ; then
            ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
            export ORACLE_HOME
            # if I have found a valid ORACLE_SID then I export it
            export ORACLE_SID=$p_sid
        fi
   esac
done
if [[ "$ORACLE_HOME" = "" ]] ; then
    echo "Sid not in $ORATAB !"
    echo "Usage: $USAGE"
    exit 99
fi

$ORACLE_HOME/bin/sqlplus /nolog << EOF
set serveroutput on
connect / as sysdba;
begin
system.xp_stat.takeStats(m_owner =>'$p_schema',
                  percentual_sample =>49,
                  m_degree => 1,
                  percForTables =>100,
                  percForIndexes =>100,
                  logBase => 1.2,
                  m_minimalLogLevel =>system.xp_stat.INFO,
                  m_truncateLogTable =>false,
                  m_simulation_only =>false,
                  m_stop_on_exception =>false,
                  m_log_retention_days => 2,
                  m_launch_notes => '$p_launch_notes');
end;
/
exit;

EOF
exit

Assuming VPO is a database user, from command line:

./xp_take_stats.sh pvgh1 VPO

Useful query

Update xp_stats table with analyzed date with catalog date

update xp_stats x set stats_date = (select last_analyzed from xpv_real_time_last_analyzed where owner = x.owner
and segment_name = x.segment_name and nvl(partition_name, ' ') = nvl(x.partition_name, ' '));

Quick activity log report since yesterday

select * from xp_stat_log
            where log_level > 1
            and trunc(data) in (trunc(sysdate), trunc(sysdate - 1))
            order by prog desc;

All segments ordered by last analyzed date

select * from xp_stats
            order by NVL(stats_date, to_date(1,'yyyy')) desc;

All segments analyzed in last session

select * from xp_stats
            order by analyzed_in_last_session desc;

All segments ordered by last analyzed date for histograms

select * from xp_stats
            order by NVL(h_last_date, to_date(1,'yyyy')) desc;

All stats taken in a period

select * from xp_stat_log
            where data >= to_date('31/05/2004 6.00.00', 'dd/mm/yyyy hh24.mi.ss')
            and data <= to_date('01/06/2004 8.04.58', 'dd/mm/yyyy hh24.mi.ss')
            and msg like '%Stats will be%'
            order by prog asc;

See all log history for a segment

set serveroutput on size 50000;

declare
            dummy varchar2(50);
            begin
            dummy := xp_stat.getLogForSegment(m_segment=>'VPO.VPO045_VPOV41_FK_I.',
            m_session => null, out_to_console=> TRUE, logDate => true);
            end;

For last session only select SEGMENT_LOG from:

select * from xpv_stats
            where segment_name = 'ADEMP_ALLEGATI'
            and owner='VPO';

Take stats again next time, for the segments analyzed in a period

update xp_stats set block_changes_when_analyzed = -1000000 where owner||'.'||segment_name||'.'||partition_name in (
            select distinct segment from xp_stat_log
            where data >= to_date('03/06/2004 15.00.00', 'dd/mm/yyyy hh24.mi.ss')
            and data <= to_date('03/06/2004 15.50.00', 'dd/mm/yyyy hh24.mi.ss')
            and msg like '%Stats will be%');

See how many actual bytes were to be analyzed in a session

select count(*) obj_to_analyze,  sum(bytes) bytes_total, sum(bytes)/1024/1024 MB_total, sum(bytes)/1024/1024/1024 GB_total,
sum(bytes)/1024/1024/1024/1024 TB_total from (
	select segment, bytes from xp_stat_log, dba_segments s
	where xp_session_name = 'DD761D33918F30D0E0330AB4400F30D0'
	and msg like 'Stats will be taken'
	and s.owner||'.'||segment_name||'.'||partition_name = segment
	order by prog asc
)

A usefull way to determine stats params

begin;

xp_stat.PRINTSAMPLEPERCFORBLOCKCHANGES(100, 2, 1.2);

end;

--A 2 blocks segment will only be analyzed when all its blocks will be changed, while for a

--largest segment a smaller percentual of changed blocks will be needed to take stats

Interfacing Log Miner for further evaluation

Install and configure packages and environment for custom Log Miner procedures Log Mining - Tips