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

来源：互联网发布：linux查看swap大小编辑：程序博客网时间：2024/05/16 09:45

Article ID:42983Software: 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.3Platforms: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 onexec dbms_stats.gather_table_stats('BEN','ROADS_1M')Results :PL/SQL procedure successfully completed.43:36:39.79SQL 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 NIM084365Submitted Sep 4, 2012 11:20 AMSeverity MediumApplies To ArcGISVersion Found 10.0Prog Language N/AServer Platform AllClient Platform All WindowsDatabase OracleLocale N/AStatus DeclinedVersion Fixed N/ASP 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就可以在移动端获取最新技术文章

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

0 0