Oracle Database

Oracle Database 11g: 2 Day DBA Release 2

Course Outline

 

  • Overview of Oracle Database Administration
    • Relational Databases: Introduction
    • What is SQL?
    • Overview of Oracle Instance and Database
    • Common Tasks of an Oracle Database Administrator (DBA)
    • Tools for Administering an Oracle Database
  • Installing the Database and Creating the Database
    • Using Oracle Universal Installer
    • Installation Prerequisites
    • Setting Environment Variables
    • Installing the Oracle Database Software: Steps
    • Using the Database Configuration Assistant (DBCA)
    • Using the DBCA to Create a Database
    • Performing Other Actions with the DBCA
    • Using the DBCA to Manage Templates
  • Using Oracle Enterprise Manager Database Control and SQL-Based Management Tools
    • Oracle Database 11g Release 2 Management Framework: Components
    • Administering the Database by Using Enterprise Manager: Overview
    • Starting the Database Control Console Process
    • Accessing Enterprise Manager Database Control
    • Oracle Enterprise Manager: Navigation
    • Granting Enterprise Manager Administrative Privileges
    • Configuring Enterprise Manager Settings and Preferences
    • Using SQL*Plus and SQL Developer to Access Your Database
  • Configuring the Oracle Network Environment
    • Tools for Configuring and Managing the Oracle Network
    • Accessing the Enterprise Manager Net Services Administration Page
    • Managing the Oracle Net Listener with Enterprise Manager
    • Using the Listener Control Utility
    • Connecting to an Oracle Database
    • Oracle Net: Naming Methods
    • Using Enterprise Manager to Configure Local Naming
    • Using Oracle Net Manager to Configure Local Naming
  • Managing the Oracle Instance
    • Oracle Instance Management
    • Oracle Processes
    • Oracle Memory Structures
    • Starting the Instance and Opening the Database
    • Shutting Down an Oracle Database Instance
    • Viewing and Modifying Initialization Parameters
    • Managing Memory Components
    • Viewing the Alert Log
  • Managing Database Storage Structures
    • Oracle Database Structures
    • Exploring the Storage Structure of the Oracle Database
    • Multiplexing the Redo Log
    • Enlarging the Database
    • Creating, Modifying, and Dropping Tablespaces
    • Reclaiming Space in Your Tablespace
    • Managing Undo Data
    • Using the Undo Advisor to Compute the Minimum Undo Tablespace Size
  • Administering Users and Security
    • Overview of Users and Security
    • Oracle-Defined Users
    • Creating, Modifying, and Dropping Users
    • Locking and Unlocking Accounts
    • Unlocking a User Account and Resetting the Password
    • Setting the Password Policy
    • Modifying the Default Password Policy
    • Administering Privileges and Roles
  • Managing Schema Objects
    • Creating, Modifying, and Dropping Tables
    • Understanding Data Integrity and Defining Constraints
    • Viewing the Contents of a Table
    • Creating and Dropping Indexes
    • Managing Views
    • Managing Program Code Stored in the Database
    • Managing Access to Schema Objects
    • Loading Data
  • Performing Backup and Recovery
    • Backup, Restore, and Recovery Concepts
    • Configuring the Database for Backup and Recovery
    • Configuring Backup Settings
    • Backing Up the Database
    • Using the Oracle-Suggested Backup Strategy
    • Performing Backup Maintenance Tasks
    • Using Oracle Advised Recovery to Recover the Database
    • Performing Flashback Table and Flashback Drop
  • Monitoring the Database and Using the Advisors
    • Monitoring General Database State, Workload, and Performance
    • Setting Metric Thresholds
    • Setting Up Direct Alert Notification
    • Diagnosing Performance Problems
    • Viewing ADDM Performance Analysis and Responding to Findings
    • Using the SQL Tuning and SQL Access Advisors
    • Configuring the Automatic SQL Tuning Advisor
    • Viewing Automatic SQL Tuning Results
  • Investigating, Reporting, and Resolving Problems
    • Using the Enterprise Manager Support Workbench
    • Workflow for Investigating, Reporting, and Resolving a Problem
    • Performing the Tasks to Investigate and Report a Critical Error
  • Managing Oracle Database Software
    • Understanding Software Management and Patch Releases
    • Patching Oracle Database Software
    • Using the Database Upgrade Assistant to Upgrade the Database

 
Duration: 16 Hours
Course Fee: INR. 20,000 + Tax


Oracle Database 11g: Administration Workshop I Release 2

Course Outline

 

  • Exploring the Oracle Database Architecture
    • Oracle Database Architecture Overview
    • Interacting with an Oracle Database
    • Process Architecture
    • Database Storage Architecture
    • Logical and Physical Database Structures
    • Tablespaces and Data Files
    • SYSTEM and SYSAUX Tablespaces
    • Segments, Extents, and Blocks
  • Preparing the Database Environment
    • Tasks of an Oracle Database Administrator
    • Tools Used to Administer an Oracle Database
    • Installation: System Requirements
    • Optimal Flexible Architecture (OFA)
    • Setting Environment Variables
    • Oracle Universal Installer (OUI)
    • Database Configuration Options
    • Advanced Installation Options
  • Creating an Oracle Database
    • Planning the Database
    • Configuring the Listener
    • Using the DBCA to Create a Database
    • Password Management
    • Creating a Database Design Template
    • Using the DBCA to Delete a Database
    • Using DBCA For Additional Tasks
  • Managing the Oracle Instance
    • Management Framework
    • Oracle Enterprise Manager
    • Initialization Parameter
    • Database Startup and Shutdown
    • Shutdown Modes
    • Viewing the Alert History
    • Using Trace Files
    • Data Dictionary: Overview
  • Configuring the Oracle Network Environment
    • Oracle Net Services & Listener
    • Establishing Net Connections
    • Tools for Configuring and Managing the Oracle Network
    • Net Services Administration Pages
    • Naming Methods
    • Directory Naming
    • SGA and PGA
    • When Not to Use a Shared Server
  • Managing Database Storage Structures
    • Storage Structures
    • How Table Data Is Stored
    • Anatomy of a Database Block
    • Space Management in Tablespaces
    • Tablespaces in the Preconfigured Database
    • Actions with Tablespaces
    • Oracle Managed Files (OMF)
    • ASM: Concepts
  • Administering User Security
    • Database User Accounts
    • Predefined Accounts: SYS and SYSTEM
    • Administrator Authentication
    • Benefits of Roles
    • Predefined Roles
    • Implementing Password Security Features
    • Assigning Quota to Users
  • Managing Schema Objects
    • What Is a Schema?
    • Accessing Schema Objects
    • Specifying Data Types in Tables
    • Creating and Modifying Tables
    • Understanding Data Integrity
    • Indexes & Views
    • Sequences
    • Temporary Tables
  • Managing Data and Concurrency
    • Manipulating Data Through SQL
    • PL/SQL
    • Administering PL/SQL Objects
    • Functions, Procedures, Packages & Triggers
    • Data Concurrency
    • Enqueue Mechanism
    • Resolving Lock Conflicts Using SQL
    • Deadlocks
  • Managing Undo Data
    • Data Manipulation
    • Transactions and Undo Data
    • Undo Data Versus Redo Data
    • Configuring Undo Retention
    • Guaranteeing Undo Retention
    • Using the Undo Advisor
    • Viewing System Activity
  • Implementing Oracle Database Security
    • Industry Security Requirements
    • Principle of Least Privilege
    • Monitoring for Compliance
    • Value-Based Auditing
    • Fine-Grained Auditing
    • DBA Auditing
    • Security Updates
    • Applying Security Patches
  • Database Maintenance
    • Introducing Terminology
    • Optimizer Statistics
    • Automatic Workload Repository (AWR)
    • Automatic Database Diagnostic Monitor (ADDM)
    • Advisory Framework
    • Automated Maintenance Tasks
    • Server-Generated Alerts
    • Reacting to Alerts
  • Performance Management
    • Performance Monitoring
    • Managing Memory Components
    • Enabling Automatic Memory Management (AMM)
    • Automatic Shared Memory Advisor
    • Using Memory Advisors
    • Dynamic Performance Statistics
    • Troubleshooting and Tuning Views
    • Invalid and Unusable Objects
  • Backup and Recovery Concepts
    • Part of Your Job
    • Statement Failure
    • User Error
    • Understanding Instance Recovery
    • Phases of Instance Recovery
    • Using the MTTR Advisor
    • Media Failure
    • Archive Log Files
  • Performing Database Backups
    • Backup Solutions: Overview
    • Oracle Secure Backup
    • User-Managed Backup
    • Terminology
    • Recovery Manager (RMAN)
    • Configuring Backup Settings
    • Backing Up the Control File to a Trace File
    • Monitoring the Flash Recovery Area
  • Performing Database Recovery
    • Opening a Database
    • Data Recovery Advisor
    • Loss of a Control File
    • Loss of a Redo Log File
    • Data Recovery Advisor
    • Data Failures
    • Listing Data Failures
    • Data Recovery Advisor Views
  • Moving Data
    • Moving Data: General Architecture
    • Directory Object: Overview
    • SQL*Loader: Overview
    • Data Pump: Overview
    • Performance Initialization Parameters
    • Using Enterprise Manager to Monitor Data Pump Jobs
    • Data Dictionary
  • Enhancing Database Capabilities
    • Using EM Support Workbench
    • Create a Service Request
    • Package and upload diagnostic data to Oracle Support
    • Track the SR and Implement Repairs
    • Incident Packaging Configuration
    • Working with Oracle Support
    • MetaLink Integration
    • Managing Patches

 
Duration: 40 Hours
Course Fee: INR. 40,000 + Tax


Oracle Database 11g: Administration Workshop II Release 2

Course Outline

 

  • Exploring the Oracle Database Architecture
    • Oracle Database Architecture Overview
    • Interacting with an Oracle Database
    • Process Architecture
    • Database Storage Architecture
    • Logical and Physical Database Structures
    • Tablespaces and Data Files
    • SYSTEM and SYSAUX Tablespaces
    • Segments, Extents, and Blocks
  • Preparing the Database Environment
    • Tasks of an Oracle Database Administrator
    • Tools Used to Administer an Oracle Database
    • Installation: System Requirements
    • Optimal Flexible Architecture (OFA)
    • Setting Environment Variables
    • Oracle Universal Installer (OUI)
    • Database Configuration Options
    • Advanced Installation Options
  • Creating an Oracle Database
    • Planning the Database
    • Configuring the Listener
    • Using the DBCA to Create a Database
    • Password Management
    • Creating a Database Design Template
    • Using the DBCA to Delete a Database
    • Using DBCA For Additional Tasks
  • Managing the Oracle Instance
    • Management Framework
    • Oracle Enterprise Manager
    • Initialization Parameter
    • Database Startup and Shutdown
    • Shutdown Modes
    • Viewing the Alert History
    • Using Trace Files
    • Data Dictionary: Overview
  • Configuring the Oracle Network Environment
    • Oracle Net Services & Listener
    • Establishing Net Connections
    • Tools for Configuring and Managing the Oracle Network
    • Net Services Administration Pages
    • Naming Methods
    • Directory Naming
    • SGA and PGA
    • When Not to Use a Shared Server
  • Managing Database Storage Structures
    • Storage Structures
    • How Table Data Is Stored
    • Anatomy of a Database Block
    • Space Management in Tablespaces
    • Tablespaces in the Preconfigured Database
    • Actions with Tablespaces
    • Oracle Managed Files (OMF)
    • ASM: Concepts
  • Administering User Security
    • Database User Accounts
    • Predefined Accounts: SYS and SYSTEM
    • Administrator Authentication
    • Benefits of Roles
    • Predefined Roles
    • Implementing Password Security Features
    • Assigning Quota to Users
  • Managing Schema Objects
    • What Is a Schema?
    • Accessing Schema Objects
    • Specifying Data Types in Tables
    • Creating and Modifying Tables
    • Understanding Data Integrity
    • Indexes & Views
    • Sequences
    • Temporary Tables
  • Managing Data and Concurrency
    • Manipulating Data Through SQL
    • PL/SQL
    • Administering PL/SQL Objects
    • Functions, Procedures, Packages & Triggers
    • Data Concurrency
    • Enqueue Mechanism
    • Resolving Lock Conflicts Using SQL
    • Deadlocks
  • Managing Undo Data
    • Data Manipulation
    • Transactions and Undo Data
    • Undo Data Versus Redo Data
    • Configuring Undo Retention
    • Guaranteeing Undo Retention
    • Using the Undo Advisor
    • Viewing System Activity
  • Implementing Oracle Database Security
    • Industry Security Requirements
    • Principle of Least Privilege
    • Monitoring for Compliance
    • Value-Based Auditing
    • Fine-Grained Auditing
    • DBA Auditing
    • Security Updates
    • Applying Security Patches
  • Database Maintenance
    • Introducing Terminology
    • Optimizer Statistics
    • Automatic Workload Repository (AWR)
    • Automatic Database Diagnostic Monitor (ADDM)
    • Advisory Framework
    • Automated Maintenance Tasks
    • Server-Generated Alerts
    • Reacting to Alerts
  • Performance Management
    • Performance Monitoring
    • Managing Memory Components
    • Enabling Automatic Memory Management (AMM)
    • Automatic Shared Memory Advisor
    • Using Memory Advisors
    • Dynamic Performance Statistics
    • Troubleshooting and Tuning Views
    • Invalid and Unusable Objects
  • Backup and Recovery Concepts
    • Part of Your Job
    • Statement Failure
    • User Error
    • Understanding Instance Recovery
    • Phases of Instance Recovery
    • Using the MTTR Advisor
    • Media Failure
    • Archive Log Files
  • Performing Database Backups
    • Backup Solutions: Overview
    • Oracle Secure Backup
    • User-Managed Backup
    • Terminology
    • Recovery Manager (RMAN)
    • Configuring Backup Settings
    • Backing Up the Control File to a Trace File
    • Monitoring the Flash Recovery Area
  • Performing Database Recovery
    • Opening a Database
    • Data Recovery Advisor
    • Loss of a Control File
    • Loss of a Redo Log File
    • Data Recovery Advisor
    • Data Failures
    • Listing Data Failures
    • Data Recovery Advisor Views
  • Moving Data
    • Moving Data: General Architecture
    • Directory Object: Overview
    • SQL*Loader: Overview
    • Data Pump: Overview
    • Performance Initialization Parameters
    • Using Enterprise Manager to Monitor Data Pump Jobs
    • Data Dictionary
  • Enhancing Database Capabilities
    • Using EM Support Workbench
    • Create a Service Request
    • Package and upload diagnostic data to Oracle Support
    • Track the SR and Implement Repairs
    • Incident Packaging Configuration
    • Working with Oracle Support
    • MetaLink Integration
    • Managing Patches

 
Duration: 40 Hours
Course Fee: INR. 40,000 + Tax


Oracle Database 11g: Advanced PL/SQL

Course Outline

 

  • Introduction
    • Course objectives
    • Course agenda
    • Tables and data used for this course
    • Overview of the development environments: SQL Developer, SQL Plus
  • PL/SQL Programming Concepts Review
    • Identify PL/SQL block structure
    • Create procedures
    • Create functions
    • List restrictions and guidelines on calling functions from SQL expressions
    • Create packages
    • Review of implicit and explicit cursors
    • List exception syntax
    • Identify the Oracle supplied packages
  • Designing PL/SQL Code
    • Describe the predefined data types
    • Create subtypes based on existing types for an application
    • List the different guidelines for cursor design
    • Cursor variables
  • Using Collections
    • Overview of collections
    • Use Associative arrays
    • Use Nested tables
    • Use VARRAYs
    • Compare nested tables and VARRAYs
    • Write PL/SQL programs that use collections
    • Use Collections effectively
  • Manipulating Large Objects
    • Describe a LOB object
    • Use BFILEs
    • Use DBMS_LOB.READ and DBMS_LOB.WRITE to manipulate LOBs
    • Create a temporary LOB programmatically with the DBMS_LOB package
    • Introduction to SecureFile LOBs
    • Use SecureFile LOBs to store documents
    • Convert BasicFile LOBs to SecureFile LOB format
    • Enable reduplication and compression
  • Using Advanced Interface Methods
    • Calling External Procedures from PL/SQL
    • Benefits of External Procedures
    • C advanced interface methods
    • Java advanced interface methods
  • Performance and Tuning
    • Understand and influence the compiler
    • Tune PL/SQL code
    • Enable intra unit inlining
    • Identify and tune memory issues
    • Recognize network issues
  • Improving Performance with Caching
    • Describe result caching
    • Use SQL query result cache
    • PL/SQL function cache
    • Review PL/SQL function cache considerations
  • Analyzing PL/SQL Code
    • Finding Coding Information
    • Using DBMS_DESCRIBE
    • Using ALL_ARGUMENTS
    • Using DBMS_UTILITY.FORMAT_CALL_STACK
    • Collecting PL/Scope Data
    • The USER/ALL/DBA_IDENTIFIERS Catalog View
    • DBMS_METADATA Package
  • Profiling and Tracing PL/SQL Code
    • Tracing PL/SQL Execution
    • Tracing PL/SQL: Steps
  • Implementing VPD with Fine-Grained Access Control
    • Understand how fine-grained access control works overall
    • Describe the features of fine-grained access control
    • Describe an application context
    • Create an application context
    • Set an application context
    • List the DBMS_RLS procedures
    • Implement a policy
    • Query the dictionary views holding information on fine-grained access
  • Safeguarding Your Code Against SQL Injection Attacks
    • SQL Injection Overview
    • Reducing the Attack Surface
    • Avoiding Dynamic SQL
    • Using Bind Arguments
    • Filtering Input with DBMS_ASSERT
    • Designing Code Immune to SQL Injections
    • Testing Code for SQL Injection Flaws

 
Duration: 24 Hours
Course Fee: INR. 25,000 + Tax


Oracle Database 11g: Introduction to SQL

Course Outline

 

  • Introducing Oracle Database 11g
    • List the features of Oracle Database 11g
    • Discuss the basic design, theoretical and physical aspects of a relational database
    • Categorize the different types of SQL statements
    • Describe the data set used by the course
    • Log onto the database using the SQL Developer environment
    • Save queries to files and use script files in SQL Developer
  • Retrieving Data Using the SQL SELECT Statement
    • List the capabilities of SQL SELECT statements
    • Generate a report of data from the output of a basic SELECT statement
    • Select All Columns
    • Select Specific Columns
    • Use Column Heading Defaults
    • Use Arithmetic Operators
    • Understand Operator Precedence
    • Learn the DESCRIBE command to display the table structure
  • Restricting and Sorting Data
    • Write queries that contain a WHERE clause to limit the output retrieved
    • List the comparison operators and logical operators that are used in a WHERE clause
    • Describe the rules of precedence for comparison and logical operators
    • Use character string literals in the WHERE clause
    • Write queries that contain an ORDER BY clause sort the output of a SELECT statement
    • Sort output in descending and ascending order
  • Using Single-Row Functions to Customize Output
    • Describe the differences between single row and multiple row functions
    • Manipulate strings with character function in the SELECT and WHERE clauses
    • Manipulate numbers with the ROUND, TRUNC and MOD functions
    • Perform arithmetic with date data
    • Manipulate dates with the date functions
  • Using Conversion Functions and Conditional Expressions
    • Describe implicit and explicit data type conversion
    • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
    • Nest multiple functions
    • Apply the NVL, NULLIF, and COALESCE functions to data
    • Use conditional IF THEN ELSE logic in a SELECT statement
  • Reporting Aggregated Data Using the Group Functions
    • Use the aggregation functions in SELECT statements to produce meaningful reports
    • Create queries that divide the data in groups by using the GROUP BY clause
    • Create queries that exclude groups of date by using the HAVING clause
  • Displaying Data From Multiple Tables
    • Write SELECT statements to access data from more than one table
    • View data that generally does not meet a join condition by using outer joins
    • Join a table by using a self join
  • Using Sub-queries to Solve Queries
    • Describe the types of problem that sub-queries can solve
    • Define sub-queries
    • List the types of sub-queries
    • Write single-row and multiple-row sub-queries
  • Using the SET Operators
    • Describe the SET operators
    • Use a SET operator to combine multiple queries into a single query
    • Control the order of rows returned when using the SET operators
  • Manipulating Data
    • Describe each DML statement
    • Insert rows into a table with the INSERT statement
    • Use the UPDATE statement to change rows in a table
    • Delete rows from a table with the DELETE statement
    • Save and discard changes with the COMMIT and ROLLBACK statements
    • Explain read consistency
  • Using DDL Statements to Create and Manage Tables
    • Categorize the main database objects
    • Review the table structure
    • List the data types available for columns
    • Create a simple table
    • Decipher how constraints can be created at table creation
    • Describe how schema objects work
  • Creating Other Schema Objects
    • Create a simple and complex view
    • Retrieve data from views
    • Create, maintain, and use sequences
    • Create and maintain indexes
    • Create private and public synonyms
  • Controlling User Access
    • Differentiate system privileges from object privileges
    • Grant privileges on tables
    • View privileges in the data dictionary
    • Grant roles
    • Distinguish between privileges and roles
  • Managing Schema Objects
    • Add constraints
    • Create indexes
    • Create indexes using the CREATE TABLE statement
    • Create function-based indexes
    • Drop columns and set column UNUSED
    • Perform FLASHBACK operations
    • Create and use external tables
  • Managing Objects with Data Dictionary Views
    • Explain the data dictionary
    • Find table information
    • Report on column information
    • View constraint information
    • Find view information
    • Verify sequence information
    • Understand synonyms
    • Add comments
  • Manipulating Large Data Sets
    • Manipulate data using sub-queries
    • Describe the features of multi-table inserts
    • Use the different types of multi-table inserts
    • Merge rows in a table
    • Track the changes to data over a period of time
  • Managing Data in Different Time Zones
    • Use data types similar to DATE that store fractional seconds and track time zones
    • Use data types that store the difference between two date-time values
    • Practice using the multiple data-time functions for globalize applications
  • Retrieving Data Using Sub-queries
    • Write a multiple-column sub-query
    • Use scalar sub-queries in SQL
    • Solve problems with correlated sub-queries
    • Update and delete rows using correlated sub-queries
    • Use the EXISTS and NOT EXISTS operators
    • Use the WITH clause
  • Regular Expression Support
    • List the benefits of using regular expressions
    • Use regular expressions to search for, match, and replace strings

 
Duration: 24 Hours
Course Fee: INR. 20,000 + Tax


Oracle Database 11g: Performance Tuning DBA Release 2

Course Outline

 

  • Introduction
    • This lesson introduces the Performance Tuning course objectives and agenda
  • Basic Tuning Tools
    • Monitoring tools overview
    • Enterprise Manager
    • V$ Views, Statistics and Metrics
    • Wait Events
  • Using Automatic Workload Repository
    • Managing the Automatic Workload RepositoryCreate AWR Snapshots
    • Real Time SQL Monitoring (a 11.1 feature new lesson in NF L-15)
  • Defining Problems
    • Defining the Problem
    • Limit the Scope Setting the Priority
    • Top SQL Reports
    • Common Tuning Problems Tuning During the Life Cycle
    • ADDM Tuning Session
    • Performance Versus Business Requirements
    • Performance Tuning Resources Filing a Performance Service Request
    • Monitoring and Tuning Tools: Overview
  • Using Metrics and Alerts
    • Metrics, Alerts, and Baselines
    • Limitation of Base Statistics Typical Delta Tools
    • Oracle Database 11g Solution: Metrics
    • Benefits of Metrics
    • Viewing Metric History Information Vsing EM to View Metric Details
    • Statistic Histograms Histogram Views
    • Database Control Usage Model Setting Thresholds
    • Server-Generated Alerts, Creating and Testing an Alert Metric and Alert Views
  • Using Baselines
    • Comparative Performance Analysis with AWR Baselines
    • Automatic Workload Repository Baselines
    • Moving Window Baseline
    • Baselines in Performance Page Settings Baseline Templates
    • AWR Baselines Creating AWR Baselines
    • Managing Baselines with PL/SQL Baseline Views
    • Performance Monitoring and Baselines Defining Alert Thresholds Using a Static Baseline
    • Using EM to Quickly Configure Changing Adaptive Threshold Settings
  • Using AWR Based Tools
    • Automatic Maintenance Tasks
    • ADDM Performance Monitoring
    • Active Session History: Overview
  • Monitoring an Application
    • What Is a Service? Service Attributes Service Types
    • Creating Services Managing Services in a Single-Instance Environment
    • Everything Switches to Services.
    • Using Services with Client Applications Using Services with the Resource Manager
    • Services and Resource Manager with EM Using Services with the Scheduler
    • Using Services with Parallel Operations Metric Thresholds
    • Service Aggregation and Tracing Service Aggregation Configuration.
    • Client Identifier Aggregation and Tracing Service Performance Views
  • Identifying Problem SQL Statements
    • SQL Statement Processing Phases Role of the Oracle Optimizer
    • Identifying Bad SQL, Real Time SQL Monitoring (a 11.1 feature new lesson in NF L-15) TOP SQL Reports
    • What Is an Execution Plan? Methods for Viewing Execution Plans Uses of Execution Plans
    • DBMS_XPLAN Package: Overview EXPLAIN PLAN Command
    • Reading an Execution Plan, Using the V$SQL_PLAN View Querying the AWR
    • SQL*Plus AUTOTRACE SQL Trace Facility
    • How to Use the SQL Trace Facility
    • Generate an Optimizer Trace
  • Influencing the Optimizer
    • Functions of the Query Optimizer, Selectivity, Cardinality and Cost Changing Optimizer Behavior
    • Using Hints, Optimizer Statistics Extended Statistics
    • Controlling the Behavior of the Optimizer with Parameters
    • Enabling Query Optimizer Features Influencing the Optimizer Approach
    • Optimizing SQL Statements, Access Paths Choosing an Access Path
    • Join Sort Operations
    • How the Query Optimizer Chooses Execution Plans for Joins
    • Reducing the Cost
  • Using SQL Performance Analyzer
    • Real Application Testing: Overview Use Cases
    • SQL Performance Analyzer: Process Capturing the SQL Workload
    • Creating a SQL Performance Analyzer Task SPA (NF Lesson 9) DBMS_SQLTUNE.CREATE_TUNING_TASK
    • Optimizer Upgrade Simulation SQL Performance Analyzer Task Page
    • Comparison Report Comparison Report SQL Detail
    • Tuning Regressing Statements Preventing Regressions
    • Parameter Change Analysis Guided Workflow Analysis
    • SQL Performance Analyzer: PL/SQL Example Data Dictionary Views
  • SQL Performance Management
    • Maintaining SQL Performance and Optimizer Statistics Automated Maintenance Tasks
    • Statistic Gathering Options Setting Statistic Preferences
    • Restore Statistics
    • Deferred Statistics Publishing: Overview Example
    • Automatic SQL Tuning: Overview
    • SQL Tuning Advisor: Overview
    • Using the SQL Access Advisor
    • SQL Plan Management: Overview
  • Using Database Replay
    • The Big Picture System Architecture
    • Capture Replay Considerations
    • Replay Options Analysis
    • Database Replay Workflow in Enterprise Manager
    • Packages and Procedures
    • Data Dictionary Views: Database Replay
    • Database Replay: PL/SQL Example
    • Calibrating Replay Clients
  • Tuning the Shared Pool
    • Shared Pool Architecture Operation
    • The Library Cache Latch and Mutex
    • Diagnostic Tools for Tuning the Shared Pool
    • Avoiding Hard Soft Parses
    • Sizing the Shared Pool Avoiding Fragmentation
    • Data Dictionary Cache SQL Query Result Cache
    • UGA and Oracle Shared Server
    • Large Pool Tuning the Large Pool
  • Tuning the Buffer Cache
    • Oracle Database Architecture: Buffer Cache
    • Database Buffers
    • Buffer Hash Table for Lookups
    • Working Sets
    • Buffer Cache Tuning Goals and Techniques
    • Buffer Cache Performance Symptoms Solutions
    • Automatically Tuned Multiblock Reads
    • Flushing the Buffer Cache (for Testing Only)
  • Tuning PGA and Temporary Space
    • SQL Memory Usage Performance Impact
    • SQL Memory Manager
    • Configuring Automatic PGA Memory Setting PGA_AGGREGATE_TARGET Initially
    • Monitoring Tuning SQL Memory Usage
    • PGA Target Advice Statistics Histograms
    • Automatic PGA and Enterprise Manager Automatic PGA and AWR Reports
    • Temporary Tablespace Management: Overview Monitoring Temporary Tablespace
    • Temporary Tablespace Shrink Tablespace Option for Creating Temporary Table
  • Automatic Memory Management
    • Oracle Database Architecture, Dynamic SGA Memory Advisories
    • Granule Manually Adding Granules to Components
    • Increasing the Size of an SGA Component, SGA Sizing Parameters Manually Resizing Dynamic SGA Parameters
    • Automatic Shared Memory Management Memory Broker Architecture
    • Behavior of Auto-Tuned Manually TunedSGA Parameters
    • Using the V$PARAMETER View Resizing SGA_TARGET
    • Disabling, Configuring Monitoring Automatic Shared Memory Management (ASMM)
    • Automatic Memory Management
  • Tuning Segment Space Usage
    • Space and Extent Management Locally Managed Extents
    • How Table Data Is Stored Anatomy of a Database Block
    • Minimize Block Visits
    • The DB_BLOCK_SIZE Parameter
    • Small Large Block Size: Considerations
    • Block Allocation, Free Lists Block Space Management with Free Lists
    • Automatic Segment Space Management
    • Migration and Chaining, Shrinking Segments Table Compression: Overview
  • Tuning I/O
    • I/O Architecture, File System Characteristics, I/O Modes Direct I/O
    • Bandwidth Versus Size Important I/O Metrics for Oracle Databases
    • I/O Calibration and Enterprise Manager, I/O Calibration and the PL/SQL Interface I/O Statistics and Enterprise Manager
    • Stripe and Mirror Everything
    • Using RAID
    • I/O Diagnostics
    • Database I/O Tuning
    • What Is Automatic Storage Management?
  • Performance Tuning Summary
    • Best practices identified throughout the course
    • Summarize the performance tuning methodology
  • Appendix B: Using Statspack
    • Installing Statspack
    • Capturing Statspack Snapshots
    • Reporting with Statspack
    • Statspack Considerations
    • Statspack and AWR Reports
    • Reading a Statspack Report
    • Statspack and AWR

 
Duration: 40 Hours
Course Fee: INR. 40,000 + Tax