Frequently asked question

In Oracle, why does it take a long time to build statistics on large ST_GEOMETRY data?

Last Published: April 26, 2020


Attempting to use the ArcCatalog Analyze... command or gathering table statistics in SQL*Plus for a feature class using ST_GEOMETRY in Oracle can take a very long time to complete.
SQL*Plus example:
set timing on
exec dbms_stats.gather_table_stats('BEN','ROADS_1M')

Results :
PL/SQL procedure successfully completed.


SQL to identify the poorly performing query :

SELECT se.sid,se.username,sa.sql_text 
FROM v$session se, v$sqlarea sa 
WHERE se.sql_address=sa.address 
AND se.sql_hash_value=sa.hash_value;

Poorly Performing Query :

SELECT /*+ no_parallel(b) no_parallel_index(b) 
dbms_stats cursor_sharing_exact use_weak_name_resl 
dynamic_sampling(0) no_monitoring 
*/ dbms_rowid.rowid_block_number(b.rowid) 
(SELECT s.sp_id, s.gx,, row_number() 
( Partition BY s.sp_id ORDER BY s.gx, rncol 
FROM BEN.S57_IDX$ s) sp, BEN.ROADS_1M b 
WHERE rncol = 1 AND b.rowid = sp.sp_id 
ORDER BY sp.gx,, sp.sp_id

This behavior has been identified to occur in versions of Oracle prior to Esri recommends using Oracle versions or above, which use a better execution plan than previous Oracle releases, and this in turn improves the statistics building process.

If this behavior is encountered, consider upgrading the Oracle instance to a minimum version of or

Article ID:000013348

  • Legacy Products
  • ArcMap

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Related Information

Discover more on this topic