oracle pl/sql best practices – part 2
TRANSCRIPT
![Page 1: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/1.jpg)
Oracle PL/SQL Best Practices – Part 2
John [email protected]
www.themisinc.comwww.themisinc.com/webinars
![Page 2: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/2.jpg)
Presenter
John Mullins Themis Inc. ([email protected]) 30+ years of Oracle experience Oracle Certified Professional DBA Certified Technical Trainer Over 300 classes taught
![Page 3: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/3.jpg)
Themis Inc.
More than 25 years in the industry More than 90,000 students have attended a
Themis training event Courses:
DB2, SQL Server, Oracle, Unix, Linux, Java, Web Development, .NET and many more
www.themisinc.com www.themisinc.com/webinars
![Page 4: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/4.jpg)
Related Themis Courses
Introduction to Oracle PL/SQL Programming June 26
Oracle Advanced PL/SQL Programming June 29
Troubleshooting, Debugging and Tuning Oracle PL/SQL Programs May 8, 2017
![Page 5: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/5.jpg)
Webinar Objectives
To become familiar with suggested best practices related to performance and troubleshooting when coding Oracle PL/SQL programs
![Page 6: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/6.jpg)
1 – Use the PL/SQL Profiler
Enables the collection of performance data Information Gathered:
The total number of times each line has been executed
The total amount of time that has been spent executing that line
The minimum and maximum times that have been spent on a particular execution of that line
![Page 7: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/7.jpg)
The PL/SQL Profiler
The dbms_profiler package START_PROFILER procedure STOP_PROFILER procedure
Identify “hot” spots in your PL/SQL code Identify starting points for further tuning efforts
![Page 8: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/8.jpg)
DBMS_PROFILER Usage
Easy to use Steps:
Start the profiler Execute application code Stop the profiler Examine the profiler data
![Page 9: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/9.jpg)
PLSQL_PROFILER_DATATEXT TOTAL_OCCUR TOTAL_TIME LINE
------------------------------------------------------- ----------- ------------- ----------
...
...v_calculated_value(read_data%ROWCOUNT) := ((12.34 50000 .045677 18
/2.91) / i.id) + (3.41 * i.id /1.179);
FOR i IN 1..50000 LOOP 50001 .004041 21
INSERT INTO forall_2 VALUES (v_id(i), v_calculate 50000 .726758 22
d_value(i));
FOR i IN 1..50000 LOOP 50000 .014334 25
UPDATE forall_2 50000 57.191248 26
FOR i IN 40001..50000 LOOP 10000 .002895 31
DELETE FROM forall_2 10000 11.872791 32
COMMIT; 1 .000136 35
END; 1 .000909 36
![Page 10: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/10.jpg)
2 – Be Aware of the PL/SQL Optimizer Settings
Enabled by default Can rearrange code PLSQL_OPTIMIZE_LEVEL parameter ALL_PLSQL_OBJECT_SETTINGS data
dictionary view
![Page 11: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/11.jpg)
PLSQL_OPTIMIZE_LEVEL
Parameter Controls automatic optimizations Valid values: (0-3) Default value is 2
![Page 12: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/12.jpg)
3 - Tune the SQL First
Use typical SQL tuning methods Explain Plan utility Good indexing Up to date object statistics SQL Tuning Advisor
![Page 13: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/13.jpg)
4 – Use the Result Cache Where Appropriate
Cache stores entire result sets in shared memory Reduces reads
![Page 14: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/14.jpg)
PL/SQL Function with Result Cache
CREATE OR REPLACE FUNCTION AVG_SAL_DEPT (v_deptno IN NUMBER)
RETURN NUMBER RESULT_CACHE ISv_avg NUMBER;
BEGINSELECT avg(sal) INTO v_avgFROM empWHERE deptno = v_deptno;
RETURN v_avg; END;/
![Page 15: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/15.jpg)
5 - Subprogram Inlining
Subprogram inlining replaces a subprogram invocation with a copy of the invoked subprogram If the invoked and invoking subprograms are in the
same program unit Be aware of the PLSQL_OPTIMIZE_LEVEL
setting
![Page 16: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/16.jpg)
6 – Give Data Types Some Thought
Use PLS_INTEGER or SIMPLE_INTEGER instead of NUMBER for computations
Be aware of PLSQL_CODE_TYPE setting INTERPRETED NATIVE
![Page 17: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/17.jpg)
7 – Passing Parameters and the NOCOPY Clause
Pass the corresponding actual parameter by reference instead of value
The NOCOPY clause requests that the compiler pass the corresponding actual parameter by reference instead of value
![Page 18: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/18.jpg)
8 – Reduce Context Switches
A context switch occurs every time the PL/SQL engine calls the SQL engine to parse, execute, or fetch from a cursor. This increases CPU time. This increases logical reads.
Goal: Reduce context switches.
![Page 19: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/19.jpg)
When to Consider Bulk Binds
A PL/SQL program that reads thousands of rows or more from a cursor or performs that many similar INSERT/UPDATE/DELETE statements will most likely benefit from bulk binds.
![Page 20: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/20.jpg)
Bulk Binding Benefits
Bulk Binds enable a PL/SQL program to fetch many rows from a cursor in one call instead of fetching one row at a time.
They also allow many similar DML statements to be executed with one call instead of requiring a separate call for each.
![Page 21: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/21.jpg)
The Need for Arrays
Bulk Binds use arrays to carry many rows of data between the PL/SQL program and the database.
Any array type is ok. Associative Arrays VARRAYS Nested Tables
![Page 22: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/22.jpg)
Reading with Bulk CollectBEGINOPEN read_orders;FETCH read_ordersBULK COLLECT INTO v_order_ids, v_order_totals;
SELECT order_id, order_totalBULK COLLECT INTO v_order_ids, v_order_totalsFROM product_order;
![Page 23: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/23.jpg)
DML with Bulk BindsFORALL i IN 1..v_order_ids.COUNT
UPDATE product_order SET order_total = v_order_totals(i)-(v_order_totals(i)*.10)WHERE order_id = v_order_ids(i);
![Page 24: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/24.jpg)
Summary
Following best practices helps promote consistent, reliable, readable, easier to maintain PL/SQL code along with possible performance gains
Questions? [email protected]
![Page 25: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/25.jpg)
Upcoming Webinars
Enhanced System-Period Temporal Tables on DB2 for z/OS Thursday April 27 11:30am ET www.themisinc.com/webinars
MQ Publish and Subscribe Thursday May 4 11:30am ET www.themisinc.com/webinars
![Page 26: Oracle PL/SQL Best Practices – Part 2](https://reader034.vdocuments.us/reader034/viewer/2022042922/626ae0e1367b3678674d5786/html5/thumbnails/26.jpg)
For More Information
Visit the Themis web site www.themisinc.com John Caccavale
To get a copy of the presentation: http://www.themisinc.com/webinars
Thank you for attending. Have a good day.