Tuesday, October 24, 2017

MANUALLY CREATE HISTOGRAM WITHOUT WORKLOAD OR COLUMN USAGE INFORMATION IN SYS.COL_USAGE$

MANUALLY CREATE HISTOGRAM WITHOUT WORKLOAD OR COLUMN USAGE INFORMATION IN SYS.COL_USAGE$


For skew data, histogram provides better statistics based on data distribution to help optimizer "to generate accurate cardinality estimates for filter and join predicates that involve these columns."
see oracle doc:

When you cannot wait for SQL workload to be used to populate filter and join predicates information in COL_USAGE$.
After identifying the need to create histogram(probably after you created extended statistics/group column), the test method provides how to create histogram with workload been executed.



--creating test objects

  1. SH: IBA> create table test_histogram as select * from dba_objects;

  2. Table created.


--simulating previous statistic gathering before histogram
  1. SH: IBA> exec dbms_stats.gather_table_stats(null,'TEST_HISTOGRAM');

  2. PL/SQL procedure successfully completed.
--Query column histogram information. 
  1. SH: IBA> SELECT COLUMN_NAME ,NUM_DISTINCT,NUM_NULLS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_name='TEST_HISTOGRAM';
  1. COLUMN_NAME       NUM_DISTINCT  NUM_NULLS HISTOGRAM
  2. ------------------------------ ------------ ---------- ---------------
  3. OWNER 30     0 NONE
  4. OBJECT_NAME      46108     0 NONE
  5. SUBOBJECT_NAME 162 74732 NONE
  6. OBJECT_ID      75273     0 NONE
  7. DATA_OBJECT_ID       9540 65663 NONE
  8. OBJECT_TYPE 45     0 NONE
  9. CREATED 872     0 NONE
  10. LAST_DDL_TIME 955     0 NONE
  11. TIMESTAMP       1007     0 NONE
  12. STATUS  2     0 NONE
  13. TEMPORARY  2     0 NONE

  14. COLUMN_NAME       NUM_DISTINCT  NUM_NULLS HISTOGRAM
  15. ------------------------------ ------------ ---------- ---------------
  16. GENERATED  2     0 NONE
  17. SECONDARY  2     0 NONE
  18. NAMESPACE 21     0 NONE
  19. EDITION_NAME  0 75273 NONE

  20. 15 rows selected.

  21. SH: IBA>



--Above show histogram not created.
--Setting preference for dbms_stats

  1. SH: IBA>  exec dbms_stats.set_table_prefs('SH', 'TEST_HISTOGRAM','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 254 OBJECT_TYPE');

  2. PL/SQL procedure successfully completed.
--gather stat.
  1. SH: IBA> exec dbms_stats.gather_table_stats(null,'TEST_HISTOGRAM');

  2. PL/SQL procedure successfully completed.

  3. SH: IBA>  SELECT COLUMN_NAME ,NUM_DISTINCT,NUM_NULLS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_name='TEST_HISTOGRAM';

  4. COLUMN_NAME       NUM_DISTINCT  NUM_NULLS HISTOGRAM
  5. ------------------------------ ------------ ---------- ---------------
  6. OWNER 30     0 NONE
  7. OBJECT_NAME      46108     0 NONE
  8. SUBOBJECT_NAME 162 74732 NONE
  9. OBJECT_ID      75273     0 NONE
  10. DATA_OBJECT_ID       9540 65663 NONE
  11. OBJECT_TYPE 45     0 FREQUENCY
  12. CREATED 872     0 NONE
  13. LAST_DDL_TIME 955     0 NONE
  14. TIMESTAMP       1007     0 NONE
  15. STATUS  2     0 NONE
  16. TEMPORARY  2     0 NONE
  17. GENERATED  2     0 NONE
  18. SECONDARY  2     0 NONE
  19. NAMESPACE 21     0 NONE
  20. EDITION_NAME  0 75273 NONE

  21. 15 rows selected.

  22. SH: IBA>

--this histogram, will always be created when you executed dbms_stats accordingly.

--below show that after workload, database register predicates usage and create histogram for other columns because of "FOR ALL COLUMNS SIZE AUTO"

--run some workload.
  1. SH: IBA> select OWNER,SUBOBJECT_NAME from TEST_HISTOGRAM where OBJECT_NAME='TEST_HISTOGRAM' and created between sysdate and sysdate -1;

  2. no rows selected
--gather stats, this create new histogram basic on COL_USAGE.
  1. SH: IBA> exec dbms_stats.gather_table_stats(null,'TEST_HISTOGRAM');

  2. PL/SQL procedure successfully completed.

  3. SH: IBA> SELECT COLUMN_NAME ,NUM_DISTINCT,NUM_NULLS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_name='TEST_HISTOGRAM';

  4. COLUMN_NAME       NUM_DISTINCT  NUM_NULLS HISTOGRAM
  5. ------------------------------ ------------ ---------- ---------------
  6. OWNER 30     0 NONE
  7. OBJECT_NAME      46108     0 NONE
  8. SUBOBJECT_NAME 162 74732 NONE
  9. OBJECT_ID      75273     0 NONE
  10. DATA_OBJECT_ID       9540 65663 NONE
  11. OBJECT_TYPE 45     0 FREQUENCY
  12. CREATED 872     0 HEIGHT BALANCED
  13. LAST_DDL_TIME 955     0 NONE
  14. TIMESTAMP       1007     0 NONE
  15. STATUS  2     0 NONE
  16. TEMPORARY  2     0 NONE
  17. GENERATED  2     0 NONE
  18. SECONDARY  2     0 NONE
  19. NAMESPACE 21     0 NONE
  20. EDITION_NAME  0 75273 NONE

  21. 15 rows selected.

Hope you save some time ;)


No comments:

Post a Comment