There was a question on forums : "Is there a way to ONLY generate histograms? not touching the rest of the statistics that are not histograms".
If you use DBMS_STATS.GATHER_TABLE_STATS( ... method_opt='FOR COLUMNS x SIZE n'); Table Stats are also gathered and updated. Can this be avoided ?
Here's a test case :
If you use DBMS_STATS.GATHER_TABLE_STATS( ... method_opt='FOR COLUMNS x SIZE n'); Table Stats are also gathered and updated. Can this be avoided ?
Here's a test case :
00:57:48 SQL> create table ST_TEST as select object_id as id, owner as col_1, object_name as col_2 00:57:56 2 from dba_objects; Table created. 00:58:05 SQL> exec dbms_stats.gather_table_stats('','ST_TEST'); PL/SQL procedure successfully completed. 00:58:13 SQL> select num_rows, last_analyzed from user_tables where table_name = 'ST_TEST'; NUM_ROWS LAST_ANALYZED ---------- ------------------ 76713 26-APR-12 00:58:13 00:58:24 SQL> delete ST_TEST where col_2 like 'DBA_%'; 1650 rows deleted. 00:58:48 SQL> commit; Commit complete. 00:58:52 SQL> !sleep 30 00:59:30 SQL> exec dbms_stats.gather_table_stats('','ST_TEST',method_opt=>'FOR COLUMNS COL_1 SIZE 250'); PL/SQL procedure successfully completed. 01:00:12 SQL> select num_rows, last_analyzed from user_tables where table_name = 'ST_TEST'; NUM_ROWS LAST_ANALYZED ---------- ------------------ 75063 26-APR-12 01:00:12 01:00:20 SQL>The DBMS_STATS call with METHOD_OPT also updated Table level statistics. Can you avoid having to gather Table level statistics ? What about column statistics and histograms on other columns in the table ? Are they recomputed and updated as well ? Can you avoid the CPU overheads for these actions ?
.
.
.
No comments:
Post a Comment