Collect Statistics for a Large Partitioned Table is Slow When Incremental Option Is Used (Doc ID 1302628.1)

Last updated on APRIL 25, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.

Symptoms

After upgrade to 11.2 the collection of statistics for a big partitioned table does not finish.

The method is

exec dbms_stats.gather_table_stats(ownname => 'OTEST', tabname => 'USAGE', estimate_percent => 1, degree => 20, cascade => true);


Most of the time is spent by the following SQL on visiting blocks and with disk reads, as the following shows.

SQL ID: 1zdkwckmyutrd
Plan Hash: 3200470404
DELETE FROM SYS.WRI$_OPTSTAT_SYNOPSIS$
WHERE
SYNOPSIS# IN (SELECT H.SYNOPSIS# FROM SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$ H
WHERE H.BO# = :B1 AND H.GROUP# NOT IN (SELECT T.OBJ# * 2 FROM SYS.TABPART$
T WHERE T.BO# = :B1 UNION ALL SELECT T.OBJ# * 2 FROM SYS.TABCOMPART$ T
WHERE T.BO# = :B1 ))

call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00       0.00          0          0          0          0
Execute      1  1353.29    1412.20    1321248  104076467          0          0
Fetch        0     0.00       0.00          0          0          0          0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        2  1353.29    1412.20    1321248  104076467          0          0



Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
 0 DELETE WRI$_OPTSTAT_SYNOPSIS$ (cr=0 pr=0 pw=0 time=0 us)
 0 FILTER (cr=0 pr=0 pw=0 time=0 us)
 376323248 HASH JOIN (cr=1321293 pr=1321248 pw=0 time=311179584 us
cost=3492572 size=53390047200 card=2321306400)
 911927 TABLE ACCESS FULL WRI$_OPTSTAT_SYNOPSIS_HEAD$ (cr=14315 pr=14295 pw=0
time=315366 us cost=3173 size=15747100 card=926300)
454944265 TABLE ACCESS FULL WRI$_OPTSTAT_SYNOPSIS$ (cr=1306978 pr=1306953
 pw=0 time=188280768 us cost=1481402 size=13927838400 card=2321306400)
 106590 UNION-ALL (cr=102754733 pr=0 pw=0 time=0 us)
 106590 TABLE ACCESS FULL TABPART$ (cr=102754733 pr=0 pw=0 time=0 us cost=470
size=3146 card=286)
 0 TABLE ACCESS BY INDEX ROWID TABCOMPART$ (cr=0 pr=0 pw=0 time=0 us cost=0
size=26 card=1)
 0 INDEX RANGE SCAN I_TABCOMPART_BOPART$ (cr=0 pr=0 pw=0 time=0 us cost=0
size=0 card=1)(object id 291)

DBMS_STATS.GET_PREFS('INCREMENTAL','OTEST','USAGE')

------------------------------------------------------------------------------
TRUE


It can be seen that estimate_percent specified for gather_table_stats is not observed when incremental preference is on.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms