Categories

JAVA DATEBASE
Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Java
Desktop
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Architecture
Migration and Modernization
E-Business Suite
Siebel
PeopleSoft Enterprise
JD Edwards World
Industries
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Primavera
ATG
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums

 



Tags

General Questions


Why histograms were not created first ?


Hi,  I am on 11.2.0.3.  I created a table, inserted data in it, indexes are there and gathred stats on the table. Here are the SQLs of my activity. Question is, why histograms were not created in first 2 attempts (i.e. with default method_opt and SIZE AUTO) ? SQL> select index_name, column_name from user_ind_columns where table_name='TABLE1';   INDEX_NAME                     COLUMN_NAME ------------------------------ ----------- TABLE1_IDX2                    ATTR_NUM     --- Skewed column TABLE1_IDX1                    ID      TABLE1_IDX1                    ATTR_NUM TABLE1_IDX1                    SEQ_ID     SQL> select count(*) from table1;     COUNT(*) ----------     159998   -- First try SQL> begin   2  dbms_stats.gather_table_stats( ownname => USER,   3  tabname => 'TABLE1',   4  cascade => TRUE);   5  end;   6  /    -- No histograms created SQL> select table_name, column_name, histogram from user_tab_col_statistics where table_name='TABLE1';   TABLE_NAME                     COLUMN_NAME                    HISTOGRAM ------------------------------ ------------------------------ --------------- TABLE1                         ID                             NONE TABLE1                         ATTR_NUM                       NONE TABLE1                         START_DATE                     NONE TABLE1                         SEQ_ID                         NONE TABLE1                         ATTR_VALUE                     NONE TABLE1                         ATTR_STATUS                    NONE TABLE1                         END_DATE                       NONE TABLE1                         EX_DATE                        NONE   -- Second try SQL> begin   2  dbms_stats.gather_table_stats( ownname => USER,   3  tabname => 'TABLE1',   4  cascade => TRUE,   5  method_opt => 'FOR ALL COLUMNS SIZE AUTO');   6  end;   7  /    -- Still no histograms created SQL> select table_name, column_name, histogram from user_tab_col_statistics where table_name='TABLE1';   TABLE_NAME                     COLUMN_NAME                    HISTOGRAM ------------------------------ ------------------------------ --------------- TABLE1                         ID                             NONE TABLE1                         ATTR_NUM                       NONE TABLE1                         START_DATE                     NONE TABLE1                         SEQ_ID                         NONE TABLE1                         ATTR_VALUE                     NONE TABLE1                         ATTR_STATUS                    NONE TABLE1                         END_DATE                       NONE TABLE1                         EX_DATE                        NONE   -- Third try SQL> begin   2  dbms_stats.gather_table_stats( ownname => USER,   3  tabname => 'TABLE1',   4  cascade => TRUE,   5  method_opt => 'FOR ALL COLUMNS SIZE 254');   6  end;   7  /    -- Finally histograms created, but look at the sample size SQL> select table_name, column_name, num_buckets, sample_size, histogram   2  from user_tab_col_statistics where table_name='TABLE1';   TABLE_NAME      COLUMN_NAME     NUM_BUCKETS SAMPLE_SIZE HISTOGRAM --------------- --------------- ----------- ----------- --------------- TABLE1          ID                      254        5357 HEIGHT BALANCED TABLE1          ATTR_NUM                 25        5357 FREQUENCY TABLE1          START_DATE              254        5357 HEIGHT BALANCED TABLE1          SEQ_ID                    5        5358 FREQUENCY TABLE1          ATTR_VALUE              255        5357 HEIGHT BALANCED TABLE1          ATTR_STATUS               1        5357 FREQUENCY TABLE1          END_DATE                  1        5357 FREQUENCY TABLE1          EX_DATE                   0             NONE   Thanks in advance

Please check, https://forums.oracle.com/forums/thread.jspa?threadID=657198

Hi Fran,  I have seen Oracle docs. Oracle documentation recommends to go with default setup i.e. do not give any value for METHOD_OPT and let Oracle decide. That's what I did in the first go, it did not create histograms and thats my question.

The SIZE AUTO option means that a histogram will be considered only if the column has appeared in the WHERE clause of at least one query.  "SIZE 254" collects histograms unconditionally  Regards Jonathan Lewis

From the DBMS_STATS.GATHER_TABLE_STATS documentation on the METHOD_OPT parameter  "- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns."  If you just created the table then, I'm guessing, you haven't generated any workload that involved the column (that is, you haven't executed any queries where the column with skewed data has been queried).  That tells Oracle that, at least historically, it wouldn't have been useful to have a histogram.  If there has historically not been a use for a histogram, Oracle guesses that there won't be one in the future either.  Gathering histograms isn't free (both in terms of the cost of gathering them and the cost of maintaining them over time particularly for monotonically increasing columns).  So Oracle tries to strike a balance between making statistics quick and easy to gather and maintain and ensuring that the optimizer has all the information it will realistically need to optimize queries.  Justin

Thanks Jonathan. That explains why histogram was not created.


Related Links

ORA-12154:TNS:could not resolve the connect identifier specified
Junk characters while using UNISTR function
Help understanding Materialized views
Best way to change partition key on existing table
DataBase is very slow performance
Database migration
Grant Read only privilege
Deleting Data from 85 tables
Ora-00907
Oracle database object name 30 characters limit
Import a database
script for aix
PDF Files in Oracle Database
Running STATSPACK in oralce 10g
Oracle database monitored using SNMP
where is my linstener.ora