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:

How Physical design is related to Logical Table

The physical design of a database is a crucial aspect of database management, as it directly impacts the overall performance, storage efficiency, and maintainability of the system. It refers to the process of translating the logical data model, which consists of tables, relationships, and constraints, into a physical schema that can be efficiently implemented on a specific storage medium. In this analysis, we will explore the relationship between the physical design of a database and the actual implementation of the logical tables.

1. Storage Structures: One of the primary tasks in the physical design process is determining the most appropriate storage structures for the logical tables. These structures, which include heap files, clustered indexes, and partitioning, govern how the data is stored on disk and subsequently accessed. The choice of storage structure has a significant impact on the performance of data retrieval and manipulation operations, as well as the space requirements for the database.
2. Indexing: Indexing is a critical aspect of physical design that directly impacts the efficiency of data retrieval. By creating an index on one or more columns of a logical table, the database management system (DBMS) can quickly locate and access the required data, often bypassing the need for a full table scan. The choice of index type (e.g., B-tree, bitmap, or hash) and the columns to be indexed depend on the query patterns and access frequency of the database. A well-designed indexing strategy can significantly improve the performance of a database system.
3. Data Clustering: In some cases, it is advantageous to store related data from multiple logical tables close together on disk. This process, known as data clustering, can enhance the performance of join operations and reduce the need for costly disk I/O. The physical design process involves determining the optimal clustering strategy, taking into consideration factors such as the relationships between tables, query patterns, and the size of the tables.
4. Data Compression: Data compression techniques can be employed in the physical design process to reduce the storage space required for the logical tables. By compressing data, the DBMS can store more information in the same amount of disk space, potentially improving the efficiency of data retrieval and manipulation operations. The choice of compression technique (e.g., run-length encoding, dictionary encoding, or delta encoding) depends on the nature of the data and the specific requirements of the database system.
5. Data Partitioning: Partitioning is a technique used in the physical design process to divide large logical tables into smaller, more manageable pieces. This division can be based on a range of values, a specific column, or a combination of both. Partitioning can improve performance by allowing the DBMS to read or write data from multiple partitions in parallel, as well as reducing the amount of data that must be scanned during query execution.

The physical design of a database plays a pivotal role in determining the efficiency and maintainability of the logical tables. It involves several key aspects, such as storage structures, indexing, data clustering, data compression, and data partitioning, that directly impact the performance of data retrieval and manipulation operations. A well-designed physical schema can significantly enhance the overall performance of a database system, ensuring that it meets the specific needs and requirements of its users.

Indexes - The proper placement of

1. b-tree and
2. bitmapped indexes

can have a huge impact upon performance.

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 (RI) referential integrity: constraints - primary key constraints, check constraints, and uniqueness constraints will also impact the performance of the table.

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