FAQ: 如何提升ArcSDE的性能

问题： 如何提升ArcSDE的性能？
回答： ArcSDE的性能可以通过分析空间数据和重建他们的索引来提升。表的分析和索引的重建对于版本地理数据库的常规维护是相当重要的。定期执行这两个操作可以帮助最大限度的维持版本地理数据库的性能。

以下是用以执行下列功能的脚本（用于Oracle和SQL Server）：
1） 通过拥有SDE数据的任意用户重建每一个索引。
2） 分析拥有SDE数据的每一个用户的schema。

注意：如果被用户拥有的任何表并没有任何实际的图层，那么这个脚本将需要被修改。

这些脚本并非是针对每一个与ArcSDE相关的性能问题的解决方案，但是应该作为维护日程中定期执行的内容。

注意：应该有规律地针对正在改变的数据集进行数据分析。这将更新被oracle基于成本的优化器使用的统计信息，以产生用于SQL的最优执行计划。ArcSDE在后台SQL 中包含的功能都依赖于oracle基于成本的优化器，它依赖于最新的统计信息。此外，可以考虑使用其它的脚本和工具用于这类操作。例如，Oracle tools可以被用于在适当的时候更新统计和重建其它的索引。

Oracle 8i/9i/10G(需要以’SYS用户运行’)
SET SERVEROUTPUT ON

DECLARE

CURSOR Owner_Cur IS
SELECT DISTINCT(OWNER) owner
FROM sde.table_registry
ORDER BY owner;

CURSOR Index_Cur IS
SELECT owner, index_name
FROM dba_indexes
WHERE owner IN
(SELECT DISTINCT(owner)
FROM sde.table_registry)
AND INDEX_TYPE = 'NORMAL'
ORDER BY owner, index_name;

SQL_STMT VARCHAR2(200);

BEGIN

DBMS_OUTPUT.ENABLE (100000);

FOR IndexRec IN Index_Cur LOOP
SQL_STMT := ('ALTER INDEX ' || IndexRec.owner || '.' || IndexRec.index_name || ' REBUILD');
DBMS_OUTPUT.PUT_LINE(SQL_STMT);
EXECUTE IMMEDIATE SQL_STMT;
DBMS_OUTPUT.NEW_LINE;
END LOOP;

FOR OwnerRec IN Owner_Cur LOOP
DBMS_OUTPUT.PUT_LINE('Analyzing schema : ' || OwnerRec.owner);
DBMS_STATS.GATHER_SCHEMA_STATS(OwnerRec.owner);
DBMS_OUTPUT.NEW_LINE;
END LOOP;

END;

/
SQL Server 2000/2005 – 单数据库模型（需要以’SA’用户运行或作为’DBO’用户 – 依赖于SDE库如何被创建）：
-- Rebuild indexes on tables for all owners in the table registry.
-- ***************************************************************
-- Run under the Database that contains the SDE Repository tables.
-- If the SDE schema is owned by 'DBO', change:
-- select distinct owner from SDE.sde_table_registry
-- to
-- select distinct owner from DBO.sde_table_registry

use <Admin_Database>

Declare @OwnerName varchar(50)
Declare @TableName varchar(50)
Declare @QualifiedName varchar(100)

Declare OwnerCursor cursor for
select distinct owner from SDE.sde_table_registry

open OwnerCursor
Fetch OwnerCursor into @OwnerName
while (@@fetch_status=0)
begin
Declare TableCursor cursor for
-- Get all the tables for this user
select so.name from sysobjects so join sysusers su on so.uid = su.uid where type = 'U' and su.name = @OwnerName
open TableCursor
Fetch TableCursor into @TableName
while (@@fetch_status=0)
begin
set @QualifiedName = @OwnerName + '.' + @TableName
print 'Rebilding indexes on : ' + @QualifiedName
dbcc dbreindex(@QualifiedName) WITH NO_INFOMSGS
print 'Updating Statistics on: ' + @QualifiedName
EXEC ('UPDATE STATISTICS ' + @QualifiedName )
print ''
Fetch TableCursor into @TableName
end
Fetch OwnerCursor into @OwnerName
close TableCursor
Deallocate TableCursor
end
close OwnerCursor
Deallocate OwnerCursor
注意：为了获得<Admin_Database>,直接在SDE服务器上执行’sdeservice –o list…’ 命令。

SQL Server 2000/2005 – 多数据库模型（需要以’SA’用户运行或作为’DBO’用户 – 依赖于SDE库如何被创建）：
-- Rebuild indexes on tables for all owners in the table registry.
-- ***************************************************************
-- Run under the Database that contains the SDE Repository tables.
-- If the SDE schema is owned by 'DBO', change:
-- select distinct owner from SDE.sde_table_registry
-- to
-- select distinct owner from DBO.sde_table_registry

-- In a Multiple Database Model, you need to run this script in
-- every database that contains data registered with ArcSDE.

use <Database_where_spatial_data_resides>

Declare @OwnerName varchar(50)
Declare @TableName varchar(50)
Declare @QualifiedName varchar(100)

Declare OwnerCursor cursor for
select distinct owner from SDE.SDE.sde_table_registry
open OwnerCursor
Fetch OwnerCursor into @OwnerName
while (@@fetch_status=0)
begin
Declare TableCursor cursor for
-- Get all the tables for this user
select so.name from sysobjects so join sysusers su on so.uid = su.uid where type = 'U' and su.name = @OwnerName
open TableCursor
Fetch TableCursor into @TableName
while (@@fetch_status=0)
begin
set @QualifiedName = @OwnerName + '.' + @TableName
print 'Rebilding indexes on : ' + @QualifiedName
dbcc dbreindex(@QualifiedName) WITH NO_INFOMSGS
print 'Updating Statistics on: ' + @QualifiedName
EXEC ('UPDATE STATISTICS ' + @QualifiedName )
print ''
Fetch TableCursor into @TableName
end
Fetch OwnerCursor into @OwnerName
close TableCursor
Deallocate TableCursor
end
close OwnerCursor
Deallocate OwnerCursor

注意：为了获得包含注册到ArcSDE 数据的所有数据库的列表，执行下面的查询：
select distinct(database_name) from SDE.SDE.sde_table_registry;




创建时间：2003-02-14
最近更新： 2011-07-18


【原文链接】
http://support.esrichina.com.c ... .html