AskTom Home
   ViewForPrinting
 
   Home
   Recent   Archives   Advanced Search   FAQ by Category   Most Popular   Hot Articles   Your Questions   Question Details ]   RSS Feed   
 
brenda -- Thanks for the question regarding "what kind of analyze is best", version 8.1.7.3.0
originally submitted on 7-Oct-2002 14:29 Eastern US time, last updated 2-Jul-2004 12:30 You Asked (Jump to Tom's latest followup)
Hi Tom:
we are having some issues with performance on our 500GB database environment. we 
do analyze tables on a daily basis every 4 hours since data loading on different 
time, not sure when the data will finish loading. I have seen different kinds of 
'analyze':

a. analyze table xxxx estimate statistics sample 10 percent;  

b. analyze table xxxx estimate statistics sample 10 percent for table for all 
indexes for all indexed columns;

c. analyze table xxxx validate structure;  

d. analyze table t compute statistics
   for table
   for all indexes
   for all indexed columns;

e. analyze table xxxx compute statistics for table


could you tell me 5 analyze differences? and what is the best for DW when we 
have daily loading going on during the day and analyze job is running as well?
 
and we said...
I don't like any of them.

DBMS_STATS is what you want to use.  If you 

ALTER TABLE <tname> MONITORING

then you can gather stats JUST on tables that need it (we'll keep track for you 
-- and only analyze tables in which about 10% of the data has changed).  That 
way the analyzes will go much faster.

Me, I'm sort of a fan of a method that would analyze the table, the indexes and 
in most cases -- the indexed columns (although for unique or data with fairly 
uniform distributions, you could be more specific and just analyze the indexes 
that are on skewed data).

Here is a synopsis of the differences for the above (but, don't use them! use 
dbms_stats to do what they do.  Which is best????  well, compute is prefered but 
for really big things, not feasible.  I said what i like -- table, indexes and 
the proper set of indexed columns)


[email protected]> create table t as select * from all_objects;

Table created.

[email protected]> create index t_idx on t(object_id);

Index created.

[email protected]>
[email protected]> analyze table t estimate statistics sample 10 
percent;

Table analyzed.

[email protected]>
[email protected]> select t.num_rows, i.num_rows, c.cnt
  2    from (select num_rows from user_tables where table_name = 'T') t,
  3         (select num_rows from user_indexes where table_name = 'T' ) i,
  4         (select count(distinct column_name) cnt from user_tab_histograms 
where table_name = 'T' ) c
  5  /

  NUM_ROWS   NUM_ROWS        CNT
---------- ---------- ----------
     23634      23634         12

that shows we got the table analyzed, the index(es) analyzed and histograms 
for all of the columns in the table that had at least one not null value 
(expensive!)


[email protected]> analyze table t delete statistics;

Table analyzed.

[email protected]>
[email protected]> analyze table t estimate statistics sample 10 
percent for table for all indexes for all indexed columns;

Table analyzed.

[email protected]> select t.num_rows, i.num_rows, c.cnt
  2    from (select num_rows from user_tables where table_name = 'T') t,
  3         (select num_rows from user_indexes where table_name = 'T' ) i,
  4         (select count(distinct column_name) cnt from user_tab_histograms 
where table_name = 'T' ) c
  5  /

  NUM_ROWS   NUM_ROWS        CNT
---------- ---------- ----------
     23634      23634          1

Here we got the table, the indexes and a histogram on ONE column -- the one we 
have an index on (not particularly relevant in this case as object id is unique 
and hence distributed nicely, not skewed)



[email protected]> analyze table t delete statistics;

Table analyzed.

[email protected]>
[email protected]>
[email protected]> analyze table t validate structure;

Table analyzed.

[email protected]> select t.num_rows, i.num_rows, c.cnt
  2    from (select num_rows from user_tables where table_name = 'T') t,
  3         (select num_rows from user_indexes where table_name = 'T' ) i,
  4         (select count(distinct column_name) cnt from user_tab_histograms 
where table_name = 'T' ) c
  5  /

  NUM_ROWS   NUM_ROWS        CNT
---------- ---------- ----------
                               0

No stats, just a diagnostic tool -- not for statistics gathering!

[email protected]> analyze table t delete statistics;

Table analyzed.

[email protected]>
[email protected]>
[email protected]>
[email protected]> analyze table t compute statistics for table 
for all indexes for all indexed columns;

Table analyzed.

[email protected]> select t.num_rows, i.num_rows, c.cnt
  2    from (select num_rows from user_tables where table_name = 'T') t,
  3         (select num_rows from user_indexes where table_name = 'T' ) i,
  4         (select count(distinct column_name) cnt from user_tab_histograms 
where table_name = 'T' ) c
  5  /

  NUM_ROWS   NUM_ROWS        CNT
---------- ---------- ----------
     23634      23634          1

[email protected]> analyze table t delete statistics;

Table analyzed.

same as the estimate but does the full table -- if I had a bigger set, the 
num_rows here might be more accurate using compute then estimate


[email protected]>
[email protected]>
[email protected]> analyze table t compute statistics;

Table analyzed.

[email protected]> select t.num_rows, i.num_rows, c.cnt
  2    from (select num_rows from user_tables where table_name = 'T') t,
  3         (select num_rows from user_indexes where table_name = 'T' ) i,
  4         (select count(distinct column_name) cnt from user_tab_histograms 
where table_name = 'T' ) c
  5  /

  NUM_ROWS   NUM_ROWS        CNT
---------- ---------- ----------
     23634      23634         12

same as the corresponding estimate but does the entire table

 
  Reviews    
What do you think of this analyze script?  October 08, 2002
Reviewer:  Alan Rosenthal  from CA USA

Hi Tom,

I use the following sqlplus script to analyze my db's.  I have a good window of 
time when I can run this so I am not concerned with the time to run the analyze. 
 Is this script good?  Is there anything more that I should do?

=====================================
prompt "Gathering all stats"
execute dbms_stats.gather_database_stats( cascade => TRUE ) ;

prompt "Deleting sys stats"
execute dbms_stats.delete_schema_stats( 'SYS' ) ;

prompt "Deleting system stats"
execute dbms_stats.delete_schema_stats( 'SYSTEM' ) ;

exit ;
===============================

I'm definitely going to look into monitoring.

Thanks,

Alan
 

Followup:
Instead of gathering on SYS and SYSTEM and then removing - you might

 for x in ( select username 
              from dba_users 
             where username not in ( 'SYS', 'SYSTEM' ) )
  loop
      dbms_stats.gather_SCHEMA_stats( ...... for that username )
  end loop; 

Review  October 08, 2002
Reviewer:  Irfan  from Kuwait

Q1. Your advice is to use dbms_stat. There is another package
dbms_utility, Do you prefer dbms_stat over dbms_utility?
if yes then why ?

Q2. In your book expert one-on-one, in the appendix A regarding dbms_utility you 
have written that "WE START BY FIRST DELETING AND THEN COLLECTING STATISTICS". 
Why there is a need to delete the statistics? The oracle manual says that 
ENTRIES ARE UPDATED FOR AN OBJECT EACH TIME WE ANALYZE THE OBJECT.

Thanks and regards. 

Followup:
q1) dbms_utility has a "pre dbms stat" procedure that offers no good 
functionality (nothing like dbms_stat)

ignore that dbms_utility procedure
use dbms_stat

q2) I did that (the delete) just to show "look, there is nothing up my sleeve".  
I deleted -- showed there were no stats and then analyzed showing there are now 
stats. 

Thats all, just an example. 

Histograms with bind variables  October 08, 2002
Reviewer:  Todd  from Los Angeles, CA

Tom, if we have built an app to use bind variables instead of literal values, is 
there any benefit to building histograms on even just indexed columns? 

Followup:
could be -- you can (and should sometimes) have literals in there, eg:

select count(*)
  from t
 where dname = 'SALES'
   and sal > :x;


There is nothing wrong with that -- if the query is executed over and over and 
over.  A histogram on dname might well be appropriate.  A well tuned system uses 
bind variables where appropriate and literals when it wants to.  See -- if that 
query was ALWAYS executed with dname = 'SALES', there would be no point in 
binding 'SALES' in -- we only bind in values that change from execution to 
execution. 

DBMS_STATS  October 08, 2002
Reviewer:  brenda zhang  from USA

Hi Tom,
thank you very much! very through.... but I still have oen question here: for 
instance, we have 8 milions table cust_dim,we create a following procedure to 
analyze table, but this table cust_dim will not get analyzed untill there are 
more than 10% of data change as you said: 
"you can gather stats JUST on tables that need it (we'll keep track for you and 
only analyze tables in which about 10% of the data has changed).  

so the statistics will not be accurate all the time if the data change is less 
than 10%. do we still have to use scheduler to analyze on a regular basis beside 
DBMS_STATS.GATHER_DATABASE_STATS? 

create or replace procedure anal_schemas is
begin
    begin
    DBMS_STATS.GATHER_DATABASE_STATS (
        estimate_percent => 10,
               block_sample => FALSE,
              method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1',
           degree => 8,
            granularity =>'DEFAULT',
               cascade => TRUE,
            stattab => NULL,
             statid => NULL,
             options => 'GATHER STALE'
    --       objlist =>'LIST',
    --    statown => NULL 
    );
    end;
    
    begin
    DBMS_STATS.DELETE_SCHEMA_STATS  ('SYS');
    end;

    begin
    DBMS_STATS.DELETE_SCHEMA_STATS  ('SYSTEM');
    end;
end;
/
 

Followup:
well, I still would gather for SCHEMAS not database...

and yes, you'll need to schedule this to run from time to time. 

DBMS_STATS.GATHER_SCHEMA_STATS doesn't work  October 08, 2002
Reviewer:  Brenda Zhang  from USA

Tom: there is a bug for DBMS_STATS.GATHER_SCHEMA_STATS??
<Bug 1890016>:  GATHER STALE DOES NOT SEEM TO BE WORKING FOR 
DBMS_STATS.GATHER_SCHEMA_STATS  

Followup:
but the bug also has in it:

Rediscovery Information:
You will see this bug when using dbms_stats.gather_xxx_stats and :
1. An invalid granularity is supplied
2. The GATHER STALE or GATHER EMPTY options are used
3. The procedure call does not seem to work
.
Workaround:
Correct the granularity parameter


simple workaround (and its fixed in the current releases) 

Review  October 09, 2002
Reviewer:  Irfan  from Kuwait

You mean to say that <Bug 1890016>: will not affect if I ignore  GATHER STALE or 
GATHER EMPTY. In this case following should work in Oracle 9.0.1.3 to compute 
statistics of schema ?

execute DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',NULL);

Thanks 

Followup:
yes. 

Advice on "SIZE 1" histogram  October 14, 2002
Reviewer:  Brad  from dallas, tx

The manual describing the DBMS_STATS options makes the point that you must 
specify "SIZE 1" in order to enable parallel processing for histograms.   
Doesn't a 1 bucket histogram limit it's usefulness?

Any advice on this?

E.G. 

  DBMS_STATS.GATHER_DATABASE_STATS (
        estimate_percent => 15,
        method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1',
        degree => 8,                           ^^^^^^
        cascade => TRUE) ;
     

Followup:
There may be certain specific columns you won't do in parallel...  computing 
histograms is very expensive -- do it only for those columns you have identified 
a need for (eg: wanna make a system go faster at night, stop using "analyze 
table compute statistics", you are computing histograms on everything) 

Review  October 26, 2002
Reviewer:  Irfan  from Kuwait

I am using following to Analyze schema with COMPUTE option
(I am specifying NULL for COMPUTE.)
execute DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',NULL);

For gathering statistics on index as well, do I need to specify CASCADE=true ? 
Or by default above will collect index Statistics ?

Thanks. 

Followup:
Here is a way you can determine what is happening when you analyze (whats 
getting gathered)


[email protected]> drop user a cascade;
User dropped.

[email protected]> grant dba to a identified by a;
Grant succeeded.

[email protected]> @connect a/a

[email protected]> create table t ( x int primary key, y int );
Table created.

[email protected]> insert into t values ( 1, 1 );
1 row created.

[email protected]> exec dbms_stats.gather_schema_stats( 'A', NULL );
PL/SQL procedure successfully completed.

[email protected]> select count(*) from user_tables where num_rows is not 
null;

  COUNT(*)
----------
         1

indicates TABLE got analyzed

[email protected]> select count(*) from user_indexes where num_rows is not 
null;

  COUNT(*)
----------
         0

but not any indexes

[email protected]> select count(distinct column_name) from 
user_tab_histograms;

COUNT(DISTINCTCOLUMN_NAME)
--------------------------
                         2

[email protected]>

but it did get histograms for every column in the table


 

which granularity should be used to analyze a schema?  October 27, 2002
Reviewer:  Leo  from Austin, TX, USA

I have a schema that has partition tables and non-partition tables.  I want to 
use DBMS_STATS.GATHER_SCHEMA_STATS to analyze all tables in the schema.  Which 
granularity should be used, 'DEFAULT', 'ALL' or 'PARTITION'? 

Followup:
only you can answer that.  which do you want to use (best to understand what 
they do, and apply the one that is correct and proper for your environment)

if there was a single way that was superior, there wouldn't be a choice.

You might find gather schema stats to be too coarse in a higher end system.  you 
may find you need to do some things table by table. 

  October 27, 2002
Reviewer:  leo

One advantage to use DBMS_STATS is that it can only analyze stale tables, but 
this option is only available in GATHER_SCHEMA_STATS and GATHER_DATABASE_STATS 
procedures.  Using the right granularity is critical for analyzing both 
partition and non-partition tables in a schema. 

Followup:
hmmm, well, lets see -- 

I can use LIST STALE on a schema and then do it table by table based on the 
results.

So, while you must use stale at the database or schema level, you certainly are 
not forced to analyze stale only at the schema or database level, you can just 
ask for a list of stale "things" and analyze them as you see fit (based off of a 
table perhaps you maintain of analyze options for a given segment?)

So, I disagree that stale is limited to schema or database.  It is that you 
would ask for a list of stale objects at the database or schema level and then 
proceed with analyzing if you like. 

Review  October 27, 2002
Reviewer:  Irfan  from Kuwait

I went through your example to see want is happening when we analyze schema. I 
used dbms_utility.analyze_schema with your example. Following is the outcome.

SQL> create table t ( x int primary key, y int );

Table created.

SQL> insert into t values ( 1, 1 );

1 row created.

SQL> execute dbms_utility.analyze_schema('A','COMPUTE');

PL/SQL procedure successfully completed.

SQL> select count(*) from user_tables where num_rows is not
  2  null;

  COUNT(*)
----------
         1

SQL> select count(*) from user_indexes where num_rows is not
  2  null;

  COUNT(*)
----------
         1
SQL> select count(distinct column_name) from
  2  user_tab_histograms;

COUNT(DISTINCTCOLUMN_NAME)
--------------------------
                         2


So, when I use DBMS_UTILITY.ANALYZE_SCHEMA I am getting statistics on INDEXES 
also. Correct ?

Then in this case DBMS_UTILITY should be prefered over DBMS_STATS. Please 
suggest. 

Followup:
do not use dbms_utility -- it is there for backwards support to 7.3.

Use dbms_stats, ignore dbms_utility for analyzing tables.  It is never 
preferred.  It has so few features as to make it just pale in comparision.

and remember this -- more is NOT better.  more is sometimes "more".

It is RARE that you want histograms on everything -- it is very very very time 
consuming and resource consuming to compute them.   

REVIEW  October 27, 2002
Reviewer:  Irfan  from Kuwait

So, I am going to use DBMS_STATS. I want to get the statistics on indexes also. 
I this case how can I define the syntex of DBMS_STATS.

I tried to do following to Analyze ENRIRE schema with INDEXES including.

exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',NULL,FALSE,'FOR ALLCLOUMNS SIZE 
1',NULL, 'DEFAULT',TRUE)

Is the above correct.(I could not understand block_sample and method_opt, that's 
why I put default values.)

Is there any other better way to do this ?

 

Followup:
well, lets see:

@ORA920.US.ORACLE.COM> create table t ( x int primary key, y int );

Table created.

[email protected]> insert into t values ( 1, 1 );

1 row created.

[email protected]> exec DBMS_STATS.GATHER_SCHEMA_STATS('A',NULL,FALSE,'FOR 
ALL COLUMNS SIZE 1',NULL, 'DEFAULT',TRUE)

PL/SQL procedure successfully completed.

[email protected]>
[email protected]> select count(*) from user_tables where num_rows is not 
null;

  COUNT(*)
----------
         1

[email protected]> select count(*) from user_indexes where num_rows is not 
null;

  COUNT(*)
----------
         1

[email protected]> select count(distinct column_name) from 
user_tab_histograms;

COUNT(DISTINCTCOLUMN_NAME)
--------------------------
                         2


it does accomplish your goal of analyzing the indexes, yes.  But -- are you sure 
you want 100% column level histograms -- that, only you can answer. 

Review  October 27, 2002
Reviewer:  Irfan  from kuwait

Thanks for detailed explanation one more question. Instead of typing the all 
default values, Can I use following to save me typing effort. It it take all the 
default values?

execute dbms_stats.gather_schema_stats ('A',NULL,cascade=>true);

Thanks again. 

Followup:
fortunately, we document all of the defaults and how to use these things:

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/dbms_st2.htm#1003995
is the documentation for gather schema stats -- just check it out and make sure 
the defaults are the ones you want.

by the way .... most people would SCHEDULE this using dbms_job to happen on a 
recurring basis -- so you would save on typing by scheduling it once or by 
creating a script that has the "long version" in it
 

is the 8.1.7 doc wrong?  October 28, 2002
Reviewer:  A reader

Hi

In SQL ref guide it says this about analyze command:

===========================================================
If you want to collect statistics on both the table as a whole and on one or 
more columns, be sure to generate the statistics for the table first, and then 
for the columns. Otherwise, the table-only ANALYZE will overwrite the histograms 
generated by the column ANALYZE. For example, issue the following statements: 

ANALYZE TABLE emp ESTIMATE STATISTICS;
ANALYZE TABLE emp ESTIMATE STATISTICS 
FOR ALL COLUMNS;

===========================================================

I mean why we have to do twice if the first analyze command gets stats for all 
columns already?????
   

Followup:
not technically wrong -- just a bad example.  The premise is right -- the 
example is not "good". you do not need to do that twice.  It is pointing out 
that if you do some analyzes in certain orders -- the last one "wins".  They 
should have done it differently, yes.  

Clarification  November 05, 2002
Reviewer:  Steve  from UK

Hi Tom

We've identified about 3 tables that have indexed columns that require 
histograms - so we issue the following:

exec 
dbms_stats.gather_table_stats(ownname=>'<schema>',tabname=>'<tabname>',method_opt
=>'FOR COLUMNS <columnname> SIZE 3');

If we want to analyse the entire schema - will we need to run the above command 
after gathering stats for the entire schema or is there a way of getting the 
database to look after it for us?

Cheers

Steve
 

Followup:
you would run it after -- not sure what you mean by "getting the database to 
look after it for us" 

  November 05, 2002
Reviewer:  Steve  from UK

Thanks Tom,

I guess what I meant was whether we had to run the command separately afterwards 
as you've now said, or whether, once it's analysed with the extra buckets, 
Oracle would always use the same number of buckets with a standard 
dbms_stat.gather_schema_statistics command on those columns. 

Why DBMS_STATS.gather_schema_stats('SCHEMA') does not work?  November 12, 2002
Reviewer:  A Reader  from USA

Hi Tom,
I have a database that is used on online transaction system.  Some thing about 
the statictis I don't understand.  When I run 
DBMS_STATS.gather_schema_stats('SCHEMA'), the tables in the schema are analyzed 
(I check the last_analyzed in v$session).  However, when I run a query, the 
performance does not improve at all.  I also rin the 
dbms_utility.analyze_schema('SCHEMA','COMPUTE'), the same thing happen, no 
performance gain.  Both methods worked in other databases. Since I know which 
table I need to analyze, I run "analyze table t compute statistics".  After 
this, the respose time for the query improve from 1.1 s to 80 ms.  The execution 
plan never changes according to the plans generated before and after all the 
analysis.  Another thing I notice is that, after a few logins using the 
application interface, the query slow down again.  Questions:
1.  Wyh DBMS_STATS.gather_schema_stats('SCHEMA') or dbms_utility.analyze_schema 
does not work and I need to analyze the specific table? 
2.  Why the plan does not change but the query (which always uses full table 
scan) performance changes?
3.  Why the query slow again after a few logins?  How can I "chache" the 
staistics or the plan?

Thank you very much for your help. 

Followup:
Oh - would it not be totally cool if just running dbms_stats was like setting 
fast=true!

what you need to do is 
a) run the queries with sql_trace before and after having statistics
b) compare the query plans and results

Same with all of the other questions you have -- sql_tracing will tell you what 
is going on.

With the amount of detail provided here -- no tkprofs, no examples -- there is 
not much more I can say (well, I can say that if you have my book "Expert one on 
one Oracle" read chapter 10 and follow the procedures outlined there to see what 
is going on) 

please guide  November 29, 2002
Reviewer:  Yogeeeraj  from Mauritius

hello,

From above:
<quote>
I can use LIST STALE on a schema and then do it table by table based on the 
results.
</quote>

Can you please direct me to the document where this is explained in more details 
or give an example?

thanks a lot.
Best Regards
Yogeeraj 

Followup:
have you read the supplied packages guide?  all supplied packages are documented 
there.

this is a rather simple exercise in plsql programming.  Here is a simple 
example:


[email protected]> create table t as select * from all_objects where 
rownum < 100;
Table created.


[email protected]> alter table t monitoring;
Table altered.

-- that is needed for "staleness" measuring



[email protected]> analyze table t compute statistics;
Table analyzed.

[email protected]> declare
  2          l_objList  dbms_stats.objectTab;
  3  begin
  4          dbms_stats.gather_schema_stats
  5          ( ownname        => USER,
  6            options        => 'LIST STALE',
  7            objlist        => l_objList );
  8
  9          for i in 1 .. l_objList.count
 10          loop
 11                  dbms_output.put_line( l_objList(i).objType );
 12                  dbms_output.put_line( l_objList(i).objName );
 13          end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.


that tells me that no objects need stats right now.  If they did, we would only 
list them out -- not actually gather stats on them

[email protected]>
[email protected]> insert into t select * from t;

99 rows created.

[email protected]> commit;

Commit complete.

[email protected]>
[email protected]> declare
  2          l_objList  dbms_stats.objectTab;
  3  begin
  4          dbms_stats.gather_schema_stats
  5          ( ownname        => USER,
  6            options        => 'LIST STALE',
  7            objlist        => l_objList );
  8
  9          for i in 1 .. l_objList.count
 10          loop
 11                  dbms_output.put_line( l_objList(i).objType );
 12                  dbms_output.put_line( l_objList(i).objName );
 13          end loop;
 14  end;
 15  /
TABLE
T

PL/SQL procedure successfully completed.

Ahh, now T needs stats, more then about 10% of the data was modified.  This 
shows that we just listed this fact:


[email protected]> select num_rows from user_tables where table_name = 
'T';

  NUM_ROWS
----------
        99

[email protected]> exec dbms_stats.gather_schema_stats( ownname => 
user, options => 'GATHER STALE' );

PL/SQL procedure successfully completed.

[email protected]> select num_rows from user_tables where table_name = 
'T';

  NUM_ROWS
----------
       198

[email protected]>

it told us to gather stats, it didn't actually do it when we used LIST STALE
 

Great explanation !!  November 30, 2002
Reviewer:  Yogeeraj  from Mauritius

Hello,

Thank you a lot for this detailed explanation. It really helps!

I will go through the supplied package guide again.

Best Regards
Yogeeraj
 

New v9.2 GATHER_AUTO feature  December 05, 2002
Reviewer:  Brad  from Dallas, TX

The GATHER_AUTO option is new in v9.x.   The 9.2 doc says that when you use the 
GATHER_AUTO option you may not specify other options that direct which 
statistics are gathered, and seems to imply that it decides which stats to 
gather in an automated fashion.  Do you happen to know if it determines which 
stats to gather on a table by table basis or if it just has a standard set of 
stats that it gathers.

-- Turn on monitoring for all the tables in the schema.
-- This only needs to be done as new tables are added.
begin
  dbms_stats.alter_schema_tab_monitoring(
     ownname    => 'MYSCHEMA'
  );
end;
/

-- The GATHER AUTO option causes statistics to be gathered
-- for tables that Oracle thinks need updated stats.
begin
  dbms_stats.gather_schema_stats(
     ownname    => 'MYSCHEMA'
    ,options    => 'GATHER AUTO'
  );
end;
/
 

Followup:
it is table/table, column by column.  It is also wholly undocumented.  It 
samples to decide what to do.

If you look at a sql_trace (be prepared to wait -- sql_trace really slows this 
puppy down), you'll see really strange looking queries like:

select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring no_expand
  index_ffs(t,"T1_IDX1") parallel_index(t,"T1_IDX1",32767) */ count(*) as nrw,
  count(distinct sys_op_lbid(34421,'L',t.rowid)) as nlb,null as ndk,null as
  clf
from
 "A"."T1"  sample block (.00001)  t where "OBJECT_ID" is not null

select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring no_expand
  index_ffs(t,"T1_IDX1") parallel_index(t,"T1_IDX1",32767) */ count(*) as nrw,
  count(distinct sys_op_lbid(34421,'L',t.rowid)) as nlb,null as ndk,null as
  clf
from
 "A"."T1"  sample block (.001)  t where "OBJECT_ID" is not null

select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring no_expand
  index_ffs(t,"T1_IDX1") parallel_index(t,"T1_IDX1",32767) */ count(*) as nrw,
  count(distinct sys_op_lbid(34421,'L',t.rowid)) as nlb,null as ndk,null as
  clf
from
 "A"."T1"  sample block (.1)  t where "OBJECT_ID" is not null

select  /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring parallel(t,
  32767)  parallel_index(t,32767) */ count(*)
from
 "A"."T1" sample block (.001)

select  /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring parallel(t,
  32767)  parallel_index(t,32767) */ count(*)
from
 "A"."T1" sample block (.1)

....
insert /*+ append */ into sys.ora_temp_1_ds_110 select  /*+
  cursor_sharing_exact dynamic_sampling(0) no_monitoring parallel(t,32767)
  parallel_index(t,32767) */ "OWNER","OBJECT_NAME","SUBOBJECT_NAME",
  "OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME",
  "TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY" from "A"."T1"
  sample (14.70588235294117647058823529411764705882) t
....

(please don't ask me about undocumented hints and functions you see -- pretend 
they do not exist because for all intents and purposes -- they don't!)

 

Again dbms_stats vs analyze  January 20, 2003
Reviewer:  Igor  from France

Hi
 
 We were doing daily
  dbms_stats.gather_schema_stats( OWNNAME => 'SCHEMA', DEGREE => 2, CASCADE => 
true, method_opt => 'for all columns size 1' )
  but found that some complex queries have suboptimal execution path.

 After running, 
                analyze table &tab
                compute statistics
                for table
                for all indexes
                for all columns

 on tables in these queries optimizer found much efficient 
 execution plan.
 My conclusion was that that analyze collected more info
 for CBO.
 What would be equivalent in dbms_stats to this analyze ?
 
                                                Igor

P.S. Also what confuses me a bit is that you are advertising dbms_stats but use 
analyze on table 
level instead of gather_table_stats in most 
 of your examples (I dont mean things like chaining, validate structure and 
such)

  Thanks for your clarification


                               Igor
 

Followup:
No,they just gathered DIFFERENT statistics.  your method opt in dbms_stats is 
different then in the analyze.


In my "demos" -- I have toy tables.  I just find it easier to type in (as that 
is what I do, type it in) analyze then the corresponding dbms_stats command.  
Just laziness on my part as far as that goes.

do as i say, not as i do (in this case) ;)

 

Yes, but  January 20, 2003
Reviewer:  Igor  from France

Thanks for your fast response

 When you look at method opt option they are not 
 as versatile as with 'analyze table'.
 I would like to run DBMS_STATS.GATHER_SCHEMA_STATS
 with a equal level collected by prior analyze table
 so that CBO is able to do efficient execution plan.
 Maybe is a good idea to play with number of buckets (size x) ? ( I don't know 
what is number Oracle choose in ANALYZE statement)

  Thanks for your response

                                    Igor
 

Followup:
they are as versitile? what do you mean? 

More description of GATHER_SCHEMA_STATS method_opt  January 20, 2003
Reviewer:  David Penington  from Melbourne, Australia

Tom,
I have read the 9iR1 Supplied PL/SQL packages documentation for DBMS_STATS, and 
the chapter on gathering statistics in the 9iR1 Database Performance Guide. Is 
there any more information available on the meaning and meaning and impact of 
the METHOD_OPT of GATHER_SCHEMA_STATS and GATHER_TABLE_STATS ?
I know I could work it out by repeated testing, but something formal is nice 
(and more reliable)
Eg. Does it always gather table stat's ?
What do SIZE REPEAT, SIZE AUTO, SIZE SKEWONLY do?
What will it do when a column is names but doesn't occur in some of the tables 
(testing tells me what happens in the version I'm testing, but not on other 
versions)?

 

Followup:
Ok, the method opt dictates how histograms will be computed.

DBMS_STATS in general always gets table statistics 
(gather_table,gather_schema,gather_database all do).  

If you use CASCADE=>TRUE, it'll get indexes.  

METHOD_OPT dictates how histograms are done.

Take the analyze command for example:

analyze table T compute statistics
for table              <<=== dbms_stats does this when you gather 
                             table,schema,database stats
for all indexes        <<=== when you do table/schema/database AND cascade=> 
                             TRUE or you use gather index stats directly
for all columns size 1 <<== when you default method_opt, can be overriden


Now, we have method_opts of:

o for all columns  - gather histograms on all columns -- hugely expensive and 
rarely should be used

o for all indexed columns - gather histograms on all columns that are currently 
indexed.  I find this typically to be the "most useful" although you can 
arguably skip nearly unique columns such as primary keys

o for all hidden columns - gather histograms on columns you cannot see, useful 
if using the object relational features which incorporate tons of hidden columns 
in your objects.

o for columns <list> - gather histograms on these specific columns, could be 
useful to skip primary key indexes or just to get the 2 or 3 columns of interest


Each of the above can be modified with SIZE clauses where SIZE can be:

o SIZE <N>.  N is an integer between 1 and 254 representing the number of 
buckets. See note at
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3126073805757
to understand what this means and what happens if for example you use N = 10 and 
there are 5 distinct column values or you use N = 10 and there are 15 distinct 
values...

o SIZE REPEAT -- says to do the histograms just like you did them last time.  It 
reads the data dictionary to figure out what to do

o SIZE AUTO -- Oracle looks at the data and using a magical, undocumented and 
changing algorithm, figures out all by itself what columns to gather stats on 
and how many buckets and all.  It'll collect histograms in memory only for those 
columns which are used by your applications (those columns appearing in a 
predicate involving an equality, range, or like operators). we know that a 
particular column was used by an application because at parse time, we'll store
workload information in SGA. Then we'll store histograms in the data dictionary 
only if it has skewed data (and it worthy of a histogram)

o SIZE SKEWONLY  when you collect histograms with the SIZE option set to 
SKEWONLY, we collect histogram data in memory for all specified columns (if you 
do not specify any, all columns are used). Once an "in-memory" histogram is 
computed for a column, it is stored inside the data dictionary only if it has 
"popular" values (multiple end-points with the same value which is what we 
define by "there is skew in the data").



don't know what you mean by "What will it do when a column is names but doesn't 
occur in some of the tables (testing tells me what happens in the version I'm 
testing, but not on other versions)?"... guess you mean "i listed a column name 
in the method opt, but not such column exists" -- it should ignore it. 

  January 22, 2003
Reviewer:  Igor  from France

Hi

  OK. Playing with buckets I got wanted result.
  Thank for your help 

gather schema & unusable indexes  January 24, 2003
Reviewer:  Igor  from France

Hi

 Is there workaround without index rebuild ?
 (I would like to recollect statistics skipping unusable
 indexes, but doing it on the rest)

drop table t;

Table dropped.

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index ti on t ( object_id );

Index created.

SQL> alter index ti unusable;

Index altered.

SQL> SQL> alter session set skip_unusable_indexes=true;

Session altered.


SQL> SQL> create table t2 as select * from dba_objects;
c
Table created.

SQL> reate index ti2 on t2 ( object_id );

Index created.

SQL> exec dbms_stats.gather_schema_stats( OWNNAME => 'T', DEGREE => 4, CASCADE 
=> true, method_opt => 'for all columns size 1' )
BEGIN dbms_stats.gather_schema_stats( OWNNAME => 'T', DEGREE => 4, CASCADE => 
true, method_opt => 'for all columns size 1' ); END;

*
ERROR at line 1:
ORA-01502: index 'T.TI' or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 4481
ORA-06512: at "SYS.DBMS_STATS", line 4612
ORA-06512: at "SYS.DBMS_STATS", line 4722
ORA-06512: at "SYS.DBMS_STATS", line 4704
ORA-06512: at line 1


SQL> 1 select table_name, last_analyzed
SQL> /

TABLE_NAME                     LAST_ANAL
------------------------------ ---------
T                              24-JAN-03
T2

 Thanks for your answer

                                               Igor 

Followup:
obvious solution:

cascade => false

for x in ( select * from user_indexes where status = 'VALID' )
loop
   dbms_stats.gather_index_stats() 

  January 24, 2003
Reviewer:  Igor  from France

Hah, but it's not so elegant ;-)

 Anyway, what is a bit confusing is that even when 
 I should skip these indexes (and really Oracle does it 
 in DML statements ), when you start procedure to collect
 statistics, which is essentially select ( OK, a bit "strange" ) he doesn't.
 It smells like "feature", no ? :-)

                                          Igor
 

Followup:
well, the docs do say 

...
This setting allows all operations (inserts, deletes, updates, and selects) on 
tables with unusable indexes or index partitions.
......


nothing about other operations.

 

  March 06, 2003
Reviewer:  A reader

Tom,

What should be the frequency of issuing analyze commands in OLTP vs DSS systems.

Thanks. 

Followup:
I don't know.

Really.

I would recommend you use alter table T monitoring and let the system tell you 
how often for OLTP....

In DW -- it is usually a little more clear -- after you load.

 

Follow-up question  March 17, 2003
Reviewer:  Bob Rich  from IN USA

Tom,
  I'm trying to test the gather_stale option on multiple tables having been 
analyzed with different percentages.  I've set-up a test with two identical 
tables with more that 1 million rows each.  I analyzed table 'A' using 100% of 
the rows, and table 'B' using an estimate of 15%.  I then turned on table 
monitoring, truncated and reloaded each table.  I then used 
dbms_stats.gather_schema_stats( ownname => 'XXXX', options => 'GATHER STALE');  
It appears each table was reanalyzed using 100% of the rows.  I expected table 
'B' to use 15%.  Is this the way it is supposed to work?  

Thank you 

Followup:
yes it is.  If you want "custom collection methods" on the objects (different 
percents, whatever) you would (could) use list stale and maintain a database 
table of parameters to dbms_stats to be used

so you would list stale
iterate over the list and fetch the options
gather table stats for that object 

One more follow-up  March 17, 2003
Reviewer:  Bob Rich  from IN USA

Tom,
  Okay, I can build a script to read the dba_tab_modifications table and analyze 
the tables, but how do I "clean out" the dba_tab_modifications table once the 
analyze script runs?  Or, do I only use table monitoring on tables that I am 
willing to use estimate_percentage = 100%. I can't find anything on Metalink. 

Granularity  April 24, 2003
Reviewer:  Matt

Tom, 

We have a v large partitioned table, around 1000 Million rows, each period we 
add extra partitions an analyze them.

Initially we were doing a 
dbms_stats.gather_stats specifying table name and the new partitions with 
granularity set to 'ALL' - over time this has started to slow down.

I think we would be better changing this to 'PARTITION' and then updating the 
global table stats once at the end, is this correct ? What is the best method 
for gathering the global table stats, is there anyway we can get it to use the 
already analyzed partition stats, or will it do a scan ?

 

Followup:
in order to gather global stats -- the data as a whole must be inspected (an 
estimate will help there, maybe parallel as well).


We cannot correctly determine data skew and distributions from the individual 
partition stats, we need to "globally" look at the table.


Now, if your queries all do partition pruning down to a single partition -- you 
do not really need global stats... 

Tom, what's going on here ?  July 15, 2003
Reviewer:  Fan  from PA

(9iR2/LINUX)

Tom, please help, thanks

begin DBMS_STATS.GATHER_SCHEMA_STATS('scott',NULL); end;

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file client_UNW.bad in UTL_FILE_DIR not found
ORA-06512: at "SYS.DBMS_STATS", line 9136
ORA-06512: at "SYS.DBMS_STATS", line 9616
ORA-06512: at "SYS.DBMS_STATS", line 9800
ORA-06512: at "SYS.DBMS_STATS", line 9854
ORA-06512: at "SYS.DBMS_STATS", line 9831
ORA-06512: at line 1 

Followup:
scott has an external table

the exteneral file is "bogus", doesn't work, gives an error when you query it.

either

a) fix it
b) skip it
c) drop it

query 

USER_EXTERNAL_TABLES

 

how does estimate statistics work?  July 23, 2003
Reviewer:  A reader

Hi

I would like to know how estimate statistics work? If I do 

analyze table mytable estimate statistics sample 2000 rows 

and myemp has 1000000 rows then how does Oracle estimate?   

Followup:
by pulling a random sample of about 2000 rows from the table and using that as 
the basis for the statistics.

You can see it looks at pretty much the entire thing to get this random sample 
of rows via:


ops$tkyte@ORA920LAP> create table t1 as select * from big_table.big_table where 
rownum <= 10000;

Table created.

ops$tkyte@ORA920LAP> create table t2 as select * from big_table.big_table where 
rownum <= 50000;

Table created.

ops$tkyte@ORA920LAP> create table t3 as select * from big_table.big_table where 
rownum <= 100000;

Table created.

ops$tkyte@ORA920LAP> create table t4 as select * from big_table.big_table where 
rownum <= 500000;

Table created.

ops$tkyte@ORA920LAP> create table t5 as select * from big_table.big_table where 
rownum <= 1000000;

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> alter session set sql_trace=true;

Session altered.

ops$tkyte@ORA920LAP> analyze table t1 estimate statistics sample 2000 rows;

Table analyzed.

ops$tkyte@ORA920LAP> analyze table t2 estimate statistics sample 2000 rows;

Table analyzed.

ops$tkyte@ORA920LAP> analyze table t3 estimate statistics sample 2000 rows;

Table analyzed.

ops$tkyte@ORA920LAP> analyze table t4 estimate statistics sample 2000 rows;

Table analyzed.

ops$tkyte@ORA920LAP> analyze table t5 estimate statistics sample 2000 rows;

Table analyzed.


analyze table t1 estimate statistics sample 2000 rows

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.08       0.84        139        148          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.08       0.84        139        148          2           0
********************************************************************************
analyze table t2 estimate statistics sample 2000 rows

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.13       0.88        734        726          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.14       0.88        734        726          2           0
********************************************************************************
analyze table t3 estimate statistics sample 2000 rows

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.20       1.41       1480       1439          3           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.20       1.41       1480       1439          3           0
********************************************************************************
analyze table t4 estimate statistics sample 2000 rows

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.42       3.42       6111       2009          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.42       3.42       6111       2009          2           0
********************************************************************************
analyze table t5 estimate statistics sample 2000 rows

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.58       1.88      10322       2009          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.59       1.88      10322       2009          2           0


The more data in the table, the more work it takes to get the required number of 
rows.

also note the 2000 is "about" 2000:

ops$tkyte@ORA920LAP> select table_name, sample_size from user_tables;

TABLE_NAME                     SAMPLE_SIZE
------------------------------ -----------
T1                                    1724
T2                                    1914
T3                                    1914
T4                                    1983
T5                                    1838
 

cool  July 23, 2003
Reviewer:  robert  from PA

but aren't you on vacation ?
so what's all this VPNing :} 

I dont understand   July 23, 2003
Reviewer:  A reader

Hi

From your last example how does Oracle determine the total number of rows by 
just reading roughly 2000 rows? Does it do this: determine how many blocks are 
used to store these 2000 rows then get the average rows per block then multiply 
that to number of blocks? If so where does it get the number of blocks? 

Followup:
not documented, but it certainly knows where the HWM of the table is (it knows 
how many blocks might contain rows) -- even the RBO needed that in order to full 
scan. 

if HWM is always known   July 24, 2003
Reviewer:  A reader

Hi

As far as I know we must analyze the tables to get the HWM, if as you said RBO 
also needs HWM then how does RBO obtain that data from? 

Followup:
you do not need to analyze, the package dbms_space can be used to find this 
information out.  it is all on the segment headers.

otherwise, the rbo would NOT be able to full scan! 

What happens if SYS/SYSTEM tables are analyzed also  July 25, 2003
Reviewer:  Dilbert  from San Jose, CA

Hi Tom,

What I understand from your answers is that sys and system accounts should not 
be analyzed as they work best with RULE. In one of our instances, I found that 
sys and system have been analyzed. Would it have a negative effect on 
performance ? I am a pl/sql developer so its not an easy task for me to play 
around with the statistics to create a test case and prove :(

 

Followup:
in 9i -- it is OK and supported.  in 8i it might be an issue (but you would 
know, it would be obvious that there is a problem).  in the next release, it'll 
be the way to go. 

dbms_stats equivalent of estimate statistics  July 25, 2003
Reviewer:  Peter Tran  from Houston, TX USA

Tom,

I've recommended for our company to convert over to using dbms_stats rather than 
analyze table.

One question I got was what's the equivalent of dbms_stats parameter setting to 
emulate analyze table estimate statistics runtime?

I tried:

DBMS_STATS.GATHER_TABLE_STATS (                     
    ownname=>NULL,                                  
    tabname=><table_name>,             
    partname=>NULL,                                 
    estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,  
    method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',
    degree=>4,                                      
    cascade=>TRUE);                                 

On a 15 million rows table, the analyze table estimate statistics ran in 
seconds, while the above dbms_stats.gather_table_stats() took over 3 minutes!

Are they so different that we should expect dbms_stats to run much longer than 
analyze table <table_name> estimate statistics?

Is trying to compare the runtime meaningless, since one (dbms_stats) is much 
better than the other?

Thanks,
-Peter 

Followup:
3 minutes!  good gosh, so?  really -- if you would like, you can use a fixed 
estimate percent just like before.  but hey, 3 minutes on a 15million row table 
doesn't sound too bad given the level of stats you are gathering.  

you do not give me the command you used to use -- did you cascade there? did you 
gather histograms?  what?

are you sure parallel makes sense even -- is the data nicely spread out? 

The gory details  July 25, 2003
Reviewer:  Peter Tran  from Houston, TX USA

SQL> select count(*) from big_table;

  COUNT(*)
----------
  15179090

SQL> analyze table big_table delete statistics;

Table analyzed.

SQL> analyze table big_table estimate statistics;

Table analyzed.

Elapsed: 00:00:02.00

SQL> @check_table

INDEX_NAME                     T_NUM_ROWS I_NUM_ROWS UQ_COLUMNS
------------------------------ ---------- ---------- ----------
big_table_PK                     15195279 14946619.2          5
big_table_IX                     15195279   12665663          5
big_table_UQ                     15195279   12390453          5

SQL> analyze table big_table delete statistics;

Table analyzed.

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS (
  3       ownname=>NULL,tabname=>'big_table',
  4       partname=>NULL,
  5       estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
  6       method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',
  7       degree=>4,
  8       cascade=>true);
  9  END;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:36.09

SQL> @check_table

INDEX_NAME                     T_NUM_ROWS I_NUM_ROWS UQ_COLUMNS
------------------------------ ---------- ---------- ----------
big_table_PK                     15179090   14712725          5
big_table_IX                     15179090   14593628          5
big_table_UQ                     15179090   14705608          5


Where @check_table.sql is:

select i.index_name, 
       t.num_rows T_NUM_ROWS, 
       i.num_rows I_NUM_ROWS, c.cnt UQ_COLUMNS
from (select num_rows from user_tables where table_name = 'big_table') t,
     (select index_name, num_rows from user_indexes where table_name = 
'big_table' ) i,
     (select count(distinct column_name) cnt from user_tab_histograms where 
table_name = 'big_table' ) c
/

I guess it's just a shock to the developers when they first start using 
dbms_stat and realize that they're not going to get response time in the 
seconds.

I thought the degree parameter is used to specify how much parallism you can use 
while gathering the statistics.  I noticed that if I leave out the degree=>4 
part, it takes double the time to execute the dbms_stats.

In the first version, I forgot to specify the estimate_percent, so it defaulted 
to compute.  The job ran for more than 30 minutes.  I finally had to kill it.

-Peter 

Followup:
you are if you check out the sample size, you'll find you were analyzing about 
1064 rows with that estimate.  You might have been lucky, you might have the 
most well behaved data on the planet whereby 1064 rows from 15million is 
representative enough to represent your data.

In most cases, it will not be.  you can obviously set the estimate size and such 
so as to "correct" this, to make the estimate go faster if you like. 

9i  July 29, 2003
Reviewer:  Tatiane

>in 9i -- it is OK and supported.  in 8i it might be an >issue (but you would 
>know, it would be obvious that there is a problem).  in >the next release, 
it'll 
>be the way to go. 

You mean in 9i (and beyond) could I simply do (without problems with sys or 
system):

DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
   method_opt =>       'FOR ALL INDEXED COLUMNS SIZE AUTO',
   cascade =>          TRUE 
);

? 

Followup:
after TESTING in your TEST system, running your applications in TEST, yes.

although you might consider table monitoring.... and gather stale...

remember your data dictionary is not very volitile, it need not be analyzed 
frequently.

so, gather database stats, not sure, schema stats -- yes. 

statistics connected as SYS  August 01, 2003
Reviewer:  [email protected]  from Bolivia

Hi, Tom, which is your opinion about 
connect as SYS to gather full statistics
with the following command.
begin
 DBMS_STATS.GATHER_DATABASE_STATS();
end;

I am almost completely sure I'm wrong, which privileges do you suggest, to 
execute them, only DBA?
 

Followup:
i would prefer to gather them schema by schema using the gather stale option 
instead of "gather everything".

have each schema gather its own stats on its own schedule. 

  August 01, 2003
Reviewer:  A reader

Thank you :) 

estimate vs compute  August 15, 2003
Reviewer:  Reader

Tom, If I use estimate statistics with some % in analyze table command, is there 
anything like 25% or 30% for estimate that will do the analysis just the same if 
I used compute statistics option. In other words, is there any optimum % oracle 
recommends for estimate that would do the same as compute so that for really 
"huge" tables we don't have to use compute option. 

Also, analyze table t compute statistics command by default analyzes the table 
as well for all the columns and indexes associated with it. Thanks. 

Followup:
if there were an "optimum", that would be the only setting ;)

anything at 49 or above is COMPUTE.

anything below tries to do what you ask.  In many cases, given a large enough 
evenly distributed set of data 10% can do it.  the more skewed the data, the 
larger the sample required to get a feel for the degree of skewedness.


A simple test like this can show you what is getting gathered with what 
commands:

ops$tkyte@ORA920> create table t ( x int primary key );

Table created.

ops$tkyte@ORA920> insert into t values(1);

1 row created.

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select 'table', decode( num_rows, null, 'nope', 'yep' ) from 
user_tables where table_name = 'T'
  2  union all
  3  select 'index', decode( num_rows, null, 'nope', 'yep' ) from user_indexes 
where table_name = 'T'
  4  union all
  5  select 'histograms', decode( count(*), 0, 'nope', 'yep' ) from 
user_tab_histograms where table_name = 'T' and rownum = 1
  6  /

'TABLE'    DECO
---------- ----
table      nope
index      nope
histograms nope

ops$tkyte@ORA920> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA920> select 'table', decode( num_rows, null, 'nope', 'yep' ) from 
user_tables where table_name = 'T'
  2  union all
  3  select 'index', decode( num_rows, null, 'nope', 'yep' ) from user_indexes 
where table_name = 'T'
  4  union all
  5  select 'histograms', decode( count(*), 0, 'nope', 'yep' ) from 
user_tab_histograms where table_name = 'T' and rownum = 1
  6  /

'TABLE'    DECO
---------- ----
table      yep
index      yep
histograms yep

ops$tkyte@ORA920> analyze table t delete statistics;

Table analyzed.

ops$tkyte@ORA920> analyze table t compute statistics for table;

Table analyzed.

ops$tkyte@ORA920> select 'table', decode( num_rows, null, 'nope', 'yep' ) from 
user_tables where table_name = 'T'
  2  union all
  3  select 'index', decode( num_rows, null, 'nope', 'yep' ) from user_indexes 
where table_name = 'T'
  4  union all
  5  select 'histograms', decode( count(*), 0, 'nope', 'yep' ) from 
user_tab_histograms where table_name = 'T' and rownum = 1
  6  /

'TABLE'    DECO
---------- ----
table      yep
index      nope
histograms nope

ops$tkyte@ORA920> analyze table t delete statistics;

Table analyzed.

ops$tkyte@ORA920> analyze table t compute statistics for all indexes;

Table analyzed.

ops$tkyte@ORA920> select 'table', decode( num_rows, null, 'nope', 'yep' ) from 
user_tables where table_name = 'T'
  2  union all
  3  select 'index', decode( num_rows, null, 'nope', 'yep' ) from user_indexes 
where table_name = 'T'
  4  union all
  5  select 'histograms', decode( count(*), 0, 'nope', 'yep' ) from 
user_tab_histograms where table_name = 'T' and rownum = 1
  6  /

'TABLE'    DECO
---------- ----
table      nope
index      yep
histograms nope

 

Very helpful. Thanks much.  August 15, 2003
Reviewer:  reader

  

dbms_stats does it different ....  August 15, 2003
Reviewer:  reader

Tom, I did the above test with dbms_stats package as follows, and could please 
sugest how do i get my index analyzed also. A is the table t in your above demo 
with primary key defined.

SQL> exec dbms_stats.gather_table_stats('DEMO','A');

PL/SQL procedure successfully completed.

SQL> select 'table', decode( num_rows, null, 'NO', 'YES' ) from 
  2  dba_tables where table_name = 'A'
  3    union all
  4  select 'index', decode( num_rows, null, 'NO', 'YES' ) from dba_indexes 
  5  where table_name = 'A'
  6    union all
  7  select 'histograms', decode( count(*), 0, 'NO', 'YES' ) from 
  8  dba_tab_histograms where table_name = 'A' and rownum = 1
  9  /

'TABLE'    DEC
---------- ---
table      YES
index      NO
histograms YES

SQL> analyze table a delete statistics;

Table analyzed.

SQL> select 'table', decode( num_rows, null, 'NO', 'YES' ) from 
  2  dba_tables where table_name = 'A'
  3    union all
  4  select 'index', decode( num_rows, null, 'NO', 'YES' ) from dba_indexes 
  5  where table_name = 'A'
  6    union all
  7  select 'histograms', decode( count(*), 0, 'NO', 'YES' ) from 
  8  dba_tab_histograms where table_name = 'A' and rownum = 1
  9  /

'TABLE'    DEC
---------- ---
table      NO
index      NO
histograms NO

SQL> exec dbms_stats.gather_table_stats('DEMO','A', METHOD_OPT => 'FOR ALL 
INDEXED cOLUMNS');

PL/SQL procedure successfully completed.

SQL> select 'table', decode( num_rows, null, 'NO', 'YES' ) from 
  2  dba_tables where table_name = 'A'
  3    union all
  4  select 'index', decode( num_rows, null, 'NO', 'YES' ) from dba_indexes 
  5  where table_name = 'A'
  6    union all
  7  select 'histograms', decode( count(*), 0, 'NO', 'YES' ) from 
  8  dba_tab_histograms where table_name = 'A' and rownum = 1
  9  /

'TABLE'    DEC
---------- ---
table      YES
index      NO
histograms YES 

guess I have to use gather_index_stats proc?  August 15, 2003
Reviewer:  reader

  

Figured it out by reading fully this thread  August 15, 2003
Reviewer:  reader

I should use cascade option. Tom, you are great!

SQL> exec dbms_stats.gather_table_stats('DEMO','A', CASCADE => TRUE);

PL/SQL procedure successfully completed.

SQL> select 'table', decode( num_rows, null, 'NO', 'YES' ) from 
  2  dba_tables where table_name = 'A'
  3    union all
  4  select 'index', decode( num_rows, null, 'NO', 'YES' ) from dba_indexes
  5  where table_name = 'A'
  6    union all
  7  select 'histograms', decode( count(*), 0, 'NO', 'YES' ) from 
  8  dba_tab_histograms where table_name = 'A' and rownum = 1
  9  /

'TABLE'    DEC
---------- ---
table      YES
index      YES
histograms YES 

difference between dbms_stats and analyze  August 15, 2003
Reviewer:  Ramu  from Cincinnati

Other than analyzing using parallel option what are the advantages in using 
dbms_stats package in analyzing tables and indexes? 

When I use either analyze table command or dbms_stats.gather_table_stats 
procedure, indexes associated with the table also get analyzed? If so, why there 
is another command analyze index and another procedure 
dbms_stats.gather_index_stats to analyze indexes? Thanks. 

Followup:
analyze table T compute statistics

is much like

dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all columns', 
cascade=>true);


analyze index i compute statistics

is much like

dbms_stats.gather_index_stats


and so on.  dbms_stats.gather_schema_stats -- well, that has no corresponding 
equal in analyze.

you cannot export/import stats with analyze

you cannot gather system stats with analyze

you cannot set stats with analyze

you cannot gather STALE <<<=== big reason, huge reason >>> with analyze

it is the stated direction (dbms_stats is)



 

stale?  August 16, 2003
Reviewer:  A Reader

<quote>you cannot gather STALE<quote>

Tom, what does stale do? Thanks. 

Followup:
oracle can monitor tables.

you only need to gather stats on tables where the data changed enough to warrant 
it.

dbms-stats can use this monitoring data to decide what tables are "stale" and in 
need of gathering.

so, some nights, dbms_stats might gather stats on ZERO tables, others on all, 
others on some -- the amount of work it does is proportional to the amount of 
work it needs to do.

in general, some tables, but not all, will be stale -- so every night, 
dbms_stats will gather stats on some subset of your tables, not all of them.

analyze does everything, regardless of whether it needs it or not. 

great explanation - how about new tables  September 17, 2003
Reviewer:  Sravan  from Northampton, MA

Tom,
I am monitoring all the schema objects and using the stale option with "gather 
AUTO" for dbms_stats job. They are running fine now on the existing tables.

How about the new tables which gets added to the database. 
1) Does the job automatically recognizes the new table? 
2) Will the table be monitored?( picks up automatically ? Or should I set a job 
to run ALTER_SCHEMA_TAB_MONITORING frequently to add it the list ?)
3) Will gather_schema_stats recognises the new tables and gathers the statistics 
?


=================================================
Monitoring of the tables is done using:

exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING('SCOTT',TRUE);
================================================
dbms_stats definition looks like(runs 3 times a week):

exec dbms_stats.gather_schema_stats( 
     ownname          => 'SCOTT', 
     options          => 'GATHER AUTO',
     estimate_percent => dbms_stats.auto_sample_size,
     method_opt       => 'FOR ALL INDEXED COLUMNS SIZE 1'
     degree           => 3
   )
================================================= 

Followup:
it'll get the new tables...
it will not be monitored
you might add the alter schema tab monitoring to the script...

ops$tkyte@ORA920> @connect a/a
ops$tkyte@ORA920> set termout off

a@ORA920> set termout on
a@ORA920>
a@ORA920> create table t1 ( x int );
 
Table created.
 
a@ORA920>
a@ORA920> exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING('A',TRUE);
 
PL/SQL procedure successfully completed.
 
a@ORA920>
a@ORA920> create table t2 ( x int );
 
Table created.
 
a@ORA920>
a@ORA920> select table_name, monitoring from user_tables;
 
TABLE_NAME                     MON
------------------------------ ---
T1                             YES
T2                             NO
 
a@ORA920>
a@ORA920>
a@ORA920> begin dbms_stats.gather_schema_stats(
  2       ownname          => user,
  3       options          => 'GATHER AUTO',
  4       estimate_percent => dbms_stats.auto_sample_size,
  5       method_opt       => 'FOR ALL INDEXED COLUMNS SIZE 1',
  6       degree           => 3
  7     );
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
a@ORA920>
a@ORA920> create table t3 ( x int );
 
Table created.
 
a@ORA920> select table_name, monitoring, num_rows from user_tables;
 
TABLE_NAME                     MON   NUM_ROWS
------------------------------ --- ----------
T1                             YES          0
T2                             NO           0
T3                             NO
 
stats on both tables -- no monitoring tho on t2 and t3

a@ORA920>
a@ORA920> begin dbms_stats.gather_schema_stats(
  2       ownname          => user,
  3       options          => 'GATHER AUTO',
  4       estimate_percent => dbms_stats.auto_sample_size,
  5       method_opt       => 'FOR ALL INDEXED COLUMNS SIZE 1',
  6       degree           => 3
  7     );
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
a@ORA920>
a@ORA920> select table_name, monitoring, num_rows from user_tables;
 
TABLE_NAME                     MON   NUM_ROWS
------------------------------ --- ----------
T1                             YES          0
T2                             NO           0
T3                             NO           0

shows it picks up all tables -- but no monitoring
 
a@ORA920> exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING('A',TRUE);
 
PL/SQL procedure successfully completed.
 
a@ORA920> select table_name, monitoring, num_rows from user_tables;
 
TABLE_NAME                     MON   NUM_ROWS
------------------------------ --- ----------
T1                             YES          0
T2                             YES          0
T3                             YES          0
 
 

new tables - cleared  September 18, 2003
Reviewer:  A reader

Tom, great illustration. Thanks for your help.
Sravan 

Spendid info !  October 28, 2003
Reviewer:  horace  from UK

Thanks for the excellent tutorial

We have tables where all the columns are indexed.  We use dbms_stats with 
cascade=true to analyze the tables and indexes.

The default for method_opt is 'for all columns size 1' with the other options 
being.....

o for all columns  
o for all indexed columns
o for all hidden columns
o for columns <list>

Is there a way of analyzing the table and indexes without gathering 
histographical stats using dbms_stats, i.e., the equivalent of 'analyze table 
table_name estimate statistics;' 

Followup:
method_opt => null

 

  October 29, 2003
Reviewer:  Dillip  from CA USA

Hi Tom,

If I collect the stats using the following command, is there something missing 
for the CBO to generate a good execution plan ? Or what is the best command to 
collect the stats of a table (including indexes, histograms) so that the 
optmizer gets all the information that is needed. 

execute dbms_stats.gather_table_stats(ownname => 'TEST', 
                      tabname => 'TEST', 
                      method_opt => 'FOR ALL INDEXED COLUMNS', 
                      granularity => 'ALL', 
                      cascade => TRUE, 
                      degree => DBMS_STATS.DEFAULT_DEGREE) 

Followup:
that is a good "general purpose" one.  many times the histograms are in fact not 
necessary -- that depends on your data and queries.   

DBMS_STATS was very useful for me..Thanks  November 07, 2003
Reviewer:  M S Reddy  from USA.

Tom
  what happens if we run an explain plan while we are running dbms_stats to 
gatheer a table statistics.i can see that the plan changes even before the 
procedure is completed.does this do any harm or effect my gathered statistics on 
that table.what happens if i run a simple select count(*) on that table while we 
are gathering stats.
i expect that this should not cause any harm,but just wanted to confirm it from 
you.
Thanks,
M S Reddy. 

Followup:
stats are consulted to generate the plan, then they are not used.  so, it is 
really only during parse that anything happens here -- the parse will read 
whatever is in the data dictionary at that point in time.

no harm. 

  December 15, 2003
Reviewer:  A reader

  

SKEWONLY usage  December 16, 2003
Reviewer:  A reader

Hi Tom, recently i received a tip from Burleston
at the end shows

--*******************************************************
-- SKEWONLY option ? Detailed analysis
--
-- Use this method for a first-time analysis 
-- for skewed indexes
-- This runs a long time because all indexes are examined
--*******************************************************
 
begin
   dbms_stats.gather_schema_stats(
      ownname          => 'SCOTT', 
      estimate_percent => dbms_stats.auto_sample_size, 
      method_opt       => 'for all columns size skewonly', 
      degree           => 7
   );
end;
/

My question is
If I'm using bind variables and get that kind of statistics
in theory I'm not going to get benefit from histogram 
statistics
Or I'm wrong and this information helps the optimizer in some way? 

Followup:
there is bind variable peeking that happens 

there will be times you have constants.

so yes, they can and do come into play.

and it is funny his comment says "indexes" -- but this call doesn't even gather 
stats on indexes, cascade defaults to false -- the for all columns is about the 
table columns, not indexed columns..  it is not just looking at indexed columns 
at all (which is OK, just the comment is muffed up)

using SIZE SKEWONLY  means that when you collect histograms with the SIZE option 
set to SKEWONLY, we collect histogram data in memory for all specified columns 
(if you do not specify any, all columns are used). Once an "in-memory" histogram 
is computed for a column, it is stored inside the data dictionary only if it has 

"popular" values (multiple end-points with the same value which is what we 
define by "there is skew in the data")

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5792247321358


 

More information  December 17, 2003
Reviewer:  A reader

Thanks Tom
Sorry I should had gave you the link
This is the tip 
http://www.dba-oracle.com/oracle_tips_skewonly.htm
    Something funny is that you Burleston has in this page
    
http://www.dba-oracle.com/articles.htm#tips
    the tips untils january 24 2004 ;)

Let me clear my question, it was
Even if I only use bind variables (select a from b where c=:1), not 
constants(select a from b where c='hi'). 
As I understand, maybe wrongly, the additional information got with skewonly 
helps to the optimizer.
I'm right or not?.

Or the question in other way.
If I don't use constant values in the where clauses, required to get advantage 
of histograms, will I benefit of using skewonly as is explained in the article?

Thanks Tom :)
 

Followup:
BIND VARAIBLE PEEKING -- yes, histograms are relevant even with binds.





 

  December 18, 2003
Reviewer:  A reader

Thanks a lot tom :) 

a quote  December 18, 2003
Reviewer:  A reader

If you don't have any idea what is bind variable peeking,

"In summary, bind variable peeking in Oracle 9i does not help us when we need 
different execution plans for different values of a bind variable. Bind variable 
peeking allows the optimizer to take the value of a bind variable into account 
when first generating an execution plan for a SQL statement. If we know that the 
data distribution is heavily skewed and that future executions of the SQL 
statement may involve different data distributions for the bind variable value, 
we must still resort to using literals to get the best performance."

from here 
http://www.dbspecialists.com/specialists/specialist2003-11.html
:)  

  December 29, 2003
Reviewer:  Dilllip  from CA USA

Tom,

With the following command the column statisticts (NUM_DISTINCT,NUM_NULLS and 
DENSITY) for the non-indexes columns are not collected, is there a way to have 
the column statistics for all the columns and histograms for the indexed columns 
in a single dbms_stats command ?

execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'TEST', 
method_opt => 'FOR ALL INDEXED COLUMNS', granularity => 'ALL', cascade => TRUE, 
degree => DBMS_STATS.DEFAULT_DEGREE) 

Followup:
those are gathered as part of histogram processing -- column statistics & 
histograms are sort of synonymous here. 

  December 30, 2003
Reviewer:  Dillip  from CA USA

I am not clear how they are synonymous. Can you pls explain ? Pls see the test 
case I have provided, I do not see any stats for non-indexed columns as method 
of stat collection is only indexed columns, when I collect stats using the 
analyze command, I see stats for all the columns for the table and then I 
collect histograms for the indexes columns separately.

SQL> create table test as select * from dba_objects;

Table created.

SQL> create index test_idx on test(owner, object_name);

Index created.

SQL> create index test_idx1 on test(created);

Index created.

SQL> execute dbms_stats.gather_table_stats(ownname => 'ORAPERF', tabname => 
'TEST', method_opt => 'FOR ALL INDEXED COLUMNS', granularity => 'ALL', cascade 
=> TRUE, degree => DBMS_STATS.DEFAULT_DEGREE);

PL/SQL procedure successfully completed.

SQL> @stat test

Table ....

TABNAME              LAST_ANALYZED         NUM_ROWS     BLOCKS AVG_ROW_LEN 
SAMPLE_SIZE
-------------------- ------------------- ---------- ---------- ----------- 
-----------
TEST                 12-30-2003 18:55:36      23429        301         100       
23429

Columns(s) ....

COLUMN_NAME                    LAST_ANALYZED       NUM_DISTINCT  NUM_NULLS       
  DENSITY NUM_BUCKETS
------------------------------ ------------------- ------------ ---------- 
--------------- -----------
OWNER                          12-30-2003 18:55:36            5          0       
.00002134           4
OBJECT_NAME                    12-30-2003 18:55:36        22055          0       
.00004851          75
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED                        12-30-2003 18:55:36         3257          0       
.00050874          75
LAST_DDL_TIME
TIMESTAMP
STATUS
TEMPORARY
GENERATED
SECONDARY

Index(s) ....

INDNAME            LAST_ANALYZED            LFBLK      DKEYS    ALVLBLK    
AVGDBLK         CF     BLEVEL
------------------ ------------------- ---------- ---------- ---------- 
---------- ---------- ----------
TEST_IDX           12-30-2003 18:55:41        113      23062          1          
1        414          1
                                                                      1          
1
TEST_IDX1          12-30-2003 18:55:42         63       3257          1          
1       3897          1

 

Followup:
you don't show your analyze, but analyze by default gets histograms:

ops$tkyte@ORA9IR2> create table t as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> analyze table t compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*) from user_tab_histograms where table_name = 
'T';
 
  COUNT(*)
----------
        16


so, your "collection of them after the fact" isn't useful.  they are already 
there, for all columns.




 

  December 30, 2003
Reviewer:  Dillip  from CA USA

Pls see this, the two entries with end point as 0, 1 are collected by default. 

Pls see below and comment

SQL> analyze table test delete statistics;

Table analyzed.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> @stat test

Table ....

TABNAME              LAST_ANALYZED         NUM_ROWS     BLOCKS AVG_ROW_LEN 
SAMPLE_SIZE
-------------------- ------------------- ---------- ---------- ----------- 
-----------
TEST                 12-30-2003 20:57:31      23429        301          90       
23429

Columns(s) ....

COLUMN_NAME                    LAST_ANALYZED       NUM_DISTINCT  NUM_NULLS       
  DENSITY NUM
------------------------------ ------------------- ------------ ---------- 
--------------- ---
OWNER                          12-30-2003 20:57:31            5          0       
.20000000
OBJECT_NAME                    12-30-2003 20:57:31        22055          0       
.00004534
SUBOBJECT_NAME                 12-30-2003 20:57:31            2      23378       
.50000000
OBJECT_ID                      12-30-2003 20:57:31        23429          0       
.00004268
DATA_OBJECT_ID                 12-30-2003 20:57:31        18441       4952       
.00005423
OBJECT_TYPE                    12-30-2003 20:57:31           22          0       
.04545455
CREATED                        12-30-2003 20:57:31         3257          0       
.00030703
LAST_DDL_TIME                  12-30-2003 20:57:31         2110          0       
.00047393
TIMESTAMP                      12-30-2003 20:57:31         3471          0       
.00028810
STATUS                         12-30-2003 20:57:31            1          0      
1.00000000
TEMPORARY                      12-30-2003 20:57:31            2          0       
.50000000
GENERATED                      12-30-2003 20:57:31            2          0       
.50000000
SECONDARY                      12-30-2003 20:57:31            1          0      
1.00000000

Index(s) ....

INDNAME            LAST_ANALYZED            LFBLK      DKEYS    ALVLBLK    
AVGDBLK         CF
------------------ ------------------- ---------- ---------- ---------- 
---------- ----------
TEST_IDX           12-30-2003 20:57:31        113      23062          1          
1        305
                                                                      1          
1
TEST_IDX1          12-30-2003 20:57:31         63       3257          1          
1       3897
SQL> @hist test.object_id

Histograms ....

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0              2
              1         187780
SQL> analyze table test compute statistics for columns object_id;

Table analyzed.

SQL> @hist test.object_id

Histograms ....

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0              2
              1            325
              2            652
              3            967
              4           1280
              5           1593
              6           1906
              7           2221
              8           2534
              9           2847
             10           3167
             11           3489
             12           3823
             13           4144
             14           4916
             15           5249
             16           5569
             17           5882
             18           7895
             19           8213
             20           8526
             21           8839
             22           9156
             23          14043
             24          14501
             25          14875
             26          15206
             27          15576
             28          15913
             29          16234
             30          16549
             31          16881
             32          17360
             33          17744
             34          18147
             35          18546
             36          19003
             37          19395
             38          19726
             39          20053
             40          20373
             41          20808
             42          21160
             43          21492
             44          21867
             45          22207
             46          22642
             47          23052
             48          23522
             49          23972
             50          52147
             51          82430
             52         102176
             53         109848
             54         128984
             55         141920
             56         181841
             57         182154
             58         182467
             59         182780
             60         183093
             61         183406
             62         183719
             63         184032
             64         184345
             65         184658
             66         184971
             67         185284
             68         185597
             69         185910
             70         186223
             71         186536
             72         186849
             73         187162
             74         187475
             75         187780

 

Followup:
so -- you HAD histograms, the analyze command gathered histograms for all 
columns.  dbms_stats can do that as well -- but that goes against your stated 
desire, hence my point that "you cannot get there from here -- to get the data 
you are asking for requires histograms!" 

  December 30, 2003
Reviewer:  Dillip  from CA USA

Let me rephrase my question then.

I want to collect default histograms (two values) for 
all columns and histograms for all indexes columns (75 buckets).

Can you let me know the dbms_stats command to do that ?  

Followup:
it takes two of them.  just like with analyze. 

ops$tkyte@ORA9IR2> create table t as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2> create index empno_idx on t(empno);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>TRUE 
);
 
PL/SQL procedure successfully completed.

that is just like "analyze table compute statistics"
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select column_name, num_distinct
  2    from user_tab_columns
  3   where table_name = 'T'
  4  /
 
COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
EMPNO                                    14
ENAME                                    14
JOB                                       5
MGR                                       6
HIREDATE                                 13
SAL                                      12
COMM                                      4
DEPTNO                                    3
 
8 rows selected.
 
ops$tkyte@ORA9IR2> select column_name, count(*)
  2    from user_tab_histograms
  3   where table_name = 'T'
  4   group by column_name
  5  /
 
COLUMN_NAME                      COUNT(*)
------------------------------ ----------
COMM                                    2
DEPTNO                                  2
EMPNO                                   2
ENAME                                   2
HIREDATE                                2
JOB                                     2
MGR                                     2
SAL                                     2
 
8 rows selected.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt => 
'for all indexed columns size 75' );
 
PL/SQL procedure successfully completed.
 
and those are you "special" histograms


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select column_name, num_distinct
  2    from user_tab_columns
  3   where table_name = 'T'
  4  /
 
COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
EMPNO                                    14
ENAME                                    14
JOB                                       5
MGR                                       6
HIREDATE                                 13
SAL                                      12
COMM                                      4
DEPTNO                                    3
 
8 rows selected.
 
ops$tkyte@ORA9IR2> select column_name, count(*)
  2    from user_tab_histograms
  3   where table_name = 'T'
  4   group by column_name
  5  /
 
COLUMN_NAME                      COUNT(*)
------------------------------ ----------
COMM                                    2
DEPTNO                                  2
EMPNO                                  14
ENAME                                   2
HIREDATE                                2
JOB                                     2
MGR                                     2
SAL                                     2
 
8 rows selected.
 
 

  December 30, 2003
Reviewer:  Dillip  from CA USA

Tom, Thanks a lot. So there is no single command to do this
in dbms_stats (that was my initial question).

Thanks again for the clarification. 

  January 23, 2004
Reviewer:  Dillip  from CA USA

Tom, 

I have one more question, if I use these two commands, will the second command 
again collect the table stats ?
When I test I see the timestamp for the LAST_ANALYZED is changing, but not sure 
if it is collecting the stats again ? If it's going to collect the table stats 
again is there a way not to collect the without using 
dbms_stat.gather_index_stats ?

(1)
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'TEST');

(2)
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'TEST', 
method_opt => 'FOR ALL INDEXED COLUMNS', cascade => TRUE);
 

Followup:
table stats are always gathered with dbms_stats.gather_table_stats

it can additionally gather histograms and indexes.


you cannot use "gather table stats" (which by its very name says something!) 
without gathering table stats.  if you only want index stats, well, you'd use 
that api call. 

pl/sql numeric error  February 17, 2004
Reviewer:  A reader

Hi

I have following procedure (part of a package)

PROCEDURE ANALYZE_DAILY
   AS
      type array is table of varchar2(256);
      l_schema             array          := array('LSC', 'PERFSTAT');
      l_weekday            number;
      l_degree             number         := 1;
      l_percent            number         := 20;
      l_table_name         array;
      l_columns            array;
      l_monitoring         array;
      l_partitioned        array;
      l_last_analyzed      array;      
      l_objlist            dbms_stats.objectTab;
      l_granularity        varchar2(10)   := 'DEFAULT';
      l_method             varchar2(50)   := 'FOR ALL INDEXED COLUMNS SIZE 75';  
    
   BEGIN
      execute immediate 'alter session set nls_territory = ''SPAIN''';
      execute immediate 'alter session set sort_area_size = 33554432';
      l_weekday := to_char(sysdate, 'D');
      if l_weekday in (4) then
            for i in 1 .. l_schema.count
            loop
                  select a.table_name, b.columns, a.monitoring, a.partitioned, 
a.last_analyzed
                    bulk collect into l_table_name, l_columns, l_monitoring, 
l_partitioned, l_last_analyzed
                    from dba_tables a, dbstat_tab b
                   where a.owner = b.owner(+)
                     and a.table_name = b.table_name(+)
                     and a.owner = l_schema(i);
                  dbms_stats.gather_schema_stats(OWNNAME => l_schema(i),
                                                 OPTIONS => 'LIST STALE',
                                                 OBJLIST => l_objlist);
                  for x in l_table_name.first..l_table_name.last
                  loop
                        if l_monitoring(x) = 'NO' then
                           execute immediate 'alter table 
'||l_schema(i)||'.'||l_table_name(x)||' monitoring';
                        end if;
                        if l_last_analyzed(x) is NULL then
                           analyze_table(p_owner       => l_schema(i), 
                                         p_table       => l_table_name(x), 
                                         p_percent     => l_percent,
                                         p_method      => 'FOR COLUMNS 
'||l_columns(i)||' SIZE 75', 
                                         p_granularity => l_granularity,
                                         p_degree      => l_degree);
                        end if;                                      
                  end loop;
                  for y in 1..l_objlist.count
                  loop
                        analyze_table(p_owner       => l_schema(i), 
                                      p_table       => l_objlist(y).objname, 
                                      p_percent     => l_percent,
                                      p_method      => 'FOR COLUMNS 
'||l_columns(i)||' SIZE 75', 
                                      p_granularity => l_granularity,
                                      p_degree      => l_degree);
                  end loop;
            end loop;
      elsif l_weekday = 7 then
            for i in 1 .. l_schema.count
            loop
                  analyze_schema(p_owner       => l_schema(i), 
                                 p_percent     => l_percent, 
                                 p_method      => l_method,
                                 p_granularity => l_granularity,
                                 p_degree      => l_degree);
            end loop;
      else
            null;
      end if; 
      END ANALYZE_DAILY;

it works well, however if I change

for y in 1..l_objlist.count

to

for y in l_objlist.first..l_objlist.last

I get an error saying 

exec pkg_dbstat.analyze_daily;
BEGIN pkg_dbstat.analyze_daily; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYSTEM.PKG_DBSTAT", line 112
ORA-06512: at line 1

However it does what I need, analyze the tables.

What is the difference between 

for y in 1..l_objlist.count
and
for y in l_objlist.first..l_objlist.last


??? 

Followup:
if l_objlist is "empty"

- count will be 0
- but first and last will be null

ops$tkyte@ORA920PC> begin
  2    dbms_output.put_line( 'before loop' );
  3    for i in 1 .. 0
  4    loop
  5          dbms_output.put_line( 'in loop' );
  6    end loop;
  7    dbms_output.put_line( 'after loop' );
  8  end;
  9  /
before loop
after loop
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> begin
  2    dbms_output.put_line( 'before loop' );
  3    for i in null..null
  4    loop
  5          dbms_output.put_line( 'in loop' );
  6    end loop;
  7    dbms_output.put_line( 'after loop' );
  8  end;
  9  /
before loop
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 3


You cannot "loop" with NULL -- that is the error.
 

reg table monitoring  March 19, 2004
Reviewer:  sam  from NJ, USA

Tom,

How much of a overhead a table monitoring has on a table??

Is it advisable to have table monitoring on ??
Both on OLTP & DSS systems.

Or, if we know on our system , a order entry table has lot's of insert/update 
etc. so let's not monitor that.(generate stats by default , since we know it's 
changed a lot-  10%  may be )
Monitor other tables where the changes are random.
Is that good approach?


  

Followup:
pretty much nill overhead.

in 10g it'll actually be turned on for you pretty much. 

Delete Stats  March 31, 2004
Reviewer:  A reader

In what cases should I delete statistics ? What is it useful for ? 

Followup:
you generally never need it.  there for completeness.  I use it for testing (get 
rid of existing, gather in a different fashion). 

confused at analyze  April 01, 2004
Reviewer:  Jerry  from US

Tom, 

This site is really a wonderland.  Thank you so much for your hard work.  

A follow up on the analyze question.  I found that different analyze options can 
bring totally different results.  In my company we run analyze like this:

exec dbms_stats.gather_table_stats (ownname=>'XYZ',tabname=> 
'EMPLOYEES',estimate_percent=>25,method_opt=> 'FOR ALL 
COLUMNS',degree=>4,cascade=>TRUE ); 

It runs for all user tables.  
My first question is why we need 'FOR ALL COLUMNS'.  At most, 'for all indexed 
columns' should be enought, right?

Also If I run analyze using 'for all column' or using 'for all indexed columns' 
in one database, one important query ran really slow because the execution plan 
does not utilize a certain index.  If I delete the stats, then run analyze 
without the method_opt, just

exec dbms_stats.gather_table_stats (ownname=>'XYZ',tabname=> 
'EMPLOYEES',estimate_percent=>25,degree=>4,cascade=>TRUE ); 

Then the query uses the index and comes back fast.

However, the same analyze options tested in another database with exactly same 
table/views but different data showed no different in results.

I looked at the query.  It's actually fairly simple, one subquery then 
outer-join.  I don't think there is any other way I rewrite it to make it 
simpler.

Is the analyze method the issue here?  How should I go about to diagnose this 
type of issue?

Thank you!
 

Followup:
it doesn't run for all usre tables -- it should only run for one?

you'd have to tell ME why you need or want for all columns.  In a DW, it can be 
relevant.  In a transactional system -- probably not.

that dbms_stats without the method opt is using for 'all columns size 1' by 
default.

I'd need an example to look at. 

  April 02, 2004
Reviewer:  Jerry

This is an internal application we build.  Because we don't know what's data 
would be like, there are lots of indexes built, on all foreign keys, all id 
columns.  Many columns can have its own index and also be part of another 
indexed.  Also the analyze is scripted and histogram info is collect for all 
indexed columns.

The application is primarily OLTP but also hosts some DW types of queries, sort 
of hybrid.  Ugly but for history and cost reason, not much can be done.

As for the query, please see below:
sql>analyze table MAPPING delete statistics;

Table analyzed.

sql>exec dbms_stats.gather_table_stats (ownname=>'TMP',tabname=> 'MAPP'
ING',estimate_percent=>25,method_opt=> 'FOR ALL COLUMNS',degree=>4,cascade=>TRUE 
);  

PL/SQL procedure successfully completed.

sql>explain plan for
  2  select * from shared;

Explained.

sql>@utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
-------------------

---------------------------------------------------------------------------------
------------------
| Id  | Operation                  |  Name                        | Rows  | 
Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------
------------------
|   0 | SELECT STATEMENT           |                              |   103K|    
85M|       |   177K|
|   1 |  VIEW                      | SHARED                       |   103K|    
85M|       |   177K|
|   2 |   SORT UNIQUE              |                              |   103K|    
12M|    27M|   177K|
|*  3 |    HASH JOIN               |                              |   103K|    
12M|       |   175K|
|   4 |     VIEW                   |                              |  1285 |  
7710 |       |   161 |
|*  5 |      FILTER                |                              |       |      
 |       |       |
|   6 |       SORT GROUP BY        |                              |  1285 | 
12850 |       |   161 |
|*  7 |        INDEX FAST FULL SCAN| DEVICE_UNIQ                  | 49913 |   
487K|       |    16 |
|   8 |     NESTED LOOPS OUTER     |                              | 87717 |    
10M|       |   175K|
|   9 |      TABLE ACCESS FULL     | ALLOCATION                   | 87717 |  
6852K|       |   240 |
|* 10 |      VIEW PUSHED PREDICATE |                              |     1 |    
43 |       |     2 |
|* 11 |       HASH JOIN            |                              |   190 |  
8170 |  1736K|   104 |
|  12 |        TABLE ACCESS FULL   | HOSTDEVICE                   | 41204 |  
1247K|       |    74 |
|* 13 |        INDEX RANGE SCAN    | MAPPING_IDX1                 |   190 |  
2280 |       |     2 |
---------------------------------------------------------------------------------
------------------


31 rows selected.


sql>analyze table MAPPING delete statistics;

Table analyzed.

sql>exec dbms_stats.gather_table_stats (ownname=>'TMP',tabname=> 'MAPP
ING',estimate_percent=>25,degree=>4,cascade=>TRUE );  

PL/SQL procedure successfully completed.


sql>delete from plan_table;

14 rows deleted.

sql>commit;

Commit complete.

sql>explain plan for 
  2  select * from shared;

Explained.

sql>@utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
-------------------

---------------------------------------------------------------------------------
-------------------
| Id  | Operation                         |  Name                        | Rows  
| Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------
-------------------
|   0 | SELECT STATEMENT                  |                              |   
103K|    85M|       |   177K|
|   1 |  VIEW                             | SHARED                       |   
103K|    85M|       |   177K|
|   2 |   SORT UNIQUE                     |                              |   
103K|    12M|    27M|   177K|
|*  3 |    HASH JOIN                      |                              |   
103K|    12M|       |   175K|
|   4 |     VIEW                          |                              |  1285 
|  7710 |       |   161 |
|*  5 |      FILTER                       |                              |       
|       |       |       |
|   6 |       SORT GROUP BY               |                              |  1285 
| 12850 |       |   161 |
|*  7 |        INDEX FAST FULL SCAN       | DEVICE_UNIQ                  | 49913 
|   487K|       |    16 |
|   8 |     NESTED LOOPS OUTER            |                              | 87717 
|    10M|       |   175K|
|   9 |      TABLE ACCESS FULL            | ALLOCATION                   | 87717 
|  6852K|       |   240 |
|* 10 |      VIEW PUSHED PREDICATE        |                              |     1 
|    43 |       |     2 |
|  11 |       NESTED LOOPS                |                              |    12 
|   516 |       |    14 |
|* 12 |        INDEX RANGE SCAN           | MAPPING_IDX1                 |    12 
|   144 |       |     2 |
|  13 |        TABLE ACCESS BY INDEX ROWID| HOSTDEVICE                   |     1 
|    31 |       |     1 |
|* 14 |         INDEX UNIQUE SCAN         | PK_HOSTDEVICE                |     1 
|       |       |       |
---------------------------------------------------------------------------------
-------------------

32 rows selected.


The second query runs very fast.  The first one did not come back after 4-5 
hours.  There is no data change on the static testing environment.  It seems 
that oracle pick the wrong execution plan when given too much information.  The 
mapping table are all id columns joining several tables together
 

Statistics for database.  April 20, 2004
Reviewer:  Kashif  from Reston, VA

Hi Tom,

You've mentioned in some earlier posts that you prefer gathering stats on a 
schema-by-schema basis instead of on the entire database. I've currently 
scheduled statistics to be gathered for the entire database for only stale 
objects. So the command I use is similar to the following:

   dbms_stats.gather_database_stats ( method_opt  => 'for all indexed columns',
                                      granularity => 'DEFAULT',
                                      cascade     => TRUE,
                                      options     => 'GATHER STALE',
                                      objlist     => StaleObjectsArray );

No chance there on stats being generated inadvertently on the SYS or SYSTEM or 
any other schema, unless MONITORING is turned on for each of the individual 
tables. We have a DW here, and the load of the data into the DW happens at the 
beginning of the month, and the schemas I have turned monitoring on all get 
loaded at the same time, i.e. at the beginning of the month. I've schedule the 
stats to be gathered at the middle and the end of the month. 

I like the ability to turn MONITORING on any table in the database and be done 
with it, instead of having to schedule the individual schema, although 
admittedly we don't really bring schemas online frequently at all (we have two 
schemas being monitored at the moment). Is this a feasible scenario for 
gathering stats at the database level? Any insight will be useful. Thanks.

Kashif

P.S. In asking you the question, I sort of reached my own conclusion, i.e. just 
gather the stats per schema, but I'm still curious to know your thoughts. 

Followup:
everything is feasible - i look at schemas as "independent entities with their 
own schedules"  -- IN GENERAL.

in your unique set of circumstances, this is not the case.  so, what you are 
doing makes sense for you. 

HIDDEN COLUMNS  May 12, 2004
Reviewer:  tHIRU

Tom,

Pasting your earlier comments on hidden columns:

o for all hidden columns - gather histograms on columns you cannot see, useful 
if using the object relational features which incorporate tons of hidden columns 
in your objects.

We have high implementation of object relational features. Is it ok just to go 
with
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> NULL, TABNAME => 'T1Tab', CASCADE 
=> TRUE
,METHOD_OPT => 'FOR ALL HIDDEN COLLUMNS');

AND THEN REFRESH WITH:

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'T1Tab', 
CASCADE=> FALSE, 
METHOD_OPT => 'FOR ALL HIDDEN COLUMNS');

WILL THIS TAKE CARE OF ALL INDEXED COLUMNS TOO?

 

Followup:
Looks like I got that one wrong in researching that further.  Seems to not seek 
out object hidden columns, but rather hidden virtual columns from function based 
indexes:

ops$tkyte@ORA9IR2> create table t ( x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t
  2  select rownum, rownum/2 from all_users;
 
43 rows created.
 
ops$tkyte@ORA9IR2> create index t_idx on t( (x+y)/2 );
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx2 on t(x);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2    DBMS_STATS.GATHER_TABLE_STATS
  3    (OWNNAME=> NULL,
  4     TABNAME => 'T',
  5     CASCADE => TRUE,
  6     METHOD_OPT => 'FOR ALL hidden COLUMNS');
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select table_name, num_rows from user_tables where table_name 
= 'T';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T                                      43
 
ops$tkyte@ORA9IR2> select table_name, column_name, count(*) from 
user_tab_histograms
  2  where table_name = 'T' group by table_name, column_name;
 
TABLE_NAME                     COLUMN_NAME    COUNT(*)
------------------------------ ------------ ----------
T                              SYS_NC00003$         43
 
ops$tkyte@ORA9IR2> select table_name, index_name, num_rows from user_indexes 
where table_name = 'T';
 
TABLE_NAME                     INDEX_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
T                              T_IDX                                  43
T                              T_IDX2                                 43
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.delete_table_stats( user, 'T' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2    DBMS_STATS.GATHER_TABLE_STATS
  3    (OWNNAME=> NULL,
  4     TABNAME => 'T',
  5     CASCADE => TRUE,
  6     METHOD_OPT => 'FOR ALL indexed COLUMNS');
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select table_name, num_rows from user_tables where table_name 
= 'T';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T                                      43
 
ops$tkyte@ORA9IR2> select table_name, column_name, count(*) from 
user_tab_histograms
  2  where table_name = 'T' group by table_name, column_name;
 
TABLE_NAME                     COLUMN_NAME    COUNT(*)
------------------------------ ------------ ----------
T                              X                    43
T                              SYS_NC00003$         43
 
ops$tkyte@ORA9IR2> select table_name, index_name, num_rows from user_indexes 
where table_name = 'T';
 
TABLE_NAME                     INDEX_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
T                              T_IDX                                  43
T                              T_IDX2                                 43


but -- why would you want to stop analyzing the indexes? 

  May 13, 2004
Reviewer:  A reader

 
The refresh option of CASCADE =>FALSE I got it from Metalink. So my guess is it 
speeds
up the refresh process. So you recommend that I give CASCADE => TRUE even the 
second time
and "for all indexed columns". Right Tom?  One more thing just to know that I am 
doing right:
Everyday in the morning the tables get loaded fresh through a batch job. And I 
have planned to
do the initial "gather_stats" after the load and then the refresh in the 
afternoon when the
database is hot. Is this a suggested way or are there any other tips to do this. 
Also will the 
users get a performance degradation while the stats are being refreshed? Thanks. 

Followup:
I

o analyze tables first time after creation
o alter them to be monitoring
o gather STALE after that (this is what 10g automates actually)


in that fashion, only objects that need to be regathered will be (stale).


do you change the data so dramatically during the morning/early afternoon that 
you would need to refresh stats all over again? (they will be like a big batch 
job, that is the impact they'll have -- that and when they complete -- they will 
by default invalidate cursors so you'll have a hard parse bump as well) 

thiru  May 13, 2004
Reviewer:  A reader

Yes, the data changes though the no of records dont' jump up much.  I would 
follow
your steps to implement stats. Is  dbms_stats.ALTER_SCHEMA_TAB_MONITORING the 
right
procedure to use instead of doing for each table? Almost all the tables are 
built on
object types. Also if you can highlight the steps to gathere STALE, it would 
immensely help.  

Followup:
you can use that procedure or just alter tables, whichever you like better.

a gather stale example:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1154434873552#8727886618633

  May 13, 2004
Reviewer:  Thiru

Thanks for the input. One last thing:

a. During "GATHER STALE" which option is better ->"FOR ALL COLUMNS" or
"FOR ALL INDEXED COLUMNS" or "FOR ALL COLUMNS SIZE 1" . How to determine
the option to be used.
DECLARE jobno number;
BEGIN
    DBMS_JOB.SUBMIT(job => jobno,
    what =>'DBMS_STATS.GATHER_SCHEMA_STATS (''TEST_USER'',
    NULL,FALSE,''FOR ALL COLUMNS'',NULL,''DEFAULT'',TRUE,NULL,NULL,''GATHER 
STALE'',''LIST'' ); ',
    next_date => SYSDATE, interval => 'Sysdate + 1/24');
END;


b. Will the cursors be invalidated for tables that are refreshed with "GATHER 
STALE". In 
that case, the first few queries would take a longer time than before. Right? 
Any way
to avoid this?

c. Any performance issues while these statistics run during heavy load.

I am reading your book "Effective Oracle..." It is excellent. 

Followup:
a) use what you used in the first place?  on the initial gather? or just use 
"repeat"

b) after you gather stats, plans need to be re-evaluated, so sure.

to avoid it, don't gather stats, don't generate new plans :)

there is a no_invalidate parameter, but if you don't plan on creating new plans, 
why bother gathering? 

is it only for statistics gathering?  May 15, 2004
Reviewer:  Raaghid  from India

Is Analyze used only for statistics gathering? or will it increase performance 
of the query, if the tables used in the query analyzed. In nutshell, what are 
the usages of analyze 

Followup:
Please see the admin and performance guides.

analyze is for gathering statistics and/or validating structures.

it is predominately to be used for the latter.

 

  May 31, 2004
Reviewer:  A reader

1) It's not clear to me in the docs, but I see in your example that the default 
for "alter table t compute/estimate statistics" is:

for table
for all columns
for all indexed columns

right ? 

2) What is the default sample for the estimate clause ? 

Followup:
1) yes.

2) 
....
SAMPLE Specify the amount of data from the analyzed object Oracle should sample 
to estimate statistics. If you omit this parameter, then Oracle samples 1064 
rows.
...... 

analyze_schema gives better plan than gather_schema_stats ??  June 03, 2004
Reviewer:  A reader  from New Jersey

Hi Tom,

I'm in the process of replacing analyze_schema scripts with gather_schema_stats.

Earlier someone stated that they saw a more efficient plan with 'analyze' than 
with gather_schema_stats. I'm observing something similar but I will let your 
expertise decide which plan is "better", owing to the fact that I know very 
little about CBO. 

Why does gather_schema_stats results in full table scans? Is it because the 
tables are pretty small? Although, request table does have 107K rows.

Thanks so much, as always.

Here are the facts:

$ cat j1.sql

select ROW_NUM, REQ_ID, LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from (
select rownum as ROW_NUM, REQ_ID, LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from
(
  select r.REQ_ID as REQ_ID,
  r.last_updated as LAST_UPDATED,
  r.REQ_STATUS as REQ_STATUS,
  dc.ACTION_DESC as ACTION_DESC,
  dc.CMD_DESC as CMD_DESC,
  dc.GUI_CMD_DESC as GUI_CMD_DESC,
 dc.oma as OMA,
  u.first_name||' '||u.last_name as USER_NAME
  from REQ r, DEV_COM dc, users u
  where r.dev_id = 6
  and r.cmd_id = dc.cmd_id
  and r.user_id = u.user_id
  order by 1 desc
  )
  )
where ROW_NUM between 1 and 5
;

**************************************************************

SQL> select count(*) from users;
  COUNT(*)
----------
        29

SQL> select count(*) from dev_com;

  COUNT(*)
----------
       226

SQL> select count(*) from req;

  COUNT(*)
----------
    107520

**************************************************************

exec dbms_stats.delete_schema_stats(user);

exec dbms_stats.gather_schema_stats (ownname=>USER, estimate_percent=>dbms_stats
.auto_sample_size, cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO
');

select count(*) from user_tab_histograms where table_name='REQ';
  COUNT(*)
----------
       349


SQL> @j1
Elapsed: 00:00:04.92

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1710 Card=45723 Byte
          s=6767004)

   1    0   VIEW (Cost=1710 Card=45723 Bytes=6767004)
   2    1     COUNT
   3    2       VIEW (Cost=1710 Card=45723 Bytes=6172605)
   4    3         SORT (ORDER BY) (Cost=1710 Card=45723 Bytes=6675558)
   5    4           HASH JOIN (Cost=675 Card=45723 Bytes=6675558)
   6    5             TABLE ACCESS (FULL) OF 'USERS' (Cost=10 Card=29
          Bytes=1102)

   7    5             HASH JOIN (Cost=664 Card=45723 Bytes=4938084)
   8    7               TABLE ACCESS (FULL) OF 'DEV_COM' (Cost=
          2 Card=226 Bytes=12430)

   9    7               TABLE ACCESS (FULL) OF 'REQ' (Cost=661 Car
          d=45723 Bytes=2423319)



exec dbms_stats.delete_schema_stats(user);


exec dbms_utility.analyze_schema(user,'compute');

select count(*) from user_tab_histograms where table_name='REQ';
  COUNT(*)
----------
       226


SQL> @j1
Elapsed: 00:00:01.89

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=188 Card=1991 Bytes=
          294668)

   1    0   VIEW (Cost=188 Card=1991 Bytes=294668)
   2    1     COUNT
   3    2       VIEW (Cost=188 Card=1991 Bytes=268785)
   4    3         SORT (ORDER BY) (Cost=188 Card=1991 Bytes=157289)
   5    4           HASH JOIN (Cost=159 Card=1991 Bytes=157289)
   6    5             TABLE ACCESS (FULL) OF 'DEV_COM' (Cost=2
          Card=226 Bytes=8814)

   7    5             HASH JOIN (Cost=156 Card=1991 Bytes=79640)
   8    7               TABLE ACCESS (FULL) OF 'USERS' (Cost=10 Card=2
          9 Bytes=377)

   9    7               TABLE ACCESS (BY INDEX ROWID) OF 'REQ' (Co
          st=145 Card=1991 Bytes=53757)

  10    9                 INDEX (RANGE SCAN) OF 'DUP_REQ_2' (NON-U
          NIQUE) (Cost=7 Card=1991)
 

Followup:
always gotta have the tkprofs.

I would recommend always to write queries like that as:


select /*+ FIRST_ROWS */ * 
  from ( select /*+ FIRST_ROWS */ a.*, rownum r
           from (YOUR_FIRST_ROWS_QUERY_HERE) A
          where rownum <= :max)
 where r >= :min;

using between is way sub-optimal.  If you have an index on r(dev_id,req_id) -- 
this could be quite speedy indeed.
 

Strategy for Gather Stat.  June 03, 2004
Reviewer:  Goh Seong Hin  from Malaysia

Dear Tom,

   What is the recommended strategy to gather the statistic in OLTP environment 
? 1 huge table (10million records) is very active in DML (insert, update and 
delete) throughout the day and the rest of the tables is not very active 
(probably 200-500 records updated/inserted/deleted per day).
Assuming that we are in Oracle 8i which does not have auto monitor features to 
assist us. Based on your experience, what should be the best way to gather the 
statistic. 

  How about the following strategy?
  ---------------------------------   
  Huge table -> gather statistics with estimate 5% of data every 6 hours.
  The rest of the table -> gather full statistics every sunday nite.

  What is the different between the analyze table x stimate statistics sample 5 
rows and 10 rows besides the number of rows inspected to gather the statistic. I 
tried to gather a table called emp (contains 11 rows) with 3 rows sample and 
found out that the system shows the total number of distinct in dba_tab_columns. 
Since the system is able to get the info without going through the entire table. 
Is it recommended for us to gather certain percentage of row for all of our 
tables (to shorten the gathering time).

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
EMP_NO
EMP_NAME
DEPT_NO

SQL> analyze table emp estimate statistics sample 3 rows;

Table analyzed.

SQL> select column_name, num_distinct
from dba_tab_columns
where table_name = 'EMP'  2    3
  4  /


COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
EMP_NO                                   11
EMP_NAME                                 11
DEPT_NO                                   3 


     Thanks in advance.

Rgds
SHGoh

 

Followup:
8i has alter table T monitoring and GATHER STALE with dbms_stats.

use that to identify just the tables that need to be analyzed. 10,000,000 
records without histograms won't take very long to compute -- only if you need 
histograms on many columns (doubtful, probably don't need any) would you look at 
estimate.

testing against really small things like that isn't "meaningful".  3 rows -- not 
a good test case. 

Here is the tkprof from FTS ...  June 04, 2004
Reviewer:  A reader

Hi Tom,

This is the tkprof from gather_schema_stats that's doing a FTS on REQ table:
============================================

TKPROF: Release 9.2.0.4.0 - Production on Fri Jun 4 10:35:16 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: itdb_ora_2508.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

alter session set sql_trace = true


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.04          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.04          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 433  
********************************************************************************

select default$ 
from
 col$ where rowid=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0          8          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.00       0.00          0          8          0           4

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY USER ROWID COL$ (cr=1 r=0 w=0 time=91 us)

********************************************************************************

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 
  sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, 
  spare1, spare2, avgcln 
from
 hist_head$ where obj#=:1 and intcol#=:2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute     44      0.00       0.00          0          0          0           0
Fetch       44      0.00       0.00          0         88          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       92      0.00       0.01          0         88          0           0

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

select ROW_NUM, REQ_ID, LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from (
select rownum as ROW_NUM, REQ_ID, LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from
(
  select r.REQ_ID as REQ_ID,
  r.last_updated as LAST_UPDATED,
  r.REQ_STATUS as REQ_STATUS,
  dc.ACTION_DESC as ACTION_DESC,
  dc.CMD_DESC as CMD_DESC,
  dc.GUI_CMD_DESC as GUI_CMD_DESC,
 dc.oma as OMA,
  u.first_name||' '||u.last_name as USER_NAME
  from REQUEST r, DEVICE_COMMAND dc, users u
  where r.dev_id = 6
  and r.cmd_id = dc.cmd_id
  and r.user_id = u.user_id
  order by 1 desc
  )
  )
where ROW_NUM between 1 and 5

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.18       0.17          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      4.33      11.19       6308       8746          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      4.51      11.36       6308       8746          0          10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  VIEW  (cr=4373 r=3253 w=0 time=5458161 us)
  45952   COUNT  (cr=4373 r=3253 w=0 time=5341429 us)
  45952    VIEW  (cr=4373 r=3253 w=0 time=5182836 us)
  45952     SORT ORDER BY (cr=4373 r=3253 w=0 time=5019472 us)
  45952      HASH JOIN  (cr=4373 r=3253 w=0 time=4493816 us)
     29       TABLE ACCESS FULL USERS (cr=21 r=0 w=0 time=703 us)
  45952       HASH JOIN  (cr=4352 r=3253 w=0 time=4123196 us)
    226        TABLE ACCESS FULL DEVICE_COMMAND (cr=5 r=3 w=0 time=24315 us)
  45952        TABLE ACCESS FULL REQUEST (cr=4347 r=3250 w=0 time=3698938 us)

********************************************************************************

SELECT DECODE('A','A','1','2') 
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.01       0.00          0          6          0           2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=201 us)

********************************************************************************

select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
  o.dataobj#,o.flags 
from
 obj$ o where o.obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          6          0           2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

SELECT ID ID_PLUS_EXP,PARENT_ID PARENT_ID_PLUS_EXP,LPAD(' ',2*(LEVEL-1))
  ||OPERATION||DECODE(OTHER_TAG,NULL,'','*')||DECODE(OPTIONS,NULL,'',' 
  ('||OPTIONS||')')||DECODE(OBJECT_NAME,NULL,'',' OF '''||OBJECT_NAME||'''')
  ||DECODE(OBJECT_TYPE,NULL,'',' ('||OBJECT_TYPE||')')||DECODE(ID,0,
  DECODE(OPTIMIZER,NULL,'',' Optimizer='||OPTIMIZER))||DECODE(COST,NULL,'',' 
  (Cost='||COST||DECODE(CARDINALITY,NULL,'',' Card='||CARDINALITY)
  ||DECODE(BYTES,NULL,'',' Bytes='||BYTES)||')') PLAN_PLUS_EXP,OBJECT_NODE 
  OBJECT_NODE_PLUS_EXP 
FROM
 PLAN_TABLE START WITH ID=0 AND STATEMENT_ID=:1 CONNECT BY PRIOR ID=PARENT_ID 
  AND STATEMENT_ID=:1 ORDER BY ID,POSITION


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.01       0.00          0          0          0           0
Fetch        3      0.02       0.02          0         31          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.03       0.02          0         31          0          10

Misses in library cache during parse: 2
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=3 r=0 w=0 time=239 us)
      0   CONNECT BY WITH FILTERING (cr=3 r=0 w=0 time=219 us)
      0    NESTED LOOPS  (cr=3 r=0 w=0 time=156 us)
      0     TABLE ACCESS FULL PLAN_TABLE (cr=3 r=0 w=0 time=150 us)
      0     TABLE ACCESS BY USER ROWID PLAN_TABLE (cr=0 r=0 w=0 time=0 us)
      0    HASH JOIN  (cr=0 r=0 w=0 time=0 us)
      0     CONNECT BY PUMP  (cr=0 r=0 w=0 time=0 us)
      0     TABLE ACCESS FULL PLAN_TABLE (cr=0 r=0 w=0 time=0 us)

********************************************************************************

SELECT USERENV('SESSIONID') 
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=130 us)

********************************************************************************

select text 
from
 view$ where rowid=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          1          4          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.01          1          4          0           2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY USER ROWID VIEW$ (cr=1 r=1 w=0 time=9114 us)

********************************************************************************

SELECT DISTINCT SID 
FROM
 V$MYSTAT


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          1          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.01          1          2          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE (cr=0 r=0 w=0 time=3015 us)
    248   FILTER  (cr=0 r=0 w=0 time=2347 us)
    280    FIXED TABLE FULL X$KSUMYSTA (cr=0 r=0 w=0 time=1369 us)
      1    FIXED TABLE FULL X$KSUSGIF (cr=0 r=0 w=0 time=7 us)

********************************************************************************

SELECT STATISTIC# S, NAME 
FROM
 SYS.V_$STATNAME WHERE NAME IN ('recursive calls','db block gets','consistent 
  gets','physical reads','redo size','bytes sent via SQL*Net to client',
  'bytes received via SQL*Net from client','SQL*Net roundtrips to/from 
  client','sorts (memory)','sorts (disk)') ORDER BY S


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.06          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.06          0          2          0          10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  FIXED TABLE FULL X$KSUSD (cr=0 r=0 w=0 time=730 us)

********************************************************************************

insert into sqm_alarm_config(sqm_alarm_config_id,ALARM_MSG_ID,MSG_TYPE_ID,
  ALARM_LEVEl,ALARM_LEVEL_VALUE,alarm_thresh,create_user) 
values
(102,34,251,'LAI','q' ,'10',1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.04          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.04          0          0          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  
********************************************************************************

DELETE FROM PLAN_TABLE 
WHERE
 STATEMENT_ID=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          6         12          10
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          6         12          10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  (cr=3 r=0 w=0 time=134 us)
      0   TABLE ACCESS FULL PLAN_TABLE (cr=3 r=0 w=0 time=123 us)

********************************************************************************

EXPLAIN PLAN SET STATEMENT_ID='PLUS7522' FOR select ROW_NUM, REQ_ID, 
LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from (
select rownum as ROW_NUM, REQ_ID, LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from
(
  select r.REQ_ID as REQ_ID,
  r.last_updated as LAST_UPDATED,
  r.REQ_STATUS as REQ_STATUS,
  dc.ACTION_DESC as ACTION_DESC,
  dc.CMD_DESC as CMD_DESC,
  dc.GUI_CMD_DESC as GUI_CMD_DESC,
 dc.oma as OMA,
  u.first_name||' '||u.last_name as USER_NAME
  from REQUEST r, DEVICE_COMMAND dc, users u
  where r.dev_id = 6
  and r.cmd_id = dc.cmd_id
  and r.user_id = u.user_id
  order by 1 desc
  )
  )
where ROW_NUM between 1 and 5

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.01          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.02          0          0          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  
********************************************************************************

insert into plan_table (statement_id, timestamp, operation, options,
  object_node, object_owner, object_name, object_instance, object_type,
  search_columns, id, parent_id, position, other,optimizer, cost, cardinality,
   bytes, other_tag, partition_start, partition_stop, partition_id, 
  distribution, cpu_cost, io_cost, temp_space, access_predicates, 
  filter_predicates ) 
values
(:1,SYSDATE,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,
  :20,:21,:22,:23,:24,:25,:26,:27)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     10      0.01       0.01          0          2         11          10
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.01       0.01          0          2         11          10

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 433     (recursive depth: 1)
********************************************************************************

select o.name, u.name 
from
 sys.obj$ o, sys.user$ u where obj# = :1 and owner# = user#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

SELECT ID ID_PLUS_EXP,OTHER_TAG OTHER_TAG_PLUS_EXP,OTHER OTHER_PLUS_EXP 
FROM
 PLAN_TABLE WHERE STATEMENT_ID=:1 AND OTHER_TAG IS NOT NULL ORDER BY ID


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=3 r=0 w=0 time=259 us)
      0   TABLE ACCESS FULL PLAN_TABLE (cr=3 r=0 w=0 time=175 us)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       14      0.23       0.28          1          4          0           0
Execute     15      0.02       0.10          0          6         12          10
Fetch       14      4.35      11.22       6308       8789          0          34
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       43      4.60      11.61       6309       8799         12          44

Misses in library cache during parse: 11
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       14      0.00       0.00          0          0          0           0
Execute     62      0.01       0.01          0          2         11          10
Fetch       52      0.00       0.01          1        106          0           8
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      128      0.01       0.04          1        108         11          18

Misses in library cache during parse: 6
Misses in library cache during execute: 1

   16  user  SQL statements in session.
   13  internal SQL statements in session.
   29  SQL statements in session.
********************************************************************************
Trace file: itdb_ora_2508.trc
Trace file compatibility: 9.00.01
Sort options: default

       3  sessions in tracefile.
      30  user  SQL statements in trace file.
      28  internal SQL statements in trace file.
      29  SQL statements in trace file.
      17  unique SQL statements in trace file.
     425  lines in trace file.

 

Followup:
really only needed a tiny bit, the relevant portion. 

Here is the tkprof from INDEX RANGE SCAN...  June 04, 2004
Reviewer:  A reader

This is the tkprof from analyze_schema that's doing a INDEX RANGE SCAN  on REQ 
table:
============================================


TKPROF: Release 9.2.0.4.0 - Production on Fri Jun 4 10:55:10 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: itdb_ora_2508.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

alter session set sql_trace = true


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 433  
********************************************************************************

SELECT DECODE('A','A','1','2') 
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          6          0           2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=199 us)

********************************************************************************

SELECT ID ID_PLUS_EXP,PARENT_ID PARENT_ID_PLUS_EXP,LPAD(' ',2*(LEVEL-1))
  ||OPERATION||DECODE(OTHER_TAG,NULL,'','*')||DECODE(OPTIONS,NULL,'',' 
  ('||OPTIONS||')')||DECODE(OBJECT_NAME,NULL,'',' OF '''||OBJECT_NAME||'''')
  ||DECODE(OBJECT_TYPE,NULL,'',' ('||OBJECT_TYPE||')')||DECODE(ID,0,
  DECODE(OPTIMIZER,NULL,'',' Optimizer='||OPTIMIZER))||DECODE(COST,NULL,'',' 
  (Cost='||COST||DECODE(CARDINALITY,NULL,'',' Card='||CARDINALITY)
  ||DECODE(BYTES,NULL,'',' Bytes='||BYTES)||')') PLAN_PLUS_EXP,OBJECT_NODE 
  OBJECT_NODE_PLUS_EXP 
FROM
 PLAN_TABLE START WITH ID=0 AND STATEMENT_ID=:1 CONNECT BY PRIOR ID=PARENT_ID 
  AND STATEMENT_ID=:1 ORDER BY ID,POSITION


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        6      0.05       0.07          1         68          0          22
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.05       0.08          1         68          0          22

Misses in library cache during parse: 3
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=3 r=0 w=0 time=382 us)
      0   CONNECT BY WITH FILTERING (cr=3 r=0 w=0 time=324 us)
      0    NESTED LOOPS  (cr=3 r=0 w=0 time=229 us)
      0     TABLE ACCESS FULL PLAN_TABLE (cr=3 r=0 w=0 time=219 us)
      0     TABLE ACCESS BY USER ROWID PLAN_TABLE (cr=0 r=0 w=0 time=0 us)
      0    HASH JOIN  (cr=0 r=0 w=0 time=0 us)
      0     CONNECT BY PUMP  (cr=0 r=0 w=0 time=0 us)
      0     TABLE ACCESS FULL PLAN_TABLE (cr=0 r=0 w=0 time=0 us)

********************************************************************************

SELECT USERENV('SESSIONID') 
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          6          0           2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=192 us)

********************************************************************************

select ROW_NUM, REQ_ID, LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from (
select rownum as ROW_NUM, REQ_ID, LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from
(
  select r.REQ_ID as REQ_ID,
  r.last_updated as LAST_UPDATED,
  r.REQ_STATUS as REQ_STATUS,
  dc.ACTION_DESC as ACTION_DESC,
  dc.CMD_DESC as CMD_DESC,
  dc.GUI_CMD_DESC as GUI_CMD_DESC,
 dc.oma as OMA,
  u.first_name||' '||u.last_name as USER_NAME
  from REQ r, DEV_COM dc, users u
  where r.dev_id = 6
  and r.cmd_id = dc.cmd_id
  and r.user_id = u.user_id
  order by 1 desc
  )
  )
where ROW_NUM between 1 and 5

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.01          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      4.14       4.37         70       8776          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      4.15       4.38         70       8776          0          10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  VIEW  (cr=4388 r=0 w=0 time=2068784 us)
  45952   COUNT  (cr=4388 r=0 w=0 time=1958742 us)
  45952    VIEW  (cr=4388 r=0 w=0 time=1799604 us)
  45952     SORT ORDER BY (cr=4388 r=0 w=0 time=1642682 us)
  45952      HASH JOIN  (cr=4388 r=0 w=0 time=1175554 us)
    226       TABLE ACCESS FULL DEV_COM (cr=5 r=0 w=0 time=1095 us)
  45952       HASH JOIN  (cr=4383 r=0 w=0 time=778086 us)
     29        TABLE ACCESS FULL USERS (cr=21 r=0 w=0 time=728 us)
  45952        TABLE ACCESS BY INDEX ROWID REQ (cr=4362 r=0 w=0 time=406214 us)
  45952         INDEX RANGE SCAN DUP_REQ_2 (cr=137 r=0 w=0 time=123214 
us)(object id 213014)

********************************************************************************

DELETE FROM PLAN_TABLE 
WHERE
 STATEMENT_ID=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.01       0.00          0          0          0           0
Execute      4      0.00       0.00          0         12         24          22
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.01       0.00          0         12         24          22

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  (cr=3 r=0 w=0 time=161 us)
      0   TABLE ACCESS FULL PLAN_TABLE (cr=3 r=0 w=0 time=150 us)

********************************************************************************

select default$ 
from
 col$ where rowid=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        6      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         12          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       18      0.00       0.00          0         12          0           6

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY USER ROWID COL$ (cr=1 r=0 w=0 time=86 us)

********************************************************************************

EXPLAIN PLAN SET STATEMENT_ID='PLUS7524' FOR select ROW_NUM, REQ_ID, 
LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from (
select rownum as ROW_NUM, REQ_ID, LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from
(
  select r.REQ_ID as REQ_ID,
  r.last_updated as LAST_UPDATED,
  r.REQ_STATUS as REQ_STATUS,
  dc.ACTION_DESC as ACTION_DESC,
  dc.CMD_DESC as CMD_DESC,
  dc.GUI_CMD_DESC as GUI_CMD_DESC,
 dc.oma as OMA,
  u.first_name||' '||u.last_name as USER_NAME
  from REQ r, DEV_COM dc, users u
  where r.dev_id = 6
  and r.cmd_id = dc.cmd_id
  and r.user_id = u.user_id
  order by 1 desc
  )
  )
where ROW_NUM between 1 and 5

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.14       0.14          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.15       0.14          0          0          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  
********************************************************************************

select user# 
from
 sys.user$ where name = 'OUTLN'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID USER$ (cr=2 r=0 w=0 time=81 us)
      1   INDEX UNIQUE SCAN I_USER1 (cr=1 r=0 w=0 time=45 us)(object id 44)

********************************************************************************

insert into plan_table (statement_id, timestamp, operation, options,
  object_node, object_owner, object_name, object_instance, object_type,
  search_columns, id, parent_id, position, other,optimizer, cost, cardinality,
   bytes, other_tag, partition_start, partition_stop, partition_id, 
  distribution, cpu_cost, io_cost, temp_space, access_predicates, 
  filter_predicates ) 
values
(:1,SYSDATE,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,
  :20,:21,:22,:23,:24,:25,:26,:27)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute     22      0.00       0.00          0          3         24          22
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       24      0.01       0.01          0          3         24          22

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 433     (recursive depth: 1)
********************************************************************************

select o.name, u.name 
from
 sys.obj$ o, sys.user$ u where obj# = :1 and owner# = user#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

SELECT ID ID_PLUS_EXP,OTHER_TAG OTHER_TAG_PLUS_EXP,OTHER OTHER_PLUS_EXP 
FROM
 PLAN_TABLE WHERE STATEMENT_ID=:1 AND OTHER_TAG IS NOT NULL ORDER BY ID


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          6          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=3 r=0 w=0 time=257 us)
      0   TABLE ACCESS FULL PLAN_TABLE (cr=3 r=0 w=0 time=173 us)

********************************************************************************

EXPLAIN PLAN SET STATEMENT_ID='PLUS7525' FOR select ROW_NUM, REQ_ID, 
LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from (
select rownum as ROW_NUM, REQ_ID, LAST_UPDATED, REQ_STATUS,
ACTION_DESC, CMD_DESC, GUI_CMD_DESC, OMA, USER_NAME from
(
  select r.REQ_ID as REQ_ID,
  r.last_updated as LAST_UPDATED,
  r.REQ_STATUS as REQ_STATUS,
  dc.ACTION_DESC as ACTION_DESC,
  dc.CMD_DESC as CMD_DESC,
  dc.GUI_CMD_DESC as GUI_CMD_DESC,
 dc.oma as OMA,
  u.first_name||' '||u.last_name as USER_NAME
  from REQ r, DEV_COM dc, users u
  where r.dev_id = 6
  and r.cmd_id = dc.cmd_id
  and r.user_id = u.user_id
  order by 1 desc
  )
  )
where ROW_NUM between 1 and 5

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.02       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.02          0          0          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 433  



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       18      0.17       0.18          0          0          0           0
Execute     20      0.03       0.02          0         12         24          22
Fetch       16      4.19       4.44         71       8862          0          36
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       54      4.39       4.65         71       8874         24          58

Misses in library cache during parse: 8


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       11      0.01       0.00          0          0          0           0
Execute     29      0.00       0.01          0          3         24          22
Fetch        7      0.00       0.00          0         14          0           7
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       47      0.01       0.01          0         17         24          29

Misses in library cache during parse: 2
Misses in library cache during execute: 1

   22  user  SQL statements in session.
    9  internal SQL statements in session.
   31  SQL statements in session.
********************************************************************************
Trace file: itdb_ora_2508.trc
Trace file compatibility: 9.00.01
Sort options: default

       2  sessions in tracefile.
      33  user  SQL statements in trace file.
      13  internal SQL statements in trace file.
      31  SQL statements in trace file.
      13  unique SQL statements in trace file.
     424  lines in trace file.



 

Followup:
interesting.


autotraces would lead a reasonable person to believe we were using the same 
tables and this was apples to apples.

but tkprof showing reality shows you are using totally different sets of tables.


HMMM.....

but the bottom line here is the first one got hit with some physical IO the 
other did not.

but they processed the same "amount" of data (8,746 vs 8,776 LIO's).  


But again -- take the advice to rewrite this query using just "r" -- ordering 
it, getting the first N, and then joining to that and you'll find this query 
runs "sub second" in all cases.


 

Confession to make!!  June 07, 2004
Reviewer:  A reader

Well, I was trying to hide the real table names by editing the tkprof output.. 
did it in one but not the other... that didn't escape your eagle eyes :)

Anyway, I 
i) simplified the query and got the between out of the equation. 
ii)Did this on a UNIX box and got windoze out of the equation ;)
iii) changed optimizer_index_cost_adj to 35 and optimzer_index_caching to 90.

None of these made a difference - gather_stats is still doing a FTS on request 
table. I really want to move away from analyze_schema and am trying to build 
some confidence in gather_stats. This is the first candidate query I tried for 
comparison.

Can you help me identify why the FTS with gather_stats? Is there anything else I 
can email you?

Thanks.
 

Followup:
but they do the same amount of work.

one did not have to do PIO
the other did have to do PIO

does the other do repeated PIO if you run it over and over? 

Here is the simplified query and its explain plans  June 07, 2004
Reviewer:  A reader

Simplified Query (with real table names :)
==========================================

select req_id
  from REQUEST r, DEVICE_COMMAND dc , users u
  where r.dev_id = 6
  and r.cmd_id = dc.cmd_id
  and r.user_id = u.user_id
  order by 1 desc
;

Plan with analyze schema:
=========================

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=31 Bytes=620
          )
   1    0   SORT (ORDER BY) (Cost=12 Card=31 Bytes=620)
   2    1     NESTED LOOPS (Cost=5 Card=31 Bytes=620)
   3    2       NESTED LOOPS (Cost=4 Card=31 Bytes=496)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'REQUEST' (Cost=3 C
          ard=31 Bytes=403)
   5    4           INDEX (RANGE SCAN) OF 'DUP_REQUEST_2' (NON-UNIQUE)
           (Cost=1 Card=31)
   6    3         INDEX (UNIQUE SCAN) OF 'PK_USERS_1' (UNIQUE)
   7    2       INDEX (UNIQUE SCAN) OF 'PK_DEVICE_COMMAND' (UNIQUE)


Plan with gather_stats:
=========================

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=718 Bytes=18
          668)

   1    0   SORT (ORDER BY) (Cost=19 Card=718 Bytes=18668)
   2    1     NESTED LOOPS (Cost=9 Card=718 Bytes=18668)
   3    2       NESTED LOOPS (Cost=8 Card=718 Bytes=15078)
   4    3         TABLE ACCESS (FULL) OF 'REQUEST' (Cost=6 Card=718 By
          tes=12206)

   5    3         INDEX (UNIQUE SCAN) OF 'PK_USERS_1' (UNIQUE)
   6    2       INDEX (UNIQUE SCAN) OF 'PK_DEVICE_COMMAND' (UNIQUE)

 

dbms_stats.alter_database_tab_monitoring  June 29, 2004
Reviewer:  A reader

I tried dbms_stats.alter_database_tab_monitoring a few times on my database and 
it failed with 'Resouce busy, update with NOWAIT requested' all the time.

Why would this be? All this should do is loop thru all the schemas and set ALTER 
TABLE ... MONITORING, right? Why would that fail like this?

Thanks

[BTW, the 9iR2 docs refer to this as alter_database_tabLE_monitoring which is 
more than the allowed 30-character length for identifiers!] 

Followup:
the alter table needs a table lock for an instant, that is what is failing.

either wait for a period of inactivity -- or simply DIY:

begin
  for x in ( select  .... )
    execute immediate 'alter ....'
  end loop;
end;

and through an exception handler in there to catch the resource busy errors. 

  July 01, 2004
Reviewer:  Jane

Tom,
Whenever we enable Monitoring for table, how will the database remember whether 
it needs to be analyzed or not. 

Is it something that triggered when we do dbms_stats 

Thanks in advance 

Followup:
they maintain stats in memory and flush this to disk every 3 hours (now 15 
minutes in 9i and up).  dba_tab_modifications lets you peek at this (also in 9i, 
you can flush the status out using dbms_stats)


then, you would use the GATHER STALE option with dbms_stats gather schema stats 
(or database stats) to gather stats just on the tables that need it. 

Statistics on SYS  July 02, 2004
Reviewer:  Ross  from London, United Kingdom

Hi Tom,

This has been an informative discussion, but I have another question: In many of 
the posts, people have analyzed the entire database and then deleted the 
statistics for SYS and SYSTEM.
What are the possible effects of having statistics for these users?

The reason I ask is because one of our testers was struggling with running a 
query on one of our views based on the following query:

select   P.param_value client
            ,VI.host_name hostname
            ,user schema
            ,sysdate snapshot_dt
            ,object_type
            ,object_name
            ,OTH.version
            ,NVL(vid,'NO VID') vid
            ,create_dt
            ,modified_dt
            ,OTH.ora_status
from    PARAMETER  P,
        V$INSTANCE VI,
(
  select  object_type
          ,object_name
          ,TDP_WHAT.get_vers(vid) version
          ,create_dt
          ,modified_dt
          ,ora_status
          ,vid
  from
  (
    select  UO.object_type,
            UO.object_name,
            vid,
            created         create_dt,
            last_ddl_time   modified_dt,
            status          ora_status
    from    user_objects UO,
    (
      select  type    object_type,
              name    object_name,
              TDP_WHAT.get_vid(type,name) vid
      from    user_source
      where   instr(text, '@'||'(#)', 1 ) > 0
      and     instr(text,'$Header',1) > 0
      and     line < 1000
      UNION
      select  object_type,
              object_name,
              TDP_WHAT.get_vid(object_type, object_name) vid
      from    USER_OBJECTS
      where   object_type = 'VIEW'
      or      ( object_type = 'TRIGGER' and ( substr(object_name, 
length(object_name)-1, 1 ) != '_' and substr(object_name, length(object_name)-2, 
1 ) != '_' ))
    ) SRC
    where   UO.object_type in ( 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 
'PROCEDURE', 'TRIGGER', 'VIEW', 'TYPE', 'TYPE BODY' )
    and     UO.object_name = SRC.object_name(+)
    and     UO.object_type = SRC.object_type(+)
    and    ( UO.object_type != 'TRIGGER' or ( substr(UO.object_name, 
length(UO.object_name)-1, 1 ) != '_' and substr(UO.object_name, 
length(UO.object_name)-2, 1 ) != '_' ))
    and     instr(UO.object_name, '$' ) = 0
  )
) OTH
where   param_name = 'SITE_NAME'
order by 1,2,3

This was generally taking around 30 seconds to return a single row until he 
tried logging on as SYS and analysing  system tables SOURCE$, VIEW$ and TRIGGER$ 
(using DBMS_DDL.analyze_object - don't worry, I know your views on this package, 
but anyway).
This reduced the run time of the query for a given object/object type by a 
factor of roughly 200.

We are considering incorporating this into the nightly statistics gathering 
operation, but I wanted to request your views on analyzing SYS-owned objects. 
Are there specific dangers to this?

Many thanks,
Ross 

Followup:
in 8i, you can, but probably don't want to.
in 9i, you can -- as long as you did so on your test environment as well
in 10g, it just happens. 

Was this response helpful to you? Let us know!
Bookmark this page with the link HERE

Information

 

This page provides the details of the question asked. To find another question click on the search tab. To view the question archives by week click on the archives tab.

 
Copyright © 2003 Oracle Corporation, All rights reserved.
Hosted by www.Geocities.ws

1