CONTENTS

  Title Page

  Copyright Page

  Send Us Your Comments

  Preface

  1      Introduction to Database Design and Tuning with Oracle Expert for Rdb
    1.1 What Is Logical Database Design?
    1.2 What Is Physical Database Design?
      1.2.1      Where Logical and Physical Design Fit in the Database Design Cycle
      1.2.2      Physical Design and Redesign
    1.3 What Is Database Tuning?
      1.3.1      Database Tuning Issues
        1.3.1.1      Extensive Tool Expertise
        1.3.1.2      Consistency in Tuning
    1.4 Using Oracle Expert for Rdb for Physical Database Design
      1.4.1      Understanding the Design Hierarchy
      1.4.2      Using Oracle Expert for Rdb Windows
      1.4.3      Using the Sample Database Design
      1.4.4      Logical Names Used by Oracle Expert for Rdb
      1.4.5      Global Customization Variables
    1.5 Using Oracle Expert for Rdb for Logical Schema Manipulation
    1.6 Using Oracle Expert for Rdb for Tuning Analysis
    1.7 Suggestions and Recommendations
      1.7.1      Using Oracle Expert for Rdb with Multiple Designers
      1.7.2      Maintaining Design Directories
      1.7.3      Improving Performance on Schema Import Operations
    1.8 Terminology Used in This Manual

  2      Getting Started with Oracle Expert for Rdb
    2.1 General Information on Using Oracle Expert for Rdb
      2.1.1      Starting an Oracle Expert for Rdb Session
      2.1.2      Ending an Oracle Expert for Rdb Session
      2.1.3      Getting Help
      2.1.4      Handling Oracle Expert for Rdb Errors
    2.2 Understanding Window Pull-Down Menu Choices
      2.2.1      Menus of the Directory Window
        2.2.1.1      Directory Window File Menu
        2.2.1.2      Directory Window Design Menu
        2.2.1.3      Directory Window Collect Menu
        2.2.1.4      Directory Window Generate Menu
        2.2.1.5      Directory Window Implement Menu
        2.2.1.6      Directory Window Report Menu
        2.2.1.7      Directory Window View Menu
      2.2.2      Choices in Menus of the Environment Window
        2.2.2.1      Environment Window File Menu
      2.2.3      Choices in Menus of the Volume Window
        2.2.3.1      Volume Window File Menu
        2.2.3.2      Volume Window Design Menu
        2.2.3.3      Volume Window View Menu
      2.2.4      Choices in Menus of the Workload Window
        2.2.4.1      Workload Window File Menu
        2.2.4.2      Workload Window Edit Menu
        2.2.4.3      Workload Window Design Menu
        2.2.4.4      Workload Window View Menu
        2.2.4.5      Workload Window Options Menu
      2.2.5      Using Keys for Menu Navigation
        2.2.5.1      Activating the Menu Bar for Keyboard Use
        2.2.5.2      Navigating Across the Menu Bar by Using the Keyboard
        2.2.5.3      Selecting Menu Choices by Using the Keyboard
        2.2.5.4      Leaving Menu Selection by Using the Keyboard
      2.2.6      Using Keys for Common Operations with Tables and Dialog Boxes
      2.2.7      Naming Design Directories and Design Elements
    2.3 Using the Directory Window
      2.3.1      Viewing the Design Hierarchy
        2.3.1.1      Viewing More Information
        2.3.1.2      Viewing Less Information
        2.3.1.3      Faster Methods
    2.4 Entering Design Information
      2.4.1      Defining the Workload
        2.4.1.1      Opening the Workload Window
        2.4.1.2      Understanding the Workload Definition
        2.4.1.3      Determining the Frequency and Importance of Workload Elements
        2.4.1.4      Exiting from the Workload Window
        2.4.1.5      Validating the Workload
      2.4.2      Defining the Data Volume
        2.4.2.1      Understanding the Data Volume
        2.4.2.2      Determining the Volatility
        2.4.2.3      Exiting from the Volume Window
      2.4.3      Defining the Environment
        2.4.3.1      Understanding the Environment
        2.4.3.2      Exiting from the Environment Window
    2.5 Generating the Output Design Elements
      2.5.1      Generating the Physical Schema
      2.5.2      Generating the Output Design Reports
    2.6 Viewing and Interpreting the Output Design Reports
    2.7 Printing and Storing the Input Design Elements
    2.8 Exporting the Input Design Elements and Generated Physical Schema

  3      Working with Design Repositories
    3.1 File and Design Menu Choices in the Directory Window
    3.2 Opening a Design Repository
    3.3 Creating Design Repositories
      3.3.1      Creating and Using Design Repositories
      3.3.2      Creating a Conceptual Design Directory
      3.3.3      Creating a Logical Design Directory
        3.3.3.1      Creating a Logical Schema
        3.3.3.2      Editing the Logical Schema
        3.3.3.3      Importing the Logical Schema
      3.3.4      Creating a Physical Design Directory
    3.4 Maintaining Your Design Directories
      3.4.1      Examining Directory Elements
      3.4.2      Copying Design Directories
      3.4.3      Editing Design Directories
      3.4.4      Deleting Design Directories and Design Elements
        3.4.4.1      Deleting a Main Design Repository

  4      Defining the Database Workload
    4.1 Workload Design Element
      4.1.1      Methods of Obtaining a Workload
    4.2 File, Edit, Design, View, and Options Menu Choices in the Workload Window
      4.2.1      Choices in Menus of the Workload Window
        4.2.1.1      Workload Window File Menu
        4.2.1.2      Workload Window Edit Menu
        4.2.1.3      Workload Window Design Menu
        4.2.1.4      Workload Window View Menu
        4.2.1.5      Workload Window Options Menu
    4.3 Importing a Workload
    4.4 Using Oracle TRACE to Collect Workload Information
      4.4.1      Specifying a Compiled or Dynamic Workload
      4.4.2      Creating the Oracle TRACE Statistics Report
    4.5 Starting the Workload Definition
    4.6 Defining a Workload for New Database Designs
      4.6.1      Creating the Elements of a Workload
        4.6.1.1      Creating the Applications
        4.6.1.2      Creating the Programs
        4.6.1.3      Creating the Transactions
        4.6.1.4      Creating the Database Requests
    4.7 Defining a Workload for Existing Databases
      4.7.1      Using Source Program Listings to Identify Workload Information
      4.7.2      Using SQL Module Language Files in Defining a Workload
    4.8 Examining Workload Elements
    4.9 Editing and Copying Workload Elements
      4.9.1      Requests That Contain Views
    4.10 Exporting a Workload
    4.11 Validating the Workload

  5      Defining the Data Volume
    5.1 File Menu Choices in the Volume Window
    5.2 Using the Volume Window
      5.2.1      Defining the Cardinality 1, Cardinality 2, and Cardinality 3 Volumes
        5.2.1.1      Determining the Volatility
        5.2.1.2      Defining the Volume for Columns
        5.2.1.3      Defining the Volume for List Fields
        5.2.1.4      Entering Segment Number and Average Length
    5.3 Using Volume Information from an Existing Database
    5.4 Using Volume Information from a PDL File
    5.5 Exporting the Volume
    5.6 Validating the Volume

  6      Defining the System Environment
    6.1 File Menu Choices in the Environment Window
    6.2 Defining or Modifying the Environment
      6.2.1      Defining the Number of Users
      6.2.2      Defining the Maximum Physical Memory Available
      6.2.3      Defining the Available Memory for Database Applications
      6.2.4      Journaling and Database Recovery
        6.2.4.1      After-Image Journal File
        6.2.4.2      Recovery-Unit Journal File
      6.2.5      Defining the Disk Environment
        6.2.5.1      Entering Disk Environment Information
        6.2.5.2      Moving Around in the Edit Disk Information Dialog Box
    6.3 Importing the Environment
    6.4 Exporting the Environment

  7      Generating and Implementing the Physical Design
    7.1 Generating a New Physical Schema
    7.2 Creating the Output Redesign Procedure
    7.3 Executing the Redesign Procedure
    7.4 Creating the Physical Design Reports
      7.4.1      Creating the Analysis Report
      7.4.2      Creating the Audit Report
      7.4.3      Creating the Workload Report
      7.4.4      Creating the Physical Schema Report
    7.5 Using the Physical Design Reports
      7.5.1      Analysis Report Description
      7.5.2      Audit Report Description
        7.5.2.1      Audit Report Interpretation
      7.5.3      Workload Report Description
        7.5.3.1      Workload Summary Interpretation
        7.5.3.2      Workload Detail Interpretation
      7.5.4      Physical Schema Report Description
        7.5.4.1      Overview Interpretation
        7.5.4.2      Collating Sequence Summary Interpretation
        7.5.4.3      Domain Summary Interpretation
        7.5.4.4      Table Summary Interpretation
        7.5.4.5      Constraint Summary Interpretation
        7.5.4.6      Trigger Summary Interpretation
        7.5.4.7      Function Summary Interpretation
        7.5.4.8      Module Summary Interpretation
        7.5.4.9      Grant Summary Interpretation
        7.5.4.10     Index Summary Interpretation
        7.5.4.11     Area Summary Interpretation
        7.5.4.12     Disk Summary Interpretation
        7.5.4.13     Area Mappings Summary Interpretation
        7.5.4.14     Cross Reference Summary Interpretation
    7.6 Comparing Physical Database Designs
      7.6.1      Steps for Changing and Comparing Design Elements
        7.6.1.1      Changing the Number of Disks
        7.6.1.2      Adding a Table Join

  8      Tuning the Database Environment
    8.1 Collecting System Data for Tuning
      8.1.1      Collection Logical Names
      8.1.2      When to Collect Tuning Information
      8.1.3      Where to Collect Tuning Information
        8.1.3.1      Defining Collection Queues
        8.1.3.2      Setting Collection Parameters
      8.1.4      What Data to Collect
      8.1.5      Starting Data Collection
      8.1.6      Stopping Data Collection
    8.2 Importing Tuning Data
    8.3 Generating Tuning Recommendations
    8.4 Generating Tuning Reports
      8.4.1      Tuning Audit Report
      8.4.2      Tuning Analysis Report
      8.4.3      Tuning Configuration Report
    8.5 Implementing Tuning Recommendations
      8.5.1      Changing User Quotas
      8.5.2      Changing System Parameters
      8.5.3      Changing the Database

  A Oracle Expert Physical Design Language
    A.1 Defining an Environment Design Element

  ENVIRONMENT Statement
    A.2 Defining a Volume Design Element

  VOLUME Statement

  DEFAULT Statement

  TABLE Statement

  COLUMN Statement

  COMPUTED Statement

  LIST Statement
    A.3 Defining a Workload Design Element

  WORKLOAD Statement

  CYCLE Statement

  APPLICATION Statement

  PROGRAM Statement

  TRANSACTION Statement

  REQUEST Statement

  B Oracle Expert Help
    B.1 Accessing the System Help Files
      B.1.1     Printing the Help Files
      B.1.2     Printing the Glossary
      B.1.3     Directly Reading the Help Files

  C Keys for Window and Dialog Box Operations
    C.1 Working with Oracle Expert Windows
      C.1.1      Selecting Entries
      C.1.2      Moving Between Cells
      C.1.3      Other Operations
    C.2 Working with Dialog Boxes
    C.3 Working with Text Fields
      C.3.1      Editing Fields
      C.3.2      Selecting Fields
      C.3.3      Other Operations