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