Hi.
We have observed that the gather table statistics frequently runs very slow. Although this is not always the case. This is happening in an 11.2.0.4 OBIA data warehouse, as part of an incremetal load (refresh).
Here are the execution times (in seconds) for the last 19 executions. These executions occur nightly at approximately the same time.
This is the command that is being run:
DBMS_STATS.GATHER_TABLE_STATS
(ownname => 'XXXXX',
tabname => 'W\_GL\_OTHER\_F',
cascade => FALSE,
estimate\_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE,
method\_opt => 'FOR ALL COLUMNS
SIZE AUTO',
degree => DBMS\_STATS.DEFAULT\_DEGREE);
We are trying to determine if there is anything we can do to increase and/or consistency the performance of this task. Also, wondering why sometimes it finishes very quickly.
Here are the non-default parameters (as taken from the alert log):
processes = 2500
sessions = 3840
filesystemio_options = "SETALL"
disk_asynch_io = TRUE
sga_target = 8G
db_block_size = 8192
db_writer_processes = 16
compatible = "11.2.0.4.0"
remote_login_passwordfile= "EXCLUSIVE"
session_cached_cursors = 500
plsql_code_type = "NATIVE"
job_queue_processes = 10
parallel_max_servers = 16
audit_trail = "DB"
open_cursors = 800
_b_tree_bitmap_plans = FALSE
star_transformation_enabled= "TRUE"
parallel_adaptive_multi_user= FALSE
query_rewrite_enabled = "TRUE"
query_rewrite_integrity = "TRUSTED"
pga_aggregate_target = 4G
There are 175,226,008 rows in this table (W_GL_OTHER_F).
Thanks to all who participate here!
[DB 11.2.0.4] - OBIA Data warehouse
Regards,
Charles