Transcript INDEX SEGMENTS

7202ICT Database
Administration
Lecture 7
Managing Database Storage
Part 2
Orale Concept Manuel Chapter 3 & 4
INDEX SEGMENTS
• B-TREE
• If indexes are created on a table, then an index segment is created
for each index.
• Index Segment Features
 Balanced tree indexes can quickly locate key values
 ROWID addresses (hexadecimal values) can provide direct access to the
data block
 Queries that reference indexes columns can be resolved in the index
without even touching the table data segment
 Index searches are an alternative to full table scans
 Index segments are physically separate and independent from their
associated table segments
 Each index can have its own storage parameters
 Indexes are generally much smaller than their associated table
 Indexes can be stored in a different tablespace for optimization
Example:
• Create an index on the EMP table
SQL> CREATE INDEX emp_ename /* Index name */
ON emp (ename) /* table and column name */
STORAGE (INITIAL 500K NEXT 500K
PCTINCREASE 0)
TABLESPACE
IDX_SPACE;
TEMPORARY SEGMENTS
• Temporary segments provide workspace for the RDBMS to sort
and join tables while performing complex searches.
• Temporary Segments
 Are created automatically by ORACLE to perform complex
operations such as joins, group by, creating an index, and anything
that requires sorting
 Are created in memory if enough memory space is available, or in
data files if not
 Created when needed on a per transaction basis
 Space is reclaimed at the end of the transaction by the SMON
background process
 Each tablespace may contain temporary segments
TEMPORARY SEGMENTS (cont)
 Space allocation for temporary segments is determined by the
DEFAULT STORAGE clause of the tablespace that the temporary
segments are being created in
 Dynamic extension of temporary tables during sort operations may
cause tablespace fragmentation
 The DBA may define which tablespace each user uses for
temporary segments, thus it is possible to manage the temporary
segments and even have a dedicated tablespace for them
 Temporary segments are not protected by use of the REDO LOG
since they pose no threat to data integrity
ROLLBACK SEGMENTS
• Rollback segment entries are written in a circular fashion for
rollback, read consistency, and recovery.
– Each Rollback Segment
 Consists of several rollback entries from multiple transactions
 Stores block information such as file and block ID, as well as
data as it existed before being modified
 Must be created under special circumstances and brought
online before being used by any transaction
 May grow in size due to large or long running transactions
 Will automatically shrink to OPTIMAL if extended beyond it
 Can be assigned to a transaction automatically or explicitly
Rollback Segment Storage Parameters





INITIAL
NEXT
MINEXTENTS
MAXEXTENTS
OPTIMAL
Parameter Notes
 OPTIMAL specifies the optimal size of a rollback
segment in bytes
 ORACLE de-allocates extents when a rollback segment
is larger than OPTIMAL
 PCTINCREASE cannot be specified for rollback
segments and is always zero
 MINEXTENTS must be at least 2 to accommodate the
segment being written to in a circular fashion
Example
• Create a rollback segment using the space utilization
parameters, then bring the rollback segment online.
SQL> CREATE ROLLBACK SEGMENT rbs01
TABLESPACE RBS
STORAGE (INITIAL 10K
NEXT 10K
OPTIMAL 20K MAXEXTENTS 121);
SQL> ALTER ROLLBACK SEGMENT rbs01 ONLINE;
SYSTEM SEGMENT
• The system segment contains data dictionary tables to
be loaded when the database is opened.
• System Segment
 Also referred to as the cache segment
 It cannot be read modified or dropped
 Exists in the STSTEM tablespace and is owned by the
user SYS
 Requires no action on the part of the DBA
 Usually needs less than 50 ORACLE blocks
TABLESPACES AND DATA FILES
• Data in an ORACLE database is logically stored in
tablespaces and physically stored in database files.
• An ORACLE can be subdivided into smaller logical
areas of space known as tablespaces.
Tablespaces
 Each tablespace contains one or more operating system
files
 Tablespace can be brought online while the database is
running
 Tablespace can be taken offline while the tablespace is
running, except for the SYSTEM tablespace which
must always remain online
 Objects created in a tablespace can never be allocated
space outside of their original tablespace
Useful Features of the Tablespace
 Control of space allocation and assigning space quotas
to users
 Control availability of data by taking individual
tablespaces online or offline
 Distribute data storage across devices to improve I/O
performance and reduce I/O against a single disk
 Perform partial backup and partial recovery operations
Note :
• A database always consists of at least one tablespace,
SYSTEM. Generally, additional tablespaces are added
to the database for increased control and long term
ease of maintenance.
• Essentially, ORACLE sees the entire database made up
of two types of tablespaces: SYSTEM and nonSYSTEM.
SYSTEM Tablespace
 Required in all databases for database operation
 Contains data dictionary information, definitions of
stored procedures, packages, and database triggers
 Contains the SYSTEM rollback segment
 Can contain user data if so desired
Non-System Tablespace
 Allows more flexibility in database administration
 Consists of:






rollback segments,
temporary segments,
application data,
application indexes,
user space,
and so forth
PHYSICAL DATABASE STRUCTURE
• The total amount of physical space allotted to ORACLE
database objects depends upon the size of the physical
operating system files created on behalf of each individual
tablespace.
 Each logical tablespace is physically made up of one or
more operating system files
 A segment, such as a table segment, can span multiple files
as long as those files belong to the same tablespace
Resolving Disk Contention
• Disk contention can be minimized, by separating
groups of segments that will contend for disk resources
among different tablespaces. Naturally, those
tablespace should not all map to data files on the same
disk.
To Minimize Disk Contention
 Separate dictionary segments from other segments
 Separate rollback segments from other segments
 Separate data segments from their corresponding index
segments
Data Dictionary Views
DISPLAYING EXTENT AND SEGMENT INFORMATION
• The data dictionary contains a set of views, which can be queried
to see how many extents and segments exist in the database.





DBA_EXTENTS
DBA_FREE_SPACE
DBA_SEGMENTS
DBA_TABLESPACES
DBA_DATA_FILES
Example :
The DBA_SEGMENTS view can be used to gain summary information about
extent allocation in the database. It is also possible to run reports on this view
to determine the level of fragmentation of a segment and the danger of it
reaching MAX_EXTENTS.
SQL> SELECT tablespace_name, count(*) SEGMENTS, SUM(bytes) BYTES
FROM dba_segments
GROUP BY tablespace_name;
TABLESPACE_NAME
-----------------APPL_DATA
RBS
SYSTEM
TEMP
•
SEGMENTS
------------10
2
108
0
BYTES
---------10240
40960
2240512
20480
As can be seen above, each row displays a tablespace name, the number of
segments contained in that tablespace and the total number of bytes
consumed by those segments from that tablespace.
More Examples :
• List the names of the columns in DBA_TABLESPACES.
SQL> DESCRIBE dba_tablespaces
NAME
---------------------TABLESPACE_NAME
INITIAL_EXTENT
NEXT_EXTENT
MIN_EXTENTS
MAX_EXTENTS
PCT_INCREASE
STATUS
NULL
--------
TYPE
--------------VARCHAR2(30)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(9)
More Examples :
• List the name and status of each tablespace.
SQL>
SELECT tablespace_name, status
FROM dba_tablespaces;
TABLESPACE_NAME
----------------SYSTEM
APPL_DATA
RBS
TEMP
STATUS
------------ONLINE
ONLINE
ONLINE
ONLINE
More Examples :
• List the names of the columns in the DBA_DATA_FILES view.
SQL> DESCRIBE dba_data_files
NAME
----------------FILE_NAME
FILE_ID
TABLESPACE_NAME
BYTES
BLOCKS
STATUS
NULL
--------
TYPE
-------------------VARCHAR2(257)
NUMBER
VARCHAR2(30)
NUMBER
NUMBER
VARCHAR2(9)
More Examples :
• List general information about the data files belonging to each tablespace.
SQL> SELECT file_name, file_id, tablespace_name, bytes
FROM dba_data_files;
FILE_NAME
----------------/exp1/ora1/data
/user_01.dbf
…
FILE_ID
TABLESPACE_NAME
BYTES
--------- ------------------ --------1
SYSTEM
10485760
More Examples :
• List the extents of free space in each tablespace.
SQL> SELECT *
FROM dba_free_space
ORDER BY file_id, block_id;
TABLESPACE_NAME
FILE_ID
BLOCK_ID
------------------ ---------------SYSTEM
1
900
SYSTEM
1
909
SYSTEM
1
1912
…
BYTES
BLOCKS
-------- ---------18432
4096
6572032
9
2
3209
More Examples :
• List the same free space information, as in the above example, using the
names of each data file instead of its file id.
SQL> SELECT free.tablespace_name, free.block_id,
free.bytes, free.blocks, df.file_name
FROM dba_free_space free, dba_data_files df
WHERE free.file_id = df.file_id
ORDER BY 1,2;
TABLESPACE_NAME
---------------SYSTEM
SYSTEM
SYSTEM
…
BLOCK_ID
BYTES
--------- -----900
18432
909
4096
1912 6572032
BLOCKS
-----9
2
32
FILE_NAME
----------user01.dbf
user01.dbf
user01.dbf
More Examples :
• List general information about all segments in the database, using the
DBA_SEGMENTS view.
SQL>
SELECT owner, segment_name, extents, max_extents
FROM dba_segments
ORDER BY 1,2;
OWNER
--------SCOTT
SYSTEM
SYSTEM
SYS
…
SEGMENT_NAME
-------------------EMP
RBS1
RBS2
UNDO$
EXTENTS
-------------5
20
2
50
MAX_EXTENTS
----------25
121
121
99