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:
Post a Comment