Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: analyze table versus DBMS_STATS.GATHER_TABLE_STATS(...)

Re: analyze table versus DBMS_STATS.GATHER_TABLE_STATS(...)

From: <J.Velikanovs_at_alise.lv>
Date: Tue, 6 Jul 2004 11:56:30 +0300
Message-ID: <OF704BA838.8399BADF-ONC2256EC9.003111FE-C2256EC9.0031CEAF@alise.lv> 





I suspect DBMS_STATS doesn’t compute statistics for 100% ->> by deffault 
<<-, it use some sampling.



Jurijs


9268222




http://otn.oracle.com/ocm/jvelikanovs.html








J.Velikanovs_at_alise.lv


Sent by: oracle-l-bounce_at_freelists.org


06.07.2004 11:53


Please respond to oracle-l
 

        To:     oracle-l_at_freelists.org
        cc: 
        Subject:        Re: analyze table versus 


DBMS_STATS.GATHER_TABLE_STATS(...)



Since 9i, I believe, Oracle recommends to use DBMS_STATS.
DBMS_STATS is able to compute more information then analyse.
Question which one compute more accurate.
I suspect DBMS_STATS doesn’t compute statistics for 100%, it use some 
sampling.


I haven’t chance to investigate this issue in more details. Many be 
experts in lists can comment more.



Below Stupid test, but good input.



TEST Text




WIN2000 9.2.0.4


SYS:jozh> drop table testa;
SYS:jozh> create table testa (n number, v varchar2(1000));
SYS:jozh> begin for f in 1..100000 loop
SYS:jozh> insert into testa values (f, 'aaaaaaaaaaaaaaaaaaaaaa');
SYS:jozh> end loop; end;
SYS:jozh> /


SYS:jozh> set timing on
SYS:jozh> begin dbms_support.start_trace(true,true); end;


SYS:jozh> /


SYS:jozh> analyze table testa compute statistics;



Table analyzed.



Elapsed: 00:00:01.82


SYS:jozh> begin dbms_support.stop_trace;


SYS:jozh> begin dbms_support.start_trace(true,true); end;
SYS:jozh> /
SYS:jozh> begin dbms_stats.gather_table_stats('sys','testa'); end;
SYS:jozh> /





PL/SQL procedure successfully completed.



Elapsed: 00:00:00.91



TKPROF output




analyze table testa compute statistics




call     count       cpu    elapsed       disk      query    current  rows
------- ------  -------- ---------- ---------- ---------- ---------- 



Parse        1      0.00       0.00          0          0          0     0
Execute      1      1.73       1.74        148        683          6     0
Fetch        0      0.00       0.00          0          0          0     0


------- ------  -------- ---------- ---------- ---------- ---------- 




total        2      1.73       1.74        148        683          6     0



Misses in library cache during parse: 1


Optimizer mode: CHOOSE


Parsing user id: SYS



Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total 


Waited




select  /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring*/ 
count(*)


  ,count("N"),count(distinct 


"N"),sum(vsize("N")),substrb(dump(min("N"),16,0,
 


32),1,120),substrb(dump(max("N"),16,0,32),1,120),count("V"),count(distinct 




 "V"),sum(vsize("V")),substrb(dump(min(substrb("V",1,32)),16,0,32),1,120),
  substrb(dump(max(substrb("V",1,32)),16,0,32),1,120) 
from


 "SYS"."TESTA" t 




call     count       cpu    elapsed       disk      query    current  rows
------- ------  -------- ---------- ---------- ---------- ---------- 



Parse        1      0.00       0.00          0          0          0     0
Execute      2      0.00       0.00          0          0          0     0
Fetch        2      0.82       0.91        149        684          4     1


------- ------  -------- ---------- ---------- ---------- ---------- 




total        5      0.82       0.91        149        684          4     1



Misses in library cache during parse: 1


Optimizer mode: CHOOSE


Parsing user id: SYS   (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------
      1  SORT GROUP BY (cr=684 r=149 w=149 time=916820 us)


 100000   TABLE ACCESS FULL TESTA (cr=684 r=0 w=0 time=184877 us)




Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total 


Waited




BTW you can run SQL for analyze by your self ;) coreect results and load 
into DD statistics using DBMS_STATS.



select  /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring*/ 
count(*)


  ,count("N"),count(distinct 


"N"),sum(vsize("N")),substrb(dump(min("N"),16,0,
 


32),1,120),substrb(dump(max("N"),16,0,32),1,120),count("V"),count(distinct 




 "V"),sum(vsize("V")),substrb(dump(min(substrb("V",1,32)),16,0,32),1,120),
  substrb(dump(max(substrb("V",1,32)),16,0,32),1,120) 
from


 "SYS"."TESTA" t;




Jurijs


9268222




http://otn.oracle.com/ocm/jvelikanovs.html








"Raphael A. Bauer" <raphael.bauer_at_informatik.hu-berlin.de>
Sent by: oracle-l-bounce_at_freelists.org


06.07.2004 11:09


Please respond to oracle-l
 

        To:     oracle-l_at_freelists.org
        cc: 
        Subject:        analyze table versus 


DBMS_STATS.GATHER_TABLE_STATS(...)



Hi Folks,



I got a really interesting topic about 9i.
I encountered a big difference in the performance of my sql commands 
wether I use analyze table (..) or DBMS_STATS.GATHER_TABLE_STATS(...) 
(both "full" analyzes). DBMS_STATS is in my case about 40secs faster 
than analyze... The plan of both sql queries stayed the same, only the 
costs differed a bit. My Oracle Books say that DBMS_STAT and analyze.. 
are qute the same. But that's not reality...
A - I forgot - my test schema is only one simple table. Nothing to do 
with partitions or more complex topics...



I don't want to show you my exact tables and queries. They are not so 
important. More important is: Where are those 40secs hidden - and what 
is the real difference of DBMS_STATS.GATHER_TABLE_STATS and analyze.



Thanks a lot!



Raphael




Please see the official ORACLE-L FAQ: http://www.orafaq.com




To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------





----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------





----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------



Received on Tue Jul 06 2004 - 04:00:49 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US