If you want to have your database sing on key then you need to get it tuned
Download
1 / 82

If You Want to Have your Database Sing on Key, Then You Need to Get It Tuned - PowerPoint PPT Presentation


  • 82 Views
  • Uploaded on

If You Want to Have your Database Sing on Key, Then You Need to Get It Tuned. Bryan Dickerson, Woolpert Jim Moening, ESRI. Agenda. How Does ArcSDE Work? Database Tuning (Oracle and SQL) Best Practices for Geodatabase Design ArcSDE Management Roles & Responsibilities. How Does ArcSDE Work?.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'If You Want to Have your Database Sing on Key, Then You Need to Get It Tuned' - jerica


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
If you want to have your database sing on key then you need to get it tuned

If You Want to Have your Database Sing on Key, Then You Need to Get It Tuned

Bryan Dickerson, Woolpert

Jim Moening, ESRI


Agenda
Agenda to Get It Tuned

  • How Does ArcSDE Work?

  • Database Tuning (Oracle and SQL)

  • Best Practices for Geodatabase Design

  • ArcSDE Management Roles & Responsibilities

Database Tuning Water/Wastewater DB Design Conference February 2005


How does arcsde work
How Does ArcSDE Work? to Get It Tuned

Database Tuning Water/Wastewater DB Design Conference February 2005


Arcsde data storage
ArcSDE Data Storage to Get It Tuned

  • A feature class (layer) is comprised of 3 tables in an ArcSDE database

  • Business table

  • Feature table

  • Spatial index table

Database Tuning Water/Wastewater DB Design Conference February 2005


Business table
Business Table to Get It Tuned

  • Contains attributes and a spatial column

  • Spatial column is a key used to relate to the feature and spatial index tables

Business table (Roads)

Feature table (F1)

Spatial Indextable(S1)

Database Tuning Water/Wastewater DB Design Conference February 2005


Feature table
Feature Table to Get It Tuned

  • Stores geometry including annotation and CAD features

  • Also stores attributes describing the geometry such as Area and Length

Business table (Roads)

Feature table (F1)

Spatial Indextable(S1)

Database Tuning Water/Wastewater DB Design Conference February 2005


Spatial index table
Spatial Index Table to Get It Tuned

  • Defines the grid range for each feature

  • Defines the shape envelope for each feature

Business table (Roads)

Feature table (F1)

Spatial Indextable(S1)

Database Tuning Water/Wastewater DB Design Conference February 2005


Arcsde storage model

Spatial to Get It Tuned

column

CENSUS_TRACTS

SHAPE

1

2

2

3

1

Business table

1

2

ArcSDE Storage Model

Coordinate

Information

F34

2

FID

POINTS

3

xy,xy,xy

1

. . . . .

S34

1

2

. . . . .

SP_FID

xy,xy,xy

GX

GY

1

1

1

. . . . .

xy,xy,xy

3

2

1

1

3

1

1

  • Feature table stores geometry

  • Spatial index table stores grid tiles and envelopes

  • Tables join on integer Feature ID column

2

1

2

3

1

2

1

2

1

2

2

1

Database Tuning Water/Wastewater DB Design Conference February 2005


Spatial index
Spatial index to Get It Tuned

  • Spatial index is used to improve performance of data retrieval

  • IBM DB2, Oracle, and SQL Server use grid tiles

    • Goal

      • Optimize the size of the grid tiles

    • Balance

      • Number of features referenced by each tile (features per tiles)

      • Number of tiles referencing each feature (tiles per feature)

  • Informix and Oracle Spatial use R-tree indexing

    • R-tree index constructs the index using the feature envelope

      • Do not need to add any parameters for building R-tree (built automatically)

Database Tuning Water/Wastewater DB Design Conference February 2005


Reducing work with a spatial index
Reducing Work With a Spatial Index to Get It Tuned

  • Clients use a spatial filter to reduce query results and speed up data retrieval

    • Example: Only fetch visible features for display

  • ArcSDE uses spatial index to reduce I/O

    • Eliminates need for full table scan

    • Same principle as RDBMS column index

Spatial filter

Database Tuning Water/Wastewater DB Design Conference February 2005


Spatial index1
Spatial Index to Get It Tuned

  • Spatial index uses the envelope (bounding box) of the features for fast searching

38,68

53,28

49.5,53.6

32,64

49,25

Envelopes for area and line features use min x,y and max x,y coordinates. The envelope for a point is the actual x,y coordinate.

Database Tuning Water/Wastewater DB Design Conference February 2005


Grid tile spatial index components
Grid Tile Spatial Index Components to Get It Tuned

49

ParcelsExample

48

73

74

75

76

77

  • Grid tiles

    • Two numbers in indexed columns

  • Shape envelope

    • Two coordinates (four numbers) in indexed columns

  • Shape

    • Many coordinates in binary column

A

Database Tuning Water/Wastewater DB Design Conference February 2005


How the grid tile spatial index works

RDBMS to Get It Tuned

ArcSDE server or client

How the Grid Tile Spatial Index Works

Goal: Avoid reading shapes for entire layer (full table scan)

Four stages of elimination

FILTER (SELECTION SHAPE)

LAYER

tile tile

1.

envelope envelope

2.

shape envelope

3.

shape shape

4.

Database Tuning Water/Wastewater DB Design Conference February 2005


Selecting a spatial index size
Selecting a Spatial Index Size to Get It Tuned

  • Size tiles based on average feature envelope

  • Grid tile size too small

  • S-table is too big

  • Many tiles referencing one feature

  • Grid 3x feature envelope

  • S-table smaller

  • Fewer tiles referencing one feature

  • Many rows take longer to search

  • Many tiles referencing one feature inefficient

    Balance tiles per feature against feature per tile

Database Tuning Water/Wastewater DB Design Conference February 2005


Arcsde command line tools are your friend
ArcSDE Command Line Tools Are Your Friend to Get It Tuned

sdelayer -o si_stats -l Parcels,shape -i esri_sde -s jayhawk -D demo

ArcSDE 8.2 Build 967 Thu Feb 28 22:31:11 PST 2002

Layer Administration Utility

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

Layer 6 Spatial Index Statistics:

Level 1, Grid Size 1326.14

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

| Grid Records: 6438 |

| Feature Records: 5099 |

| Grids/Feature Ratio: 1.26 |

| Avg. Features per Grid: 55.03 |

| Max. Features per Grid: 181 |

| % of Features Wholly Inside 1 Grid: 81.64 |

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

| Spatial Index Record Count By Group |

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

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

| Features: 5073 26 8 0 0 0 0 0 |

| % Total: 99% 1% 0% 0% 0% 0% 0% 0%|

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

Database Tuning Water/Wastewater DB Design Conference February 2005


Spatial index tile size considerations
Spatial Index Tile Size Considerations to Get It Tuned

  • Optimal size varies by average feature envelope

    • Recalculate index if feature class changes

  • Most feature envelopes should not exceed grid size

    • S-table becomes too large

    • Features are referenced by many tiles which is inefficient

  • Use multiple resolutions (multiple grids) for feature classes with highly-variable feature sizes

Database Tuning Water/Wastewater DB Design Conference February 2005


Making data editable register as versioned
Making Data Editable - Register As Versioned to Get It Tuned

  • Performed by data owner in ArcCatalog

  • Two tables are added to feature class

    • Additions table

    • Deletions table

Database Tuning Water/Wastewater DB Design Conference February 2005


How tables react to edits

Inserting to Get It Tuned

Adds table

Deletes table

ID

Name

SHAPE

ID

Citrus

3

Adds table

Deletes table

Deleting

ID

Name

SHAPE

ID

Citrus

2

3

Adds table

Deletes table

Updating

ID

Name

SHAPE

ID

Citrus

2

3

1

1

Roma

How Tables React to Edits

Business table

ID

Name

SHAPE

1

Roma

2

Palm

Database Tuning Water/Wastewater DB Design Conference February 2005


Raster data storage
Raster Data Storage to Get It Tuned

  • Raster data is stored in a similar fashion to vector data

  • 4 tables are used to reference a raster feature class

    • Business table

    • Raster bands table (stores bands for each image)

    • Raster auxiliary table (stores metadata for each raster band)

    • Raster blocks table (stores the actual raster in a tiled format)

  • Blocks table is most important as it is by far the largest

Database Tuning Water/Wastewater DB Design Conference February 2005


Pyramids
Pyramids to Get It Tuned

  • Pyramids can be built for a raster layer when loaded into ArcSDE

  • Multiple resolutions of the raster layer are stored in the database

  • At a zoomed out scale the lower resolution is displayed

  • As the user zooms in to the highest level of detail, the raster as it was originally loaded (its highest resolution) is shown

  • This significantly helps to speed display of raster layers in ArcGIS and ArcIMS

Database Tuning Water/Wastewater DB Design Conference February 2005


Raster blocks table sde blk 1
Raster Blocks Table (SDE_BLK_1) to Get It Tuned

  • Raster is divided into tiles containing many pixels and each tile is a row in the table

  • Each tile at each pyramid level has a row in the table

  • This table WILL get very large!

Database Tuning Water/Wastewater DB Design Conference February 2005


Database tuning for oracle and sql
Database Tuning for Oracle and SQL to Get It Tuned

Database Tuning Water/Wastewater DB Design Conference February 2005


Query response time
Query Response Time to Get It Tuned

  • When the client submits a query, the server must:

    • Analyze query statement

    • Fetch data

    • Transfer results to client over network

  • 2 biggest performance factors are network and disk I/O

    • Network I/O is database independent

    • Scanning large tables increases disk I/O

    • Fetching and transmitting large results increases disk and network I/O

Database Tuning Water/Wastewater DB Design Conference February 2005


Network i o
Network I/O to Get It Tuned

  • T1 line connecting a remote office to an ArcSDE database in a headquarters office can be tied up with one ArcGIS desktop performing normal operations

  • Solution to this is terminal emulation software such as Citrix or Terminal Server

Database Tuning Water/Wastewater DB Design Conference February 2005


Sql server basics
SQL Server Basics to Get It Tuned

  • Terminology is different than that for Oracle

  • A SQL Server instance is made up of many databases

  • SQL Server databases are a logical container for objects such as:

    • Users

    • Roles

    • Tables

    • Etc…

Database Tuning Water/Wastewater DB Design Conference February 2005


Sql server database
SQL Server Database to Get It Tuned

  • A SQL Server database is comprised of data files and log files

  • A database has, at minimum, one data file and one log file

Database

Logfile

Datafile

Database Tuning Water/Wastewater DB Design Conference February 2005


Sql server file groups
SQL Server File Groups to Get It Tuned

  • There can be multiple data files and log files and these can be stored in different places

  • A group of data files and/or log files is called a file group

Database

File group

Logfile

Logfile

Datafile

Datafile

Database Tuning Water/Wastewater DB Design Conference February 2005


Oracle basics
Oracle Basics to Get It Tuned

  • Terminology is different than that for SQL Server

  • An Oracle database is made up of many table spaces

  • A table space can be made up of many data files. Also need to be aware of redo log files and control files

  • Table spaces are logical containers for objects such as:

    • Tables

    • Stored procedures

  • Roles and users are stored at the database level and not the table space level

Database Tuning Water/Wastewater DB Design Conference February 2005


Dbtune file table
DBTune File/Table to Get It Tuned

  • Actually exists as a table in the SDE database or table space

  • Contains storage parameters for tables and feature classes

  • Whenever a new feature class is created and data is loaded, the ArcSDE server process reads the DBTune table for information on how to store these new objects

  • There can be different parameters for different layers or data types and these are specified via keywords

Database Tuning Water/Wastewater DB Design Conference February 2005


Storing layers in the sde database sql or table space oracle
Storing Layers in the SDE Database (SQL) or Table Space (Oracle)

  • Do not store layers in the SDE database or table space

  • Leave the SDE database or table space for ArcSDE application use (managing what layers are where, versioning, etc.)

  • Otherwise this can significantly slow down data retrieval as this database or table space will become large

Database Tuning Water/Wastewater DB Design Conference February 2005


Multiple database logins
Multiple Database Logins (Oracle)

  • Do not have all your editors in your organization use the same login

  • Each database user has a log table in the SDE database or table space that tracks a user’s actions

  • If all users use one login, all their actions will write to one table and this table will grow very large very fast

  • Doing this also prohibits from restricting user access on a layer by layer basis

Database Tuning Water/Wastewater DB Design Conference February 2005


Disk i o contention
Disk I/O Contention (Oracle)

  • This is the number one way to slow down an ArcSDE database

  • As users view or query data remember that, at minimum, ArcSDE is working through the business tables, feature tables, and spatial index tables for each layer in the map

  • If the layers are versioned, ArcSDE is also working through the adds and deletes tables

Database Tuning Water/Wastewater DB Design Conference February 2005


Preventing disk i o contention
Preventing Disk I/O Contention (Oracle)

  • Use many disks and spread the tables and logs across those disks

  • In SQL Server, make use of file groups and spread the data and log files across different physical disks

  • In Oracle, make use of different table spaces and store the different tables (business, feature, and index) in the different table spaces on the different disks

Database Tuning Water/Wastewater DB Design Conference February 2005


Example sql server configuration
Example SQL Server Configuration (Oracle)

Operating System

Pagefile

ArcSDE Software

SQL Server Software

System database

SDE database

Business table file group

Disk 1

Disk 3

Feature table file group

SDE Transaction log

Raster table file group

Spatial index file group

Disk 4

Disk 2

Database Tuning Water/Wastewater DB Design Conference February 2005


Example oracle configuration
Example Oracle Configuration (Oracle)

Operating System

Pagefile/Swap space

ArcSDE Software

Oracle Software

Business table space

Primary control files

Disk 1

Disk 3

Feature table space

Redo log

Raster table space

Spatial index table space

Secondary control files

Disk 4

Disk 2

Database Tuning Water/Wastewater DB Design Conference February 2005


Disk i o raid
Disk I/O - RAID (Oracle)

  • RAID = Redundant Array of Independent (or Inexpensive) Disks

  • Different levels of RAID

    • RAID 0 = Striping. Data is striped across drives. Offers good performance but no fault tolerance.

    • RAID 1 = Mirroring. Data is written or mirrored to a second disk. Simple fault tolerance and performs better than a single disk.

    • RAID 5 = Striping w/ parity. Data is striped and parity calculations are distributed among disks. Provides good READ performance and excellent fault tolerance.

    • RAID 0 + 1 = Mirroring and striping. Excellent read/write performance and good fault tolerance.

Database Tuning Water/Wastewater DB Design Conference February 2005


Other things you can do
Other Things You Can Do… (Oracle)

  • Reduce amount of data processed for faster queries

  • Provide a filter to limit number of rows

  • Index tables and layers to reduce searching

    • If you know users will search or query on particular fields, index those fields ahead of time.

  • ArcSDE is fastest for small, indexed queries!!!

Database Tuning Water/Wastewater DB Design Conference February 2005


Arcsde performance tuning
ArcSDE Performance Tuning (Oracle)

  • Optimize Workflow

  • Data Loading

  • Data Maintenance

  • Reconcile/Post/Compress

  • Database Configuration

  • Keep it simple

  • Increase default cache sizes

  • Editing Operations

  • Use of edit cache is CRITICAL

  • Index Management

  • Regularly rebuild indexes

  • Collect Performance Stats

  • Establish baseline

  • Use for performance validation

  • Versioning Methodology

  • Reconcile performance costs

  • Using versioning to model history or alternatives

  • Reconcile/post/compress procedures

  • Database Design

  • Cost of complex data models

    • While Editing

    • Recursive Relationships

    • Feature linked annotation

    • Types of labels

  • Monitor Table Statistics

  • Adds and Deletes tables

  • SDE.state_lineages, states

A good DBA is ESSENTIAL!

Database Tuning Water/Wastewater DB Design Conference February 2005


Tools for managing arcsde databases
Tools for Managing ArcSDE Databases (Oracle)

  • Database vendor provided tools

    • SQL Server Enterprise Manager

    • SQL Server Query Analyzer

    • Oracle DBA Studio

  • ESRI provided tools

    • ArcCatalog

    • ArcSDE command line tools

  • Other tools

    • StorageVisitron (available from ArcScripts)

    • Version Manager ArcObjects sample (Developer Kit)

Database Tuning Water/Wastewater DB Design Conference February 2005


Excellent instructor led classes
Excellent Instructor-led Classes (Oracle)

  • ArcSDE Administration for SQL Server

  • ArcSDE Administration for Oracle

  • System Architecture Design for GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Best practices for geodatabase design

Seamless Data Structure (Oracle)

Topology

Custom features

Integrity: Domains, Subtypes, Relationship classes

Geodatabase

Disconnected Editing*

Versioning*

Multi-User Editing*

Scalability*

*ArcSDE Geodatabase (Enterprise)

Best Practices for Geodatabase Design

Database Tuning Water/Wastewater DB Design Conference February 2005


Support esri com project center
support.esri.com – Project Center (Oracle)

Database Tuning Water/Wastewater DB Design Conference February 2005


Support esri com project center cont
support.esri.com – Project Center (cont.) (Oracle)

Database Tuning Water/Wastewater DB Design Conference February 2005


Database design
Database Design (Oracle)

Database Tuning Water/Wastewater DB Design Conference February 2005


Data design planning
Data Design Planning (Oracle)

Database Tuning Water/Wastewater DB Design Conference February 2005


Geodatabase
Geodatabase (Oracle)

Database Tuning Water/Wastewater DB Design Conference February 2005


Data models tips and tricks
Data Models Tips and Tricks (Oracle)

Database Tuning Water/Wastewater DB Design Conference February 2005


Steps to designing geodatabases
Steps to designing geodatabases (Oracle)

  • Conceptual Design

    • Identity the information products to be produced with your GIS.

    • Identify the key thematic layers based on your information requirements.

    • Specify the scale ranges and spatial representations for each thematic layer.

    • Group representations into datasets.

Database Tuning Water/Wastewater DB Design Conference February 2005


Steps to designing geodatabases1
Steps to designing geodatabases (Oracle)

  • Logical Design

    • Define the tabular database structure and behavior for descriptive attributes.

    • Define the spatial properties of your datasets.

    • Propose a geodatabase design.

Database Tuning Water/Wastewater DB Design Conference February 2005


Steps to designing geodatabases2
Steps to designing geodatabases (Oracle)

  • Physical Design

    • Implement, prototype, review, and refine your design.

    • Design work flows for building and maintaining each layer.

    • Document your design using appropriate methods.

Database Tuning Water/Wastewater DB Design Conference February 2005


Best practices
Best Practices (Oracle)

  • The schema design phase of your geodatabase implementation is critical to the ultimate performance of your GIS system.

  • Geodatabases can contain feature datasets, feature classes, relationship classes, geometric networks, topologies, feature-linked annotation, and other specialized abstractions.

  • Careful consideration of the cost, limitations, and true purpose of each of these abstractions is required to avoid creating a geodatabase that may not perform well in either a single- or multi-user environment.

Database Tuning Water/Wastewater DB Design Conference February 2005


Best practices feature datasets
Best Practices – Feature Datasets (Oracle)

A feature dataset is a container for feature classes that share the same spatial reference, along with relationship classes, geometric networks, and topologies.

  • Feature classes edited together in ArcMap can be grouped into feature datasets

  • Feature classes not typically edited together should be segregated into separate feature datasets or geodatabases

  • Feature classes that participate in a geometric network or topology must be grouped in the same feature dataset.

  • Feature classes can participate in no more than one topology OR geometric network.

Database Tuning Water/Wastewater DB Design Conference February 2005


Best practices subtypes
Best Practices – Subtypes (Oracle)

Subtypes let you apply a classification system within a feature class and apply behavior through rules. Subtypes are essential to good design because they help reduce the number of feature classes and improve performance.

  • Let you control every configurable behavior:

    • Attribute rules

    • Relationship rules

    • Network Connectivity Rules

    • Topology Rules

  • Reduce the number of feature classes by creating collections of features within a single feature class

Database Tuning Water/Wastewater DB Design Conference February 2005


Best practices relationship classes
Best Practices – Relationship Classes (Oracle)

Use relationship classes for referential integrity persisted in the geodatabase, on-the-fly relates for editing performance, and joins for labeling and symbology.

  • Messaging occurs between related objects when querying or editing these objects. This incurs overhead in the application. Composite relationship require more messaging, and thus more overhead, than simple relationships.

  • Relationships can only be defined between objects in the same geodatabase.

  • Still requires a join for SQL query, labeling, or symbology.

  • When modeling spatial relationships, you will gain efficiency in your data creation and maintenance procedures by first thinking about how to use geodatabase topologies, geometric networks, shared-edge editing, and geospatial operators in your data model before resorting to relationship classes.

Database Tuning Water/Wastewater DB Design Conference February 2005


Best practices geometric networks
Best Practices – Geometric Networks (Oracle)

A geometric network is a system of edges (lines) and junctions (points) that convey objects. They are a mechanism to represent connectivity between edges and junctions where they connect.

  • Geometric networks provide fast network tracing capabilities and maintain connectivity during editing.

  • The maintenance of the logical network adds overhead to editing and version management.

  • The greater number of feature classes participating in a network, the greater amount of overhead will be incurred in maintaining the logical network and connectivity during editing.

Database Tuning Water/Wastewater DB Design Conference February 2005


Best practices topologies
Best Practices – Topologies (Oracle)

A geodatabase topology lets you accurately model geometric relationships between features. Topology can be considered a special type of relationship among features. Your data model will be more efficient if you can associate features by topology rather than with a relationship class. But remember that a feature class cannot participate in a topology and a geometric network at the same time.

  • Topologies manage a set of rules and errors associated with the violation of the rules.

  • A geodatabase topology can incur editing overhead and work flow considerations.

  • The greater number of feature classes and rules in a topology, then greater amount of overhead for the management of the rules and errors within a topology.

Database Tuning Water/Wastewater DB Design Conference February 2005


Best practices annotation and labeling
Best Practices – Annotation and Labeling (Oracle)

Choosing whether to cartographically edit or automatically place text is an important decision. It depends on the quality of source data, how much effort is to be expended, and the desired map quality.

Database Tuning Water/Wastewater DB Design Conference February 2005


Best practices design tips
Best Practices – Design Tips (Oracle)

  • Build on your existing GIS designs

    • Most existing designs are suitable for moving forward. You can build on what has worked in the past and find new capabilities than can improve efficiency.

  • Use generic geodatabase types when feasible

    • Combining generic data structures with rich GIS tools provide the best solutions to scale and support multiple users and applications.

    • Leverage the ArcGIS software logic as much as possible and only use customized GIS data structures as a last resort.

  • Integrate independent feature classes using topology

    • Many users will find opportunities to use topologies in the geodatabase. This can create significant increases in efficiency for customization, data maintenance, and user productivity.

Database Tuning Water/Wastewater DB Design Conference February 2005


Best practices design tips1
Best Practices – Design Tips (Oracle)

  • Combine GIS design concepts with traditional relational database design methods

    • Both RDBMS and GIS design methodologies are critical for good GIS design. One is not sufficient without the other. Learn to use and apply both techniques.

  • Prototype and pilot your geodatabase design

    • Prototyping a design using personal geodatabases, ArcMap, and ArcCatalog is a very effective option for creating an efficient design process.

    • During the final stages of design, you will want to test scalability and work flows that represent the work your organization will perform. Use this activity to make final adjustments to your design as necessary.

Database Tuning Water/Wastewater DB Design Conference February 2005


Arcsde management roles responsibilities

ArcSDE (Oracle)

RDBMS

ArcSDE Management Roles & Responsibilities

Database Tuning Water/Wastewater DB Design Conference February 2005


Support esri com project center1
support.esri.com - Project Center (Oracle)

Database Tuning Water/Wastewater DB Design Conference February 2005


Support esri com project center cont1
support.esri.com - Project Center (cont.) (Oracle)

Database Tuning Water/Wastewater DB Design Conference February 2005


Project center skill development
Project Center - Skill Development (Oracle)

Database Tuning Water/Wastewater DB Design Conference February 2005


Arcsde management roles responsibilities1
ArcSDE Management Roles & Responsibilities (Oracle)

  • The following discussion outlines the major roles and responsibilities involved in deploying your spatial data and GIS applications throughout your enterprise.

  • Two Broad Categories for ArcSDE Roles & Responsibilities:

    • Administration

    • Development

Database Tuning Water/Wastewater DB Design Conference February 2005


Administration tasks
Administration Tasks (Oracle)

Database Tuning Water/Wastewater DB Design Conference February 2005


Arcsde service management
ArcSDE Service Management (Oracle)

Responsibility:

  • Manage the ArcSDE service including:

    • Starting and stopping the service

    • Establishing a recovery process, if the service stops

    • Establishing a troubleshooting process

    • Remedy an ArcSDE service that will not restart

    • Fail-over

      Requirements to do the Job:

  • Must have Administrative or Power User rights on the service host

  • Must have access to the Windows Event Log

  • Must have Administrative access to the DBMS to diagnose database changes or problems

  • Must have knowledge of IT policies and methodologies

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Server load management
Server Load Management (Oracle)

Responsibilities:

  • Monitor and remedy server load

  • Monitor server load or automate the monitoring of the server load

  • Create framework for alerts

  • Diagnose which processes and operations are overloading the server

  • Remedy unnecessary load, like a runaway process

  • Build plan(s) to overcome overloaded server

  • Potentially build fail-over infrastructure of mission critical server

    Requirements to do the Job:

  • Access to system monitoring tools (e.g. PerfMon, Task Manager)

  • Must have Administrative or Power User rights on the host

  • Must have knowledge of IT policies and methodologies

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Arcsde service anomaly management
ArcSDE Service Anomaly Management (Oracle)

Responsibility:

  • Manage the ArcSDE service if unforeseen troubles occur

    Requirements to do the Job:

  • Must have Administrative or Power User access to the service host

  • Must have access to the Windows Event Log to diagnose problems

  • Must have access to a technical support group to diagnose and remedy problems

  • Must have Administrative access to the DBMS to diagnose database problems

  • Must have knowledge of IT policies and methodologies

  • Must be able to troubleshoot ArcSDE service anomalies

  • Must be able to understand ArcSDE error logs to help resolve problems

    Owner: IT or GIS

    Notes:

  • Timely support required and is part of the criteria for choosing the staff

Database Tuning Water/Wastewater DB Design Conference February 2005


Spatial database access
Spatial Database Access (Oracle)

Responsibilities:

  • Manage access to the spatial database in ArcSDE, including:

    • Create new users to a spatial database

    • Maintain administration user

    • Create a procedure to support ArcSDE/RDBMS standard security (i.e. ArcSDE does not support integrated security)

    • Prepare and enforce procedure to manage ArcSDE clients (e.g. individual logins/roles)

    • Lock down command line administration tools

      Requirements to do the Job:

  • Must have administrative access to RDBMS to create logins

  • Must have administrative access to RDBMS spatial databases

  • Must have the ability to control the use of the ArcSDE admin tools (e.g. sdemon)

  • Must know how to enforce RDBMS data access

  • Must have knowledge of IT policies and methodologies

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Spatial database recovery
Spatial Database Recovery (Oracle)

Responsibility:

  • Establish recovery policy and procedures including:

    • Frequency (incremental, differential)

    • Archive location

    • Versioning

    • Recovery medium (i.e. backup vs. replication)

  • Enforce recovery policy and procedures

  • Automate recovery procedure where practical

    Requirements to do the Job:

  • Must have administrative access to SQL Server to create and maintain recovery procedure

  • Must know how to create and enforce SQL Server recovery procedures

  • Must have knowledge of ArcSDE layer schema to recover correct database objects

  • Must have knowledge of IT policies and methodologies

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Replicate spatial data
Replicate Spatial Data (Oracle)

Responsibilities:

  • Configure ArcSDE replication facilities (e.g. f and s tables)

  • Establish appropriate replication for business needs (e.g. transactional, snapshot, merge)

  • Implement replication needs

    Requirements to do the Job:

  • Must have administrative access to RDBMS to create and maintain replication configurations

  • Must know how to create and enforce RDBMS replication

  • Must have knowledge of ArcSDE layer schema to replicate correct database objects

  • Must have knowledge of IT policies and methodologies

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Tune arcsde layers
Tune ArcSDE Layers (Oracle)

Responsibilities:

  • Configure a spatial index for a given layer

  • Measure and monitor the performance of the spatial searches critical to the business needs of the organization (i.e. data maintenance, custom products, general products)

  • Maintain performance and Quality of Service as new spatial applications arrive, data and maintenance grows, and the user base increases

    Requirements to do the Job:

  • Must know how to adjust a spatial index of an ArcSDE layer

  • Must know how to measure the performance of a spatial search in a given application

  • Must know Quality of Service requirements

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Tune server and network infrastructure
Tune Server and Network Infrastructure (Oracle)

Responsibilities:

  • Configure the ArcSDE instance parameters for the ArcSDE server

  • Measure and monitor the performance of the ArcSDE server instance, and the anticipated needs (i.e. data maintenance, custom products, general products, usage growth)

  • maintain performance and quality of service as new users and applications arrive, data and maintenance grows, and the user base increases

    Requirements to do the Job:

  • Must know Quality of Service requirements

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Arcsde upgrade management
ArcSDE Upgrade Management (Oracle)

Responsibilities:

  • Ensure ArcSDE service version is up to date, and supports existing and new application functionality

  • Ensure ArcSDE compatibility with applications (i.e. MapObjects, ArcIMS, ArcGIS)

  • Install and configure upgrades to ArcSDE service and its application clients

  • Support and remedy incompatibility issues

  • Implement ArcSDE upgrades (service packs, sdeupgrade)

  • Install and configure upgrades of ArcSDE dependencies (e.g. DBMS, access libraries, operating system)

    Requirements to do the Job:

  • Must be able to monitor and maintain the versions of ArcSDE and the applications it supports

  • Must be able to monitor and maintain the versions of ArcSDE dependent technologies (e.g. operating system, DBMS, etc.)

  • Must have administrative privileges on ArcSDE server

  • Must have administrative privileges on ArcSDE DBMS

  • Must have knowledge of IT policies and methodologies

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Research improvements in data management
Research Improvements in Data Management (Oracle)

Responsibilities:

  • Continually investigate how to improve spatial data management

  • Continually test and scrutinize new spatial data management tools, techniques, and technologies

  • Investigate new data management techniques (e.g. DBMS abstract data types)

  • Investigate new spatial analysis to improve overall the organizations effectiveness

    Requirements to do the Job:

  • Must have knowledge of common GIS tools, techniques, and modern technologies

  • Must have knowledge of common spatial data maintenance issues and requirements

  • Must have knowledge of common spatial data access issues and requirements

  • Must have knowledge of GIS services and practices

  • Must have a willingness to try new things

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Development tasks
Development Tasks (Oracle)

Database Tuning Water/Wastewater DB Design Conference February 2005


Develop tools and workflow to maintain changes to spatial data
Develop Tools and Workflow to Maintain Changes to Spatial Data

Responsibilities:

  • Translate related changes in business data to changes in spatial data (e.g. city annexed 2 new parcels)

  • Use the right GIS tool and approach to maintain the spatial data

  • Automate data maintenance where practical

  • Support automation (tools or workflow)

  • Integrate new GIS tools and techniques to maintain the spatial data

    Requirements to do the Job:

  • Must have knowledge of the functional capabilities of ESRI client applications

  • Must have knowledge of current software engineering policies and methodologies

  • Must have knowledge of GIS policies and methodologies

  • Must have knowledge of GIS discipline (e.g. coordinate systems, topology)

  • Must have knowledge of spatial editing issues (e.g. topology, spatial processing)

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Develop custom gis solutions
Develop Custom GIS Solutions Data

Responsibilities:

  • Understand and translate specific business need to GIS analysis perspective

  • Identify the right tool and spatial data to deliver that business need

  • Develop spatial solution to satisfy that business need

  • Test spatial solution

  • Deliver or deploy spatial solution

    Requirements to do the Job:

  • Must have the ability to translate general business needs to spatial processing requirements

  • Must have knowledge of the functional capabilities of ESRI client applications

  • Must have knowledge of current software engineering policies and methodologies

  • Must have knowledge of GIS policies and methodologies

  • Must have knowledge of GIS discipline (e.g. coordinate systems, topology)

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Develop general gis applications
Develop General GIS Applications Data

Responsibilities:

  • Develop GIS application for general business needs (e.g. “show all the hydrants inspected this year on a map”)

  • Test GIS application for general business needs

  • Deploy GIS application for general business needs

  • Maintain GIS application for general business needs

    Requirements to do the Job:

  • Must have knowledge of the functional abilities and limitations in ESRI client applications (e.g. ArcObjects)

  • Must have knowledge of current software engineering policies and methodologies

  • Must have knowledge of GIS policies and methodologies

  • Must have knowledge of GIS discipline (e.g. coordinate systems, topology)

  • Must have knowledge of IT policies and methodologies

  • Must know how to deploy applications

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Support deployed gis applications
Support Deployed GIS Applications Data

Responsibilities:

  • Support users with technical issues about GIS applications (e.g. “help desk”)

  • Maintain the data sources for GIS applications

  • Troubleshoot functional issues of a GIS application

  • Support web based spatial applications (if web based mapping services apply)

    Requirements to do the Job:

  • Must have the ability to install and uninstall applications and their dependencies

  • Must have knowledge of IT policies and methodologies

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Spatial data design
Spatial Data Design Data

Responsibility:

  • Determine the appropriate layout of spatial data layers, including:

    • Organize by entity type (e.g. points, lines, polygons)

    • Organize by functional analysis (e.g. SQL queries)

    • Organize by normalization principles

    • Organize by maintenance (i.e. minimize maintenance effort)

    • Organize by spatial data vendors (e.g. Assessor changes, CAD structure)

      Requirements to do the Job:

  • Must know spatial data maintenance workflow in detail

  • Must know how the spatial data will be used (i.e. map functionality and products)

  • Must know basic principles of normalization and database objects

  • Must know ArcSDE layer schema

    Owner: IT or GIS

Database Tuning Water/Wastewater DB Design Conference February 2005


Thank you questions

Thank you! DataQuestions?


ad