copyright © 200\8 quest software high performance pl/sql guy harrison chief architect, database...
TRANSCRIPT
![Page 1: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/1.jpg)
Copyright © 200\8 Quest Software
High Performance PL/SQL
Guy Harrison
Chief Architect, Database Solutions
![Page 2: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/2.jpg)
PL/SQL top tips
1. Optimize network traffic
2. Array processing
3. Set PLSQL_OPTIMIZE_LEVEL
4. Loop processing
5. Recursion
6. NoCopy
7. Associative arrays
8. Bind variables in NDS
9. Number crunching
10.Using the profiler
11. G
![Page 3: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/3.jpg)
Tip 0: It’s usually the SQL
• Most PL/SQL routines spend most of their time executing SELECT statements and DML
• Tune these first:– Identify proportion of time spent in SQL (profiler, V$SQL)– Use SQL Trace+ tkprof or the profiler to identify top SQL
• SQL tuning is a big topic but:– Look at statistics collection policies
• In development AND in production– Consider adequacy of indexing– Learn hints– Exploit 10g/11g tuning facilities (if licensed)– Don’t issue SQL when you don’t need to
![Page 4: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/4.jpg)
PLSQL_OPTIMIZE_LEVEL
• Introduced in 10g• Controls transparent optimization of PL/SQL code
similar to reorganizing code
– Level 0: No optimization– Level 1: Minor optimizations, not much reorganization– Level 2: (the default) Significant reorganization including loop
optimizations and automatic bulk collect– Level 3: (11g only) Further optimizations, notably automatic in-lining
of subroutines
![Page 5: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/5.jpg)
Motivations for stored procedures
• Historically:– Security– Client-Server division of labour– Separation of business logic– Manageability– Portability ?– Network overhead – Divide and conquer complex SQL
• Today– Middle tier provides most of these– Network traffic is perhaps the strongest remaining motivation
![Page 6: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/6.jpg)
Optimizing network traffic
• PL/SQL routines most massively outperform other languages when network round trips are significant.
![Page 7: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/7.jpg)
Network traffic
• Routines that process large numbers of rows and return simple aggregates are also candidates for a stored procedure approach
![Page 8: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/8.jpg)
Stored procedure alternative
![Page 9: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/9.jpg)
Network traffic example
![Page 10: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/10.jpg)
Array processing
• Considered bad:
• Excessive loop iterations• Increases logical reads (rows in the same block
fetched separately)
![Page 11: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/11.jpg)
Array processing
• Considered better:
• Selects all data in a single operation• Large result sets might take longer as memory grows• Other concurrent sessions may have limited memory
for sorts, etc. • Out of memory errors are possible
![Page 12: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/12.jpg)
Array processing
• Considered best:
• Never more that p_array_size elements in collection• Best throughput, acceptable memory utilization
![Page 13: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/13.jpg)
Array processing (plsql_optimize_level=1)
1 10 100 1000 10000 100000 10000000
20
40
60
80
100
120
140
160
180
200
Bulk Collect Size
Ela
pse
d T
ime
No bulk collect
Bulk collect without LIMIT
![Page 14: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/14.jpg)
Bulk Collect and PLSQL_OPTIMIZE_LEVEL
• PLSQL_OPTIMIZE_LEVEL>1 causes transparent BULK COLLECT LIMIT 100
• This means that FOR loops can actually be more efficient that unlimited BULK COLLECT!
1 10 100 1000 10000 100000 1000000 100000000
50
100
150
200
250
300
Array Size
Ela
sped
tim
e (s
)
No Bulk CollectBulk Collect no limit
********************************************************************************
SQL ID : 6z2hybgm1ahkhSELECT /*+ cache(t) */ PK, DATA FROM BULK_COLLECT_TAB
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 25000 3.26 12.49 73530 98241 0 2499998------- ------ -------- ---------- ---------- ---------- ---------- ----------total 25002 3.26 12.49 73530 98241 0 2499998
Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 88 (recursive depth: 1)
![Page 15: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/15.jpg)
Reduce unnecessary Looping
• Unnecessary loop iterations burn CPU
Well formed loop
Poorly formed loop
0 5 10 15 20 25 30 35
3.96
34.31
Elapsed time (s)
![Page 16: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/16.jpg)
Remove loop Invariant terms
• Any term in a loop that does not vary should be extracted from the loop
• PLSQL_OPTIMIZE_LEVEL>1 does this automatically
![Page 17: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/17.jpg)
Loop invariant terms relocated
![Page 18: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/18.jpg)
Loop invariant performance improvements
plsql_optimize_level=2
Optimized loop
Original loop
0 2 4 6 8 10 12
5.28
5.87
11.09
Elapsed time (s)
![Page 19: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/19.jpg)
Recursive routines
• Recursive routines often offer elegant solutions.
• However, deep recursion is memory-intensive and usually not scalable
![Page 20: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/20.jpg)
Recursion memory overhead
0 1000000 2000000 3000000 4000000 5000000 6000000 7000000 8000000 9000000100000000
200
400
600
800
1000
1200
1400
Recursive
Non-recursive
Recursive Depth
PG
A m
emo
ry (
MB
)
![Page 21: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/21.jpg)
NOCOPY
• The NOCOPY clause causes a parameter to be passed “by reference” rather than “by value”
• Without NOCOPY, a copy of each parameter variable is created within the subroutine
• This is particularly expensive when collections are passed as parameters
![Page 22: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/22.jpg)
NoCopy performance gains
• 4,000 row, 10 column “table”; 4000 lookups:
NOCOPY
NO NOCOPY
0 100 200 300 400 500 600 700 800 900
0.28
864.96
Elapsed time (s)
![Page 23: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/23.jpg)
Associative arrays
• Traditionally, sequential scans of PLSQL tables are used for caching database table data:
![Page 24: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/24.jpg)
Associative arrays
• Associative arrays allow for faster and simpler lookups:
![Page 25: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/25.jpg)
Associative array performance
• 10,000 random customer lookups with 55,000 customers
Associative lookups
Sequential scan
0 5 10 15 20 25 30
0.04
29.79
Elapsed time (s)
![Page 26: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/26.jpg)
Bind variables in Dynamic SQL
• Using bind variables allows sharable SQL, reduces parse overhead and minimizes latch contention
• Unlike other languages, PL/SQL uses bind variables transparently
• However, dynamic SQL makes it easy to “forget”
![Page 27: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/27.jpg)
Using bind variables
![Page 28: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/28.jpg)
Bind variable performance
• 10,000 calls like this:
Bind variables
No Binds
0 1 2 3 4 5 6 7 8
3.42
7.84
Elasped Time (s)
![Page 29: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/29.jpg)
Number crunching
• Until recently, it’s been hard to determine how much time is spent in PLSQL code, vs time in SQL inside PLSQL:
![Page 30: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/30.jpg)
Java for computation?
Your results will vary
8i
9i
10g
11g
0 5 10 15 20 25 30 35
Java
PLSQL Native
PLSQL
Elapsed Time (s)
![Page 31: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/31.jpg)
Why Native didn’t work well for me…
• I need a routine with no SQL and no built in functions!
![Page 32: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/32.jpg)
The profiler
• DBMS_PROFILER is the best way to find PL/SQL “hot spots”:
![Page 33: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/33.jpg)
![Page 34: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/34.jpg)
Toad profiler support
![Page 35: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/35.jpg)
Hierarchical profiler
$ plshprof -output hprof demo1.trc
![Page 36: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/36.jpg)
Plshprof output
![Page 37: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/37.jpg)
DBMS_HPROF tables
![Page 38: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/38.jpg)
Toad Hierarchical profiler
![Page 39: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/39.jpg)
11g and other stuff
• 11g Native compilation • 11g In-lining• Data types (SIMPLE_INTEGER)• IF and CASE ordering• SQL tuning (duh!)• PLSQL Function cache
![Page 40: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/40.jpg)
Function cache example
• Suits deterministic but expensive functions• Expensive table lookups on non-volatile tables
![Page 41: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/41.jpg)
• 100 executions, random date ranges 1-30 days:
Function cache
No function cache
0 1 2 3 4 5 6
1.51
5.21
Elapsed time (s)
![Page 42: Copyright © 200\8 Quest Software High Performance PL/SQL Guy Harrison Chief Architect, Database Solutions](https://reader030.vdocuments.us/reader030/viewer/2022032702/56649ca65503460f94968c39/html5/thumbnails/42.jpg)
Thank You – Q&A