Home > 11g > HOW TO CHECK HISTOGRAMS PRESENT FOR A TABLE OR NOT

HOW TO CHECK HISTOGRAMS PRESENT FOR A TABLE OR NOT

March 20, 2014

How to check weather histograms available for a particular table or not.

Today i was asked to check weather histograms are available or not, immediately i googled and found a way for the same. You can use below command to check weather histograms got created or not.

set lines 120
set pages 120
column owner format a15
column table_name format a20
SELECT OWNER, TABLE_NAME, HISTOGRAM,column_name, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS where table_name like ‘

‘;

Even you can owner also.

I thought of putting my entire RD, so that i can refer in the future.

ground preparation.

1) create a table test(srno number, name varchar2(20));

2) insert into test values(&srno, ‘&name’));

3) collect the stats with col size 1.

4) check the stats.

5) collect the stats with col size auto.

6) check the stats.

7) do some select using srno.

8) collec the stats with col size auto.

9) check the stats.

10) collect the status with col size with some value > 1

11) check the stats.

Observations:-

1) No entries will be there in DBA_TAB_COL_STATISTICS table if we do not run stats collection irrespective of col size.
2) Even if i specify auto in collection, it will not collect the histograms until there is a real need.
3) Even if i specify more num of buckets, it will take only as needed.

Please fidn the entire log here with. i have done the testing in 11g database.

SQL> CREATE TABLE TEST(SRNO NUMBER, NAME VARCHAR2(20));

Table created.

SQL> insert into test values(&srno, ‘&name’)
2 ;
Enter value for srno: 1
Enter value for name: 111
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(1, ‘111’)

1 row created.

SQL> /
Enter value for srno: 2
Enter value for name: 22
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(2, ’22’)

1 row created.

SQL> /
Enter value for srno: 3
Enter value for name: 33
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(3, ’33’)

1 row created.

SQL> /
Enter value for srno: 4
Enter value for name: 44
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(4, ’44’)

1 row created.

SQL> commit;

Commit complete.

SQL> set lines 120
set pages 120
column owner format a15
column table_name format a20
SELECT OWNER, TABLE_NAME, HISTOGRAM,column_name, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS where table_name like ‘TEST’;SQL> SQL> SQL> SQL>

no rows selected

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for all columns size 1′,cascade=>true);

PL/SQL procedure successfully completed.

SQL> set lines 120
set pages 120
column owner format a15
column table_name format a20
SELECT OWNER, TABLE_NAME, HISTOGRAM,column_name, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS where table_name like ‘TEST’;SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST NONE SRNO 1

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for all columns size auto’,cascade=>true);

PL/SQL procedure successfully completed.

SQL> set lines 120
set pages 120
column owner format a15
column table_name format a20
SELECT OWNER, TABLE_NAME, HISTOGRAM,column_name, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS where table_name like ‘TEST’;SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST NONE SRNO 1

SQL> select * from test where srno=&1
2 ;
Enter value for 1: 2
old 1: select * from test where srno=&1
new 1: select * from test where srno=2

SRNO NAME
———- ——————–
2 22

SQL> /
Enter value for 1: 3
old 1: select * from test where srno=&1
new 1: select * from test where srno=3

SRNO NAME
———- ——————–
3 33

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for all columns size 1′,cascade=>true);

PL/SQL procedure successfully completed.

SQL> set lines 120
set pages 120
column owner format a15
column table_name format a20
SELECT OWNER, TABLE_NAME, HISTOGRAM,column_name, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS where table_name like ‘TEST’;SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST NONE SRNO 1

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for columns size 2 srno’,cascade=>true);

PL/SQL procedure successfully completed.

SQL> set lines 120
set pages 120
column owner format a15
column table_name format a20
SELECT OWNER, TABLE_NAME, HISTOGRAM,column_name, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS where table_name like ‘TEST’;SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST HEIGHT BALANCED SRNO 2

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for columns size 5 srno for columns size 4 name ‘,cascade=>true);

PL/SQL procedure successfully completed.

SQL> set lines 120
set pages 120
column owner format a15
column table_name format a20
SELECT OWNER, TABLE_NAME, HISTOGRAM,column_name, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS where table_name like ‘TEST’;SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST HEIGHT BALANCED NAME 4
SYSTEM TEST HEIGHT BALANCED SRNO 4

SQL>

Advertisements
Categories: 11g
%d bloggers like this: