Re: analyze table versus DBMS_STATS.GATHER_TABLE_STATS(...)
- From: J.Velikanovs@xxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 6 Jul 2004 11:53:51 +0300
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
---------------------------------------- Waited ----------
------------
direct path write 4 0.00 0.00
direct path read 14 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
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
---------------------------------------- Waited ----------
------------
direct path write 4 0.00 0.00
direct path read 9 0.00 0.00
********************************************************************************
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@xxxxxxxxxxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
06.07.2004 11:09
Please respond to oracle-l
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
