caveat
reader, January 20, 2004 - 2:02 pm UTC
Loss of data, not likely.
DB corruption , possible.
I have run into database crashes
when using dbms_stats.gather_schema_stats
with 'GATHER AUTO' option under 9.0.1
Of course, it was in a test database
and problem was resolved by upgrading
before rolling out to prod.
I guess that's what Tom meant by "Caution".
January 20, 2004 - 2:19 pm UTC
db corruption? that would be loss of data. dbms_stats will not corrupt data (and has an equal likelyhood of crashing an instance as does any command -- from SELECT to ALTER to ANALYZE)
it is very likely that if dbms_stats caused some sort of deadlock situation -- that analyze would as well (dbms_stats sometimes just calls analyze at the end of the day!)
"like software was wine or something"
Dilip Patel, January 20, 2004 - 5:39 pm UTC
(like software was wine or something) -- Lolzzzzzzzzzzzzz. Still laughing.
Tom - Your way of explaining is excellent.
Diff bet COLUMNS and INDEXED COLUMNS
A reader, January 25, 2004 - 6:11 pm UTC
Tom
Thanks for your response. I have a followup Q on specific to what we are doing.
The script we used haS
FOR I IN (ALL of Application Schemas)
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => I.SCHEMA_NAME ,
estimate_percent=> NULL, --COMPUTE
block_sample => NULL,
method_opt => 'FOR ALL INDEXED COLUMNS',
degree => 2,
granularity => 'DEFAULT',
cascade => TRUE,
) ;
END LOOP
The one Production/DBA was using has
FOR All tables and indexes in Application Schemas,
ANALYZE TABLE COMPUTE STATISTICS ;
ANALYZE INDEX COMPUTE STATISTICS ;
END LOOP ;
One of the main diff I noticed (apart from the usage perspective) is that ANALYZE with no method/opt will compute "FOR ALL COLUMNS"
Q1. Can you see any other differences as far as collected stats are concerned?
Q2. How are stats on NON-INDEXED Columns used by the optimizer?
Thanks again for taking the questions
January 25, 2004 - 6:25 pm UTC
hehehe.
analyze table T compute statistics -- that already got the indexes. that gets table, column and index stats.
They use histograms not only to decide "use index or not" but to decide "how many rows are going to come out of this full scan"...
note the plan differences and different cards/etc in the following:
ops$tkyte@ORA9IR2> create table t
2 as
3 select 999 id, all_objects.* from all_objects;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update t set id = 1 where rownum = 1;
1 row updated.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> analyze table t compute statistics
2 for table
3 for columns id;
Table analyzed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t2
2 as
3 select 1 id from dual
4 union all
5 select 999 from dual;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> analyze table t2 compute statistics;
Table analyzed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t, t2 where t.id = 999 and t.id = t2.id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=46 Card=29558 Bytes=3044474)
1 0 MERGE JOIN (CARTESIAN) (Cost=46 Card=29558 Bytes=3044474)
2 1 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1 Bytes=3)
3 1 BUFFER (SORT) (Cost=44 Card=29558 Bytes=2955800)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=44 Card=29558 Bytes=2955800)
ops$tkyte@ORA9IR2> select * from t, t2 where t.id = 1 and t.id = t2.id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=46 Card=1 Bytes=103)
1 0 MERGE JOIN (CARTESIAN) (Cost=46 Card=1 Bytes=103)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=44 Card=1 Bytes=100)
3 1 BUFFER (SORT) (Cost=2 Card=1 Bytes=3)
4 3 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1 Bytes=3)
ops$tkyte@ORA9IR2> set autotrace off