What does CONFIDENCE mean ?
Anton, September 03, 2002 - 10:40 pm UTC
Hi Tom.
What does the CONFIDENCE figure listed by LIST_EMPTY mean ? I've searched in the 8i DBMS_STATS documntation but it says that the CONFIDENCE column is not used.
Thanks.
September 04, 2002 - 7:30 am UTC
I guess it means nothing then, if it says "it is not used"
Why or why not get full statistics
Juan Carlos Reyes Pacheco, March 19, 2003 - 3:03 pm UTC
Hi Tom
I was using
begin
DBMS_UTILITY.Analyze_Database( 'COMPUTE' );
end;
until I start to read several answers about this so I have 3 questions, please
1. How do I get full statistics for all objects in the database.
2. Is possible to get full statistis for all objects in the database only for those that not has statistics or has statistics calculated more than one month ago.
3. Which real advantage you get getting full statistis, is better to use a sample, which size to be as useful as full statistics?
Thanks.
March 19, 2003 - 4:00 pm UTC
don't do that. that is there from 7.3
dbms_stats is the tool to use.
1) read up on DBMS_STATS - gather_database_stats
2) read up on ALTER TABLE MONITORING and the gather stale option of dbms_stats. It doesn't make sense to gather stats based on time -- it makes sense to gather stats based on percentage of changes to the table -- we can do that.
3) if the tables are very large, random samples work well. if the tables are not very large, random samples can be very skewed. large is many gigabytes and above.
I am sorry but that does not work
A reader, March 19, 2003 - 6:06 pm UTC
2) read up on ALTER TABLE MONITORING and the gather stale option of dbms_stats.
It doesn't make sense to gather stats based on time -- it makes sense to gather
stats based on percentage of changes to the table -- we can do that.
that unfortunately does not work until Oracle 9.2.0
A reader, March 20, 2003 - 8:09 am UTC
Thanks Tom :) nice answer
check bug 1890016
A reader, March 20, 2003 - 8:17 am UTC
well I am saying it´s not working because I tried to implement on Sun Solaris and Oracle 8.1.7.4 and it didnt work, after that Meatalink confirmed my fears... another bug fixed in 9.2.0 and seems like they dont want to release a good dbms_stats package to fix this in 8.1.7 (yes it´s a PL/SQL problem and not any kernel or funky bug)
March 20, 2003 - 9:13 am UTC
You know -- when you add a little detail, you get a little more.
umm, simple workaround -- specify the granularity parameter. (that is mentioned right there in the report you read)
...
Rediscovery Information:
You will see this bug when using dbms_stats.gather_xxx_stats and :
1. An invalid granularity is supplied
2. The GATHER STALE or GATHER EMPTY options are used
3. The procedure call does not seem to work
.
Workaround:
Correct the granularity parameter
......
another simple workaround -- list stale, gather (hey, thats what is above isn't it?)
Also, that is 9.0.2 which is 9iR1, not release 2.
<b>but in any case, I believe your test was flawed since this is in fact corrected in 8174</b>
Maybe you either
o did not wait the requisite 3 hours or so for the tab modifications view to be updated
o did not shutdown normal and restart to test the behavior.
In 8i, there is a 3 hour time delay -- the table isn't marked stale for at least 3 hours (in 9i, running gather stale forces the modifications view to be updated first, changing this behavior).
Anyway, here is a test that shows -- yes in fact this does work:
ops$tkyte@ORA817DEV> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA817DEV> alter table emp monitoring;
Table altered.
ops$tkyte@ORA817DEV> analyze table emp compute statistics;
Table analyzed.
ops$tkyte@ORA817DEV> select table_name, num_rows, blocks, avg_row_len
2 from user_tables
3 where table_name = 'EMP';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
EMP 14 1 40
<b>Now, lets modify emp:</b>
ops$tkyte@ORA817DEV> insert into emp select * from emp;
14 rows created.
ops$tkyte@ORA817DEV> /
28 rows created.
ops$tkyte@ORA817DEV> /
56 rows created.
ops$tkyte@ORA817DEV> /
112 rows created.
ops$tkyte@ORA817DEV> /
224 rows created.
ops$tkyte@ORA817DEV> /
448 rows created.
ops$tkyte@ORA817DEV> /
896 rows created.
ops$tkyte@ORA817DEV> commit;
Commit complete.
<b>and since I didn't feel like waiting 3 hours, I simulated 3 hours by bouncing:</b>
ops$tkyte@ORA817DEV> connect / as sysdba
Connected.
ops$tkyte@ORA817DEV> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ops$tkyte@ORA817DEV> startup
ORACLE instance started.
Total System Global Area 31965344 bytes
Fixed Size 73888 bytes
Variable Size 26796032 bytes
Database Buffers 4915200 bytes
Redo Buffers 180224 bytes
Database mounted.
Database opened.
ops$tkyte@ORA817DEV> connect /
Connected.
ops$tkyte@ORA817DEV> select * from sys.dba_tab_modifications;
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
INSERTS UPDATES DELETES TIMESTAMP TRU
---------- ---------- ---------- --------- ---
OPS$TKYTE EMP
1778 0 0 20-MAR-03 NO
<b>table appears to need to be analyzed right...</b>
ops$tkyte@ORA817DEV> exec dbms_stats.gather_schema_stats( user, options => 'gather stale');
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> select table_name, num_rows, blocks, avg_row_len
2 from user_tables
3 where table_name = 'EMP';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
EMP 1792 15 37
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select * from sys.dba_tab_modifications;
no rows selected
<b>and hey -- it was! now, if you run this and if it was not -- just specify granularity => 'DEFAULT' and it'll be..</b>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
<b>as 8174 is the terminal release of 8i, is it the only currently supported release level..</b>
Why some sys statics are not updated
Juan Carlos Reyes, March 20, 2003 - 9:40 am UTC
Hi Tom I ran
DBMS_STATS.GATHER_DATABASE_STATS(); connected as sys, the only user connected.
And I had some tables in sys with last analyzed in null and others were not analyzed
Then I run
DBMS_STATS.GATHER_SCHEMA_STATS( 'SYS' );
Then I has some (less than before ) with last analyzed in null, and others tables without updated statics (last statics dic-3-2002)
ATEMPTAB$,MAP_OBJECT,ODCI_SECOBJ$,
ODCI_WARNINGS$,LOGMNRT_SEED$,LOGMNRT_DICTIONARY$,
LOGMNRT_OBJ$,LOGMNRT_TAB$,LOGMNRT_COL$,
LOGMNRT_ATTRCOL$,LOGMNRT_TS$,LOGMNRT_IND$,LOGMNRT_USER$,
Questions.
1. Why I has to run DBMS_STATS.GATHER_SCHEMA_STATS( 'SYS' ); to get some additional tables analyzed for first time?
2. Why some tables statistics aren't update.
Thanks Tom
March 20, 2003 - 10:42 am UTC
first -- it is not recommended to gather stats on sys as yet. In 9i it is supported, in 8i, not so.
SYS is special, SYS is magical (don't run stuff as SYS, please -- create your OWN database account and use that).
some sys tables are skipped purposefully, analyzing them could lead to certain issues. sys is special.
Thanks Tom
A reader, March 20, 2003 - 11:01 am UTC
instace failure and monitoring
A reader, March 20, 2003 - 11:10 am UTC
Hi
I wonder, if we use gather stale option what happen if instance fails? I guess we lost all stale statistics stored in *_tab_modifications (SGA) right? We have to run again the base analyze (for all monitored tables) again right?
March 20, 2003 - 11:23 am UTC
it is flushed every 3 hours. So, no, not really.
so at most
A reader, March 20, 2003 - 11:25 am UTC
Hi
So at most we will lose 3 hours statstics, am I correct :?
March 20, 2003 - 12:05 pm UTC
3 hours of observed changes..
only one question more DBMS_STATS.GATHER_SYSTEM_STATS
Juan Carlos Reyes P, March 20, 2003 - 11:46 am UTC
Hi Tom, I had been looking for more about information about
DBMS_STATS.GATHER_SYSTEM_STATS, other than simple reference
But I can't get.
I would like to askyou one question more.
As I read DBMS_STATS.GATHER_SYSTEM_STATS allows you get the CUP and IO conditions in specifict periods of time. Then reload the proper conditions through a job.
how important is to get and set DBMS_STATS.GATHER_SYSTEM_STATS in an OLTP and in a DSS.
I had seen how recalculating statistics a query that took 30mn took only 5mn. Is DBMS_STATS.GATHER_SYSTEM_STATS really as important to set? you can get that kind of improvement, something you can't skip?
I had tried to create two scenarios but I don't see the difference, could you please illustrate what savings you can get.
March 20, 2003 - 12:14 pm UTC
You need a really busy system to see the difference. (or at least a busy system then a not so busy system or a system that does OLTP by day and batch by night)
Those are the conditions under which this becomes useful. On a single user test machine, it'll be really hard to simulate.
Thanks
A reader, March 20, 2003 - 12:38 pm UTC
Any harm with MONITORING
Bob D., March 20, 2003 - 2:34 pm UTC
Is there any reason NOT to use the MONITORING option ???
Unless you always need the very latest stats generated
all the time I don't see why all my tables shouldn't
have this option on. Thanks.
March 20, 2003 - 3:19 pm UTC
neither do i. It is very low bandwidth.
calculate DBMS_STATS.GATHER_SYSTEM_STATS only to help CBO
Juan Carlos reyesp, March 20, 2003 - 5:47 pm UTC
Hi Tom, one last question.
What does DBMS_STATS.GATHER_SYSTEM_STATS do really?
I think make sense to execute it only to get it update some statistics about IO and CPU use, to help CBO. Even if you don't need to switch between distinct saved systemstats.
I'm right or not?
In that case which is the most advisable way to execute it?
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'interval', interval => 60);
END;
/
In a periodo of average cpu and io use?
March 20, 2003 - 6:33 pm UTC
I just don't have enough real world experience with it as yet to give you best practices -- sorry.
About the bug...
Alex Daher, March 21, 2003 - 9:27 am UTC
Well, I am using monitoring/gather stale stats on a 8.1.7.4 database at Sun Solaris when it's working pretty fine... much better then gathering ALL stats every time!
I have a daily job the calls dbms_stats.
Why do the exactly same work twice??? :-)
About the bug...
Alex Daher, March 21, 2003 - 9:28 am UTC
Well, I am using monitoring/gather stale stats on a 8.1.7.4 database at Sun
Solaris AND it's working pretty fine... much better then gathering ALL stats
every time!
I have a daily job THAT calls dbms_stats.
Why do the exactly same work twice??? :-)
GATHER_SYSTEM_STATS
PINGU, May 09, 2003 - 3:49 am UTC
Hi
Oracle highly recommends running GATHER_SYSTEM_STATS so CBO can use a better execution plan however I really dont see the use of this procedure! I mean if my server is heavily loaded (only loaded not dead!) 24 hours per day, durng day time users and night times tons of batch running in our e10000, what´s the point of using this procedure, I mean whats the point of having one statistics in the morning and one during night? I think it will just mess up even more to CBO no?
Anyone used this in production?
May 09, 2003 - 1:24 pm UTC
thousands of people use this in production every day.
your question does not compute -- not sure what you are asking.
for you -- sounds like you want to "monitor" tables (alter table T monitoring) and gather_stale stats from time to time.
Ok, STALE is very usefull, but...
Bruno Paquet, July 10, 2003 - 12:22 pm UTC
i collect table stats and play with the SIZE in method_opt for correct use of indexes.
Do i simply execute the DBMS_STATS with the SIZE for every table and after when i will execute DBMS_STATS for the schema with STALE, do Oracle keep my SIZE option?
July 10, 2003 - 1:31 pm UTC
you would use list stale to find the stale objects and then invoke dbms_stats with customize stats options (perhaps table driven) in order to do that.
what about other statistics in ANALYZE
J, September 25, 2003 - 10:15 am UTC
when it is the direction to use DBMS_STATS package, what about the statistics available in regular analyze command but not in dbms_stats yet, say, chained_row, etc. How should we use these two efficiently to avoid duplicate efforts? in reality, how should we schedule to gather statisics in:
GATHER_DATABASE_STATS
GATHER_INDEX_STATS (shouldn't this gathered in schema stats?)
GATHER_SCHEMA_STATS
GATHER_SYSTEM_STATS
GATHER_TABLE_STATS
and for table with long values or frequent DML operation, do analyze on table?
as of now, we do weekly analyze on schema level and do daily analyze on new daily partition tables. we plan to use DBMS_STATS but still confuse on what we will get from gathering system statistics.
thanks!
September 25, 2003 - 11:21 pm UTC
chained rows is not a statistic. the optimizer does not use this piece of data.
dbms_stats gathers that which the optimizer uses. anything it does not use, it does not gather.
Regarding GATHER_SYSTEM_STATS
Matt, December 01, 2003 - 2:12 am UTC
I was told by a reputable source that the use of this procedure negates the need to alter the optimizer_index_cost_adj and optimizer_index_caching parameters. I've only just started playing with this feature. Can you confirm or deny that the generated AUX_STATS$ values are used in some way used instead of these parameters?
Can you can think up a suitable test to prove/disprove the above that doesn't involve a 10053 trace? If so, let me know and I will set it up.
Best Regards,
December 01, 2003 - 7:08 am UTC
does not negate but can reduce (as a side effect)
If you look at the definitions of the optimizer_* settings, they are somewhat orthogonal to the system stats.
optimizer_* tells oracle what to expect to find in the buffer cache.
system stats tells oracle the cost of doing single block IO, multiblock IO and how fast the machine in general is.
it is not that they are used INSTEAD OF, they can be used IN CONJUNCTION WITH.
Here is the test case showing this. test case shows
a) plan with default optimizer* and no system stats
b) different plan with some system stats forced onto the system
c) yet a 3rd plan with both optimizer* and system stats in place
ops$tkyte@ORA9IR2> create table t1
2 as
3 select mod(rownum,1000) id, rpad('x',300,'x') data
4 from all_objects
5 where rownum <= 5*1000;
Table created.
ops$tkyte@ORA9IR2> create table t2
2 as
3 select rownum id, rpad('x',300,'x') data
4 from all_objects
5 where rownum <= 1000;
Table created.
ops$tkyte@ORA9IR2> create index t1_idx on t1(id);
Index created.
ops$tkyte@ORA9IR2> create index t2_idx on t2(id);
Index created.
ops$tkyte@ORA9IR2> begin
2 dbms_stats.gather_table_stats( user, 'T1',
3 method_opt=> 'for all indexed columns', cascade=>true );
4 dbms_stats.gather_table_stats( user, 'T2',
5 method_opt=> 'for all indexed columns', cascade=>true );
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> alter system flush shared_pool;
System altered.
ops$tkyte@ORA9IR2> alter session set optimizer_index_caching = 0;
Session altered.
ops$tkyte@ORA9IR2> alter session set optimizer_index_cost_adj = 100;
Session altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t1, t2 where t1.id = t2.id and t2.id between 50 and 55;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=5 Bytes=1000)
1 0 HASH JOIN (Cost=28 Card=5 Bytes=1000)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=3 Card=5 Bytes=500)
3 2 INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=2 Card=5)
4 1 TABLE ACCESS (FULL) OF 'T1' (Cost=24 Card=26 Bytes=2600)
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> exec dbms_stats.set_system_stats( 'sreadtim', 0.595 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.set_system_stats( 'mreadtim', 2.386 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.set_system_stats( 'cpuspeed', 340 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.set_system_stats( 'mbrc', 7 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.set_system_stats( 'maxthr', 17729536 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 12-01-2003 07:04
SYSSTATS_INFO DSTOP 12-01-2003 07:04
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN SREADTIM .595
SYSSTATS_MAIN MREADTIM 2.386
SYSSTATS_MAIN CPUSPEED 340
SYSSTATS_MAIN MBRC 7
SYSSTATS_MAIN MAXTHR 17729536
SYSSTATS_MAIN SLAVETHR -1
10 rows selected.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t1, t2 where t1.id = t2.id and t2.id between 50 and 55;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=5 Bytes=1000)
1 0 HASH JOIN (Cost=34 Card=5 Bytes=1000)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=4 Card=5 Bytes=500)
3 2 INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=3 Card=5)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=30 Card=26 Bytes=2600) 5 4 INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=3 Card=1)
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> alter session set optimizer_index_caching = 90;
Session altered.
ops$tkyte@ORA9IR2> alter session set optimizer_index_cost_adj = 10;
Session altered.
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t1, t2 where t1.id = t2.id and t2.id between 50 and 55;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=5 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=100)
2 1 NESTED LOOPS (Cost=4 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=5 Bytes=500)
4 3 INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=3 Card=5)
5 2 INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=1 Card=1)
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> spool off
Relaiable way to choose values of the optimizer parameters?
Matt, December 02, 2003 - 12:27 am UTC
This makes sense. I can see that the SYSTEM stats and optimizer parameters affect the plans in each case.
Is there some reliable way of working out what the optimizer values should be set to. Is it possible to somehow calculate precisely how much of an index is cached and use this value, for example? In the past I have spent some considerable time testing various combinations and never being comfortable that I have found the right values.
Best Regards,
December 02, 2003 - 8:33 am UTC
I'm of the mind set (until someone convinces me otherwise) that there are about 3 settings for the optimizer_* stuff
a) as they are
b) in the middle
c) opposite of what they are
sort of like "low", "med", and "high" index bias.
using system stats may obviate the need to look at them intensely as the optimizer will take into consideration the "power" of your machine.
Is there something Wrong ???
Riaz Shahid, May 19, 2004 - 5:26 am UTC
Why the statement :
SQL> exec dbms_stats.gather_table_stats( user, 'GSTT01',method_opt=> 'for all indexed columns of size 256', cascade=>true );
Gives error:
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "SYS.DBMS_DDL", line 179
ORA-06512: at "SYS.DBMS_STATS", line 4467
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 1
While :
exec dbms_stats.gather_table_stats( user, 'GSTT01',method_opt=> 'for all indexed columns', cascade=>true );
is OK.
Whilee from Documentation:
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):
FOR ALL [INDEXED | HIDDEN] COLUMNS [SIZE integer]
FOR COLUMNS [SIZE integer] column|attribute
[,column|attribute ...]
Optimizer related table statistics are always gathered.
N.B Using 8.1.7.2.1 on Windowz 2000.
May 19, 2004 - 10:54 am UTC
you have "of size", it should be just "size"
A reader, May 19, 2004 - 12:59 pm UTC
Is it only my impression or autorace does not really execute SELECT statements when I use TRACEONLY EXPLAIN, differently from what happens when we run tkprof ?
After all, it's just an estimated plan, like in EXPLAIN PLAN FOR. I needn't have the select executed because:
1) I don't need a select output;
2) I don't need execution statistics.
3) I can avoid stressing the database with a possible huge select. (fewer PIOs, LIOs, latches, waits, etc)
That's what would make sense to me if I were developing autotrace at Oracle -- aren't we always looking foward to optimizing our code ?
Can you confirm that ?
May 19, 2004 - 2:30 pm UTC
it is not your impression -- with traceonly explain, it does not run them.
sort of wish it did the same for insert/update/delete and merge but it doesn't, they get executed.
DBMS_STATS.GATHER_SYSTEM_STATS
Thiru., May 20, 2004 - 3:08 pm UTC
DMBS_STATS.GATHER_SYSTEM_STATS:
A. IT WILL BE RUN BY USER SYS AND NOT BY A USER HAVING DBA ROLE.
B. FIRST TO CREATE A STAT TABLE.
C. GATHER STATS DURING SOME PEAK LOAD WITH AN INTERVAL OF SAY 5 MINUTES.
D. IMPORT THIS TABLE SO THAT THE CBO CAN LOOK AT THE SYSTEM STATS WHILE
CREATING THE EXECUTION PLAN.
E. GATHER SYSTEM STATS ONCE IN A WHILE ESPCIALLY WHEN THE LOAD IS HIGH.
F. ALONG WITH THE OPTIMIZER PARAMETERS OF CACHING AND COST_ADJ THE QUERIES WOULD
BE EXECUTED OPTIMALLY.
G. NO PERFORMANCE DEGRADATION WHILE THE STATS ARE BEING COLLECTED.
Is my understanding correct?
tHANKS.
May 20, 2004 - 8:13 pm UTC
why the CAPSLOCK.... anyway...
a) is wrong, just use a dba account.
b) ok
c) during "normal load" will do -- probably best, you want to let the optimizer understand "normal"
d) ok
e) see c
f) could be -- may well not need the others as the optimizer would have a feel for your IO and CPU
g) correct.
gather stale and partition/subpartitioned tables
A reader, May 20, 2004 - 6:36 pm UTC
Hi
I think there might be some problems gathering stale statistics on partitioned or subpartitioned tables. Usually I run dbms_stats.gather_table_stats because I must analyze table after tables since some needs histograms and some not.
If I monitor a 64 partitions table in an OLTP database, then the day after 32 partitiones needs to be analyzed because of heavy DMLs going, if I run dbms_stats.gather_table_stats using granularity of ALL (I want global stats and partition stats) then I will be analyzing the same table 32 times right? Is there any sort
of workaround for this?
What my script does is basically this
dbms_stats.gather_schema_stats(OWNNAME => l_schema_a,
OPTIONS => 'LIST STALE',
OBJLIST => l_objlist);
then
FOR y in 1 .. l_objlist.count
LOOP
dbms_stats.gather_table_stats(OWNNAME => l_schema_a,
TABNAME => l_objList(y).objname,
PARTNAME => l_objlist(y).partname,
ESTIMATE_PERCENT => 15,
METHOD_OPT => l_method,
DEGREE => 8,
GRANULARITY => 'ALL',
CASCADE => TRUE);
END LOOP;
So basically I am analyzing partition after partition, table after table.
May 20, 2004 - 8:49 pm UTC
no, you are just analyzing partition after partition. I don't see any global table level stats there?
GRANULARITY => 'ALL'
A reader, May 21, 2004 - 1:33 am UTC
I thought GRANULARITY => 'ALL' gathers global and partition stats?
May 21, 2004 - 9:53 am UTC
sorry, focused right in on the partition name in there.
if you want to minimize the work, you would first consider "do I need partition level statistics" (they come into play when you run a query that the optimizer KNOWS 100% that "partition name = 'ABC' will be used")
and then you would not use ALL, you would gather table stats ONCE and if needed partition stats for the affected partitions only.
One more question..
Thiru, May 21, 2004 - 9:49 am UTC
Thanks for the crisp answers.
a. The schema name in DBMS_STATS.CREATE_STAT_TABLE should be the schema in which the load is going on?
b. So if during high load, the system stats is not recommended, then why is it required to generate stats regularly for system. Once is enough and import them. Right?
c. I have done the following. What is your take/comment on the CPU_COST before and after stats gathering?
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS
PL/SQL procedure successfully completed.
SQL> select * from sys.aux_stats$;
no rows selected
SQL>
SQL> truncate table plan_table;
Table truncated.
SQL> explain plan for select * from test.t where c1='SOMECHAR';
Explained.
SQL> select operation, options, object_name, cpu_cost, io_cost
2 FROM PLAN_TABLE;
OPERATION OPTIONS OBJE CPU_COST IO_COST
------------------------------ ---------- ---- ---------- ----------
SELECT STATEMENT 1 1
TABLE ACCESS BY INDEX R T 1 1
OWID
INDEX RANGE SCAN T_ID 1
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE => 'INTERVAL',INTERVAL=>1,STATTAB =>'TEST_STATS',STATID =>'MY_ID')
SQL> select STATID, C1, C2, C3 from TEST_STATS;
STATID C1 C2 C3
------- ------------- ---------------- ----------------
MY_ID AUTOGATHERING 05-21-2004 09:29 05-22-2004 09:29
MY_ID
/
STATID C1 C2 C3
------- ------------- ---------------- ----------------
MY_ID COMPLETED 05-21-2004 09:29 05-21-2004 09:30
MY_ID
SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => 'TEST_STATS', statid => 'MY_ID', statown =>'TEST_USER')
PL/SQL procedure successfully completed.
SQL> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ---------- ---------- ----------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 05-21-2004
09:29
SYSSTATS_INFO DSTOP 05-21-2004
09:30
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN SREADTIM 2.903
SYSSTATS_MAIN MREADTIM 5.254
SYSSTATS_MAIN CPUSPEED 612
SNAME PNAME PVAL1 PVAL2
------------------------------ ---------- ---------- ----------
SYSSTATS_MAIN MBRC 10
SYSSTATS_MAIN MAXTHR 4935680
SYSSTATS_MAIN SLAVETHR -1
SQL> truncate table plan_table;
Table truncated.
SQL> explain plan for select * from TEST.t where c1='CHAR';
Explained.
SQL> select operation, options, object_name, cpu_cost, io_cost
2 FROM PLAN_TABLE;
OPERATION OPTIONS OBJE CPU_COST IO_COST
------------------------------ ---------- ---- ---------- ----------
SELECT STATEMENT 1193 1
TABLE ACCESS BY INDEX R T 1193 1
OWID
INDEX RANGE SCAN T_ID 4611 1
May 21, 2004 - 11:26 am UTC
one more? three more :)
do you have my book "Effective Oracle be Design" -- i cover this topic in chapter 6 with examples and all.
a) what "load is going on". the table should be in the schema that is doing the gathering.
b) your "normal" load characteristics will change as you add the stats. You analyze during normal load. Optimizer uses this in generating new plans. You hope the stats change a couple of plans -- which in turn will change your concept of "normal load" -- which in turn will change the IO characteristics (maybe you are doing tons more single block IO, single block IO might not be as cheap anymore, need to adjust to that). It is an interative process. the act of gather statistics and using them will *change* them ;) sort of like the Heisenberg uncertainty principle -- the act of observing something changes it.
c) no take -- it is what it is supposed to be doing -- recosting things
ORA-06564: object DATA_FILE_DIR does not exist
Sameer, June 25, 2004 - 3:58 am UTC
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jun 25 12:35:17 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS;
BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;
*
ERROR at line 1:
ORA-06564: object DATA_FILE_DIR does not exist
ORA-06512: at "SYS.DBMS_STATS", line 9136
ORA-06512: at "SYS.DBMS_STATS", line 9616
ORA-06512: at "SYS.DBMS_STATS", line 9800
ORA-06512: at "SYS.DBMS_STATS", line 9893
ORA-06512: at "SYS.DBMS_STATS", line 9873
ORA-06512: at line 1
I had previously gathered stats without any problems. But today i'm facing this error. Did i hit any bug?
Thanks Sameer
June 25, 2004 - 2:20 pm UTC
was data file dir an object you dropped during this? could be that consistent read told dbms_stats "here is an object" but by the time it got around to analyzing it, you had dropped it.
When to gather system stats?
A reader, June 29, 2004 - 7:45 pm UTC
"You need a really busy system to see the difference. (or at least a busy system then a not so busy system or a system that does OLTP by day and batch by night"
I have a system like this, primarily OLTP by day and batch by night.
When/how should I gather system stats for this system?
Should I dbms_stats.gather_system_stats('start') at, say, 9AM and dbms_stats.gather_system_stats('stop') at 9AM the next day to let Oracle gather a good sample of various workloads?
Suggestions? Thanks
After reading Jonathan's article on OTN </code>
http://otn.oracle.com/pub/articles/lewis_cbo.html <code>I am ready to start taking advantage of system statistics, he really drives the point home with showing how the exact same (trivial) query behaves dramtically different with/without system stats!
June 29, 2004 - 8:23 pm UTC
you would want to start after a workload began and stop before it ends.
You would want "oltp" stats and "batch" stats -- otherwise, the averages of the two would just erase eachother out.
if you have my book "Effective Oracle by Design", i show how to capture them and using dbms_stats to import/export them at will (for the different workloads)
ORA-06564: object DATA_FILE_DIR does not exist
Richard, July 02, 2004 - 5:07 am UTC
Hi,
Sameer from Pune, India described a problem with DBMS_STATS - and I have just encountered the same error:
BEGIN dbms_stats.gather_DATABASE_stats(); END;
*
ERROR at line 1:
ORA-06564: object DATA_FILE_DIR does not exist
ORA-06512: at "SYS.DBMS_STATS", line 9136
ORA-06512: at "SYS.DBMS_STATS", line 9616
ORA-06512: at "SYS.DBMS_STATS", line 9800
ORA-06512: at "SYS.DBMS_STATS", line 9893
ORA-06512: at "SYS.DBMS_STATS", line 9873
ORA-06512: at line 1
The above was generated during an overnight batch job. I have since checked (using OEM) for an object named 'DATA_FILE_DIR', but it isn't showing.
I suspect DATA_FILE_DIR must be an Oracle-owned object (as Sameer had the same error). Could Oracle be destroying it during stats gathering?
As ever, any help gratefully received.
July 02, 2004 - 9:56 am UTC
I researched further, someone created an external table (using an example -- data_file_dir is pretty generic) and you are trying to analyze it.
You don't have access to that directory object, hence it fails.
analyze as someone with create any directory or get rid of the external table.
DATA_FILE_DIR
Richard, July 02, 2004 - 11:35 am UTC
July 02, 2004 - 11:43 am UTC
there, it is exactly what I said.
someone installed examples
data_file_dir is part of the examples.
all directory objects are owned by "sys" (they have no owner really)....
workaround above is what you need to do, you are analyzing the demonstrations, the demonstrations have external tables, the external tables rely on directory objects, you don't have the ability to access that directory objects
which brings me back to advice that "gathering database stats isn't really a good thing". I would use gather schema stats -- to focus in on exactly the schema's I WANT -- to make the entire process run faster (do you really need to analyze demo's??)
Ah Ha!
Richard, July 02, 2004 - 11:51 am UTC
Hi,
Sorry, I misunderstood your meaning when you said *using an example*, and didn't twig that you meant OE, SH etc!
I was aware that database-level analysis was a no-no before 9i, but thought it was OK (and have read so in a few places) to use with 9i and upward. Never mind. Schema-level analysis with DBMS_STATS is obviously my way forward.
Many thanks for your help.
July 02, 2004 - 11:54 am UTC
I'm just personally not a fan of database wide gathering, databases are big things, I think of applications -- i think the application itself should have the ability to analyze itself -- and that means "schema level" at best.
External Tables
Richard, July 02, 2004 - 12:00 pm UTC
Hi,
Just a thought: am I right in saying that analyzing such an External Table is impossible, anyway?
July 02, 2004 - 12:15 pm UTC
no, not at all -- in fact in my book "Effective Oracle By Design" -- i show how you can analyze an external table in order to find the biggest (largest), most redundant columns in the data to be loaded in order to construct an order by that would achieve maximum compression during a CREATE TABLE AS SELECT or INSERT /*+ APPEND */ operation.
anks for Gathering database statistics
Sanjaya Balasuriya, September 23, 2004 - 3:44 am UTC
Hi Tom,
In your books "Expert one on one Oracle" under Appendix A : Necessary supplied packages; DBMS_UTILITY, describing analyze_database you say, "and has a nasty side effect of analyzing data dictionary (these are SYS owned objects, and should never analyze these".
Does this stands same for dbms_stats.gather_database_stats ?
Should we ignore dbms_stats.gather_database_stats too ?
I'm using;
dbms_stats.gather_database_stats(
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE auto',
cascade => true);
daily for analyzing my OLTP databases.
Is this a good way to use it ?
Thanks.
-San
September 24, 2004 - 7:58 am UTC
In 8i, it would not be recommended to analyze the dictionary.
In 9i, as long as you do it from day one (eg: from development on out), it is safe, and supported. But, if you are already in production -- don't just start doing it (it is like making a big code change, you do that in TEST TEST TEST)
In 10g, it'll just be analyzed out of the box.
I myself never have used database level stats, no. You might consider "alter table t monitor" and using gather stale on dbms_stats.gather_schema_stats run schema by schema (perhaps doing two or three schemas at a time). It'll go lots faster. Also, it would be recommended to EXPORT stats (using dbms_stats) and saving them -- you can "trend" with them over time and if "something goes horribly wrong" and you suspect the statistics -- you have the old ones to go back to.
importing system statistics
Parag Jayant Patankar, August 09, 2005 - 6:40 am UTC
Hi Tom,
I am trying to understnad system statistics example from your book effective oracle by design in Oracle 9.2.0.6 on AIX
When I am trying to import statistics into dictionary tables I am getting following error
15:43:06 SQL> exec dbms_stats.import_system_stats(-
15:44:51 > stattab=>'SYSTEM_STATS',STATID=>'DWH',statown=>'sys');
BEGIN dbms_stats.import_system_stats( stattab=>'SYSTEM_STATS',STATID=>'DWH',statown=>'sys'); END;
*
ERROR at line 1:
ORA-20000: Unable to import system statistics stats from user stat table
SYS.SYSTEM_STATS: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 5506
ORA-06512: at line 1
then I have created system_stats table in "sys" ( is it correct ? if not what should be correct method ) "
secondly after creating same table in sys I am getting same error.
Kindly suggest.
regards & thanks
pjp
August 09, 2005 - 10:18 am UTC
STOP USING SYS.
Log out now and start all over.
See, in the book -- I used a "normal user", use the step by steps in there as "a normal user"
It started with:
exec dbms_stats.drop_stat_table( user, 'SYSTEM_STATS' );
exec dbms_stats.create_stat_table( user, 'SYSTEM_STATS' );
exec dbms_stats.delete_system_stats;
To run each simulation, the general process is as follows:
1. Run the stored procedure without gathering system statistics. This is just priming the pump- getting the shared pool warmed up.
2. Submit the procedure N times to the job queue, to permit it to run in the background.
3. Commit the DBMS_JOB calls so the job queue may see those jobs in the queue.
4. Begin gathering system statistics. We name each set of statistics: OLTP, DW for data warehousing, and MIXED for a mixed workload. These identifying tags are used to put in place the set of system statistics we want later.
5. Periodically, poll the job queues every five seconds until the jobs are finished running.
6. Finish gathering the system statistics.
For example, this PL/SQL block executed the OLTP workload:
declare
n number;
begin
oltp_style;
dbms_job.submit( n, 'oltp_style;' );
dbms_job.submit( n, 'oltp_style;' );
dbms_job.submit( n, 'oltp_style;' );
commit;
dbms_stats.gather_system_stats( gathering_mode => 'START',
stattab => 'SYSTEM_STATS',
statid => 'OLTP' );
select count(*) into n from user_jobs where what = 'oltp_style;';
while ( n > 0 )
loop
dbms_lock.sleep(5);
select count(*) into n from user_jobs where what = 'oltp_style;';
end loop;
dbms_stats.gather_system_stats( gathering_mode => 'STOP',
stattab => 'SYSTEM_STATS',
statid => 'OLTP' );
end;
/
.....
Review the Optimizer's Query Plans
Now, we want to review the plans that the optimizer would come up with for our sample query (the one used previously with OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING). So, using EXPLAIN PLAN on that query using AUTOTRACE, we see the following:
big_table@ORA920> alter system flush shared_pool;
System altered.
big_table@ORA920> alter session set optimizer_index_cost_adj=100;
Session altered.
big_table@ORA920> alter session set optimizer_index_caching=0;
Session altered.
big_table@ORA920> begin
2 dbms_stats.import_system_stats
3 ( stattab => 'SYSTEM_STATS', statid => 'OLTP', statown => user );
3 end;
4 /
..........
Still facing a problem while importing sytem statistics
Parag Jayant Patankar, August 10, 2005 - 4:42 am UTC
Hi Tom,
Thanks for your answer. But still I am facing following problem
13:59:28 SQL> show user
USER is "ATLAS"
13:59:25 SQL> l
1 begin
2 dbms_stats.import_system_stats(stattab => 'SYSTEM_STATS', statid => 'DWH',
3* end;
13:59:27 SQL> /
begin
*
ERROR at line 1:
ORA-20000: Unable to import system statistics stats from user stat table
ATLAS.SYSTEM_STATS: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 5506
ORA-06512: at line 2
13:59:28 SQL> show user
USER is "ATLAS"
13:59:34 SQL> desc system_stats
Name Null? Type
----------------------------------------- -------- ----------------------------
STATID VARCHAR2(30)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(30)
C2 VARCHAR2(30)
C3 VARCHAR2(30)
C4 VARCHAR2(30)
C5 VARCHAR2(30)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
D1 DATE
R1 RAW(32)
R2 RAW(32)
CH1 VARCHAR2(1000)
13:59:40 SQL>
13:59:56 SQL> select statid from system_stats;
STATID
------------------------------
DWH
DWH
Kindly suggest where I have done mistake.
regards & thanks
pjp
August 10, 2005 - 10:14 am UTC
or insufficient privileges
do you have the privileges to do this. are you a "DBA" for example.
Not working till now
Parag Jayant Patankar, August 11, 2005 - 2:05 am UTC
Hi Tom,
Thanks for your answer. I have "DBA" role but still I am not able to import statistics in 9.2 in AIX
11:20:03 SQL> show user
USER is "SYSTEM"
11:20:05 SQL> grant dba to atlas;
Grant succeeded.
11:20:09 SQL> conn atlas/atlas@testinfod
Connected.
11:20:19 SQL>
11:22:29 SQL> select count(*) from system_stats;
COUNT(*)
----------
2
1 row selected.
11:22:40 SQL> begin
11:22:53 2 dbms_stats.import_system_stats(stattab => 'SYSTEM_STATS', statid => 'DWH', statown=>'ATLAS');
11:22:53 3 end;
11:22:59 4 /
begin
*
ERROR at line 1:
ORA-20003: Unable to import system statistics
ORA-06512: at "SYS.DBMS_STATS", line 5511
ORA-06512: at line 2
Kindly help me.
regards & thanks
pjp
August 11, 2005 - 9:45 am UTC
what does
select STATID, C1, C2, C3 from system_stats;
return. (peek at Note 154601.1 on metalink)
BADSTATS
Parag Jayant Patankar, August 11, 2005 - 9:47 am UTC
Hi Tom,
Something went wrong. If I do
19:06:32 SQL> select STATID, C1, C2, C3 from system_stats;
STATID C1
------------------------------ ------------------------------
C2 C3
------------------------------ ------------------------------
DWH BADSTATS
08-11-2005 10:13 08-11-2005 10:17
DWH
2 rows selected.
Can you guide me how to complete importing process ?
regards & thanks
pjp
August 11, 2005 - 6:06 pm UTC
did you check out that note?
(hint: that is the stats table there, there are no stats to actually import)
ORA-00928: missing SELECT keyword ? probably something really dumb...
Bruce Schwalm, August 11, 2005 - 5:00 pm UTC
Why won't this work? Using 9.2.0.4 .
I know it looks goofy, but I want self-documentation.
Thanks!
execute DBMS_STATS.GATHER_DATABASE_STATS
(25,
-- estimate_percent (NULL means compute)
FALSE,
-- block_sample
'FOR ALL COLUMNS SIZE 250',
-- method_opt
NULL,
-- degree (of parallelism - NULL uses table default)
'ALL',
-- granularity (pertinent to partitioned tables only)
TRUE,
-- cascade (?Gather Index statistics as well?)
NULL,
-- stattab - which user statistics table to put the stats to
-- - NULL means dictionary
NULL,
-- statid - The optional identified for these stats within stattab
'GATHER STALE',
-- options
'LIST',
-- list of objects found to be stale or empty
NULL,
-- statown - if stattab specified,
-- the schema containing stattab
-- if NULL, assumes each schema has stattab
FALSE,
-- gather_sys?
FALSE,
-- no_invalidate?
-- Use with CASCADE, read the book first.
FALSE,
-- gather_temp?
FALSE
-- gather_fixed?
)
;
PL/SQL procedure successfully completed.
(25,
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
August 12, 2005 - 8:21 am UTC
No clue, you have a bug in your script file. the dbms_stats call worked fine, it was whatever was in your script after that - that bombed.
system statistics
Parag J Patankar, August 12, 2005 - 11:40 am UTC
Hi Tom,
Thanks for your reference to your metalink note. As per your book I tried to gather syste statistics and stop it after workload. Due to BADSTATS status from statistics table I have not stop system statistics in procedure. After workloading procedure is complete I have checked stats table it was showing
20:35:36 SQL> /
STATID C1
------------------------------ ------------------------------
C2 C3
------------------------------ ------------------------------
DWH MANUALGATHERING
08-12-2005 20:30 08-12-2005 20:30
After that I have stop statistics from SQL prompt and then imported it into user successfully. My questions related are
Q1. I have not understood what is the meaning of MANUALGATHERING /AUTOGATHERING etc. Will you pl explain more about this ?
Q2. Will you pl explain from which manual I can read more details about this manualgathering and autogathering ?
Q3. Why earlier I was not able to import statistics ?
regards & thanks
pjp
August 13, 2005 - 9:32 am UTC
manual -- you are doing the start/stop explicitly.
auto -- it does the start/stop, you are NOT doing start/stop
the supplied packages guide goes into the inputs into this, unless you are doing START-STOP (manual) it would be auto.
Because, they were "badstats", as per that column. they were not valid
What kind of stats for partition table
A reader, August 24, 2005 - 10:20 am UTC
Tom,
Could you tell me what stats you would collect for an IOT partitioned table like:
CREATE TABLE FLOATTABLE
( DATEANDTIME DATE NOT NULL,
MILLITM NUMBER(3) NOT NULL,
TAGINDEX NUMBER(10) NOT NULL,
VAL FLOAT(126),
STATUS VARCHAR2(1 BYTE),
MARKER VARCHAR2(1 BYTE),
CONSTRAINT PK_FLOATTABLE
PRIMARY KEY (TAGINDEX, DATEANDTIME, MILLITM))
ORGANIZATION INDEX
PARTITION BY RANGE (DATEANDTIME)
( PARTITION FLOAT_2005_P029 VALUES LESS THAN (TO_DATE(' 2005-07-23 00:00:00', 'SYYYY-MM-DD H24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION FLOAT_2005_P030 VALUES LESS THAN (TO_DATE(' 2005-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION FLOAT_2005_P031 VALUES LESS THAN (TO_DATE(' 2005-08-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
.Partitions are one week in size.
.Data is never updated, just inserted.
.Rows are ~ 15 Million a day.
.There are currently some 2000 distinct TAGINDEX values
.TAGINDEX are not evenly distributed (some log every sec. other ever 5 min).
.Data is always queried in the form of:
SELECT DATEANDTIME, MILLITM, TAGINDEX,
VAL, STATUS, MARKER
FROM FLOATTABLE
WHERE ( TAGINDEX = :P1
OR TAGINDEX = :P2
OR TAGINDEX = :P3
OR TAGINDEX = :P4
OR TAGINDEX = :P5
OR TAGINDEX = :P6 )
AND DATEANDTIME BETWEEN :1 AND :2
ORDER BY DATEANDTIME DESC, MILLITM DESC
.Where the user selects one or more (up to 8) different TAGINDEX values.
.DATEANDTIME values in query above are usually one to two hours apart by can span days.
We are currently just using the following to collect stats on the active partition nightly.
DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME => B_OWNER,
TABNAME => B_TABLE,
PARTNAME => X.PARTITION_NAME,
GRANULARITY => 'PARTITION',
METHOD_OPT => 'FOR ALL INDEXED COLUMNS',
ESTIMATE_PERCENT => 1,
CASCADE => FALSE,
DEGREE => 2);
Your suggestions and criticism are most welcome.
Thanks for your time..
DB ver. 9.2.0.6
August 24, 2005 - 5:38 pm UTC
AND DATEANDTIME BETWEEN :1 AND :2
that means the optimizer will not be able to eliminate down to a single paratition, hence global statistics would typically be used to optimize the query
so, you'll want to make sure you have decent global statistics.
gather_database_stats
Parag Jayant Patankar, October 20, 2005 - 8:21 am UTC
Hi Tom,
I gone thru documentation of pl/sql packages but I have not understood clearly
What is the use of dbms_stats.gather_database_stats ? ( normally we collect stats by gather_schema_stats ) and where it can be real useful ?
regards & thanks
pjp
October 20, 2005 - 8:38 am UTC
what is the use?
to gather stats on the entire database.
where can it be real useful?
when you want to gather stats on all schemas??
(sorry, just seems "obvious"? isn't it?)
gather_database_stats
Parag J Patankar, October 20, 2005 - 10:50 am UTC
Hi Tom,
Thanks for your answer. By gathering database statistics does it going to affect dictionary tables also ? Does it will affect perfomance of recusrive calls ?
regards & thanks
pjp
October 20, 2005 - 4:34 pm UTC
read the parameter list description - in particular "gather_sys"
DBMS_STATS error
Deepak, October 24, 2005 - 9:49 am UTC
Hi Tom,
Getting error while executing the following...
SQL> begin
2 dbms_stats.gather_table_stats(ownname=> 'USER01', tabname=> 'MY_TBL', partname=> NULL);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.DBMS_STATS", line 9643
ORA-06512: at "SYS.DBMS_STATS", line 9657
ORA-06512: at line 2
Any pointers to the problem...?
October 24, 2005 - 11:43 am UTC
I'll suggest support for this, I do not see anything obvious.
Oracle 10g don't gather statistics for new tables
juancarlosreyesp@yahoo.com, November 21, 2005 - 5:58 pm UTC
Hi Tom I am testing dmbs_stats and leave 2 days an empty table and oracle 10g didn't gather statistics automatically. The job is enabled and statistics level is typical.
I'm testing now to see if I query something on the table, the optimizer will gather statistics this night.
Any comment?
Thank you.
08:32:11 SQL> drop table cachun.test;
Tabla borrada.
Transcurrido: 00:00:02.54
08:36:50 SQL> create table cachun.test as select * from dba_objects;
Tabla creada.
Transcurrido: 00:00:02.86
08:36:55 SQL> select last_analyzed from dba_tables where owner='CACHUN' and table_name='TEST';
LAST_ANA
--------
I tested it several times and in two days last_analyzed still is null.
November 21, 2005 - 6:07 pm UTC
check the job, is it really running.
A reader, November 22, 2005 - 7:34 am UTC
Yes Tom, and it is enabled, well I tested and use the table for a query, to see if that was needed (maybe the optimizer looked tables on v$sql).
And I gathered statistics on sys and fixed tables (that didn't gathered statistics on test table)
And one hour again, statistics whre gathered, I restarted my databaes this morning and recreated the table to see if today gathers again the statistics. I'll tell you.
the rule to gather statistics (and update) on fixed and sys
A reader, November 22, 2005 - 7:36 am UTC
(Tom I askyou you a question you didn't answer, but looking at "Your questions" I see it didn't reach you. The only thing happened was I went back from preview and couldn't review to confirm again. so I went a step back before s ending the question I think that disabled the question as valid.)
Tom which is the rule to gather statistics (and update) on fixed and sys tables on 10gr2.
Thank you
ORA-06512: at "SYS.DBMS_STATS", line 7788
NN, December 02, 2005 - 12:47 pm UTC
BEGIN dbms_stats.gather_index_stats('symom','SYMOM_LICENSE_LINES_DN13'); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze INDEX "SYMOM"."SYMOM_LICENSE_LINES_DN13",
insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 7788
ORA-06512: at line 1
wHY WE ARE GETTING ABOVE ERROR
December 03, 2005 - 10:02 am UTC
I don't know, you don't give me enough information. such as "are you symom" when you do this, or someone else.
and if you are someone else, do you have the privilege to analyze other peoples stuff.
I'll assume you are not logged in as 'symom' and whomever you are logged in as does not have the analyze any privilege:
ops$tkyte@ORA10GR2> create user a identified by a default tablespace users quota unlimited on users;
User created.
ops$tkyte@ORA10GR2> create user b identified by b default tablespace users quota unlimited on users;
User created.
ops$tkyte@ORA10GR2> grant create session, create table to a;
Grant succeeded.
ops$tkyte@ORA10GR2> grant create session to b;
Grant succeeded.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> connect a/a
Connected.
a@ORA10GR2> create table t ( x int );
Table created.
a@ORA10GR2> create index t_idx on t(x);
Index created.
a@ORA10GR2> exec dbms_stats.gather_index_stats( 'a', 'T_IDX' );
PL/SQL procedure successfully completed.
a@ORA10GR2>
a@ORA10GR2> connect b/b
Connected.
b@ORA10GR2> exec dbms_stats.gather_index_stats( 'a', 'T_IDX' );
BEGIN dbms_stats.gather_index_stats( 'a', 'T_IDX' ); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze INDEX "A"."T_IDX", insufficient privileges or
does not exist
ORA-06512: at "SYS.DBMS_STATS", line 10596
ORA-06512: at "SYS.DBMS_STATS", line 10645
ORA-06512: at line 1
b@ORA10GR2> connect /
Connected.
ops$tkyte@ORA10GR2> grant analyze any to b;
Grant succeeded.
ops$tkyte@ORA10GR2> connect b/b
Connected.
b@ORA10GR2> exec dbms_stats.gather_index_stats( 'a', 'T_IDX' );
PL/SQL procedure successfully completed.
Bug stats
nn, December 05, 2005 - 12:04 pm UTC
Note:2415196.8
Bug 2415196 ORA-904 from DBMS_STATS.GATHER_SCHEMA_STATS if column name contains multibyte character
We need to run analyze index
BEGIN dbms_stats.gather_index_stats('symom','SYMOM_LICENSE_LINES_DN19'); END;
*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 7807
ORA-06512: at line 1
SQL> analyze index symom.SYMOM_LICENSE_LINES_DN19 compute statistics;
Index analyzed.
dba_tab_modifications
Su Baba, March 19, 2006 - 3:13 pm UTC
Shouldn't the second SQL against dba_tab_modifications (after instance shutdown/startup) return data?
SQL> show user
USER is "SCOTT"
SQL> DROP TABLE x;
Table dropped.
SQL>
SQL> CREATE TABLE x AS
2 SELECT * FROM all_objects WHERE rownum < 100;
Table created.
SQL>
SQL> SELECT monitoring
2 FROM user_tables
3 WHERE table_name = 'X';
MON
---
YES
SQL> ANALYZE TABLE x COMPUTE STATISTICS;
Table analyzed.
SQL>
SQL> INSERT INTO x SELECT * FROM x;
99 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> SELECT inserts, updates, deletes, timestamp
2 FROM sys.dba_tab_modifications
3 WHERE table_owner = 'SCOTT' AND
4 table_name = 'X';
no rows selected
SQL> connect sys@orcl as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> connect sys@orcl as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 83886784 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> connect scott@orcl
Connected.
SQL> SELECT inserts, updates, deletes, timestamp
2 FROM sys.dba_tab_modifications
3 WHERE table_owner = 'SCOTT' AND
4 table_name = 'X';
no rows selected
SQL>
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
March 20, 2006 - 7:03 am UTC
use dbms_stats.FLUSH_DATABASE_MONITORING_INFO, not shutdown/startup. things change over time.
Gather_system_stats
Sudershan, May 14, 2006 - 11:23 am UTC
Oracle 9.2.0.4.0 On Sun
Tom,
I am seeing really bad performance from few of the data dictionary tables...This is from the script underneath sys.dba_free_space..This same query in lot of other databases comes back in less than a second..where in this database it is taking about a minute or so...not to mention the consistent gets...
would running gather_system_stats help?
Can it be run anytime?
set autotrace traceonly
***query behind sys.dba_free_space***
select ts.name, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0;
54139 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'FILE$'
4 2 TABLE ACCESS (CLUSTER) OF 'FET$'
5 4 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
6 1 TABLE ACCESS (CLUSTER) OF 'TS$'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6871793 consistent gets
0 physical reads
0 redo size
1195089 bytes sent via SQL*Net to client
40350 bytes received via SQL*Net from client
3611 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
54139 rows processed
May 14, 2006 - 11:27 am UTC
yes, it could. Right now you are using the RBO however - be prepared that when you gather statistics on the sys tables, you will affect every query against them, they will all start using the CBO.
In 8i - not suggested necessarily to index SYS
In 9i - suggested, as long as you have tested it (or have a back out plan if it doesn't work out well initially, while we figure out why)
In 10g - it'll be done by default
Gather_System_Stats
Sudershan, May 14, 2006 - 11:38 pm UTC
9204 on Sun..
well this is one of the production databases that is doing this...
I never had to run stats on dictionary before..Is this something that any database can have a need for some times..or is this a rare thing?
When you say a backout plan...I guess that would be running the delete_system_stats procedure...when I looked at
the 9.2 documentation...it does not explain that procedure..
It lists it as follows:
DELETE_SYSTEM_STATS Procedure
This procedure deletes system statistics.
Syntax
DBMS_STATS.DELETE_INDEX_STATS (
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
May 15, 2006 - 6:43 am UTC
Your dictionary on that database has "more stuff" than your dictionary in your other databases. The plan that works for "smaller" dictionaries isn't working so well here. That is all.
The 9ir2 documentation certainly does document the delete statistics.
</code>
http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm#1011262 <code>
It need not say much more than "this deletes them".
Delete system stats undoes the gather system stats - which is NOT what you are asking about. system statistics are measures of your CPU speed, single and multi-block IO rates, actual multi-block IO counts and so on. That is not what you are talking about gathering.
backing out of gather_system_stats
sudershan, May 15, 2006 - 11:39 am UTC
So, how do you back out after gather_system_stats..if things go south..
May 15, 2006 - 3:18 pm UTC
you would use delete system stats?
But - herein lies the confusion.
You want to gather statistics on the SYS schema.
You were not gathering system statistics - which is entirely different.
DBMS_STATS gathering with DBMS_JOB package
Ashok, September 19, 2006 - 2:12 pm UTC
Hi Tom,
I have read about your comments about using DBMS_JOB for DBMS_STATS and found very useful. However, when I tried to gather stats using DBMS_JOB package with parameter -
WHAT => 'DBMS_STATS.GATHER_SCHEMA_STATS(''IMG'', estimate_percent=>25);' , it did not work.
My requirement is to run stats every Sunday at 02:00 AM, so what should the value for NEXT_DATE => & INTERVAL => be specified?
Your help would be much appreciated.
Thanks
Ashok
September 19, 2006 - 2:56 pm UTC
my car won't start. why not?
(I've supplied as much information about my thing that won't work as you did about yours - we are even)
RE: my car won't start. why not?
Mark A. Williams, September 19, 2006 - 2:58 pm UTC
September 19, 2006 - 3:29 pm UTC
indeed, I do have a hybrid, but I'm sure it is not the batteries?
Now what?
Now what
Mark A. Williams, September 19, 2006 - 3:33 pm UTC
Better just do a re-org on the engine compartment.
- Mark
Re: Now what
Pablo, September 20, 2006 - 6:30 am UTC
> Better just do a re-org on the engine compartment
I'm sure that Tom does regular re-orgs of his engine compartment to improve performance :)
September 20, 2006 - 3:05 pm UTC
I tried that based on many excellent articles I've found on the internet. They described how reorgs of the engine compartment are absolutely safe, and how everyone just does them. I'd be stupid not to do them.
Now, my car is on fire. I must have plugged some battery in backwards - but I followed all of their advice.
Can you help now, this is getting serious?
my car is on fire
Pablo, September 21, 2006 - 8:29 am UTC
>Now, my car is on fire. I must have plugged some battery in backwards - but I
>followed all of their advice.
>Can you help now, this is getting serious?
UPDATE engine_compartment
SET fire = 'OUT'
WHERE battery_make = 'Sony';
dbms_stats.gather_database_stats not gathering data on all indexes
A reader, October 14, 2006 - 11:03 am UTC
Hi Tom,
i have a job that run daily to run
begin dbms_stats.gather_database_stats(); end;
but when i check my all schemas(regular users not sys or system) i found some tables and indexes aren't analyzed
is there a reason for that ?
thanks
I am facing same problem
A reader, October 15, 2006 - 6:21 am UTC
Hi Tom,
i am facing the same problem , when i run
sql>exec dbms_stats.gather_database_stats() ;
then i query the dba_tables , i found some tables haven't been analyzed at all , while others are analyzed. do you have any clue about the reason ?
2) i know that without using cascade option dbms_stats wont gather indexes statistics , but is it useful to gather index statistics ?
Thank You
October 15, 2006 - 7:04 am UTC
got a "for example", it is known that gather database stats skips various system schemas (like sys)
so, have you looked at these table owners and the table types, in short, provide a tad more "detail"
for example, after a SUCCESSFUL gather database stats, what useful information can you see from this query:
select owner, table_name, iot_type, temporary, secondary, nested from dba_tables where last_analyzed is null and owner <> 'SYS';
(please do not post 5000 lines of output here either - not asking for the output, asking for you to run the query and review the output yourself). Perhaps you are looking at temporary tables (for example) or SYS owned tables.
A reader, October 16, 2006 - 8:43 am UTC
Hi Tom,
i tried to find out in oracle9i when i execute
sql>exec dbms_stats.gather_database_stats() ;
does it gather statistics on all objects or just stale objects only ?
may you please give us a brief details.
thanks
First time running Statspack
rjvaggas@yahoo.com, October 23, 2006 - 7:42 am UTC
Hi Tom:
This is my first time running statspack. How do I make statspack.snap execute faster? What did I miss?
1 SELECT --p.spid,
2 s.sid, s.serial#, s.username,
3 --s.osuser, s.terminal, s.status, s.program,
4 TO_CHAR(SYSDATE, 'hh:mi:ss') CURRENT_TIME,
5 TO_CHAR(LOGON_TIME, 'hh:mi:ss') START_TIME,
6 T.SQL_TEXT
7 FROM v$session s,
8 V$SQL T,
9 v$process p
10 WHERE
11 S.SQL_ADDRESS = T.ADDRESS
12 AND s.username = 'PERFSTAT'
13* AND p.addr = s.paddr
sbc_admin@SBCBO> /
SID SERIAL# USERNAME CURRENT_ START_TI SQL_TEXT
---------- ---------- ---------- -------- -------- --------------------
593 862 PERFSTAT 10:16:23 09:29:17 BEGIN statspack.snap
; END;
I'm using the nmon64 utility (similar to top) on AIX 5.2 and 9.2.0.5.0RDBMS. CPU and disk usage is low
Thanks as usual
October 23, 2006 - 10:20 am UTC
if you are telling me a statspack.snap is taking 45 minutes, you have a problem. Please utilize support if you don't know how to diagnose what it is waiting for here. statspack snaps take seconds, not minutes/hours.
oops
RJ, October 23, 2006 - 7:47 am UTC
sorry Tom:
I asked the question on the wrong thread. Please ignore this followup
ORA-00928: missing SELECT keyword
Beroetz, November 13, 2006 - 9:55 am UTC
I have an hierarchy query that fails after gathering schema statistics!!!
Note the following code and correct me if this is not a bug:
SELECT *
FROM My_Table
CONNECT BY parent = PRIOR name
START WITH parent is null
> The query returns 1204 rows
CALL DBMS_STATS.Gather_Schema_Stats('MY_USER')
> Executed in 3,44 sec.
SELECT *
FROM My_Table
CONNECT BY parent = PRIOR name
START WITH parent is null
> ORA-00928: missing SELECT keyword
CALL DBMS_STATS.Clear_Schema_Stats('MY_USER')
> Executed in 2,87 sec.
SELECT *
FROM My_Table
CONNECT BY parent = PRIOR name
START WITH parent is null
> The query returns 1204 rows
November 14, 2006 - 4:02 am UTC
please utilize support.
GATHER_STATS_JOB is not working accurate
MJ, March 27, 2008 - 7:36 pm UTC
Hey TOM,
(Oracle :Solaris: Version 10.2.0.3.0 - Production)
We have default scheduled JOB for gathering stats in one of our production system. As per the Oracle-docs it does gather with "GATHER AUTO" options.
But I can find several application table having stale stats in our systems.
Like
Table :-XXXX shows below details
From DBA_TABLES:- No of rows :- 747152
Actual Table :- No of rows :- 0
Dont you think that DBMS_STATS.GATHER_SYSTEM_STATS not working as per expectation.?
Could you please explain me whther I am looking something wrong or I should not rely blindly on default gathering stats method of Oracle 10g.
Thanks
MJ
March 30, 2008 - 8:47 am UTC
insufficient data - you have not shown that the job has been running, you have not shown when the table last had statistics gathered on it.
for example, what if the last gather of that table in question was six hours ago - you haven't shown it hasn't been gathered against, you have just shown that right now they are not in sync with reality. The dba might have locked that table (eg: it is a table that is filled up and emptied and filled and emptied and they wanted to have numbers that represented the average size of the table for the optimizer)
Do a bit more detective work please....
What do I do when I am in a RAC environment.
Marco Gilbert, November 19, 2008 - 5:33 pm UTC
Hi,
I understand that having two set of system statistics is a must if I have a OLTP workload during the day and a DSS workload at night.
But what if I am in a RAC environment where one instance is used for OLTP while the other is used for DSS (and the two hosts might be of very different configuration).
It looks like the system statistics are collected and stored at the database level (aux_stats$), so I cannot have specific system statistics by instance.
Why would you suggest to do then ?
Thanks
November 24, 2008 - 11:12 am UTC
it is not a 'must', it can be useful if you have entirely different types of workloads - one that full scans frequently and the other that does not.
In your case, you probably want your OLTP workload, it is likely to be the more representative set of statistics for the work you care most about.
Do they plan to have system stats by instance.
Marco Gilbert, November 25, 2008 - 12:47 pm UTC
Hi,
Again about RAC database, do they know if there is something in the air about keeping the system statistics by instance/node instead of keeping them at the database level ?
It seems that it would be better since each node of a RAC might behave differently.
Thanks
November 25, 2008 - 2:13 pm UTC
not that I am aware of
Extrapolate statistics
Snehasish Das, February 02, 2011 - 2:10 pm UTC
Hi Tom,
we want to see how the plan of query changes when the volume doubles or triples. Can you let me know is there any way so that we can the extrapolate the statitics in the database, i.e if the the current statistics is for say 1 million records can i run a gatherstat such a way that it would build the statics for say 3million records.
Thanks,
Snehasish Das.
February 03, 2011 - 1:58 pm UTC
you would use dbms_stats.SET_ routines to set statistics of your choosing. there is nothing builtin to "double or triple" the existing statistics but you can freely set them.
about dbms_gather_stats
Suraj I, July 21, 2021 - 7:16 am UTC
when this dbms_gather_stats scheduler runs ,users facing issues and complaining oracle slow and when this scheduler run then the users going in concurrency wait class , and dbms scheduler takes 45 tot 50 minute to complete his job , so please suggest needful ...
thanks
July 21, 2021 - 7:26 am UTC
Are you using the defaults for everything here?
Suraj I, July 21, 2021 - 7:33 am UTC
yes we using all defaults ,and saturday , sunday this job run every 4hr from 6 am and users facing issues hence we change the window repeat interval and duration ,now this scheduler runns only one time in a day but still issue is not resolved ,
July 23, 2021 - 7:15 am UTC
OK, using the routine mentioned way back in the first answer to this question, can you get a list of what is being gathered each time. Lets see how much work is going on
about dbms_gather_stats
Suraj I, July 23, 2021 - 3:02 pm UTC
when {DBMS_SCHEDULER
call dbms_stats.gather_database_stats_job_proc ( )} that perticular time user facing issues and complaining oracle slow and when this scheduler call that job then it holds the user in CONCURRENCY wait class for 15-20 Min.
July 23, 2021 - 4:52 pm UTC
I'm unsure what you're doing here - could you clarify please?
about dbms_gather_stats
Suraj I, July 24, 2021 - 5:21 am UTC
sir ,
please give me suggestion about that above query because it addressed from last 30 days and before that its working normally so i want to know why dbms_gather_database_stats holds the users for 15-20 min while running ,i know we have to gather stats on offload time but our database is always busy 24/7 so please suggest accordingly ..
thanks...
July 26, 2021 - 1:45 pm UTC
Gathering stats shouldn't "hold" other users while it's running. Possible reasons this is happening include:
- You're at the limit of server resources and gathering stats is enough to push you over
- You've hit a bug
- It's a coincidence and something else is affecting other users
- something else
If you need further help with this, reach out to Oracle Support.