| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_STATS vs. manual ANALYZE ... desupported?
"Domenic" <domenicg_at_hotmail.com> wrote in message
news:c7e08a19.0409161253.8235233_at_posting.google.com...
> Does anyone know *why* Oracle is saying we shouldn't do manual
> analyzing via our own scripts with or without dynamic PL/SQL loops?
Because it is more sensible to have one standard flexible way of collecting stats?
> Instead, they're saying if we don't use dbms_stats, we're going
> straight to hell.
I'd like to see the reference.
> I find using my own scripts allows me to create
> histograms where needed, to analyze more active tables and partitions
> more frequently, etc.
I don't see any reason why you can't write scripts that use dbms_stats yourself? We do. All of your requirements above can be met by dbms_stats.
> Instead, in the 10g docs it says if we don't switch over to
> dbms_stats, the optimizer won't *see* our stats collected directly via
> ANALYZE in a future release.
>
> What does that mean? I thought dbms_stats just generated ANALYZEs in
> a loop behind the scenes. Am I missing something? I thought the end
> result was the same and that it could not be determined which method
> was used.
In 8i (might not be true of 817) your understanding is correct. In 9 and above it isn't.
SQL> create table t2
2 as select * from all_objects
3 ;
Table created.
SQL> alter session set sql_trace=true;
Session altered.
SQL> exec dbms_stats.gather_table_stats(USER,'T2',CASCADE => TRUE);
PL/SQL procedure successfully completed.
SQL> alter session set sql_trace=FALSE;
Session altered.
Then look at the trace file, you'll see a whole bunch of plain old sql that is used to gather the stats. Stuff like
PARSING IN CURSOR #57 len=2257 dep=1 uid=68 oct=3 lid=68 tim=221966981316
hv=3081725647 ad='67a23b1c'
select /*+ cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)
no_monitoring */ count(*),count("OWNER"),count(distinct
"OWNER"),sum(vsize("OWNER")),substrb(dump(min("OWNER"),16,0,32),1,120),substrb(dump(max("OWNER"),16,0,32),1,120),count("OBJECT_NAME"),count(distinct
"OBJECT_NAME"),sum(vsize("OBJECT_NAME")),substrb(dump(min("OBJECT_NAME"),16,0,32),1,120),substrb(dump(max("OBJECT_NAME"),16,0,32),1,120),count("SUBOBJECT_NAME"),count(distinct
"SUBOBJECT_NAME"),sum(vsize("SUBOBJECT_NAME")),substrb(dump(min("SUBOBJECT_NAME"),16,0,32),1,120),substrb(dump(max("SUBOBJECT_NAME"),16,0,32),1,120),count("OBJECT_ID"),count(distinct
"OBJECT_ID"),sum(vsize("OBJECT_ID")),substrb(dump(min("OBJECT_ID"),16,0,32),1,120),substrb(dump(max("OBJECT_ID"),16,0,32),1,120),count("DATA_OBJECT_ID"),count(distinct
"DATA_OBJECT_ID"),sum(vsize("DATA_OBJECT_ID")),substrb(dump(min("DATA_OBJECT_ID"),16,0,32),1,120),substrb(dump(max("DATA_OBJECT_ID"),16,0,32),1,120),count("OBJECT_TYPE"),count(distinct
"OBJECT_TYPE"),sum(vsize("OBJECT_TYPE")),substrb(dump(min("OBJECT_TYPE"),16,0,32),1,120),substrb(dump(max("OBJECT_TYPE"),16,0,32),1,120),count("CREATED"),count(distinct
"CREATED"),substrb(dump(min("CREATED"),16,0,32),1,120),substrb(dump(max("CREATED"),16,0,32),1,120),count("LAST_DDL_TIME"),count(distinct
"LAST_DDL_TIME"),substrb(dump(min("LAST_DDL_TIME"),16,0,32),1,120),substrb(dump(max("LAST_DDL_TIME"),16,0,32),1,120),count("TIMESTAMP"),count(distinct
"TIMESTAMP"),sum(vsize("TIMESTAMP")),substrb(dump(min("TIMESTAMP"),16,0,32),1,120),substrb(dump(max("TIMESTAMP"),16,0,32),1,120),count("STATUS"),count(distinct
"STATUS"),sum(vsize("STATUS")),substrb(dump(min("STATUS"),16,0,32),1,120),substrb(dump(max("STATUS"),16,0,32),1,120),count("TEMPORARY"),count(distinct
"TEMPORARY"),sum(vsize("TEMPORARY")),substrb(dump(min("TEMPORARY"),16,0,32),1,120),substrb(dump(max("TEMPORARY"),16,0,32),1,120),count("GENERATED"),count(distinct
"GENERATED"),sum(vsize("GENERATED")),substrb(dump(min("GENERATED"),16,0,32),1,120),substrb(dump(max("GENERATED"),16,0,32),1,120),count("SECONDARY"),count(distinct
"SECONDARY"),sum(vsize("SECONDARY")),substrb(dump(min("SECONDARY"),16,0,32),1,120),substrb(dump(max("SECONDARY"),16,0,32),1,120)
from "NIALL"."T2" sample ( 18.7074829932) t
END OF STMT
etc etc. (the above is from 10 but the move started in (at least) 9). As to
what it means - move to DBMS_STATS.
DBMS_STATS is able to gather both more and more accurate information than analyse (there are exceptions but these are generally minor). In principle giving the CBO better information to use is A Good Thing.
-- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.comReceived on Thu Sep 16 2004 - 16:44:45 CDT
 |
 |