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

CONTROL_FILE_RECORD_KEEP_TIME

Oracle 11gR1

Property	Description
Parameter type	Integer
Default value	7 (days)
Modifiable	ALTER SYSTEM
Range of values	0 to 365 (days)
Basic	No

CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If this parameter is set to 0, then reusable sections never expand, and records are reused as needed.

Note:

This parameter applies only to records in the control file that are circularly reusable (such as archive log records and various backup records). It does not apply to records such as datafile, tablespace, and redo thread records, which are never reused unless the corresponding object is dropped from the tablespace.

Oracle initializatoin parameters

DBA_XML_TABLES

Oracle 11gR1

DBA_XML_TABLES describes all XML tables in the database. Its columns are the same as those in ALL_XML_TABLES.

Related Views

· ALL_XML_TABLES describes the XML tables accessible to the current user.

· USER_XML_TABLES describes the XML tables owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)		Owner of the XML table
TABLE_NAME	VARCHAR2(30)		Name of the XML table
XMLSCHEMA	VARCHAR2(700)		Name of the XML Schema that is used for the table definition
SCHEMA_OWNER	VARCHAR2(30)		Owner of the XML Schema that is used for the table definition
ELEMENT_NAME	VARCHAR2(2000)		Name of the XML SChema element that is used for the table
STORAGE_TYPE	VARCHAR2(17)		Storage option for the XMLtype data: · OBJECT-RELATIONAL · BINARY · CLOB
ANYSCHEMA	VARCHAR2(3)		If storage is BINARY, indicates whether the column allows ANYSCHEMA (YES) or not (NO), else NULL
NONSCHEMA	VARCHAR2(3)		If storage is BINARY, indicates whether the column allows NONSCHEMA (YES) or not (NO), else NULL

Note:

You can check the contents of ALL_XML_SCHEMAS and ALL_XML_TABLES views to check if XDB is being used in the database. If there is anything there which is owned by any user other than XDB then you may be using the XDB feature.

You may see following standard schemas under EXFSYS, MDSYS, ORDSYS, and SYS, e.g. -

OWNER SCHEMA_URL
---------- --------------------------------------------------------------------
EXFSYS http://xmlns.oracle.com/rlmgr/rclsprop.xsd
EXFSYS http://xmlns.oracle.com/rlmgr/rulecond.xsd
MDSYS http://www.opengis.net/cartographicText.xsd
MDSYS http://www.opengis.net/gml/feature.xsd
MDSYS http://www.opengis.net/gml/geometry.xsd
MDSYS http://www.w3.org/1999/xlink/xlinks.xsd
MDSYS http://xmlns.oracle.com/spatial/georaster/georaster.xsd
ORDSYS http://xmlns.oracle.com/ord/meta/dicomImage
ORDSYS http://xmlns.oracle.com/ord/meta/exif
ORDSYS http://xmlns.oracle.com/ord/meta/iptc
ORDSYS http://xmlns.oracle.com/ord/meta/ordimage
ORDSYS http://xmlns.oracle.com/ord/meta/xmp
SYS http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd

These are standard schemas which relate to Oracle Intermedia EXIF metadata, Oracle GEORASTER, EXFSYS, Oracle Locator components and may appear in the list if you have any of these components installed. These can easily be recreated by running the standard supplied scripts.

Metalink Note: 558834.1: How To Re-register XML Schemas After XDB Has Been Re-installed?

Oracle data dictionary views

DBA_XML_SCHEMAS

Oracle 11gR1

DBA_XML_SCHEMAS describes all registered XML schemas in the database. Its columns are the same as those in ALL_XML_SCHEMAS.

Related Views

· ALL_XML_SCHEMAS describes the registered XML schemas accessible to the current user.

· USER_XML_SCHEMAS describes the registered XML schemas owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)		Owner of the XML schema
SCHEMA_URL	VARCHAR2(700)		Schema URL of the XML schema
LOCAL	VARCHAR2(3)		Indicates whether the XML schema is local (YES) or global (NO)
SCHEMA	XMLTYPE		XML schema document
INT_OBJNAME	VARCHAR2(4000)		Internal database object name for the schema
QUAL_SCHEMA_URL	VARCHAR2(767)		Fully qualified schema URL
HIER_TYPE	VARCHAR2(11)		Type of hierarchy for which the schema is enabled: · NONE · RESMETADATA · CONTENTS
BINARY	VARCHAR2(3)		Indicates whether the XML Schema is registered for binary encoding usage (YES) or not (NO)
SCHEMA_ID	RAW(16)		Opaque schema identifier (16 bytes)
HIDDEN	VARCHAR2(3)		Indicates whether the XML Schema has been deleted in hidden mode (YES) or not (NO)

Note:

You can check the contents of ALL_XML_SCHEMAS and ALL_XML_TABLES views to check if XDB is being used in the database. If there is anything there which is owned by any user other than XDB then you may be using the XDB feature.

You may see following standard schemas under EXFSYS, MDSYS, ORDSYS, and SYS, e.g. -

OWNER SCHEMA_URL
---------- --------------------------------------------------------------------
EXFSYS http://xmlns.oracle.com/rlmgr/rclsprop.xsd
EXFSYS http://xmlns.oracle.com/rlmgr/rulecond.xsd
MDSYS http://www.opengis.net/cartographicText.xsd
MDSYS http://www.opengis.net/gml/feature.xsd
MDSYS http://www.opengis.net/gml/geometry.xsd
MDSYS http://www.w3.org/1999/xlink/xlinks.xsd
MDSYS http://xmlns.oracle.com/spatial/georaster/georaster.xsd
ORDSYS http://xmlns.oracle.com/ord/meta/dicomImage
ORDSYS http://xmlns.oracle.com/ord/meta/exif
ORDSYS http://xmlns.oracle.com/ord/meta/iptc
ORDSYS http://xmlns.oracle.com/ord/meta/ordimage
ORDSYS http://xmlns.oracle.com/ord/meta/xmp
SYS http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd

These are standard schemas which relate to Oracle Intermedia EXIF metadata, Oracle GEORASTER, EXFSYS, Oracle Locator components and may appear in the list if you have any of these components installed. These can easily be recreated by running the standard supplied scripts.

Metalink Note: 558834.1: How To Re-register XML Schemas After XDB Has Been Re-installed?

Oracle data dictionary views

DBA_REFRESH_CHILDREN

Oracle 11gR1

DBA_REFRESH_CHILDREN lists all of the objects in all refresh groups in the database.

Related Views

· ALL_REFRESH_CHILDREN lists all the objects in refresh groups that are accessible to the current user.

· USER_REFRESH_CHILDREN describes the objects in all refresh groups owned by the current user.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the object in the refresh group
NAME	VARCHAR2(30)	NOT NULL	Name of the object in the refresh group
TYPE	VARCHAR2(30)		Type of the object in the refresh group
ROWNER	VARCHAR2(30)	NOT NULL	Name of the owner of the refresh group
RNAME	VARCHAR2(30)	NOT NULL	Name of the refresh group
REFGROUP	NUMBER		Internal identifier of refresh group
IMPLICIT_DESTROY	VARCHAR2(1)		(Y| N) If Y, then destroy the refresh group when its last item is subtracted
PUSH_DEFERRED_RPC	VARCHAR2(1)		(Y |N) If Y then push changes from snapshot to master before refresh
REFRESH_AFTER _ERRORS	VARCHAR2(1)		If Y, proceed with refresh despite error when pushing deferred RPCs
ROLLBACK_SEG	VARCHAR2(30)		Name of the rollback segment to use while refreshing
JOB	NUMBER		Identifier of job used to refresh the group automatically
NEXT_DATE	DATE		Date that this job will next be refreshed automatically, if not broken
INTERVAL	VARCHAR2(200)		A date function used to compute the next NEXT_DATE
BROKEN	VARCHAR2(1)		(Y |N) Y means the job is broken and will never be run
PURGE_OPTION	NUMBER(38)		The method for purging the transaction queue after each push. 1 indicates quick purge option; 2 indicates precise purge option
PARALLELISM	NUMBER(38)		The level of parallelism for transaction propagation
HEAP_SIZE	NUMBER(38)		The size of the heap

Oracle data dictionary views

DBA_FREE_SPACE

Oracle 11gR1

DBA_FREE_SPACE describes the free extents in all tablespaces in the database.

Note that if a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.

Related View

USER_FREE_SPACE describes the free extents in the tablespaces accessible to the current user.

Column	Datatype	NULL	Description
TABLESPACE_NAME	VARCHAR2(30)		Name of the tablespace containing the extent
FILE_ID	NUMBER		File identifier number of the file containing the extent
BLOCK_ID	NUMBER		Starting block number of the extent
BYTES	NUMBER		Size of the extent (in bytes)
BLOCKS	NUMBER		Size of the extent (in Oracle blocks)
RELATIVE_FNO	NUMBER		Relative file number of the file containing the extent

Oracle data dictionary views