dbms stats collection time

[purirb]

I am trying to gather stats and I see that no matter what I use in degree below it has no effect on time to collect stats(in fact it is taking longer if i bump up this 1,2,3...). I am running this when there is no one on the system. If I am not running parallel server will DEGREE value matter, our tables are created without any degree.

DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'scott',
options => 'GATHER AUTO',
DEGREE =>DBMS_STATS.DEFAULT_DEGREE,
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=>TRUE);

[davey23uk]

parallel server has nothing to do with degreee (perhaps you mean parallel query?)

anyway, I'll turn it around on you - why do you think it will go quicker?

You also dont mention hardware / os

[purirb]

I am running on Solaris UNI and Oracle 9.0.4(believe that is called 10g) also. I tried using degee Null(no param passed),2,3. I notice it has no effect, actually collect time goes down. I was using parallel in lose tem, may be it means server or query. I already posted syntax I used before. Why degree has no effect here?

[tamilselvan]

I am running on Solaris UNI and Oracle 9.0.4(believe that is called 10g) also. I tried using degee Null(no param passed),2,3. I notice it has no effect, actually collect time goes down. I was using parallel in lose tem, may be it means server or query. I already posted syntax I used before. Why degree has no effect here?

Scott schema may not have enough rows in tables for parallelising statistics collection.
Also, you need to look parallel parameters in spfile/init. If they are not set properly, then parallelism will not be used.

Tamil

[davey23uk]

and 9.0.4 is not 10g

exactly what version do you have?

(thre is no db version 9.0.4)

[purirb]

Actually, we are running Oracle 10.1.0.4.0 on Sun Solaris, sorry for that wrong number 9.0.4( we have a number like that for OAS - that was the confusion).

[purirb]

I am actually running against real application and some tables are pretty big. I do not think we really are set up for parallel server. What parameters I need to set in spfile to start that, most parameters like parallel* are set to false I see in v$parameter? Does this parameter(degree) in dbmas_stats package server have to do with parallel server?

[khussain]

I am actually running against real application ...

If you mean RAC, then that is the new Parallel Server.

To determine if you are using Parallel query, then these init params will give you that info: PARALLEL_MAX_SERVERS and PARALLEL_MIN_SERVERS

Also, as mentioned earlier, scott schema is not a good schema to do this test in ... use a schema with more data.

If I were you, I would leave it to NULL, and move forward.

[dbaxchangedba]

Based on the options you've used :

DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'scott',
options => 'GATHER AUTO',
DEGREE =>DBMS_STATS.DEFAULT_DEGREE,
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=>TRUE);

- GATHER AUTO would do one of two things : If monitoring is enabled on the tables within the scott schema then stats are collected only if they are stale (there is a 10% change in the data for a particular table) otherwise stats are skipped for that table. On the other hand if monitoring is not enabled then oracle collects stats by default which is an overkill and can cause plan instability. Use dbms_stats.ALTER_SCHEMA_TAB_MONITORING procedure to enable monitoring on tables so that you collect stats only on objects that have stale stats on them.

- As suggested by others in the thread, Parallelism will help speed up the stats collection but that would also mean that each of the parallel slave process that is involved in the stats collection process would create global temporary tables with ROWID ranges through the use of PX_GRANULE. Make sure you have sufficient temp space to accomodate global temp tables for each of the parallel slave processes. Also make sure you use locally managed tempfiles for your temporary tablespaces to avoid Space management transaction (ST) enqueue waits.

- Stats collection processes use full scans on tables leading to "db file scattered read" wait events. Having higher values for db_file_multiblock_read_count at the session level can potentially help the process.

Good Luck!

http://www.dbaxchange.com