Alok's view of the world and everything within !!!!: December 2016

Histograms:

Google definition: Histograms are a feature of the cost-based optimizer (CBO) that allows the Oracle engine to determine how data is distributed within a column. They are most useful for a column that is included in the WHERE clause of SQL and the data distribution is skewed.

Study table data, if it doesn't fit the size and definition, don't use histograms. It can have serious performance impact.

Some reference commands:
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT' , tabname => 'XEMP',method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',estimate_percent=>20,degree=>2,cascade=>TRUE,GRANULARITY=>'ALL');

select num_rows,last_analyzed from dba_tables where table_name='XEMP';

select column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_col_statistics where owner='SCOTT';

select count(1) from xemp where deptno=30;

explain plan for select sum(sal) from xemp where deptno=30;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY);

exec dbms_stats.delete_column_stats(ownname=>'SCOTT', tabname=>'XEMP', colname=>'DEPTNO', col_stat_type=>'HISTOGRAM');

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

10:00:47 SQL> explain plan for select sum(sal) from xemp where deptno in (10,20);

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY);

Explained.

Elapsed: 00:00:00.13

10:01:23 SQL> 10:01:23 SQL>

PLAN_TABLE_OUTPUT

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

Plan hash value: 2458953120

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

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

| 0 | SELECT STATEMENT | | 1 | 16 | 14837 (1)| 00:02:59 |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | TABLE ACCESS FULL| XEMP | 4196K| 64M| 14837 (1)| 00:02:59 |

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

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

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

2 - filter("DEPTNO"=10 OR "DEPTNO"=20)

14 rows selected.

10:02:15 SQL> explain plan for select sum(sal) from xemp where deptno=10;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY);

Explained.

Elapsed: 00:00:00.00

10:02:34 SQL>

PLAN_TABLE_OUTPUT

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

Plan hash value: 2458953120

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

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

| 0 | SELECT STATEMENT | | 1 | 16 | 14834 (1)| 00:02:59 |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | TABLE ACCESS FULL| XEMP | 1571K| 23M| 14834 (1)| 00:02:59 |

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

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

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

2 - filter("DEPTNO"=10)

14 rows selected.

Elapsed: 00:00:00.03

10:10:53 SQL> explain plan for select sum(sal) from xemp where deptno=30;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY);

Explained.

Elapsed: 00:00:00.01

10:11:05 SQL>

PLAN_TABLE_OUTPUT

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

Plan hash value: 2551043416

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

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

| 0 | SELECT STATEMENT | | 1 | 16 | 4 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| XEMP | 3 | 48 | 4 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IND1 | 3 | | 3 (0)| 00:00:01 | ç======================

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

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

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

3 - access("DEPTNO"=30)

15 rows selected.

----Histogram deleted

10:11:07 SQL> exec dbms_stats.delete_column_stats(ownname=>'SCOTT', tabname=>'XEMP', colname=>'DEPTNO', col_stat_type=>'HISTOGRAM');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

10:12:11 SQL> explain plan for select sum(sal) from xemp where deptno=30;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY);

Explained.

Elapsed: 00:00:00.00

10:12:27 SQL>

PLAN_TABLE_OUTPUT

Plan hash value: 2458953120

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

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

| 0 | SELECT STATEMENT | | 1 | 16 | 14834 (1)| 00:02:59 |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | TABLE ACCESS FULL| XEMP | 1398K| 21M| 14834 (1)| 00:02:59 |

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

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

2 - filter("DEPTNO"=30)

14 rows selected.

Data on which histogram was run:

10:03:31 SQL> select count(1) from xemp where deptno=&dep;

Enter value for dep: 10

old 1: select count(1) from xemp where deptno=&dep

new 1: select count(1) from xemp where deptno=10

COUNT(1)

----------

1572864

Elapsed: 00:00:00.24

10:04:12 SQL> /

Enter value for dep: 20

old 1: select count(1) from xemp where deptno=&dep

new 1: select count(1) from xemp where deptno=20

COUNT(1)

----------

2621440

Elapsed: 00:00:00.24

10:04:15 SQL> /

Enter value for dep: 30

old 1: select count(1) from xemp where deptno=&dep

new 1: select count(1) from xemp where deptno=30

COUNT(1)

----------

------------------------Impact of Method opt procedure
My preference:
exec dbms_stats.gather_schema_stats(ownname=>'&OWNER', estimate_percent => 100, method_opt => 'for all columns size AUTO',degree=>4,cascade=>true);
or
exec dbms_stats.gather_schema_stats(ownname=>'&OWNER',method_opt =>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY', estimate_percent => 100, degree=>4,cascade=>true);

To drop current histogram and have only normal stats:
exec dbms_stats.gather_schema_stats(ownname=>'&OWNER',method_opt =>NULL, estimate_percent => 100, degree=>4,cascade=>true); ----For Small database this should be enough.

Enter value for owner: SCOTT
old 1: select /*Histgram Check owner */ owner,table_name,column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_col_statistics where owner='&owner'
new 1: select /*Histgram Check owner */ owner,table_name,column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_col_statistics where owner='SCOTT'

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT XEMP EMPNO 128 0 NONE 27-DEC-16
SCOTT XEMP ENAME 8 0 NONE 27-DEC-16
SCOTT XEMP JOB 4 0 NONE 27-DEC-16
SCOTT XEMP MGR 5 11860 NONE 27-DEC-16
SCOTT XEMP HIREDATE 8 0 NONE 27-DEC-16
SCOTT XEMP SAL 7 0 NONE 27-DEC-16
SCOTT XEMP COMM 0 97380 NONE 27-DEC-16
SCOTT XEMP DEPTNO 2 0 NONE 27-DEC-16
SCOTT YEMP EMPNO 134 0 NONE 27-DEC-16
SCOTT YEMP ENAME 14 0 NONE 27-DEC-16
SCOTT YEMP JOB 5 0 NONE 27-DEC-16

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT YEMP MGR 6 12152 NONE 27-DEC-16
SCOTT YEMP HIREDATE 13 0 NONE 27-DEC-16
SCOTT YEMP SAL 12 0 NONE 27-DEC-16
SCOTT YEMP COMM 4 97132 NONE 27-DEC-16
SCOTT YEMP DEPTNO 3 0 NONE 27-DEC-16

16 rows selected.

SQL> exec dbms_stats.gather_schema_stats(ownname=>'&OWNER', estimate_percent => 100, method_opt => 'for all columns size AUTO',degree=>4,cascade=>true);
Enter value for owner: SCOTT

PL/SQL procedure successfully completed.

SQL> select /*Histgram Check owner */ owner,table_name,column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_col_statistics where owner='SCOTT'
2 ;

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT SALGRADE GRADE 5 0 NONE 13-JAN-17
SCOTT SALGRADE LOSAL 5 0 NONE 13-JAN-17
SCOTT SALGRADE HISAL 5 0 NONE 13-JAN-17
SCOTT EMP EMPNO 14 0 NONE 13-JAN-17
SCOTT EMP ENAME 14 0 NONE 13-JAN-17
SCOTT EMP JOB 5 0 NONE 13-JAN-17
SCOTT EMP MGR 6 1 NONE 13-JAN-17
SCOTT EMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT EMP SAL 12 0 NONE 13-JAN-17
SCOTT EMP COMM 4 10 NONE 13-JAN-17
SCOTT EMP DEPTNO 3 0 FREQUENCY 13-JAN-17

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT DEPT DEPTNO 4 0 NONE 13-JAN-17
SCOTT DEPT DNAME 4 0 NONE 13-JAN-17
SCOTT DEPT LOC 4 0 NONE 13-JAN-17
SCOTT BONUS ENAME 0 0 NONE 13-JAN-17
SCOTT BONUS JOB 0 0 NONE 13-JAN-17
SCOTT BONUS SAL 0 0 NONE 13-JAN-17
SCOTT BONUS COMM 0 0 NONE 13-JAN-17
SCOTT XEMP EMPNO 134 0 FREQUENCY 13-JAN-17
SCOTT XEMP ENAME 14 0 NONE 13-JAN-17
SCOTT XEMP JOB 5 0 NONE 13-JAN-17
SCOTT XEMP MGR 6 12152 NONE 13-JAN-17

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT XEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT XEMP SAL 12 0 NONE 13-JAN-17
SCOTT XEMP COMM 4 97132 NONE 13-JAN-17
SCOTT XEMP DEPTNO 3 0 NONE 13-JAN-17
SCOTT YEMP EMPNO 134 0 NONE 13-JAN-17
SCOTT YEMP ENAME 14 0 NONE 13-JAN-17
SCOTT YEMP JOB 5 0 NONE 13-JAN-17
SCOTT YEMP MGR 6 12152 NONE 13-JAN-17
SCOTT YEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT YEMP SAL 12 0 NONE 13-JAN-17
SCOTT YEMP COMM 4 97132 NONE 13-JAN-17

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT YEMP DEPTNO 3 0 NONE 13-JAN-17

34 rows selected.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'&OWNER', tabname=>'&TABLE', estimate_percent => 10, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254', degree => DBMS_STATS.DEFAULT_DEGREE, cascade => TRUE);
Enter value for owner: SCOTT
Enter value for table: ^C

SQL> exec dbms_stats.gather_schema_stats(ownname=>'&OWNER', estimate_percent => 100, method_opt => 'for all columns size AUTO',degree=>4,cascade=>true);
Enter value for owner: SCOTT

PL/SQL procedure successfully completed.

SQL> select /*Histgram Check owner */ owner,table_name,column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_col_statistics where owner='SCOTT';

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT SALGRADE GRADE 5 0 NONE 13-JAN-17
SCOTT SALGRADE LOSAL 5 0 NONE 13-JAN-17
SCOTT SALGRADE HISAL 5 0 NONE 13-JAN-17
SCOTT EMP EMPNO 14 0 NONE 13-JAN-17
SCOTT EMP ENAME 14 0 NONE 13-JAN-17
SCOTT EMP JOB 5 0 NONE 13-JAN-17
SCOTT EMP MGR 6 1 NONE 13-JAN-17
SCOTT EMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT EMP SAL 12 0 NONE 13-JAN-17
SCOTT EMP COMM 4 10 NONE 13-JAN-17
SCOTT EMP DEPTNO 3 0 FREQUENCY 13-JAN-17

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT DEPT DEPTNO 4 0 NONE 13-JAN-17
SCOTT DEPT DNAME 4 0 NONE 13-JAN-17
SCOTT DEPT LOC 4 0 NONE 13-JAN-17
SCOTT BONUS ENAME 0 0 NONE 13-JAN-17
SCOTT BONUS JOB 0 0 NONE 13-JAN-17
SCOTT BONUS SAL 0 0 NONE 13-JAN-17
SCOTT BONUS COMM 0 0 NONE 13-JAN-17
SCOTT XEMP EMPNO 134 0 FREQUENCY 13-JAN-17
SCOTT XEMP ENAME 14 0 NONE 13-JAN-17
SCOTT XEMP JOB 5 0 NONE 13-JAN-17
SCOTT XEMP MGR 6 12152 NONE 13-JAN-17

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT XEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT XEMP SAL 12 0 NONE 13-JAN-17
SCOTT XEMP COMM 4 97132 NONE 13-JAN-17
SCOTT XEMP DEPTNO 3 0 NONE 13-JAN-17
SCOTT YEMP EMPNO 134 0 NONE 13-JAN-17
SCOTT YEMP ENAME 14 0 NONE 13-JAN-17
SCOTT YEMP JOB 5 0 NONE 13-JAN-17
SCOTT YEMP MGR 6 12152 NONE 13-JAN-17
SCOTT YEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT YEMP SAL 12 0 NONE 13-JAN-17
SCOTT YEMP COMM 4 97132 NONE 13-JAN-17

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT YEMP DEPTNO 3 0 NONE 13-JAN-17

34 rows selected.

SQL> set pagesize 1000
SQL> exec dbms_stats.gather_schema_stats(ownname=>'&OWNER', estimate_percent => 100, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254',degree=>4,cascade=>true);
Enter value for owner: SCOTT

PL/SQL procedure successfully completed.

SQL> select /*Histgram Check owner */ owner,table_name,column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_col_statistics where owner='SCOTT';

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT SALGRADE GRADE 5 0 NONE 13-JAN-17
SCOTT SALGRADE LOSAL 5 0 NONE 13-JAN-17
SCOTT SALGRADE HISAL 5 0 NONE 13-JAN-17
SCOTT EMP EMPNO 14 0 HEIGHT BALANCED 13-JAN-17
SCOTT EMP ENAME 14 0 NONE 13-JAN-17
SCOTT EMP JOB 5 0 NONE 13-JAN-17
SCOTT EMP MGR 6 1 NONE 13-JAN-17
SCOTT EMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT EMP SAL 12 0 NONE 13-JAN-17
SCOTT EMP COMM 4 10 NONE 13-JAN-17
SCOTT EMP DEPTNO 3 0 FREQUENCY 13-JAN-17
SCOTT DEPT DEPTNO 4 0 HEIGHT BALANCED 13-JAN-17
SCOTT DEPT DNAME 4 0 NONE 13-JAN-17
SCOTT DEPT LOC 4 0 NONE 13-JAN-17
SCOTT BONUS ENAME 0 0 NONE 13-JAN-17
SCOTT BONUS JOB 0 0 NONE 13-JAN-17
SCOTT BONUS SAL 0 0 NONE 13-JAN-17
SCOTT BONUS COMM 0 0 NONE 13-JAN-17
SCOTT XEMP EMPNO 134 0 FREQUENCY 13-JAN-17
SCOTT XEMP ENAME 14 0 NONE 13-JAN-17
SCOTT XEMP JOB 5 0 NONE 13-JAN-17
SCOTT XEMP MGR 6 12152 NONE 13-JAN-17
SCOTT XEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT XEMP SAL 12 0 NONE 13-JAN-17
SCOTT XEMP COMM 4 97132 NONE 13-JAN-17
SCOTT XEMP DEPTNO 3 0 FREQUENCY 13-JAN-17
SCOTT YEMP EMPNO 134 0 NONE 13-JAN-17
SCOTT YEMP ENAME 14 0 NONE 13-JAN-17
SCOTT YEMP JOB 5 0 NONE 13-JAN-17
SCOTT YEMP MGR 6 12152 NONE 13-JAN-17
SCOTT YEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT YEMP SAL 12 0 NONE 13-JAN-17
SCOTT YEMP COMM 4 97132 NONE 13-JAN-17
SCOTT YEMP DEPTNO 3 0 NONE 13-JAN-17

34 rows selected.

SQL> exec dbms_stats.gather_schema_stats(ownname=>'&OWNER', estimate_percent => 100, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1',degree=>4,cascade=>true);
Enter value for owner: SCOTT

PL/SQL procedure successfully completed.

SQL> select /*Histgram Check owner */ owner,table_name,column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_col_statistics where owner='SCOTT';

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT SALGRADE GRADE 5 0 NONE 13-JAN-17
SCOTT SALGRADE LOSAL 5 0 NONE 13-JAN-17
SCOTT SALGRADE HISAL 5 0 NONE 13-JAN-17
SCOTT EMP EMPNO 14 0 NONE 13-JAN-17
SCOTT EMP ENAME 14 0 NONE 13-JAN-17
SCOTT EMP JOB 5 0 NONE 13-JAN-17
SCOTT EMP MGR 6 1 NONE 13-JAN-17
SCOTT EMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT EMP SAL 12 0 NONE 13-JAN-17
SCOTT EMP COMM 4 10 NONE 13-JAN-17
SCOTT EMP DEPTNO 3 0 FREQUENCY 13-JAN-17
SCOTT DEPT DEPTNO 4 0 NONE 13-JAN-17
SCOTT DEPT DNAME 4 0 NONE 13-JAN-17
SCOTT DEPT LOC 4 0 NONE 13-JAN-17
SCOTT BONUS ENAME 0 0 NONE 13-JAN-17
SCOTT BONUS JOB 0 0 NONE 13-JAN-17
SCOTT BONUS SAL 0 0 NONE 13-JAN-17
SCOTT BONUS COMM 0 0 NONE 13-JAN-17
SCOTT XEMP EMPNO 134 0 NONE 13-JAN-17
SCOTT XEMP ENAME 14 0 NONE 13-JAN-17
SCOTT XEMP JOB 5 0 NONE 13-JAN-17
SCOTT XEMP MGR 6 12152 NONE 13-JAN-17
SCOTT XEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT XEMP SAL 12 0 NONE 13-JAN-17
SCOTT XEMP COMM 4 97132 NONE 13-JAN-17
SCOTT XEMP DEPTNO 3 0 NONE 13-JAN-17
SCOTT YEMP EMPNO 134 0 NONE 13-JAN-17
SCOTT YEMP ENAME 14 0 NONE 13-JAN-17
SCOTT YEMP JOB 5 0 NONE 13-JAN-17
SCOTT YEMP MGR 6 12152 NONE 13-JAN-17
SCOTT YEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT YEMP SAL 12 0 NONE 13-JAN-17
SCOTT YEMP COMM 4 97132 NONE 13-JAN-17
SCOTT YEMP DEPTNO 3 0 NONE 13-JAN-17

34 rows selected.

SQL> exec dbms_stats.gather_schema_stats(ownname=>'&OWNER', estimate_percent => 100, degree=>4,cascade=>true);
Enter value for owner: SCOTT

PL/SQL procedure successfully completed.

SQL> select /*Histgram Check owner */ owner,table_name,column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_col_statistics where owner='SCOTT';

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT SALGRADE GRADE 5 0 NONE 13-JAN-17
SCOTT SALGRADE LOSAL 5 0 NONE 13-JAN-17
SCOTT SALGRADE HISAL 5 0 NONE 13-JAN-17
SCOTT EMP EMPNO 14 0 NONE 13-JAN-17
SCOTT EMP ENAME 14 0 NONE 13-JAN-17
SCOTT EMP JOB 5 0 NONE 13-JAN-17
SCOTT EMP MGR 6 1 NONE 13-JAN-17
SCOTT EMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT EMP SAL 12 0 NONE 13-JAN-17
SCOTT EMP COMM 4 10 NONE 13-JAN-17
SCOTT EMP DEPTNO 3 0 FREQUENCY 13-JAN-17
SCOTT DEPT DEPTNO 4 0 NONE 13-JAN-17
SCOTT DEPT DNAME 4 0 NONE 13-JAN-17
SCOTT DEPT LOC 4 0 NONE 13-JAN-17
SCOTT BONUS ENAME 0 0 NONE 13-JAN-17
SCOTT BONUS JOB 0 0 NONE 13-JAN-17
SCOTT BONUS SAL 0 0 NONE 13-JAN-17
SCOTT BONUS COMM 0 0 NONE 13-JAN-17
SCOTT XEMP EMPNO 134 0 FREQUENCY 13-JAN-17
SCOTT XEMP ENAME 14 0 NONE 13-JAN-17
SCOTT XEMP JOB 5 0 NONE 13-JAN-17
SCOTT XEMP MGR 6 12152 NONE 13-JAN-17
SCOTT XEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT XEMP SAL 12 0 NONE 13-JAN-17
SCOTT XEMP COMM 4 97132 NONE 13-JAN-17
SCOTT XEMP DEPTNO 3 0 NONE 13-JAN-17
SCOTT YEMP EMPNO 134 0 NONE 13-JAN-17
SCOTT YEMP ENAME 14 0 NONE 13-JAN-17
SCOTT YEMP JOB 5 0 NONE 13-JAN-17
SCOTT YEMP MGR 6 12152 NONE 13-JAN-17
SCOTT YEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT YEMP SAL 12 0 NONE 13-JAN-17
SCOTT YEMP COMM 4 97132 NONE 13-JAN-17
SCOTT YEMP DEPTNO 3 0 NONE 13-JAN-17

34 rows selected.

SQL> exec dbms_stats.gather_schema_stats(ownname=>'&OWNER',method_opt =>NULL, estimate_percent => 100, degree=>4,cascade=>true);
Enter value for owner: SCOTT

PL/SQL procedure successfully completed.

SQL> select /*Histgram Check owner */ owner,table_name,column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_col_statistics where owner='SCOTT';

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT SALGRADE GRADE 5 0 NONE 13-JAN-17
SCOTT SALGRADE LOSAL 5 0 NONE 13-JAN-17
SCOTT SALGRADE HISAL 5 0 NONE 13-JAN-17
SCOTT EMP EMPNO 14 0 NONE 13-JAN-17
SCOTT EMP ENAME 14 0 NONE 13-JAN-17
SCOTT EMP JOB 5 0 NONE 13-JAN-17
SCOTT EMP MGR 6 1 NONE 13-JAN-17
SCOTT EMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT EMP SAL 12 0 NONE 13-JAN-17
SCOTT EMP COMM 4 10 NONE 13-JAN-17
SCOTT EMP DEPTNO 3 0 NONE 13-JAN-17
SCOTT DEPT DEPTNO 4 0 NONE 13-JAN-17
SCOTT DEPT DNAME 4 0 NONE 13-JAN-17
SCOTT DEPT LOC 4 0 NONE 13-JAN-17
SCOTT BONUS ENAME 0 0 NONE 13-JAN-17
SCOTT BONUS JOB 0 0 NONE 13-JAN-17
SCOTT BONUS SAL 0 0 NONE 13-JAN-17
SCOTT BONUS COMM 0 0 NONE 13-JAN-17
SCOTT XEMP EMPNO 134 0 NONE 13-JAN-17
SCOTT XEMP ENAME 14 0 NONE 13-JAN-17
SCOTT XEMP JOB 5 0 NONE 13-JAN-17
SCOTT XEMP MGR 6 12152 NONE 13-JAN-17
SCOTT XEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT XEMP SAL 12 0 NONE 13-JAN-17
SCOTT XEMP COMM 4 97132 NONE 13-JAN-17
SCOTT XEMP DEPTNO 3 0 NONE 13-JAN-17
SCOTT YEMP EMPNO 134 0 NONE 13-JAN-17
SCOTT YEMP ENAME 14 0 NONE 13-JAN-17
SCOTT YEMP JOB 5 0 NONE 13-JAN-17
SCOTT YEMP MGR 6 12152 NONE 13-JAN-17
SCOTT YEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT YEMP SAL 12 0 NONE 13-JAN-17
SCOTT YEMP COMM 4 97132 NONE 13-JAN-17
SCOTT YEMP DEPTNO 3 0 NONE 13-JAN-17

34 rows selected.

SQL> exec dbms_stats.gather_schema_stats(ownname=>'&OWNER',method_opt =>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY', estimate_percent => 100, degree=>4,cascade=>true);
Enter value for owner: SCOTT

PL/SQL procedure successfully completed.

SQL> select /*Histgram Check owner */ owner,table_name,column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_col_statistics where owner='SCOTT';

OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ------------ ---------- --------------- ---------
SCOTT SALGRADE GRADE 5 0 NONE 13-JAN-17
SCOTT SALGRADE LOSAL 5 0 NONE 13-JAN-17
SCOTT SALGRADE HISAL 5 0 NONE 13-JAN-17
SCOTT EMP EMPNO 14 0 NONE 13-JAN-17
SCOTT EMP ENAME 14 0 NONE 13-JAN-17
SCOTT EMP JOB 5 0 NONE 13-JAN-17
SCOTT EMP MGR 6 1 NONE 13-JAN-17
SCOTT EMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT EMP SAL 12 0 NONE 13-JAN-17
SCOTT EMP COMM 4 10 NONE 13-JAN-17
SCOTT EMP DEPTNO 3 0 NONE 13-JAN-17
SCOTT DEPT DEPTNO 4 0 NONE 13-JAN-17
SCOTT DEPT DNAME 4 0 NONE 13-JAN-17
SCOTT DEPT LOC 4 0 NONE 13-JAN-17
SCOTT BONUS ENAME 0 0 NONE 13-JAN-17
SCOTT BONUS JOB 0 0 NONE 13-JAN-17
SCOTT BONUS SAL 0 0 NONE 13-JAN-17
SCOTT BONUS COMM 0 0 NONE 13-JAN-17
SCOTT XEMP EMPNO 134 0 FREQUENCY 13-JAN-17
SCOTT XEMP ENAME 14 0 NONE 13-JAN-17
SCOTT XEMP JOB 5 0 NONE 13-JAN-17
SCOTT XEMP MGR 6 12152 NONE 13-JAN-17
SCOTT XEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT XEMP SAL 12 0 NONE 13-JAN-17
SCOTT XEMP COMM 4 97132 NONE 13-JAN-17
SCOTT XEMP DEPTNO 3 0 FREQUENCY 13-JAN-17
SCOTT YEMP EMPNO 134 0 NONE 13-JAN-17
SCOTT YEMP ENAME 14 0 NONE 13-JAN-17
SCOTT YEMP JOB 5 0 NONE 13-JAN-17
SCOTT YEMP MGR 6 12152 NONE 13-JAN-17
SCOTT YEMP HIREDATE 13 0 NONE 13-JAN-17
SCOTT YEMP SAL 12 0 NONE 13-JAN-17
SCOTT YEMP COMM 4 97132 NONE 13-JAN-17
SCOTT YEMP DEPTNO 3 0 NONE 13-JAN-17

34 rows selected.

Just for reference: this demo data for scott schema, XEMP & YEMP are clone table created by me.
SQL> select count(1) from XEMP;

COUNT(1)
----------
97136

SQL> select count(1) from YEMP;

COUNT(1)
----------
97136

Must Read: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf

Alok's view of the world and everything within !!!!

Monday, 12 December 2016

Oracle : Histograms and the impact

Still learning ways of the world after 23yrs in IT/Database arena.

About Me

Total Pageviews