Securing HP NonStop Servers in an Open Systems World: TCP/IP, OSS and SQL, 2/e (Paperback)

Description

Recent corporate events have exposed the frequency and consequences of poor system security implementations and inadequate protection of private information. In a world of increasingly complex computing environments, myriad compliance regulations and the soaring costs of security breaches, it is economically essential for companies to become proactive in implementing effective system and data security measures. This volume is a comprehensive reference for understanding security risks, mitigations and best practices as they apply to the various components of these business-critical computing environments. HP NonStop Servers are used by Financial, Medical, Manufacturing enterprises where there can be no down time. Securing HP NonStop Servers in an Open Systems World: OSS, TCP/IP, and SQL takes a wide angle view of NonStop Server use. This book addresses protection of the Open Systems Services environment, network interfaces including TCP/IP and standard SQL databases. It lays out a roadmap of changes since our first book HP has made to Safeguard, elaborating on the advantages and disadvantages of implementing each new version. Even the security aspects of managing Operating System upgrades are given attention. Auditors, security policy makers, information security administrators and system managers will find the practical information they need for putting security principles into practice to meet industry standards as well as compliance regulations.

 

Table of Contents

Contents at a Glance Preface Introduction Part I. Data Model Tuning Chapter 1. The Relational Database Model Chapter 2. Tuning the Relational Database Model Chapter 3. Different Forms of the Relational Database Model Chapter 4. A Brief History of Data Modeling Part II. SQL Code Tuning Chapter 5. What is SQL? Chapter 6. Basic Concepts of Efficient SQL Chapter 7. Advanced Concepts of Efficient SQL Chapter 8. Common Sense Indexing Chapter 9. Oracle SQL Optimization and Statistics Chapter 10. How Oracle SQL Optimization Works Chapter 11. Overriding Optimizer Behavior Using Hints Chapter 12. How to Find Problem Queries Chapter 13. Automated SQL Tuning Part III. Physical and Configuration Tuning Chapter 14. Installing Oracle and Creating a Database Chapter 15. Tuning Oracle Database File Structures Chapter 16. Object Tuning Chapter 17. Low Level Physical Tuning Chapter 18. Hardware Resource Usage Tuning Chapter 19. Tuning Network Usage Chapter 20. Oracle Partitioning and Parallelism Chapter 21. Ratios: Possible Symptoms of Problems Chapter 22. Wait Events Chapter 23. Latches Chapter 24. Tools and Utilities Part IV. Tuning Everything at Once Chapter 25. The Wait Event Interface Chapter 26. The Database Control Chapter 27. Tuning With STATSPACK Appendices Appendix A. Sample Databases Appendix B. Sample Scripts (script changes) Appendix C. Sources of Information (accreditations) Appendix D. SQL Tuning in Oracle Enterprise Manager (might delete this one, depending on how much I remove from part III, which is 9i) Table of Contents Preface Introduction 1. A Tuning Environment What is Required When Tuning Oracle Database? What Tools are Available? Skilled Personnel Staging (Testing) Environments Duplicating Production Databases for Effective Tuning When to Tune? What to Tune in Production? When to Stop Tuning in Production? Bottlenecks Configuration Physical Space Usage SQL Code Tuning Data Model Tuning 2. Tuning from Development to Production The Steps in Tuning Data Model Tuning SQL Code Tuning Configuration and Physical Tuning Configuration Tuning Physical Tuning 3. How is this Book Organized? Part I. Data Model Tuning Part II. SQL Code Tuning Part III. Physical and Configuration Tuning Part IV. Tuning Everything At Once Appendices 4. Some Final Points 5. What is Oracle Database 10g? 6. What is New in Oracle Database 10gR2? Part I. Data Model Tuning Chapter 1. The Relational Database Model (updated and refined) 1. The Formal Definition of Normalization a) Anomalies b) Dependence and Determinance c) 1st Normal Form (1NF) d) 2nd Normal Form (2NF) e) 3rd Normal Form (3NF) f) Boyce-Codd Normal Form (BCNF) g) 4th Normal Form (4NF) h) 5th Normal Form (5NF) i) Domain Key Normal Form (DKNF) 2. A Layman?s Approach to Normalization a) 1st Normal Form b) 2nd Normal Form c) 3rd Normal Form d) Beyond 3rd Normal Form i. One-To-One NULL Separation Relationships ii. Separating Object Collections In Tables iii. Multi-Column Composite Keys iv. Summarizing a Layman?s Form of Normalization 3. Referential Integrity Chapter 2. Tuning the Relational Database Model (updated and refined) 1. Normalization and Tuning 2. Referential Integrity and Tuning a) Using Referential Integrity or Not b) How to Implement Referential Integrity i. Using Constraints (Primary and Foreign Keys) (a) Efficient Keys (b) Indexing Foreign Keys and Locking Issues (c) Sacrificing Referential Integrity for Performance ii. Coding Business Rules in the Database (a) Using Triggers for Referential Integrity (b) Using Triggers for Event Trapping (c) Using Stored Procedures and Functions 3. Optimizing with Alternate Indexes 4. Undoing Normalization a) Denormalization i. Reminding Ourselves about Normalization ii. Why Denormalize? iii. What to Look for to Denormalize? (a) Mutable and Complex Joins (i) Mutable Joins to Find Few Columns (b) Adding Composite Keys (c) One-to-One Relationships (d) Many-to-Many Join Resolution Entities (e) Application Functions versus Entities (f) Static Data in Multiple Entities (g) Intermediary Entities Covering Summary Groupings and Calculations iv. Denormalizing by Reversing Normal Forms (a) Denormalizing Beyond 3rd Normal Form (i) Denormalizing One-to-One NULL Separation Relationships (ii) Denormalizing Contained Object Collections (iii) Denormalizing Multi-Column Composite Keys (iv) Denormalizing Extra Entities For Common Columns (v) Denormalizing Formal 3rd Normal Form Transitive Dependencies (vi) Denormalizing Calculated Columns (vii) Denormalizing Formal Boyce-Codd Normal Form (b) Denormalizing 3rd Normal Form Many-to-Many Join Resolution Entities (c) Denormalizing 2nd Normal Form b) Some Useful Tricks i. Copying Columns Between Entities ii. Placing Summary Columns into Parent Entities iii. Separating Active and Inactive Data iv. Mixing Heavily and Lightly Accessed Columns v. Focus on Heavily Used Functionality vi. Using Views vii. Local Application Caching c) Using Special Purpose Oracle Database Objects Chapter 3. Different Forms of the Relational Database Model (updated and refined) 1. The Purist?s Relational Database Model 2. Object Applications and the Relational Database Model a) The Object Database Model b) The Object-Relational Database Model c) The Benefits of Overlaying Objects onto Relations Chapter 4. A Brief History of Data Modeling (updated and refined) 1. The History of Data Modeling a) The Different Types of Data Models 2. The History of Relational Databases 3. The History of the Oracle Database 4. The Roots of SQL Part II. SQL Code Tuning Chapter 5. What is SQL? (re-tested on 10g) 1. DML and DDL 2. DML Statement Syntax a) The SELECT Statement i. Logical Operators ii. Comparison Conditions iii. Types of SELECT Statements (a) Simple Query (b) Filtering Queries using the WHERE Clause (c) Sorting Queries using the ORDER BY Clause (d) Joining Tables (i) Types of Joins (e) Subqueries (f) Table and View Creation (g) Hierarchical Query (h) Set Operators and Composite Queries (i) Flashback (i) Flashback Versions Queries (ii) Flashback Database iv. Using DISTINCT v. The DUAL Table vi. NULLs vii. Pseudocolumns viii. Using Functions b) The INSERT Statement i. Multiple Table INSERT Statements c) The UPDATE Statement d) The DELETE and TRUNCATE Statements e) The MERGE Statement 3. Transaction Control a) COMMIT versus ROLLBACK b) Transaction Control between Multiple Sessions 4. Parallel Queries Chapter 6. Basic Concepts of Efficient SQL (re-tested on 10g) 1. The SELECT Statement a) A Count of Rows in the Accounts Schema b) Filtering with the WHERE Clause c) Sorting with the ORDER BY Clause i. Overriding WHERE with ORDER BY d) Grouping Result Sets i. Sorting with the GROUP BY Clause ii. Using DISTINCT iii. The HAVING Clause (a) The MODEL Clause iv. ROLLUP, CUBE and GROUPING SETS e) The FOR UPDATE Clause 2. Using Functions a) The COUNT Function b) The DECODE Function c) Datatype Conversions d) Using Functions in Queries i. Functions in the SELECT Statement ii. Functions in the WHERE Clause iii. Functions in the ORDER BY Clause iv. Functions in the GROUP BY Clause 3. Pseudocolumns a) Sequences b) ROWID Pointers c) ROWNUM 4. Comparison Conditions a) Equi, Anti and Range b) LIKE Pattern Matching c) Set Membership d) Groups Chapter 7. Advanced Concepts of Efficient SQL (re-tested on 10g) 1. Joins a) Join Formats b) Efficient Joins i. Intersections ii. Self Joins iii. Equi Joins and Range Joins c) Inefficient Joins i. Cartesian Products ii. Outer Joins iii. Anti Joins iv. Mutable and Complex Joins d) How to Tune a Join 2. Using Subqueries for Efficiency a) Correlated versus non-Correlated Subqueries b) IN versus EXISTS c) Nested Subqueries d) Replacing Joins with Subqueries i. Remove Tables Without Returned Columns Using EXISTS ii. FROM Clause Subquery Nesting 3. Using Synonyms 4. Using Views 5. Temporary Tables 6. Resorting to PL/SQL a) Tuning DML in PL/SQL i. The RETURNING INTO Clause b) When to Resort to PL/SQL and Cursors c) Java or PL/SQL 7. Object and Relational Conflicts a) Large Binary Objects in a Relational Database b) Object-Relational Collections 8. Replacing DELETE with TRUNCATE Chapter 8. Common Sense Indexing (re-tested on 10g) 1. What and How to Index a) When Not to Use Indexes b) Utilizing Referential Integrity Indexes i. Alternate and Secondary Indexing 2. Types of Indexes 3. Types of Indexes in Oracle Database a) The Syntax of Oracle Database Indexes b) Oracle Database BTree Indexes c) Read Only Indexing i. Bitmap Indexes (a) Are Bitmaps Faster than BTree Indexes? (b) Bitmap Index Locking (c) Using Composite Bitmap Indexes (d) Do Bitmap Indexes Overflow? (e) Bitmap Join Indexes ii. Clusters (a) Hash Clusters (i) Sorted Hash Clusters (b) Index Organized Tables 4. Tuning BTree Indexes a) Overflow and Rebuilding i. Lost Index Space b) Reverse Key Indexes c) Compressed Composite Indexes i. Compressed Indexes and DML Activity d) Function Based Indexes e) NULLs and Indexes 5. Summarizing Indexes Chapter 9. Oracle SQL Optimization and Statistics (re-tested on 10g) A lot of changes in this chapter for rule/cost based optimization 1. What is the Parser? 2. What is the Purpose of the Optimizer? a) What Does the Optimizer Do? b) What are Statistics? c) Query Plan Access Paths 3. Rule versus Cost Based Optimization a) Setting the Optimization Mode b) What Was Rule Based Optimization? i. Outlines Hints and Cost Based Optimization c) What is Cost Based Optimization? i. Configuration Parameters and Cost Based Optimization (a) Dynamic Sampling ii. The Importance of Statistics and Realistic Statistics iii. Generating Statistics (a) What to Generate Statistics For? (i) Tables (ii) Indexes (iii) Columns (b) The ANALYZE Command (c) The DBMS_STATS Package (d) Automated Statistics Gathering (i) Automatic Statistics Generation in Oracle9i Database (ii) Automatic Statistics Generation in Oracle10g Database (e) The SAMPLE Clause (f) Timed Statistics iv. Histograms Chapter 10. How Oracle SQL Optimization Works (re-tested on 10g) 1. Data Access Methods a) Accessing Tables and Indexes i. Full Table Scans (a) Reading Many Blocks at Once (i) Small Static Tables (ii) Reading Most of the Rows (b) Reading Deleted Rows (c) Parallel Table Scans ii. Sample Table Scans iii. ROWID Scans iv. Index Scans (a) Index Unique Scan (b) Index Range Scan (i) Reverse Order Index Range Scan (c) Index Skip Scan (d) Index Full Scan (i) Fast Full Index Scan (ii) The DISTINCT Clause (iii) The COUNT Function (iv) Retrieving with NOT NULL (v) Parallel Index Scan (e) Index Join (f) Bitmap Join v. Cluster and Hash Scans b) Joining Tables i. Join Order Execution ii. Types of Joins (a) Nested Loop Join (b) Hash Join (c) Sort Merge Join iii. Mutable Join Nesting iv. Semi Join v. Joins to Avoid (a) Cartesian Join (b) Outer Join (i) Grouped Outer Join c) Sorting i. Unique Sort ii. ORDER BY Sort iii. GROUP BY Sort iv. Sort Merge Join Sort v. Aggregate Sort d) Special Cases i. Concatenation ii. The IN List Operator iii. UNION, MINUS and INTERSECT Chapter 11. Overriding Optimizer Behavior Using Hints (re-tested on 10g) 1. How to Use Hints 2. Hints: Suggestion or Force? 3. Classifying Hints 4. Influencing the Optimizer in General a) Altering Table Scans b) Altering Index Scans c) Altering Joins d) Cause Parallel SQL Execution e) Altering Queries and Subqueries 5. Naming Query Blocks for Hints a) Global Table Hints Chapter 12. How to Find Problem Queries (re-tested on 10g) 1. Tools to Detect Problems 2. EXPLAIN PLAN a) What Does EXPLAIN PLAN Produce? b) What to Look for in Query Plans? c) Problems Producing Query Plans d) EXPLAIN PLAN Command Syntax e) How to Create the PLAN_TABLE f) What is Not Provided in Query Plans? 3. SQL Trace and TKPROF a) Setting up SQL Trace i. Session Level Tracing ii. Finding Trace Files b) Using SQL Trace c) TKPROF i. Syntax of TKPROF ii. Using TKPROF iii. Interpretation of TKPROF Output TRCSESS End to End Tracing 4. Autotrace 5. Oracle Database Performance Views for Tuning SQL a) Finding Cached SQL Code i. Examining SQL Code ii. Hard Hitting SQL Code (a) Using V$SQLAREA (i) Executions (ii) Disk + Buffer Reads per Row (iii) Rows per Sort (iv) Rows per Fetch (v) Parses per Execution (vi) Disk versus Logical Reads (b) Using V$SQL (i) Optimizer Cost (ii) CPU Time (iii) Elapsed Time iii. Examining Cached Query Plans With V$SQL_PLAN Chapter 13. Tuning SQL with Oracle Enterprise Manager Automatic Gathering of Statistics The AWR and the ADDM The AWR The ADDM Automating SQL Tuning Part III. Physical and Configuration Tuning Chapter 14. Installing Oracle and Creating a Database Installation chapter. Will change to incorporate 10g, or be solely about 10g. Minor changes. This chapter is essentially an introduction to Part III. Potential removal for dictionary/locally managed tablespaces/manual rollback 1. Installing Oracle Database a) Different Editions of Oracle Database b) Oracle Enterprise Manager 2. Basic Configuration a) Basic Network Files i. Configuring the Listener ii. Configuring the Client b) The Parameter File i. Database Identification ii. Control Files iii. Block Size iv. Memory Buffers and I/O Behavior (a) The Database Buffer Cache (b) The Shared Pool (c) Connection Memory Requirements (d) The Large Pool (e) The Java Pool (f) The Redo Log Buffer Cache v. SQL Code Optimization vi. Auditing and Tracing (a) Auditing (b) Tracing vii. Archiving, Check Points, the Alert Log and Trace Files (a) Archive Logs (b) Check Pointing (c) The Alert Log and Trace Files viii. Rollback and Undo (a) Manual Rollback Segments (b) Automated Undo Segments ix. Resource Management x. Job Scheduling (a) The Scheduler xi. Networking 3. Creating a Database a) The Database Configuration Assistant i. Current Trends b) Manual Database Creation i. Create the Directory Structure ii. Create the Database iii. Tablespace and Rollback Creation iv. The Catalog and the SPFILE c) Automated Storage Management Chapter 15. Tuning Oracle Database File Structures Potential removal for dictionary/locally managed tablespaces/manual rollback 1. Oracle Database Architecture and the Physical Layer a) The Oracle Instance i. Buffers ii. Processes b) The Oracle Database or File System Layer i. How Oracle Database Files Fit Together (a) Special Types of Datafiles ii. Tuning Datafiles iii. Control Files iv. Tuning Redo Logs and Archive Logs c) The Networking Layer 2. Tuning and the Logical Layer a) Tablespaces i. Dictionary Managed Tablespaces ii. Locally Managed Tablespaces (a) Auto Extend (b) Minimum Extent Sizes (c) Block Size (d) Logging (e) Extent Management (f) Segment Space Management (g) BIGFILE Tablespaces (h) Avoiding Datafile Header Contention iii. Temporary Sort Space (a) Tablespace Groups iv. Manual Rollback and Automatic Undo (a) Automated Undo (b) Manual Rollback Segments 3. Oracle Managed Files (OMF) 4. Automatic Storage Management a) Disk Groups b) Tablespace Groups c) Automated Tuning Rebalancing d) Using an ASM Database Chapter 16. Object Tuning Potential removal for dictionary/locally managed tablespaces/manual rollback 1. Tables a) Caching b) Logging c) Table Parallelism d) Storing LOBs Separately e) Dropping Columns f) Deallocating Unused Space 2. Indexes a) Monitoring b) Index Parallelism c) Fragmentation and Coalescing 3. Index Organized Tables and Clusters 4. Sequences (some updating) 5. Synonyms and Views (some updating) 6. The Recycle Bin Chapter 17. Low Level Physical Tuning Potential removal for dictionary/locally managed tablespaces/manual rollback 1. What is the High Water Mark? 2. Space Used in a Database 3. What are Row Chaining and Row Migration? 4. Different Types of Objects 5. How Much Block and Extent Tuning? 6. Choosing Database Block Size 7. Physical Block Structure a) What is in a Block? b) Block Space Management i. Assessing PCTFREE Settings c) Block Concurrency 8. Extent Level Storage Parameters a) Setting Extent Sizes b) Minimum and Maximum Extents c) Variable Extent Sizes d) Managing Concurrency e) Minimizing Rollback Resizing f) Different Cache Recently Used Lists Chapter 18. Hardware Resource Usage Tuning Potential removal for dictionary/locally managed tablespaces/manual rollback 1. Tuning Oracle CPU Usage a) Busy I/O and Intense CPU Activity i. Swapping and Paging b) Possible Oracle Database Causes of High CPU Activity i. Poorly Tuned SQL Code ii. Poor Index Usage iii. Rollback and Undo iv. Temporary Sort Space v. Row Locks and Latch Waits vi. High Network Activity 2. How Oracle Database Uses Memory a) The Database Buffer Cache b) The Shared Pool i. The Library Cache ii. The Metadata or Dictionary Cache iii. Pinning Objects in the Shared Pool c) Session Connection Cache i. Automated Memory Management (sga and pga automation) ii. Manual Memory Management d) The Large Pool i. Shared Servers and Virtual Circuits e) The Redo Log Buffer f) The Java Pool g) Getting Advice on Buffers Must refer at least refer to extensive advice capabilities in the Database Control i. Database Buffer Cache Advice ii. Shared Pool Advice iii. PGA Advice iv. Java Pool Advice 3. Tuning I/O Usage a) RAID Arrays b) Performance with ASM c) Performance with OMF Chapter 19. Tuning Network Usage 1. The Listener a) Listener Queue Size b) Switching Off Listener Logging and Tracing c) Multiple Listeners and Load Balancing d) Automatic Listener Configuration 2. Network Naming Methods a) Local Naming i. Dedicated Versus Shared Servers ii. The Session Data Unit Buffer (SDU) 3. Connection Profiles 4. Shared Servers a) Configuration Parameters b) Network Performance Views i. Shared Servers ii. Dispatchers iii. Circuits iv. Using Events Chapter 20. Oracle Partitioning and Parallelism 1. What is Oracle Partitioning? a) Why is Oracle Partitioning Beneficial? b) How are Tables and Indexes Partitioned? c) Oracle Partitioning Methods i. Partitioning by Range ii. Partitioning by List iii. Hash Partitions iv. Composite Partitions 2. Tricks with Partitions 3. Parallel Processing and Partitioning (reviewers wanted new sections or just expansion) 4. Hash Partitioned Global Indexing (reviewers wanted new section or just expansion) Chapter 21. Ratios: Possible Symptoms of Problems 1. Database Buffer Cache Hit Ratio a) Default, Keep and Recycle Pools 2. Table Access Ratios 3. Index Use Ratio 4. Dictionary Cache Hit Ratio 5. Library Cache Hit Ratios 6. Disk Sort Ratio 7. Chained Rows Ratio 8. Parse Ratios 9. Latch Hit Ratio Chapter 22. Wait Events Updated with new wait events for 10g. Possibly built-in or a New in 10g section 1. Idle Events 2. Significant Events a) Buffer Busy Waits i. Causes of Buffer Busy Waits ii. Increasing and Decreasing Buffer Busy Waits b) Datafile Scattered and Sequential Reads c) Direct Path Reads and Writes d) Free Buffer Waits e) Row Cache Lock Waits f) Library Cache Waits g) Redo Log Waits h) Rollback and Undo Waits i. Manual Rollback Waits ii. Automated Undo Waits i) Enqueue Waits j) Latch Free Waits Chapter 23. Latches Updated with new wait events for 10g. Possibly built-in or a New in 10g section 1. What is a Latch? a) Latch Misses, Spins and Sleeps b) Latch Performance Views c) Latches in Real Time 2. The Most Significant Latches a) The Database Buffer Cache b) The Shared Pool i. Library Cache Latches ii. Metadata Cache Latches c) The Redo Log Buffer d) Network and Database Connection Latches Chapter 24. Tools and Utilities 1. Oracle Enterprise Manager Much of this is out of date in 10g or shifted from the Console to the Database Control. Could include both 9i and 10g or just change for 10g (watching the page count) a) Diagnostics Pack i. Event Monitoring ii. Lock Monitoring iii. TopSessions iv. TopSQL v. Performance Manager b) Tuning Pack i. Tablespace Map and the Reorg Wizard 2. Spotlight 3. Operating System Tools a) Windows Performance Monitor b) Unix Utilities 4. Other Utilities and Tools a) Application End to End Tracing (expansion from previous chapters – if necc) b) Import, Export and SQL*Loader c) Resource Management and Profiling d) Recovery Manager (RMAN) e) STATSPACK Part IV. Tuning Everything at Once Chapter 25. Tuning with the Wait Event Interface 1. What is a Bottleneck? 2. Detecting Potential Bottlenecks 3. What is the Wait Event Interface? a) The System Aggregation Layer b) The Session Layer c) The Third Layer and Beyond 4. Oracle Enterprise Manager and the Wait Event Interface 5. Oracle Database Wait Event Interface Improvements 6. The Database Control and the Wait Event Interface Chapter 26. Tuning with the Database Control This chapter is separated because it encompasses all tuning types (part 2 and part 3). Also want to give an overall picture. Extends part of chapter 25 into 10g. This chapter is graphic rich! Also may briefly repeat sections covered already. Part IV is a little like a combination of a summary, recap and adding just a little more information, but focusing on the tools that make everything so much easier. Something like this (demonstrating use of DB control tools to solve real problems): 1. Automated Performance Diagnostics and Tuning 2. Top Activity 3. Top Consumers 4. Duplicate SQL 5. Blocking Sessions 6. Hang Analysis 7. Instance Locks 8. Instance Activity 9. Advisor Central 10. All Metrics 11. SQL History Alert History 12. Blackouts 13. Monitoring Configuration 14. Alert Log Content 15. Monitor in Memory Access Mode Chapter 27. Tuning With STATSPACK 1. Using STATSPACK a) An Example STATSPACK Report Appendices Appendix A. Sample Databases Appendix B. Sample Scripts Appendix C. Sources of Information Appendix D. SQL Tuning in Oracle Enterprise Manager