English

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

Question

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

Answer

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.

43:36:39.79

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) 
FROM 
(SELECT s.sp_id, s.gx, s.gy, row_number() 
OVER
( Partition BY s.sp_id ORDER BY s.gx, s.gy) 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.gy, sp.sp_id

This behavior has been identified to occur in versions of Oracle prior to 11.2.0.4.0. Esri recommends using Oracle versions 11.2.0.4.0 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 11.2.0.4.0 or 12.1.0.1.0.

Related Information