Mohamed Houri’s Oracle Notes

August 6, 2020

Historical column histogram

Filed under: Oracle — hourim @ 4:03 pm

Tracking column histogram modifications is not a common task among the Oracle DBA and developer’s sphere. And, why one would want to know the past column histogram values? In this blog post I intend to give a script serving the first purpose and show an example where this script can be of a great help.

1. Historical column histogram values

Here’s below the script which I have named h_hist1 (you will see later why I decided to use this script name):

/* ----------------------------------------------------------------------------------|
|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 				         |
|																					 |
|Usage  :  SQL> @h_hist1                                                             |
|			Enter value for table_name: t1                                           |
|			Enter value for owner: test                                              |
|			Enter value for col_name: n2     				                         |
-------------------------------------------------------------------------------------|*/
col object_name   	    format a20
col column_name  		format a12
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 ,'dd/mm/yyyy hh24:mi:ss')     last_analyzed
			,to_char(timestamp# ,'dd/mm/yyyy 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;

And here’s below a simple illustration usage:

SQL> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for all columns size auto');
SQL> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for all columns size 1');
SQL> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for all columns size 3');

SQL> @h_hist1
Enter value for table_name: t1
Enter value for owner: c##mhouri
Enter value for col_name: n2

OBJECT_NAME  COLUMN_NAME  HISTOGRAM        LAST_ANALYZED        PREV_HISTOGRAM   PREV_LAST_ANALYZED
------------ ------------ ---------------- -------------------- ---------------- --------------------
T1           N2           HISTOGRAM        06/08/2020 15:39:00  HISTOGRAM        25/06/2020 18:42:06
T1           N2           NONE             06/08/2020 15:43:58  HISTOGRAM        06/08/2020 15:38:59
T1           N2           TOP-FREQUENCY    06/08/2020 16:03:19  NONE             06/08/2020 15:43:58

As you can see we went from HISTOGRAM on August the 6th at 15:39 to NONE at 15:43 then to TOP-FREQUENCY at 16:03. If I gather again statistics on the same table then here’s what the h_hist1 script will show:

SQL> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for all columns size auto');
SQL> @h_hist1

OBJECT_NAME COLUMN_NAME  HISTOGRAM        LAST_ANALYZED        PREV_HISTOGRAM   PREV_LAST_ANALYZED
----------- ------------ ---------------- -------------------- ---------------- --------------------
T1          N2           HISTOGRAM        06/08/2020 15:39:00  HISTOGRAM        25/06/2020 18:42:06
T1          N2           NONE             06/08/2020 15:43:58  HISTOGRAM        06/08/2020 15:38:59
T1          N2           HISTOGRAM        06/08/2020 16:03:19  NONE             06/08/2020 15:43:58
T1          N2           FREQUENCY        06/08/2020 16:23:00  HISTOGRAM        06/08/2020 16:03:18

While I lost the histogram type (TOP-FREQENCY) as I go back through the historical path of the n2 column, I am, however, able to say whether, previously, this column was having a histogram or not.

When you Google using the words “historical histogram Oracle”, the first hit is a blog post by Nigel Bayliss: How do I get histogram history? The script shared by Nigel has been named h_hist. Here’s below the output of this script when applied to the current t1/n2 case:

SQL> @h_hist t1 c##mhouri
Table : C##MHOURI.T1

Column: N2                                         Last analyzed: 2020-06-08 16:22:59 [Current: FREQUENCY]
-     2020-06-08 15:39:00     0 ->     5 buckets CHANGE
-     2020-06-08 15:43:58     5 ->     0 buckets CHANGE
-     2020-06-08 16:03:19     0 ->     3 buckets CHANGE
-     2020-06-08 16:22:59     3 ->     5 buckets CHANGE

PL/SQL procedure successfully completed.

It coincides perfectly with my script but, for practical reasons, I do prefer using my script 🙂

2. When you might need this script?

Here’s an example to illustrate the utility of the h_hist1 script

SQL> select count(1) from t where n2= 100;

  COUNT(1)
----------
       100

SQL_ID  2k5g2apy78hj8, child number 1
-------------------------------------
Plan hash value: 1882749816
------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |
|   1 |  SORT AGGREGATE   |          |     1 |     3 |
|*  2 |   INDEX RANGE SCAN| T_ACS_I1 |   100 |   300 |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=:SYS_B_1)

SQL> select count(1) from t where n2= 1e6;

  COUNT(1)
----------
   1099049

SQL_ID  2k5g2apy78hj8, child number 2
-------------------------------------
Plan hash value: 2966233522
---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |
|*  2 |   TABLE ACCESS FULL| T    |  1099K|  3219K|
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N2"=:SYS_B_1)

We have a query switching from a FULL TABLE SCAN to an INDEX RANGE SCAN depending on the number of rows processed by each bind variable value. So far so good until you are asked to trouble shoot a performance issue caused by this query since it is sticking into an INDEX RANGE SCAN execution plan whatever the bind variable value is

SQL> select count(1) from t where n2= 1e6;

  COUNT(1)
----------
   1099049

------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |
|   1 |  SORT AGGREGATE   |          |     1 |     3 |
|*  2 |   INDEX RANGE SCAN| T_ACS_I1 |   240K|   703K|
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=:SYS_B_1)

This performance pain occurred because someone somewhere has deleted the histogram of column n2 from table t as the following proves:

SQL> @h_hist1

OBJECT_NAME COLUMN_NAME  HISTOGRAM        LAST_ANALYZED        PREV_HISTOGRAM   PREV_LAST_ANALYZED
----------- ------------ ---------------- -------------------- ---------------- --------------------
T           N2           HISTOGRAM        06/08/2020 17:10:44  HISTOGRAM
T           N2           NONE             06/08/2020 17:32:45  HISTOGRAM        06/08/2020 17:10:43 

Thanks to the FREQUENCY histogram on the column n2, the previous cursor was bind sensitive and bind aware allowing, as such, the query to have the best execution plan possible per regards to the bind variable value. But as soon as the n2 column has lost its histogram and the cursor has been flushed from memory, the new cursor was neither bind sensitive nor bind aware explaining why the corresponding query was sticking in the INDEX RANGE SCAN execution plan.

select
   dbms_stats.report_stats_operations(since => sysdate -1) text_line
from dual;

----------------------------------------------------------------------------------------
| Operation Id | Operation              | Target              | Start Time             |
----------------------------------------------------------------------------------------
| 5219         | gather_table_stats     | "C##MHOURI"."T"     | 06/08/20               |
|              |                        |                     | 17:32:45,333000 +02:00 |
----------------------------------------------------------------------------------------

select
   dbms_stats.report_single_stats_operation
      (opid         => 5219
      ,detail_level => 'ALL')
from dual;

---------------------------------------------------------------------------------------------
| Operation | Operation          | Target          | Session | Additional Info              |
| Id        |                    |                 | Id      |                              |
---------------------------------------------------------------------------------------------
| 5219      | gather_table_stats | "C##MHOURI"."T" | 395     | Parameters: [block_sample:   |
|           |                    |                 |         | FALSE] [cascade: NULL]       |
|           |                    |                 |         | [concurrent: FALSE] [degree: |
|           |                    |                 |         | NULL] [estimate_percent:     |
|           |                    |                 |         | DBMS_STATS.AUTO_SAMPLE_SIZE] |
|           |                    |                 |         | [force: FALSE] [granularity: |
|           |                    |                 |         | AUTO] [method_opt: for all   |
|           |                    |                 |         | columns size 1]              |
|           |                    |                 |         | [no_invalidate: NULL]        |
|           |                    |                 |         | [ownname: C##MHOURI]         |
|           |                    |                 |         | [partname: ]                 |
|           |                    |                 |         | [reporting_mode: FALSE]      |
|           |                    |                 |         | [statid: ] [statown: ]       |
|           |                    |                 |         | [stattab: ] [stattype: DATA] |
|           |                    |                 |         | [tabname: t]                 |
---------------------------------------------------------------------------------------------

Spot how the last dbms_stat run (operation id n°5219) did not gather histogram because it used:

[method_opt: for all columns size 1] 

3. Conclusion

Tracking column histogram modifications can be very handy in certain circumstances. The h_hist1 script provided in this blog post could help in such case of situations

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: