132 lines
4.4 KiB
SQL
132 lines
4.4 KiB
SQL
@@myheader.sql
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
DEFINE tab="&&1"
|
|
DEFINE col="&&2"
|
|
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
UNDEFINE col_name
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
COLUMN _col_name NEW_VALUE col_name NOPRINT
|
|
|
|
set term off
|
|
SELECT
|
|
upper(CASE
|
|
WHEN INSTR('&&tab','.') > 0 THEN
|
|
SUBSTR('&&tab',INSTR('&&tab','.')+1)
|
|
ELSE
|
|
'&tab'
|
|
END
|
|
)
|
|
"_table_name"
|
|
, upper(CASE
|
|
WHEN INSTR('&&tab','.') > 0 THEN
|
|
UPPER(SUBSTR('&&tab',1,INSTR('&&tab','.')-1))
|
|
ELSE
|
|
user
|
|
END
|
|
)
|
|
"_owner"
|
|
, upper('&&col')
|
|
"_col_name"
|
|
FROM DUAL;
|
|
|
|
|
|
|
|
set term on
|
|
|
|
PROMPT
|
|
PROMPT *************************************************
|
|
PROMPT * Historical column histogram values
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - owner = '&&owner'
|
|
PROMPT * - table_name = '&&table_name'
|
|
PROMPT * - col_name = '&&col_name'
|
|
PROMPT *************************************************
|
|
|
|
/* ----------------------------------------------------------------------------------|
|
|
|Author : Mohamed Houri |
|
|
|Date : 03/07/2020 |
|
|
|Scope : This script gives historical column histogram values |
|
|
| -- I am using sys.WRI$_OPTSTAT_HISTHEAD_HISTORY for this purpose |
|
|
| -- I am only able to say whether, previously, there was HISTOGRAM or not |
|
|
| -- I can't show the historical type of Histogram |
|
|
| | |
|
|
-------------------------------------------------------------------------------------|*/
|
|
|
|
col object_name format a25
|
|
col column_name format a25
|
|
col last_analyzed format a20
|
|
col prev_last_analyzed format a20
|
|
col histogram format a16
|
|
col prev_histogram format a16
|
|
WITH sq AS
|
|
(
|
|
SELECT
|
|
object_id
|
|
,object_name
|
|
,subobject_name
|
|
FROM
|
|
dba_objects
|
|
WHERE
|
|
object_name = upper ('&&table_name')
|
|
AND owner = upper('&&owner')
|
|
AND subobject_name IS NULL
|
|
)
|
|
SELECT
|
|
object_name
|
|
,column_name
|
|
,lead(prev_histogram,1,histogram) over (order by last_analyzed) histogram
|
|
,last_analyzed
|
|
,prev_histogram
|
|
,prev_last_analyzed
|
|
FROM
|
|
(
|
|
SELECT
|
|
object_name
|
|
,column_name
|
|
,(select histogram from all_tab_col_statistics where owner = upper('&&owner')
|
|
and table_name = upper('&&table_name') and column_name = upper('&&col_name')) histogram
|
|
,last_analyzed
|
|
,stat_time prev_last_analyzed
|
|
,row_number() over (order by last_analyzed) rn
|
|
,case when round(derivedDensity,9)= round(density,9) then 'NONE' else 'HISTOGRAM' end prev_histogram
|
|
FROM
|
|
(
|
|
SELECT
|
|
object_name
|
|
,column_name
|
|
,to_char(savtime ,'yyyy-mm-dd hh24:mi:ss') last_analyzed
|
|
,to_char(timestamp# ,'yyyy-mm-dd hh24:mi:ss') stat_time
|
|
,density
|
|
,1/distcnt derivedDensity
|
|
,row_number() over (order by savtime) rn
|
|
,lag(case when round(1/distcnt,9) = round(density,9) then 'NONE' else 'HISTOGRAM' end) over(order by savtime) hist_histogram
|
|
FROM
|
|
sys.WRI$_OPTSTAT_HISTHEAD_HISTORY
|
|
INNER JOIN sq ON object_id = obj#
|
|
INNER JOIN (SELECT
|
|
column_id
|
|
,column_name
|
|
FROM
|
|
dba_tab_columns
|
|
WHERE
|
|
column_name = upper('&&col_name')
|
|
AND table_name = upper('&&table_name')
|
|
AND owner = upper('&&owner')
|
|
) ON intcol# = column_id
|
|
)
|
|
WHERE
|
|
rn >= 1 --exlcude/include the very first dbms_stat
|
|
)
|
|
ORDER BY
|
|
last_analyzed;
|
|
|
|
@@myfooter.sql
|