Thursday, July 31, 2008

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

Tuesday, July 29, 2008

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

Monday, July 28, 2008

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