Skip to Main Content
  • Questions
  • database sql performance with histograms

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Krishnaprasad.

Asked: March 18, 2018 - 6:08 pm UTC

Last updated: March 21, 2018 - 11:30 am UTC

Version: 12.1.2.0

Viewed 1000+ times

You Asked

Hi Team,

is their any way to find old histogram on column . in my situation , i see table having column status having histogram height based , but earlier it was freq. based , in this situation i know this because we have collected data , but in situation , where histogram type is changed , and if we dont have any handy details related to what type of histogram earlier it was and let suppose stats was gather and type of histogram changed then in this situation how we can find old histogram type ?

Additionally , can you help us how to find cause of plan change ? what factor we need to consider it ?
request you to provide some details information , which will help me in my learning phase




and Connor said...

You can query DBA_TAB_STATS_HISTORY to get the list of when a table had stats gather on it. For example

SQL> select STATS_UPDATE_TIME
  2  from   DBA_TAB_STATS_HISTORY
  3  where  OWNER = 'ASKTOM'
  4  and    TABLE_NAME = 'ATE_QUESTION_VIEWS'
  5  order  by 1;

STATS_UPDATE_TIME
-----------------------------------------------
22-FEB-18 11.24.21.709000 PM +08:00
22-FEB-18 11.44.27.112000 PM +08:00
23-FEB-18 10.00.28.915000 PM +08:00



(To my knowledge) we don't expose the historical histogram level data in a dictionary view, but you could perhaps get some useful information from something like

select 
     c.table_name 
    ,c.column_name
    ,h.savtime
    ,h.bucket
    ,h.endpoint
    ,h.epvalue
from sys.wri$_optstat_histgrm_history  h
    ,dba_objects o
    ,dba_tab_columns c
WHERE o.owner= 'ASKTOM'
and   o.object_name='ATE_QUESTION_VIEWS'
and   c.column_name='QUESTION_ID'
and   o.object_id=h.obj#
and   c.column_id=h.intcol#
and   o.object_name=c.table_name
and   o.owner=c.owner
order by savtime, bucket
/


but obviously, once we're digging around at that level, I can't confirm if my query is correct, or will work in different versions etc. An alternate strategy would be using the results of the first query to restore versions of the stats from various points in time, and then query the standard dictionary views.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What if....

Jose Laurindo Chiappa, March 20, 2018 - 1:03 pm UTC

What if the questioner periodically export the CBO statistics using DBMS_STATS.EXPORT_SCHEMA_STATS : he would get info about histograms or not ?
Connor McDonald
March 21, 2018 - 11:30 am UTC

Yes - the stats table could be queried to get the histogram information

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.