ArcSDE for Oracle在大数据量执行创建统计信息（Analyze)耗时长的问题

Article ID:	42983
Software:	ArcSDE 10.1, 10.2, 10.2.1, 10.2.2 ArcGIS for Desktop Advanced 10.1, 10.2, 10.2.1, 10.2.2, 10.1 SP1, 10.3 ArcGIS for Desktop Standard 10.1, 10.2, 10.2.1, 10.2.2, 10.1 SP1, 10.3 ArcGIS for Desktop Basic 10.1, 10.2, 10.2.1, 10.2.2, 10.1 SP1, 10.3
Platforms:	N/A

Question

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

在Oracle数据库中，为什么对大数据量（ST_Geometry)创建统计信息会消耗非常长的时间？

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. 

不管是使用ArcCatalog的分析功能（Analyze）或者是使用Oracle提供的统计信息的存储过程dbms_stats.gather_table_stats都会消耗非常长的时间

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. 

这种情况会在Oracle 11.2.0.4以前的版本发生，Esri建议如果需要解决该问题，升级你的Oracle版本11.2.0.4或者以上，在该环境下可以提高相关的执行效率.

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.

如果有用户碰到相关问题，建议升级Oracle版本11.2.0.4或者12.1.0.1

Bug NIM-084365

Nimbus ID	NIM084365
Submitted	Sep 4, 2012 11:20 AM
Severity	Medium
Applies To	ArcGIS
Version Found	10.0
Prog Language	N/A
Server Platform	All
Client Platform	All Windows
Database	Oracle
Locale	N/A
Status	Declined
Version Fixed	N/A
SP Fixed	N/A

Synopsis

Gathering table statistics using Oracle DBMS_STATS.GATHER_TABLE_STATS generates high CPU usage and runs very slow for large SDE layers with ST_GEOMETRY data type.

Additional Status Information

N/A

Alternate Solution

N/A

----------------------------------------

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

建议看到转载，请直接访问正版链接获得最新的ArcGIS技术文章

Blog:               http://blog.csdn.net/linghe301 

                欢迎添加微信公众号：ArcGIS技术分享（arcgis_share），直接回复1就可以在移动端获取最新技术文章

----------------------------------------