what is the utility of dbms_stats.set_table_stats
September 29, 2001 - 1pm Central time zone
Reviewer: Wells from USA
Is it used to set statistics and test the performance i.e. the execution path.
Actually, We are interested in knowing how we can use and where we can use
dbms_stats.set_table_stats to the maximum benefit.
Followup September 29, 2001 - 2pm Central time zone:
You can use it to transfer the stats from your production system (with all of the data) to your
test system. You might do that to review the query plans generated by new/altered queries in your
application before putting them into production -- so you can see what might be different.
I use it to show the effects of having differeing numbers of rows/blocks in tables. For example,
in order to demonstrate a point -- I might analyze an empty table -- run a query and show the plan.
Then, I will use dbms_stats to trick the optimizer into thinking there are LOTS and LOTS of rows
and blocks in there and how the different plans. You can use this to see how the optimizer will
change its mind about the proper plan over time.
Another use I've found for it is with temporary tables. You cannot really analyze them but what
you can do is create a "real" table -- fill it up with representative data -- and then export these
stats using dbms_stats. Later, you create the temporary table and import these other stats you
saved. Now the optimizer will use these stats for the temporary table when developing query plans
that involve it. This could be important if you put lots of rows into a temp table and then use it
in subsquent SQL statments -- the optimizer would otherwise think the temporary table is "small"
why is dbms_stats slower than analyze
November 1, 2001 - 12pm Central time zone
Reviewer: A reader
Hi
I have tested a few times and always find that dbms_stats is slower than ordinary analyze even
specifying parallel degree, why is that? we are using 8.1.6.3
Also is it possible analyze a table using analyze table xx compute statistics in parallel?
Followup November 1, 2001 - 4pm Central time zone:
Geez, no test case, no parameters, not even "we find it X% slower" -- nothing.
Perhaps the tables you used were trivial in size (parallel can take much longer). Perhaps you were
comparing apples with oranges (eg: dbms_stats by default does "for all columns size 1" -- did your
analyze do that too?)
No, you cannot analyze in parallel, only dbms_stats can.
Also, I do not see it being significantly slower (given that dbms_stats will just issue an analyze
in this particular case you would expect that to be so!):
ops$tkyte@ORA717DEV.US.ORACLE.COM> variable n number
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> analyze table t delete statistics;
Table analyzed.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> exec :n := dbms_utility.get_time
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM> exec dbms_stats.gather_table_stats( user, 'T', method_opt =>
'for all indexed columns', cascade => true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> exec dbms_output.put_line( (dbms_utility.get_time-:n) || ' hsecs
' );
22762 hsecs
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> analyze table t delete statistics;
Table analyzed.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> exec :n := dbms_utility.get_time
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> analyze table t
2 compute statistics
3 for table
4 for all indexed columns
5 for all indexes
6 /
Table analyzed.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> exec dbms_output.put_line( (dbms_utility.get_time-:n) || ' hsecs
' );
22732 hsecs
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM> select num_Rows from user_tables where table_name = 'T';
NUM_ROWS
----------
1093312
and when I do it in parallel -- I see a HUGE difference:
ops$tkyte@ORA717DEV.US.ORACLE.COM> variable n number
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> analyze table t delete statistics;
Table analyzed.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> exec :n := dbms_utility.get_time
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM> exec dbms_stats.gather_table_stats( user, 'T', method_opt =>
'for all indexed columns size 1', cascade => true, degree=>8 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> exec dbms_output.put_line( (dbms_utility.get_time-:n) || ' hsecs
' );
2525 hsecs
PL/SQL procedure successfully completed.
I think you were using tables that were so small as to be insignificant in even timing them.
Btw, table t is:
create table t as select * from all_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
create index t_idx1 on t(object_id);
create index t_idx2 on t(object_name);
Partitions are a slightly different story...
November 2, 2001 - 5am Central time zone
Reviewer: Connor from Deepest Darkest Scotland
analyze table p1 partition (x1)
compute statistics
********************************************************************************
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.05 0 0 0 0
Execute 3 14.89 44.28 11089 1818 300 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 14.92 44.33 11089 1818 300 0
BEGIN dbms_stats.GATHER_TABLE_STATS('MCDONAC','P1','X1'); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 11 0.04 0.08 0 0 0 0
Execute 18 0.01 0.03 2 23 13 9
Fetch 15 50.47 198.18 48473 7511 1723 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 44 50.52 198.29 48475 7534 1736 19
so you do need to be a little wary before converting all of your analyze scripts to use dbms_stats.
Followup November 2, 2001 - 8pm Central time zone:
Connor -- you've compared apples to toaster ovens. That dbms_stats gathers lots more stats then
the analyze command does!
The analyze doesn't work 100% on partitions, for example -- analyze that table and look at the
LAST_ANALYZED in the USER_TAB_COLUMNS table -- it'll not have changed (nor will the values there).
Then, do it with dbms_stats and you'll find that it does. they are two different commands above.
DBMS_STATS slower
November 2, 2001 - 6am Central time zone
Reviewer: A reader
Hi Tom
I have done a test after reading this thread on a Tru64 Machine with 1 CPU ONLY, Oracle 8.1.7
Enterprise Edition and a table of 4500000 rows
SQL> analyze table opsc.CALLS_OPSC estimate statistics sample 20 percent for all columns size 1;
Table analyzed.
Elapsed: 00:17:14.11
SQL> exec dbms_stats.GATHER_TABLE_STATS('OPSC', 'CALLS_OPSC', estimate_percent => 20, method_opt =>
'for all columns size 1', cascade => true, degree => 2);
PL/SQL procedure successfully completed.
Elapsed: 00:20:05.20
Is DBMS_STATS slower in this case because we only have one CPU? Also why with
analyze table opsc.CALLS_OPSC estimate statistics sample 20 percent for all columns size 1;
in num_rows I see NULL? Only column statistics appears in dba_tab_columns
By the way there is no index in this table
cheers
Followup November 2, 2001 - 7am Central time zone:
Your commands are totally different.
You have to compare:
analyze table opsc.CALLS_OPSC estimate statistics sample 20 percent
for table
for all columns size 1
for all indexes
(you didn't analyze the table!! dbms_stats did, analyze did not)
and yes, there is a chance that the overhead of PQ on a single cpu system outweighs any benefit in
this particular case. But here, the difference is most likely due to the fact that dbms_stats did
more work.
17 minutes to sample 20% of a 4.5 million row table seems long to me. You might want to tkprof
that to see if you have tons of waits and if so, play around with your sort area size and such --
both of those should go faster.
Your followup on partitions - dbms_stats vs analyze
November 3, 2001 - 5am Central time zone
Reviewer: Connor McDonald from UK
I wasn't trying to dispute that dbms_stats and analyze do something very different. I'm just
trying to warn people about possible consequences of taking a set of existing ANALYZE scripts and
merely converting them (in what would seem a fairly natural sense) to DBMS_STATS - sure you get
better stats - but you may get a large resource hit.
Cheers
Connor
Followup November 3, 2001 - 8am Central time zone:
ahh, OK, I would think (hope, pray, whatever) that people would test things -- read the docs and
realize that these are different implementations -- collected radically different values - did
different things.
You are right though -- pointing it out doesn't hurt (but I still feel compelled to point out
"apples and oranges" just in case someone reading THOUGHT they were the same!)
Thanks Connor -- always appreciate your input.
missread
December 11, 2001 - 4am Central time zone
Reviewer: A reader
Hi Tom
I read the replay "dbms_stats is slower", it states
SQL> analyze table opsc.CALLS_OPSC estimate statistics sample 20 percent for all
columns size 1;
Table analyzed.
Elapsed: 00:17:14.11
SQL> exec dbms_stats.GATHER_TABLE_STATS('OPSC', 'CALLS_OPSC', estimate_percent
=> 20, method_opt => 'for all columns size 1', cascade => true, degree => 2);
PL/SQL procedure successfully completed.
Elapsed: 00:20:05.20
I am not sure, you said they are different but I dont see the difference because method_opt => 'for
all columns size 1' is specified too. Unless even you specify for all columns Oracle does for all
columns as well?
Also is there anyway to know the default values of parameters of this package? It seems impossible
and how can we know what default it is doing?
Followup December 11, 2001 - 8am Central time zone:
They are different because the analyze command does ONE thing -- gets the column stats. It does
NOT get the table stats, it does NOT get the index stats. The dbms_stats call does get the table
stats (gather_table_stats always does). In addition it gets stats FOR ALL COLUMNS (what the
analyze did). In addition, it was specified with CASCADE=>TRUE meaning the indexes associated with
the table will be analyzed as well.
You can see the difference to confirm it via:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects;
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(object_id);
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t delete statistics;
Table analyzed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t
2 estimate statistics sample 20 percent
3 for all columns size 1;
Table analyzed.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select last_analyzed from user_tables where table_name = 'T';
LAST_ANAL
---------
ops$tkyte@ORA817DEV.US.ORACLE.COM> select last_analyzed from user_indexes where index_name =
'T_IDX';
LAST_ANAL
---------
ops$tkyte@ORA817DEV.US.ORACLE.COM> select column_name, last_analyzed from user_tab_columns where
table_name = 'T';
COLUMN_NAME LAST_ANAL
------------------------------ ---------
OWNER 11-DEC-01
OBJECT_NAME 11-DEC-01
SUBOBJECT_NAME 11-DEC-01
OBJECT_ID 11-DEC-01
DATA_OBJECT_ID 11-DEC-01
OBJECT_TYPE 11-DEC-01
CREATED 11-DEC-01
LAST_DDL_TIME 11-DEC-01
TIMESTAMP 11-DEC-01
STATUS 11-DEC-01
TEMPORARY 11-DEC-01
GENERATED 11-DEC-01
SECONDARY 11-DEC-01
13 rows selected.
Note that the columns have a last_analyzed -- the table and index DO NOT
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t delete statistics;
Table analyzed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 dbms_stats.GATHER_TABLE_STATS
3 (user, 'T', estimate_percent => 20,
4 method_opt => 'for all columns size 1',
5 cascade => true, degree => 2);
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select last_analyzed from user_tables where table_name = 'T';
LAST_ANAL
---------
11-DEC-01
ops$tkyte@ORA817DEV.US.ORACLE.COM> select last_analyzed from user_indexes where index_name =
'T_IDX';
LAST_ANAL
---------
11-DEC-01
ops$tkyte@ORA817DEV.US.ORACLE.COM> select column_name, last_analyzed from user_tab_columns where
table_name = 'T';
COLUMN_NAME LAST_ANAL
------------------------------ ---------
OWNER 11-DEC-01
OBJECT_NAME 11-DEC-01
SUBOBJECT_NAME 11-DEC-01
OBJECT_ID 11-DEC-01
DATA_OBJECT_ID 11-DEC-01
OBJECT_TYPE 11-DEC-01
CREATED 11-DEC-01
LAST_DDL_TIME 11-DEC-01
TIMESTAMP 11-DEC-01
STATUS 11-DEC-01
TEMPORARY 11-DEC-01
GENERATED 11-DEC-01
SECONDARY 11-DEC-01
13 rows selected.
and now the table, index and columns have been indexed
As for how to know the default parameters (and how this package works) one would read the
documentation on it:
http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76936/dbms_sta.htm#999107 ....
Follow on questions
April 18, 2002 - 3pm Central time zone
Reviewer: Robert Ware from St. Louis Mo.
Tom
First, THANK YOU FOR ALL THE INFORMATION SHARING YOU PROVIDE.
1) Does dbms_stats provide information on chained_rows compatible to the analyze command? If so
what do I need to modify in my script below to implement it?
---We recently replaced---
RDWARE@ORCL..SQL>BEGIN
2 FOR s in (select distinct(owner) schema
3 from dba_tables
4 where owner not like 'SYS%')
5 LOOP
6 dbms_output.put_line('Analyzing the entire '||s.schema||' schema');
7 dbms_utility.analyze_schema(s.schema,'ESTIMATE',null,20,'for table for all indexed
columns size 254');
8 End Loop;
9 END;
10 /
Analyzing the entire LJCAVANO schema
Analyzing the entire OUTLN schema
Analyzing the entire PD6_REPO schema
Analyzing the entire PD8_REPO schema
Analyzing the entire RDWARE schema
Analyzing the entire RPCOCA schema
Analyzing the entire RSPAYNE schema
Analyzing the entire SCOTT schema
Analyzing the entire SDCORP_REPO schema
PL/SQL procedure successfully completed.
Elapsed: 00:01:05.72
---with---
RDWARE@ORCL..SQL>BEGIN
2 FOR s in (select distinct(owner) schema
3 from dba_tables
4 where owner not like 'SYS%')
5 LOOP
6 dbms_output.put_line('Analyzing the entire '||s.schema||' schema');
7 dbms_stats.gather_schema_stats(ownname => s.schema,
8 estimate_percent => 20,
9 method_opt => 'FOR ALL COLUMNS SIZE 1',
10 degree => 8,
11 cascade => true);
12 End Loop;
13 END;
14 /
Analyzing the entire LJCAVANO schema
Analyzing the entire OUTLN schema
Analyzing the entire PD6_REPO schema
Analyzing the entire PD8_REPO schema
Analyzing the entire RDWARE schema
Analyzing the entire RPCOCA schema
Analyzing the entire RSPAYNE schema
Analyzing the entire SCOTT schema
Analyzing the entire SDCORP_REPO schema
PL/SQL procedure successfully completed.
Elapsed: 00:09:21.68
Followup April 18, 2002 - 9pm Central time zone:
if you do not use parallel (and use size > 1), it'll get the chain count Estimated. the parallel
stuff seems to disable that.
DBMS_STATS: i tried and it worked! Made my life easy.. ;)
June 29, 2002 - 7am Central time zone
Reviewer: Yogeeraj from Mauritius
Hello,
I was trying to analyze a schema in our database, and seems like it is was not working!!
------------------------------------------------------------
SQL> exec print_table('select * from dba_tables where owner=''CMTSTORE'' and
table_name=''STT110''');
OWNER : CMTSTORE
TABLE_NAME : STT110
TABLESPACE_NAME : CMTSTORE_LMT_DATA_MEDIUM
CLUSTER_NAME :
IOT_NAME :
PCT_FREE : 10
PCT_USED : 40
INI_TRANS : 1
MAX_TRANS : 255
INITIAL_EXTENT : 10485760
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE : 0
FREELISTS : 1
FREELIST_GROUPS : 1
LOGGING : YES
BACKED_UP : N
NUM_ROWS : 62255
BLOCKS : 679
EMPTY_BLOCKS : 668
AVG_SPACE : 898
CHAIN_CNT : 0
AVG_ROW_LEN : 73
AVG_SPACE_FREELIST_BLOCKS : 6942
NUM_FREELIST_BLOCKS : 5
DEGREE : 1
INSTANCES : 1
CACHE : N
TABLE_LOCK : ENABLED
SAMPLE_SIZE : 12451
LAST_ANALYZED : 19-apr-2002 10:26:00
PARTITIONED : NO
IOT_TYPE :
TEMPORARY : N
SECONDARY : N
NESTED : NO
BUFFER_POOL : DEFAULT
ROW_MOVEMENT : DISABLED
GLOBAL_STATS : YES
USER_STATS : NO
DURATION :
SKIP_CORRUPT : DISABLED
MONITORING : NO
CLUSTER_OWNER :
-----------------
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_schema_stats('cmtstore',method_opt=>'for all indexed
columns',cascade=>true,stattab=>'CMTSTORESTATS');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.create_stat_table('yd','cmtstorestats');
PL/SQL procedure successfully completed.
SQL> exec print_table('select * from dba_tables where owner=''CMTSTORE'' and tab
le_name=''STT110''');
OWNER : CMTSTORE
TABLE_NAME : STT110
TABLESPACE_NAME : CMTSTORE_LMT_DATA_MEDIUM
CLUSTER_NAME :
IOT_NAME :
PCT_FREE : 10
PCT_USED : 40
INI_TRANS : 1
MAX_TRANS : 255
INITIAL_EXTENT : 10485760
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE : 0
FREELISTS : 1
FREELIST_GROUPS : 1
LOGGING : YES
BACKED_UP : N
NUM_ROWS : 62255
BLOCKS : 679
EMPTY_BLOCKS : 668
AVG_SPACE : 898
CHAIN_CNT : 0
AVG_ROW_LEN : 73
AVG_SPACE_FREELIST_BLOCKS : 6942
NUM_FREELIST_BLOCKS : 5
DEGREE : 1
INSTANCES : 1
CACHE : N
TABLE_LOCK : ENABLED
SAMPLE_SIZE : 12451
LAST_ANALYZED : 19-apr-2002 10:26:00
PARTITIONED : NO
IOT_TYPE :
TEMPORARY : N
SECONDARY : N
NESTED : NO
BUFFER_POOL : DEFAULT
ROW_MOVEMENT : DISABLED
GLOBAL_STATS : YES
USER_STATS : NO
DURATION :
SKIP_CORRUPT : DISABLED
MONITORING : NO
CLUSTER_OWNER :
-----------------
PL/SQL procedure successfully completed.
SQL>
============================================================
You will note that the last_analyzed column did not change.
LAST_ANALYZED : 19-apr-2002 10:26:00
also, no records found in the table which i specified to save the previous statistics:
SQL> select count(*)
2 from cmtstorestats;
COUNT(*)
----------
0
SQL>
============================================================
Then as you always recommend ==> when back to Oracle Documentations: "Supplied PL/SQL Packages" and
found that something was missing -
"statown Schema containing stattab (if different than ownname)".
and tried again: (IT WORKED!!)
============================================================
yd@CMTDB.CMT.MU> exec dbms_stats.gather_schema_stats('cmtstore',method_opt=>'for all indexed
columns',cascade=>true,stattab=>'CMTSTORESTATS',statown=>'yd');
PL/SQL procedure successfully completed.
Elapsed: 00:00:52.57
yd@CMTDB.CMT.MU> exec print_table('select * from dba_tables where owner=''CMTSTO
RE'' and table_name=''STT110''');
OWNER : CMTSTORE
TABLE_NAME : STT110
TABLESPACE_NAME : CMTSTORE_LMT_DATA_MEDIUM
CLUSTER_NAME :
IOT_NAME :
PCT_FREE : 10
PCT_USED : 40
INI_TRANS : 1
MAX_TRANS : 255
INITIAL_EXTENT : 10485760
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE : 0
FREELISTS : 1
FREELIST_GROUPS : 1
LOGGING : YES
BACKED_UP : N
NUM_ROWS : 74026
BLOCKS : 807
EMPTY_BLOCKS : 472
AVG_SPACE : 950
CHAIN_CNT : 0
AVG_ROW_LEN : 76
AVG_SPACE_FREELIST_BLOCKS : 7737
NUM_FREELIST_BLOCKS : 4
DEGREE : 1
INSTANCES : 1
CACHE : N
TABLE_LOCK : ENABLED
SAMPLE_SIZE : 74026
LAST_ANALYZED : 29-jun-2002 15:21:13
PARTITIONED : NO
IOT_TYPE :
TEMPORARY : N
SECONDARY : N
NESTED : NO
BUFFER_POOL : DEFAULT
ROW_MOVEMENT : DISABLED
GLOBAL_STATS : NO
USER_STATS : NO
DURATION :
SKIP_CORRUPT : DISABLED
MONITORING : NO
CLUSTER_OWNER :
-----------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
yd@CMTDB.CMT.MU>
============================================================
I have two question:
1. If i set MONITORING on all tables and indexes (??) do i still need to run these steps
regularly?
2. If i decide to use only CBO on my production database, what type of problems (if any) can i
anticipate?
Thanks a lot
Best Regards
Yogeeraj.
PS. Since i got your book, i have lesser questions to ask! Thanks. (however the DBMS_STATS is not
there in the Appendices ;) - maybe you don't use it that frequently)
Followup June 29, 2002 - 10am Central time zone:
1) Yes.
if you use monitoring on a table, you still use dbms_stats to gather stats -- but only on STALE
objects (not everything)
Monitoring on an index is for an entirely differently purpose, it'll tell you if the index was used
or not. It is not for statistics purposes.
2) None.
I viewed dbms_stats (like backup and recovery) as being 100% a DBA thing. The book is definitely
100% useful to the DBA however, it was geared towards the developer. At 1,200+ pages -- something
had to give (believe me, there is a lot more I left out then I put in)
Thanks a lot for the reply.
July 1, 2002 - 4am Central time zone
Reviewer: Yogeeraj from Mauritius
thanks for the reply.
BTW, very nice discussion in this link:
http://asktom.oracle.com/pls/ask/f?p=4950:8:987522::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1260600
445561,%7Bmonitoring%7D
One last Question:
I have a "huge table" with 1.5M records. What will be the best way to update its statistics
(table-with lots of inserts/updates 24x7)? (how do i know if i really need to do it? ;) the current
statistics are since the date we moved from Oracle 7.2.3 to Oracle 8.1.7.
============================================================
yd@CMTDB.CMT.MU> exec print_table('select * from dba_tables where table_name=''KNT200'' and
owner=''SWPROD''');
OWNER : SWPROD
TABLE_NAME : KNT200
TABLESPACE_NAME : PFS_TBS_KN
CLUSTER_NAME :
IOT_NAME :
PCT_FREE : 15
PCT_USED : 50
INI_TRANS : 10
MAX_TRANS : 200
INITIAL_EXTENT : 272629760
NEXT_EXTENT : 10485760
MIN_EXTENTS : 1
MAX_EXTENTS : 240
PCT_INCREASE : 0
FREELISTS : 1
FREELIST_GROUPS : 1
LOGGING : YES
BACKED_UP : N
NUM_ROWS : 1319110
BLOCKS : 17160
EMPTY_BLOCKS : 16119
AVG_SPACE : 1226
CHAIN_CNT : 0
AVG_ROW_LEN : 85
AVG_SPACE_FREELIST_BLOCKS : 0
NUM_FREELIST_BLOCKS : 0
DEGREE : 1
INSTANCES : 1
CACHE : N
TABLE_LOCK : ENABLED
SAMPLE_SIZE : 1054
LAST_ANALYZED : 16-dec-2001 10:36:59
PARTITIONED : NO
IOT_TYPE :
TEMPORARY : N
SECONDARY : N
NESTED : NO
BUFFER_POOL : DEFAULT
ROW_MOVEMENT : DISABLED
GLOBAL_STATS : NO
USER_STATS : NO
DURATION :
SKIP_CORRUPT : DISABLED
MONITORING : NO
CLUSTER_OWNER :
-----------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
yd@CMTDB.CMT.MU> select count(*) from knt200;
COUNT(*)
----------
1540611
Elapsed: 00:00:09.33
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IND_KNT200_1' (NON-UNIQUE) (Cost=2 Card=1319110)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
5434 consistent gets
5432 physical reads
0 redo size
298 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
============================================================
Thank you again for you time.
Best Regards
Yogeeraj
Followup July 1, 2002 - 7am Central time zone:
well, 1.5 million records with an average row length of 85 bytes is not very big at all.
But, you would alter table T monitoring and let dbms_stats tell you when to do it. I would analyze
this table once (fully) and then let monitoring take it from there.
You are only talking about 150 meg or so of data here, pretty small.
bright
July 1, 2002 - 8am Central time zone
Reviewer: Yogeeraj from Mauritius
hello,
thank you for the precious guidelines.
(We always thought that our 16 GB Database and 2M record/200MB tables could be classified as large
databse/tables ;))
One little confusion about "monitoring"
you said:
http://asktom.oracle.com/pls/ask/f?p=4950:8:987536::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1260600
445561,%7Bmonitoring%7D <quote>
...since you know the table is undergoing lots of inserts during the day, monitoring is not really
needed - you know for a fact the stats are going stale frequently and need to be re-gathered.
</quote>
so should i really, do a "alter table knt200 monitoring;"?
Also, is it true that a "table" is locked during the whole analysis operation when using
DBMS_STATS? - a problem for "online tables".
How can i know how often i need to run the DBMS_STATS.GATHER_SCHEMA_STATS with the GATHER STALE
option?
thank you again for your precious time and all the coaching you give us here!
Best Regards
Yogeeraj
Followup July 1, 2002 - 8am Central time zone:
You can either gather stats every night (if about 10% of the table will change -- thats the
threshold monitoring will use) or monitor it and gather stale. it is ultimately upto you. I
might monitor and then see how often we do gather stats -- to see if my thoughts about how fast
this table changes were in fact correct.
No, a table is not locked during an analyze or dbms_stats.
What the user_tab_modifications view. It'll show you how rapidly things are changing. How often
you run this will be a function of
a) how often you WANT to run it
b) how often you CAN run it
c) how often you should run it (based on how fast the data changes)
DBMS_STATS
July 1, 2002 - 10am Central time zone
Reviewer: Suresh
Tom,
This is with reference to the review posted by
Robert Ware, St.Louis. In that you have mentioned, DBMS_STATS will be able to gather statistics of
chained rows if we make 'size > 1'.
In the URL I have mentioned below, you said the following.
http://asktom.oracle.com/pls/ask/f?p=4950:8:989417::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4347359
891525,%7Bsuresh%7D
"DBMS_STATS include the number of rows, number of blocks currently containing data, and average
row length but not the number of chained rows, average free space, or number of unused data
blocks."
Could you please let me know which of the statement is correct or have I misunderstood.
Thanks,
Suresh
Followup July 1, 2002 - 10am Central time zone:
when you do size > 1, dbms_stats seems to use analyze instead of doing it itself.
i would not count on that always being the case, but thats the way it is right now.
Reader
March 25, 2003 - 12am Central time zone
Reviewer: A reader
How does oracle determine the optimal bucket size based on the data distribution and application
work load on the
columns automatically ( how Oracle calculates the work load) when
method_opt =>'for all columns size AUTO' is used
Thanks
Using dbms_stats to transport statistics for performance tuning
March 25, 2003 - 10am Central time zone
Reviewer: Wolfgang Breitling from Calgary, Canada
I use that all the time. I take schema exports of a system (export rows=n) and build an empty
tuning sandbox. Then I export the statistics from the production database
(dbms_stats.export_xxx_stats followed by an export of the stattab table) and then import the
statistics into the sandbox. Then I can do analysis of sql in the sandbox. Given the same input
parameters the optimizer will come up with the same access plan - even across OS platforms. Knowing
what is wrong with the original plan I can try various things, sometimes with the aid of a 10053
trace, to get the optimizer to choose a different plan. At that point I have to go back to the
production system to try out the modification to see if that new plan is actually performing
better.
That seems like a lot of work for potentially little gain, but I am a consultant, so this setup
allows me to do some of the sql analysis work off-site.
As a note to the claims that dbms_stats is slower than analyze. I have not done any stringent
comparisons, but on my first experiences with dbms_stats I had the distinct impression that it was
faster than the analyze and I am only using dbms_stats to gather statistics, but then I do not
constantly analyze tables either.
how can i gather the statstics only in the "create stat table" ?
May 21, 2003 - 10pm Central time zone
Reviewer: biti_rainy from zhuhai china
SQL> begin
2 DBMS_STATS.GATHER_SCHEMA_STATS (
3 ownname => user,
4 method_opt => 'for all indexed columns',
5 cascade => true,
6 stattab => 'BILLY_STAT',
7 statid => '2003-05-22-01',
8 statown => user);
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select count(*) from billy_stat;
COUNT(*)
----------
0
there is no data in billy_stat , but the statistics is stored in the DICT TABLE .
if i want the statistics only store in the billy_stat, how to do ?
Followup May 23, 2003 - 7am Central time zone:
you don't.
you would
o export the stats
o gather them, saving in billy_stat
o import the original ones
(but one would question WHY you wouldn't want them put into the dictionary since they would be more
current/accurate)
frequence of gathering statistics
August 6, 2003 - 2pm Central time zone
Reviewer: A reader
Tom,
The impression I get from you and other articles I have read, is that current statistics are a good
thing.
"And if you don't keep your statistics current, the cost-based optimizer may not function
effectively for a particular query."
http://www.oracle.com/oramag/oracle/00-jul/index.html?o40tun.html
And your statement: "but one would question WHY you wouldn't want them put into the dictionary
since they would be more current/accurate".
But then I recently read an article by Donald K. Burleson where he states:
"One of the most common mistakes made by Oracle DBAs is to frequently re-analyze the schema.
Remember, the sole purpose of doing that is to change the execution plans for your SQL, and if it
ain't broke, don't fix it. If you are satisfied with your current SQL performance, re-analyzing
a schema could cause significant performance problems and undo the tuning efforts of the
development staff. In practice, very few shops are sufficiently dynamic to require periodic schema
re-analysis."
http://technet.oracle.com/oramag/webcolumns/2003/techarticles/burleson_cbo_pt1_pt2.html
So, I am wondering your take on what Mr. Burleson says.
Thanks.
Followup August 9, 2003 - 12pm Central time zone:
I disagree... I would say you might consider EXPORTING your statistics frequently (nice to keep the
history as a BONUS) so that in the event something does "go bad" -- you can get back to yesterday
quickly.
But having current statistics is extremely relevant for the most trivial of applications --
consider a range scan on time sensitive data (every day you add more records, the CBO -- which
hasn't seen a new stat for 6 months assumes there is NO DATA for the last 6 months).
Alter table monitoring
Export old
Gather stale
that way you get the benefits of any advances in performance, coupled with a good and immediate
fallback plan. You get a very good history to boot (hey, how did my data change in size/skew over
the last 6 months? 12 months? 2 years?)
thanks Tom - makes sense
August 11, 2003 - 8am Central time zone
Reviewer: A reader
method_opt definition from 8.1.7 documentation
September 11, 2003 - 9am Central time zone
Reviewer: A reader
Hi
I came across these setence in 8.1.7 documentation talking about DBMS_STATS
method_opt:
Method options of the following format (the phrase 'SIZE 1' is required to ensure gathering
statistics in parallel and for use with the phrase hidden)
I dont understand what's this talking about at all, is it saying if we leave method_opt NULL we
cannot gather statistics in parallel...??!!
Followup September 11, 2003 - 6pm Central time zone:
big_table@ORA920> alter session set events '10046 trace name context forever, level 12';
Session altered.
big_table@ORA920> begin
2 dbms_stats.gather_table_stats
3 ( ownname => user,
4 tabname => 'BIG_TABLE',
5 method_opt => NULL,
6 degree => 4 );
7 end;
8 /
and tkprof shows....
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring parallel(t,
4) parallel_index(t,4) */ count(*),sum(vsize("ID")),substrb(dump(min("ID")
,16,0,32),1,120),substrb(dump(max("ID"),16,0,32),1,120),count(distinct
"OWNER"),sum(vsize("OWNER")),substrb(dump(min("OWNER"),16,0,32),1,120),
substrb(dump(max("OWNER"),16,0,32),1,120),count(distinct "OBJECT_NAME"),
sum(vsize("OBJECT_NAME")),substrb(dump(min("OBJECT_NAME"),16,0,32),1,120),
substrb(dump(max("OBJECT_NAME"),16,0,32),1,120),count("SUBOBJECT_NAME"),
count(distinct "SUBOBJECT_NAME"),sum(vsize("SUBOBJECT_NAME")),
substrb(dump(min("SUBOBJECT_NAME"),16,0,32),1,120),
substrb(dump(max("SUBOBJECT_NAME"),16,0,32),1,120),count(distinct
"OBJECT_ID"),sum(vsize("OBJECT_ID")),substrb(dump(min("OBJECT_ID"),16,0,32),
1,120),substrb(dump(max("OBJECT_ID"),16,0,32),1,120),count("DATA_OBJECT_ID")
,count(distinct "DATA_OBJECT_ID"),sum(vsize("DATA_OBJECT_ID")),
substrb(dump(min("DATA_OBJECT_ID"),16,0,32),1,120),
substrb(dump(max("DATA_OBJECT_ID"),16,0,32),1,120),count("OBJECT_TYPE"),
count(distinct "OBJECT_TYPE"),sum(vsize("OBJECT_TYPE")),
substrb(dump(min("OBJECT_TYPE"),16,0,32),1,120),
substrb(dump(max("OBJECT_TYPE"),16,0,32),1,120),count(distinct "CREATED"),
substrb(dump(min("CREATED"),16,0,32),1,120),substrb(dump(max("CREATED"),16,
0,32),1,120),count(distinct "LAST_DDL_TIME"),
substrb(dump(min("LAST_DDL_TIME"),16,0,32),1,120),
substrb(dump(max("LAST_DDL_TIME"),16,0,32),1,120),count("TIMESTAMP"),
count(distinct "TIMESTAMP"),sum(vsize("TIMESTAMP")),
substrb(dump(min("TIMESTAMP"),16,0,32),1,120),substrb(dump(max("TIMESTAMP"),
16,0,32),1,120),count("STATUS"),count(distinct "STATUS"),sum(vsize("STATUS")
),substrb(dump(min("STATUS"),16,0,32),1,120),substrb(dump(max("STATUS"),16,
0,32),1,120),count("TEMPORARY"),count(distinct "TEMPORARY"),
sum(vsize("TEMPORARY")),substrb(dump(min("TEMPORARY"),16,0,32),1,120),
substrb(dump(max("TEMPORARY"),16,0,32),1,120),count("GENERATED"),
count(distinct "GENERATED"),sum(vsize("GENERATED")),
substrb(dump(min("GENERATED"),16,0,32),1,120),substrb(dump(max("GENERATED"),
16,0,32),1,120),count("SECONDARY"),count(distinct "SECONDARY"),
sum(vsize("SECONDARY")),substrb(dump(min("SECONDARY"),16,0,32),1,120),
substrb(dump(max("SECONDARY"),16,0,32),1,120)
from
"BIG_TABLE"."BIG_TABLE" t
it is that if you gather histograms in 8i and want parallel, you would use size 1. setting
method-opt to null bypasses histograms entirely.
am slightly confused
September 18, 2003 - 5pm Central time zone
Reviewer: Sravan from Northampton, MA
Tom,
I deleted the statistics on a schema.
Ran a job which gathered statistics on that schema and took 13 hours.
I tried to run it in parallel, by specifying the degree. It took 16 hours.
(Please note, that am not specifying the "method_opt" option.)
Why is the parallel job taking longer? Am I missing something obvious here or forgot to include or
do other prep work ?
First Job definition
=====================
DECLARE
v_jobno number ;
BEGIN
DBMS_JOB.SUBMIT(v_jobno,
'DBMS_STATS.GATHER_SCHEMA_STATS(''SUPPORT'',
ESTIMATE_PERCENT => 35,
CASCADE => TRUE);');
COMMIT;
END;
/
======================================
2nd Job definition with Degree specified(there are 2 CPU's on the unix box)
========================================
DECLARE
v_jobno number ;
BEGIN
DBMS_JOB.SUBMIT(v_jobno, 'DBMS_STATS.GATHER_SCHEMA_STATS(''SUPPORT'',
ESTIMATE_PERCENT => 35,
CASCADE => TRUE,
DEGREE => 3);');
COMMIT;
END;
/
================================
Followup September 18, 2003 - 5pm Central time zone:
other things on the box contending with the parallel jobs...
all of the data being on too few disks (so the parallel jobs contend with eachother)
parallel has overheads - if the schema has lots of little things, then the overhead of parallel is
greater then any benefit you might get.
use statspack to see what things are "waiting for" and whats going on .
thanks
September 23, 2003 - 10am Central time zone
Reviewer: sravan from Northampton, MA
Tom,
will look into it.
dbms_stats
September 25, 2003 - 8am Central time zone
Reviewer: K.venkat from India
Tom,
Using dbms_stats, to get the index analyzed, should we use the cascade option? When one should go
for index analysis?
Thanks,
Venkat
Followup September 25, 2003 - 8am Central time zone:
cascade => true is one method.
you might do individual indexes if you wanted
o to do the simultaneously
o gather different leves of stats for different indexes
Table lock ....
September 25, 2003 - 6pm Central time zone
Reviewer: reader
When i use dbms_stats package to collect statistics on a table, does it lock the table like analyze
table command? Thanks.
Followup September 25, 2003 - 11pm Central time zone:
no but then neither does analyze (unless you are doing validate structure and then you can use
validate online in 9i)
Validate structure
September 27, 2003 - 11am Central time zone
Reviewer: Reader
Is there an equivalent procedure in dbms_stats to validate structure online? Thanks. I am trying my
best to digest enormous information that you have provided in your new book Effective Oracle by
Design, that is simply superb.
Followup September 27, 2003 - 1pm Central time zone:
analyze is slated to do this and this alone at some time in the future. analyze is destined to be
the "validate" thing -- analyze validate structure, analyze list chained rows, etc.
dbms_stats will stick with stats and stats only.
Table lock ? Commit ?
September 28, 2003 - 12am Central time zone
Reviewer: A reader
If not table lock how come we cannot compile a proc using that table when analyzing is in process
using dbms_stats
Also I see dbms_stats issues commits automatically Correct ?
Followup September 28, 2003 - 9am Central time zone:
it doesn't work that way for me.
I just kicked off a dbms_stats.gather_table_stats on a big_table
I then proceeded to create and compile procedures that access that table
no problem.
as dbms_stats updates the data dictionary, it'll commit in order to make those changes visible and
so as to not lock up the dictionary entries.
Table Lock ,commit ?
September 29, 2003 - 10pm Central time zone
Reviewer: A reader
You said
Followup:
no but then neither does analyze (unless you are doing validate structure
and then you can use validate online in 9i).
Are you sure because it does lock the table otherwise why would proc wait to compile while anlyze
is going.I know this may not be the correct way just tyring to understand the behaviour of
analyzing and dbms_stats. Yours is the the correct way.
Followup September 30, 2003 - 7am Central time zone:
it *doesn't* block the procedure.
Ok, so I write a shell script:
#!/bin/csh -vx
echo analyze table big_table compute statistics > analyze.sql
echo / >> analyze.sql
echo exit >> analyze.sql
sqlplus big_table/big_table @analyze &
sleep 5
sqlplus big_table/big_table <<EOF
@showsql
create or replace procedure p
as
begin
for x in ( select * from big_table )
loop
null;
end loop;
end;
/
exit
EOF
that'll kick off an analyze, give it a couple of seconds to start... then it creates a
procedure in another session that references the table being analyzed. The output of that was:
$ ./test.sh
echo analyze table big_table compute statistics > analyze.sql
echo analyze table big_table compute statistics
echo / >> analyze.sql
echo /
echo exit >> analyze.sql
echo exit
sqlplus big_table/big_table @analyze &
[1] 28005
sqlplus big_table/big_table @analyze
sleep 5
sleep 5
SQL*Plus: Release 8.1.7.0.0 - Production on Tue Sep 30 06:57:40 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
sqlplus big_table/big_table << EOF
sqlplus big_table/big_table
SQL*Plus: Release 8.1.7.0.0 - Production on Tue Sep 30 06:57:45 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
big_table@ORA817DEV>
USERNAME SID_SERIAL STATUS MODULE LAST_CALL_ET
--------------- --------------- ---------- --------------- ------
BIG_TABLE '8,30332' ACTIVE 01@ analyze.sql 4
BIG_TABLE '9,231' ACTIVE 01@ showsql.sql 0
2 rows selected.
that shows the analyze is running and has been for about 4 seconds...
--------------------
BIG_TABLE(8,30332) ospid = 28005 command = 62 program = sqlplus@aria-dev (TNS V1-V3) dedicated
server=28007
Tuesday 06:57 Tuesday 06:57 last et = 4
analyze table big_table compute statistics
big_table@ORA817DEV> 2 3 4 5 6 7 8 9
Procedure created.
and at the same time -- we created a procedure.
big_table@ORA817DEV> big_table@ORA817DEV> Disconnected from Oracle8i Enterprise Edition Release
8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Monitoring Threshold
November 7, 2003 - 3am Central time zone
Reviewer: Matt from UK
"You can either gather stats every night (if about 10% of the table will change
-- thats the threshold monitoring will use) or monitor it and gather stale."
Our tests look to show that monitor will show *all* changes to the table - regardless of the 10%
threshold, and the dbms_stats gather stale filters out the 10%.
This is on 8.1.7.4 does behaviour change with 9i ?
Followup November 7, 2003 - 9am Central time zone:
yes, monitor monitors all changes and we only gather stats when it hits a threshold. I'm not sure
what you mean by this tho? the threshold that triggers that gather and resets the monitoring is
about 10% change.
oops... the proof
November 7, 2003 - 4am Central time zone
Reviewer: Matt from UK
SQL> select count(*) from stat_test_table
2 /
COUNT(*)
----------
1000
1 row selected.
SQL>
SQL> select num_rows,to_char(last_analyzed,'DD-MON-RRRR HH24:MI:SS') from all_tables
2 where table_name = 'STAT_TEST_TABLE'
3 /
NUM_ROWS TO_CHAR(LAST_ANALYZE
---------- --------------------
1000 07-NOV-2003 08:59:45
1 row selected.
SQL>
SQL> select table_name, inserts, updates,deletes from all_tab_modifications
2 /
no rows selected
SQL>
SQL> insert
2 into stat_test_table
3 select rownum+1000,
4 owner,
5 object_name,
6 object_type,
7 1,
8 1
9 from all_objects
10 where rownum <=5
11 /
5 rows created.
SQL>
SQL> commit;
Commit complete.
--
-- Force the refresh of all_tab_modifications by restarting instance
--
SVRMGR> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 127309948 bytes
Fixed Size 116860 bytes
Variable Size 94253056 bytes
Database Buffers 32768000 bytes
Redo Buffers 172032 bytes
Database mounted.
Database opened.
--
-- Reconnect
--
SQL> conn matt/matt
Connected.
SQL> select table_name, inserts, updates,deletes from all_tab_modifications
/ 2
TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
STAT_TEST_TABLE 5 0 0
begin
dbms_stats.gather_schema_stats ('MATT', options => 'GATHER STALE');
end;
SQL> select table_name, inserts, updates,deletes from all_tab_modifications
/ 2
TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
STAT_TEST_TABLE 5 0 0
--
-- Now Insert Higher Than 10%
--
1 insert
2 into stat_test_table
3 select rownum+1200,
4 owner,
5 object_name,
6 object_type,
7 1,
8 1
9 from all_objects
10* where rownum <=110
SQL> /
110 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from stat_test_table;
COUNT(*)
----------
1115
1 row selected.
--
-- BOUNCE THE INSTANCE
--
1* select table_name, inserts, updates,deletes from all_tab_modifications
SQL> /
TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
STAT_TEST_TABLE 115 0 0
1 row selected.
SQL> l
1 begin
2 dbms_stats.gather_schema_stats ('MATT', options => 'GATHER STALE');
3 end;
4*
SQL> /
PL/SQL procedure successfully completed.
SQL> select table_name, inserts, updates,deletes from all_tab_modifications
2 /
no rows selected
1* select num_rows,to_char(last_analyzed,'DD-MON-RRRR HH24:MI:SS') from all_tables
SQL> 2 where table_name = 'STAT_TEST_TABLE'
SQL> /
NUM_ROWS TO_CHAR(LAST_ANALYZE
---------- --------------------
1115 07-NOV-2003 09:46:01
1 row selected.
Followup November 7, 2003 - 9am Central time zone:
yes, that is the way it works -- dbms_stats uses the 10% threshold and resets the monitoring
statistics.
'GATHER STALE' is not working!?
November 24, 2003 - 6am Central time zone
Reviewer: harri
Hi!
I have this procedure:
SQL> select text from dba_source where name='HK_ANALYZE';
TEXT
--------------------------------------------------------------------------------
procedure hk_analyze
(v_schema in varchar2,
v_estimate_pct in number default 0,
v_degree in number default 1)
as
a dbms_stats.objecttab;
start_time varchar(30);
stop_time varchar(30);
begin
select TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') into start_time from dual;
dbms_stats.gather_schema_stats
(OWNNAME => v_schema,
ESTIMATE_PERCENT => v_estimate_pct,
CASCADE => TRUE,
GRANULARITY => 'ALL',
OPTIONS=>'GATHER EMPTY',
DEGREE => v_degree,
OBJLIST=>a);
dbms_output.put_line('1. kerran analysoidut');
for i in 1 .. a.count
loop
dbms_output.put_line( a(i).ownname || '.' ||a(i).objName ||' ('||a(i).objT
ype || ')' );
end loop;
dbms_output.put_line('-----');
dbms_stats.gather_schema_stats
(OWNNAME => v_schema,
ESTIMATE_PERCENT => v_estimate_pct,
CASCADE => TRUE,
GRANULARITY => 'ALL',
OPTIONS=>'GATHER STALE',
DEGREE => v_degree,
OBJLIST=>a);
dbms_output.put_line('Analysoitu, koska vM-dhintM-dM-dn 10% muuttunut:');
for i in 1 .. a.count
loop
dbms_output.put_line( a(i).ownname || '.' ||a(i).objName ||' ('||a(i).objT
ype || ')' );
end loop;
select TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') into stop_time from dual;
dbms_output.put_line('-----');
DBMS_OUTPUT.PUT_LINE('Analysointi alkoi: ' || start_time);
DBMS_OUTPUT.PUT_LINE('Analysointi loppui: ' || stop_time);
end hk_analyze;
45 rows selected.
******
SQL> create table MYYNTI.HK_TESTI as select * from dba_objects where rownum<1000;
Table created.
SQL> exec hk_analyze('MYYNTI', 99,1)
1. kerran analysoidut:
"MYYNTI"."HK_TESTI" (TABLE)
-----
Analysoitu, koska vM-dhintM-dM-dn 10% muuttunut:
-----
Analysointi alkoi: 24-NOV-2003 13:13:14
Analysointi loppui: 24-NOV-2003 13:13:15
PL/SQL procedure successfully completed.
...this is ok. Table is analyzed first time.
SQL> exec hk_analyze('MYYNTI', 99,1)
1. kerran analysoidut:
-----
Analysoitu, koska vM-dhintM-dM-dn 10% muuttunut:
-----
Analysointi alkoi: 24-NOV-2003 13:13:21
Analysointi loppui: 24-NOV-2003 13:13:21
PL/SQL procedure successfully completed.
...and this is ok. No inserts or updates to that table. But then gather stale-part of the procedure
is not working:
SQL> insert into MYYNTI.HK_TESTI select * from dba_objects where rownum<110;
109 rows created.
SQL> commit;
Commit complete.
SQL> exec hk_analyze('MYYNTI', 99,1)
1. kerran analysoidut:
-----
Analysoitu, koska vM-dhintM-dM-dn 10% muuttunut:
-----
Analysointi alkoi: 24-NOV-2003 13:13:54
Analysointi loppui: 24-NOV-2003 13:13:55
PL/SQL procedure successfully completed.
SQL> insert into MYYNTI.HK_TESTI select * from dba_objects where rownum<110;
109 rows created.
SQL> commit;
Commit complete.
SQL> exec hk_analyze('MYYNTI', 99,1)
1. kerran analysoidut:
-----
Analysoitu, koska vM-dhintM-dM-dn 10% muuttunut:
-----
Analysointi alkoi: 24-NOV-2003 13:14:11
Analysointi loppui: 24-NOV-2003 13:14:11
PL/SQL procedure successfully completed.
SQL> delete from MYYNTI.HK_TESTI where rownum<400;
399 rows deleted.
SQL> exec hk_analyze('MYYNTI', 99,1)
1. kerran analysoidut:
-----
Analysoitu, koska vM-dhintM-dM-dn 10% muuttunut:
-----
Analysointi alkoi: 24-NOV-2003 13:14:41
Analysointi loppui: 24-NOV-2003 13:14:41
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> exec hk_analyze('MYYNTI', 99,1)
1. kerran analysoidut:
-----
Analysoitu, koska vM-dhintM-dM-dn 10% muuttunut:
-----
Analysointi alkoi: 24-NOV-2003 13:14:48
Analysointi loppui: 24-NOV-2003 13:14:48
PL/SQL procedure successfully completed.
SQL> update MYYNTI.HK_TESTI set owner='TESTI' where owner='SYS';
818 rows updated.
SQL> commit;
Commit complete.
SQL> exec hk_analyze('MYYNTI', 99,1)
1. kerran analysoidut:
-----
Analysoitu, koska vM-dhintM-dM-dn 10% muuttunut:
-----
Analysointi alkoi: 24-NOV-2003 13:15:36
Analysointi loppui: 24-NOV-2003 13:15:36
PL/SQL procedure successfully completed.
SQL>
******
translations of strings
"Analysoitu, koska vM-dhintM-dM-dn 10% muuttunut:" =
analyzed becouse of 10% changes
"1. kerran analysoidut" = first time analyzed
****
So, what is wrong here?
-Harri
Followup November 24, 2003 - 8am Central time zone:
depending on your version (and you do not seem to mention one) the stats info is flushed every 3
hours or during a normal shutdown. If you have current releases, this is not the case.
also -- you never made the table monitoring?? alter table T monitoring; else it will never be
stale.
I tested in 9i with monitoring and it worked fine.
GATHER-problem
November 24, 2003 - 2pm Central time zone
Reviewer: HK from Finland
The version of that db is 8.1.7.4.0.
I did't know that "flush"-thing. Now, when flush is done it's analyzing that table:
SQL> exec hk_analyze('MYYNTI', 30,2)
1. kerran analysoidut:
-----
Analysoitu, koska yli 10% muuttunut:
"MYYNTI"."HK_TESTI" (TABLE)
-----
Analysointi alkoi: 24-NOV-2003 21:21:09
Analysointi loppui: 24-NOV-2003 21:21:13
*****
Thanks for Your information!!
Originally I developed procedure in 9i db. In procedure there I have line:
dbms_stats.ALTER_SCHEMA_TAB_MONITORING(v_schema, TRUE);
That's not working in 8i. So, now I added lines:
begin
for asd in no_monitor loop
begin
execute immediate ('alter table '|| v_schema || '.' || asd.table_name || ' monitoring');
exception
when e_busy then
DBMS_OUTPUT.PUT_LINE('Setting monitoring on table' || v_schema || '.' || asd.table_name || '
was unsuccesfull!');
end;
end loop;
...and it seems to work fine. What You think, is that a good solution for that?
Followup November 24, 2003 - 3pm Central time zone:
yup, thats perfect (the execute immediate)
move statistics between users
December 8, 2003 - 1pm Central time zone
Reviewer: A reader
Hi
we have 2 schemas in our database, one is development and the other query testing.
we analyze development schema once every 2 days, it takes around 8 hours. how can we move these
statistics from deveopment schema to to query testing schema?
with dbms_stats.get/set or dbms_stats.export/import?
What is the difference?
Followup December 9, 2003 - 5am Central time zone:
dbms_stats export and import.
export and import copy stats from place to place
set lets you manaully "set" them to whatever values you want.
gather_table_stats & gather stale option
February 6, 2004 - 2pm Central time zone
Reviewer: Craig from NW Arkansas
Does the gather stale option work only with gather_schema_stats, or could you gather stale for a
single table using gather_table_stats?
All the examples and the docs seem to imply gather stale works only with gather_schema_stats.
Followup February 7, 2004 - 1pm Central time zone:
gather_schema|database_stats actually.
If you want to use gather_table_stats only on stale tables ( presumuably because you want different
method_opts or something) you would use gather_schema_stats with LIST STALE to get a list of stale
objects that you could then use gather_table_stats on.
Results of a Trace of Gather_Table_Stats for Global Statistics
February 9, 2004 - 6pm Central time zone
Reviewer: Doug Wingate from New Orleans, LA
I've skimmed through this thread and, although I think my question is germaine to the topic, I
didn't find that it had already been discussed. If I'm mistaken, please just tell me where I can
read your prior response. Thanks.
In the course of my work today, I had occasion to execute this call to Gather_Table_Stats to
refresh the global statistics on a partitioned data warehouse fact table. I confess that the
database is at level 8.1.7.3. (We're working on upgrading to level 9.2.)
EXECUTE DBMS_STATS.GATHER_TABLE_STATS (-
ownname => 'WAREHOUSE',-
tabname => 'CHARGES',-
partname => NULL,-
estimate_percent => 3,-
block_sample => TRUE,-
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 75',-
degree => 4,-
granularity => 'GLOBAL',-
cascade => TRUE,-
stattab => NULL,-
statid => NULL,-
statown => NULL);
I traced the execution of the procedure and looked at the trace file to see what I could learn
about how Gather_Table_Stats does its work of gathering global statistics. I found this ANALYZE
statement buried deep in the trace file.
analyze table "WAREHOUSE"."CHARGES"
ESTIMATE statistics sample 3 percent
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS
SIZE 75
Setting aside, if possible, the matter of the index statistics also gathered here, are 'global
statistics' really merely the same as the statistics one can gather with 'ANALYZE TABLE...ESTIMATE
STATISTICS...FOR TABLE;'? I mean, if you or someone else wishes to say that Gather_Table_Stats
guides and simplifies the writing of ANALYZE statements by correctly writing ANALYZE statements on
users' behalf, in response to simple parameters that allow the user to clearly express his
intention, rather than leaving the user to flounder and perhaps write the statement incorrectly, I
agree. But is there any more to it than that even in the case of global statistics?
Thanks.
Followup February 9, 2004 - 8pm Central time zone:
In 8i, dbms_stats many times dropped down to "analyze" (serial)
In 9i, it does so infinitely less often.
So, you are just seeing the artifacts of an implementation over time. In 8i, anything with the
size > 1 wasn't parallelized (done with analyze). Later, when done with sql, it could be.
Very Helpful
February 10, 2004 - 2pm Central time zone
Reviewer: Doug Wingate from New Orleans
Thanks. It hadn't occurred to me until now that to the extent Gather_Table_Stats uses SELECT
statements to gather statistics, its execution plans can be optimized just like any other SELECT
statement's plan can be optimized.
global statistics
May 6, 2004 - 5am Central time zone
Reviewer: Marvin
Hi
I am using dbms_stats for a oltp database with hash and range partitioned tables. I use gather
stale option this leads me to some doubts, when I run dbms_stats I gather statistics only for those
partitions which have been gone over 10% DML (as monitoring suggests) so my GLOBAL_STATS is never
updated, can this lead to problems? The other option is set granularity to 'ALL' (i have
subpartitions too) but this is not a very helpful option. By setting to ALL I am forced to analyze
all partitions again which means the gather_stale is useless (not only gathering modified objects
but ALL)
Is this how dbms_stats works...? Is there anyway to do cumulative global stats?
Followup May 6, 2004 - 7am Central time zone:
the only way to get global stats is to look at all partitions. The reason is:
if column X has 10 distinct values in each of P1, P2, ... P10 -- does column x have
a) 10 distinct values in the table..
b) 100 distinct values
c) some number between 10 and 100
the answer is any of the above -- you have to look at them all.
So, you can use dbms_stats to LIST STALE and gather global stats on all tables with stale
partitions.
partition stats are used by the optimizer when a query is known to evaluate to a FIXED partition.
Eg: select * from partitioned_tab where part_key = 55 would use partition stats since the
optimizer would know at optimize time which partition it will hit. However, "select * from
partitioned_tab where part_key = :x" would not since the partition is not known until runtime (bind
time). That does not mean "don't use binds", that does mean "gather global stats"
exec dbms_stats.set_table_stats( user, 't', numrows => 10000, numblks => 1000 );
May 26, 2004 - 3pm Central time zone
Reviewer: sonali from waltham, ma
select num_Rows from user_tables where table_name = 'T';
execUTE dbms_stats.set_table_stats( user, 'T', numrows => 10000, numblks => 1000 );
From what I understand reading all the discussion that one can set table_stats if the table is not
analyzed( dbms_stats.gather_table_stats..)
Can I use this set_table_stats to fake as if the table has million rows where as in real it has
only 500 rows. This way I can forcee the query plan , table design, indexes etc..at the customer
site.
We always have this issue with performanace as we have small development databases and huge
customer databases.
Thanks
Followup May 26, 2004 - 4pm Central time zone:
you need to have a huge customer database upon which to test with.
what if Oracle said "you know what, we don't really have any large databases to test with, we'll
just try 500meg or so. If it works OK with that, we can ship it"
same concept -- we test larger than our customers go, just to know that it has a chance of working.
otherwise, we know it has no chance.
So what if you set the stats and tell your test instance (that has 1000 rows) that it should
pretend it has 1,000,000.
What would you gain from that?
o not the knowledge of how the query will really perform
o not the knowledge of how the query will scale
the only thing you gain is "i can see the probable plan it'll use". Now what?
get a terabyte of disk. fill it up.
dbms_stats.auto_sample_size ????
May 27, 2004 - 2am Central time zone
Reviewer: Neeraj from india
SELECT COUNT(*) FROM GCA_DS_PRS_EXT_PUB
57207324
Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'GCA_USER',TABNAME =>
'GCA_DS_PRS_EXT_PUB',ESTIMATE_PERCENT =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=> 'for all INDEXED
COLUMNS size 1',cascade => true,degree=>3);
value of sample_size is 57207323 and num_rows is 57207323 why ?????
Is it mean estimation is 100% ?
Followup May 27, 2004 - 9am Central time zone:
auto means auto. auto means we do what we want. there are many triggers that say "hey, we've seen
some things that lead us to believe we want to do 50% or more" and once you hit 49%+ -- analyze
becomes compute since you are touching pretty much every byte anyway.
DBMS_STATS.AUTO_SAMPLE_SIZE
May 27, 2004 - 11pm Central time zone
Reviewer: friend from india
thanx TOM.
But still i want to share these stats with you.
Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'GCA_USER',TABNAME => 'PUB',ESTIMATE_PERCENT
=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=> 'for a
ll INDEXED COLUMNS size 1',cascade => true,degree=>3);
PL/SQL procedure successfully completed.
Elapsed: 00:46:22.25
select sample_size,num_rows from user_tables where table_name='PUB';
SAMPLE_SIZE NUM_ROWS
----------- ----------
57207323 57207323
Elapsed: 00:00:00.03
Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'GCA_USER',TABNAME => 'PUB',ESTIMATE_PERCENT
=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=> 'for a
ll INDEXED COLUMNS size 1',cascade => true,degree=>3);
PL/SQL procedure successfully completed.
Elapsed: 00:52:26.88
select sample_size,num_rows from user_tables where table_name='PUB';
SAMPLE_SIZE NUM_ROWS
----------- ----------
57207323 57207323
Elapsed: 00:00:00.08
select sample_size,num_rows from user_tables where table_name='BOOK';
SAMPLE_SIZE NUM_ROWS
----------- ----------
4968487 24842435
Elapsed: 00:00:00.05
Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'GCA_USER',TABNAME => 'BOOK',ESTIMATE_PERCENT
=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=> 'for a
ll INDEXED COLUMNS size 1',cascade => true,degree => 3);
PL/SQL procedure successfully completed.
Elapsed: 01:16:55.60
select sample_size,num_rows from user_tables where table_name='BOOK';
SAMPLE_SIZE NUM_ROWS
----------- ----------
24842085 24842085
Elapsed: 00:00:00.01
Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'GCA_USER',TABNAME => 'BOOK',ESTIMATE_PERCENT
=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=> 'for a
ll INDEXED COLUMNS size 1',cascade => true,degree => 3);
PL/SQL procedure successfully completed.
Elapsed: 01:16:49.76
SAMPLE_SIZE NUM_ROWS
----------- ----------
24842085 24842085
Elapsed: 00:00:00.02
Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'GCA_USER',TABNAME => 'BILL',ESTIMATE_PERCENT
=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'for
all INDEXED COLUMNS size 1',CASCADE => TRUE,degree => 3);
PL/SQL procedure successfully completed.
Elapsed: 01:14:53.66
select sample_size,num_rows from user_tables where table_name='BILL';
SAMPLE_SIZE NUM_ROWS
----------- ----------
24046022 24046022
Elapsed: 00:00:00.02
Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'GCA_USER',TABNAME => 'BILL',ESTIMATE_PERCENT
=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'for
all INDEXED COLUMNS size 1',CASCADE => TRUE,degree => 3);
PL/SQL procedure successfully completed.
Elapsed: 01:10:09.34
select sample_size,num_rows from user_tables where table_name='BILL';
SAMPLE_SIZE NUM_ROWS
----------- ----------
24046022 24046022
Followup May 28, 2004 - 8am Central time zone:
thanks?
friend
May 28, 2004 - 8am Central time zone
Reviewer: friend from india
welcome sir
but still ihave a proble :)
as sample_size is equal to num_rows and count(*) of base table
and some very good dba ( under whom i am working) pointing on these stats and planning to implement
estimate_percent=> 20 as acc to them its 100% ( num_rows = count(*) of table)
Followup May 28, 2004 - 11am Central time zone:
you either want "auto" or you don't. It is up to you.
dbms_stats
May 28, 2004 - 8am Central time zone
Reviewer: friend from india
i mean accordingt o them if sample_size= count(*) from tables = num_rows from user_tables then it
stands for estimate_percent => 100
Need words from you to explain them and also i have to buy your book and i am getting here expert
one on one wrex publisher please confirm the name....
and effective oracle by design is different from above ????
Followup May 28, 2004 - 11am Central time zone:
auto means just that, you are saying "i don't want to set the details, please use your best
judgement". the algorithm is saying "you know what, we want to do it all"
i have two main books, expert one on one oracle (apress is current publisher), and effective oracle
by design (oraclepress)
June 8, 2004 - 10am Central time zone
Reviewer: Thiru
user logged in : my_user
when dbms_stats.delete_schema_stats('MY_USER') is run, the stats are not
deleted as can be verified from user_object_tables and user_indexes
while if I am logged in as system and run
dbms_stats.delete_schema_stats('MY_USER') then the stats is gone in
my_user schema.
Any reason for this. No errors come up when it is run by my_user. Also
my_user has dba role.
Followup June 8, 2004 - 11am Central time zone:
can you help me to reproduce the issue?
ops$tkyte@ORA9IR2> drop user my_user
2 cascade;
User dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant dba to my_user identified by my_user;
Grant succeeded.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect my_user/my_user;
ops$tkyte@ORA9IR2> set termout off
my_user@ORA9IR2> set termout on
my_user@ORA9IR2>
my_user@ORA9IR2> create table t as select * from scott.emp;
Table created.
my_user@ORA9IR2>
my_user@ORA9IR2> exec dbms_stats.gather_schema_stats( user );
PL/SQL procedure successfully completed.
my_user@ORA9IR2> select num_rows from user_tables;
NUM_ROWS
----------
14
my_user@ORA9IR2>
my_user@ORA9IR2> exec dbms_stats.delete_schema_stats( user );
PL/SQL procedure successfully completed.
my_user@ORA9IR2>
my_user@ORA9IR2> select num_rows from user_tables;
NUM_ROWS
----------
my_user@ORA9IR2>
June 8, 2004 - 12pm Central time zone
Reviewer: A reader
I was checking only user_object_tables and user_indexes and not user_tables.
The delete schema stats is working for heap tables only and not for object tables.
When I query from user_object_tables the last_analyzed date still shows up. Also user_indexes
stats does not get deleted.
Followup June 8, 2004 - 1pm Central time zone:
in 9.2 and before, object tables are not done by gather schema stats, they are not removed by
delete schema stats. (no, i don't know why)
in 10g, they are. So, in 9i and before, you need to use gather table/index stats for object tables
and delete table/index stats. this applies to nested tables as well.
in 10g, you can use schema stats on them as well.
(i've never used object types to store data, never hit that nuance before myself)
DBMS_STATS.AUTO_SAMPLE_SIZE
June 24, 2004 - 1pm Central time zone
Reviewer: Arun Gupta from Harrisburg, PA USA
Tom,
When I use auto_sample_size, is there a way for me to know what sample size Oracle actually used in
gather_schema_stats? Does the auto_sample_size effect indexes also? After using this command, I
checked the num_rows in a table T1 and it's pk index, PK_T1. The num_rows in PK_T1 was much higher
than num_rows in T1.
The sample_size of T1=num_rows of T1 and
sample_size of PK_T1=num_rows of PK_T1 but
num_rows of T1 < num_rows of PK_T1.
The last_analyzed_time of T1 and PK_T1 is within 13 seconds of each other, there was no other
activity.
My dbms_stats command is:
dbms_stats.gather_schema_stats(
ownname => 'ABC',
estimate_percent =>dbms_stats.auto_sample_size,
options => 'GATHER STALE',
degree => DBMS_STATS.DEFAULT_DEGREE,
cascade => TRUE);
The monitoring option is on. What could be causing this discrepancy?
Thanks
Followup June 24, 2004 - 3pm Central time zone:
the data dictionary has "sample_size" as a column.
what is "much higher"
June 24, 2004 - 3pm Central time zone
Reviewer: Arun Gupta from Harrisburg, PA USA
The num_rows in T1 is 280,855 less than num_rows in PK_T1. The num_rows in T1=8827920 and num_rows
in PK_T1=9108775.
Followup June 24, 2004 - 3pm Central time zone:
that is a 3% margin of error on an estimate. not bad eh?
Just like to you and me, $1,000,000 would be a lot. (making an assumption on your part)
But, to Warren Buffet -- maybe not.
Look at it in proportion there.
DBMS_STATS on Partitioned Table
June 24, 2004 - 8pm Central time zone
Reviewer: Wayne from Phoenix, AZ USA
Tom,
I am analyzing a partition (2.5M rows) of a table using
DBMS_STATS.GATHER_TABLE_STATS(...) specifying:
granularity => 'PARTITION' -- no global stats
cascade => true -- indices only take 3-4 minutes
degree =>2
But it took 1 hour and 37 minutes (Unix/12CPU, Ora817).
If I specify estimate_percent =>10, it took 10 minutes.
My questions:
1) Does the number look ok (10 min for 10% data)? Any parameters might need to be tweaked?
* If I set granularity as default, it will take 3.7 hours for that partition (collect global
stats).
** It took 3.5 minutes to do the following, am I still comparing apples to oranges (except the
degree =2)?
analyze table t partition a estimate statistics sample 10
for all columns
for all local indexes
for all indexed columns
2) DBAs collect global stats on weekends sampling 5% of data (gather_schema_stats). Does 5% look
low? Queries seems to run fine though (select count(*) from the_table returns results (59M) in 3
seconds provided that all local indexes have the fresh stats).
Thanks,
Followup June 25, 2004 - 7am Central time zone:
1) PQ is not always the fastest way to the end. I would drop the degree=>2 and see what happpens.
2.5 rows is not huge. If data is not spread over physical devices, pq probably won't be doing too
much good.
tkprof is always very revealing to see what is happening and what is taking long and what you are
waiting on (10046 level 12 trace)
2) a select count(*) with the CBO will pretty much go for the primary key index using a fast full
scan regardless. It would not be a good benchmark query.
If your data is not heavily skewed, if it is nicely distributed, 5% may be better than good enough,
sure.
Partition Stats
June 25, 2004 - 9pm Central time zone
Reviewer: Wayne
Tom,
I retested the gather_table_stats on a partition with and without parallelization, it works just
fine (3.5 min and 4.5 minutes respectively).
migrate
July 6, 2004 - 2am Central time zone
Reviewer: lotus
dear tom
We are moving from 8i to 9i .
Please suggest the precautions we should take from application point of view?
Please provide dtails and links to study
Ultimate link
July 7, 2004 - 12am Central time zone
Reviewer: Neeraj
Dear All
The above is link is magic box of docuementation.
Thanx Tom :)
Best Regards
DBMS_STATS.DEFAULT_DEGREE
July 15, 2004 - 9am Central time zone
Reviewer: Vlado from Norristown, PA
I'm trying to figure out which init.ora parameters DBMS_STATS.DEFAULT_DEGREE depends on. Have not
found anything in the Oracle documentation nor Metalink. Any ideas?
Followup July 15, 2004 - 12pm Central time zone:
the parallel ones -- parallel automatic tuning, parallel max servers and the like.
total size used by a table
August 4, 2004 - 7am Central time zone
Reviewer: apl from india
Can we get the total size used by the table and total number of rows presented in the table without
analyzing the table? I mean i don't want to use dbms_stats.gather_database_stats or analyze
command.
Followup August 4, 2004 - 10am Central time zone:
select count(*) from t; gives you the number of rows.
select blocks from dba_segments where segment_name = 'T' and owner = user;
gives you the allocated space.
The query is wrong!!!!!!!!
August 4, 2004 - 6pm Central time zone
Reviewer: A reader
The query provided by Tom is wrong.
Here is the correct query:
select blocks from dba_segments where segment_TYPE = 'TABLE' and owner = user;
I hope it is the right answer. As segement_name = 'T' does not exist in 9iR2. And also it should
have been segment_type.
Tom must have been in a hurry. ;)
Sorry I must have been in hurry!!!!!!!
August 4, 2004 - 6pm Central time zone
Reviewer: A reader
Sorry Tom.
I must have been in hurry. The query is right. I just did not read it properly.
Quite embarrassing I must admit...
Space Used
August 5, 2004 - 12am Central time zone
Reviewer: APL
But i want the used space by a table.
Actually my requirement is gather all the used space for every table in user schemas for each user
in a database without using 'analyzing' and 'dbms_gather_stats'.
Followup August 5, 2004 - 12pm Central time zone:
and which part of my response did not meet that requirement?
you asked "number of rows" -- i responded with "select count(*)"
you asked "size consumed" -- i responded with a query against dba_segments.
No stats, no analyze, no dbms_stats - but both answers recieved.
What was missing?
Size of table
August 6, 2004 - 12am Central time zone
Reviewer: APL
What about the free space? I want the used space ie excluding the PCTFREE?
I think from your query we will get the allocated blocks.
Followup August 6, 2004 - 8am Central time zone:
that is correct -- and what was asked for (you get what you ask for, nothing more, nothing less)
"Can we get the total size used by the table and total number of rows presented
in the table without analyzing the table?"
the total size used by the table is in dba_segments. that is what is allocated to, therefore "in
use"
what is "used space excluding the pctfree"? pctfree is just a number "please keep 10% free during
insertions". with pctfree of 90, you can have blocks that are 100% full, 50% full, 10% full, 0%
full.
if you want the "bytes consumed by rows in the table"
select count(*), sum( nvl(vsize(c1),0) + nvl(vsize(c2),0) + ... + nvl(vsize(cN,0)) from t;
what is the correct granualarity in this case
August 18, 2004 - 7am Central time zone
Reviewer: Ajeet from Bombay
Tom,
I have a table called rdm_f_parameter --
SQL> desc rdm_f_parameter ;
Name Null? Type
----------------------------------------- -------- --------------
DIM_SEQ_ID NOT NULL NUMBER(9)
PARAM_SEQ_ID NOT NULL NUMBER(9)
FLEET NOT NULL VARCHAR2(10)
FLIGHT_DATETIME NOT NULL DATE
PARAMETER_VALUE NOT NULL NUMBER(15,4)
CREATION_DATE NOT NULL DATE
It is partitioned by range on flight_datetime
and subpartition by list on fleet.
all queries on this table will have flight_datetime and fleet both in the where clause -- a sample
SQL is below --
select
a.flight_datetime
,max( decode( c.parameter_name, 'DEGT', a.parameter_value, NULL ) ) DEGT
,max( decode( c.parameter_name, 'DPOIL', a.parameter_value, NULL ) ) DPOIL
,max( decode( c.parameter_name, 'BRAT', a.parameter_value, NULL ) ) BRAT
from
rdm.rdm_d_plot b
,rdm.rdm_d_param c
,rdm.rdm_f_parameter a
where a.dim_seq_id = b.dim_seq_id
and a.param_seq_id = c.param_seq_id
and c.parameter_name in ('DEGT','DPOIL','BRAT')
and a.fleet = 'MAS'
and b.fleet = 'MAS'
and b.aircraft_id = '9M-MMQ'
and b.flight_phase = 'CRUISE'
and b.engine_id = '856295'
and a.flight_datetime >= trunc(sysdate) - 360
group by a.flight_datetime ;
the 2 other tables in above sql are kind of Lookup table with maximum 5000 rows.
I have been trying the different granularity options while collection stats using dbms_stats..what
should be the correct granularity -- Partition or Subpartition as I do get a different (very
different ) explain plan.also when I use subpartition as granularity I do get slighlty better
response time.
The reason I asked this is -- the rdm_f_parameter table is very large it has 120 Million rows as
of today.
I have 2 bitmap indexes - on rdm_f_parameter
one on dim_seq_id and another on param_seq_id.
I have kept no index on fleet or flight_datetime as queries will always have these 2 columns in the
where clause.
do you see any desgin issue ?
I don't have a response time or performance issue as of now --and I am still in development
database so wanted to have your opinion.
Thanks and Kind regards,
Ajeet
Followup August 18, 2004 - 9am Central time zone:
do you use bind variables?
do the plans have "key key" in the partition start/stop or a single fixed number?
IF the query plans are always using "fixed partitions" in the partition start/stop, then partition
level stats will be used. eg given:
ops$tkyte@ORA9IR2> CREATE TABLE t
2 (
3 temp_date date,
4 x int,
5 y int
6 )
7 PARTITION BY RANGE (temp_date)
8 subpartition by hash(x)
9 (
10 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users,
11 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace tools,
12 PARTITION junk VALUES LESS THAN (MAXVALUE)
13 )
14 ;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
4 rows deleted.
ops$tkyte@ORA9IR2> explain plan for
2 select * from t where x = 5 and temp_date = to_date('01-jan-2004');
Explained.
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 | | |
|* 1 | TABLE ACCESS FULL | T | 1 | 35 | 2 | 3 | 3 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."X"=5 AND "T"."TEMP_DATE"=TO_DATE('2004-01-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
Note: cpu costing is off
15 rows selected.
local partition statistics on partition "3" will be used to optimize that query plan since the
optimizer knows "partition 3" but...
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x number
ops$tkyte@ORA9IR2> variable y varchar2(20)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
2 rows deleted.
ops$tkyte@ORA9IR2> explain plan for
2 select * from t where x = :x and temp_date = to_date(:y);
Explained.
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------
--------------------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost | Pstart| Pstop
|----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 | | |
| 1 | PARTITION RANGE SINGLE| | | | | KEY | KEY |
| 2 | PARTITION HASH SINGLE| | | | | KEY | KEY |
|* 3 | TABLE ACCESS FULL | T | 1 | 35 | 2 | KEY | KEY |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."X"=TO_NUMBER(:Z) AND "T"."TEMP_DATE"=TO_DATE(:Z))
Note: cpu costing is off
16 rows selected.
will use table (global) statistics to optimize since it is not know what partition(s) will be
accessed -- only that some subset of partitions will be
Bind varaibles most of the time
August 19, 2004 - 4am Central time zone
Reviewer: Ajeet from Bombay
Tom -
Thanks for great explanation - it is now very clear when to use what granularity in dbms_stats for
partitioning table.
In my case most of the quries will use bind variables as we use a stored procedure which passes the
values in the where clause of the sql - I have given above.so use bind variables in 95% of quries
on the above mentioned table.
Also my explain shows keys/keys as pstart and pstop .
However in 5% of cases -- some power user may want to run the above query with hard coded values
for Fleet and Flight_datetime .As informed my table is partition by range on flight_datetime and
subpartiton by list on Fleet.
Thanks so much .
Ajeet
Blocks
August 19, 2004 - 5pm Central time zone
Reviewer: A reader
Hi Tom,
Just wanted to know how to translate blocks in terms of size.
That is I want to get the size of the tablei/indexes in terms of MB, etc.
So how do I interpret blocks in terms of actual size.
Thanks as always
Followup August 19, 2004 - 8pm Central time zone:
query v$parameter to see your block size
ops$tkyte@ORA9IR2> select value from v$parameter where name = 'db_block_size'
2 /
VALUE
-------------------------------------------------------------------------------
8192
block size is 8,192 bytes - now multiply.
export/import_database_stats
August 21, 2004 - 10pm Central time zone
Reviewer: A reader
I frequently get errors when using dbms_stats.export_database_stats or import_databse_stats.
Consider
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Aug 21 22:28:50 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> set timing on
SQL> exec dbms_stats.import_database_stats('cbo_stats','2004-07-25.FULL','cbo')
BEGIN dbms_stats.import_database_stats('cbo_stats','2004-07-25.FULL','cbo'); END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object within the database
ORA-06512: at "SYS.DBMS_STATS", line 4914
ORA-06512: at "SYS.DBMS_STATS", line 4943
ORA-06512: at "SYS.DBMS_STATS", line 5460
ORA-06512: at line 1
Elapsed: 00:02:27.73
SQL> show parameter o7
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
SQL>
Why is this?
When this happens, what state is the dictionary left it? Are part of the stats imported or nothing?
How can I tell?
Thanks
Followup August 22, 2004 - 8am Central time zone:
please contact support to see if you are hitting <Bug:3026474> on the import statistics and for
guidance on the status of your statistics (you could enable sql_trace and see if there are any
XCTEND -- commit -- records in there to see if some stats where committed before the error)
is it necessary to delete statistics before recalculating them
September 8, 2004 - 9am Central time zone
Reviewer: A reader
I was wondering if it is recommended as part of the regular maintenance of statistics, to first
delete them before recalculating them on a regular interval. i.e. if I have
dbms_stats.gather_schema_stats running every night for a specified schema, should I first run
dbms_stats.delete_schema_stats as normal practice, or should I only worry about deleting the stats
if I change the way/parameters of my anaylze job?
Followup September 8, 2004 - 10am Central time zone:
it would be:
a) save the old ones (export stats)
b) gather new
no need to remove the old (that leaves you at a point in time with NO stats -- eg: RBO kicks in or
even as bad -- cbo gets some analyzed and unanalyzed tables)
create index ... compute statistics
October 13, 2004 - 2pm Central time zone
Reviewer: A reader
I have some large tables with indexes. I gathered stats on these tables using dbms_stats
(cascade=>true,method_opt=>'for all indexed columns')
Now, I want to create a new index on one of the existing columns
If I do
create index ... compute statistics;
is this the same as
create index ...;
and regathering stats on the table using (cascade=>true,method_opt=>'for all indexed columns')
The latter would do much more work than the former by gathering stats on the table and all the
other indexes which I dont necessarily want.
[The reason I ask is that I am seeing some queries where the CBO is not properly picking the new
index. If I hint the query to use the new index, the LIOs drop by orders of magnitude]
Thanks
Followup October 13, 2004 - 3pm Central time zone:
ops$tkyte@ORA9IR2> create table t
2 as
3 select * from all_objects;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for all indexed
columns', cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t1_idx on t(object_id) COMPUTE STATISTICS ;
Index created.
ops$tkyte@ORA9IR2> create index t2_idx on t(owner) COMPUTE STATISTICS ;
Index created.
ops$tkyte@ORA9IR2> column column_name format a15
ops$tkyte@ORA9IR2> select index_name, clustering_factor, num_rows from user_indexes where
table_name = 'T';
INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
------------------------------ ----------------- ----------
T1_IDX 24513 27897
T2_IDX 397 27897
ops$tkyte@ORA9IR2> select column_name, count(*) from user_tab_histograms where table_name = 'T'
group by column_name;
COLUMN_NAME COUNT(*)
--------------- ----------
OBJECT_ID 2
OWNER 2
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for all indexed
columns', cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select index_name, clustering_factor, num_rows from user_indexes where
table_name = 'T';
INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
------------------------------ ----------------- ----------
T1_IDX 24513 27897
T2_IDX 397 27897
ops$tkyte@ORA9IR2> select column_name, count(*) from user_tab_histograms where table_name = 'T'
group by column_name;
COLUMN_NAME COUNT(*)
--------------- ----------
OBJECT_ID 76
OWNER 17
it does not gather histograms -- just basic column level statistics
outstanding as always
October 13, 2004 - 4pm Central time zone
Reviewer: A reader from FL, USA
November 9, 2004 - 9am Central time zone
Reviewer: A reader
OK so if I do
1) dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all indexed columns')
2) create index t_idx on t(i) compute statistics;
3) dbms_stats.gather_table_stats(user,'t',cascade=>false,method_opt=>'for columns i')
that would be like doing (1) with the index already in place?
(3) seems a bit expensive just to gather histograms on the
column. It would re-gather stats on the table as well. Is there a way to do just the histograms on
a specific column without re-gathering stats on the table itself?
Thanks
Followup November 9, 2004 - 10am Central time zone:
No, the stats gathered on the create index are just column level stats (hi/lo/num distinct). the
other other (3) gathers histograms. consider:
ops$tkyte@ORA9IR2> create table t as select * from all_objects;
Table created.
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade => true, method_opt=>'for
all indexed columns' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select 'table', table_name, num_rows from user_tables where table_name = 'T'
2 union all
3 select 'index', index_name, num_rows from user_indexes where table_name = 'T'
4 union all
5 select 'cols', column_name, count(*) from user_tab_histograms where table_name = 'T' group by
column_name;
'TABL TABLE_NAME NUM_ROWS
----- ------------------------------ ----------
table T 27928
ops$tkyte@ORA9IR2> create index t_idx on t(object_type) compute statistics;
Index created.
ops$tkyte@ORA9IR2> select 'table', table_name, num_rows from user_tables where table_name = 'T'
2 union all
3 select 'index', index_name, num_rows from user_indexes where table_name = 'T'
4 union all
5 select 'cols', column_name, count(*) from user_tab_histograms where table_name = 'T' group by
column_name;
'TABL TABLE_NAME NUM_ROWS
----- ------------------------------ ----------
table T 27928
index T_IDX 27928
cols OBJECT_TYPE 2
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade => false,
method_opt=>'for columns object_type' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select 'table', table_name, num_rows from user_tables where table_name = 'T'
2 union all
3 select 'index', index_name, num_rows from user_indexes where table_name = 'T'
4 union all
5 select 'cols', column_name, count(*) from user_tab_histograms where table_name = 'T' group by
column_name;
'TABL TABLE_NAME NUM_ROWS
----- ------------------------------ ----------
table T 27928
index T_IDX 27928
cols OBJECT_TYPE 26
ops$tkyte@ORA9IR2>
3) well -- in order to gather histograms, you are going the scan the table, the table stats sort of
"come for free" there.
November 9, 2004 - 5pm Central time zone
Reviewer: A reader
I got what you meant, but you misunderstood my question
"that would be like doing (1) with the index already in place?"
And the answer is yes
i.e. if my table already had the index and I did cascade=>true,method_opt=>'for all indexed
columns', this would gather index stats and histograms
Instead, if I add the index later (create index ... compute statistics) and I want the same
end-result, I need to do
cascade=>false(since the index DDL already did this)
method_opt=>'for columns xxx' (this will do the histograms)
Thanks
gathering stats on partition
November 16, 2004 - 3pm Central time zone
Reviewer: A reader
Hi Tom,
I am not able to understand the purpose of degree:
if I am gathering stats for a partition, and there is no global index. Will degree helpful? if yes,
what it will do for gathering stats for a partition of a table with no global indices.
Thanks
Followup November 16, 2004 - 11pm Central time zone:
degree is for "parallel"
do you want to do it in parallel? if so, degree is useful, else it is not.
Partition Stats
November 17, 2004 - 2am Central time zone
Reviewer: A reader
Thanks Tom.
So, even for partition stats and local indices, I can gather stats in parallel?
Followup November 17, 2004 - 9am Central time zone:
you can gather statistics on a segment in parallel. partitions are segments.
Thanks a lot!
November 17, 2004 - 2pm Central time zone
Reviewer: A reader
Wrong Sample Size for Indexes with DBMS_STATS
December 27, 2004 - 4pm Central time zone
Reviewer: Kiran Ghanta from Richmond, VA USA
Hi Tom,
I sincerely appreciate your response to my question regarding DBMS_STATS on 9.2.0.4
I am not sure, if I am hitting a bug with DBMS_STATS or not, here is what I have observed while
using DBMS_STATS
=========================================================
TableName : TEST_1 (97million rows) Primary Key : TEST_PK_1
With DBMS_STATS ==>
Est.Percent No.Rows SampleSize Sample%
----------------------------------------------------
Est 5% 96,394,065 247,212 0.256459773
Est 7% 102,514,323 262,908 0.256459773
Est 10% 96,139,054 246,558 0.256459773
Est 20% 99,199,183 254,406 0.256459773
Est 40% 102,259,312 262,254 0.256459774
Est 100% 94,949,004 243,506 0.256459773
With Analyze ==>
Est.Percent No.Rows SampleSize Sample%
----------------------------------------------------
Est 5% 97,408,463 5,101,692 5.237421722
Est 7% 97,409,408 7,347,964 7.543382257
Est 10% 97,410,149 11,212,668 11.51078005
Est 20% 97,410,723 18,946,872 19.4504993
Est 40% 97,411,101 34,701,732 35.62400142
Compute 97,349,264 97,349,264 100
Could you please let me know why the DBMS_STATS is always gathering stats for only .25% sample? I
am seeing this behavior in gathering stats for indexes with both gather_table_stats and
gather_index_stats procedures.
I have tested this with different volumes of data(43million rows and 1.5million rows). The Sample
percents are constant at .456% and 16% respectively.
Please advise,
thanx!
kiran
Followup December 27, 2004 - 5pm Central time zone:
share with us the precise command you used please.
Wrong Sample Size for Indexes with DBMS_STATS
December 27, 2004 - 6pm Central time zone
Reviewer: Kiran Ghanta from Richmond, VA USA
Tom,
Here is the sample test, I ran for the stats
======================================================
rghanta@QA.WORLD > @gather_tbl_stats.sql
rghanta@QA.WORLD > variable n1 number
rghanta@QA.WORLD > exec :n1:=dbms_utility.get_time;
rghanta@QA.WORLD > set echo on
rghanta@QA.WORLD > DECLARE
2 BEGIN
3 dbms_stats.gather_table_stats( ownname => 'RGHANTA',
4 tabname => 'TEST_ALA_NONPART',
5 estimate_percent => 5,
6 block_sample => FALSE,
7 degree => 1,
8 granularity => 'DEFAULT',
9 cascade => TRUE );
10 END;
11 /
rghanta@QA.WORLD > set echo off
Table Test_ALA_NONPART 97million rows Stats gathered in 297.4 secs
rghanta@QA.WORLD > @qindstats
Average Average
Index Distinct Number Leaf Blocks Data Blocks Cluster
Sample Date
Name Unique Keys of Rows Per Key Per Key Factor
Size SamplePcnt MM-DD-YYYY
--------------- --------- ------------ ------------ ----------- ----------- ------------
----------- ---------- ----------
ALA_PK UNIQUE 95,204,015 95,204,015 1 1 2,065,821
244,160 .256459772 12-27-2004
ALA_DL_NPART NONUNIQUE 54 98,561,814 8,372 41,855 2,260,202
253,098 .256791134 12-27-2004
============================
Deleted the table stats using DELETE_TABLE_STATS procedure
rghanta@QA.WORLD > @qindstats
Average Average
Index Distinct Number Leaf Blocks Data Blocks Cluster
Sample Date
Name Unique Keys of Rows Per Key Per Key Factor
Size SamplePcnt MM-DD-YYYY
--------------- --------- ------------ ------------ ----------- ----------- ------------
----------- ---------- ----------
ALA_PK UNIQUE
ALA_DL_NPART NONUNIQUE
rghanta@QA.WORLD > exec dbms_stats.gather_index_stats(user,'ALA_PK',ESTIMATE_PERCENT=>5);
rghanta@QA.WORLD > @qindstats
Average Average
Index Distinct Number Leaf Blocks Data Blocks Cluster
Sample Date
Name Unique Keys of Rows Per Key Per Key Factor
Size SamplePcnt MM-DD-YYYY
--------------- --------- ------------ ------------ ----------- ----------- ------------
----------- ---------- ----------
ALA_PK UNIQUE 93,588,947 93,588,947 1 1 2,036,966
240,018 .256459772 12-27-2004
ALA_DL_NPART NONUNIQUE
==============================
Deleted the index stats using DELETE_INDEX_STATS procedure
rghanta@QA.WORLD > exec dbms_stats.delete_index_stats(user,'ALA_PK');
rghanta@QA.WORLD > @qindstats
Average Average
Index Distinct Number Leaf Blocks Data Blocks Cluster
Sample Date
Name Unique Keys of Rows Per Key Per Key Factor
Size SamplePcnt MM-DD-YYYY
--------------- --------- ------------ ------------ ----------- ----------- ------------
----------- ---------- ----------
ALA_PK UNIQUE
ALA_DL_NPART NONUNIQUE
Gathered index stats with analyze command..
rghanta@QA.WORLD > analyze index ala_pk estimate statistics sample 5 percent;
rghanta@QA.WORLD > @qindstats
Average Average
Index Distinct Number Leaf Blocks Data Blocks Cluster
Sample Date
Name Unique Keys of Rows Per Key Per Key Factor
Size SamplePcnt MM-DD-YYYY
--------------- --------- ------------ ------------ ----------- ----------- ------------
----------- ---------- ----------
ALA_PK UNIQUE 97,408,462 97,408,463 1 1 2,109,950
5,101,692 5.23742173 12-27-2004
ALA_DL_NPART NONUNIQUE
====================================
Pl.let me know if you find any thing wrong.
thanx!
kiran
temp tables and dbms_stats.set_table_stats
December 29, 2004 - 2pm Central time zone
Reviewer: denni50 from na
Hi Tom
Happy 2005!
Came upon a question on this thread dbms_stats.set_table_stats) where you answered:
"Another use I've found for it is with temporary tables. You cannot really analyze them but what
you can do is create a "real" table -- fill it up with representative data -- and then export these
stats using dbms_stats. Later, you create the temporary table and import these other stats you
saved. Now the optimizer will use these stats for the temporary table when developing query plans
that involve it. This could be important if you put lots of rows into a temp table and then use it
in subsquent SQL statments -- the optimizer would otherwise think the temporary table is "small" "
it brought to mind a recent occurrence where we were in the testing stages of upgrading our canned
software to a later version and discovered one of the functionalties had been changed considerably
and used a temp table to store
updated data(which can involve 700,000+ rows) that is stored in a separate tablespace. When we
tested the new module, that generated a summary report, it was calculated that an additional 31 1/2
hours was added to the process that was entirely attributed to the loading into the new temp_table.
(below is the actual time from the report showing the loading phase into the temp_table)
time calculation:
total seconds: 113042.75/60 = 1884.0458 minutes
1884.0458/60 = 31.40 hours
The vendor never provided an answer or solution as to why the process was taking so long and we had
to scrap the upgrade. When I read your answer about temp tables and stats it rang a bell as our
temp tables were not analyzed as you described.
Could you please expand on a "real" table vs temp table.
Does it need the exact same structure as the temp table?
Not sure how I would fill it with representative data since the functionality does the loading
through the job process.
Next I would export the "real" table with the stats...create the temp_table..then import the data
and stats from the "real" table...is that correct?
I believe this is the reason the process took so long as sql statements were also being run against
the temp table in addition to the loading.
thanks
Followup December 29, 2004 - 7pm Central time zone:
the process is:
create a REAL table that is identical to your temporary table in name, structure, etc.
fill this REAL table with representative data, what you expect normally.
gather and export the stats for this REAL table.
drop it, create the GTT in its place, import the stats.
(or just use dbms_stats.set_ to set the statistics)
thanks Tom!!
December 30, 2004 - 8am Central time zone
Reviewer: denni50 from na
that is soooo cool....can't wait to test it out.
:~)
btw:
went to NYC for the x-mas(Amtrak train)and had the good
fortune to sit next to(of all things) an Oracle DBA
from Herndon VA. We virtually spent the entire travel
talking about you, your website and your books...it
was very entertaining and engaging to say the least.
(don't worry...we showered you with accolades!...chuckle)
Wrong Sample Size for Indexes with DBMS_STATS
December 30, 2004 - 9am Central time zone
Reviewer: Kiran Ghanta from Richmond, VA USA
Hi Tom,
I was hoping you could advise me on the problem described above. The distinct keys, data blocks per
key numbers vary significantly for nonunique indexes between DBMS_STATS and ANALYZE. Do you think
it would be a good idea, if I use DBMS_STATS for TABLE STATS and ANALYZE for INDEX STATS? I need to
gather stats for the partitioned tables as well.
Pl. advise,
thank you!
kiran
Followup December 30, 2004 - 11am Central time zone:
can you summarize the differences you feel are "significant"
the num of rows example you posted -- they are different by small %'s at most?
Wrong Sample Size for Indexes with DBMS_STATS
December 30, 2004 - 2pm Central time zone
Reviewer: Kiran Ghanta from Richmond, VA USA
Thank you Tom for your response. Here is the comparison between DBMS_STATS and ANALYZE for one of
my nonunique indexes.
Estimate BtreeLvl LeafBlks DstnctKeys NumOfRows AvgLfBlkPerKey AvgDatBlkPerKey ClustFact
SampleSize SamplePct
----------------------------------------------------------------------------------------------------
------------------------------
DBMS_STATS
----------
10% 3 424,859 62 92,619,241 6,852 34,375 2,131,304
237,838 0.256791135
20% 3 438,878 53 95,675,422 8,280 41,807 2,215,808
245,686 0.256791133
40% 3 445,498 59 97,118,618 7,550 38,790 2,288,630
249,392 0.256791133
100% 3 421,744 53 91,940,090 7,957 40,837 2,164,404
236,094 0.256791134
ANALYZE
-------
10% 3 445,530 397 97,124,067 1,122 5,637 2,238,114
10,747,673 11.06592149
20% 3 445,530 586 97,124,866 760 3,758 2,202,672
21,476,121 22.1118668
40% 3 445,530 852 97,125,330 522 2,619 2,231,520
49,907,940 51.38509182
Compute 3 445,308 984 97,077,102 452 1,850 1,820,419
97,077,102 100
thanks,
kiran
Followup December 30, 2004 - 2pm Central time zone:
the fundemental difference is the computation of distinct keys (it would be easier to look at this
stuff side by side
dbms_stats analyze pct_diff
distinct keys 12341342 432432432 123
it would not wrap and it would show concisely the differences.... I ended up having to do that --
it points only back to distinct keys as the others are derived from that)
leaf blocks, btreelvl, cluster factor, num rows, no problem.
I would file a tar with support to see why it is not bumping up the sample size however (but unless
you have identified a problem with the use of the existing stats, no i wouldn't flip over to
analyze -- the important numbers seem to be there)
Wrong Sample Size for Indexes with DBMS_STATS
December 31, 2004 - 9am Central time zone
Reviewer: Kiran Ghanta from Richmond, VA USA
Thanks a lot Tom. Wish you a very happy new year!
dbms_stats.gather_schema_stats
January 14, 2005 - 3pm Central time zone
Reviewer: A reader
Tom,
I have a few questions about dbms_stats package
1. If I use dbms_stats.gather_schema_stats with "stattab =>", such as execute
dbms_stats.gather_schema_stats('prod', statstab => 'mytab', statown =>'dev'), does it mean the
schema is analyzed even though the statistics is stored in dev.mytab instead of data dictionary?
Will the statistics be used by the optimizer?
2. I used above command
execute dbms_stats.gather_schema_stats('prod', statstab => 'mytab', statown =>'dev')
to gather the statisitics, but when I first run the package, the dev.mytab has nothing there though
the last_anlayzed is not null in the all_tables for the owner of 'prod'. If I run twice execute
dbms_stats.gather_schema_stats('prod', statstab => 'mytab', statown =>'dev'), then the data is
populated in the dev.mytab table. Is there something wrong with my code or there is a bug in
dbms_stats.gather_schema_stats?
Thank you,
Daxu
Followup January 14, 2005 - 8pm Central time zone:
it specifies where to SAVE existing stats PRIOR to gathering new stats, consider:
ops$tkyte@ORA9IR2> create table t as select * from all_objects;
Table created.
ops$tkyte@ORA9IR2> set autotrace traceonly explain;
ops$tkyte@ORA9IR2> select count(*) from t;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T'
using rbo, no card=
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> exec dbms_stats.CREATE_STAT_TABLE( user, 'STATS' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.gather_schema_stats( user, stattab=>'STATS' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select count(*) from stats;
COUNT(*)
----------
0
nothing in the stats table yet..
ops$tkyte@ORA9IR2> set autotrace traceonly explain;
ops$tkyte@ORA9IR2> select count(*) from t;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=62 Card=27784)
but the analyze kicked in the CBO, we have stats
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> truncate table t;
Table truncated.
ops$tkyte@ORA9IR2> exec dbms_stats.gather_schema_stats( user, stattab=>'STATS' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> set autotrace traceonly explain;
ops$tkyte@ORA9IR2> select count(*) from t;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1)
new stats....
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select count(*) from stats;
COUNT(*)
----------
40
ops$tkyte@ORA9IR2>
old ones saved for us...
dbms_stats
January 15, 2005 - 1am Central time zone
Reviewer: A reader
Tom.
Thank you very much for the detailed explanation, now I am clear about this.
Daxu
running dbms_stats twice a day?
January 15, 2005 - 9pm Central time zone
Reviewer: Eugene
Hi Tom,
Would it be beneficial to run statistics gathering twice a day? Will I see noticeable benefits OR
will it just be a waist of recources?
Thanks,
Eugene
Followup January 15, 2005 - 9pm Central time zone:
do you update about 1% of your data per day
or 100% of your data
or more or less or...................
sort of depends, would the statistics gathered about your data be significantly different if you
gathered
a) every minute
b) every hour
c) every N hours (n < 24)
d) every day
e) every Unit of time (unit of time > a day)
that is what you need to think about.
running dbms_stats twice a day?
January 16, 2005 - 2pm Central time zone
Reviewer: Eugene
Tom,
Thanks for reply. Is there a "rule" you go by to determine when you need to run stats. For instance
10% of records are updated and stats need to be run OR since only 3% of rows were updated and there
is no benefit from running it again.
I've heard DBAs writing their own procedures to determine that value (% of changed rows) to run or
not to run DBMS_STATS.
Wonder what you'd say...
Thanks,
Eugene
Followup January 16, 2005 - 3pm Central time zone:
why would they write their own procedure when this is precisely what "alter table T monitoring" and
gather stale do?
Many times, a % change (which is what the database already has code to do) is sufficient -- but
there are cases that can be problematic (especially in a system where binds are not used -- data
warehouses for example). As you add data, the "upper/lower bound" of some columns might change
drastically. So that yesterday:
select .... where x < 5;
was correctly estimated to return "N" rows -- but after an incremental load of a small percentage
of the overall data -- now returns 50000*N rows. They may need special consideration (but then
again, scenario was a DW, you know when the data is going to change drastically...)
running dbms_stats twice a day?
January 16, 2005 - 9pm Central time zone
Reviewer: Eugene
Thanks Tom,
I will look into "monitoring" and "gather stale" some more.
Eugene
best sample size
January 20, 2005 - 9am Central time zone
Reviewer: sreenivasa rao from india
HI,tom
The production DB on version 8.1.5.
SQL> DESC STI.TBL_FAXSTATUS
Name Null? Type
----------------------------------------- -------- ----------------
DN_JOBID NOT NULL NUMBER(10)
DC_CUSTID VARCHAR2(20)
DC_STATUS VARCHAR2(1)
DC_FAXNO VARCHAR2(12)
DC_FAXREQTYPE NOT NULL VARCHAR2(5)
DD_SYSDATE DATE
DC_DOMAIN VARCHAR2(2)
SQL> SELECT * FROM STI.TBL_FAXSTATUS;
DN_JOBID DC_CUSTID D DC_FAXNO DC_FA DD_SYSDAT DC
---------- -------------------- - ------------ ----- --------- --
7740 4129038080008053 1 923414206 9111 07-JAN-05 CC
7742 4129038083254092 0 92582349 9111 14-JAN-05 CC
7741 4129034057113059 1 951634514 9111 12-JAN-05 CC
7744 5543757680173810 1 926143499 9111 19-JAN-05 CC
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'STI',ESTIMATE_PERCENT =>5,TABNAME =>
'TBL_FAXSTATUS',METHOD_OPT => '
FOR COLUMNS DN_JOBID SIZE 4,DC_CUSTID SIZE 4,DC_STATUS SIZE 2,DC_FAXNO SIZE 4,DC_FAXREQTYPE
SIZE 1,DD_SYSDATE SIZE 4,DC_
DOMAIN SIZE 1',cascade =>TRUE);
PL/SQL procedure successfully completed.
Here am i correct in gathering statistics for the above table?
Pls guide me how to set estimate_percent and method_opt.
i set the sample size 5 as oracle recommonds it.
questions:
1.How do you set the above values(estimate_percent,method_opt) in my 8.1.5 database.
2.If thousands of distinct values for a column in a million row table,how can we set the method_opt
for that column,as it ranges only for (0-254).
thanks in advance for your great suggestions.
sreenivas
parameter: method_opt
March 18, 2005 - 11pm Central time zone
Reviewer: A reader
Hi Tom,
Is default method_opt good enough? How can I tune "size ?" in
this parameter?
thanks,
Error with dbms_stats
May 1, 2005 - 6am Central time zone
Reviewer: A reader
Hi Tom,
When I analyze a partition (about 100Million rows) with compute statistics, it works. But with
dbms_stats, it comes out with error:
BEGIN
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P004
ORA-04030: out of process memory when trying to allocate 16384 bytes (sort
subheap,sort key)
ORA-06512: at "SYS.DBMS_STATS", line 9136
ORA-06512: at "SYS.DBMS_STATS", line 9150
ORA-06512: at line 3
____________________
I am confused, why it is using parallel query? I did not put any parallel clause in table
definition, and even parallel parameters settings are (please see parallel_automatic_tuning
boolean FALSE):
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 2152
parallel_instance_group string
parallel_max_servers integer 6
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 2
__________________
The command used is:
DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME => 'TEST',
TABNAME => 'TEST',
PARTNAME => 'TEST_PART8',
METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 1',
CASCADE => TRUE); -- ???
______________
Any help will be useful.
Thanks & Regards
Followup May 1, 2005 - 8am Central time zone:
dbms_stats defaults parallelism differently in different releases and you don't say what you are
using so.....
suggest you check out the supplied packages guide for your release to see how it is defaulting
and in any case, if you don't want it, you can always say so.
You do have parallel query enabled.
You might have a non-default degree of parallelism on the table.
about the setting of method_opt parameter
May 9, 2005 - 9pm Central time zone
Reviewer: Peiyan yu from CHINA
Tom:
I see the following in this paper:
it is that if you gather histograms in 8i and want parallel, you would use size
1. setting method-opt to null bypasses histograms entirely.
It's what i want. In normal cituation, I wont collect the histogram statistics, so I prepare to set
the method_opt to null, but I dont get the expected result .
create table t_histogram(id number primary key , name varchar2(10) , depno number) monitoring ;
insert into t_histogram select object_id , substr(object_name,1,10) , mod(rownum,3)
from user_objects where rownum < 21 ;
commit;
exec dbms_stats.gather_table_stats('DCE','T_HISTOGRAM',method_opt => null, cascade=>true );
column column_name format a10
column table_name format a20
SELECT * FROM USER_HISTOGRAMS WHERE TABLE_NAME='T_HISTOGRAM' ;
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
-------------------- ---------- --------------- --------------
--------------------------------------------------------------------------------
T_HISTOGRAM ID 0 49461
T_HISTOGRAM NAME 0 3.649482310901
T_HISTOGRAM DEPNO 0 0
T_HISTOGRAM ID 1 49481
T_HISTOGRAM NAME 1 3.651914664850
T_HISTOGRAM DEPNO 1 2
From oracle's doc , I dont find the method to bypass the histograms, do you give me some
suggestions.
Thank you
Peiyan Yu
Followup May 10, 2005 - 7am Central time zone:
method_opt => 'for columns'
in 9i, 10g will not generate histograms.
about the setting of metho_opt
May 10, 2005 - 10am Central time zone
Reviewer: Peiyan Yu from CHINA
Thank you very much.
I collected the statistics by what you told me. Indeed, when setting the 'for columns', the
dbms_stats did not collect the histogram, but it also did not collect the column statistics, but
what I want to do is bypassing the histogram. Because using default setting, I think the histogram
statistics is not exact.
Best reguards
Peiyan Yu
Followup May 10, 2005 - 10am Central time zone:
which do you want -- histograms of size 1 (column stats) or nothing?
about the setting of method_opt
May 10, 2005 - 9pm Central time zone
Reviewer: Peiyan yu from CHINA
Thank you again.
What I mean is if I only collect column statistics,not including the histogram.
When setting the ' for all columns size 1' , by querying the user_tab_histograms, I can get
histogram statistics, that's not what I want. But if I collect the columns statistics , It's
necessary to get some histogram statistics, even if the size is one .
Best Reguards
Peiyan Yu
Followup May 10, 2005 - 11pm Central time zone:
confused, example of what you
a) see
b) want to see
would help.
about the setting of method_opt
May 11, 2005 - 9am Central time zone
Reviewer: peiyan yu from CHINA
For example:
SQL> desc t_histogram
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(10)
DEPNO NUMBER
SQL> insert into t_histogram select object_id , substr(object_name,1,10) , mod(object_id,3)
2 from all_objects where rownum<21 ;
20 rows created.
SQL> commit ;
Commit complete.
SQL> analyze table t_histogram delete statistics ;
Table analyzed.
Now collecting statistics, set the method_opt to 'for all columns size 1':
SQL> exec dbms_stats.gather_table_stats('SIRIUS','T_HISTOGRAM',METHOD_OPT=>'for all columns size
1') ;
PL/SQL procedure successfully completed.
I queryed the statistics result.
This is the first part.
SQL> select table_name,column_name,density,last_analyzed
2 from user_tab_columns where table_name='T_HISTOGRAM';
TABLE_NAME COLUMN_NAM DENSITY LAST_ANALYZE
--------------- ---------- ---------- ------------
T_HISTOGRAM ID .05 11-MAY-05
T_HISTOGRAM NAME .083333333 11-MAY-05
T_HISTOGRAM DEPNO .333333333 11-MAY-05
This is the second part.
SQL> select count(1) from user_tab_histograms
2 where table_name ='T_HISTOGRAM' ;
COUNT(1)
----------
6
The first part is what I want, but the second is not what i want. Now , how to set the method_opt
parameter ?
If setting the para to 'for columns' , then no statistics about columns. that is not what i want.
thanks
peiyan yu
Followup May 11, 2005 - 12pm Central time zone:
you don't get one without the other -- why do you think you want one without the other?
about the setting of metho_opt
May 11, 2005 - 9pm Central time zone
Reviewer: peiyan yu from CHINA
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/stats.htm#27065 <quote>
Histograms are not useful for columns with the following characteristics:
All predicates on the column use bind variables.
The column data is uniformly distributed.
The column is unique and is used only with equality predicates.
</quote>
and if setting the method_opt to 'for all columns size 1', in this cituation, this histogram
statistics is helpful to cbo optimizer?
according those, I think perhaps there should be option to let us to bypass the collect statistics.
of course , from the oracle doc , I cant find the setting, so ask you for help .
thank for your reply
peiyan yu
Followup May 12, 2005 - 7am Central time zone:
and then you don't get the other statistics either. You either
a) want them
b) don't want them
dbms_stats.create_stat_table which table?
August 15, 2005 - 3pm Central time zone
Reviewer: Gabriel from Montreal, Canada
Hello Tom,
Is it possible to find out which table is used by dbms_stats package to store the data?
dbms_stats.create_stat_table lets the user create its own table but how would somebody else figure
out which table it is?
Thank you
Followup August 15, 2005 - 10pm Central time zone:
They would ask them?
dbms_stats.gather_* routines populate the dictionary.
You can then export/import them to a named table. You have to let others know what the name of
this table is if you care to share them.
info not stored anywhere
August 17, 2005 - 2pm Central time zone
Reviewer: Gab from Beautiful Montreal, Canada
Hello Tom,
If the person that created the stats table is on vacation and hasn't shared that info before
leaving (we've all been there) is there any other way to find out which table is that?
I find it hard to believe that the information of which table is populated by the dbms_stats
package is not stored somewhere in the dictionary. How does dbms_stats go by finding out which
table to store the info every time it runs?
Thank you,
Followup August 17, 2005 - 5pm Central time zone:
dbms_stats DOES NOT populate that table -- dbms_stats.gather_table_stats populates the dictionary.
you can decide to EXPORT (copy from the dictionary) statistics to a table, that is what this table
is.
If the guy that COPIED them into a table is on vacation, so what, you don't "need" them.
I think you are confused about how/where stats go -- when you GATHER - they go into the dictionary
(you know what tables those are)
when you EXPORT (copy) them, they go into any table name you desire, you could have hundreds of
tables they are copied into. But only the person that copied them needs to know where they were
copied to (they are a COPY)
what is this table then?
August 18, 2005 - 3pm Central time zone
Reviewer: Gab from Montreal, Canada
Hello Tom,
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent NUMBER DEFAULT NULL,
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
degree NUMBER DEFAULT NULL,
granularity VARCHAR2 DEFAULT 'DEFAULT',
cascade BOOLEAN DEFAULT FALSE,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
gather_sys BOOLEAN DEFAULT FALSE,
no_invalidate BOOLEAN DEFAULT FALSE,
gather_temp BOOLEAN DEFAULT FALSE);
stattab
User stat table identifier describing where to save the current statistics.
The statistics table is assumed to reside in the same schema as the object being analyzed, so there
must be one such table in each schema to use this option.
Can you please tell me then what is the purpose of this stattab table and how can I find which one
was used?
Thank you again,
Followup August 18, 2005 - 4pm Central time zone:
that saves the current stats before they are replaced.
the new stats go into the dictionary.
we neither care what this name is, nor do we track it. It is not relevant to our operation, every
time you call dbms_stats, it could have a *DIFFERENT NAME*. If you are using this feature, one
would certainly hope you would have a name well known to all in place (eg: dba's just don't log
into the database and type in the gather command in sqlplus do they -- you have a job, certified by
your team, agreed upon, in some sort of configuration management -- and you can just read that to
see how you might name this table in your system)
ops$tkyte@ORA9IR2> exec dbms_stats.create_stat_table( user, 'OUR_FINE_NAME' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t as select * from all_objects where 1=0;
Table created.
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', stattab => 'OUR_FINE_NAME' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select count(*) from our_fine_name;
COUNT(*)
----------
0
ops$tkyte@ORA9IR2> select num_rows from user_tables where table_name = 'T';
NUM_ROWS
----------
0
ops$tkyte@ORA9IR2> insert /*+ APPEND */ into t select * from all_objects;
28001 rows created.
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', stattab => 'OUR_FINE_NAME' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select count(*) from our_fine_name;
COUNT(*)
----------
14
ops$tkyte@ORA9IR2> select num_rows from user_tables where table_name = 'T';
NUM_ROWS
----------
28001
after the gather, the current stats - in dictionary, the old stats - in our_fine_name, but we
neither care, nor are we dependent on "our_fine_name", it is just data -- data you are managing.
If this is a relevant table to you -- you would know it's name already
1 select owner, table_name
2 from all_tab_columns
3 where column_name in ( 'STATID', 'TYPE', 'VERSION', 'FLAGS' )
4 group by owner, table_name
5* having count(*) = 4
ops$tkyte@ORA9IR2> /
OWNER TABLE_NAME
------------------------------ ------------------------------
A SYSTEM_STATS
Z001698 ST
OPS$TKYTE OUR_FINE_NAME
would be a way to see "candidate" tables -- tables that *might* have some stats saved into them.
Statistics by session on temporary tables
September 15, 2005 - 1pm Central time zone
Reviewer: Grant in Calgary from Calgary, Alberta Canada
Hi Tom,
You had talked about creating statistics on temporary tables above. Is there a way to import or
alter those statistics at a session level. The reason I ask is that I have a procedure that is
frequently called from multiple sessions concurrently that use a temporary table. The problem is
that one session may populate the table with 50,000 records and work with it while at the same time
another session has 20,000,000 records. I want to find a way to import the statistics by session
for these temporary tables.
Followup September 15, 2005 - 2pm Central time zone:
how many times is this query executed per day.
How do you fake stats for a function cast as a table ?
September 16, 2005 - 5pm Central time zone
Reviewer: Rahul from NJ
Tom,
Learnt more than a few things from your site and your book. Keep it up!
A question regarding stats and function tables. We use queries of the following form :
SELECT fnc.col2,
tab.col1,
tabl.col2
from Table(cast(my_func(4,'1,AA,2,BB,3,CC,4,DD') as myTableType)) fnc,
real_table tab
where fnc.id = tab.id
Here my_func could be either a pipelined or a regular function.
Question is, how does the optimizer estimate the number of rows that fnc will return ?
If we know the approx number of rows that fnc will return, is there a way to supply these stats to
the optimizer (via DBMS_STATS or hints) ?
Works like a charm !
September 16, 2005 - 6pm Central time zone
Reviewer: Rahul from NJ
Hard to believe, but I had never heard of asktom till I bought Expert one on one.
Since then I have been regularly searching here for solutions to my Oracle problems. I can't
remember a time when I did not find a solution already analyzed in depth.
Guess I should have just looked harder for this one :).
Thanks a lot!
Statistics by session on temporary tables
September 21, 2005 - 4pm Central time zone
Reviewer: Grant from Calgary, AB
Actually Tom, I continued searching your site after my questions became aware of dynamic sampling
which seems to be the answer to my problem in regards to changing in and out statistics for
temporary tables.
Thanks anyways Tom.
GATHER_SCHEMA_STATS
October 11, 2005 - 1am Central time zone
Reviewer: Deepak from India
Hi Tom,
We use the DBMS_STATS.GATHER_TABLE_STATS & DBMS_STATS.GATHER_INDEX_STATS to collect the index and
table statistics for ALL of our schema tables & indexes.
Would like to know if we use DBMS_STATS.GATHER_SCHEMA_STATS instead of the prior approach, then
will their be any benefits and vice-versa?
Followup October 11, 2005 - 6am Central time zone:
do you use a consistent set of options for the gather commands or do they vary by table/index?
GATHER_CHEMA_STATS
October 11, 2005 - 10am Central time zone
Reviewer: Deepak from India
Hi Tom,
We use a consistent set of configuration for all tables/indexes.
Followup October 11, 2005 - 3pm Central time zone:
then, verify you get the same stats doing what you believe to be equivalent commands in a TEST
database (to verify the parameters you send to gather schema stats do the same thing as gather
table stats does now) and feel free to simplify your process after you validate it.
ORA-03113: end-of-file on communication channel
October 17, 2005 - 1pm Central time zone
Reviewer: Branka from VA USA
When I run
exec dbms_stats.gather_table_stats( ownname=>'BB', tabname =>'B_TAB', cascade=>true ,method_opt =>
'FOR ALL COLUMNS SIZE AUTO')
I get ERROR at line 1:
ORA-03113: end-of-file on communication channel
Any other table get analyzed without problem.
When I analyze with
analyze table BB.B_TAB compute statistics for table for all indexes for all indexed columns
I don't have any problem.
Do you have any idea what can be problem?
Thanks
Followup October 17, 2005 - 2pm Central time zone:
3113, 7445, 600 -> all imply "please contact support, something unexpected has happened and we had
to "stop""
DBMS_STATS - lots of unique SQL :(
October 24, 2005 - 4pm Central time zone
Reviewer: Philip Moore from Cincinnati, OH USA
<code>Hi Tom,
I often use DBMS_STATS - but I find it to generate a LOT of SQL when used on partitioned tables with locally partitioned indexes.
The use of DBMS_STATS (at least in 9i Release 2) seems to flood the shared pool with literally hundreds of unique SQL statements. This is due to the fact that DBMS_STATS issues SQL with "PARTITION()" clauses - with literal values for the partition name.
Imagine a star schema where we have a fact table with 12 partitions. We then create a bitmap index (Locally partitioned) on that table for just a single dimension.
When we run DBMS_STATS on the table, we get 26 unique SQL statements: 1 for the table "global" gather, 1 for the index "global" gather, 12 for the table partition gathers, and 12 for the index partition gathers. (3 more are shown in my demo below (1 each for the proc call, table build, and index build)).
This seems to directly refute what you have been trying to instill in us as developers for quite some time - use Bind Variables - and reuse stored SQL! Shouldn't Oracle get rid of the "PARTITION()" clauses - and instead place a predicate such as:
"sale_date BETWEEN :partition_low_value AND :partition_high_value"
Why not rely on Oracle's robust partition pruning capability instead of naming the partition in SQL - causing yet another unique SQL statement to get parsed, stored, etc (especially one that will NOT get executed again for quite some time)?
That would reduce our 26 statements to 4, a drastic difference - saving 22 slots for parsed SQL.
I give an example of this below. This test was done in Oracle 9i Release 2 (9.2.0.6)
phil@ORA9iR2 -> @c:\asktom
phil@ORA9iR2 -> /*----------------------------------------------------------------*/
phil@ORA9iR2 -> PROMPT Let's create our table
Let's create our table
phil@ORA9iR2 ->
phil@ORA9iR2 -> DROP TABLE asktom_test
2 /
DROP TABLE asktom_test
*
ERROR at line 1:
ORA-00942: table or view does not exist
phil@ORA9iR2 ->
phil@ORA9iR2 -> CREATE TABLE asktom_test
2 ( product_id INT
3 , sale_date DATE
4 , sale_amount NUMBER
5 )
6 PARTITION BY RANGE (sale_date)
7 ( PARTITION p_jan_2005 VALUES LESS THAN (TO_DATE('02/01/2005', 'MM/DD/YYYY'))
8 , PARTITION p_feb_2005 VALUES LESS THAN (TO_DATE('03/01/2005', 'MM/DD/YYYY'))
9 , PARTITION p_mar_2005 VALUES LESS THAN (TO_DATE('04/01/2005', 'MM/DD/YYYY'))
10 , PARTITION p_apr_2005 VALUES LESS THAN (TO_DATE('05/01/2005', 'MM/DD/YYYY'))
11 , PARTITION p_may_2005 VALUES LESS THAN (TO_DATE('06/01/2005', 'MM/DD/YYYY'))
12 , PARTITION p_jun_2005 VALUES LESS THAN (TO_DATE('07/01/2005', 'MM/DD/YYYY'))
13 , PARTITION p_jul_2005 VALUES LESS THAN (TO_DATE('08/01/2005', 'MM/DD/YYYY'))
14 , PARTITION p_aug_2005 VALUES LESS THAN (TO_DATE('09/01/2005', 'MM/DD/YYYY'))
15 , PARTITION p_sep_2005 VALUES LESS THAN (TO_DATE('10/01/2005', 'MM/DD/YYYY'))
16 , PARTITION p_oct_2005 VALUES LESS THAN (TO_DATE('11/01/2005', 'MM/DD/YYYY'))
17 , PARTITION p_nov_2005 VALUES LESS THAN (TO_DATE('12/01/2005', 'MM/DD/YYYY'))
18 , PARTITION p_dec_2005 VALUES LESS THAN (TO_DATE('01/01/2006', 'MM/DD/YYYY'))
19 )
20 /
Table created.
phil@ORA9iR2 ->
phil@ORA9iR2 -> /*----------------------------------------------------------------*/
phil@ORA9iR2 -> CREATE BITMAP INDEX asktom_test_product_id_bidx ON asktom_test(product_id)
2 LOCAL
3 /
Index created.
phil@ORA9iR2 ->
phil@ORA9iR2 -> /*----------------------------------------------------------------*/
phil@ORA9iR2 -> PROMPT Now let's insert some data...
Now let's insert some data...
phil@ORA9iR2 ->
phil@ORA9iR2 -> INSERT /*+ append */
2 INTO asktom_test
3 SELECT MOD(LEVEL, 100) AS product_id
4 , TO_DATE('01/01/2005', 'MM/DD/YYYY') + MOD(ROWNUM, 365) AS sale_date
5 , dbms_random.RANDOM AS sale_amount
6 FROM dual
7 CONNECT BY LEVEL <= 120000
8 /
120000 rows created.
phil@ORA9iR2 ->
phil@ORA9iR2 -> COMMIT ;
Commit complete.
phil@ORA9iR2 ->
phil@ORA9iR2 -> /*----------------------------------------------------------------*/
phil@ORA9iR2 -> PROMPT Now let's gather stats (and put a lot of SQL in the shared pool :))
Now let's gather stats (and put a lot of SQL in the shared pool :))
phil@ORA9iR2 ->
phil@ORA9iR2 -> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS (ownname => USER
3 , tabname => 'ASKTOM_TEST'
4 , partname => NULL
5 , estimate_percent => DBMS_STATS.auto_sample_size
6 , block_sample => TRUE
7 , method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'
8 , DEGREE => NULL
9 , granularity => 'ALL'
10 , CASCADE => TRUE
11 , stattab => NULL
12 , statid => NULL
13 , statown => NULL
14 , no_invalidate => NULL
15 );
16 END;
17 /
PL/SQL procedure successfully completed.
phil@ORA9iR2 ->
phil@ORA9iR2 -> /*----------------------------------------------------------------*/
phil@ORA9iR2 -> PROMPT Now let's see how many statements were generated for this gather stats proc call...
Now let's see how many statements were generated for this gather stats proc call...
phil@ORA9iR2 ->
phil@ORA9iR2 -> SELECT sql_text
2 FROM v$sqlarea
3 WHERE UPPER(sql_text) LIKE '%ASKTOM_TEST%'
4 /
SQL_TEXT
----------------------------------------------------------------------------------------------------
SELECT sql_text FROM v$sqlarea WHERE UPPER(sql_text) LIKE '%ASKTOM_TEST%'
analyze index "PHIL"."ASKTOM_TEST_PRODUCT_ID_BIDX" ESTIMATE statistics sample 5 percent
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring*/ count(*) from "PHIL"."ASK
TOM_TEST" sample block ( 1.0000000000,1) t
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring noparallel(t) noparallel_index(t
) */ count(*) from "PHIL"."ASKTOM_TEST" partition ("P_APR_2005") sample block ( 10.0000000000,1
) t
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring noparallel(t) noparallel_index(t
) */ count(*) from "PHIL"."ASKTOM_TEST" partition ("P_AUG_2005") sample block ( 10.0000000000,1
) t
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring noparallel(t) noparallel_index(t
) */ count(*) from "PHIL"."ASKTOM_TEST" partition ("P_DEC_2005") sample block ( 10.0000000000,1
) t
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring noparallel(t) noparallel_index(t
) */ count(*) from "PHIL"."ASKTOM_TEST" partition ("P_JAN_2005") sample block ( 10.0000000000,1
) t
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring noparallel(t) noparallel_index(t
) */ count(*) from "PHIL"."ASKTOM_TEST" partition ("P_JUL_2005") sample block ( 10.0000000000,1
) t
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring noparallel(t) noparallel_index(t
) */ count(*) from "PHIL"."ASKTOM_TEST" partition ("P_JUN_2005") sample block ( 10.0000000000,1
) t
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring noparallel(t) noparallel_index(t
) */ count(*) from "PHIL"."ASKTOM_TEST" partition ("P_MAR_2005") sample block ( 10.0000000000,1
) t
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring noparallel(t) noparallel_index(t
) */ count(*) from "PHIL"."ASKTOM_TEST" partition ("P_MAY_2005") sample block ( 10.0000000000,1
) t
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring noparallel(t) noparallel_index(t
) */ count(*) from "PHIL"."ASKTOM_TEST" partition ("P_NOV_2005") sample block ( 10.0000000000,1
) t
select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring noparallel(t) noparallel_index(t
) */ count(*) from "PHIL"."ASKTOM_TEST" partition ("P_OCT_2005") sample block ( 10.0000000000,1
) t
select /*+ cursor_sharing
Followup October 25, 2005 - 1am Central time zone:
you call 26 "flooding the shared pool"?
Please bother to read
October 25, 2005 - 11am Central time zone
Reviewer: Philip Moore from Cincinnati, OH USA
Hi Tom,
No - I don't call 26 "flooding the shared pool". I used a simple example as you require to
demonstrate that a table with just one index and 12 partitions created a lot of unique SQL.
If you had bothered to read my response in entirety - you would have noticed that it would create
many more with more indexes/dimension columns. We have cases where it generates 1,000's of
statements (by refreshing multiple partitioned-Materialized Views with bitmap indexes then
gathering stats).
I think you are getting too busy nowadays to monitor this site. Instead of offering a helpful
suggestion you answer with a question that intends to mock the questioner. Instead of reading a
question in entirety - you now seem to jump on the first "gotcha" (hmm - sounds a lot like Oracle
support). How does this help people become successful with Oracle software?
Sincerely,
Philip
Followup October 26, 2005 - 7am Central time zone:
"If you had bothered to read my response in entirety"
Ok, fine.
Yes, you are correct. I'll go onto the next thing now.
Seriously - you are correct -- when I have to hit page down over and over and over and over in a
comment/followup - you are right. I cannot read them all. I try to take *new* questions - but
spend too much time doing this part of it.
So yes, you are absolutely correct - I breeze through the reviews and followups (check out the home
page to get a count of how many in the last 4 weeks).
BLOB Statistics
October 26, 2005 - 5am Central time zone
Reviewer: Deepak from India
Hi Tom,
Want to know when I collect statistics of a table using DBMS_STATS package, is there any statistics
related to BLOB column are gathered?
Let's suppose
> I gathered the statistics of a table T1 using DBMS_STATS.
> Added a Blob column to the table T1.
Need I collect statistics for the table T1 again? If I do not, then will it affect the performance?
Please help.
Followup October 26, 2005 - 12pm Central time zone:
the blob data is generally stored out of line (not in the table)
and if you added a blob column, just added it, nothing in the table changed (they are all null -
they are the last column, the consume zero bytes of storage). so nothing would have changed.
Thanks Tom
October 27, 2005 - 1am Central time zone
Reviewer: Deepak from India
Thanks Tom.
Capturing schema statistics for all users except SYS and SYSTEM
November 16, 2005 - 3pm Central time zone
Reviewer: Vikas Katyal from INDIA
Hi Tom,
Can you please let me know what is the mistake here in the code.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_job NUMBER;
3 BEGIN
4 for x in (Select username from dba_users where username NOT IN ('SYS','SYSTEM')) loop
5 DBMS_JOB.submit(l_job,
6 'BEGIN DBMS_STATS.gather_schema_stats(' || x.username || ')''; END;',
7 trunc(SYSDATE),
8 'trunc(SYSDATE) + 1');
9 end loop;
10 COMMIT;
11 DBMS_OUTPUT.put_line('Job: ' || l_job);
12 END;
13 /
DECLARE
*
ERROR at line 1:
ORA-06550: line 1, column 138:
PLS-00103: Encountered the symbol "; END; END; " when expecting one of the following:
:= . ( % ;
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 5
Thanks
Followup November 16, 2005 - 6pm Central time zone:
the statement you generate would resemble this:
ops$tkyte@ORA10GR2> declare
2 x_username varchar2(30) := 'SCOTT';
3 begin
4 dbms_output.put_line(
5 'BEGIN DBMS_STATS.gather_schema_stats(' || x_username || ')''; END;'
6 );
7 end;
8 /
BEGIN DBMS_STATS.gather_schema_stats(SCOTT)'; END;
PL/SQL procedure successfully completed.
missing a quote and a quote in the wrong place...
In RAC env
January 15, 2006 - 12pm Central time zone
Reviewer: David
Tom, Is there a way to restrict dbms_stats.gather_table_stats to use only one instance to spawn
parallel process assuming I specify value 4 for DEGREE attribute. Thanks.
Followup January 15, 2006 - 3pm Central time zone:
you have to go out of your way to permit it to do cross instance parallel query - do you have that
even set up?
Donald K. Burleson
January 16, 2006 - 9am Central time zone
Reviewer: BH from Belgium
From further up the thread:
-------------------------------------------------------
And your statement: "but one would question WHY you wouldn't want them put into
the dictionary since they would be more current/accurate".
But then I recently read an article by Donald K. Burleson where he states:
"One of the most common mistakes made by Oracle DBAs is to frequently re-analyze
the schema. Remember, the sole purpose of doing that is to change the execution
plans for your SQL, and if it ain't broke, don't fix it. If you are satisfied
with your current SQL performance, re-analyzing
a schema could cause significant performance problems and undo the tuning
efforts of the development staff. In practice, very few shops are sufficiently
dynamic to require periodic schema re-analysis."
-------------------------------------------------------
Just commenting on Donald K. Burleson and for that matter Mike Ault. Anyone who boasts about being
one the world's leading expert blah blah blah in this, that and the other, should be taken with an
extremely large truck load of salt. And these two are no exception.
Over the years, I have found numerous mistakes in works by both of these 'experts' ranging from
simple editing mistakes upwards. I would strong dissuade anyone buying books and other materials
from this intrepid duo and instead stick with authors like Cary Millsap, Steve Feuerstein, Steve
Adams, Jonathan Lewis and of course the excellent Tom himself. Take the word of Messrs Burleson and
Ault at your peril. You may well risk yours and your companys livelihoods!!!
DBMS_STAT
March 25, 2006 - 1am Central time zone
Reviewer: Mukesh from INDIA
Dear Tom,
Actually i am new to dbms_stats , i want to know that does this utility helps in the following
situation:
Suppose TABLE M is having 100 rows, and i inser 2 more rows to that table,so in this case does this
utility will provide the complete statistics of that table, if yes than plz tell how?
and the second query is by specifying gather auto in the options clause how mouch does it
analyzes(how much modification in (%) made to table by default).
Third thing which i would like to ask is What does gather_auto do, esimate or compute?
Followup March 25, 2006 - 7am Central time zone:
if you add 2 rows to 100, It is highly unlikely you would consider gathering statistics.
dbms_stats is a package that gathers statistics used by the cost based optimizer to come up with
query plans.
auto does whatever auto wants to do. It might estimate, it might not, it might get histograms, it
might not.
Performance Issue after gathering stats on a New Partition
March 27, 2006 - 3am Central time zone
Reviewer: VLS from Bombay, India
Hi Tom,
I have a partitioned table which is around 100GB in size. The table is partition on month.
Every month when I add a partition and generate the stats for this partition after some data
is inserted into it, I see a major performance issue on the queries involving this table and
as a workaround, I gather the stats on the entire table. After gathering the stats on the entire
table, the performance comes to normalcy. But then, do I really need to gather the stats on the
entire table against a single partition ? Because gathering the stats on entire table every month
takes lots of time and huge resources as well. The DBA's say that it has to do with the Global
Stats of the table when a partition is analyzed.
Would request you to suggest me a workaround by way of which I can eliminate gathering of
statistics of the entire table every month.
Regards
VLS
Followup March 27, 2006 - 9am Central time zone:
if you KNOW the statistics, you can just tell us.
Eg: if you gather stats just on the new partition you know things like how many rows you added, how
many blocks it added and so on. You could also determine the new high/low values for certain
columns (like timestamps/dates/sequence populated columns) and set them for the table using
dbms_stats.set_**** functions.
The optimizer doesn't care WHERE the stats come from, how they get into the dictionary - only that
they actually exist.
If Oracle cannot determine precisely what SINGLE partition will be accessed at hard parse time,
Oracle will use global table stats - it is likely your queries do not access a single partition (or
that partition cannot be determined at hard parse time) and hence use global table stats - not
local partition stats.
But you can SET them instead of GATHERING them.
Performance Issue after adding new partition
March 27, 2006 - 10pm Central time zone
Reviewer: VLS from Bombay, India
Hi Tom,
As a workaround, I started generating the stats of the newly added partition after some data has
been inserted into the new partition, using following command.
exec dbms_stats(ownname=>'VLS',tabname=>'PART_EXAMPLE',part_name=>'P4',cascade=>TRUE);
When I execute this command, it generates the stats of the new partition plus it also updates the
global stats of the table.
As per your suggestion, I would follow this policy once I add a new partition :
1. Will add the new partition during less load on the database which is during night hours in my
case when only batch processes are run.
2. Flush the shared pool so that the queries on the table on which I added a new partition are hard
parsed.
3. Once some data, which might impact the performance, is inserted into the table, generate the
stats using the procedure used by me (as mentioned above).
Will it be OK ? The procedure mentioned above should not affect the performance. Am i Right ?
Would appreciate if you can validate this.
Regards
VLS
Followup March 28, 2006 - 7am Central time zone:
2) is not necessary, we'll already do that. In fact, flushing the shared pool would be bad since
only the affect SQL's need to go.
3) you might just use dbms_stats.set_* API's after adding the partition to give it some
representative statistics to start with. that is, add partition WITH some reasonable statistics
associated with it already.
SQL Reference Guide documentation bug (for EXCHANGE PARTITION)???
March 28, 2006 - 12pm Central time zone
Reviewer: Phil from Cincinnati, OH USA
Hi Tom,
After working with ALTER TABLE ... EXCHANGE PARTITION for loading datawarehouses - I think I
discovered either a documentation bug or a database bug with 9iR2 (9.2.0.6).
I had hoped to gather stats on my "little" table prior to the swap - and having those statistics
factored into the larger table's global statistics after the exchange as the documentation states
in the 9iR2 SQL Reference Guide. Here is the excerpt from page 11-82:
"All statistics of the table and partition are exchanged, including table, column, index
statistics, and histograms. The aggregate statistics of the table receiving the new
partition are recalculated."
Here is a demo showing that this does NOT occur:
SQL> @c:\asktom2
SQL> COLUMN table_name FORMAT A30;
SQL> COLUMN last_analyzed FORMAT A20;
SQL> SET linesize 80;
SQL>
SQL> SELECT *
2 FROM v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
SQL>
SQL> DROP TABLE big_table;
Table dropped.
SQL>
SQL> DROP TABLE swap_table;
Table dropped.
SQL>
SQL> CREATE TABLE big_table
2 (a INT
3 )
4 PARTITION BY RANGE (a)
5 (PARTITION p1 VALUES LESS THAN (10000)
6 );
Table created.
SQL>
SQL> -- Create our swap table
SQL> CREATE TABLE swap_table
2 AS
3 SELECT *
4 FROM big_table;
Table created.
SQL>
SQL> -- Generate some data
SQL> INSERT INTO swap_table
2 SELECT LEVEL
3 FROM DUAL
4 CONNECT BY LEVEL < 10000;
9999 rows created.
SQL>
SQL> INSERT INTO big_table
2 SELECT LEVEL
3 FROM dual
4 CONNECT BY LEVEL < 5000;
4999 rows created.
SQL>
SQL> COMMIT ;
Commit complete.
SQL>
SQL> -- Get thorough stats (global and partitioned)
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS (ownname => USER
3 , tabname => 'SWAP_TABLE'
4 , partname => NULL
5 , estimate_percent => DBMS_STATS.auto_sample_size
6 , block_sample => TRUE
7 , method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'
8 , DEGREE => NULL
9 , granularity => 'ALL'
10 , CASCADE => TRUE
11 , stattab => NULL
12 , statid => NULL
13 , statown => NULL
14 , no_invalidate => NULL
15 );
16 END;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS (ownname => USER
3 , tabname => 'BIG_TABLE'
4 , partname => NULL
5 , estimate_percent => DBMS_STATS.auto_sample_size
6 , block_sample => TRUE
7 , method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'
8 , DEGREE => NULL
9 , granularity => 'ALL'
10 , CASCADE => TRUE
11 , stattab => NULL
12 , statid => NULL
13 , statown => NULL
14 , no_invalidate => NULL
15 );
16 END;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- See the stats before the swap
SQL> SELECT table_name
2 , global_stats
3 , num_rows
4 , last_analyzed
5 FROM user_tables
6 WHERE table_name IN ('BIG_TABLE', 'SWAP_TABLE');
TABLE_NAME GLO NUM_ROWS LAST_ANALYZED
------------------------------ --- ---------- --------------------
BIG_TABLE YES 4999 28-MAR-06
SWAP_TABLE YES 9999 28-MAR-06
SQL>
SQL> -- Swap
SQL> ALTER TABLE big_table
2 EXCHANGE PARTITION p1 WITH TABLE swap_table
3 INCLUDING INDEXES WITH VALIDATION;
Table altered.
SQL>
SQL> -- See the stats after the first swap
SQL> SELECT table_name
2 , global_stats
3 , num_rows
4 , last_analyzed
5 FROM user_tables
6 WHERE table_name IN ('BIG_TABLE', 'SWAP_TABLE');
TABLE_NAME GLO NUM_ROWS LAST_ANALYZED
------------------------------ --- ---------- --------------------
BIG_TABLE YES 4999 28-MAR-06
SWAP_TABLE YES 4999 28-MAR-06
SQL>
SQL> -- Swap the partition back
SQL> ALTER TABLE big_table
2 EXCHANGE PARTITION p1 WITH TABLE swap_table
3 INCLUDING INDEXES WITH VALIDATION;
Table altered.
SQL>
SQL> -- See the stats after the swap back
SQL> SELECT table_name
2 , global_stats
3 , num_rows
4 , last_analyzed
5 FROM user_tables
6 WHERE table_name IN ('BIG_TABLE', 'SWAP_TABLE');
TABLE_NAME GLO NUM_ROWS LAST_ANALYZED
------------------------------ --- ---------- --------------------
BIG_TABLE YES 4999 28-MAR-06
SWAP_TABLE YES 9999 28-MAR-06
----------------------------------------------
Is this a database bug or a documentation bug? I know I could use your workaround mentioned above
- but just wanted to call this out...
Thanks!
Sincerely,
Phil
Followup March 28, 2006 - 8pm Central time zone:
I'll look into this - have book marked this one.
Global statistics update on exchange
March 29, 2006 - 2am Central time zone
Reviewer: Michel Cadot from France
I made many tests with multipartition tables (to avoid the singularity of 1 partition), with
estimate or compute, with different method_opt values and granularity values (ALL, GLOBAL,
PARTITION to get estimate or compute global statistics), in 9.2.0.6 and 10.2.0.1, only partition
statistics are updated (exchanged), global ones still remained the same.
I think it is more a documentation bug as the purpose of exchange is mainly the speed and
recalculate global statistics may be a quite long task.
Regards
Michel
Followup March 29, 2006 - 7am Central time zone:
many of the global stats could be UPDATED - no need to recompute.
if there were 1,000,000,000 rows before the exchange and you took away 1,000,000 but added
2,000,000 with the exchange....
if there were N blocks before and you took away M and added L....
so many of them could be maintained - but not all. If you had 10 partitions and a column X has 100
distinct values globally and in the new partition column X has 100 distinct values - after the
exchange all you can say is X has between 101 and 200 distinct values
Any luck?
April 12, 2006 - 10am Central time zone
Reviewer: Phil from Cincinnati, OH USA
"I'll look into this - have book marked this one. "
Hi Tom, just wanted to check if you believe I should open a TAR for the database or the
documentation set...
(In the mean time - I've written a packaged procedure to remove the old partition's rows and blocks
from the global totals for the partitioned table and then to add the swap tables rows and blocks to
that result. I also do this for each index as well. I don't touch the "Average" statistics such
as row length, etc - nor do I touch histograms. Do you think this is ok?)
Thank you sir.
Phil
Followup April 12, 2006 - 11am Central time zone:
I think that is OK, I still have this on my todo list...
If you could file a tar that would be great really - to get it started.
ora-12842 and dbms_stats
May 18, 2006 - 3pm Central time zone
Reviewer: sudershan from usa
ORA-9206
Description
ORA-12842 possible during a Parallel Query if concurrent DDL occurs
against the objects on which the query depends.
Tom,
Have you ever seen ORA-12842 in conjunction with DBMS_STATS
running on a table.
I know DBMS_STATS does not get a lock on a table..but are there any other issues if DML is running
on a table..while stats are gathered?
Also, Could DBMS_STATS be running at the same time through
two different jobs..on the same object..will that cause any issues?
Thanks.
Followup May 19, 2006 - 11am Central time zone:
dbms_stats can use parallel query.
dbms_stats uses sql, like a program would.
[tkyte@vmware ~]$ oerr ora 12842
12842, 00000, "Cursor invalidated during parallel execution"
// *Cause: The cursor was invalidated during the parse phase of deferred
// parallel processing, e.g. when set operands are parallelized.
// *Action: Depends on why the cursor was invalidated. Possible causes include
// DDL on a schema object and shared pool being flushed.
[tkyte@vmware ~]$
What happens when then analyze is stopped in between.
May 22, 2006 - 10am Central time zone
Reviewer: Koshal from Virginia
we have a big table with 200 Million records (175 columns). The table is analyzed weekly. It takes
almost 9 hrs. Sometimes during release we can not allocate 9 hrs for analyze, we can allocate only
7 hrs . In such scenario, is it better not to analyze that week or complete whatever that is
possible. For the reasons best know to them they are using only Analyze not dbms_stats.
Thanks
Koshal
Exporting statistic from one partition to another
June 6, 2006 - 1pm Central time zone
Reviewer: Max from USA
Tom,
I have exported statistics from one partition (range) to another, but the query plan is not the
same when changing range in my predicate. I am trying to understand why.
Here is my syntax for exporting statistics:
--export statistics from an old partition (ie, 'PART_20060529')
begin
DBMS_STATS.EXPORT_TABLE_STATS (
ownname => 'ACR_ADMIN',
tabname => 'ACR_MESSAGE',
partname => 'PART_20060529',
stattab => 'ACR_MESSAGE_STAT',
cascade => TRUE);
end;
/
update ACR_MESSAGE_STAT set c2 = 'PART_20060530';
update ACR_MESSAGE_STAT set c3 = 'PART_20060530_US';
--import statistics from an old partition into the new partition (ie, 'PART_20060530')
begin
DBMS_STATS.IMPORT_TABLE_STATS (
ownname => 'ACR_ADMIN',
tabname => 'ACR_MESSAGE',
partname => 'PART_20060530',
stattab => 'ACR_MESSAGE_STAT',
cascade => TRUE);
end;
/
I have reviewed user_tab_partitions, user_tab_subparitions to make sure that the statistics were
updated.
I am trying to understand why the same query with a different predicate on date will not give me
the same plan is the statistics are the same, or are they not?
Followup June 6, 2006 - 2pm Central time zone:
give full example please.
DBMS_STAT
December 4, 2006 - 2pm Central time zone
Reviewer: AD
Hi Tom,
Some are of the opinion that we should be writing a generic procedure which will be using DBMS_STAT
to gather stats across all tables and parameters values being passed in are same in all situation.
Can you please suggest is this a right approach?
Thanks & Regards
Followup December 5, 2006 - 9pm Central time zone:
one of three things could be said:
a) it could be good
b) it could be bad
c) it could be neither good nor bad
doubt it would be a in most cases.
dbms_stats automatic options and estimate value
February 27, 2007 - 8am Central time zone
Reviewer: jean-pierre from France
Tom,
We will migrate from 8i to 10g, majority of the databases are OLTP, there are big databases (with partitioned tables), and others are in RBO.
1) Is it better to use method_opt = 'for all columns size 1' for OLTP database because (and this option seems good actually) ?
2) for DSS environnement, is it better to use method_opt = 'for all indexed colomuns size auto' ? best option is size=AUTO or SIZE=SKEWONLY ?
3) is AUTO_SAMPLE_SIZE relevant option and estimate percentage could it be different between table and related indexes ?
4) if no statistics are available (new objects), which percentage Oracle chooses first time ?
5) if this option not relevant, is it a good idea to dissociate the gather statistics indexes of the tables to specifie a different percentage for indexes ?
6) for table we can take a percentage between 5 to 10%. Indexes needs a higher percentage (even compute !) ?
7) in your opinion (experience) which automatic options (often the default) are inapropriate (dangerous ?)
thanks a lot.
Jean-Pierre
DBMS_STATS, 'LIST_STALE' consideration an limitation
February 27, 2007 - 8am Central time zone
Reviewer: jean-pierre from france
Tom,
1) Is it a good idea to iterate on each schema, collect the STALE object ('LIST STALE' option in first), and execute for each table the
gather_table_stats procedure with the following options :
estimate_percent=>dbms_stat.AUTO_SAMPLE_SIZE
method_opt=>'FOR ALL COLUMNS SIZE 1'
degree=> dbms_stats.AUTO_DEGREE
cascade=>TRUE
granularity=>'AUTO'
2) 'LIST STALE' option do not returns objects with empty statistics. It is moreover necessary to use 'LIST EMPTY' ?
3) if only one table partition has more than 10 percent of modifications, 'LIST STALE' will return how many rows (one for the concerned partition, and
one for the table level ?). What must I do to gather statistics for the concerned partition and at the table level, and for the related indexes (global or local) ?
Is it enought to execute only one statement to gather statistics : with granularity = AUTO and cascade = true or AUTO_CASCADE ?
4) if a new partition is added, 'LIST STALE' will return no rows ? It is neccessary to use 'LIST EMPTY' or gather statistics explicitely to the partition ?
5) in 10g when an index is created, statistics are implicitely gathered. With which options ? Can I disable this behaviour ?
6) GATHER_STATS_AUTO job has a stop_on_window_close option causing to terminate this job at the end of maintenance window. How can I do the same with the method
I have said before ?
7) GATHER_STATS_AUTO is (according to Oracle documentation) similar to GATHER_DATABASE_STATS with 'GATHER AUTO' option. But in the DB Consol we can owerride different
options. And yet, in documentation we can read that with 'GATHER AUTO', parameters like METHOD_OPT,DEGREE,and others are ignored. Where is the truth ?
thanks a lot.
Jean-Pierre
schema name in dbms_stats.gather_table_stats
March 16, 2007 - 10am Central time zone
Reviewer: A reader
Tom please explain me about schema name in one of parameters of DBMS_STATS.gather_table_stats.
SQL> conn scott
Enter password: *****
Connected.
SQL> create table t as select * from dual;
Table created.
-- I try to do this but getting an error
SQL> exec dbms_stats.gather_table_stats(scott, 't');
BEGIN dbms_stats.gather_table_stats(scott, 't'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 37:
PLS-00201: identifier 'SCOTT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> exec dbms_stats.gather_table_stats(user, 't');
PL/SQL procedure successfully completed.
But when I try user as schema name it is succeed. So what user actually is ?
Thanks.
Followup March 17, 2007 - 3pm Central time zone:
you need to pass a string in.
scott - that is an identifier
'SCOTT' - that is a string with the schema name scott in it.
USER - that is a function that returns the string 'SCOTT' when scott is logged on.
dbms_stats
March 27, 2007 - 4pm Central time zone
Reviewer: reader
In sqlserver 2005, there is a database option called AUTO_CREATE_STATISTICS. If this is set to ON, statistics are automatically created on columns used in a predicate.
Is there a similar option in oracle when we use dbms_stats package in 9i?
In 10g, does GATHER_STATS_JOB do this?
Thanks.
global_stats
May 29, 2007 - 12pm Central time zone
Reviewer: A reader
What is global_stats?
How to make it YES or NO in dbms_tables?
Is it important for optimizer to make decisions?
Thanks,
Followup May 30, 2007 - 11am Central time zone:
are you using partitioning? if not, they mean nothing to you.
dbms_stats slow on tables with many columns
June 20, 2007 - 5pm Central time zone
Reviewer: Udi Karni from California, USA
Hi Tom,
Dbms_stats typically runs very fast, even on partitioned tables. However - on very wide (hundreds of columns) partitioned tables - it switches to a cycle of "fast-slow" which overall takes a long time.
Increasing the parallel or decreasing sample size doesn't help.
Ideas?
NUM_ROWS and SAMPLE_SIZE
July 30, 2007 - 11am Central time zone
Reviewer: Chaman from Mumbai
Tom,
The NUM_ROWS column of ALL_TABLES table has the value which nearly equal to the actual number of
rows but the SAMPLE_SIZE column is 5% of the number of rows.
Does this mean that the table was analyze with a sample size of 5%? If so in that case the NUM_ROWS
must be equal to 5% of total number of rows.
Followup July 30, 2007 - 5pm Central time zone:
why would num_rows be 5%
if we sample 5% of the rows, we extrapolate up (eg: multiply by 20) to arrive at....
the aggregate statistics.
we sample to avoid processing the entire result set and we extrapolate (guess) at the real numbers.
granularity and partname in dbms_stats
July 30, 2007 - 5pm Central time zone
Reviewer: Jay from New Jersey, USA
Hi, Tom,
If I just specify partname without specifying granularity, does gather_table_stats collects stats
on the table level or partition level? The situation I have is that I have a large partitioned
table, with 65 partitions of 1.5 million rows each. I just wanted to collect stats on a newly
inserted partition for the moment. But it took long and during the process it looks like it was
repeatedly scanning all partitions of the full tables (as I can see in the v$session_longops) and
it was executing something like
insert /*+ append */ into sys.ora_temp_1_ds_26332
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring */
col1, col2, ..., col19, rowid SYS_DS_ALIAS_0
from myschema.mytable sample ( .1274453176) t;
and then
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl
dynamic_sampling(0) no_monitoring */ count(*),count("COL1"),count(distinct "COL2"),... from
myschema.mytable sample ( 1.2744531756) t;
So I guessed it was actually collecting stats on the full table since I didn't specify granularity,
even though I specified the partname.
However, at the end of the stats collection, I checked dba_tab_partitions and dba_ind_partitions
and only saw the partition I specified showed recently updated last_analyzed column. So I'm
confused why it was doing the above two mentioned SQLs at all in an apparent scan of full table.
Thanks,
Jay
Followup August 2, 2007 - 9am Central time zone:
It'll vary by release possibly as granularities default value can and will change. However, in 10gR2, it defaults to auto and will get the table (global statistics) as well as the partition (local) statistics for that single partition, similar results will be seen in 9i (except that get_param did not exist back then..) :
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
4 OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
5 TEMPORARY, GENERATED, SECONDARY
6 )
7 PARTITION BY hash (object_id) partitions 8
8 as
9 select * from all_objects
10 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column partition_name new_val PNAME
ops$tkyte%ORA10GR2> select partition_name from user_tab_partitions where table_name = 'T';
PARTITION_NAME
------------------------------
SYS_P737
SYS_P738
SYS_P739
SYS_P740
SYS_P741
SYS_P742
SYS_P743
SYS_P744
8 rows selected.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select dbms_stats.get_param( 'GRANULARITY' ) from dual;
DBMS_STATS.GET_PARAM('GRANULARITY')
-------------------------------------------------------------------------------
AUTO
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', '&PNAME' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select to_char(last_analyzed,'dd-mon-yyyy hh24:mi:ss') la, num_rows from
user_tables where table_name = 'T';
LA NUM_ROWS
-------------------- ----------
02-aug-2007 08:52:02 49980
ops$tkyte%ORA10GR2> select partition_name, to_char(last_analyzed,'dd-mon-yyyy hh24:mi:ss') la,
num_rows from user_tab_partitions where table_name = 'T';
PARTITION_NAME LA NUM_ROWS
------------------------------ -------------------- ----------
SYS_P737
SYS_P738
SYS_P739
SYS_P740
SYS_P741
SYS_P742
SYS_P743
SYS_P744 02-aug-2007 08:52:02 6209
8 rows selected.
More explain needed ...
July 31, 2007 - 11am Central time zone
Reviewer: Chaman
Tom,
Thanks for your quick response. Sorry to say, I could not understand what you said.
Followup July 30, 2007 - 5pm US/Eastern:
why would num_rows be 5%
if we sample 5% of the rows, we extrapolate up (eg: multiply by 20) to arrive at....
the aggregate statistics.
we sample to avoid processing the entire result set and we extrapolate (guess) at the real numbers.
Followup August 2, 2007 - 10am Central time zone:
you asked to gather statistics.
you asked to sample only 5% of the rows.
We need to have statistics that represent the FULL TABLE. So, we sample 5% of the rows in the table.
And then multiply to get a guess as to what the full table would have.
analyze v.s. stats
August 1, 2007 - 5am Central time zone
Reviewer: A reader
Hello,Mr. Tom!
I have got lots of help from you!
Now I have a new question on analyze and dbms_stats.
I did a test below:
SQL> create table tab1(a number(1));
Table created
SQL> create table tab2(a char(1));
Table created
SQL> insert into tab1 values(1);
1 row inserted
SQL> insert into tab2 values('1');
1 row inserted
SQL> commit;
Commit complete
SQL> analyze table tab1 compute statistics;
Table analyzed
SQL> analyze table tab2 compute statistics;
Table analyzed
SQL> select table_name,AVG_ROW_LEN from user_tables where table_name in ('TAB1','TAB2');
TABLE_NAME AVG_ROW_LEN
------------------------------ -----------
TAB1 6
TAB2 5
SQL> exec dbms_stats.gather_table_stats(user,'TAB1')
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(user,'TAB2')
PL/SQL procedure successfully completed
SQL> select table_name,AVG_ROW_LEN from user_tables where table_name in ('TAB1','TAB2');
TABLE_NAME AVG_ROW_LEN
------------------------------ -----------
TAB1 3
TAB2 2
I have 2 questions:
1:Why the results of ANALYZE and DBMS_STATS are different?
2:I think lenth of columns of tab1 and tab2 is 1 bytes,but the result is 2,3(using DBMS_STATS) and
5,6(using ANALYZE),why?
I would be very thankful if ¿you give me some explain!
Best Regards!
Alan
Followup August 5, 2007 - 9am Central time zone:
1) because they are different commands. analyze to gather statistics is deprecated, the optimizer is designed to use dbms_stats generated statistics.
2) these are ROW WIDTHS - and there is necessary control data in a row used to store the data - eg; a varchar2 has a leading byte length. A row is a data structure, more than just your data
analyzing indexes,
August 2, 2007 - 11am Central time zone
Reviewer: A reader
Using DBMS_STATS analyzing an index can be done by setting CASCADE=TRUE while analying the tables,
or by using gather_index_stats.
I have been told that gather_table_stats with cascade=>true, does not analyze the index in parallel
(even though degree is set to big number like 8 or 12)
In that case, is it advisable to use gather_index_stats to analyze the index? Will this run in
parallel?
Another question:
We have multi terabyte database (more than 12TB) but not all tables in the database are involved in
every day DML activities.
Is it a good option considering speed and collecting good information in the dictionary, to use
DBMS_STATS.GATHER_SCHEMA_STATS with OPTIONS set to GATHER_STALE?
Currently we use dbms_stats.gather_table_stats for each and every table with CASCADE=true,
METHOD_OPT=for all indexed columns size 1 and estimate percent varying depending on the size of the
table.
Our problem is DBMS_STATS job run for more than 2-3 days and we want to reduce that.
August 3, 2007 - 12am Central time zone
Reviewer: A reader
Tom,
Which data dictionary tables are used to store database statistics if stat table is not specified.
Thanks.
Followup August 5, 2007 - 1pm Central time zone:
ours, the internal undocumented ones.
You see them in user_tables, user_indexes and the like. Where we put them physically - not relevant to you or me. Where you access them - priceless - and those are the documented data dictionary views
dbms_stats tuning
November 19, 2007 - 1pm Central time zone
Reviewer: a reader from uk
We are using Oracle 10.2.0.2 database. We have a partitioned table that has 900 million records. I
have a script to collect statistics on it using dbms_stats procedure. This seems to be taking
around 8 hours. Could you tell which parameters I need to look at in order to speed up the stats
collection for this table and any other ways to speed up this process. I am not sure what else you
need, so please let me know and I will give you all the details.
dbms_stats.gather_table_stats('TEST', 'PART_TABLE', partname => null, estimate_percent => 10,
block_sample => false, method_opt => 'FOR ALL COLUMNS SIZE 1', degree => 4, granularity =>
'DEFAULT', cascade => true);
Thanks
Followup November 21, 2007 - 1pm Central time zone:
there are too many 'details' to list here.
for example, do you EVEN NEED to gather stats (fastest way to do something is to NOT do it).
eg: if you say "well, we bulk load this table fresh every week", then I'll say "maybe you just use the SET_* functions to TELL us the statistics and don't gather them
eg: if you say "well, this is a rolling window partition - our queries ALWAYS specify a single partition key in the predicate (so the optimizer only hits a SINGLE partition at a time, definitely)" - then I'd say gather only local statistics and only on the partitions that have been modified - which should be one since this is a rolling window
eg: if you say "much of the same as right above, but we do not include the partition key, but we have normally distributed data so the global stats that are inferred from the local stats are better than good enough" - I say "same answer"
DBMS_STATS never finishes
November 21, 2007 - 4pm Central time zone
Reviewer: Suzanne from Portland OR
I have a DBMS_STATS job that runs once a week on one of my test databases. In the last two weeks
it has failed to complete. After a couple of days somebody notices that the system performance is
slow and one of the DBA's kills the job (this is not normal behavior). I've managed to track down
the table that seems to be the road block but I don't know how to debug this issue. Everytime I
start a DBMS_STATS subprogram (including delete_table_stats) the process seems to run and run.
Since there is no error message generated what Oracle facilities should I use to track down the
problem?
Followup November 21, 2007 - 4pm Central time zone:
have you tried tracing it with waits (you can tail -f the trace file on unix)
or use v$sesswait to see what it is waiting on plus v$sql to see what it is doing.
and of course, you've started the tar with support right?
dbms_stats
November 22, 2007 - 11am Central time zone
Reviewer: parlee from uk
I am running the dbms_stats procedure (as shown below) to collect stats on a partitioned table with
granularity as PARTITION and cascade FALSE (intend to collect statistics on indexes separately).
There are around 500 partitions on the table. I've been monitoring its performance. It takes on an
average 10 seconds to do each partition. In between, all of a sudden, it jumps to 20 odd minutes
per partition. I notice in the trace file that there are lot of db file scattered reads. After
investigating more, I found that the scattered reads are on the primary key index of this
partitioned table. I wonder,
1. Why does it to look at the PK index when I am limiting it to gather partition stats for table
only.
2. Even if it needs to look at the index, why doesn't it read the index whilst collecting stats for
every single partition.
Thanks in advance.
Followup November 26, 2007 - 11am Central time zone:
... In between, all of a sudden, it jumps to 20 odd
minutes per partition. ...
what does "in between" mean?
are you using auto for the rest of the inputs, what release, what is the FULL dbms_stats command you are using, what is different perhaps about the partitions in question.
dbms_stats
November 22, 2007 - 11am Central time zone
Reviewer: parlee from uk
I am running the dbms_stats procedure (as shown below) to collect stats on a partitioned table with
granularity as PARTITION and cascade FALSE (intend to collect statistics on indexes separately).
There are around 500 partitions on the table. I've been monitoring its performance. It takes on an
average 10 seconds to do each partition. In between, all of a sudden, it jumps to 20 odd minutes
per partition. I notice in the trace file that there are lot of db file scattered reads. After
investigating more, I found that the scattered reads are on the primary key index of this
partitioned table. I wonder,
dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'PART_TABLE', partname => 'P1',
estimate_percent => 10,
block_sample => false, method_opt => 'FOR ALL COLUMNS SIZE 1', degree => 4, granularity =>
'PARTITION', cascade => false);
1. Why does it to look at the PK index when I am limiting it to gather partition stats for table
only.
2. Even if it needs to look at the index, why doesn't it read the index whilst collecting stats for
every single partition.
Thanks in advance.
Followup November 26, 2007 - 11am Central time zone:
how about tkprofing that output and posting the SQL that was being executed when you saw the reads?
DBMS_STATS never returns
November 27, 2007 - 12pm Central time zone
Reviewer: Suzanne from Portland OR
Thank you for the suggestion about v$session_wait. I used these two queried to find my wait event.
select * from v$session_wait where sid = '39';
select sql_text, sql_hash_value, sadmin
from v$session
join v$sql
on sql_hash_value = hash_value
where sid = '39';
and then I used this approach, I found on Metalink, to isolate the blocking process.
METHOD 2: EXAMINE THE X$KGLLK TABLE
-----------------------------------
The X$KGLLK table (accessible only as SYS/INTERNAL) contains all the
library object locks (both held & requested) for all sessions and
is more complete than the V$LOCK view although the column names don't
always reveal their meaning.
You can examine the locks requested (and held) by the waiting session
by looking up the session address (SADDR) in V$SESSION and doing the
following select:
select * from x$kgllk where KGLLKSES = 'saddr_from_v$session'
This will show you all the library locks held by this session where
KGLNAOBJ contains the first 80 characters of the name of the object.
The value in KGLLKHDL corresponds with the 'handle address' of the
object in METHOD 1.
You will see that at least one lock for the session has KGLLKREQ > 0
which means this is a REQUEST for a lock (thus, the session is waiting).
If we now match the KGLLKHDL with the handles of other sessions in
X$KGLLK that should give us the address of the blocking session since
KGLLKREQ=0 for this session, meaning it HAS the lock.
SELECT * FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0);
If we look a bit further we can then again match KGLLKSES with SADDR
in v$session to find further information on the blocking session:
SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);
In the same way we can also find all the blocked sessions:
SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ > 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKING SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);
I ended up finding a zombie process that had held a lock on one of my tables since Oct 29 - I, of
course, kill the process and my problem went away. Thank you again for your help.
dbms_stats index lookup
November 28, 2007 - 11am Central time zone
Reviewer: parlee from uk
Sorry Tom, I got busy with other work. Please see below the SQL output and execution plan from
tkprof output. It shows that it is doing index scan for collecting statistics for a partition of
PART_TABLE table. By "all of a sudden it jumped to 20 odd minutes", I meant, initially it was
taking around 10 seconds to collect statistics for each partition and then some partitions took
around 20 minutes, then it went down to around 10 seconds again. When it was taking 20 minutes, I
traced the session, the output of which is listed below. Please note that it is not the complete
output from the tkprof output file, I have just copied the first SQL output. I can send the whole
file if needed.
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
*/ count(*) from "TEST"."PART_TABLE" t where
TBL$OR$IDX$PART$NUM("TEST"."PART_TABLE",0,4,0,"ROWID") = :objn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 75 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=397784 pr=397772 pw=0 time=1350410697 us)
17424 INDEX FAST FULL SCAN PART_TABLE_PK_1 (cr=397784 pr=397772 pw=0 time=1350393394 us)(object
id 226025)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 3710 0.46 571.96
db file sequential read 6 0.00 0.00
Followup November 28, 2007 - 11pm Central time zone:
I see lots of scattered reads- which are not single block IO's but multiblock IOs and I see them sometimes taking 1/2 second which is GLACIAL in computer terms.
why is an IO on your system taking 1/2 second. I think perhaps something else is happening big time on your system at that time.
dbms_stats tuning
November 29, 2007 - 10am Central time zone
Reviewer: parlee from uk
regarding slow i/o, i know that it was computing statistics using threads, not sure if they were
slaves or just other sessions collecting statistics on other partitions in parallel. i am sure
there was nothing else running on the server at the time.
how did you find out the i/o taking 1/2 second?
i know that it does db file sequential read for index scan. however, does it do db file scattered
read for INDEX FAST FULL SCAN?
Followup November 29, 2007 - 7pm Central time zone:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 3710 0.46 571.96
db file sequential read 6 0.00 0.00
you told me it was 1/2 second.
max wait - 0.46
fast full scans are done just like a full scan - multiblock IO, so yes, they do scattered read waits.
dbms_stats tuning
December 4, 2007 - 5am Central time zone
Reviewer: parlee from uk
So the question is why it was doing index scan when collecting statistics on individual partitions.
Many Thanks
Followup December 3, 2007 - 7am Central time zone:
and did you go back to the tkprof to see which if the sql's it was executing did them - that would give us a good clue as to what it was doing..
aaaaaaaaaaaaaaaaa
December 10, 2007 - 7am Central time zone
Reviewer: A reader
aaaaaaaaaaaaaa
Followup December 10, 2007 - 11am Central time zone:
catch me now I'm falling...... aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
dbms_stats tuning
December 12, 2007 - 10am Central time zone
Reviewer: parlee from uk
I did include the sql that caused it to use the index. Please see below the sql:
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
*/ count(*) from "TEST"."PART_TABLE" t where
TBL$OR$IDX$PART$NUM("TEST"."PART_TABLE",0,4,0,"ROWID") = :objn
Followup December 12, 2007 - 12pm Central time zone:
I don't see what the problem is - the optimizer just decided to use an index on some apparently NOT NULLABLE column(s) as if it were a skinny version of the table.
would you have preferred to full scan the table? It instead fast full scanned a smaller index.
dbms_stats tuning
December 13, 2007 - 7am Central time zone
Reviewer: parlee from uk
this table is very big, around 500 million records. there are three indexes on this table, all
being global. i have a script that collects statistics for each partition as a one off (just before
the system goes live). thereafter we will collect statistics on partition with state statistics.
when collecting statistics first time on each partition, it runs within 10 seconds for most of them
and in between takes 20 minutes for few partitions (20 mins each). when i monitored during the slow
running partition sql, i found that it was doing INDEX FAST FULL SCAN. since the index is very
large (global index), it takes long time and hence taking 20 mins. i would not have thought it to
do the index scan. please note that i had set CASCADE to FALSE.
Followup December 13, 2007 - 9am Central time zone:
the index does not look that big
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 3710 0.46 571.96
only 3710 IO's - what, maybe 3gb? But that aside - how big is the partition itself, is the partition bigger or smaller than that global index.
And - almost 1/2 second to perform an IO, that is glacial - is there much contention on this machine, or is the disk just not configured very well?
dbms_stats tuning
December 13, 2007 - 10am Central time zone
Reviewer: parlee from uk
the index is around 6.5 GB in size. the partitions are daily partitions (one for each day).
partitions are very small compared to the index size, max 100MB each. partitions for a month reside
in its own tablespace, this is for archiving purpose.
this was run on a test server and i agree the disks were not configured efficiently. having said
that, i wouldn't expect it to be as bad as 0.5 second per read. from the statistics, doesn't it
mean 0.46 second was the max wait, other times it could have been a lot faster. it actually comes
down to average wait of 0.15 seconds per read (571/3710). please correct me if i am interpreting it
wrong.
Followup December 13, 2007 - 11am Central time zone:
other times could have been faster, yes - the average was 0.15 which is still pretty "glacial"
is it possible the optimizer thinks this index is small? what are the stats on this index
Package Procedures
December 13, 2007 - 6pm Central time zone
Reviewer: Rajeshkumar from Hartford,CT
Hi Tom,
I have 1000 PL/SQL packages. I need to find out the different public and the private procedures
within those packages. How can I do this ? Is there any data dictionary which gives these details ?
As these procedures are not stand-alone and are found within the packages, I couldnt find any
dictionary table which has their details. Please help me in this.
Thanks
Rajesh
Followup December 14, 2007 - 12pm Central time zone:
public, yes - user_arguments
private, no - you'd have to do that yourself.
Package Procedures
December 14, 2007 - 10am Central time zone
Reviewer: Rajesh from Hartford,CT
In other words, how does SQL Navigator finds the public and private procedures ? Can you please
send me that query ?
Followup December 14, 2007 - 3pm Central time zone:
public, they can discover via user_arguments.
private, they must be parsing the plsql code itself.
dbms_stats tuning
December 18, 2007 - 9am Central time zone
Reviewer: parlee from uk
we had 100% statistics collected on index at the time (we were trying different level of statistics
to get the timings). but i am sure the stats were collected with estimate_percent of 100% when we
had this problem with index scan for partitions. unfortunately the test server has been rebuilt
since then, so i do not have required details.
if for any reason it thought index scan to be better than the partition scan then the question is
what made it to do partition scan whilst collecting statistics for other partitions. there were
only one in every 30 to 40 odd partitions when it decided to do index scan.
Public and Private Functions
December 19, 2007 - 3pm Central time zone
Reviewer: Rajesh from Hartford,CT
how does SQL Navigator finds the public and private functions ?
Followup December 20, 2007 - 9am Central time zone:
like I said, it can use user_arguments for things in the specification
and it must be reading the code and parsing it to find "private"
Reverse Merge
January 8, 2008 - 1pm Central time zone
Reviewer: Rajesh from Hartford,CT
Hi Tom,
After commiting a merge statement in oracle, Is there anyway to rollback the data in the same
merge statement ?
For example..
Merge into A using B on (A.id = B.id)
When Matched then update A.col1 = B.col1
When Not Matched then insert values(B.col1,B.col2);
commit;
After 2 or 3 hours, I want to rollback the data. I am familiar of doing this rollback. But I would
like to know is there any option in Merge statement like...
Merge into A using B
When matched then update
When not matched DELETE;
This is like a reverse Merge statement. Is it possible to perform such rollback using merge ? or
how will you handle this ?
Thanks
Rajesh
Followup January 8, 2008 - 2pm Central time zone:
... After commiting a merge statement in oracle, Is there anyway to rollback the
data in the same merge statement ? ...
A commit does one thing very well: It makes permanent the changes made by inserts, updates, deletes, and merges....
There is no "rolling back"
That said - there is
a) flashback table
b) flashback query
c) flashback versions query
d) flashback transaction
Sounds like flashback table might be what you are looking for.
PL/SQL from UNIX
January 10, 2008 - 10am Central time zone
Reviewer: Rajesh from Hartford,CT
Hi Tom,
I was trying to call a PL/SQL function from UNIX. The PL/SQL was giving me an error. But it was not
reflected in the UNIX shell.
The shell script will be..
ERROR=0
ALLDETAIL=`get_interface_details $INTERFACECODE`
echo $ALLDETAIL
ERROR=$?
get_interface_details is a UNIX function which calls the PL/SQL function as follows..
get_interface_details()
{
sqlplus -s $SQLCONNECT 2>&1 <<EOF |grep ALLDETAIL|tail -1|awk '{print $2 " " $3 " " $4 " " $5}'
|read ALLDETAIL
whenever sqlerror exit 170
SET echo on
SET feedback off
SET heading off
SET linesize 120
SET serveroutput on
SET pagesize 0
SET verify off
SET space 0
spool $LOGDIR/$LOGFILE.SQL.log append
select '****************************COLLECT INTERFACE DETAILS SQLLOG******************************'
from dual;
select 'ALLDETAIL: '||NF_BTCH_UTL_PKG1.get_intrfc_dtl('$1') ret from dual
.
l
/
select '****************************COLLECT INTERFACE DETAILS SQLLOG******************************'
from dual;
spool off
EOF
RC=$ALLDETAIL
if [ -z $RC ]
then
RC=0
echo $RC
else
echo $RC
fi
return
}
So in this UNIX function call, we track the error by the variable ERROR ($?) as follows...
ERROR=0
ALLDETAIL=`get_interface_details $INTERFACECODE`
echo $ALLDETAIL
ERROR=$?
This ERROR should be 170 as PL/SQL is raising an error as we have the following line in the above
code..
whenever sqlerror exit 170
But still I get the ERROR as 0. Where am I going wrong ?
Thanks
Rajesh
Followup January 10, 2008 - 2pm Central time zone:
first, let us clear up some semantics here.
Unix does not call PLSQL.
Unix, the operating system, may execute some programs. One of these is the shell (/bin/bash for example). bash has a scripting language that allows it to call it's own procedures and such - and use Unix, the operating system, to do things like run other programs.
So, you have a shell script that runs sqlplus and sqlplus dynamically executes a stored procedure. There is no "calling" here - they are all distinctly different beasts with very very limited "communication" capabilities.
now, typically, $? is the error from the last command - and on many systems, echo is - well, a program - a command. You should grab things like $? IMMEDIATELY after the command of interest - IMMEDIATELY.
$ cat test.sh
#!/bin/sh
ERROR=0
sqlplus / <<EOF
whenever sqlerror exit 170
begin
raise program_error;
end;
/
select * from dual;
EOF
ERROR=$?
echo error = $ERROR
echo Hello World
ERROR=$?
echo error = $ERROR
$ sh test.sh
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jan 10 14:49:30 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2> 2 3 4 begin
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at line 2
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
error = 170
Hello World
error = 0
[tkyte@dellpe ~]$
see, echo succeeded, error is ZERO...
but, if you catch error RIGHT AFTER sqlplus exits (do that in your function), you'll get the error code.
PL/SQL from UNIX
January 14, 2008 - 11am Central time zone
Reviewer: Rajesh from Hartford.CT
Hi Tom,
As you said, I tried to get the error code 170 within the UNIX function itself. But it didnt work.
get_interface_details()
{
sqlplus -s $SQLCONNECT 2>&1 <<EOF |grep ALLDETAIL|tail -1|awk '{print $2 " " $3 " " $4 " " $5}'
|read ALLDETAIL
whenever sqlerror exit 170
SET echo on
SET feedback off
SET heading off
SET linesize 120
SET serveroutput on
SET pagesize 0
SET verify off
SET space 0
spool $LOGDIR/$LOGFILE.SQL.log append
select '****************************COLLECT INTERFACE DETAILS SQLLOG******************************'
from dual;
select 'ALLDETAIL: '||NF_BTCH_UTL_PKG1.get_intrfc_dtl('$1') ret from dual
.
l
/
select '****************************COLLECT INTERFACE DETAILS SQLLOG******************************'
from dual;
spool off
EOF
ERROR=$?
echo $ERROR <= It still gives the error code as 0
RC=$ALLDETAIL
if [ -z $RC ]
then
RC=0
echo $RC
else
echo $RC
fi
return
}
It still gives the error code 0 even when a PL/SQL error is raised within the
NF_BTCH_UTL_PKG1.get_intrfc_dtl function.
Am I going wrong anywhere in the program ?
Thanks
Rajesh
Followup January 14, 2008 - 3pm Central time zone:
maybe it just isn't failing the way you think it is.
this is the time to strip out everything not relevant to the problem you are thinking you have....
and make a teeny tiny itty bitty standalone example (just like I did)
that is how I find what I did wrong, by stripping out anything not part of the actual problem.
You have a big example that no one on the planet can run and experience what you see - make it something we we can see (eg: you need run NONE of your code)
hah, I see it, think about this line:
sqlplus -s $SQLCONNECT 2>&1 <<EOF |grep ALLDETAIL|tail -1|awk '{print $2 " " $3
what do you think the error code in shell would be for that if sqlplus fails? Now, what if grep fails? Now, what if tail failed? Then, what if awk fails?
Now, what if awk succeeds, but tail failed - what would be the code?
Now, what if sqlplus exits with non-zero, but awk is fine and dandy - what is the return code the SHELL gets?
if you were to have made the smallest of examples, you would have discovered this, you would have stripped out everything but sqlplus and echo $?
it would have worked
then you would have added in stuff till it broke - which would tell you what went wrong. It would have been obvious at that point...
Search
January 18, 2008 - 4pm Central time zone
Reviewer: Rajesh from Hartford,CT
Hi Tom,
This is about the search of table data. We can search any text in the package using the
dba_source. Similarly, can we search a text among the table data ? i.e. we dont know the table or
column name and know only the text to be searched in the tables. We should now find the table where
the text is present.
Is it possible ?
Thanks
Rajesh
Followup January 19, 2008 - 10pm Central time zone:
look in the OTHER PLACE you posted this EXACT SAME TEXT.
See, now if I were to trawl for text, I would get two hits due to you and your repetitive stuff!
dbms_stats.gather_system_stats
September 12, 2008 - 3pm Central time zone
Reviewer: Stan from Boston
Hi Tom,
By reviewing:
select * from sys.aux_stats$
It doesn't look like workload stats have been created in the system (SREADTIM, MREADTIM, etc are
null). When our high load window starts I want to run:
exec dbms_stats.gather_system_stats('start');
wait until high load window is over and run:
exec dbms_stats.gather_system_stats('stop');
I have 2 questions:
1) When will this potentially start to effect query plans? When the stop is done?
2) How dramaticaly can the plans change? I'm worried something might change for the worse - is
this possible?
Thanks in advance.
Followup September 16, 2008 - 2pm Central time zone:
1) yes
2) one of three things will happen....
they go faster
they stay the same
they go slower
that is always true - when you gather (or not gather even) statistics. You can export statistics and revert back as fast as you want.
error while running dbms_stats
October 19, 2008 - 10pm Central time zone
Reviewer: Manoj from London,UK
Hi Tom,
Thanks for your time.
I am trying to schedule the dbms_stats to gather the schema stats as follows:
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'sys.dbms_stats.gather_schema_stats(ownname=>''AA'', degree=>4, cascade=>true,
options=>''gather auto'');'
,next_date => to_date('23/09/2008 00:05:52','dd/mm/yyyy hh24:mi:ss')
,interval => 'trunc(SYSDATE+1)+(11/48)'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
commit;
but the job is failing with the following error:
¿ORA-04031:Unable to allocate nnn bytes of shared memory (`large pool¿, `unknown object¿, `large
pool hea¿, `PX large pool¿)¿
The large pool size is :
select * from v$sgastat where pool like 'large%pool'
POOL NAME BYTES
large pool PX msg pool 16477504
large pool free memory 299712
Questions:
a)Can you please tell me why the job is failing? Is large pool value is low?
b)Is dbms_stats is trying to run parellal query..? Not sure just i read in some posting. Please
correct me here .
c) Could it be due to fragmentation in shared pool?
d) Can you please give one example, if feasible wherein you can show the job failing and then
correct the problem by changing the SGA params?
Kind Regards,
Followup October 21, 2008 - 11am Central time zone:
a) it would appear so, what is the large pool set it itself and what is the parallel_execution_message_size
b) yes, you told it to use parallel query. degree=>4
c) well, it is talking about the large pool here, so - shared pool? no.
d) not even a little bit comprehending why that would be relevant?
error while running dbms_stats ....
October 21, 2008 - 9am Central time zone
Reviewer: Manoj from London, UK
Hi Tom
Further inputs to my above question:
select server, count(*) from v$session group by server;
SERVER COUNT(*)
DEDICATED 72
Kind Regards.
error while running dbms_stats ....
October 21, 2008 - 2pm Central time zone
Reviewer: Manoj from London, UK
Hi Tom
Many Thanks for your reply.
...a) it would appear so, what is the large pool set it itself and what is the
parallel_execution_message_size
Here are the values:
select value from v$parameter where name = 'parallel_execution_message_size';
4096
select value from v$parameter where name='large_pool_size';
16777216
Regards.
Followup October 21, 2008 - 3pm Central time zone:
it would appear that your large pool is insufficient for what you are asking to do - 16mb is rather smallish and it is currently allocated.
error while running dbms_stats ....
October 21, 2008 - 4pm Central time zone
Reviewer: Manoj from London, UK
Hi Tom,
Many thanks for prompt reply. I keep a tab on "Where is Tom?" and find that in spite of your busy
schedule you always take time to answer the questions.
Kodos! to you .
coming to the question now ,
you said above -
...it would appear that your large pool is insufficient for what you are asking to do - 16mb is
rather smallish and it is currently allocated.
Now what approach shall i follow to get rid of ORA-04031.
I read it on some doc and it says that make it present_value*2 and then see if you are being hit by
ORA-04031.
If not further, reduce it and keep reducing... till you get hit.
Then once hit, set the value just above which is safe to not get hitted.
Regards
Followup October 22, 2008 - 8am Central time zone:
or reduce your needs for the resource (eg: parallel query).
Do you need parallel query for this - if not, that would be another perfectly valid approach.
error while running dbms_stats ....
October 22, 2008 - 8pm Central time zone
Reviewer: Manoj from London , UK
Hi Tom,
...or reduce your needs for the resource (eg: parallel query).
I will run the the stats gathering procedure without parellal option.
...Do you need parallel query for this - if not, that would be another perfectly valid approach.
We have #16 CPUs in db box. I was reading this site and found at
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4541159655224
<quote TOM>
2) degree specifies the number of threads/processes that will be used. since these processes spend
lots of time doing IO -- not using CPU, you would find that your CPU is not pegged, not fully
utilized, if you set degree = #cpu -- since the processes spend lots of time in IO waits. In
order to more fully utilize this resource (that you cannot put in the bank and use later -- its a
use or lose resource), you would use a higher degree.
</quote>
Tom kindly see, if my understanding as written below is correct.
If we set degree=4 (actually we should set it 16-1 i.e. 15) then that many threads will be used to
run the sqls ( mostly full scan/ index scan ) by gather_schema_stat procedure. So, n thread => will
occupy the large pool n times larger then had the schema been gatherd without parellism? And thus
we can get rid of large pool scarcity. Basically , here we are trying to use the available
*small(16M)* large pool sequentially.?
Many thanks and sorry for keeping my question *thread* long and long..
Regards
Followup October 23, 2008 - 9am Central time zone:
you do not say if you NEED parallel query.
parallel is going to be either
a) faster
b) slower
c) the same as
serial. Do you have really large things, would they benefit from parallel.
and - very importantly - are you the ONLY THING on the machine or are you competing for resources.
error while running dbms_stats ....
October 22, 2008 - 8pm Central time zone
Reviewer: Manoj from London, UK
c/And thus we can get rid of large pool scarcity./So, by going without parallel we can get rid of
large pool scacity./
Followup October 23, 2008 - 9am Central time zone:
that was my point, if you don't use parallel, you won't be using the large pool - hence not using parallel is another perfectly valid approach to solving this issue.
error while running dbms_stats ....
October 23, 2008 - 1pm Central time zone
Reviewer: Manoj from London, UK
Hi Tom,
.....parallel is going to be either
a) faster
b) slower
c) the same as
serial. Do you have really large things, would they benefit from parallel.
I truly understand your analogy on "going parallel?" - writing 1 page summary for the new product
vs writing *book* ( expert one.)
...and - very importantly - are you the ONLY THING on the machine or are you competing for
resources.
There are OTHER THING as well, so i would rather prefer to leave the resources for OTHERS.
......you do not say if you NEED parallel query.
So i will run it without prallel.
Many Thanks for your time.
Kind Regards.
error while running dbms_stats ....
October 23, 2008 - 7pm Central time zone
Reviewer: Manoj from London, UK
Hi Tom,
I tried to run the dbms_stat as follows with degree=null as follows:
sys.dbms_stats.gather_schema_stats(ownname=>'XXXX', degree=>null, cascade=>true, options=>'gather
auto');
Here degree=>null i.e. table default
But still it running as parallel query i got the obvious error:
ORA-12012: error on auto execute of job 81
ORA-12801: error signaled in parallel query server P022, instance xxxxx:yyyyy (1)
ORA-04031: unable to allocate 16096 bytes of shared memory ("large pool","unknown object","large
pool","PX msg pool")
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10553
ORA-06512: at "SYS.DBMS_STATS", line 10718
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1
Further,
SQL>select distinct (degree) from user_tables;
1
I found the possible values of degree as
o NULL means use the table default value
o DBMS_STATS.DEFAULT_DEGREE means value based on the init parameters (9i).
SQL> select name,value from v$parameter where name like '%parallel%';
NAME VALUE
log_parallelism 1
parallel_server TRUE
parallel_server_instances 2
recovery_parallelism 0
fast_start_parallel_rollback LOW
parallel_min_percent 0
parallel_min_servers 4
parallel_max_servers 240
parallel_instance_group
parallel_execution_message_size 4096
parallel_adaptive_multi_user TRUE
parallel_threads_per_cpu 3
parallel_automatic_tuning TRUE
How to run dbms_stat *without parallel* ?
Kind regards.
Followup October 24, 2008 - 12pm Central time zone:
240 max parallel servers?!??!?!?!? You really believe each of your cpu's can do 15 things at once...
can you run this in the foreground to verify what you think is getting called is in fact getting called - or query up job 81 out of the dictionary for us to confirm.
error while running dbms_stats ....
October 23, 2008 - 7pm Central time zone
Reviewer: Manoj from London, UK
Hi Tom
Further, degree from user_indexes:
SQL> select distinct (degree) from user_indexes;
0
1
error while running dbms_stats ....
October 26, 2008 - 7am Central time zone
Reviewer: Manoj from UK
Hi Tom
Many thanks for your time.
...240 max parallel servers?!??!?!?!? You really believe each of your cpu's can do 15 things at
once...
a) For large DB the value should be ?
#cpu x 5
(i.e. 16x5)
...can you run this in the foreground to verify what you think is getting called is in fact getting
called - or query up job 81 out of the dictionary for us to confirm.
b) here is the dbms_stat job sumitted and rest. ( JOB ID is now 82, as i had removed prev job).
server1 $ more submit_job.sh
sqlplus XXXX/<YYY> <<EOF
spool jobs_s.lst
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'sys.dbms_stats.gather_schema_stats(ownname=>''XXXX'', degree=>null,
cascade=>true, options=>''gather auto'');'
,next_date => to_date('24/10/2008 05:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'trunc(SYSDATE+1)+(5/24)'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
commit;
spool off;
EOF
server1 $ sh submit_job.sh
SQL*Plus: Release 9.2.0.6.0 - Production on Fri Oct 24 19:51:15 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13
PL/SQL procedure successfully completed.
SQL> SQL>
Commit complete.
SQL> SQL> SQL> SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit
Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
server1 $
SQL>select job,last_date,last_sec,next_date,next_sec,broken,failures,what,nls_env,misc_env,instance
from user_jobs;
JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC BROKEN FAILURES
WHAT NLS_ENV MISC_ENV INSTANCE
82 24/10/2008 19:51 19:51:16 24/10/2008 19:54 19:54:04 N 1
sys.dbms_stats.gather_schema_stats(ownname=>'XXXX', degree=>null, cascade=>true, options=>'gather
auto'); NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR'
NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' 102000200000000 0
server1 $ ls <bdump_dir>/<sid>_p*.trc | wc -l
200
#in bdump dir many trace files got generated after submitting the job:
server1 $ view <bdump_dir>/alert_<sid>.log
Fri Oct 24 19:52:04 2008
Errors in file <bdump_dir>/<sid>_j000_15995.trc:
ORA-12012: error on auto execute of job 82
ORA-12805: parallel query server died unexpectedly
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10553
ORA-06512: at "SYS.DBMS_STATS", line 10718
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1
.....
....
Errors in file <bdump_dir>/<sid>_j000_5974.trc:
ORA-12012: error on auto execute of job 82
ORA-12801: error signaled in parallel query server P016, instance server1:<sid> (1)
ORA-04031: unable to allocate 16096 bytes of shared memory ("large pool","unknown object","large
pool","PX msg pool")
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10553
ORA-06512: at "SYS.DBMS_STATS", line 10718
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1
server1 $view <bdump_dir>/<sid>_j000_15995.trc
*** 2008-10-24 19:52:04.704
ORA-12012: error on auto execute of job 82
ORA-12805: parallel query server died unexpectedly
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10553
ORA-06512: at "SYS.DBMS_STATS", line 10718
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1
kjbhistory[0x0.0]
kclassert no lock[0x0.0]
kjbhistory[0x0.0]
kclassert no lock[0x0.0]
kjbhistory[0x0.0]
kclassert no lock[0x0.0]
Kind regards.
Followup October 26, 2008 - 10am Central time zone:
a) size of database - not relevant.
size of database machine - relevant.
You should let them all default.
b) verify the objects in the schema in question are not parallel enabled (yes, you ran queries and showed output above, but I'm not sure that it was the same user as the job runs as
run dbms_stats - not in the job queue, do it in the foreground once please.
error while running dbms_stats ....
October 26, 2008 - 1pm Central time zone
Reviewer: Manoj from UK
Hi Tom,
Many thanks for your time. I am getting prompt reply from you even on Sunday.
...verify the objects in the schema in question are not parallel enabled (yes, you ran queries and
showed output above, but I'm not sure that it was the same user as the job runs as
SQL> select table_name,degree from user_tables where degree !=1;
select table_name,degree from user_tables where degree !=1
*
ERROR at line 1:
ORA-01722: invalid number
SQL>
SQL> select distinct(degree) from user_tables;
DEGREE
----------
1
SQL>
a) Looks like problem with some of the values in degree column of user_tables? ANd therefore it is
not taking that value as 1?
Further,
SQL>select * from (select 'Index' , index_name,degree from user_indexes where degree != 1) where
rownum<2;
'INDEX' INDEX_NAME DEGREE
Index SYS_IL0000073101C00008$$ 0
b) we have 145 such LOB indexes with degree !=1, all have degree=0, but this should not be an issue
?
c) Yes, it was the same user as the job runs as.(Username - XXXX. Sorry i would have given some
meaningful name to username)
...run dbms_stats - not in the job queue, do it in the foreground once please.
presently some batch job is running in the database. So i will run dbms_stat in forground when
there are low actvities in DB and send it to you.
Kind Regards.
Followup October 26, 2008 - 1pm Central time zone:
degree one is the default.
degree zero is the default for the index on a lob segment.
so that is normal.
error while running dbms_stats ....
October 26, 2008 - 8pm Central time zone
Reviewer: Manoj from UK
<code>Hi Tom,
Many thanks for your valuable time. I ran the dbms_stats in forground( out of job queue) as follows:
server1 $ more forground_run.sh
sqlplus MAN/<pass> <<EOF
spool dbms_job_run_forground.lst;
set time on
exec dbms_stats.gather_schema_stats(ownname=>'MAN', degree=>null, cascade=>true, options=>'gather auto');
commit;
spool off
exit;
server1 $
server1 $ nohup ./forground_run.sh &
[1] 8922
server1 $ Sending output to nohup.out
server1 $
server1 $
[1] + Done nohup ./forground_run.sh &
server1 $
server1 $ more nohup.out
SQL*Plus: Release 9.2.0.6.0 - Production on Sun Oct 26 23:45:43 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> SQL> SQL> SQL> 23:45:43 SQL> 23:45:43 SQL> BEGIN dbms_stats.gather_schema_stats(ownname=>'MAN', degree=>null, cascade=>true, options=>'gather auto'); END
;
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P012, instance server1:instance (1)
ORA-04031: unable to allocate 16096 bytes of shared memory ("large
pool","unknown object","large pool","PX msg pool")
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10553
ORA-06512: at "SYS.DBMS_STATS", line 10718
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1
23:46:04 SQL> 23:46:04 SQL>
Commit complete.
23:46:04 SQL> 23:46:04 SQL> 23:46:04 SQL> 23:46:04 SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
server1 $
## output from the spool file -- same as nohup.out
server1 $ more dbms_job_run_forground.lst
SQL>
SQL> set time on
23:45:43 SQL>
23:45:43 SQL> exec dbms_stats.gather_schema_stats(ownname=>'MAN', degree=>null, cascade=>true, options=>'gather auto');
BEGIN dbms_stats.gather_schema_stats(ownname=>'MAN', degree=>null, cascade=>true, options=>'gather auto'); END;
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P012, instance server1:instance1 (1)
ORA-04031: unable to allocate 16096 bytes of shared memory ("large
pool","unknown object","large pool","PX msg pool")
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10553
ORA-06512: at "SYS.DBMS_STATS", line 10718
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1
23:46:04 SQL>
23:46:04 SQL> commit;
Commit complete.
23:46:04 SQL>
23:46:04 SQL> spool off
server1$
server1 $ cd <bdump_dir>
server 1 $ ls *_p*.trc
300
#Many trace file generated
# format <SID>_p<nnn>_<mmm>.trc
server1 $ view <SID>_p024_22175.trc
....
.....
*** 2008-10-26 23:46:04.374
*** SESSION ID:(80.5116) 2008-10-26 23:46:04.374
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora parameter _4031_dump_bitvec = 0
======================================
Allocation Request Summary Information
======================================
Current information setting: 00654fff
Dump Interval=300 seconds SGA Heap Dump Interval=3600 seconds
Last Dump Time=10/26/2008 23:46:03
Allocation request for: PX msg pool
Heap: c0000000de140fd8, size: 983136
******************************************************
HEAP DUMP heap name="large pool" desc=c0000000de140fd8
extent sz=0xfe0 alt=200 het=32767 rec=9 flg=-126 opc=0
parent=0000000000000000 owner=0000000000000000 nex=0000000000000000 xsz=0x1
====================
Process State Object
====================
----------------------------------------
SO: c0000000a4474f48, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=101, calls cur/top: c0000000b638a8a8/c0000000b638a8a8, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 163
"<SID>_p024_22175.trc" [Read only] 706 lines, 32832 characters
parent=0000000000000000 owner=0000000000000000 nex=0000000000000000 xsz=0x1
====================
Process State Object
====================
----------------------------------------
SO: c0000000a4474f48, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=101, calls cur/top: c0000000b638a8a8/c0000000b638a8a8, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 163
last post received-location: kxfprienq: slave
last process to post me: c0000000a445cee8 122 0
last post sent: 131896 0 162
last post sent-location: kxfprienq: QC
last process posted by me: c0000000a445cee8 122 0
(latch info) wait_event=0 bits=50
holding c0000000ae6e0ab8 Child parallel query alloc buffer level=6 child#=1
Location from where latch is held: kxfpbalo: allocate a buffer
Context saved from call: 13835058058893948848
state=busy
holding c0000000d74776b0 Child process queue reference level=4 child#=1760
Location from where latch is held: kxfprialo: addr process queue
Context saved from call: 13835058058220266736
state=busy
Process Group: DEFAULT, pseudo proc: c0000000a24473e8
O/S info: user: oracle, term: UNKNOWN, ospid: 22175
OSD pid info: Unix process pid: 22175, image: oracle@server1 (P024)
=========================
User Session State Object
=========================
----------------------------------------
SO: c0000000a45a39e0, type: 4, owner: c0000000a4474f48, flag: INIT/-/-/0x00
(session) trans: 0000000000000000, creator: c0000000a4474f48, flag: (48000041) USR/- BSY/-/-/-/-/-
DID: 0001-0065-00019BC1, short-term DID: 0000-0000-00000000
txn branch: 0000000000000000
oct: 3, prv: 0, sql: c0000000db646ad0, psql: 0000000000000000, user: 42/SA
O/S info: user: <MMM>, term: , ospid: 22175, machine: server1
program: oracle@server1 (P024)
application name: SQL*Plus, hash value=3669949024
last wait for 'latch free' blocking sess=0x0 seq=17 wait_time=347949
address=c0000000ae6e0ab8, number=c8, tries=0
temporary object counter: 0
...No current library cache object being loaded
...No instantiation object
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksm_4031_dump()+220 call ksdfmw()+960 000000000 ?
8 C000000000000916 ?
40000000028D4380 ?
000000000 ?
$cold_ksmasg()+112 call ksdfmw()+960 000654FFF ?
C0000000DE0C4BA0 ?
C0000000DE140FD8 ?
60000000004DE7D0 ?
60000000004DE934 ?
60000000004DD174 ?
kghnospc()+544 call ksdfmw()+960 000001000 ?
C0000000DE0C4BA0 ?
0000F0060 ? 0000F0060 ?
C0000000DE0C4BE8 ?
9FFFFFFFFFFFBCC0 ?
002002000 ? 000000000 ?
$cold_k
error while running dbms_stats ....
October 26, 2008 - 9pm Central time zone
Reviewer: Manoj from UK
Hi Tom,
Further, I tried to check again the degree in user_tables as follows:
SQL> select count(*) from user_tables where degree !=1;
select count(*) from user_tables where degree !=1
*
ERROR at line 1:
ORA-01722: invalid number
SQL>
SQL> select count(*) from user_tables where degree != '1';
COUNT(*)
----------
1074
SQL> select count(*) from user_tables;
COUNT(*)
----------
1074
SQL>
SQL> select distinct(degree) from user_tables;
DEGREE
----------
1
SQL>
Thanks a lot.
Kind regards.
Followup October 27, 2008 - 8am Central time zone:
ops$tkyte%ORA10GR2> select distinct '"' || degree || '"' from user_tables;
'"'||DEGREE|
------------
" 1"
degree has in this case leading blanks. It is not a number, it is a string, a formatted string at that.
and I just spotted it, your use of options with 'gather auto':
options
GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.
You probably meant to use gather stale.
error while running dbms_stats ....
October 27, 2008 - 7pm Central time zone
Reviewer: Manoj from UK
Hi Tom,
Many thanks for your time.
I will be trying the option gather stale next.
Further,
....ops$tkyte%ORA10GR2> select distinct '"' || degree || '"' from user_tables;
'"'||DEGREE|
------------
" 1"
a) Does these leading blanks causing the dbms_stat to go parallel, as table default degree value (
1) is not picked ?
I querried *other* database then the above in the problem, for values of degree and it also
results with leading spaces as above but NO error is thrown when number comparision is performed on
user_tables.
Output as follows:
SQL> select distinct '"' || degree || '"' from user_tables;
'"'||DEGREE|
------------
" 1"
SQL> select count(*) from user_tables where degree !=1;
COUNT(*)
----------
0
SQL> select count(*) from user_tables;
COUNT(*)
----------
1000
SQL>
So,
b) does it mean that degree in the above query has leading spaces but still, comparision with
number ( i.e. 1) succeeds.?
c) we were running anlayze table T compute... so far as dbms_stat is failing. Can we use dbms_stat
with stale option directly?
Also, We have 02 node RAC setup. which is running in Active-Passive ( in terms of client
connections) mode. i,e. All connections are directed to the Primary Instance always using client
TNS. So at any given point of time another instance is IDLE and is up & running.( no connection.).
So
d) Can i ran dbms_stats in another ( secondary - IDLE ) database Instance and see if it succeeds?
Your suggestion please?
Kind Regards,
Followup October 27, 2008 - 8pm Central time zone:
a) no - I was just pointing out why your queries above where not returning anything. that is all.
it went parallel because you said "do your auto thing, have at it, do what you feel like doing"
NEVER EVER NEVER EVER compare a string to a number. All it takes is one string that cannot be converted to get the error.
only compare strings to strings
dates to dates
numbers to numbers
and never any other combination.
b) no, it means the "where degree != 1" was really "where TO_NUMBER(degree) != 1" and you got lucky, the word DEFAULT did not appear
c) yes, you can use dbms_stats, yes you can use gather stale - if you understand what it means, what it does and if that satisfies your needs.
d) sure you can, but then it wouldn't be active passive - that is, expect to see a ton of interconnect traffic all of a sudden as many of the blocks will have to be shipped for the first time to the other node.
error while running dbms_stats ....
October 27, 2008 - 9pm Central time zone
Reviewer: Manoj from UK
Many Thanks Tom for enlightening me.
...c) yes, you can use dbms_stats, yes you can use gather stale - if you understand what it means,
what it does and if that satisfies your needs.
I have gathered the docs on the same and trying to go through cover to cover.
Kind Regards.
dbms_stats pkg issue after upgradation from 9i
December 23, 2008 - 12am Central time zone
Reviewer: A reader
Dear Tom,
We have upgraded our oracle applications database from 9i to oracle 10g but when i look at the dbms_stats package , i found
SQL> select dbms_stats.get_param('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
----------------------------------------------------------------------------------------------------
--------------------------------
FOR ALL COLUMNS SIZE 1
Even after upgradtion to 10g . As far as i know in 10g it should be FOR ALL COLUMNS SIZE AUTO. Can you please help me out in this case ??
Regards
Balakrishna.
Followup December 29, 2008 - 3pm Central time zone:
you can use set param to set it to whatever you want.
Details on statistics
January 5, 2009 - 11am Central time zone
Reviewer: A reader
We currently have a 120 GB database in production where we run full schema statistics every day and
that takes about 5 hours. We have a lot of IOT tables and we are gathering 100% stats on them every
day. I need some insights on how statistics work with DBMS_STATS package and how we can determine
how to better gather the statistics.
Thanks for your help.
Followup January 5, 2009 - 11am Central time zone:
... I need some insights on how
statistics work with DBMS_STATS package ...
well, dbms_stats gathers them - it doesn't really "interact" with them. I think you mean "with the optimizer", not "with the dbms_stats package"
do your tables change frequently? are they growing, shrinking, staying the same?
it could well be that most of them do not need any gathering after a while.
Why do you gather them every day? Why do you do full? version information is relevant too.
January 5, 2009 - 11am Central time zone
Reviewer: A reader
This system went live in July 2008. In the initial few weeks, that it went live we saw some issues
with performance of the front end. So we started doing statistics for the tables that were accessed
from the front end every day and then thought that we would just do the full schema statistics
daily to just make sure that we dont have any problems.
Some tables grow a lot every day and some stay the same.
Initially it started with the full schema stats job taking an hr or so and after we changed some
tables to IOT tables and started doing 100% on them the time taken has increased to 5 hours. That
is why we want to revisit our stats job and come up with something efficient.
Our database is Oracle 10.2.0.4.
Followup January 5, 2009 - 12pm Central time zone:
probably, for most of your segments - an estimate (1 to 10%) would be more than sufficient and gather_stale (only on tables that have grown/shrunk/modified a lot) would do.
you will want to *test* that - and if you want to understand 'how' statistics are used, Jonathan Lewis wrote a decent book on that - Cost Based Oracle Fundamentals
January 5, 2009 - 12pm Central time zone
Reviewer: A reader
Does gather_stale determine if the change in the table is more than a certain percentage then the
statistics will be gathered. Do you suggest that we gather table by table instead of doing schema
statistics ?
Followup January 5, 2009 - 1pm Central time zone:
gather stale uses the information in dba_tab_modifications to determine what segments are prime candidates for statistics.
you would typically use that with schema level gathering
Degree of parallelism not working
January 5, 2009 - 2pm Central time zone
Reviewer: Ashiq from Chennai, India
Hi Tom,
We've big table (size 100gig) and when we tried to gather stats with degree option - its not
gathering stats with child processes.
It's 32 CPU server
Below is my create table statement
CREATE TABLE SLS_ORDR_FACT
(
ACCT_REGN_CODE VARCHAR2(25 BYTE) NOT NULL,
SLS_ORDR_DOC_HDR_NUM_CODE VARCHAR2(25 BYTE) NOT NULL,
SLS_ORDR_DOC_LN_ITM_NUM_CODE VARCHAR2(25 BYTE) NOT NULL,
..
..
..
PARTITION US_FOOTWEAR_REPLEN_FORECAST VALUES (33)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 16M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION REMAINDER VALUES (DEFAULT)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 16M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
NOCOMPRESS
NOCACHE
PARALLEL ( DEGREE 4 INSTANCES 1 )
NOMONITORING
ENABLE ROW MOVEMENT;
CREATE UNIQUE INDEX PK_SLS_ORDR_FACT ON SLS_ORDR_FACT
(SLS_ORDR_DOC_HDR_NUM_CODE, SLS_ORDR_DOC_LN_ITM_NUM_CODE, KEY_CCD_DATE_DIM_ID, RJCT_RSN_CODE)
NOLOGGING
TABLESPACE DM_SMART_IDX
PCTFREE 1
INITRANS 13
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 16M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
aLTER TABLE SLS_ORDR_FACT ADD (
CONSTRAINT SOF_PK
PRIMARY KEY
(SLS_ORDR_DOC_HDR_NUM_CODE, SLS_ORDR_DOC_LN_ITM_NUM_CODE, KEY_CCD_DATE_DIM_ID, RJCT_RSN_CODE));
=============
####initialization parameters
parallel_automatic_tuning boolean FALSE
parallel_max_servers integer 960
parallel_min_percent integer 0
parallel_min_servers integer 37
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 3
recovery_parallelism integer 0
Followup January 5, 2009 - 2pm Central time zone:
you do not tell us what the dbms_stats call was?? and how you ascertain that it is not using parallel...
Degree of parallelism not working
January 5, 2009 - 2pm Central time zone
Reviewer: Ashiq from Chennai, India
This is my statement which I tried to execute
begin
DBMS_STATS.GATHER_TABLE_STATS
( ownname => 'DM_SMART'
, tabname => 'SLS_ORDR_FACT'
, partname => null -- partitions
, estimate_percent => 2
, degree =>8
, cascade => true -- does indexes
);
end;
/
Followup January 5, 2009 - 2pm Central time zone:
give me a create I can actually run - and tell us how you arrived at "it did not use parallel"
January 5, 2009 - 2pm Central time zone
Reviewer: Ashiq from Chennai, India
<code>Here it goes--
CREATE TABLE SLS_ORDR_FACT
(
ACCT_REGN_CODE VARCHAR2(25 BYTE) NOT NULL,
SLS_ORDR_DOC_HDR_NUM_CODE VARCHAR2(25 BYTE) NOT NULL,
SLS_ORDR_DOC_LN_ITM_NUM_CODE VARCHAR2(25 BYTE) NOT NULL,
KEY_CCD_DATE_DIM_ID NUMBER(38,2) NOT NULL,
RJCT_RSN_CODE VARCHAR2(25 BYTE) NOT NULL,
KEY_RJCT_RSN_DIM_ID NUMBER(38,2) NOT NULL,
LCL_CRNCY_CODE VARCHAR2(25 BYTE),
SLS_ORG_CODE VARCHAR2(25 BYTE),
DIV_CODE VARCHAR2(25 BYTE),
LFCYCL_CODE VARCHAR2(25 BYTE),
ORDR_ENTRY_MTHD_CODE VARCHAR2(25 BYTE),
ORDR_TYPE_CODE VARCHAR2(25 BYTE),
QLTY_TYPE_CODE VARCHAR2(25 BYTE),
REF_DOC_TYPE_CODE VARCHAR2(25 BYTE),
PRECEDE_DOC_HDR_NUM_CODE VARCHAR2(25 BYTE),
PRECEDE_DOC_LN_ITM_NUM_CODE VARCHAR2(25 BYTE),
CUST_ORDR_NUM_CODE VARCHAR2(25 BYTE),
CNFRMD_UNIT_QTY NUMBER(38,2),
CUST_RJCTD_UNIT_QTY NUMBER(38,2),
FXD_UNIT_QTY NUMBER(38,2),
ON_ORDR_UNIT_QTY NUMBER(38,2),
OPEN_UNIT_QTY NUMBER(38,2),
OPEN_TO_REF_UNIT_QTY NUMBER(38,2),
ORDR_ENTRY_UNIT_QTY NUMBER(38,2),
AUTH_FUTR_UNIT_QTY NUMBER(38,2),
OTHR_RJCTD_UNIT_QTY NUMBER(38,2),
RSRVD_UNIT_QTY NUMBER(38,2),
SCHDLD_FOR_DLVRY_UNIT_QTY NUMBER(38,2),
UNCNFRMD_UNIT_QTY NUMBER(38,2),
ORGNL_ORDR_ENTRY_UNIT_QTY NUMBER(38,2),
UOM_CNVRSN_FCTR NUMBER(38,2),
CNFRMD_AMT NUMBER(38,2),
CUST_RJCTD_AMT NUMBER(38,2),
FXD_AMT NUMBER(38,2),
ON_ORDR_AMT NUMBER(38,2),
OPEN_AMT NUMBER(38,2),
OPEN_TO_REF_AMT NUMBER(38,2),
ORDR_ENTRY_AMT NUMBER(38,2),
AUTH_FUTR_AMT NUMBER(38,2),
OTHR_RJCTD_AMT NUMBER(38,2),
RSRVD_AMT NUMBER(38,2),
SCHDLD_FOR_DLVRY_AMT NUMBER(38,2),
UNCNFRMD_AMT NUMBER(38,2),
ORGNL_ORDR_ENTRY_AMT NUMBER(38,2),
WHLSL_AMT NUMBER(38,2),
WHLSL_UNIT_PRC NUMBER(38,2),
TRL_CAT_CD VARCHAR2(25 BYTE),
GOAL_RFRNC_NUM_CODE VARCHAR2(25 BYTE),
KEY_ACCT_DEPT_DIM_ID NUMBER(38) NOT NULL,
KEY_ACCT_PRFL_DIM_ID NUMBER(38) NOT NULL,
KEY_ACCT_REGN_DIM_ID NUMBER(38) NOT NULL,
KEY_ACCT_SHPTO_DIM_ID NUMBER(38) NOT NULL,
KEY_ACCT_SLDT_DIM_ID NUMBER(38) NOT NULL,
KEY_BUSNSS_CCD_MNTH_DIM_ID NUMBER(38) NOT NULL,
KEY_BUSNSS_CRD_MNTH_DIM_ID NUMBER(38) NOT NULL,
KEY_BUSNSS_FRZ_CRD_MNTH_DIM_ID NUMBER(38) NOT NULL,
KEY_CCD_SESN_YEAR_DIM_ID NUMBER(38) NOT NULL,
KEY_CNCL_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_CRD_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_CRD_SESN_YEAR_DIM_ID NUMBER(38) NOT NULL,
KEY_DIV_DIM_ID NUMBER(38) NOT NULL,
KEY_FRZ_CRD_DIM_ID NUMBER(38) NOT NULL,
KEY_FRZ_CRD_SESN_YEAR_DIM_ID NUMBER(38) NOT NULL,
KEY_MATRL_DIM_ID NUMBER(38) NOT NULL,
KEY_ORDR_ENTRY_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_ORDR_MAINT_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_ORIGNL_CCD_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_ORIGNL_CRD_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_PRFRM_SLS_REP_DIM_ID NUMBER(38) NOT NULL,
KEY_MGMT_SLS_REP_DIM_ID NUMBER(38) NOT NULL,
KEY_RTRN_RSN_DIM_ID NUMBER(38) NOT NULL,
KEY_SLS_ORG_DIM_ID NUMBER(38) NOT NULL,
KEY_SLSMATRL_DIM_ID NUMBER(38) NOT NULL,
KEY_STNDRD_FLTR_DIM_ID NUMBER(38) NOT NULL,
KEY_AUTH_FUTR_DLNDT_DIM_ID NUMBER(38) NOT NULL,
ZZZ_CREATE_DTTM DATE DEFAULT SYSDATE,
ZZZ_UPDATE_DTTM DATE DEFAULT SYSDATE,
ZZZ_RCRD_CHNG_UID VARCHAR2(25 BYTE) DEFAULT USER,
NET_UNIT_PRC NUMBER(38,2),
USAGE_CODE VARCHAR2(10 BYTE),
DSCNT_PER_UNIT NUMBER,
TTL_DSCNT_AMT NUMBER,
KEY_SEASNL_SNPSHT_DT_DIM_ID NUMBER(38) DEFAULT 0,
SEASNL_SNPSHT_UNIT_QTY NUMBER(38) DEFAULT 0,
SEASNL_SNPSHT_AMT NUMBER(38,2) DEFAULT 0,
KEY_SSN_FRZ_CRD_DT_DIM_ID NUMBER(38) DEFAULT 0
)
TABLESPACE DM_SMART_TAB_LG
PCTUSED 0
PCTFREE 5
INITRANS 13
MAXTRANS 255
STORAGE (
PCTINCREASE 0
)
NOLOGGING
PARTITION BY LIST (KEY_STNDRD_FLTR_DIM_ID)
(
PARTITION US_REMAINDER VALUES (0)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 16M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION US_FOOTWEAR_FUTURES VALUES (1)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 16M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION US_FOOTWEAR_AT_ONCE VALUES (2)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 16M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION US_FOOTWEAR_B_GRADE VALUES (3)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 16M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION US_FOOTWEAR_CLOSEOUT VALUES (4)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 16M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION US_APPAREL_FUTURES VALUES (5)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 16M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION US_APPAREL_AT_ONCE VALUES (6)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
STORAGE (
INITIAL 16M
NEXT 16M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION US_APPAREL_B_GRADE VALUES (7)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
Followup January 5, 2009 - 4pm Central time zone:
a sample create table should be small - I don't need your storage clauses - in fact, I cannot use them. I don't have your tablespaces
please - small test case
and tell us how you "determined" what you determined.
January 5, 2009 - 3pm Central time zone
Reviewer: Ashiq from Chennai, India
Since its huge create table statment, can I send create table script directly to your mail id ?
Followup January 5, 2009 - 4pm Central time zone:
no, make it smaller
January 5, 2009 - 6pm Central time zone
Reviewer: Ashiq from Chennai, India
<code>I've removed storage parameters ---
CREATE TABLE SLS_ORDR_FACT
(
ACCT_REGN_CODE VARCHAR2(25 BYTE) NOT NULL,
SLS_ORDR_DOC_HDR_NUM_CODE VARCHAR2(25 BYTE) NOT NULL,
SLS_ORDR_DOC_LN_ITM_NUM_CODE VARCHAR2(25 BYTE) NOT NULL,
KEY_CCD_DATE_DIM_ID NUMBER(38,2) NOT NULL,
RJCT_RSN_CODE VARCHAR2(25 BYTE) NOT NULL,
KEY_RJCT_RSN_DIM_ID NUMBER(38,2) NOT NULL,
LCL_CRNCY_CODE VARCHAR2(25 BYTE),
SLS_ORG_CODE VARCHAR2(25 BYTE),
DIV_CODE VARCHAR2(25 BYTE),
LFCYCL_CODE VARCHAR2(25 BYTE),
ORDR_ENTRY_MTHD_CODE VARCHAR2(25 BYTE),
ORDR_TYPE_CODE VARCHAR2(25 BYTE),
QLTY_TYPE_CODE VARCHAR2(25 BYTE),
REF_DOC_TYPE_CODE VARCHAR2(25 BYTE),
PRECEDE_DOC_HDR_NUM_CODE VARCHAR2(25 BYTE),
PRECEDE_DOC_LN_ITM_NUM_CODE VARCHAR2(25 BYTE),
CUST_ORDR_NUM_CODE VARCHAR2(25 BYTE),
CNFRMD_UNIT_QTY NUMBER(38,2),
CUST_RJCTD_UNIT_QTY NUMBER(38,2),
FXD_UNIT_QTY NUMBER(38,2),
ON_ORDR_UNIT_QTY NUMBER(38,2),
OPEN_UNIT_QTY NUMBER(38,2),
OPEN_TO_REF_UNIT_QTY NUMBER(38,2),
ORDR_ENTRY_UNIT_QTY NUMBER(38,2),
AUTH_FUTR_UNIT_QTY NUMBER(38,2),
OTHR_RJCTD_UNIT_QTY NUMBER(38,2),
RSRVD_UNIT_QTY NUMBER(38,2),
SCHDLD_FOR_DLVRY_UNIT_QTY NUMBER(38,2),
UNCNFRMD_UNIT_QTY NUMBER(38,2),
ORGNL_ORDR_ENTRY_UNIT_QTY NUMBER(38,2),
UOM_CNVRSN_FCTR NUMBER(38,2),
CNFRMD_AMT NUMBER(38,2),
CUST_RJCTD_AMT NUMBER(38,2),
FXD_AMT NUMBER(38,2),
ON_ORDR_AMT NUMBER(38,2),
OPEN_AMT NUMBER(38,2),
OPEN_TO_REF_AMT NUMBER(38,2),
ORDR_ENTRY_AMT NUMBER(38,2),
AUTH_FUTR_AMT NUMBER(38,2),
OTHR_RJCTD_AMT NUMBER(38,2),
RSRVD_AMT NUMBER(38,2),
SCHDLD_FOR_DLVRY_AMT NUMBER(38,2),
UNCNFRMD_AMT NUMBER(38,2),
ORGNL_ORDR_ENTRY_AMT NUMBER(38,2),
WHLSL_AMT NUMBER(38,2),
WHLSL_UNIT_PRC NUMBER(38,2),
TRL_CAT_CD VARCHAR2(25 BYTE),
GOAL_RFRNC_NUM_CODE VARCHAR2(25 BYTE),
KEY_ACCT_DEPT_DIM_ID NUMBER(38) NOT NULL,
KEY_ACCT_PRFL_DIM_ID NUMBER(38) NOT NULL,
KEY_ACCT_REGN_DIM_ID NUMBER(38) NOT NULL,
KEY_ACCT_SHPTO_DIM_ID NUMBER(38) NOT NULL,
KEY_ACCT_SLDT_DIM_ID NUMBER(38) NOT NULL,
KEY_BUSNSS_CCD_MNTH_DIM_ID NUMBER(38) NOT NULL,
KEY_BUSNSS_CRD_MNTH_DIM_ID NUMBER(38) NOT NULL,
KEY_BUSNSS_FRZ_CRD_MNTH_DIM_ID NUMBER(38) NOT NULL,
KEY_CCD_SESN_YEAR_DIM_ID NUMBER(38) NOT NULL,
KEY_CNCL_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_CRD_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_CRD_SESN_YEAR_DIM_ID NUMBER(38) NOT NULL,
KEY_DIV_DIM_ID NUMBER(38) NOT NULL,
KEY_FRZ_CRD_DIM_ID NUMBER(38) NOT NULL,
KEY_FRZ_CRD_SESN_YEAR_DIM_ID NUMBER(38) NOT NULL,
KEY_MATRL_DIM_ID NUMBER(38) NOT NULL,
KEY_ORDR_ENTRY_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_ORDR_MAINT_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_ORIGNL_CCD_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_ORIGNL_CRD_DATE_DIM_ID NUMBER(38) NOT NULL,
KEY_PRFRM_SLS_REP_DIM_ID NUMBER(38) NOT NULL,
KEY_MGMT_SLS_REP_DIM_ID NUMBER(38) NOT NULL,
KEY_RTRN_RSN_DIM_ID NUMBER(38) NOT NULL,
KEY_SLS_ORG_DIM_ID NUMBER(38) NOT NULL,
KEY_SLSMATRL_DIM_ID NUMBER(38) NOT NULL,
KEY_STNDRD_FLTR_DIM_ID NUMBER(38) NOT NULL,
KEY_AUTH_FUTR_DLNDT_DIM_ID NUMBER(38) NOT NULL,
ZZZ_CREATE_DTTM DATE DEFAULT SYSDATE,
ZZZ_UPDATE_DTTM DATE DEFAULT SYSDATE,
ZZZ_RCRD_CHNG_UID VARCHAR2(25 BYTE) DEFAULT USER,
NET_UNIT_PRC NUMBER(38,2),
USAGE_CODE VARCHAR2(10 BYTE),
DSCNT_PER_UNIT NUMBER,
TTL_DSCNT_AMT NUMBER,
KEY_SEASNL_SNPSHT_DT_DIM_ID NUMBER(38) DEFAULT 0,
SEASNL_SNPSHT_UNIT_QTY NUMBER(38) DEFAULT 0,
SEASNL_SNPSHT_AMT NUMBER(38,2) DEFAULT 0,
KEY_SSN_FRZ_CRD_DT_DIM_ID NUMBER(38) DEFAULT 0
)
TABLESPACE DM_SMART_TAB_LG
PCTUSED 0
PCTFREE 5
INITRANS 13
MAXTRANS 255
STORAGE (
PCTINCREASE 0
)
NOLOGGING
PARTITION BY LIST (KEY_STNDRD_FLTR_DIM_ID)
(
PARTITION US_REMAINDER VALUES (0)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_FOOTWEAR_FUTURES VALUES (1)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_FOOTWEAR_AT_ONCE VALUES (2)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_FOOTWEAR_B_GRADE VALUES (3)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_FOOTWEAR_CLOSEOUT VALUES (4)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_APPAREL_FUTURES VALUES (5)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_APPAREL_AT_ONCE VALUES (6)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_APPAREL_B_GRADE VALUES (7)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_APPAREL_CLOSEOUT VALUES (8)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_EQUIPMENT_FUTURES VALUES (9)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_EQUIPMENT_AT_ONCE VALUES (10)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_EQUIPMENT_B_GRADE VALUES (11)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_EQUIPMENT_CLOSEOUT VALUES (12)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_FOOTWEAR_REPLEN VALUES (13)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_APPAREL_REPLEN VALUES (14)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_EQUIPMENT_REPLEN VALUES (15)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_APPAREL_REPLEN_FORECAST VALUES (31)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION US_EQUIPMENT_REPLEN_FORECAST VALUES (32)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCTFREE 5
INITRANS 13
MAXTRANS 255
,
PARTITION CANADA_REMAINDER VALUES (100)
NOLOGGING
NOCOMPRESS
TABLESPACE DM_SMART_TAB_SOF
PCT
Followup January 5, 2009 - 6pm Central time zone:
but you left on all of the other "junk"
and - and most importantly - I do not see the answer to a question I've asked over and over - how did you come to your conclusion?
Here is my much smaller example showing this "working"
ops$tkyte%ORA10GR2> CREATE TABLE SLS_ORDR_FACT
2 (
3 ACCT_REGN_CODE VARCHAR2(25 BYTE) NOT NULL,
4 SLS_ORDR_DOC_HDR_NUM_CODE VARCHAR2(25 BYTE) NOT NULL,
5 SLS_ORDR_DOC_LN_ITM_NUM_CODE VARCHAR2(25 BYTE) NOT NULL,
6 KEY_CCD_DATE_DIM_ID NUMBER(38,2) NOT NULL,
7 RJCT_RSN_CODE VARCHAR2(25 BYTE) NOT NULL,
8 KEY_RJCT_RSN_DIM_ID NUMBER(38,2) NOT NULL,
9 LCL_CRNCY_CODE VARCHAR2(25 BYTE),
10 SLS_ORG_CODE VARCHAR2(25 BYTE),
11 DIV_CODE VARCHAR2(25 BYTE),
12 ZZZ_CREATE_DTTM DATE DEFAULT SYSDATE,
13 ZZZ_UPDATE_DTTM DATE DEFAULT SYSDATE,
14 ZZZ_RCRD_CHNG_UID VARCHAR2(25 BYTE) DEFAULT USER,
15 NET_UNIT_PRC NUMBER(38,2),
16 KEY_STNDRD_FLTR_DIM_ID NUMBER(3) NOT NULL,
17 USAGE_CODE VARCHAR2(10 BYTE),
18 DSCNT_PER_UNIT NUMBER,
19 TTL_DSCNT_AMT NUMBER,
20 KEY_SEASNL_SNPSHT_DT_DIM_ID NUMBER(38) DEFAULT 0,
21 SEASNL_SNPSHT_UNIT_QTY NUMBER(38) DEFAULT 0,
22 SEASNL_SNPSHT_AMT NUMBER(38,2) DEFAULT 0,
23 KEY_SSN_FRZ_CRD_DT_DIM_ID NUMBER(38) DEFAULT 0
24 )
25 PARTITION BY LIST (KEY_STNDRD_FLTR_DIM_ID)
26 (
27 PARTITION US_REMAINDER VALUES (0),
28 PARTITION US_FOOTWEAR_FUTURES VALUES (1),
29 PARTITION US_FOOTWEAR_AT_ONCE VALUES (2),
30 PARTITION US_FOOTWEAR_B_GRADE VALUES (3),
31 PARTITION US_FOOTWEAR_CLOSEOUT VALUES (4),
32 PARTITION US_APPAREL_FUTURES VALUES (5),
33 PARTITION US_APPAREL_AT_ONCE VALUES (6),
34 PARTITION US_APPAREL_B_GRADE VALUES (7),
35 PARTITION US_APPAREL_CLOSEOUT VALUES (8),
36 PARTITION US_EQUIPMENT_FUTURES VALUES (9),
37 PARTITION US_EQUIPMENT_AT_ONCE VALUES (10),
38 PARTITION US_EQUIPMENT_B_GRADE VALUES (11),
39 PARTITION US_EQUIPMENT_CLOSEOUT VALUES (12),
40 PARTITION US_FOOTWEAR_REPLEN VALUES (13),
41 PARTITION US_APPAREL_REPLEN VALUES (14),
42 PARTITION US_EQUIPMENT_REPLEN VALUES (15),
43 PARTITION US_APPAREL_REPLEN_FORECAST VALUES (31),
44 PARTITION US_EQUIPMENT_REPLEN_FORECAST VALUES (32),
45 PARTITION CANADA_REMAINDER VALUES (100),
46 PARTITION CANADA_FOOTWEAR_FUTURES VALUES (101),
47 PARTITION CANADA_FOOTWEAR_AT_ONCE VALUES (102),
48 PARTITION CANADA_FOOTWEAR_B_GRADE VALUES (103),
49 PARTITION CANADA_FOOTWEAR_CLOSEOUT VALUES (104),
50 PARTITION CANADA_APPAREL_FUTURES VALUES (105),
51 PARTITION CANADA_APPAREL_AT_ONCE VALUES (106),
52 PARTITION CANADA_APPAREL_B_GRADE VALUES (107),
53 PARTITION CANADA_APPAREL_CLOSEOUT VALUES (108),
54 PARTITION CANADA_EQUIPMENT_FUTURES VALUES (109),
55 PARTITION CANADA_EQUIPMENT_AT_ONCE VALUES (110),
56 PARTITION CANADA_EQUIPMENT_B_GRADE VALUES (111),
57 PARTITION CANADA_EQUIPMENT_CLOSEOUT VALUES (112),
58 PARTITION CANADA_FOOTWEAR_REPLEN VALUES (113),
59 PARTITION CANADA_APPAREL_REPLEN VALUES (114),
60 PARTITION CANADA_EQUIPMENT_REPLEN VALUES (115),
61 PARTITION EMEA_REMAINDER VALUES (200),
62 PARTITION EMEA_FOOTWEAR_FUTURES VALUES (201),
63 PARTITION EMEA_FOOTWEAR_AT_ONCE VALUES (202),
64 PARTITION EMEA_FOOTWEAR_B_GRADE VALUES (203),
65 PARTITION EMEA_FOOTWEAR_CLOSEOUT VALUES (204),
66 PARTITION EMEA_FOOTWEAR_REPLEN VALUES (205),
67 PARTITION EMEA_FOOTWEAR_OTHER VALUES (206),
68 PARTITION EMEA_APPAREL_FUTURES VALUES (207),
69 PARTITION EMEA_APPAREL_AT_ONCE VALUES (208),
70 PARTITION EMEA_APPAREL_B_GRADE VALUES (209),
71 PARTITION EMEA_APPAREL_CLOSEOUT VALUES (210),
72 PARTITION EMEA_APPAREL_REPLEN VALUES (211),
73 PARTITION EMEA_APPAREL_OTHER VALUES (212),
74 PARTITION EMEA_EQUIPMENT_FUTURES VALUES (213),
75 PARTITION EMEA_EQUIPMENT_AT_ONCE VALUES (214),
76 PARTITION EMEA_EQUIPMENT_B_GRADE VALUES (215),
77 PARTITION EMEA_EQUIPMENT_CLOSEOUT VALUES (216),
78 PARTITION EMEA_EQUIPMENT_REPLEN VALUES (217),
79 PARTITION EMEA_EQUIPMENT_OTHER VALUES (218),
80 PARTITION US_FOOTWEAR_REPLEN_FORECAST VALUES (33),
81 PARTITION REMAINDER VALUES (DEFAULT)
82 )
83 PARALLEL ( DEGREE 4 INSTANCES 1 )
84 NOMONITORING
85 ENABLE ROW MOVEMENT;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE INDEX PK_SLS_ORDR_FACT ON SLS_ORDR_FACT
2 (SLS_ORDR_DOC_HDR_NUM_CODE, SLS_ORDR_DOC_LN_ITM_NUM_CODE, KEY_CCD_DATE_DIM_ID, RJCT_RSN_CODE)
NOLOGGING NOPARALLEL;
Index created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec gen_data('SLS_ORDR_FACT', 100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat parallel
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
--------------------------------------------- ----------
DBWR parallel query checkpoint buffers writte 0
n
queries parallelized 0
DML statements parallelized 0
DDL statements parallelized 0
DFO trees parallelized 0
Parallel operations not downgraded 0
Parallel operations downgraded to serial 0
Parallel operations downgraded 75 to 99 pct 0
Parallel operations downgraded 50 to 75 pct 0
Parallel operations downgraded 25 to 50 pct 0
Parallel operations downgraded 1 to 25 pct 0
11 rows selected.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
2 DBMS_STATS.GATHER_TABLE_STATS
3 ( ownname => user
4 , tabname => 'SLS_ORDR_FACT'
5 , partname => null -- partitions
6 , estimate_percent => 2
7 , degree =>8
8 , cascade => true -- does indexes
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @mystat parallel
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
--------------------------------------------- ----------
DBWR parallel query checkpoint buffers writte 0
n
queries parallelized 5
DML statements parallelized 0
DDL statements parallelized 0
DFO trees parallelized 5
Parallel operations not downgraded 5
Parallel operations downgraded to serial 0
Parallel operations downgraded 75 to 99 pct 0
Parallel operations downgraded 50 to 75 pct 0
Parallel operations downgraded 25 to 50 pct 0
Parallel operations downgraded 1 to 25 pct 0
11 rows selected.
Gather Stats
March 6, 2009 - 9am Central time zone
Reviewer: A reader
Hi Tom,
1) With number of local bitmap indexes on the partitioned table, can we gather stats in parallel?
2) If we exchange partition for history table with a new table with current month having stats, will stats of new table will be reflected in history partition table after exchange partition?
Regards,
Followup March 6, 2009 - 11am Central time zone:
1) yes
2) did you gather them? the stats should "swap" on the partition, but global statistics might be in need of some maintenance
the best way of statistcs gathering after ETL
November 3, 2009 - 7am Central time zone
Reviewer: Fredx Mercy from Croatia
Hi Tom,
You are my last chance to accelerate statistics gathering.
Here is my problem:
Let say I have one large fact table partitioned by range. ETL procedure change only couple (two or
three) from the total of 48 partitions (not necessarily last three partitions).
What is the fastest way to get table statistics?
2. Part of ETL procedure is also local index rebuilding. When rebuilding indexes Oracle
automatically gather index statistics (probably local and global-->correct me if I am wrong). If
that is true, can I use the option cascade => false when gather table statistics (Oracle Manuals
PL/SQL Packages and Types reference claim that index gathering is not parallelized so that way It
would be faster if I use degree => 4) rather then cascade => true or DBMS_STATS.AUTO_CASCADE?
3. Is it important to have histograms on columns, or is it better/faster way to collect table
statistics with method_opt => ‘FOR ALL COLUMNS SIZE 1’ insted of ‘FOR ALL COLUMNS SIZE AUTO’ whicj
is the default value?
4. I’ve noticed that if I let Oracle to decide sampling size, it takes much longer to finish
statistics gathering that when I explicitly determine sample size (for example estimate_percent =>
2 instead of
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE). Is it safe to use my estimate size instead of
Oracle’s?
5. What does the option granularity => ‘AUTO’ mean? In PL/SQL Packages and Types reference I can
only find that ‘AUTO’ determines the granularity based on the partitioning. What does that mean? Is
it better to use granularity => ‘ALL’ option instead.
Thanks in advance for your answers.
Fredx
Followup November 9, 2009 - 2pm Central time zone:
2) depends on how you gather the index stats. You are hopefully only rebuilding the local index partitions that NEED IT, not all - so it would not gather global stats (since we don't read all of that), but it might be able to update them (infer them).
if you rebuild or create the index, the statistics are computed and you need not cascade=>true.
3) you tell me - only you know. Is your data skewed? If it is, do you use those columns in your where clause often? Only you know.
the default - depends on version and what changes you've made, dbms_stats has a get_param procedure to let you know the current defaults
4) again, only you know. Are the statistics better than good enough when you go with 2%? How's it been working?
5) auto granularity is global and partition level.
Note in the below the last two gathers and see the timestamps that show what levels were gathered...
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
10 PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
11 )
12 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t select to_date( '01-jan-2007' ) + mod(rownum, 2*365 ), rownum,
'x' from all_objects;
50316 rows created.
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', granularity => 'AUTO' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select 'table', table_name, num_rows, last_analyzed from user_tables where
table_name = 'T'
2 union all
3 select 'part', partition_name, num_rows, last_analyzed from user_tab_partitions where
table_name = 'T';
'TABL TABLE_NAME NUM_ROWS LAST_ANALYZED
----- ------------------------------ ---------- --------------------
table T 50316 09-nov-2009 15:39:38
part PART1 24937 09-nov-2009 15:39:38
part PART2 25237 09-nov-2009 15:39:38
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_lock.sleep(5);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> delete from t where mod(x,2) = 0 and dt >= to_date('01-jan-2008');
12532 rows deleted.
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', granularity => 'partition',
partname => 'part2' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select 'table', table_name, num_rows, last_analyzed from user_tables where
table_name = 'T'
2 union all
3 select 'part', partition_name, num_rows, last_analyzed from user_tab_partitions where
table_name = 'T';
'TABL TABLE_NAME NUM_ROWS LAST_ANALYZED
----- ------------------------------ ---------- --------------------
table T 50316 09-nov-2009 15:39:38
part PART1 24937 09-nov-2009 15:39:38
part PART2 12600 09-nov-2009 15:39:43
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_lock.sleep(5);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', granularity => 'auto', partname
=> 'part2' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select 'table', table_name, num_rows, last_analyzed from user_tables where
table_name = 'T'
2 union all
3 select 'part', partition_name, num_rows, last_analyzed from user_tab_partitions where
table_name = 'T';
'TABL TABLE_NAME NUM_ROWS LAST_ANALYZED
----- ------------------------------ ---------- --------------------
table T 37983 09-nov-2009 15:39:49
part PART1 24937 09-nov-2009 15:39:38
part PART2 12600 09-nov-2009 15:39:49
|