Search My Oracle Blog

Custom Search

02 August, 2008

More Tests of COL_USAGE

NOTE : This is a follow up on my earliest post on COL_USAGE.

A few more simple tests to confirm that COL_USAGE is used by 'SIZE AUTO'. Oracle does not gather Histograms on columns not identified as having been queried -- which "identification" information is stored in SYS.COL_USAGE$.



22:51:33 SQL> col object_name format a30
22:51:37 SQL> select object_name, object_id from user_objects where object_type = 'TABLE';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
SOURCE_TABLE 53391
TEST_DATA 53392
OTHER_TEST_DATA 53395

22:51:38 SQL> select * from sys.col_usage$ where object_id in (53391,53392,53395);
select * from sys.col_usage$ where object_id in (53391,53392,53395)
*
ERROR at line 1:
ORA-00904: "OBJECT_ID": invalid identifier


22:51:54 SQL> select * from sys.col_usage$ where obj# in (53391,53392,53395);

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------------
53392 1 1 0 0 0 0 0 01-AUG 23:30:26
53392 3 0 0 0 1 0 0 01-AUG 23:39:23
53392 4 0 0 0 0 1 0 01-AUG 23:45:31
53395 4 0 0 0 0 1 0 02-AUG 00:14:00

22:52:33 SQL> select table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed
22:53:53 2 from user_tab_col_statistics
22:54:00 3 order by 2,1;

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ------------------------------ --------------- ------------ ----------- ----------- ---------------
SOURCE_TABLE CREATED NONE 1335 1 61594 02-AUG 00:27:57
OTHER_TEST_DATA CREATED_COL NONE 1262 1 30390 02-AUG 00:27:54
TEST_DATA CREATED_COL NONE 1269 1 30211 02-AUG 00:27:58
SOURCE_TABLE DATA_OBJECT_ID NONE 3962 1 5029 02-AUG 00:27:57
SOURCE_TABLE GENERATED NONE 2 1 61594 02-AUG 00:27:57
OTHER_TEST_DATA ID_COL NONE 50341 1 30390 02-AUG 00:27:54
TEST_DATA ID_COL NONE 50492 1 30211 02-AUG 00:27:58
SOURCE_TABLE LAST_DDL_TIME NONE 1373 1 61594 02-AUG 00:27:57
OTHER_TEST_DATA NAME_COL NONE 28169 1 30390 02-AUG 00:27:54
TEST_DATA NAME_COL NONE 28226 1 30211 02-AUG 00:27:58
SOURCE_TABLE OBJECT_ID NONE 50616 1 61594 02-AUG 00:27:57
SOURCE_TABLE OBJECT_NAME NONE 28952 1 61594 02-AUG 00:27:57
SOURCE_TABLE OBJECT_TYPE NONE 40 1 61594 02-AUG 00:27:57
SOURCE_TABLE OWNER NONE 26 1 61594 02-AUG 00:27:57
OTHER_TEST_DATA OWNER_COL NONE 26 1 30390 02-AUG 00:27:54
TEST_DATA OWNER_COL FREQUENCY 17 17 30211 02-AUG 00:27:58
SOURCE_TABLE SECONDARY NONE 2 1 61594 02-AUG 00:27:57
SOURCE_TABLE STATUS NONE 1 1 61594 02-AUG 00:27:57
SOURCE_TABLE SUBOBJECT_NAME NONE 102 1 505 02-AUG 00:27:57
SOURCE_TABLE TEMPORARY NONE 2 1 61594 02-AUG 00:27:57
SOURCE_TABLE TIMESTAMP NONE 1421 1 61594 02-AUG 00:27:57
OTHER_TEST_DATA TYPE_COL FREQUENCY 36 36 30390 02-AUG 00:27:54
TEST_DATA TYPE_COL FREQUENCY 38 38 30211 02-AUG 00:27:58

23 rows selected.

22:54:07 SQL> l3
3* order by 2,1
22:54:15 SQL> c/2,1/1,2
3* order by 1,2
22:54:17 SQL>
22:54:18 SQL> /

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ------------------------------ --------------- ------------ ----------- ----------- ---------------
OTHER_TEST_DATA CREATED_COL NONE 1262 1 30390 02-AUG 00:27:54
OTHER_TEST_DATA ID_COL NONE 50341 1 30390 02-AUG 00:27:54
OTHER_TEST_DATA NAME_COL NONE 28169 1 30390 02-AUG 00:27:54
OTHER_TEST_DATA OWNER_COL NONE 26 1 30390 02-AUG 00:27:54
OTHER_TEST_DATA TYPE_COL FREQUENCY 36 36 30390 02-AUG 00:27:54
SOURCE_TABLE CREATED NONE 1335 1 61594 02-AUG 00:27:57
SOURCE_TABLE DATA_OBJECT_ID NONE 3962 1 5029 02-AUG 00:27:57
SOURCE_TABLE GENERATED NONE 2 1 61594 02-AUG 00:27:57
SOURCE_TABLE LAST_DDL_TIME NONE 1373 1 61594 02-AUG 00:27:57
SOURCE_TABLE OBJECT_ID NONE 50616 1 61594 02-AUG 00:27:57
SOURCE_TABLE OBJECT_NAME NONE 28952 1 61594 02-AUG 00:27:57
SOURCE_TABLE OBJECT_TYPE NONE 40 1 61594 02-AUG 00:27:57
SOURCE_TABLE OWNER NONE 26 1 61594 02-AUG 00:27:57
SOURCE_TABLE SECONDARY NONE 2 1 61594 02-AUG 00:27:57
SOURCE_TABLE STATUS NONE 1 1 61594 02-AUG 00:27:57
SOURCE_TABLE SUBOBJECT_NAME NONE 102 1 505 02-AUG 00:27:57
SOURCE_TABLE TEMPORARY NONE 2 1 61594 02-AUG 00:27:57
SOURCE_TABLE TIMESTAMP NONE 1421 1 61594 02-AUG 00:27:57
TEST_DATA CREATED_COL NONE 1269 1 30211 02-AUG 00:27:58
TEST_DATA ID_COL NONE 50492 1 30211 02-AUG 00:27:58
TEST_DATA NAME_COL NONE 28226 1 30211 02-AUG 00:27:58
TEST_DATA OWNER_COL FREQUENCY 17 17 30211 02-AUG 00:27:58
TEST_DATA TYPE_COL FREQUENCY 38 38 30211 02-AUG 00:27:58

23 rows selected.

22:54:18 SQL> REM Currently there are no Histograms on SOURCE_TABLES, and a Frequency Histogram on OTHER_TEST_DATA.TYPE_COL and
22:55:06 SQL> REM on TEST_DATA.OWNER_COL, TEST_DATA.TYPE_COL
22:55:18 SQL> REM ....note that SOURCE_TABLE isn't reflected in SYS.COL_USAGE$, there being no queries on it
22:55:58 SQL>
22:55:58 SQL> REM what happens if I delete SYS.COL_USAGE$ (do NOT try this on your Production database !) and re-gather SIZE AUTO ?
22:56:25 SQL>
22:56:26 SQL> REM -- first a retest with SIZE AUTO
22:56:41 SQL>
22:56:41 SQL> exec dbms_stats.gather_schema_stats(user,estiamte_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO');
BEGIN dbms_stats.gather_schema_stats(user,estiamte_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GATHER_SCHEMA_STATS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


22:57:03 SQL> exec dbms_stats.gather_schema_stats(user,estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

22:57:34 SQL> select table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed
22:57:41 2 from user_tab_col_statistics order by 1,2;

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ------------------------------ --------------- ------------ ----------- ----------- ---------------
OTHER_TEST_DATA CREATED_COL NONE 1380 1 151815 02-AUG 22:57:26
OTHER_TEST_DATA ID_COL NONE 50605 1 151815 02-AUG 22:57:26
OTHER_TEST_DATA NAME_COL NONE 30275 1 151815 02-AUG 22:57:26
OTHER_TEST_DATA OWNER_COL NONE 26 1 151815 02-AUG 22:57:26
OTHER_TEST_DATA TYPE_COL FREQUENCY 41 41 151815 02-AUG 22:57:26
SOURCE_TABLE CREATED NONE 1378 1 303666 02-AUG 22:57:32
SOURCE_TABLE DATA_OBJECT_ID NONE 4028 1 24684 02-AUG 22:57:32
SOURCE_TABLE GENERATED NONE 2 1 303666 02-AUG 22:57:32
SOURCE_TABLE LAST_DDL_TIME NONE 1424 1 303666 02-AUG 22:57:32
SOURCE_TABLE OBJECT_ID NONE 50611 1 303666 02-AUG 22:57:32
SOURCE_TABLE OBJECT_NAME NONE 30281 1 303666 02-AUG 22:57:32
SOURCE_TABLE OBJECT_TYPE NONE 41 1 303666 02-AUG 22:57:32
SOURCE_TABLE OWNER NONE 26 1 303666 02-AUG 22:57:32
SOURCE_TABLE SECONDARY NONE 2 1 303666 02-AUG 22:57:32
SOURCE_TABLE STATUS NONE 1 1 303666 02-AUG 22:57:32
SOURCE_TABLE SUBOBJECT_NAME NONE 102 1 2466 02-AUG 22:57:32
SOURCE_TABLE TEMPORARY NONE 2 1 303666 02-AUG 22:57:32
SOURCE_TABLE TIMESTAMP NONE 1487 1 303666 02-AUG 22:57:32
TEST_DATA CREATED_COL NONE 1380 1 151815 02-AUG 22:57:34
TEST_DATA ID_COL NONE 50605 1 151815 02-AUG 22:57:34
TEST_DATA NAME_COL NONE 30275 1 151815 02-AUG 22:57:34
TEST_DATA OWNER_COL FREQUENCY 18 18 151815 02-AUG 22:57:34
TEST_DATA TYPE_COL FREQUENCY 41 41 151815 02-AUG 22:57:34

23 rows selected.

22:57:54 SQL> REM The histograms are on OTHER_TEST_DATE.TYPE_COL and TEST_DATA.OWNER_COL and TEST_DATA.TYPE_COL
22:58:21 SQL>
22:58:22 SQL> REM Let's delete sys.col_usage$ -- do NOT try this on your Production database
22:58:38 SQL>
22:58:38 SQL> delete sys.col_usage$ where obj# in (select object_id from user_objects);

4 rows deleted.

22:58:53 SQL> commit;

Commit complete.

22:58:55 SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

22:59:04 SQL>
22:59:05 SQL> REM Now, re-gather SIZE AUTO
22:59:11 SQL>
22:59:14 SQL> exec dbms_stats.gather_schema_stats(user,estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

22:59:31 SQL> select table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed
22:59:39 2 from user_tab_col_statistics order by 1,2;

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ------------------------------ --------------- ------------ ----------- ----------- ---------------
OTHER_TEST_DATA CREATED_COL NONE 1380 1 151815 02-AUG 22:59:24
OTHER_TEST_DATA ID_COL NONE 50605 1 151815 02-AUG 22:59:24
OTHER_TEST_DATA NAME_COL NONE 30275 1 151815 02-AUG 22:59:24
OTHER_TEST_DATA OWNER_COL NONE 26 1 151815 02-AUG 22:59:24
OTHER_TEST_DATA TYPE_COL NONE 41 1 151815 02-AUG 22:59:24
SOURCE_TABLE CREATED NONE 1378 1 303666 02-AUG 22:59:29
SOURCE_TABLE DATA_OBJECT_ID NONE 4028 1 24684 02-AUG 22:59:29
SOURCE_TABLE GENERATED NONE 2 1 303666 02-AUG 22:59:29
SOURCE_TABLE LAST_DDL_TIME NONE 1424 1 303666 02-AUG 22:59:29
SOURCE_TABLE OBJECT_ID NONE 50611 1 303666 02-AUG 22:59:29
SOURCE_TABLE OBJECT_NAME NONE 30281 1 303666 02-AUG 22:59:29
SOURCE_TABLE OBJECT_TYPE NONE 41 1 303666 02-AUG 22:59:29
SOURCE_TABLE OWNER NONE 26 1 303666 02-AUG 22:59:29
SOURCE_TABLE SECONDARY NONE 2 1 303666 02-AUG 22:59:29
SOURCE_TABLE STATUS NONE 1 1 303666 02-AUG 22:59:29
SOURCE_TABLE SUBOBJECT_NAME NONE 102 1 2466 02-AUG 22:59:29
SOURCE_TABLE TEMPORARY NONE 2 1 303666 02-AUG 22:59:29
SOURCE_TABLE TIMESTAMP NONE 1487 1 303666 02-AUG 22:59:29
TEST_DATA CREATED_COL NONE 1380 1 151815 02-AUG 22:59:31
TEST_DATA ID_COL NONE 50605 1 151815 02-AUG 22:59:31
TEST_DATA NAME_COL NONE 30275 1 151815 02-AUG 22:59:31
TEST_DATA OWNER_COL NONE 18 1 151815 02-AUG 22:59:31
TEST_DATA TYPE_COL NONE 41 1 151815 02-AUG 22:59:31

23 rows selected.

22:59:47 SQL> REM Even at 100% sampling, NO Histograms are gathered by SIZE AUTO.
23:00:04 SQL> REM This would be because COL_USAGE$ doesn't reflect any information on queries.
23:00:16 SQL> select count(*) from other_test_data where owner_col like 'HEMAN%';

COUNT(*)
----------
9

23:01:12 SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

23:01:21 SQL> select * from sys.col_usage$ where obj# in (select object_id from user_objects);

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------------
53395 1 0 0 0 0 1 0 02-AUG 23:01:20

23:01:42 SQL> REM a LIKE query on OTHER_TEST_DATA.OWNER_COL (object_id 53395 is 'OTHER_TEST_DATA' and Col#1 is 'OWNER_COL') is now reflected
23:02:30 SQL>
23:02:30 SQL> REM regather 100% with SIZE AUTO
23:02:36 SQL> exec dbms_stats.gather_schema_stats(user,estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

23:02:55 SQL> select table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed
23:03:07 2 from user_tab_col_statistics order by 1,2;

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ------------------------------ --------------- ------------ ----------- ----------- ---------------
OTHER_TEST_DATA CREATED_COL NONE 1380 1 151815 02-AUG 23:02:49
OTHER_TEST_DATA ID_COL NONE 50605 1 151815 02-AUG 23:02:49
OTHER_TEST_DATA NAME_COL NONE 30275 1 151815 02-AUG 23:02:49
OTHER_TEST_DATA OWNER_COL FREQUENCY 26 26 151815 02-AUG 23:02:49
OTHER_TEST_DATA TYPE_COL NONE 41 1 151815 02-AUG 23:02:49
SOURCE_TABLE CREATED NONE 1378 1 303666 02-AUG 23:02:54
SOURCE_TABLE DATA_OBJECT_ID NONE 4028 1 24684 02-AUG 23:02:54
SOURCE_TABLE GENERATED NONE 2 1 303666 02-AUG 23:02:54
SOURCE_TABLE LAST_DDL_TIME NONE 1424 1 303666 02-AUG 23:02:54
SOURCE_TABLE OBJECT_ID NONE 50611 1 303666 02-AUG 23:02:54
SOURCE_TABLE OBJECT_NAME NONE 30281 1 303666 02-AUG 23:02:54
SOURCE_TABLE OBJECT_TYPE NONE 41 1 303666 02-AUG 23:02:54
SOURCE_TABLE OWNER NONE 26 1 303666 02-AUG 23:02:54
SOURCE_TABLE SECONDARY NONE 2 1 303666 02-AUG 23:02:54
SOURCE_TABLE STATUS NONE 1 1 303666 02-AUG 23:02:54
SOURCE_TABLE SUBOBJECT_NAME NONE 102 1 2466 02-AUG 23:02:54
SOURCE_TABLE TEMPORARY NONE 2 1 303666 02-AUG 23:02:54
SOURCE_TABLE TIMESTAMP NONE 1487 1 303666 02-AUG 23:02:54
TEST_DATA CREATED_COL NONE 1380 1 151815 02-AUG 23:02:55
TEST_DATA ID_COL NONE 50605 1 151815 02-AUG 23:02:55
TEST_DATA NAME_COL NONE 30275 1 151815 02-AUG 23:02:55
TEST_DATA OWNER_COL NONE 18 1 151815 02-AUG 23:02:55
TEST_DATA TYPE_COL NONE 41 1 151815 02-AUG 23:02:55

23 rows selected.

23:03:15 SQL> REM Yes, we now see a Frequency Histogram (26 buckets for 26 known values) on OTHER_TEST_DATA.OWNER_COL
23:03:41 SQL> REM but no Histogram on other columns
23:03:46 SQL>
23:03:47 SQL>
23:03:47 SQL> REM Let's test SIZE SKEWONLY
23:03:55 SQL> exec dbms_stats.gather_schema_stats(user,estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed.

23:04:26 SQL> select table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed
23:04:41 2 from user_tab_col_statistics order by 1,2;

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ------------------------------ --------------- ------------ ----------- ----------- ---------------
OTHER_TEST_DATA CREATED_COL HEIGHT BALANCED 1380 254 151815 02-AUG 23:04:15
OTHER_TEST_DATA ID_COL NONE 50605 1 151815 02-AUG 23:04:15
OTHER_TEST_DATA NAME_COL HEIGHT BALANCED 30275 254 151815 02-AUG 23:04:15
OTHER_TEST_DATA OWNER_COL FREQUENCY 26 26 151815 02-AUG 23:04:15
OTHER_TEST_DATA TYPE_COL FREQUENCY 41 41 151815 02-AUG 23:04:15
SOURCE_TABLE CREATED HEIGHT BALANCED 1378 254 303666 02-AUG 23:04:23
SOURCE_TABLE DATA_OBJECT_ID HEIGHT BALANCED 4028 254 24684 02-AUG 23:04:23
SOURCE_TABLE GENERATED FREQUENCY 2 2 303666 02-AUG 23:04:23
SOURCE_TABLE LAST_DDL_TIME HEIGHT BALANCED 1424 254 303666 02-AUG 23:04:23
SOURCE_TABLE OBJECT_ID NONE 50611 1 303666 02-AUG 23:04:23
SOURCE_TABLE OBJECT_NAME HEIGHT BALANCED 30281 254 303666 02-AUG 23:04:23
SOURCE_TABLE OBJECT_TYPE FREQUENCY 41 41 303666 02-AUG 23:04:23
SOURCE_TABLE OWNER FREQUENCY 26 26 303666 02-AUG 23:04:23
SOURCE_TABLE SECONDARY FREQUENCY 2 2 303666 02-AUG 23:04:23
SOURCE_TABLE STATUS FREQUENCY 1 1 303666 02-AUG 23:04:23
SOURCE_TABLE SUBOBJECT_NAME FREQUENCY 102 102 2466 02-AUG 23:04:23
SOURCE_TABLE TEMPORARY FREQUENCY 2 2 303666 02-AUG 23:04:23
SOURCE_TABLE TIMESTAMP HEIGHT BALANCED 1487 254 303666 02-AUG 23:04:23
TEST_DATA CREATED_COL HEIGHT BALANCED 1380 254 151815 02-AUG 23:04:25
TEST_DATA ID_COL NONE 50605 1 151815 02-AUG 23:04:25
TEST_DATA NAME_COL HEIGHT BALANCED 30275 254 151815 02-AUG 23:04:25
TEST_DATA OWNER_COL FREQUENCY 18 18 151815 02-AUG 23:04:25
TEST_DATA TYPE_COL FREQUENCY 41 41 151815 02-AUG 23:04:25

23 rows selected.

23:04:52 SQL> REM Yes, SIZE SKEWYONLY did generate Histograms on most columns (except ID_COL which is *not* a candidate for Histograms)
23:05:29 SQL>
23:05:29 SQL> spool off


No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016