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

【解决方案】
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.


Example


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.

【创建及修改时间】
Created: 8/18/2014

Last Modified: 8/19/2014
【原文链接】
http://support.esri.com/en/kno ... 42983