SQL & PL/SQL

Hi all,

I am just trying to understand the difference between Analyze ... command DBMS_STATS.GATHER_TABLE_STATS. I undetstand from the documents, that DBMS_STATS will gather more stats than analyze. Also understand that it will collects better information like histograms than analyze.

but when I wanted to check with some simeple tests, I found that DBMS_STATS is not gathering histograms. I am looking at the table all_tab_histograms for histogrmas. May be I am wrong..

Created a new tables with some 100 thousand records...

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  dbms_stats.gather_table_stats(ownname => 'DM', tabname => 'PDM' ,
  3  estimate_percent => 100,method_opt => 'for all indexed columns size auto',
  4  degree => 4 ,cascade => true );
  5* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM all_tab_histograms WHERE table_name ='PDM';

no rows selected

SQL> ANALYZE TABLE pdm ESTIMATE STATISTICS;

Table analyzed.


  1* SELECT count(1) FROM all_tab_histograms WHERE table_name ='PDM'
SQL> /

  COUNT(1)
----------
        10

* When I re-ran the dbms_stats, It is not clearing/overwriting the statics gather by analyze command. But still keeps the data in _tabl_histograms and overwirtes the data in _tables.*

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  dbms_stats.gather_table_stats(ownname => 'DM', tabname => 'PDM' ,
  3  estimate_percent => 100,method_opt => 'for all indexed columns size auto',
  4  degree => 4 ,cascade => true );
  5* end;

  1* SELECT count(1) FROM all_tab_histograms WHERE table_name ='PDM'
SQL> /

  COUNT(1)
----------
        10

Also kindly let me know where I can learn how 'Rows , Bytes ,TempSpc, Cost ,Pstart, Pstop , TQ |IN-OUT' information in plan are arrived.

Thanks in Advance,
Jaggyam