||Oracle Tips by Burleson
Chapter 5 Oracle Tablespace
Unlike the CODASYL databases of the 1980s,
today's Oracle databases allow for tables to grow according to
specified rules and procedures. In the Oracle model, one or more
tables may reside in a tablespace. A tablespace is a predefined
container for the tables that map to fixed files of a finite size.
Tables that are assigned to the tablespace may grow according to the
growth rules that are specified, but the size of the tablespace
supersedes the expansion rules. In other words, a table may have
more extents available according to the table definition, but there
may not be room in the tablespace to allocate those extents.
Over the past few years, Oracle has gradually
recognized the benefits of bitmap data structures. As Oracle has
evolved, we’ve seen the following progressive introduction of
bitmaps into the database engine:
Oracle 7 - Bitmap Indexes
Oracle8 - Locally Managed
Oracle8i - Bitmap Segment Space
Oracle10g - Automatic Storage
It’s important to note that these are optional
structures. Bitmap segment management (ASSM) in Oracle9i is
optional and can only be implemented at the tablespace level.
Existing systems can continue to use the traditional method of
freelist management. In Oracle10g, Locally Managed Tablespaces have
become the default.
This chapter will begin with a discussion of
Oracle data blocks and then review the segment storage parameters.
We will then explore the Oracle tablespace option and understand how
proper physical design can improve performance and manageability.
Sizing Oracle Data Blocks
It is very ironic that the Oracle developer must choose a blocksize
when the data warehouse is initially created--a time when knowledge
of system performance is very limited. While it is possible to use
the Oracle import/export utility to change block sizes, too little
attention is given to the proper sizing of database blocks. The
physical block size is set with the DB_BLOCK_SIZE parameter in the
init.ora file. While the default is to have 4 K blocksizes, many
Oracle developers choose at least 8 K blocksizes for large,
distributed data warehouses. Some DBAs believe that 16 K is the best
blocksize, even for OLTP systems that seldom perform full-table
Depending upon the host platform and operating
system, Oracle block sizes may be set from 2 K up to 32 K. The
Oracle OS manual will provide the acceptable ranges for your
operating system, but the generally accepted wisdom is to create
your database blocks as large as your operating system will allow.
Remember, minimizing disk I/O is one of the most important factors
in data warehouse tuning, and the more data that can be read in a
single I/O, the faster your warehouse will perform.
Disk I/O is the single most expensive and
time-consuming operation within an Oracle database. As such, the
more data that can be read in a single I/O, the faster the
performance of the Oracle database. This principle is especially
true for databases that have many reports that read the entire
contents of a table. For systems that read random single rows from
the database, blocksize is not as important--especially with
database clusters. An Oracle cluster is a mechanism whereby an owner
row will reside on the same database block as its subordinate rows
in other tables.
For example, if we cluster order rows on the
same block as their customer owners, Oracle will only need to
perform a single I/O to retrieve the customer and all of the order
rows. Of course, in a distributed database where joins take place
across different Oracle instances, clustering cannot be used. The
additional I/O will be required to read the rows individually.
Bear in mind that increasing the block size of
an Oracle database will also affect the number of blocks that can be
cached in the buffer pool. For example, if we set the
DB_BLOCK_BUFFERS init.ora parameter to 8 MB, Oracle will be able to
cache 1000 4K blocks, but only 500 8K blocks.
Smaller block size means latches for block
access (data and index) are held for shorter time, increasing
concurrency. For other workloads (e.g DSS) larger block sizes may
better suit the usage profile of the data.
Same for UNDO, for which Oracle used 8K blocks
in the benchmark. What about a situation where your table has large
rows (say 16K each) with small index entries (say a numeric primary
key)? Do you really want them both stored in blocks of the same
shares their personal arsenal of Oracle data dictionary
scripts in this comprehensive download. With decades of
experience using Oracle monitoring scripts and Oracle tuning
scripts, we shares our secrets for navigating the Oracle
Packed with more than 690 ready-to-use Oracle scripts, this is the
definitive collection for every senior Oracle DBA.
would take man-years to develop these scripts from scratch,
making this download the best value in the Oracle industry.