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