Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Chapter 5 Oracle Tablespace Design

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:

  1. Oracle 7 - Bitmap Indexes

  2. Oracle8 - Locally Managed Tablespaces (LMTs)

  3. Oracle8i - Bitmap Segment Space Management  (ASSM)

  4. Oracle10g - Automatic Storage Management (ASM)

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 scans.

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 size?

Got Oracle Scripts?

BC 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 data dictionary.

Packed with more than 690 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.