RelationalDBDesign RelationalDBDesign 



Performance Tuning   «Prev  Next»
Lesson 6 Physical performance design
ObjectiveIdentify Oracle physical components that effect performance.

Physical Performance Design

Oracle design for performance is a very broad and important topic. As we discussed in a previous lesson, physical design refers to the actual implementation of the logical table within the Oracle database. Physical design involves the following areas:

Indexes

Indexes - The proper placement of
  1. b-tree and
  2. bitmapped indexes
can have a huge impact upon performance.

Table Storage Modes

Table storage modes - There are a host of table options that can dramatically affect table performance. These will all be covered in detail in later lessons.
These are some of the table options that you'll learn about in detail in later lessons.
  1. Cluster tables:This technique places subordinate rows in the same database block as their logical owners. For example, if we have a customer table and an order table, and all queries show the orders for a customer, we could place the order rows on the same database block as the customer rows. This greatly reduces the amount of disk I/O to service the query.
  2. Hash cluster tables: Hash clusters will convert the primary key for a row into a data block address (DBA), allowing for super-fast retrieval of rows.
  3. Placement of the table rows on the data blocks - The proper settings for PCTFREE and PCTUSED will have a huge impact upon the performance of the table.
  4. Using referential integrity: (RI) constraints - primary key constraints, check constraints, and uniqueness constraints will also impact the performance of the table.

Caching

Caching - The proper use of table caching will affect performance. Table caching will place small, frequently used tables into the most recently used end of the Oracle buffer cache with the ALTER TABLE tablename CACHE option. This makes the table rows reside in RAM storage longer, and improves their access speed.
Pinning Packages
Pinning packages - For frequently executed SQL, the SQL can be placed into an Oracle "package," and the package can be pinned into SGA memory. By pinning a package, Oracle does not have to constantly re-parse SQL statements, and the frequently used SQL will execute very fast.
Now that you understand the importance of design, you will see how a good design can prevent a plethora of performance problems. Now let's take a closer look at the Oracle database engine and see the parts of the Oracle database that can be tuned.