GATHERING STATISTICS FASTER FOR LARGE DATABASE ORACLE
There are scenarios where statistics need to be gathered after an import of database after ignoring statistics with datapump. It is suggested to use DBMS_STATS package rather than datapump to collect statistics faster for the newly imported objects. What if, the database is too large? Let us see some methods and case studies to observe the faster ways to collect statistics for large database.
Solution is “high resource” and nothing magic here
Check the size of the target schema for gathering statistics.
SQL> SELECT distinct ds.segment_name,ds.bytes/1048576 MB,dt.degree
FROM dba_Segments ds
INNER JOIN dba_tables dt on ( dt.owner = ds.owner )
WHERE
dt.owner='C##HYDRA'
ORDER BY MB desc;
Check the size of the database and top 5 big tables under the schema which contain big tables.
SQL> SELECT sum(bytes/1073741824) DB_GB from dba_segments;
DB_GB
----------
33.2966919
SQL> col SEGMENT_NAME for a20
SQL> SELECT * from (SELECT distinct ds.segment_name,ds.bytes/1048576 MB,dt.degree FROM dba_Segments ds INNER JOIN dba_tables dt on ( dt.owner = ds.owner ) ORDER BY MB desc) WHERE rownum < 6;
SEGMENT_NAME MB DEGREE
-------------------- ---------- ----------------------------------------
ADDRESSES 8422 1
ORDER_ITEMS 7350 1
CNS 4315.4375 1
CNS 4315.4375 4
UN 4287 1
To monitor the stats job, use the below query.
SQL> set lines 200
set pages 1000
col OPNAME format a20
col MESSAGE format a20
col username format a10
col opname format a10
col sid format 999999
col serial# format 999999
col TIME_REMAINING format 999999
col pct format 9999999
select sid,
serial#,
USERNAME,
opname,
100*(sofar/totalwork) as pct,
to_char(START_TIME,'dd-mm-yy hh24:mi:ss') as starttime,
to_char(LAST_UPDATE_TIME,'dd-mm-yy hh24:mi:ss') as change,
TIME_REMAINING,
ELAPSED_SECONDS,
MESSAGE
from v$session_longops
where sofar < totalwork
and message like '%Stat%';SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13
SID SERIAL# USERNAME OPNAME PCT STARTTIME CHANGE TIME_REMAINING ELAPSED_SECONDS MESSAGE
------- ------- ---------- ---------- -------- ----------------- ----------------- -------------- --------------- --------------------
73 25839 SYS Gather Dat 47 01-07-23 14:15:53 01-07-23 14:36:35 1375 1242 Gather Database Stat
abase Stat istics: Database : 1
istics 095 out of 2307 Obje
cts done
73 25839 SYS Gather Tab 20 01-07-23 14:44:38 01-07-23 14:46:17 396 99 Gather Table's Index
le's Index Statistics: Table S
Statistic ALES : 1 out of 5 In
s dexes done
73 25839 SYS Gather Dat 47 01-07-23 14:47:47 01-07-23 15:09:44 1458 1317 Gather Database Stat
abase Stat istics: Database : 1
istics 095 out of 2307 Obje
cts done
Flush the shared pool and buffer cache and start the statistics job as a benchmark. Consider that this is the first time, the stats job is executed in the database and this time should be the initial baseline performance for stats job.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> SET TIMING ON
SQL> EXEC dbms_stats.gather_database_stats();
PL/SQL procedure successfully completed.
Elapsed: 00:46:11.69
It took 46 minutes for stats for whole database to complete.
Things to review are to check the configuration of parameters for stats preference.
- Notice degree and concurrent parameters mainly which are the influencers to improve performance.
- Also check for estimate_percent parameter which is related to the sample size of the stats gathering process. If this value is too low(5), then stats may be collected faster but with a trade off of less accuracy which means optimizer may generate suboptimal execution plans down the line. If the value is high(100), then the time taken may increase compared to low value as accurate samples are collected at granular level.
- If the table is partitioned, then check for INCREMENTAL and INCREMENTAL_LEVEL if they are disabled. Enable the parameter immediately as this may benefit 10x by avoiding the full table scan during partition statistics collection.
SQL> select 'AUTOSTATS_TARGET =====> '||DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET')||'' as "STATS_PARAM" from dual
union all
select 'CASCADE =====> '||DBMS_STATS.GET_PREFS('CASCADE')||'' from dual
union all
select 'CONCURRENT =====> '||DBMS_STATS.GET_PREFS('CONCURRENT')||'' as "STATS_PARAM" from dual
union all
select 'DEGREE =====> '||DBMS_STATS.GET_PREFS('DEGREE')||'' from dual
union all
select 'ESTIMATE_PERCENT =====> '||DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')||'' from dual
union all
select 'GRANULARITY =====> '||DBMS_STATS.GET_PREFS('GRANULARITY')||'' from dual
union all
select 'INCREMENTAL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL')||'' from dual
union all
select 'INCREMENTAL_LEVEL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_LEVEL')||'' from dual
union all
select 'INCREMENTAL_STALENESS =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS')||'' from dual
union all
select 'METHOD_OPT =====> '||DBMS_STATS.GET_PREFS('METHOD_OPT')||'' from dual
union all
select 'OPTIONS =====> '||DBMS_STATS.GET_PREFS('OPTIONS')||'' from dual
union all
select 'STALE_PERCENT =====> '||DBMS_STATS.GET_PREFS('STALE_PERCENT')||'' from dual; 10 11 12 13 14 15 16 17 18 19 20 21 22 23
STATS_PARAM
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AUTOSTATS_TARGET =====> AUTO
CASCADE =====> DBMS_STATS.AUTO_CASCADE
CONCURRENT =====> AUTO
DEGREE =====> NULL
ESTIMATE_PERCENT =====> DBMS_STATS.AUTO_SAMPLE_SIZE
GRANULARITY =====> AUTO
INCREMENTAL =====> FALSE
INCREMENTAL_LEVEL =====> PARTITION
INCREMENTAL_STALENESS =====>
METHOD_OPT =====> FOR ALL COLUMNS SIZE AUTO
OPTIONS =====> GATHER
STALE_PERCENT =====> 10
12 rows selected.
Delete statistics and regather stats with parallel servers. Do not allocate parallel for tables which are less than 1 GB in size which does not get benefitted from parallel.
SQL> EXEC dbms_stats.delete_database_stats();
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> ALTER table c##hydra.ADDRESSES parallel 4;
Table altered.
SQL> ALTER table c##hydra.ORDER_ITEMS parallel 4;
Table altered.
SQL> ALTER table CNS parallel 4;
Table altered.
SQL> ALTER table c##hydra.ORDERS parallel 4;
Table altered.
SQL> ALTER table kish.sts parallel 2;
Table altered.
1.Check the settings of concurrent jobs which if set to manual, then concurrent jobs may use additional resources like CPU and MEMORY to further aggressively improve the performance but provided with more resource.
2. Make sure that the settigns for parallel max servers is correctly set as per the formula. ==> parallel_max_servers = cpu_count * parallel_threads_per_cpu * 5 * 2(if pga_aggregate_target parameter is set else 0)
3. Parallel degree policy plays a major role on how the parallel servers are controlled either manually or automatically. If set to manual then degree parameter is taken from table definition settings.(SELECT degree from dba_tables where table_name=<table>;) If set to auto, then parallel_degree_limit should be set to CPU so that oracle automatically control the parallel usage upto less than or equal to CPU threshold.
SQL> select dbms_stats.get_prefs('CONCURRENT') from dual;
DBMS_STATS.GET_PREFS('CONCURRENT')
--------------------------------------------------------------------------------
MANUAL
SQL> EXEC dbms_stats.set_global_prefs('DEGREE','DBMS_STATS.AUTO_DEGREE');
PL/SQL procedure successfully completed.
SQL> show parameter parallel_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 100
SQL> ALTER system set parallel_max_servers=200;
System altered.
SQL> ALTER system set parallel_degree_policy=MANUAL;
System altered.
Other option is to configure resource manager and allocate majority of the resource to statistics jobs.
SQL> EXEC dbms_stats.gather_database_stats();
BEGIN dbms_stats.gather_database_stats(); END;
*
ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: Resource Manager is not
enabled.
ORA-06512: at "SYS.DBMS_STATS", line 36309
ORA-06512: at line 1
SQL> BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
-- Create plan
DBMS_RESOURCE_MANAGER.create_plan(
plan => 'hybrid_plan',
comment => 'Plan for a combination of high and low priority tasks.');
-- Create consumer groups
2 3 4 5 6 7 8 9 10 11 DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'Gather_stats',
comment => 'Gather_stats - high priority');
12 13 14
DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'Other',
comment => 'Other - low priority');
-- Assign consumer groups to plan and define priorities
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'hybrid_plan',
group_or_subplan => 'Gather_stats',
comment => 'High Priority - level 1',
cpu_p1 => 100,
cpu_p2 => 100,
15 16 17 18 19 20 21 22 23 24 25 cpu_p3 => 80);
DBMS_RESOURCE_MANAGER.create 26 _plan_directive (
plan => 'hybrid_plan',
group_or_subplan => 'Other',
comment => 'Low Priority - level 2',
cpu_p1 => 0,
cpu_p2 => 0,
cpu_p3 => 20);
27 28 29 30 31 32 33 34 35 36 DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/ 37 38 39
BEGIN
*
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 1003
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 114
ORA-06512: at line 6
SQL> DECLARE
x_plan VARCHAR2(30) := 'Gather_stats';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.create_cdb_plan(
plan => 'x_plan',
comment => 'Plan for statistics job');
DBMS_RESOURCE_MANAGER.create_cdb_plan_directive(
plan => 'x_plan',
pluggable_database => 'updb',
shares => 3,
utilization_limit => 100,
parallel_server_limit => 100);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/ 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
PL/SQL procedure successfully completed.
Configure the resource plan and activate it so that the future jobs will use the plan for resource allocation.
SQL> show parameter resource
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
resource_manager_cpu_allocation integer 4
resource_manager_plan string
SQL> ALTER system set resource_manager_plan=x_plan;
System altered.
Once the resource manager plan directive is configured, then monitor the system for a while on how the CPU process is distributed across PDB’s
top - 14:31:18 up 21 min, 2 users, load average: 12.31, 12.07, 8.83
Tasks: 308 total, 2 running, 306 sleeping, 0 stopped, 0 zombie
%Cpu(s): 14.6 us, 1.2 sy, 0.0 ni, 0.0 id, 83.1 wa, 0.9 hi, 0.1 si, 0.0 st
MiB Mem : 9670.4 total, 61.8 free, 1148.2 used, 8460.4 buff/cache
MiB Swap: 4096.0 total, 4082.9 free, 13.1 used. 2419.7 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2405 oracle 20 0 6507416 510944 505968 D 16.9 5.2 0:29.23 ora_p005_hydrup
2318 oracle 20 0 6507072 574400 569196 D 15.6 5.8 0:28.31 ora_p001_hydrup
2401 oracle 20 0 6506920 503568 498504 D 6.3 5.1 0:30.86 ora_p003_hydrup
2320 oracle 20 0 6508108 555344 550468 D 5.3 5.6 0:31.49 ora_p002_hydrup
2409 oracle 20 0 6507176 462420 457508 D 5.3 4.7 0:28.64 ora_p007_hydrup
2407 oracle 20 0 6507448 528824 523288 D 4.3 5.3 0:31.62 ora_p006_hydrup
2403 oracle 20 0 6507180 503548 498812 D 3.0 5.1 0:29.91 ora_p004_hydrup
Check the long running stats job from v$session_longops to get an idea on the progress.
SQL> SELECT TARGET,STATUS,JOB_NAME,NOTES from dba_optstat_operations WHERE START_TIME > SYSDATE - (1/24);
TARGET STATUS JOB_NAME NOTES
-------------------- -------------------- -------------------- --------------------
COMPLETED <params><param name=
"force" val="FALSE"/
><param name="no_inv
alidate" val="NULL"/
><param name="statid
" val=""/><param nam
e="statown" val=""/>
<param name="stattab
" val=""/><param nam
e="stattype" val="AL
L"/></params>
IN PROGRESS <params><param name=
"block_sample" val="
FALSE"/><param name=
"cascade" val="NULL"
/><param name="concu
rrent" val="TRUE"/><
param name="degree"
val="DEFAULT_DEGREE_
VALUE"/><param name=
"estimate_percent" v
al="DEFAULT_ESTIMATE
_PERCENT"/><param na
me="gather_fixed" va
l="FALSE"/><param na
me="gather_sys" val=
"TRUE"/><param name=
"gather_temp" val="F
ALSE"/><param name="
granularity" val="DE
FAULT_GRANULARITY"/>
<param name="method_
opt" val="DEFAULT_ME
THOD_OPT"/><param na
me="no_invalidate" v
al="NULL"/><param na
me="options" val="GA
THER"/><param name="
reporting_mode" val=
"FALSE"/><param name
="statid" val=""/><p
aram name="statown"
val=""/><param name=
"stattab" val=""/><p
aram name="stattype"
val="DATA"/></param
s>
Now execute the stats for the second time with parallel settings updated. It took 54 minutes to complete with parallel settings for specific big tables. However, this has not improved the overall performance of the database statistics.
SQL> EXEC dbms_stats.gather_database_stats();
PL/SQL procedure successfully completed.
Elapsed: 00:54:28.14
Check the existing plan directives for the resource plan X_PLAN which was configured before.
SQL> SELECT PLAN,PLUGGABLE_DATABASE "PDB",SHARES,UTILIZATION_LIMIT,PARALLEL_SERVER_LIMIT,MEMORY_LIMIT,COMMENTS from dba_cdb_rsrc_plan_directives WHERE PLAN='X_PLAN';
PLAN PDB SHARES UTILIZATION_LIMIT PARALLEL_SERVER_LIMIT MEMORY_LIMIT COMMENTS
------------------------------ -------------------- ---------- ----------------- --------------------- ------------ --------------------
X_PLAN ORA$AUTOTASK 90 100 Autotask Directive
X_PLAN UPDB 3 100 100
X_PLAN ORA$DEFAULT_PDB_DIRE 1 100 100 Default Directive
CTIVE
Allocate all the parallel resource upto 100% to the PDB which contain the big tables.
SQL> DECLARE
l_plan VARCHAR2(30) := 'Gather_stats';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.create_cdb_plan(
plan => 'x3_plan',
comment => 'Plan for statistics job');
DBMS_RESOURCE_MANAGER.create_cdb_plan_directive(
plan => 'x3_plan',
pluggable_database => 'updb',
shares => 100,
utilization_limit => 100,
parallel_server_limit => 100);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
PL/SQL procedure successfully completed.
Validate if all the parallel processes are utilized after allocating 100% parallel to updb
SQL> SELECT count(*) FROM v$px_process WHERE status <> 'AVAILABLE';
COUNT(*)
----------
56
It took 43 minutes after allocating 100% resource to updb which contain huge tables
SQL> set timing on
SQL> EXEC dbms_stats.gather_database_stats();
PL/SQL procedure successfully completed.
Elapsed: 00:43:42.06
When we enable restricted session to suppress other activities in the database,
Never ending process due to restricted session. Because parallel slaves are not used for stats gathering.
SQL> SET TIMING ON
SQL> ALTER system enable restricted session;
System altered.
SQL> EXEC dbms_stats.gather_database_stats();
^Z^CBEGIN dbms_stats.gather_database_stats(); END;
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_AQ", line 366
ORA-06512: at "SYS.DBMS_STATS", line 5710
ORA-06512: at "SYS.DBMS_STATS", line 6594
ORA-06512: at "SYS.DBMS_STATS", line 34846
ORA-06512: at "SYS.DBMS_STATS", line 35711
ORA-06512: at "SYS.DBMS_STATS", line 36230
ORA-06512: at "SYS.DBMS_STATS", line 36301
ORA-06512: at line 1
Elapsed: 03:14:49.46For aggressive speed of the stats gathering, set the following if more CPU is available for oracle database.
Warning: Always test the parameters and never blindly set CPU and parallel thread parameters in a production system.
[oracle@hydrupgrd ~]$ lscpu|grep 'CPU(s):'|grep -v 'NUMA'
CPU(s): 4
SQL> show parameter cpu
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 1
parallel_threads_per_cpu integer 1
resource_manager_cpu_allocation integer 4
SQL> EXEC dbms_stats.set_global_prefs('CONCURRENT','TRUE');
PL/SQL procedure successfully completed.
SQL> ALTER system set cpu_count=4;
System altered.
SQL> ALTER system set parallel_threads_per_cpu=2;
System altered.
SQL> ALTER system set parallel_max_servers=80;
System altered.
SQL> ALTER system set parallel_servers_target=60;
System altered.
SQL> ALTER system set job_queue_processes=200;
System altered.
SQL> ALTER system set resource_manager_plan=x_plan;
System altered.
SQL> ALTER system set parallel_execution_message_size=32768 scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 637534208 bytes
Fixed Size 2927624 bytes
Variable Size 494928888 bytes
Database Buffers 134217728 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
Execute the stats job after changes and check the load on the server.
top – 14:22:46 up 3:29, 2 users, load average: 7.39, 5.87, 6.49
Tasks: 481 total, 1 running, 479 sleeping, 1 stopped, 0 zombie
%Cpu(s): 4.9 us, 0.4 sy, 0.0 ni, 28.7 id, 65.4 wa, 0.6 hi, 0.0 si, 0.0 st
MiB Mem : 9670.4 total, 72.0 free, 1895.5 used, 7702.9 buff/cache
MiB Swap: 4096.0 total, 4087.0 free, 9.0 used. 7333.9 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16455 oracle 20 0 1002628 247140 231664 S 11.9 2.5 0:06.72 ora_j027_hydrup
16052 oracle 20 0 1058584 295500 251372 S 2.6 3.0 0:04.98 oracle_16052_hy
16213 oracle 20 0 1002536 240012 230148 S 2.3 2.4 0:04.90 ora_j164_hydrup
15121 oracle 20 0 992500 249032 243396 S 1.0 2.5 0:07.52 ora_j004_hydrup
14809 oracle -2 0 985180 50068 48056 S 0.7 0.5 0:10.80 ora_vktm_hydrup
14917 oracle 20 0 987120 188932 185096 D 0.7 1.9 0:06.50 ora_p003_hydrup
16983 oracle 20 0 65872 5384 4212 R 0.7 0.1 0:00.32 top
113 root 0 -20 0 0 0 I 0.3 0.0 0:01.30 kworker/3:1H-kblockd
14815 oracle 20 0 985180 174148 172108 S 0.3 1.8 0:00.32 ora_mman_hydrup
14823 oracle 20 0 985180 57620 55580 S 0.3 0.6 0:01.95 ora_vkrm_hydrup
14861 oracle 20 0 989660 180228 176572 S 0.3 1.8 0:03.39 ora_p001_hydrup
14863 oracle 20 0 987780 195612 191312 D 0.3 2.0 0:05.33 ora_p002_hydrup
15470 oracle 20 0 988032 134944 131152 S 0.3 1.4 0:00.05 ora_j068_hydrup
15778 oracle 20 0 988152 179724 175272 S 0.3 1.8 0:00.37 ora_j221_hydrup
15886 oracle 20 0 987104 201764 197664 S 0.3 2.0 0:00.85 ora_j066_hydrup
16647 oracle 20 0 987008 162324 158608 S 0.3 1.6 0:00.34 ora_j242_hydrup
16952 oracle 20 0 1002712 263716 246444 D 0.3 2.7 0:07.07 ora_j002_hydrup
Check CPU usage now.
SQL> set lines 250
set pages 2000
col username format a15 justify center
col program format a20 justify center
col event format a20 justify center
col sid format 999999 justify center
col CPU_USAGE format 999999999 justify center
col CPUPCT format 9999999999 justify center
col value format 999999
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> col PCT format 999999.99
with "CPUPCT" as (select round(sum(value),3) as total_cpu from v$sesstat)
select * from (select z.sid,z.status,nvl(z.username,'oracle-bg') as username,nvl(z.SQL_ID,'non-SQL') as SQL_ID,z.EVENT,z.program,
SQL> 2 3 round(sum(y.value)/100,6) as "CPU_USAGE",round(trunc(100*round(sum(y.value/100)),3)/(select * from CPUPCT),3) as PCT
from v$statname x
4 5 inner join v$sesstat y on x.STATISTIC# = y.STATISTIC#
inner join v$session z on y.SID = z.SID
6 7 where x.name in ('CPU used by this session')
group by z.sid,z.username,z.SQL_ID,z.EVENT,z.program,z.status order by CPU_USAGE desc)
where rownum < 6; 8 9
SID STATUS USERNAME SQL_ID EVENT PROGRAM CPU_USAGE PCT
------- -------- --------------- ------------- -------------------- -------------------- ---------- ----------
161 ACTIVE SYS 6hwah6nnt0dhd db file sequential r oracle@hydrupgrd (J0 11 .00
ead 27)
11 ACTIVE oracle-bg non-SQL rdbms ipc message oracle@hydrupgrd (CJ 8 .00
Q0)
554 ACTIVE SYS 4xg0vm951zzpx db file sequential r sqlplus@hydrupgrd (T 6 .00
ead NS V1-V3)
146 ACTIVE oracle-bg non-SQL rdbms ipc message oracle@hydrupgrd (MM 3 .00
ON)
308 ACTIVE oracle-bg non-SQL EMON slave idle wait oracle@hydrupgrd (Q0 3 .00
0E)
SQL> /
SID STATUS USERNAME SQL_ID EVENT PROGRAM CPU_USAGE PCT
------- -------- --------------- ------------- -------------------- -------------------- ---------- ----------
161 ACTIVE SYS 5jw2nd7s3kda0 PX Deq: Execute Repl oracle@hydrupgrd (J0 13 .00
y 27)
11 ACTIVE oracle-bg non-SQL rdbms ipc message oracle@hydrupgrd (CJ 8 .00
Q0)
554 ACTIVE SYS 4xg0vm951zzpx db file sequential r sqlplus@hydrupgrd (T 7 .00
ead NS V1-V3)
146 ACTIVE oracle-bg non-SQL rdbms ipc message oracle@hydrupgrd (MM 3 .00
ON)
308 ACTIVE oracle-bg non-SQL EMON slave idle wait oracle@hydrupgrd (Q0 3 .00
0E)
After enabling concurrent, increasing max parallel, job queues, pems, cpu count etc…., it took 19 minutes for the statistics to complete which is almost 60% improvement.
SQL> set timing on
SQL> set time on
16:11:34 SQL> EXEC dbms_stats.gather_database_stats();
PL/SQL procedure successfully completed.
Elapsed: 00:19:33.79
Another cause effective way to focus on the exact problem is to trace the stats job and find out the problematic table where the time is spent all the time using stats trace.
SQL> select dbms_stats.get_param('trace') from dual;
DBMS_STATS.GET_PARAM('TRACE')
--------------------------------------------------------------------------------
0
SQL> exec DBMS_STATS.SET_GLOBAL_PREFS('trace', 2+4+8+16+64+512+1024+2048 /* 3678 */);
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_param('trace') from dual;
DBMS_STATS.GET_PARAM('TRACE')
--------------------------------------------------------------------------------
3676
*** 2023-11-20 10:43:25.700
DBMS_STATS: gather_database_stats: <params><param name="block_sample" val="FALSE"/><param name="cascade" val="NULL"/><param name="concurrent" val="TRUE"/><param name="degree" val="DEFAULT_DEGREE_VALUE"/><param name="estimate_percent" val="DEFAULT_ESTIMATE_PERCENT"/><param name="gather_fixed" val="FALSE"/><param name="gather_sys" val="TRUE"/><param name="gather_temp" val="FALSE"/><param name="granularity" val="DEFAULT_GRANULARITY"/><param name="method_opt" val="DEFAULT_METHOD_OPT"/><param name="no_invalidate" val="NULL"/><param name="options" val="GATHER"/><param name="reporting_mode" val="FALSE"/><param name="statid" val=""/><param name="statown" val=""/><param name="stattab" val=""/><param name="stattype" val="DATA"/></params>With a combination of stats trace and 10046 trace, more data can be collected with additional hints.
The below information shows that the stats job mostly waited on Streams AQ: waiting for messages in the queue wait event which is advanced queueing issue. This may not be the same case for every one who encounter stats slowness as every system is different. This may be a bug related to concurrent settings with DBMS_STATS. Disabling concurrent may reduce the events but may probably increase the time further if there is another potential problem because of non concurrent jobs.
SQL ID: 44pgaj5vdubxy Plan Hash: 0
BEGIN dbms_stats.gather_database_stats(); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.51 11156.78 734 1484 934 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.51 11156.78 734 1484 934 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 4 0.00 0.00
db file sequential read 721 0.34 6.75
control file sequential read 600 0.00 0.00
latch: shared pool 1 0.00 0.00
Streams AQ: waiting for messages in the queue
1106 12.53 11146.59
SQL*Net break/reset to client 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.51 11156.78 734 1484 934 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.51 11156.78 734 1484 934 0
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 1 39.74 39.74
Disk file operations I/O 4 0.00 0.00
db file sequential read 750 0.34 6.78
control file sequential read 600 0.00 0.00
latch: shared pool 1 0.00 0.00
Streams AQ: waiting for messages in the queue
1106 12.53 11146.59
SQL*Net break/reset to client 1 0.00 0.00