Once SET_PARAM is used, DBMS_STATS would use the specified values as the "new" defaults. However, with "GATHER AUTO" this does not happen. It merely ignores these values -- thus using a higher level of default values ('AUTO_SAMPLE_SIZE' and 'FOR ALL COLUMNS SIZE AUTO') :
col table_name format a25
col index_name format a29
col column_name format a25
col Sample_Perc format 999.99 hea 'S%age'
alter session set nls_date_format='DD-MON HH24:MI:SS';
select segment_type, segment_name, blocks from user_segments order by 1 desc,2;
SEGMENT_TYPE SEGMENT_NAME BLOCKS
------------------ --------------------------------------------------------------------------------- ----------
TABLE SOURCE_TABLE 9216
TABLE TEST_APPEND 60416
TABLE TEST_BINDVAR_PEEKING 7680
INDEX IND_TEST_APPEND 41344
INDEX TEST_BINDVAR_PEEKING_COB_NDX 3200
select num_rows from user_tables where table_name = 'TEST_APPEND';
NUM_ROWS
----------
4206140
select count(*) from test_append;
COUNT(*)
----------
3755880
select sysdate from dual;
SYSDATE
---------------
20-JUL 23:17:39
select table_name, blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_tables order by 1 ;
TABLE_NAME BLOCKS NUM_ROWS SAMPLE_SIZE S%age LAST_ANALYZED
------------------------- ---------- ---------- ----------- ------- ---------------
SOURCE_TABLE 9172 652320 65232 10.00 20-JUL 23:09:41
TEST_APPEND 60250 4206140 420614 10.00 20-JUL 23:09:53
TEST_BINDVAR_PEEKING 7600 1330390 133039 10.00 20-JUL 23:09:59
select table_name, column_name, count(*) from user_tab_histograms group by table_name, column_name having count(*) > 2 order by 1,2;
select table_name, column_name, num_distinct, num_nulls, sample_size, density, histogram from user_tab_col_statistics order by 1,2;
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS SAMPLE_SIZE DENSITY HISTOGRAM
------------------------- ------------------------- ------------ ---------- ----------- ---------- ---------------
SOURCE_TABLE CREATED 1330 0 65232 .00075188 NONE
SOURCE_TABLE DATA_OBJECT_ID 3924 599830 5249 .000254842 NONE
SOURCE_TABLE GENERATED 2 0 65232 .5 NONE
SOURCE_TABLE LAST_DDL_TIME 1374 0 65232 .000727802 NONE
SOURCE_TABLE OBJECT_ID 50422 0 65232 .000019833 NONE
SOURCE_TABLE OBJECT_NAME 28918 0 65232 .000034581 NONE
SOURCE_TABLE OBJECT_TYPE 38 0 65232 .026315789 NONE
SOURCE_TABLE OWNER 26 0 65232 .038461538 NONE
SOURCE_TABLE SECONDARY 2 0 65232 .5 NONE
SOURCE_TABLE STATUS 1 0 65232 1 NONE
SOURCE_TABLE SUBOBJECT_NAME 98 646920 540 .010204082 NONE
SOURCE_TABLE TEMPORARY 2 0 65232 .5 NONE
SOURCE_TABLE TIMESTAMP 1420 0 65232 .000704225 NONE
TEST_APPEND CREATED 1378 0 420614 .000725689 NONE
TEST_APPEND DATA_OBJECT_ID 4017 3864130 34201 .000248942 NONE
TEST_APPEND GENERATED 2 0 420614 .5 NONE
TEST_APPEND LAST_DDL_TIME 1425 0 420614 .000701754 NONE
TEST_APPEND OBJECT_ID 50442 0 420614 .000019825 NONE
TEST_APPEND OBJECT_NAME 30247 0 420614 .000033061 NONE
TEST_APPEND OBJECT_TYPE 41 0 420614 .024390244 NONE
TEST_APPEND OWNER 27 0 420614 .037037037 NONE
TEST_APPEND SECONDARY 2 0 420614 .5 NONE
TEST_APPEND STATUS 1 0 420614 1 NONE
TEST_APPEND SUBOBJECT_NAME 102 4171170 3497 .009803922 NONE
TEST_APPEND TEMPORARY 2 0 420614 .5 NONE
TEST_APPEND TIMESTAMP 1488 0 420614 .000672043 NONE
TEST_BINDVAR_PEEKING CITY_OF_BIRTH 10 0 133039 .1 NONE
TEST_BINDVAR_PEEKING COUNTRY_OF_BIRTH 1 0 133039 1 NONE
TEST_BINDVAR_PEEKING DATE_OF_BIRTH 10 0 133039 .1 NONE
TEST_BINDVAR_PEEKING MONTH_NUMBER 10 0 133039 .1 NONE
TEST_BINDVAR_PEEKING MONTH_OF_BIRTH 10 0 133039 .1 NONE
select table_name, index_name, leaf_blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_indexes order by 1,2;
TABLE_NAME INDEX_NAME LEAF_BLOCKS NUM_ROWS SAMPLE_SIZE S%age LAST_ANALYZED
------------------------- ----------------------------- ----------- ---------- ----------- ------- ---------------
TEST_APPEND IND_TEST_APPEND 37570 3926410 392641 10.00 20-JUL 23:09:57
TEST_BINDVAR_PEEKING TEST_BINDVAR_PEEKING_COB_NDX 2612 1337226 482304 36.07 20-JUL 23:10:00
select sysdate from dual;
SYSDATE
---------------
20-JUL 23:17:39
set feedback on
exec dbms_stats.set_param(pname=>'ESTIMATE_PERCENT',pval=>'25');
PL/SQL procedure successfully completed.
exec dbms_stats.set_param(pname=>'METHOD_OPT',pval=>'FOR ALL COLUMNS SIZE 50');
PL/SQL procedure successfully completed.
exec dbms_stats.set_param(pname=>'CASCADE',pval=>'FALSE');
PL/SQL procedure successfully completed.
set feedback off
REM #######################Gathering Schema Stats #################
exec dbms_stats.gather_schema_stats(ownname=>'HEMANT',options=>'GATHER AUTO');
select table_name, blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_tables order by 1 ;
TABLE_NAME BLOCKS NUM_ROWS SAMPLE_SIZE S%age LAST_ANALYZED
------------------------- ---------- ---------- ----------- ------- ---------------
SOURCE_TABLE 9172 461575 461575 100.00 20-JUL 23:17:53
TEST_APPEND 60250 3755880 3755880 100.00 20-JUL 23:18:08
TEST_BINDVAR_PEEKING 7600 1330390 133039 10.00 20-JUL 23:09:59
select table_name, column_name, count(*) from user_tab_histograms group by table_name, column_name having count(*) > 2 order by 1,2;
TABLE_NAME COLUMN_NAME COUNT(*)
------------------------- ------------------------- ----------
SOURCE_TABLE CREATED 236
SOURCE_TABLE DATA_OBJECT_ID 255
SOURCE_TABLE LAST_DDL_TIME 237
SOURCE_TABLE OBJECT_NAME 255
SOURCE_TABLE OBJECT_TYPE 32
SOURCE_TABLE OWNER 23
SOURCE_TABLE SUBOBJECT_NAME 100
SOURCE_TABLE TIMESTAMP 237
TEST_APPEND CREATED 246
TEST_APPEND DATA_OBJECT_ID 255
TEST_APPEND LAST_DDL_TIME 242
TEST_APPEND OBJECT_NAME 255
TEST_APPEND OBJECT_TYPE 27
TEST_APPEND OWNER 21
TEST_APPEND SUBOBJECT_NAME 102
TEST_APPEND TIMESTAMP 241
select table_name, column_name, num_distinct, num_nulls, sample_size, density, histogram from user_tab_col_statistics order by 1,2;
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS SAMPLE_SIZE DENSITY HISTOGRAM
------------------------- ------------------------- ------------ ---------- ----------- ---------- ---------------
SOURCE_TABLE CREATED 874 0 4958 .002352941 HEIGHT BALANCED
SOURCE_TABLE DATA_OBJECT_ID 2938 422838 38737 .000352885 HEIGHT BALANCED
SOURCE_TABLE GENERATED 2 0 4958 1.0853E-06 FREQUENCY
SOURCE_TABLE LAST_DDL_TIME 886 0 4958 .002358491 HEIGHT BALANCED
SOURCE_TABLE OBJECT_ID 35508 0 461575 .000028163 NONE
SOURCE_TABLE OBJECT_NAME 26857 0 461575 .000044659 HEIGHT BALANCED
SOURCE_TABLE OBJECT_TYPE 32 0 4958 1.0853E-06 FREQUENCY
SOURCE_TABLE OWNER 23 0 4958 1.0853E-06 FREQUENCY
SOURCE_TABLE SECONDARY 1 0 4958 1.0853E-06 FREQUENCY
SOURCE_TABLE STATUS 1 0 4958 1.0853E-06 FREQUENCY
SOURCE_TABLE SUBOBJECT_NAME 100 457597 3978 .000125691 FREQUENCY
SOURCE_TABLE TEMPORARY 2 0 4958 1.0853E-06 FREQUENCY
SOURCE_TABLE TIMESTAMP 890 0 4958 .002386635 HEIGHT BALANCED
TEST_APPEND CREATED 942 0 6237 .002427184 HEIGHT BALANCED
TEST_APPEND DATA_OBJECT_ID 3592 3451090 23675 .000346741 HEIGHT BALANCED
TEST_APPEND GENERATED 2 0 6237 1.3366E-07 FREQUENCY
TEST_APPEND LAST_DDL_TIME 941 0 6237 .002347418 HEIGHT BALANCED
TEST_APPEND OBJECT_ID 44867 0 288110 .000022288 NONE
TEST_APPEND OBJECT_NAME 29033 0 288110 .000044779 HEIGHT BALANCED
TEST_APPEND OBJECT_TYPE 27 0 6237 1.3366E-07 FREQUENCY
TEST_APPEND OWNER 21 0 6237 1.3366E-07 FREQUENCY
TEST_APPEND SECONDARY 1 0 6237 1.3366E-07 FREQUENCY
TEST_APPEND STATUS 1 0 6237 1.3366E-07 FREQUENCY
TEST_APPEND SUBOBJECT_NAME 102 3725026 30854 .000016205 FREQUENCY
TEST_APPEND TEMPORARY 2 0 6237 1.3366E-07 FREQUENCY
TEST_APPEND TIMESTAMP 953 0 6237 .002380952 HEIGHT BALANCED
TEST_BINDVAR_PEEKING CITY_OF_BIRTH 10 0 133039 .1 NONE
TEST_BINDVAR_PEEKING COUNTRY_OF_BIRTH 1 0 133039 1 NONE
TEST_BINDVAR_PEEKING DATE_OF_BIRTH 10 0 133039 .1 NONE
TEST_BINDVAR_PEEKING MONTH_NUMBER 10 0 133039 .1 NONE
TEST_BINDVAR_PEEKING MONTH_OF_BIRTH 10 0 133039 .1 NONE
select table_name, index_name, leaf_blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_indexes order by 1,2;
TABLE_NAME INDEX_NAME LEAF_BLOCKS NUM_ROWS SAMPLE_SIZE S%age LAST_ANALYZED
------------------------- ----------------------------- ----------- ---------- ----------- ------- ---------------
TEST_APPEND IND_TEST_APPEND 38328 3686411 392641 10.65 20-JUL 23:18:44
TEST_BINDVAR_PEEKING TEST_BINDVAR_PEEKING_COB_NDX 2612 1337226 482304 36.07 20-JUL 23:10:00
select sysdate from dual;
SYSDATE
---------------
20-JUL 23:18:44
REM #######################Gathering Schema Stats WITHOUT GATHER AUTO
exec dbms_stats.gather_schema_stats(ownname=>'HEMANT',estimate_percent=>10,method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);
select table_name, blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_tables order by 1 ;
TABLE_NAME BLOCKS NUM_ROWS SAMPLE_SIZE S%age LAST_ANALYZED
------------------------- ---------- ---------- ----------- ------- ---------------
SOURCE_TABLE 9172 458810 45881 10.00 20-JUL 23:18:46
TEST_APPEND 60250 3760080 376008 10.00 20-JUL 23:18:58
TEST_BINDVAR_PEEKING 7600 1331200 133120 10.00 20-JUL 23:19:03
select table_name, column_name, count(*) from user_tab_histograms group by table_name, column_name having count(*) > 2 order by 1,2;
select table_name, column_name, num_distinct, num_nulls, sample_size, density, histogram from user_tab_col_statistics order by 1,2;
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS SAMPLE_SIZE DENSITY HISTOGRAM
------------------------- ------------------------- ------------ ---------- ----------- ---------- ---------------
SOURCE_TABLE CREATED 1274 0 45881 .000784929 NONE
SOURCE_TABLE DATA_OBJECT_ID 2921 419190 3962 .000342349 NONE
SOURCE_TABLE GENERATED 2 0 45881 .5 NONE
SOURCE_TABLE LAST_DDL_TIME 1303 0 45881 .00076746 NONE
SOURCE_TABLE OBJECT_ID 35520 0 45881 .000028153 NONE
SOURCE_TABLE OBJECT_NAME 25298 0 45881 .000039529 NONE
SOURCE_TABLE OBJECT_TYPE 39 0 45881 .025641026 NONE
SOURCE_TABLE OWNER 26 0 45881 .038461538 NONE
SOURCE_TABLE SECONDARY 2 0 45881 .5 NONE
SOURCE_TABLE STATUS 1 0 45881 1 NONE
SOURCE_TABLE SUBOBJECT_NAME 96 454710 410 .010416667 NONE
SOURCE_TABLE TEMPORARY 2 0 45881 .5 NONE
SOURCE_TABLE TIMESTAMP 1348 0 45881 .00074184 NONE
TEST_APPEND CREATED 1366 0 376008 .000732064 NONE
TEST_APPEND DATA_OBJECT_ID 3603 3456130 30395 .000277546 NONE
TEST_APPEND GENERATED 2 0 376008 .5 NONE
TEST_APPEND LAST_DDL_TIME 1404 0 376008 .000712251 NONE
TEST_APPEND OBJECT_ID 44979 0 376008 .000022233 NONE
TEST_APPEND OBJECT_NAME 29078 0 376008 .00003439 NONE
TEST_APPEND OBJECT_TYPE 41 0 376008 .024390244 NONE
TEST_APPEND OWNER 27 0 376008 .037037037 NONE
TEST_APPEND SECONDARY 2 0 376008 .5 NONE
TEST_APPEND STATUS 1 0 376008 1 NONE
TEST_APPEND SUBOBJECT_NAME 103 3730460 2962 .009708738 NONE
TEST_APPEND TEMPORARY 2 0 376008 .5 NONE
TEST_APPEND TIMESTAMP 1468 0 376008 .000681199 NONE
TEST_BINDVAR_PEEKING CITY_OF_BIRTH 10 0 133120 .1 NONE
TEST_BINDVAR_PEEKING COUNTRY_OF_BIRTH 1 0 133120 1 NONE
TEST_BINDVAR_PEEKING DATE_OF_BIRTH 10 0 133120 .1 NONE
TEST_BINDVAR_PEEKING MONTH_NUMBER 10 0 133120 .1 NONE
TEST_BINDVAR_PEEKING MONTH_OF_BIRTH 10 0 133120 .1 NONE
select table_name, index_name, leaf_blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_indexes order by 1,2;
TABLE_NAME INDEX_NAME LEAF_BLOCKS NUM_ROWS SAMPLE_SIZE S%age LAST_ANALYZED
------------------------- ----------------------------- ----------- ---------- ----------- ------- ---------------
TEST_APPEND IND_TEST_APPEND 40670 3908800 390880 10.00 20-JUL 23:19:02
TEST_BINDVAR_PEEKING TEST_BINDVAR_PEEKING_COB_NDX 2653 1358519 489984 36.07 20-JUL 23:19:05
select sysdate from dual;
SYSDATE
---------------
20-JUL 23:19:05
rem exec dbms_stats.restore_schema_stats(ownname=>'HEMANT',AS_OF_TIMESTAMP=>'20-JUL-2008 11:15:00AM');
exec dbms_stats.reset_param_defaults;
spool off
10 comments:
Is it recommended to anaylze table after reorganization such as alter table move ? ifso , why ?
Ankush,
Not necessary if there is no significant change in the data.
Say your table had 1million rows two days ago, when Statistics were last updated.
Yesterday you deleted 400,000 rows and have now rebuilt the table with an ALTER TABLE move.
Now, if you do not update statistics, statistics still reflect 1million rows. In such a case it makes sense to update statistics.
Hemant
Hi
Where does bitmap for LMT
and freelist for DMT stored?
Are they stored in the first extent of the tablespace
or first extent of each segment?
Karon,
DMT doesn't use BitMaps. It uses rows stored in the data dictionary -- specifically in the UET$ and FET$ tables.
LMT allocates a chunk of 64KB in the header of each datafile to store the BitMaps.
Hemant
So is the freelist stored in UET$ and FET$ tables ?
DON'T confuse things.
Tablespace Management (DMT with UET$ and FET$ versus LMT with BitMapped Extents) relates to *EXTENTS IN THE TABLESPACE*.
Segment Space Management (MSSM with FreeLists in the Segment Header versus ASSM with BitMapped Blocks) relates to *FREE BLOCKS IN THE SEGMENT*.
Two different things. Although you cannot have ASSM in DMT. You can have either of ASSM or MSSM in LMT.
Hemant K Chitale
so, where is the freelist stored?
Karon,
Didn't I say "MSSM with FreeLists in the Segment Header" ?
The FreeList is in the Segment Header -- whether the Segment is a Table or Index or a Partition. It can be visualised as a linked list of Block IDs.
In ASSM, it is a BitMap.
See the section titled "Segment Space Management in Locally Managed Tablespaces" in Chapter 3 "Tablespaces, Datafiles, and Control Files" of the Oracle 10.2 Concepts Manual.
Hemant K Chitale
Even if you use bind variables, does the optimizer always generate execution plan for the sql statements?
I mean, the staticstic may change suddenly, hence execution plan. Optimezer should always generate execution plans.
Anonymous,
Oracle has to reparse a statement for reasons
a) if the statement and it's plan have been flushed out of the shared pool (e.g. because it hasn't been used for some time and the space had to be allocated to another SQL)
b) it has been invalidated -- any DDL (e.g. an ALTER statement) or a Gather_Stats on any of the objects being used in the statement would cause invalidatation.
Therefore, updated stats would cause the statement to be invalidated and a new plan to be generated by the optimizer. Note that 10g's GATHER_STATS has a default NO_INVALIDATE set to TRUE, which means that Oracle doesn't immediately invalidate all cursors referencing an object, but does the invalidations gracefully -- over a period of time.
So you might find that if you have N (say 5 or 10 or 15) SQLs referencing a table and you run a GATHER_STATS on the table, only some of the SQLs are invalidated immediately. The others will get invalidated gradually.
If the statements are very frequently used, the first set of statements would get re-optimized for a new execution plan but the second wouldn't get re-optimized for a new plan till after some time.
Hemant K Chitale
Post a Comment