Oracle Sql_id Stale statistics

Oracle Sql_id Stale statistics

SQL  stale optimizer statistics




define sql_id='5u4sk8mw75xxx'

set pagesize 100
set linesize 150
set trims off
set tab off
set verify off
column table_name format a50
column index_name format a50
column object_type format a40
column owner format a15


PROMPT ==========
PROMPT Tables
PROMPT ==========
with plan_tables as (
select distinct object_name,object_owner, object_type 
from v$sql_plan 
where object_type like 'TABLE%' 
and   sql_id      = '&sql_id')
select t.object_owner owner,
       t.object_name table_name,
       t.object_type object_type,
       decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness   
from   dba_tab_statistics s,
       plan_tables        t
where  s.table_name = t.object_name
and    s.owner      = t.object_owner
and    s.partition_name is null
and    s.subpartition_name is null
order by t.object_owner, t.object_name;

PROMPT ==========
PROMPT Indexes
PROMPT ==========
with plan_indexes as (
select distinct object_name,object_owner, object_type
from v$sql_plan
where object_type like 'INDEX%'
and   sql_id      = '&sql_id')
select i.object_owner owner,
       i.object_name index_name,
       i.object_type object_type,
       decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness
from   dba_ind_statistics s,
       plan_indexes       i
where  s.index_name = i.object_name
and    s.owner      = i.object_owner
and    s.partition_name is null
and    s.subpartition_name is null
order by i.object_owner, i.object_name;



from ..
https://github.com/jkstill/oracle-script-lib/blob/master/sql/stats-sqlid.sql




ttitle off
btitle off

define 1='5u4sk8mw75xxx'
define 2='Y'
col s_diag_pack new_value s_diag_pack noprint
col s_sql_id new_value s_sql_id noprint
var v_sql_id varchar2(13)



set feed off term off 
select '&1' s_sql_id from dual;

set term on

whenever sqlerror exit 128

begin 
	:v_sql_id := '&s_sql_id';
	if 
		length(:v_sql_id) < 1
		or 
		:v_sql_id is null
	then
		raise value_error;
	end if;
end;
/

whenever sqlerror continue
set feed on


define 2='Y'

set feed off term off 
select decode(upper('&2'),'Y','','--') s_diag_pack from dual;
set feed on term on


set pagesize 100 linesize 300 trimspool on  heading on

col partition_start format a6 head 'PSTART'
col sql_id format a13
col partition_stop format a6 head 'PSTOP'
col owner format a20
col table_name format a30
col index_name format a40
col phv format a60 wrap
col last_analyzed format a19
col stale_stats format a12 head 'stale_stats'
col num_rows format 99,999,999,999
col blocks format 9,99,999,999
col partition_position format 999999 head 'PP'
col TABLE_NAME  for a27
break on sql_id skip 1

--spool stats-sqlid.txt

with objects as (
	-- extra inline view is to eliminate duplicates in listagg()
	select 
		sql_id
		, listagg(phv,',') within group(order by phv)  phv
		, object_owner
		, object_name
		, object_type
		, partition_start
		, partition_stop
	from (
		select distinct
			sql_id
			, phv
			, object_owner
			, object_name
			, object_type
			, partition_start
			, partition_stop
		from (
			select 
				sql_id
				, plan_hash_value phv
				, object_owner
				, object_name
				, object_type
				, case partition_start
					when 'ROW LOCATION' then 'ROWID'
					else partition_start
				end partition_start
				, case partition_stop
					when 'ROW LOCATION' then 'ROWID'
					else partition_stop
				end partition_stop
			from v$sql_plan
			where sql_id = :v_sql_id
			and object_owner is not null
			and object_type in ('TABLE','INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)','CLUSTER','TABLE (FIXED)')
			&s_diag_pack union all
			&s_diag_pack select 
				&s_diag_pack sql_id
				&s_diag_pack , plan_hash_value phv
				&s_diag_pack , object_owner
				&s_diag_pack , object_name
				&s_diag_pack , object_type
				&s_diag_pack , case partition_start
					&s_diag_pack when 'ROW LOCATION' then 'ROWID'
					&s_diag_pack else partition_start
				&s_diag_pack end partition_start
				&s_diag_pack , case partition_stop
					&s_diag_pack when 'ROW LOCATION' then 'ROWID'
					&s_diag_pack else partition_stop
				&s_diag_pack end partition_stop
			&s_diag_pack from dba_hist_sql_plan
			&s_diag_pack where sql_id = :v_sql_id
			&s_diag_pack and object_owner is not null
			&s_diag_pack and object_type in ('TABLE','INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)','CLUSTER','TABLE (FIXED)')
		)
	)
	group by
		sql_id
		, object_owner
		, object_name
		, object_type
		, partition_start
		, partition_stop
),
indexes as (
	select * from objects where object_type in ('INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)')
),
tables as (
	select * from objects where object_type in ('TABLE','CLUSTER','TABLE (FIXED)')
)
select 
	sql_id
	, phv
	, owner
	, table_name
		|| decode(s.partition_name, null,'','.' || s.partition_name)
		as table_name
	, null index_name
	, partition_position
	, t.partition_start
	, t.partition_stop
	, num_rows
	, blocks
	, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed
	, stale_stats
from dba_tab_statistics s
 join tables t on t.object_owner = s.owner
	and t.object_name = s.table_name
union all
select 
	sql_id
	, phv
	, owner
	, table_name 
	, index_name
		|| decode(s.partition_name, null,'','.' || s.partition_name)
		as index_name
	, partition_position
	, i.partition_start
	, i.partition_stop
	, num_rows
	, leaf_blocks blocks
	, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed
	, stale_stats
from dba_ind_statistics s
 join indexes i on i.object_owner = s.owner
	and i.object_name = s.index_name
order by sql_id
	, owner
	, table_name
	, index_name nulls first
	, partition_position nulls first
/

--spool off