Oracle PLSQL Training in Chennai

Oracle sql and plsql Training in ChennaiOracle PLSQL Training with XML in Chennai from the Best Oracle PLSQL Training Institute in Chennai. Learn Oracle PL SQL from the Best Oracle PLSQL Training center in Chennai with the most experienced trainer in the field. Greens Technology Adyar provides Oracle SQL, PLSQL, Performance Tuning training in Chennai to professionals and corporates on advanced SQL, PL SQL with XML, Oracle Database 12c Analytic SQL for Data Warehousing and Performance Tuning.

Oracle PLSQL Training in Chennai provide basic to advanced Oracle PLSQL Classes in Chennai with Placement and Certification

Oracle PL/SQL Performance Tuning Training Course Training in Chennai for Developers. The PL/SQL tuning class topics start with the basic PL/SQL performance tuning and progress into increasingly complex tuning techniques, including bulking, optimizing calls to Oracle and using ref cursors. We guarantee your success via our optional PL/SQL mentoring and the students will gradually masters PL/SQL through increasingly challenging classroom exercises.

Free Demo Class: Try two Oracle FREE CLASS to see for yourself the quality of training.

By the end of this course the student will understand advanced PL/SQL tuning procedures, and will be able to use advanced tuning features such as ref cursors and bulk fetches. The student will also become familiar with the internals of Oracle PL/SQL and will be able to compile PL/SQL for super-fast performance. The overall goal for the class will be to leave the Oracle developer, programmer or analyst with the skills to expose a SQL execution plan, evaluate a plan for optimal execution and how to modify an execution plan for faster performance and throughput.

Oracle Database SQL Tuning training helps SQL and PL/SQL developers identify SQL statements that need tuning by explaining the process using Oracle Database 11g automatic tuning features, including SQL Tuning Advisor, SQL Access Advisor, Automatic Workload Repository and Automatic Database Diagnostic Monitor. It counts toward the hands-on course requirement for Oracle Database Administrator Certification.

Oracle SQL and PL/SQL Expert Training: Learn SQL from the same team involved in Oracle product development.

Oracle SQL and PLSQL Tuning Training in Chennai

  • 1Z0-054 Oracle Database 11g: Performance Tuning
  • 1Z0-117 Oracle Database 11g Release 2: SQL Tuning

Oracle SQL and PLSQL Certification Training

  • 1Z0-061 Oracle Database 12c: SQL Fundamentals
  • 1Z0-047 Oracle Database SQL Expert
  • 1Z0-051 Oracle Database 11g: SQL Fundamentals I
  • 1Z0-147 Program with PL/SQL
  • 1Z0-144 Oracle Database 11g: Program with PL/SQL
  • 1Z0-146 Oracle Database 11g: Advanced PL/SQL

Oracle PLSQL Training includes Analytic SQL for Data Warehousing on both Classroom Trainings and Online Trainings

Oracle Database SQL and PL/SQL Training in Chennai

This Oracle Database SQL and PL/SQL training delivers the fundamentals of SQL and PL/SQL along with Develop anonymous PL/SQL blocks, stored procedures, Functions, Packages, Triggers including Cursors, PLSQL Collections, Bulk Collect and more

Oracle SQL Tuning Training and Certification

  • 1Z0-117 Oracle Database 11g Release 2: SQL Tuning

Oracle SQL Query Tuning Training Course Content

Introduction to SQL Tuning

  • Find a workaround to enhance performance
  • Analyze a poorly written SQL statement
  • Create a function based index
  • Redesign a simple table
  • Rewrite the SQL statement

Using SQL Trace Facility and TKPROF

  • Explore a trace file to understand the optimizer’s decisions

Understand Basic Tuning Techniques

  • Rewrite queries for better performance
  • Rewrite applications for better performance
  • Utilize SQL Tuning Advisor using SQL Developer 3.0

Optomizer Fundamentals

  • Explore a trace file to understand the optimizer’s decisions

Understanding Serial Execution Plans

  • Use different techniques to extract execution plans
  • Use SQL monitoring

Optimizer: Table and Index Access Paths

  • Learn about using different access paths for better optimization

Optimizer: Join Operations

  • Use different access paths for better optimization
  • Examine and use the result cache

Other Optimizer Methods

  • Use different access paths for better optimization
  • Examine and use the result cache

Optimizer Statistics

  • Analyze and use system statistics
  • Use automatic statistics gathering

Using Bind Variables

  • Use adaptive cursor sharing and bind peeking
  • Use the CURSOR_SHARING initialization parameter

SQL Plan Management

  • Use SQL Plan Management

Inside the Oracle Optimizer

  • Job of the SQL optimizer
  • Parsing, semantic check, decision tree, generating plan
  • How the Oracle optimizer chooses the “best:” execution plan
  • The Oracle Optimizer SQL cache (library cache)

Oracle optimization goals

  • The Oracle Optimizer: Rule Based Optimization
  • The Oracle Optimizer: Cost Based Optimization
  • The all_rows vs first_rows_n optimizer modes
  • Generating optimizer Statistics for tables and system

Table Join internals

  • Oracle Joins, Join Methods and Access Paths
  • Nested loops, hash joins, merge joins
  • The star transformation

Evaluating full-table scans

  • Understanding multi-block reads (db_file_scattered_reads)
  • Threshold between full table scan and index range scan
  • Parallelizing full-table scans
  • When full table scans are evil
  • Using plan91.sql and plan10g.sql

Oracle Index Optimization

  • Oracle Index types
  • Index cardinality
  • SQL with multi-column indexes
  • SQL with bitmap indexes
  • Index unique scans vs. index range scans
  • Finding missing indexes

Oracle rowset sorting

  • When a sort is invoked (order by, group by, etc.)
  • Sorting with indexes vs internal sorting
  • Detecting disk sorts

Day 2: SQL Execution plan evaluation

2-1: The Goals of SQL tuning

  • Evaluate large-table full-table scans
  • Evaluate table join order
  • Evaluate index access

2 - 2: Exposing the SQL execution plans

  • The autotrace command
  • Using the explain plan facility

Reading an execution plan

  • Evaluating the steps of an execution plan
  • The cost and cardinality columns of an execution plan
  • Adding RAM usage details to an execution plan

Tracing SQL execution plans:

  • Using TKPROF
  • Using the 10046 trace event

Changing SQL: execution plans

  • Using optimizer directives (hints)
  • Changing plans with histograms
  • Changing plans with dynamic sampling

Freezing execution plans:

  • Freezing plans with multiple hints
  • Using optimizer plan stability
  • Using SQL profiles

1 – Changing SQL Execution Plans

  • Using hints
  • Changing the system-wide optimizer mode
  • Changing optimizer mode for specific statements
  • Re-writing SQL queries
  • Table join order evaluation
  • Using the ordered hint
  • Exercise – Add hints to queries

2 – Database-level SQL Tuning

  • Inside the library cache – SQL reusability
  • Avoiding SQL re-parsing - using host variables
  • Cursor Sharing
  • Optimizer Plan Stability a.k.a. stored outlines
  • 10g SQL Profiles
  • Parallel DML
  • Parallel SQL and partitions
  • SQL and the data warehouse
  • Exercise – invoke parallel query

3a – SQL and Boolean expressions

  • Equality conditions
  • Using the LIKE, BETWEEN, EXISTS and NOT
  • Using the decode and CASE operators
  • Exercise – Using Decode and CASE

3b – Using Built-in Functions (BIF’s)

  • Basics of BIFs
  • Function-based indexing
  • Creating a custom BIF using PL/SQL
  • The effect of BIFs on SQL execution
  • Exercise – Create a custom BIF and use it in SQL

4a – Aggregate Functions

  • Grouping in several levels
  • Grouping and NULLS
  • CUBE and ROLLUP
  • Performance and grouping
  • Exercise – Using rollup and cube

4b – Tuning SQL subqueries

  • Correlated subqueries
  • Non-correlated subqueries
  • Replacing subqueries with joins
  • Exercise – Tune a complex query

2 - Optimizing SQL RAM Resources

  • PGA Overview
  • SQL Sorting
  • SQL hash joins
  • Super-sizing PGA regions

5 – Oracle execution plan costing

  • The “best” execution plan (first_rows vs. all_rows)
  • Rule-based optimization
  • Cost-based optimization
  • Gathering statistics
  • Using histograms
  • When the optimizers fail
  • Exercise – Change the optimizer mode

6 – Table joining techniques

  • Sort-merge joins
  • Nested Loop joins
  • Hash joins
  • STAR joins
  • Bitmap joins
  • Exercise – Change table join techniques & evaluate performance

DAY 3 – Advanced Oracle SQL tuning

  • 1 – Indexing techniques
  • B-Tree indexes
  • Bitmap Indexes
  • Function-based Indexes
  • Clustered indexes
  • Index-only tables
  • Exercise – Create and use bitmap index

2 – Table structures and SQL Performance

  • Effect of PCTFREE & PCTUSED on DML
  • Table high-water mark
  • Table striping
  • Table partitioning
  • Row-re-sequencing and Oracle SQL I/O performance
  • Oracle9i Online table redefinition
  • Exercise – res-sequence table rows
  • 3 – Oracle SQL Tuning Techniques- Book Chapter 15, page 551-559

    • Overview of time-series SQL tuning
    • SQL execution metrics
    • Re-writing SQL syntax
    • Replace SQL with PL/SQL
    • Adding hints
    • 10g SQLAccess advisor - SQL profiles
    • 11g SQL Performance analyzer (SPA)

    4a - Time-series SQL Tuning

    • Predictive modeling for SQL performance
    • BSTAT-ESTAT, STATSPACK and AWR
    • Trending SQL operations (full scans hash joins, &c)
    • Holistic workload SQL tuning (11g SPA, Quest benchmark factory)

    5 – Finding Suboptimal SQL

    • Evaluating Large-table, full-table scans
    • Index Usage Analysis
    • Displaying reports on system-wide SQL execution
    • Exercise – run access.sql

    6 – Oracle SQL Tuning Silver Bullets

    • Materialized Views to pre-aggregate and pre-join
    • Adding indexes
    • Exercise – Create a materialized view
    • Course wrap-up

    Optional SQL tuning topics:

    1 – Using object-oriented SQL

  • Nested Tables
  • VARRAYS within tables
  • Using object ID’s in SQL
  • Using abstract data types (ADTs)
  • Exercise – Create and use an ADT
  • Oracle PLSQL Performance Tuning Training

    Oracle Performance Tuning training teaches you how to maximize productivity, reduce wait times and enhance the performance of your database. Oracle tuning training starts with an unknown database that requires tuning. You'll then learn the steps a DBA performs to identify problem areas, diagnose common problems and fix them.

    SQL Tuning Training

    Oracle 11g SQL Tuning training teaches database administrators and application developers how to build efficient SQL statements and tune database applications. Attendees learn the internals of SQL statement execution, how to monitor the performance of such execution, and how to influence the behavior of the database to achieve performance gains.

    Oracle PL/SQL Performance Tuning Training Course Training

    The PL/SQL tuning class topics start with the basic PL/SQL performance tuning and progress into increasingly complex tuning techniques, including bulking, optimizing calls to Oracle and using ref cursors. Perform code analysis to find program ambiguities, test, trace, and profile PL/SQL code. Create PL/SQL applications that use collections

    Parallel Processing in Oracle Database Training

    Parallel Processing in Database 11g training teaches you the benefits of parallelizing requests in an 11g database & use features of parallel processing.

    Oracle PLSQL Tuning Training Course Content

    Section 1 - Introducing PL/SQL

    • Introduction
    • What is PL/SQL and Why Should I use It?
    • PL/SQL Architecture
    • Overview of PL/SQL Elements
    • Blocks
    • Variables and Constants
    • Using SQL in PL/SQL
    • Branching and Conditional Control
    • Looping Statements
    • Goto
    • Procedures, Functions and Packages
    • Records
    • Object Types
    • Collections
    • Associative Arrays (Index-By Tables)
    • Nested Table Collections
    • Varrays
    • Collection Methods
    • Triggers
    • Error Handling
    • My Ideal Environment

    Section 2 - Writing Efficient PL/SQL

    • Introduction
    • Bind Variables
    • Using Bind Variables
    • The cursor_sharing Parameter
    • Dynamic Binds using Contexts
    • SQL Injection
    • Bulk Binds
    • Using Rowids when Updating
    • Short-circuit Evaluations and Ordering Logic
    • Implicit vs. Explicit Cursors
    • Declarations, Blocks, Functions and Procedures in Loops
    • Duplication of Built-in String Functions
    • Minimize Datatype Conversions
    • The Trigger Compilation Myth
    • Efficient Function Calls
    • Using the NOCOPY Hint
    • Using PLS_INTEGER and BINARY_INTEGER Types
    • Using BINARY_FLOAT and BINARY_DOUBLE Types
    • Native Compilation of PL/SQL
    • Decoupling (cheating) for Performance

    Section 3 - Arrays and Bulk Binds

    • Populating Collections Using Bulk Operations
    • Bulk collect
    • Bulk Collect from an Explicit Cursor
    • Chunking Bulk Collections Using the LIMIT Clause
    • Manually Limiting Bulk Collection Volumes
    • Bulk Collection of DML Results
    • FORALL
    • Bulk INSERT Operations
    • Bulk UPDATE Operations
    • Bulk DELETE Operations
    • Sparse Collections
    • Host Arrays in Bulk Operations
    • BULK_ROWCOUNT
    • Handling Exceptions in Bulk Operations
    • Unhandled Exceptions
    • Handled Exceptions
    • Bulk Operations that Complete
    • Dynamic SQL and Bulk Operations

    Section 4 - Caching Session Data

    • Using Arrays for Lookup Tables
    • Using Package Variables to Store Global Data
    • Using Contexts to Store Global Data
    • Introduction
    • Bind Variables and the Shared Pool
    • The NOCOPY Hint and Memory Usage
    • Bigger is Better for VARCHAR2 Variables
    • Using Packages Correctly
    • Pinning Packages in the Shared Pool
    • Conclusion

    Section 6 - Cursor Variables and REF CURSOR Types

    • Introduction
    • Defining Cursor Variables
    • Cursor Variables as Parameters
    • Cursor Attributes and Cursor Variable Usage
    • Host Variables as Cursor Variables
    • Dynamic SQL and Variant Resultsets
    • Restrictions When Using Cursor Variables
    • Cursor Expressions

    Section 7 - Table Functions and Pipelining

    • Introduction
    • Pipelining Table Functions
    • Parallel Enabled Table Functions
    • Creating Transformation Pipelines
    • Deterministic
    • Miscellaneous Information

    Section 8 - Monitoring and Profiling PL/SQL

    • Producing Performance Baselines
    • Monitoring Specific Code
    • Code Instrumentation (application tracing)
    • The DSP Package
    • dbms_application_info
    • dbms_session
    • dbms_system
    • dbms_profiler
    • dbms_trace
    • SQL trace, trcsess and tkprof
    • Generating SQL Trace Files
    • trcsess
    • tkprof
    • Trace Example
    • Execution Plans
    • plan_table
    • autotrace
    • Explain Plan
    • utlxpls.sql
    • dbms_xplan
    • Identifying the Impact of Code at the Database Level
    • Dynamic Performance Views (V$)
    • sessions.sql
    • top_sessions.sql
    • top_sql.sql
    • longops.sql
    • session_waits.sql
    • session_events_by_sid.sql and system_events.sql
    • session_stats.sql and system_stats.sql
    • session_io.sql
    • open_cursors_by_sid.sql
    • locked_objects.sql
    • STATSPACK
    • Automatic Workload Repository (AWR)
    • ADDM
    • Using Oracle Enterprise Manager

    Oracle Database 11g Advanced PL/SQL Training in Chennai

    In this Oracle Database 11G Advanced PL/SQL training, expert instructors will help you explore the advanced features of PL/SQL to design and tune PL/SQL. You'll learn how it interfaces with the database and other applications in the most efficient manner.

    Oracle Database 11g SQL Tuning Training in Chennai

    The SQL Tuning Training class covers investigative methods that reveal varying levels of detail about how the Oracle database executes a SQL statement. Students learn the different ways in which data can be accessed, which ones are most efficient under specific circumstances, and how to ensure that the best method is used. Partitioning topics are covered, in addition to taking advantage of hints, bind variables, and different types of indexes.

    Oracle Database Use XML for SQL and PL/SQL Developers

    Oracle provides PL/SQL developers with XML tools, which work inside the Oracle Database. In this course, students learn to use XML technology with PL/SQL for Application Development. This course covers the XML features and utilities available

    Introducing XML for Application Developers

    • Oracle XML DB basic concepts, features and terminology

    Oracle XML DB Repository

    • Overview of Oracle XML DB Repository
    • Creating and Accessing Resources
    • Managing Oracle XML DB Versioning
    • Accessing Oracle XML DB repository data using standard Internet protocols

    Managing XML Storage

    • Choosing Native XML Storage
    • Unstructured versus structured storage
    • Using XMLType view for relational data
    • Using hybrid storage mapping
    • Using out-of-line storage
    • Storing collections in out-of-line tables
    • Specifying SQL constraints
    • Adding constraints on repetitive elements

    Advanced XML Schema

    • Inheritance in the XML Schema
    • Managing cyclical definitions in XML Schema
    • XML Schema Evolution

    XQuery

    • XQuery: Introduction
    • XQuery Data Model
    • Using XMLQuery(), XMLTable(), and ora:view functions
    • Using XQuery Expressions
    • Using XQuery functions and operators

    Searching and Retrieving XML Data

    • Retrieve XML data
    • Querying Database: Relational Data
    • Querying Database: XMLType Data
    • Querying Database: CLOB Data
    • Searching an XML Document
    • Using XPath based Search
    • Using Full Text Search

    Advanced XQuery

    • Using XQuery to Generate XML
    • Using XQuery to Transform XML
    • Using XQuery For Querying XML
    • XQuery Support in SQL*Plus
    • Using XQuery with PL/SQL: Binding Dynamic Variables
    • Using Namespace with XQuery

    Manipulating and Generating XML Data

    • Using SQL functions to update XML data
    • Using DBMS_XMLSTORE
    • Generating XML using new SQL/XML functions
    • Generating XML using DBMS_XMLGEN package

    Understanding and Optimizing XPath and XQuery Rewrite

    • Overview of XPath Rewrite
    • XPath rewrite benefits
    • Instances where XPath rewrite occur and does not occur
    • XPath rewrite process
    • Detecting XPath Rewrite
    • XPath rewrite of SQL Functions
    • XQuery Rewrite
    • Tuning the performance of XQuery Expressions

    PL/SQL API for XML Type

    • Introducing PL/SQL APIs for XMLType
    • Document Object Model(DOM)
    • Using DBMS_XMLDOM to create a DOM document.
    • Using DBMS_XMLPARSER to access the contents and structure of XML documents
    • Using DBMS_XSLPROCESSOR for transforming an XML documents

    Importing/Exporting XML Data

    • Loading XMLType data using SQL*Loader
    • Importing and exporting XMLType data
    • Creating XMLType Queue
    • Enqueue and dequeue XML messages

    Oracle PLSQL and UNIX Shell scripting Training

    Learn to invoke a shell script from inside PL/SQL and use dbms_scheduler to invoke shell scripts, or execute shell scripts with a variety of other ways inside PL/SQL. There are many ways to execute a server-side script from inside Oracle

    SQL and PL/SQL Certification

    Earning your Oracle Certification gives you a well-respected IT credential that validates your expertise. Get Trained to pass Oracle certification exams. "If two people with roughly the same background and experience are vying for the same job, the certified person will usually get to the top of the HR pre-screening pile. Certification gives an employee the edge."

    Real time practical scenarios