PT - Histograms are Automatically Gathered example

Scenario:

Example of how Histograms are Automatically Gathered

Querey : display_colstats.sql

col COLUMN_NAME format a15 

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, HISTOGRAM, DENSITY, NUM_BUCKETS FROM   USER_TAB_COL_STATISTICS WHERE  TABLE_NAME = 'ORDERS2';

Querey : display_histo.sql 

col COLUMN_NAME format a15 

SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_REPEAT_COUNT FROM USER_HISTOGRAMS WHERE TABLE_NAME='ORDERS2' AND COLUMN_NAME='&1' ORDER BY 2; 

Querey : display_preds.sql

col COLUMN_NAME format a14

SELECT C.COLUMN_NAME, EQUIJOIN_PREDS, NONEQUIJOIN_PREDS, RANGE_PREDS, LIKE_PREDS, NULL_PREDS  FROM   SYS.COL_USAGE$ CU, USER_OBJECTS O, USER_TAB_COLUMNS C WHERE  O.OBJECT_ID = CU.OBJ# AND (O.OBJECT_NAME=C.TABLE_NAME AND C.COLUMN_ID = CU.INTCOL#)        AND OBJECT_NAME='ORDERS2' ORDER BY OBJECT_NAME,  C.COLUMN_ID;

 Automatically Histograms Gathered example

• Check METHOD_OPT for stats which is FOR ALL COLUMNS SIZE AUTO

SQL> SELECT DBMS_STATS.GET_PREFS(PNAME =>'METHOD_OPT') METHOD_OPT FROM DUAL;

METHOD_OPT

--------------------------------------------------------------------------------

FOR ALL COLUMNS SIZE AUTO

SQL>

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

TESTPLM    READ WRITE           PRIMARY

• Create table test.orders , index and insert data in table

SQL> CREATE TABLE TEST.ORDERS2 ( ORDER_ID NUMBER(12), ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE, ORDER_TOTAL NUMBER(8,2),ORDER_MODE VARCHAR2(8), CUSTOMER_ID NUMBER(12), ORDER_STATUS NUMBER(2));

Table created.

SQL> CREATE INDEX TEST.ORDERS2_TOTAL_IX ON TEST.ORDERS2(ORDER_TOTAL);

Index created.

SQL> INSERT INTO TEST.ORDERS2 SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS FROM TEST.ORDERS WHERE ORDER_TOTAL BETWEEN 10000 AND 15000;

200625 rows created.

SQL>

SQL>

SQL>  COMMIT;

Commit complete.

• Gather stats of table orders2

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'ORDERS2', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL>

• Check the stats  specific column base histogram statistics 

SQL> @display_colstats

COLUMN_NAME     NUM_DISTINCT  NUM_NULLS HISTOGRAM          DENSITY NUM_BUCKETS

--------------- ------------ ---------- --------------- ---------- -----------

ORDER_ID              200096          0 NONE            4.9976E-06           1

ORDER_DATE            200048          0 NONE            4.9988E-06           1

ORDER_TOTAL             2600          0 NONE            .000384615           1

ORDER_MODE                 2       1807 NONE                    .5           1

CUSTOMER_ID            11136          0 NONE            .000089799           1

ORDER_STATUS              10          0 NONE                    .1           1

6 rows selected.

• Flush database monitoring information from SGA

SQL>    set linesize 180

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

PL/SQL procedure successfully completed.

• Selectivity estimation  predicate range of cloumn ORDER_TOTAL on  ORDERS2 table

SQL> @display_preds

COLUMN_NAME    EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS

-------------- -------------- ----------------- ----------- ---------- ----------

ORDER_TOTAL                 0                 0           1          0          0

SQL>

• Stast gather table orders2

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST', TABNAME=>'ORDERS2');

PL/SQL procedure successfully completed.

• Check the stats  specific column base histogram  statistics 

SQL> @display_colstats

COLUMN_NAME     NUM_DISTINCT  NUM_NULLS HISTOGRAM          DENSITY NUM_BUCKETS

--------------- ------------ ---------- --------------- ---------- -----------

ORDER_ID              200096          0 NONE            4.9976E-06           1

ORDER_DATE            200048          0 NONE            4.9988E-06           1

ORDER_TOTAL             2600          0 HYBRID             .000297         254

ORDER_MODE                 2       1807 NONE                    .5           1

CUSTOMER_ID            11136          0 NONE            .000089799           1

ORDER_STATUS              10          0 NONE                    .1           1

6 rows selected.

SQL>