An example of spatial index tuning in ArcSDE

Arcsde version: 9.1

Database: Oracle 10gR2

Beginning with ArcSDE 9.2 geodatabases, the spatial index is calculated for you when you create a new dataset and provide a coordinate system in ArcGIS Desktop. You shouldn't need to tune such spatial indexes. However, if you are using an older release of ArcSDE such as 9.1, you can follow the steps below to tune the index.

1. Check the spatial index

C:\ArcSDE91>sdelayer -o si_stats -l my_layer,geometry -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

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

Layer 1606 Spatial Index Statistics:

Level 1, Grid Size 50000

|-------------------------------------------------------------------|

| Grid Records: 2680 |

| Feature Records: 174 |

| Grids/Feature Ratio: 15.40 |

| Avg. Features per Grid: 4.73 |

| Max. Features per Grid: 17 |

| % of Features Wholly Inside 1 Grid: 1.72 |

|-------------------------------------------------------------------|

| Spatial Index Record Count By Group |

| Grids: <=4 >4 >10 >25 >50 >100 >250 >500 |

|---------- ------ ------ ------ ------ ------ ------ ------ ------ |

| Features: 51 123 77 30 10 0 0 0 |

| % Total: 29% 71% 44% 17% 6% 0% 0% 0%|

|-------------------------------------------------------------------|

2. Use sdelayer –o load_only_io to drop the spatial index. Switching the feature class to load-only mode drops the spatial index. While the feature class is in load-only mode, no spatial queries or data loading is allowed on this layer.

C:\ArcSDE91>sdelayer -o load_only_io -l my_layer,geometry -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

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

Layer is now in LOAD-ONLY I/O mode.

3. Use sdelayer –o alter –g n,n,n to specify new grid sizes. Specify 0 for the second or third grid size if not used.

C:\ArcSDE91>sdelayer -o alter -l my_layer,geometry -g 150000,0,0 -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

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

Change spatial index from SPIDX_GRID,50000

to SPIDX_GRID,150000

Are you sure? (Y/N): y

Successfully Modified the Layer

Successfully changed spatial index parameters and rebuild as needed

4. Use sdelayer –o normal_io to rebuild the spatial index and make the layer accessible again.

C:\ArcSDE91>sdelayer -o normal_io -l my_layer,geometry -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

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

Layer is now in NORMAL I/O mode.

5. Check the spatial index

C:\ArcSDE91>sdelayer -o si_stats -l my_layer,geometry -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

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

Layer 1606 Spatial Index Statistics:

Level 1, Grid Size 150000

|-------------------------------------------------------------------|

| Grid Records: 675 |

| Feature Records: 174 |

| Grids/Feature Ratio: 3.88 |

| Avg. Features per Grid: 8.44 |

| Max. Features per Grid: 32 |

| % of Features Wholly Inside 1 Grid: 22.41 |

|-------------------------------------------------------------------|

| Spatial Index Record Count By Group |

| Grids: <=4 >4 >10 >25 >50 >100 >250 >500 |

|---------- ------ ------ ------ ------ ------ ------ ------ ------ |

| Features: 134 40 10 0 0 0 0 0 |

| % Total: 77% 23% 6% 0% 0% 0% 0% 0%|

|-------------------------------------------------------------------|

6. Repeat step 2 to 5 until satisfied.

C:\ArcSDE91>sdelayer -o si_stats -l my_layer,geometry -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

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

Layer 1606 Spatial Index Statistics:

Level 1, Grid Size 425000

|-------------------------------------------------------------------|

| Grid Records: 287 |

| Feature Records: 174 |

| Grids/Feature Ratio: 1.65 |

| Avg. Features per Grid: 17.94 |

| Max. Features per Grid: 77 |

| % of Features Wholly Inside 1 Grid: 58.05 |

|-------------------------------------------------------------------|

| Spatial Index Record Count By Group |

| Grids: <=4 >4 >10 >25 >50 >100 >250 >500 |

|---------- ------ ------ ------ ------ ------ ------ ------ ------ |

| Features: 174 0 0 0 0 0 0 0 |

| % Total: 100% 0% 0% 0% 0% 0% 0% 0%|

|-------------------------------------------------------------------|

V$OBJECT_USAGE

Oracle 11gR1

V$OBJECT_USAGE displays statistics about index usage gathered from the database. You can use this view to monitor index usage. All indexes that have been used at least once can be monitored and displayed in this view.

Column	Datatype	Description
INDEX_NAME	VARCHAR2(30)	Index name in sys.obj$.name
TABLE_NAME	VARCHAR2(30)	Table name in sys.obj$.name
MONITORING	VARCHAR2(3)	YES| NO
USED	VARCHAR2(3)	YES| NO
START_MONITORING	VARCHAR2(19)	Start monitoring time in sys.object_stats.start_monitoring
END_MONITORING	VARCHAR2(19)	End monitoring time in sys.object_stats.end_monitoring

Note:

Turn on monitoring for the index:

alter index test_ids monitoring usage;

Make sure the index is being monitored:

select * from v$object_usage;

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
TEST_IDS	TEST	YES	NO	07/31/2008 11:39:32	NULL

select * from test where tt='tt';

select * from v$object_usage;

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
TEST_IDS	TEST	YES	YES	07/31/2008 11:39:32	NULL

The column USED will tell us if this index is accessed to satisfy a query.

Turn off monitoring for the index:

alter index test_ids nomonitoring usage;

select * from v$object_usage;

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
TEST_IDS	TEST	NO	YES	07/31/2008 11:39:32	07/31/2008 11:46:49

Oracle dynamic performance views