Tag: Oracle 11g

Oracle 11g – Application Tuning – Workshop Training Course

Posted on by admin

Duration

28 hours (usually 4 days including breaks)

Requirements

  • The free use of the basics of SQL and knowledge of Oracle database environment (preferably Oracle 11g completion of training – Native SQL for Programmers – Workshops)
  • Practical experience in working with Oracle

Overview

For who

The workshop is intended for advanced programmers and Oracle users who seek knowledge and information on the efficient development of information systems in an Oracle database, and the tuning and testing of performance issues in existing applications. This course builds on knowledge often unavailable or incorrectly presented in the technical documentation, and collected during many years of practice leading them instructors. These workshops may be the end of the training path for developers, or a single step for people with extensive experience designing and programming in Oracle

Purpose of training

The workshop aims to provide mechanisms that occur in an Oracle database when performing SQL statements. Allows participants to avoid errors during software development, and explore, diagnose, and resolve performance problems in existing applications.

Particular emphasis is placed on the workshops, where we show the methodology and the practical aspects of the application and tuning SQL statements.

The content of the training

  • Mechanics perform SQL commands
  • Managing the process cost optimization
  • Methods of data storage and indexing
  • Monitoring database performance and processes based on dictionaries and track system applications
  • Analysis of cases of the most common problems that cause performance

Notes

The workshops are based on the software version 11g XE

Course Outline

Application Tuning Methodology

Architecture database and instance

  • Server processes
  • Memory structure (SGA, PGA)
  • Parsing and share cursors
  • The data files, log files, parameter files

Analysis of the command execution plan

  • Hypothetical plan (EXPLAIN PLAN, SQLPlus AutoTrac XPlane)
  • The actual execution plan (V $ SQL_PLAN, XPlane, AWR)

Monitoring the performance and find bottlenecks in the process

  • Monitoring the current status of the instance by system dictionary views
  • The monitoring of historical dictionaries
  • Tracking application (SQLTrace, TkProf, TreSess

The optimization process

  • Properties cost optimization and regulated
  • Determination to optimize

Control work cost-based optimizer by:

  • Session parameters and instance
  • Tips (hints)
  • Patterns of query plans

Statistics and Histograms

  • Impact statistics and histograms for performance
  • The methods of collecting statistics and histograms
  • Strategy of counting and estimating statistics
  • Management statistics: blocking, copying, editing, automation of collection, monitoring changes
  • Dynamic data sampling (temporary plates, complex predicates)
  • Multi-column statistics, based on expressions
  • Statistics System

The logical and physical structure of the database

  • Spaces tables.
  • segments
  • Extensions (EXTENTS)
  • Blocks

Data storage methods

  • The physical aspects of the table
  • temporary Tables
  • Tables index
  • external Tables
  • Partition Table (span, letter, hash, mixed)
  • Physical reorganization of tables

Materialized views and mechanism QUERY REWRITE

Methods of data indexing

  • Building B-TREE indexes
  • Properties index
  • Indexes: a unique, multi-column, function, inverse
  • Compression indices
  • Reconstruction and merging indexes
  • Virtual indexes
  • Indexes private and public
  • Bitmap Indexes and junction

Case study – full-scan data

  • The impact of a place at the table level and block performance readings
  • Loading Data conventional and direct path
  • The order of predicates

Case Study – access to data via the index

  • Methods of reading index (UNIQUE SCAN RANGE SCAN FULL SCAN FAST FULL SCAN MIN / MAX SCAN)
  • Using functional indices
  • The selectivity index (Clustering Factor)
  • Multi-column indexes and SKIP SCAN
  • NULL and indexes
  • Index tables (IOT)
  • Impact indices DML operations

Case Study – sorting

  • Sorting memory
  • Sort index
  • Sort linguistic
  • The effect of entropy to sort (Clustering Factor)

Case Study – joins and subqueries

  • The merger: MERGE, HASH, NESTED LOOP
  • Joins in OLTP and OLAP systems
  • The order of switching
  • Outer Joins
  • AntI-join
  • Joins incomplete (SEMI)
  • Subqueries simple
  • Correlated subqueries
  • The views, the WITH clause

Other operations cost-based optimizer

  • Buffer Sort
  • INLIST
  • VIEW
  • FILTER
  • Count Stop Key
  • Result Cache

Inquiries dispersed

  • Read query plans for use dblinks
  • Choosing the leading mark

Parallel processing

Oracle 11g – Programming with PL / SQL I – Workshops Training Course

Posted on by admin

Duration

28 hours (usually 4 days including breaks)

Requirements

  • Basic skills in any programming language, object-oriented structural or
  • The free use of the basics of SQL and knowledge of Oracle database environment (preferably Oracle 11g completion of training – Native SQL for Programmers – Workshops)

Overview

For who

Workshops are dedicated to developers, end users and administrators, who until now have had no contact with the language PL / SQL, and the need to exploit its huge potential in working with a database, automate processes, and in building applications

Exams and Certificates

The plan covers the training material required to pass the exam 1Z0-144 Oracle Database 11g Program with PL / SQL and obtain the title of Oracle PL / SQL Developer Certified Associate

Purpose of training

The workshop aims to familiarize participants with the programming language PL / SQL, its capabilities and limitations. This workshop will include a full understanding of the mechanisms involved in the programming language PL / SQL needed to implement the logic of the free applications, automation of data processing and database management.

The content of the training

  • Introduction to PL / SQL architecture solutions based on this language, the organization of the working environment
  • Create scripts and stored program units that operate on data

Notes

The workshops are based on the software version 11g XE

Course Outline

Introduction to PL / SQL

  • Runtime Environment
  • Construction and types of PL / SQL blocks
  • Declaring and using variables
  • Control statements, decisions, loops

SQL statements in PL / SQL

  • DML commands
  • DDL and dynamic SQL
  • TCL commands and transactional
  • SELECT

Procedures and Functions

  • Create and delete
  • Parameterization
  • Passing parameters by value and reference, nocopy

Handling errors and exceptions

Create and use cursors

  • Records
  • static cursors
  • Parameterizing cursors
  • Cursor FOR UPDATE

Associative arrays

Packages

  • The structure of the package: the specification and body
  • Section Initialization and global variables, memory management, an instance of the package
  • Encapsulation, overloading programs, pre-declaration procedures

Triggers

  • DML triggers
  • The triggers Drives (INSTEAD OF)
  • The triggers system
  • New features in Oracle 11g triggers

Examples of application packages built

  • Writing to a file through UTL_FILE
  • Sending e-mails

Management code and compiler

  • Encryption code (dynamic obfuscation, wrap)
  • conditional Compilation
  • The relationships between objects
  • Warnings

Oracle 11g – SQL language for developers – Workshop Training Course

Posted on by admin

Duration

35 hours (usually 5 days including breaks)

Requirements

Knowledge-wide information technology.

Overview

For who

Workshops are dedicated as a first step for developers and designers of applications based on Oracle databases. Participants do not need to have any prior knowledge of the Oracle database, or other relational database systems, even though such knowledge may be useful.

Exams and Certificates

The plan covers the training material required to pass the exam 1Z0-047 Oracle Database SQL Expert and obtain the title of Oracle Database SQL Certified Expert

Purpose of training

The workshop aims to familiarize participants with the Oracle database techniques to build database structures and data manipulation. Particular emphasis is placed on the participant to see across the board, which offers opportunities to design and build applications Relational Database Management System, Oracle Database, and to be able to independently work with her.

The content of the training

  • Introduction to database technology and the organization of the work environment
  • Acquisition and modification of data
  • Construction of the repository application
  • Safety and concurrency runtime

Notes

The workshops are based on the software version 11g XE

Course Outline

Introduction to the Oracle database

  • Database Architecture
  • Relational model database
  • Users diagrams sessions
  • Tools

Introduction to the SELECT statement

  • Screening and selection (WHERE clause)
  • Sorting
  • Data types, operators, and service NULL
  • Built-in scalar functions
  • Actions to date
  • National and regional settings in SQL

The analysis of aggregated data

  • Funkcje grupujące
  • Klauzula distinct
  • Klauzule GROUP BY and having

Retrieving data from multiple tables

  • Inner and outer joins (INNER JOIN, OUTER JOIN)
  • ANSI SQL syntax, and other methods connectors (SELF JOIN, NATURAL JOIN)
  • Collective operators (UNION, UNION ALL, INTERSECT, MINUS)

Subqueries

  • Subqueries simple
  • Correlated subqueries
  • Operators EXISTS and NOT EXISTS
  • Other types of subqueries

Inquiries hierarchical and samples

  • Construction of the tree (CONNECT BY PRIOR clause and START WITH)
  • The SYS_CONNECT_BY_PATH
  • Data samples (SAMPLE clause)

Data manipulation (DML)

  • “INSTRUCCIONES INSERT, UPDATE, DELETE
  • Operacja na dużych zbiorach (INSERT FIRST, ALL INSERT, MERGE)

Dictionary system

Concurrent users work

  • Transactions
  • Locks
  • FLASHBACK

Users and Permissions

  • Creating and modifying user patterns
  • Permissions and roles

Managing data storage – logical layer

  • Tables, temporary tables, index-organized tables
  • Limitations
  • Indexes
  • The views, sequences, synonyms, materialized views
  • Units stored PL / SQL

Modeling and restore the data model using Oracle SQL Modeler

Moving Data

  • A logical copy of the data – datapump import and export
  • Loading data – sqlLoader
  • External tables
  • Links database

Automating tasks

  • dbms_jobs, dbms_scheduler

Oracle 11g – SQL language for administrators – workshops Training Course

Posted on by admin

Duration

21 hours (usually 3 days including breaks)

Overview

For who

The workshop is intended for beginners, starting to work with the Oracle database, future administrators and users of systems based on this database need able to use the SQL language for the extraction and modification of the information contained in the systems.

Exams and Certificates

The training plan coincides with the material required to pass the exam: 1Z0-051 Oracle Database 11g: SQL Fundamentals I, the first step to getting most certifications Oracle’s Database

Purpose of training

The workshop aims to introduce participants to work with the Oracle database, making them familiar with the SQL language to the extent required for the efficient operation of the system and to take in the future obligations of database applications and Oracle databases.

The content of the training

  • The organization of the working environment
  • Introduction to relational databases
  • Extraction of the data using the SELECT statement
  • Modifying data using INSERT, UPDATE, DELETE
  • Overview of schema objects

Remarks

The workshops are based on 11g XE software

Course Outline

Introduction to Oracle

  • Database Architecture
  • Relational model database
  • Users diagrams sessions
  • Tools

Introduction to the SELECT statement

  • Screening and selection (WHERE clause)
  • sorting
  • Data types, operators, and service NULL
  • Built-in scalar functions
  • Actions to date
  • National and regional settings in SQL

Regular expressions

The analysis of aggregated data

  • Grouping functions
  • DISTINCT clause
  • Clauses GROUP BY and HAVING

Retrieving data from multiple tables

  • Inner and outer joins (INNER JOIN, OUTER JOIN)
  • ANSI SQL syntax, and other methods connectors (SELF JOIN, NATURAL JOIN)
  • Collective operators (UNION, UNION ALL, INTERSECT, MINUS)

Subqueries

  • Subqueries simple
  • Correlated subqueries
  • Operators EXISTS and NOT EXISTS
  • Other types of subqueries

Inquiries hierarchical and samples

  • Construction of the tree (CONNECT BY PRIOR clause and START WITH)
  • The SYS_CONNECT_BY_PATH
  • Data samples (SAMPLE clause)

Data manipulation (DML)

  • INSERT, UPDATE, DELETE
  • Operations on a large set of (INSERT FIRST INSERT ALL, MERGE)

Concurrent users work

  • Transactions
  • Locks
  • FLASHBACK

Overview of schema objects

  • Vistas
  • Sequences
  • Synonyms private and public
  • Indexes