Planning Database Creation
Good databases are well planned. They are designed to suit their purpose. This section outlines some important planning considerations before starting database creation.
Planning and Asking the Right Questions
Creating a robust, high-performance database requires careful thought and planning on the part of the DBA; planning and asking questions go hand-in-hand. You cannot plan well if you do not ask the right questions. Asking the right questions is often more important than being able to find precise answers to each question. Often, precise answers do not exist. Even if you are unable to answer all questions precisely, however, the act of asking the questions and thinking about them forces a more considered introspection of the factors involved in the process of planning and decision making. The following table presents some of the questions you should ask and the related decision areas.
Asking and thinking about these, and other such questions, can go along way in helping you design and build a database that is better suited to the tasks it will be required to perform. Several key decisions are required to be made as part of the process of creating the database. Some of these decisions, such as selecting an appropriate data block size for the database and configuring initial sizing parameters for the data dictionary objects, affect overall performance and operation for the life of the entire database. These and other such crucial decisions are examined in detail in the following sections in this chapter.
How to Determine an Appropriate Data Block Size
Perhaps the most important decision you face before creating a database is selecting an appropriate data block size for the database. Once this decision is made and the database is created, it is irretrievable. The data block size can neither be increased nor decreased for the life of the database. Data block size is specified by the database initialization parameter DB_BLOCK SIZE. Creating a database parameter file and specifying parameters is covered in detail in a subsequent section in this chapter.
To make an intelligent decision, you should understand exactly what a data block is and what role it plays in the operation of the database. At its most granular level, Oracle organizes and manages all database storage in data blocks. A data block is the smallest unit of storage and the smallest unit of I/O in the database. It is important to note that Oracle data blocks are different from operating system blocks, which are the smallest data units at the operating system level. When data needs to be fetched from disk, Oracle reads data in multiples of Oracle data blocks. A database's data block size thus determines the least unit of data that can be read from or written to disk. Data block size has important I/O implications for database operation and performance. The following set of guidelines is helpful in selecting an appropriate data block size.
Although Oracle handles data in terms of Oracle data blocks, the operating system (O5) executes operations such as disk I/O in terms of operating system blocks. To keep I/O as efficient as possible, the data block size you choose for the database must always be a multiple of the operating system block size. For example, if, as on some UNIX systems, the OS block size is set to 512 bytes, whatever data block size you choose must be a multiple of 512 bytes. Data block size is further limited by minimum and maximum allowable values. On all operating systems, the data block size must be a minimum of 2,048 bytes or 2K. The maximum allowable data block size depends on the type of operating system with a maximum limit of 32K in Oracle8i.
How the database is to be used and accessed is an important determinant in selecting the data block size. Decision support or online analytical processing (OLAP) applications such as those used for data warehousing typically involve large, sequential read operations which benefit from a larger data block size because more data can be read with lesser I/O overhead. On the other hand, online transaction processing (OLTP) applications, which typically involve a large number of small, random read/writes, generally perform more efficiently with a smaller data block size.
All the space in a data block is not available for storing data. A certain amount of space in each data block is consumed for storing block overhead, as described in the following table: Overhead Description Block header Contains general block information, such as the block address and Table directory Contains information about the tables that have rows in the block. Row directory Contains information about the rows stored in the block, including A portion of block overhead is variable, but the rest is fixed. Because block overhead becomes a smaller percentage of the overall space in a larger data block, achieving higher data density through more efficient space usage is possible by using a larger data block size than a smaller one.
Normally, each row should fit completely within a data block. If the size of a row is larger than the available space in a data block, however, Oracle needs to break up the row and store the pieces in two or more data blocks, which may not be contiguous. This phenomenon is called row chaining. I/O operations involving chained rows are necessarily more complex because they have to span multiple data blocks and are slower than those involving normal, unchained rows. Because of this, row chaining should be avoided as much as possible...
How to Read This Book
The chapters in this book divide the subject matter into key focus areas. Read this book from end to end for a comprehensive approach. The time you invest in reading this book may pay off many times over. You can also choose to adopt a modular approach to better suit your skill level and goals. If you are new to Oracle or if you wish to refresh your knowledge of the fundamentals, you can choose to first read Appendix A for a concise introduction to Oracle and then read through all chapters starting with the first. If you already have some background Oracle knowledge and know the fundamentals, you can choose to begin with whatever chapter best serves your interests or needs. For example, those wanting to update their knowledge by learning the new database administration features of Oracle8i may want to start by jumping straight to Chapter 9, "Leveraging Oracle8i's New Features," before moving on to the other chapters. Chapter 10, "The Internet DBA," will be of special interest to readers who want to learn about Oracle's Internet computing features and Oracle Application Server. Similarly, each of the other chapters covers a specific area of interest.
How to Grow Your Knowledge
The key to success in any field is to continuously growyour knowledge. Knowledge is the ultimate competitive advantage. Because technical information changes fast and furiously, I encourage you to keep pace with the changing scenario. By visiting the online community of database professionals at http-//www.dbtips.com, you can download useful scripts, including many that are presented in this book, as well as get a free copy of "How to Create SQL Scripts That Work," which provides tips and techniques for creating and customizing scripts to better suit your needs. I will post new and updated tips and techniques, scripts, articles, and papers at this site. You are welcome to share and exchange knowledge by posting your own ideas, experiences, and comments. I look forward to meeting you online at www.dbtips.com.
Overview