International Archives of the Photogrammetry, Remote Sensing and Spatial Information Sciences, Vol XXXV, Part B4. Istanbul 2004
Table3. The Analysis Result of the Experiment
Along with the Raster Block Dimension increasing the Number
of Data Block of Table and the Number of Data Block of Index
have a change in this experiment. At the same time the need of
the disk space is different. So we must choose one proper
Raster Block Dimension to save the raster data. Because of
spending too much disk space Raster Block Dimension of
64*64 can't be used. Though the Raster Block Dimension of
256*256 needs small disk space. The great Number of Data
Block Per Row will affect the performance of database
operation. The Raster Block Dimension of 32*32 may be better.
But too much rows will waste a lot of time on indexing. So the
Raster Block Dimension of 128*128 is the better one in this
experiment.
In dictionary-managed tablespace, in order to decrease the
conflict among extents the proper number of the extents is 1000,
and the maximum is 10000. The large table objects are not fit
for the dictionary-managed tablespace. We usually use the
tablespace that manages its extents locally to save the large
table objects. The initial extent and next extent is in the same
size defined by the tablespace. So the storage parameter in the
dbtune.sde file is configured as following.
BLK_STORAGE "PCTFREE 10 PCTUSED 90
INITRANS 4 STORAGE ( MINEXTENTS 1 MAXEXTENTS
1000 PCTINCREASE 0 FREELISTS 4)"
4.1.2 Table Object of Feature Dataset
The table objects of the Feature datasets are smaller than the
raster datasets’ in this spatial database. So the
dictionary-managed tablespace is used. According to the
quantity of the data we define the initial extent and next extent
size. For an example, the storage parameter in the dbtunc.sde
file is configured as following.
B STORAGE "PCTFREE 10 PCTUSED 90 INITRANS 4
TABLESPACE DLGS STORAGE (INITIAL 5M NEXT 400K
FREELISTS 4 MINEXTENTS 1 PCTINCREASE 0)"
F STORAGE "PCTFREE 10 PCTUSED 90 INITRANS 4
TABLESPACE DLGF STORAGE (INITIAL 19200K NEXT
800K FREELISTS 4 MINEXTENTS 1 PCTINCREASE 0)"
S STORAGE "PCTFREE 10 PCTUSED 90 INITRANS 4
TABLESPACE DLGS STORAGE (INITIAL 9600K NEXT
800K FREELISTS 4 MINEXTENTS 1 PCTINCREASE 0)"
4.2 Oracle Database Configuring and Tuning"!
The oracle and ArcSDE have a detail description about the
database initial parameters, and give the reference. But we need
to configure and tune the parameter for our use.
4.2.1 System Global Area (SGA)
In general the size of the SGA is no more than 2/3 physical
memory of the server machine. SGA contains four parts, Data-
base Buffer Cache, Redo Log Buffer, Shared Pool and Large
Pool.
4.2.1.1 Shared Pool: Shared Pool consists of Data Dictionary
Cache, Library Cache and User Global Arca.
€ Data Dictionary Cache keeps information about diction-
ary objects. Eventually, the database should reach a "steady
374
state" in which the most frequently used dictionary data is in
the cache. At this point, very few cache misses should occur.
To tune the cache, make the ratio of getmisses and gets is less
than 15% in the VSROWCACHE view.
e Library Cache stores shared SQL and PL/SQL areas. If a
user issues a statement that is already in the cache, the Oracle
server can use the cached version without having to reparse it.
To tune the cache, make the keywords gethitratio is more than
15% in the VSLIBRARYCACHE view. And if an execute call
for a SQL statement is done and the shared SQL area contain-
ing the parsed representation of the statement has been deallo-
cated from the library cache to make room for another state-
ment or because the objects the statement refers to have been
invalidated, the Oracle server implicitly reloads the statement
and therefore reparses it. The number of reloads is counted for
each of these namespaces. The keywords reloads should be less
than 1% in the VSLIBRARYCACHE view.
€ User Global Area keeps information about multithreaded
connections. The total memory requirement for the multi-
threaded server is no larger than if you use dedicated servers.
Y ou may need to increase SHARED. POOL | SIZE, but your
private user memory is less.
In this spatial database the initial parameter shared. pool-
size is 73400320 bytes. We do an analysis to assure actually
size we used in share pool.
e The SHARABLE, MEM For stored objects such as
packages and views is 26301236 bytes.
€ The sharable_memery for SQL statements restricted by
frequently issued statements is 7301304 bytes, though this will
not include dynamic SQL.
e [n a test environment, you can measure shareable memo-
ry by selecting the number of open cursors for a test user. You
multiply the resulting value by the total number of users. The
sharable memory for the user open cursor is 47500 bytes.
As mentioned above, the actually size of the total memory is
32M. Then plus 2095-3096 of the actually size for unexpectedly
use. The sharable memory of 41.6M is enough for this applica-
tion.
4.2.1.2. Database Buffer Cache: The Buffer Cache holds
copies of the data blocks from the data files. ARCSDE suggest
the size of Database buffer cache is "!:
DB. BLOCK, BUFFERS-
( Physical RAM x © -shared. pool size - log. buffer) x (90%)
db block. size
To tune the cache, make the Buffer Cache Hit Ratio is greater
than 90%.
Buffer Cache Hit Ratio =
L- physical reads
db block gets + consistent gets
Interne
where
are the
In this
is 136:
42.1.3
redo ¢
buffer
definit
circule
over tl
disk. L
alway:
SECO
the v3
space
occur.
VSSY
walts
the en
In this
51200
4.2.2 |
À Pro
data a
backg
specif
DB_F
4.2.3 \
As Or
Sort 7
transfi
the le
tempo
SORT
sorts
VSSY
10 pei
the so
In
SORT
In pre
data |
tuning
impor
chang
we ne
REFI
jt
Mode.
ESRI
[2] E
Mana