Skip to Main Content
  • Questions
  • how to use dbms_stats.gather_databse_stats

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: December 13, 2000 - 8:25 pm UTC

Last updated: July 26, 2021 - 1:45 pm UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

Tom:

i try to use dbms_stats to give me a report of
what statistics is missing, what i do is:

declare a dbms_stats.objecttab;
begin
dbms_stats.gather_database_stats(OPTIONS=>'LIST EMPTY',OBJLIST=>a);
end;

after that how can i know the content of a?



and we said...

You would simply print out the values of A(i) in a loop. For example:

tkyte@TKYTE816> l
1 declare
2 a dbms_stats.objecttab;
3 begin
4 dbms_stats.gather_database_stats
(OPTIONS=>'LIST EMPTY',OBJLIST=>a);
5
6 for i in 1 .. a.count
7 loop
8 dbms_output.put_line( a(i).ownname );
9 dbms_output.put_line( a(i).objType );
10 dbms_output.put_line( a(i).objName );
11 dbms_output.put_line( a(i).PartName );
12 dbms_output.put_line( a(i).subPartName );
13 dbms_output.put_line( a(i).Confidence );
14 dbms_output.put_line
( '-------------------------' );
15 end loop;
16 end;
17*
tkyte@TKYTE816> /
CTXSYS
INDEX
DRC$DEL_KEY
100
-------------------------
CTXSYS
INDEX
DRC$IDX_COLSPEC
100
-------------------------
.....

PL/SQL procedure successfully completed.


Make sure to set serveroutput on in sqlplus first!

Rating

  (71 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.

Tom Kyte
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.

Tom Kyte
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

Tom Kyte
March 20, 2003 - 7:59 am UTC

Hmm, why do you say that?

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/dbms_st2.htm#1006517 <code>

it was a feature of Oracle8i..

So, I'm sorry -- but yes, it does 'work'

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)

Tom Kyte
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

Tom Kyte
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?

Tom Kyte
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 :?

Tom Kyte
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.

Tom Kyte
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.

Tom Kyte
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?


Tom Kyte
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?

Tom Kyte
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?

Tom Kyte
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!




Tom Kyte
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,


Tom Kyte
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,


Tom Kyte
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.

 

Tom Kyte
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 ?

Tom Kyte
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.

Tom Kyte
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.

Tom Kyte
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?

Tom Kyte
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 

Tom Kyte
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 

Tom Kyte
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!

Tom Kyte
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.

Tom Kyte
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

Hi,

Thank you for looking into this.

DATA_FILE_DIR... I think it's an Oracle-owned object, because it's in the following link:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4144686815509 <code>

where it relates to the demo schemas.

I am still getting the same error message, when I run DBMS_STATS manually.

Tom Kyte
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.

Tom Kyte
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?

Tom Kyte
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


Tom Kyte
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
 

Tom Kyte
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 

Tom Kyte
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 

Tom Kyte
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 

Tom Kyte
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


Tom Kyte
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
 

Tom Kyte
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


Tom Kyte
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

Tom Kyte
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

Tom Kyte
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...? 

Tom Kyte
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. 

Tom Kyte
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



Tom Kyte
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                                          


 

Tom Kyte
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


Tom Kyte
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);





Tom Kyte
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..

Tom Kyte
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


Tom Kyte
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

> my car won't start. why not?

More battery problems?

</code> http://tkyte.blogspot.com/2006/09/paris.html <code>

Tom Kyte
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 :)

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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
Tom Kyte
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



Tom Kyte
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

Tom Kyte
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.
Tom Kyte
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
Connor McDonald
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 ,
Connor McDonald
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.
Chris Saxon
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...
Chris Saxon
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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library