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
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:3126073805757to 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.
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. |
|