20160 new features in oracle 11g for pl/sql code tuning - aioug

- 1 - Copyright © 2012 Blink Consulting Pvt. Ltd.

1

New features in Oracle 11g for PL/SQL code tuning.


Speakers Nikunj Gadoya Nikunj is working in Blink Consul4ng as Technical Consultant for more than 2 years now. He did his engineering in computer science from Hindustan college, Mumbai. With good communica4on skills & team player abili4es he is proficient in coding and implementa4on of Oracle applica4ons. He is a Oracle cer4fied professional (OCP) with rich experience in PL/SQL and SQL* Loader.

Pushpal Kumbhare Pushpal is working in Blink Consul4ng as Technical Consultant for more than three years now. He did his engineering in Informa4on Technology from SGGSIE&T college, Nanded. With good communica4on skills & team player abili4es he is proficient in coding, implementa4on and tuning of Oracle applica4ons. He posses rich experience in design and development of Interfaces and APIs in Oracle E-‐Business suite.


Agenda

Ø  Factors compelling code op4miza4on Ø  Best Prac4ces for code op4miza4on Ø  PLSQL coding challenges Ø  11g features for beXer code performance Ø  Scenarios for code tuning Ø  Few 4ps to consider Ø  Conclusion Ø  Q & A


Blink Consul4ng Blink Consul4ng is a global consul4ng and technology company, headquartered in Boston, MA. Its an Oracle Gold Partner assis4ng its customers in achieving business goals while maximizing value on their Investments. Since its incep4on in 2005 it has grown as a trusted partner in providing innova4ve, cost effec4ve and robust solu4ons across several industries. it is one of the fastest growing company in USA in the year 2010.

Our Services include: Ø Business Intelligence Ø Enterprise Performance Management (Hyperion) Ø Oracle EBS Upgrade and Implementa4on Ø Oracle Process Manufacturing Ø Enterprise Managed Services


Factors compelling code op4miza4on

Complex Business OperaFons

Increased demand for IT opFmizaFon

Risk associated with changing Business needs


Best prac4ces for code op4miza4on

InformaFon gathering and delivering

Knowledge of all new features for latest versions of Oracle database

Keeping track of Oracle frequent releases and latest version upgrades

Maintaining high quality of service


PLSQL coding challenges

TradiFonal PLSQL coding drawbacks

Slow execuFon of code

Excess Memory consumpFon

LocaFng the issues


Ø  BULK COLLECT

Ø  PL/SQL Func4on Result Cache

Ø  NOCOPY hint

Ø  SEQUENCE in PL/SQL Expressions

Ø  FOLLOWS and COMPOUND TRIGGERS keyword

Ø  Automa4c Subprogram Inlining

Ø  Advance collec4on features

11g features for beXer code performance


BULK COLLECT Ø  The most important performance op4miza4on feature of PLSQL Ø  BULK PROCESS: Speed up the repeated execu4on of the same

SQL statements by reducing number of context switches Ø  Uses PGA: Run faster but consume more memory Ø  Oracle provides various clauses to avoid excess use of PGA

•  LIMIT •  EXIT

Ø  Examples: •  Retrieve bulk data •  Explicit cursors vs. Bulk Collect •  Simple Bulk Collect •  Bulk Collect with LIMIT clause


PL/SQL Func4on Result Cache Ø  Improved and a high-‐impact feature. Ø Use RESULT_CACHE clause in func4on to get (cache) the result

of last invoked func4on(same parameters). Hence, skip the re-‐execu4on of func4on. Results in beXer performance.

Ø  RESULT_CACHE uses a global memory alloca4on (SGA)and the cache is available across the sessions. It flushes out memory when needed for system to store the new results.

Ø  Required Setup: •  Set RESULT_CACHE_MAX_SIZE as desired •  Set all the parameters to default

Ø  Examples: •  Func4on without RESULT_CACHE •  Func4on with RESULT_CACHE


NOCOPY Hint Ø  Copying of actual parameters to formal parameters and vise

versa, slows down the execu4on of code

Ø When parameters hold large data structures such as collec4ons, records etc, memory usage increases and slows down execu4on of code

Ø NOCOPY is a hint to compiler

Ø  Instruct compiler to pass OUT & IN OUT parameters by reference

Ø  Examples: §  without NOCOPY §  with NOCOPY


Dynamic SQL Features Ø Na4ve Dynamic SQL(NDS) does not support large SQL string

Ø NDS needs to know no. of bind inputs and outputs Ø  DBMS_SQL

Ø New func4ons in 11g: •  DBMS_SQL.TO_NUMBER_CURSOR

•  DBMS_SQL.TO_REFCURSOR


SEQUENCE in PL/SQL Expressions Ø Oracle has added sequence fetching to the list of PLSQL

expressions

Ø  Pseudo-‐columns can be assigned to a variable

Ø  Sequence expression can also be used to set default values Ø  Sequence pseudo-‐columns can be referenced directly. Hence,

variable assignment can be bypassed

Ø  Removed DUAL table, for SEQUENCE value retrieval


FOLLOWS and COMPOUND TRIGGERS COMPOUND TRIGGERS:

Ø  Perform mul4ple ac4ons at different 4ming points on a single

table

Ø  These 4ming points can share a single global declara4on sec4on

Ø Once the statement ends, trigger state is cleaned up

Ø  11g allows COMPOUND TRIGGER clause

Ø  Fires at each 4ming point associated with DML statements


FOLLOWS Keyword:

Ø  Specify the sequence of execu4on

Ø  FOLLOWS helps to design mul4ple triggers on same object at

same 4ming point

Ø  Helps in tracking the execu4on of specified ac4ons and results

Ø  Easy debugging

FOLLOWS and COMPOUND TRIGGERS (Contd…)


Sub-‐program Inlining Ø  Every call to func4on / procedure causes slight performance

overhead Ø  Subprogram Inlining reduces the overheads of calling func4ons /

procedures repeatedly

Ø  Replaces subrou4ne call during compila4on Ø  Developer can con4nue to write well constructed, modular code

without any performance issues

Ø  Greater op4miza4on benefits for procedural code Ø  Controlled by PLSQL_OPTIMIZE_LEVEL and INLINE pragma. Ø  Improved performance Ø  SETUPS:

§  PLSQL_OPTIMIZE_LEVEL = 2 (default). §  PLSQL_OPTIMIZE_LEVEL = 3 ; Inlines the code. §  PRAGMA INLINE (proc_name,'NO/YES');


Scenarios for code tuning Ø  Program that performs lot of calcula4ons

Ø  Func4ons that are called from PLSQL queries

Ø  Programs that performs lot of INSERT, UPDATE or DELETE or

looping through query results

Ø  Older code on newer version of database without u4lizing the database features


Few 4ps to consider Ø  Avoid CPU Overhead with:

§  More efficient SQL statements, func4on calls and loops §  Avoid DataType conversion

Ø  Avoid Memory Overhead with:

§  Declaring size for VARCHAR2 variables §  Grouping sub-‐programs into a package

Ø  Reducing Loop Overhead for DML & SQL Queries: §  FORALL §  BULK COLLECT

Ø  Tuning PL/SQL Procedure Calls:

§  NOCOPY compiler hint §  Compiling PLSQL code for Na4ve Execu4on


Conclusion Ø  Simple code 4ps like those highlighted

above can aid the developers in

wri4ng beXer and error-‐free programs

and get desired results

Ø  This will eventually result in an

op4mized system performance with

minimal shortcomings


Connect us @ Nikunj Gadoya Email:[email protected] Profile: hWp://www.linkedin.com/in/nikunjgadoya Pushpal Kumbhare Email:[email protected] Profile: hWp://www.linkedin.com/in/pushpalk

20160 new features in oracle 11g for pl/sql code tuning - aioug

Documents