Oracle and UNIX Performance Tuning

AHMED ALOMARI is a Senior Performance Engineer with Oracle Corporation residing in Redwood Shores, California. Mr. Alomari has a tremendous amount of hands-on experience in administrating and tuning large scale UNIX and database systems. He has written many white papers on performance and tuning, and has spoken at several conferences. This book combines Mr. Alomari's personal experiences administrating and tuning large scale systems with his detailed knowledge of both UNIX and Oracle.

Introduction. Tuning and Science. Tuning by Iteration. Tuning by Understanding. Tuning and Training. Getting the Most of Your Environment. Tuning on the Project. The Team. Resources. Version Control. The Repository. Staying Current. 1. Inside the UNIX Kernel. UNIX Internals. UNIX Architecture. Kernel Layer. File Systems. Virtual Memory. Shared Memory. Process Model. The Job Scheduler. Threads Model. Signals and Interrupts. Network Services and Communication. I/O Services. Synchronization. System Daemon Layer. Shell Layer. Application Layer. 2. Memory and Processor Tuning. UNIX Memory Model. Paging. Swapping. Process Memory Model. Monitoring Paging and Swapping. Tuning Paging. Physmem. Lotsfree. Desfree. Minfree. Maxpgio. Swapping. Configuring Swap. Memory Shortages. Memory Surplus. Kernel Memory Allocator. Shared Memory Parameters. Semaphore Parameters. Processor Architecture. Cache Concepts. Cache Coherency. Central Bus. Threads Model. Threads Safety. Threads Programming. Process Tuning. Processor Related Kernel Parameters. Think Parallel. Right-Sizing the Number of Processors. Affinity Binding on Multi-Processor Systems. Benchmarking Processors. 3. Configuring and Tuning. UNIX I/O Model. Logical Volume Manager. Striping. Striping on Sequent. Striping on Sun. Striping on HP. Maximizing the Performance of the Strip. Partitioning the Disks. Setting the Stripe Width. Mirroring. RAID-5. Asynchronous I/O. Asynchronous I/O in Solaris. Asynchronous I/O in HP-UX. Asynchronous on Sequent. Asynchronous I/O in Oracle. Direct I/O (DIO). Kernelized Asynchronous I/O (KAIO). List I/O (LIO). Read(). Db_file_multiblock_read_count. File Systems Versus Raw Devices. UNIX File Systems. Raw Devices. Separate Sequential and Random I/O. Tablespace Optimization. Setting the Database Block Size. Monitoring I/O Activity. Tuning NFS. NFS and Database. Using the Automounter. Configuring and Sizing the Disk I/O Subsystems. 4. Application Tuning: Eliminating the SQL Bottleneck. Application Architecture. Development Environment. Tuning Pro*C Applications. Using Arrays in Pro*C. Linking with the Shared Library. Pro*C Compile Options. Parallel Processing Using Pro*C. The Make File. Tuning SQL. Rule-Based Optimizer. Coat-Based Optimizer. Using Hints. The Explain Plan. The Hints. Tuning by Selectivity. Using Parallel Query. Using PL/SQL. PL/SQL Tables. Locking Contention. Using the System Packages. Profiling and Optimization. 5. Tuning an OLTP System. Performance Monitoring. Tuning I/O. Local Volume Manager (LVM). Raw Devices. Asynchronous I/O. Oracle Writes. Veritas File System Mount Options. Veritas Database Edition for Oracle. Number of Data Files. Checkpoints. Disk Cache. Monitoring File I/O. Latches and Locks. LRU Latches. Tuning Redo. Configuring the Redo Log Files. Operating System Mirroring Versus Oracle Mirroring. Using PrestoServe for the Redo Log Files. Monitoring the Redo Buffer. Tuning Redo Log Buffer Latches. Tuning Spin_Count. Tuning Rollback Segments. Sizing Rollback Segments. Large Transactions. The Shared Pool. Library Cache. Data Dictionary Cache. Maximizing the Shared Pool Utilization. Fragmentation in the Shared Pool. Sizing the SGA. Tuning Shared Memory. Sizing the Buffer Cache. Tuning Archiving. The Post-Wait Driver. Tuning TCP/IP. Tuning SQL*Net V2. Recursive SQL and tkprof. Tuning Storage Clauses. The Free List. Monitoring Locking. Referential Integrity. Discrete Transactions. Hash Cluster. Database Design. 6.Tuning a Decision Support System (DSS). The Data Warehouse. Requirements for a Data Warehouse. DSS Database Design. Tuning I/O. Sorting. Direct Write Sorts. The Temporary Tablespace. Temporary Tablespaces in Oracle 7.3. Bitmap Indexes. Tuning Queries. The Star Hint. Anti-Join. Histograms. Merge Joins. Hash Joins. Partitioned Views. Loading Data. Direct Path Loads. Conventional Path Loads. Fixed Field Loads. Loading Through NFS. Building Indexes. Building Tables. Parallel Storage. Degree of Parallelism. Parallel Query and the 9/13 Rule. Maximizing Parallel Query Performance. Configuring the Number of Parallel Query Slaves. Reducing Locks. Inter-Node Parallel Query. Single-Task Processes. 7. Media Recovery. The System is Down. System Architecture. Hardware Requirements. Selecting the Backup and Recovery Tools. Configuring and Sizing the Media Devices. Export and the Buffer Option. The Direct Export Option. Import and the Buffer Option. Export, Import, and the 2 G File Limit. Enterprise Backup Utility (EBU). EBU and Hot Backups. Disk and Tape I/O Sizes. Tuning EBU Parameters. Parallel Recovery. Standby Database. 8. Conclusion. Chapter Summaries. Chapter 1 "Inside the UNIX Kernel." Chapter 2 "Memory and Processor Tuning." Chapter 3 "Configuring and Tuning I/O." Chapter 4 "Application Tuning: Eliminating the SQL Bottleneck." Chapter 5 "Tuning an OLTP System." Chapter 6 "Tuning a Decision Support System (DSS)." Chapter 7 "Media Recovery." Oracle8. The Network Computing Architecture. Appendix A: Installing and Configuring UNIX for Optimal Performance. A Corporate Architecture. Determining the Type of System. Sizing the File Systems. Mirroring the Operating System. Installing the UNIX Operating System. Naming Service. Post-Installation Procedures. Appendix B: Installing Oracle for Optimal Performance. A Corporate Architecture. Determining the Type of System. Sizing the File Systems. Directories and File Locations. Installing the Oracle DBMS. Configuring SQL*Net. Post-Installation Procedures. References.