What is Pragma Inline in Oracle

PL / SQL performance tuning in 11g

PL / SQL performance tuning in 11g DOAG conference Nuremberg 2009 Dr. Hildegard Asenbauer MuniQSoft GmbH Founded in 1998 Areas of activity: Oracle training courses Oracle IT Consulting & amp; Services Software Solutions Oracle Licenses MuniQSoft GmbH Gr & uuml; nwalder Weg 13 a D-82008 Unterhaching www.muniqsoft.de +49 89 6628 6789-0 MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 2 Tuning Database Tuning DBA SQL- Tuning developer PL / SQL tuning developer MuniQSoft GmbH DOAG 2009 PL / SQL performance tuning Page 3 Avoiding repeated executions Before 11g: Keeping static tables in PL / SQL arrays Saving the return value of functions in variables Advantage: faster access Disadvantages: only within the same session No invalidation in the event of changes Memory consumption MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 4 11g: RESULT CACHE Result Cache in SGA  session-wide New parameter result_cache_max_size Must & gt; 0 its default depending on other init.ora parameters (memory_target / sga_target / shared_pool_size) Can be used in SQL and PL / SQL SQL: result_cache_mode or Hint result_cache MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 5 RESULT CACHE key; Keyword RESULT_CACHE with function declaration Package functions: Header AND Body When accessing tables: RELIES_ON Package functions: Body cache is automatically invalidated when tables are changed. ATTENTION: NO error if clause is missing! 2 records are allowed as data type. With cursor FOR loops, implicit bulk DML by parameter plsql_optimize_level possible MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 10 Bulk Binds Bulk Select SELECT .. BULK COLLECT INTO .. ​​FETCH .. BULK COLLECT INTO .. [LIMIT X] Storage space is not automatically released again Bulk DML FORALL i IN x..y [SAVE EXCEPTIONS] FORALL i IN INDICES OF v_arr [BETWEEN x AND y] [SAVE EXCEPTIONS] FORALL i IN VALUES OF v_arr [SAVE EXCEPTIONS ] MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 11 Bulk Binds in 11g Finally, individual fields of the record can be accessed in the DML command! This means that it can be used without restriction for all DML commands: FORALL i IN UPDATE SET WHERE v_rec.FIRST..v_rec.LAST tab col1 = v_rec (i) .field1 col2 = v_rec (i) .field2; FORALL i IN v_rec.FIRST..v_rec.LAST DELETE FROM tab WHERE col1 = v_rec (i) .field1; MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 12 PLSQL_OPTIMIZE_LEVEL parameters introduced in 10g, possible settings: 0: no additional optimizations, behavior like 9i 1: optimizations like removing unnecessary calculations 2 (default): further optimizations eg implicit conversion of CURSOR FOR loops in BULK SELECT extension in 11g: 3: further than 2 including automatic inlining MuniQSoft GmbH DOAG 2009 PL / SQL performance tuning page 13 PLSQL_OPTIMIZE_LEVEL adjustable at system / session level ALTER SYSTEM SET PLSQL_OPTIMIZE_LEVEL = 1; ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3; Can be set for individual program units ALTER PROCEDURE & lt; my_proc & gt; COMPILE plsql_optimize_level = 3; DD-View: DBA_PLSQL_OBJECT_SETTINGS MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 14 Native compilation Creation of machine code, interpreter step omitted Introduced in 9i, but complicated simplification in 10g: automatic search for OS-specific C -Compiler (GCC, VC ++, .Net) file $ ORACLE_HOME / plsql / spnc_commands Next C compiler and external directory required Parameter PLSQL_OPTIMIZE_LEVEL must be at least 2 MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 15 Native Compilation 11g None C compiler is no longer required No external directory required Storage in the SYSTEM tablespace Only required parameter: PLSQL_CODE_TYPE New data type SIMPLE_INTEGER MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 16 Native compilation Valid PLSQL_CODE_TYPE: NATIVE INTERPRETED Native compilation adjustable: At system and session level ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE'; At procedure level ALTER PROCEDURE & lt; my_proc & gt; COMPILE PLSQL_CODE_TYPE = NATIVE; MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 17 SIMPLE_INTEGER Subtype of PLS_INTEGER Same value range (-2.147.483.648 to 2.147.483.647) NOT NULL Constraint No overflow, instead wrapping Only with native compilation with advantages MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 18 Inlining Inlining prevents overhead when calling subroutines Pragma: INLINE PRAGMA INLINE (procedure, 'YES' | 'NO'); PLSQL_OPTIMIZE_LEVEL & gt; = 2 Position: immediately before the call Automatic inlining: PLSQL_OPTIMIZE_LEVEL = 3 Source code remains unchanged MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 19 NOCOPY Compiler Hint, can be specified for OUT and IN OUT parameters: PROCEDURE (p_nocopy p_array IN OUT NOCOPY MY_PACK.t_array) causes transfer of BY REFERENCE instead of BY VALUE No copying, but transfer of the memory address Can with PLSQL_OPTIMIZE_LEVEL & gt; 1 also take place automatically MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 20 SQL in PL / SQL Main problem with real performance bottlenecks!  SQL tuning required Use bind variables! Outsource SQL commands in your own modules and avoid duplications! MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 21 Miscellaneous Test under realistic conditions Pay attention to boundary conditions (available resources, parallel sessions vs. single session, ...) Keep locks as short as possible Modularize and pay attention for clean program runs Make extensive checks at the end of conditional statements Pipelined functions can accelerate the response time MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 22 Performance measurement Easiest possibility (in test phase): Time measurement DBMS_UTILITY.get_time DBMS_UTILITY.get_cpu_time v_begin: = DBMS_UTILITY.get_cpu_time; p_do_proc1; v_end: = DBMS_UTILITY.get_cpu_time; DBMS_OUTPUT.put_line ('Time method 1:' || (v_end - v_begin)); MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 23 Performance Measurement DBMS_PROFILER: Which programs were called? Which lines were processed how often? How long did it take to process a line in total, minimally, maximally? How long was the entire run? Output ends up in tables (to be created beforehand) MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 24 Performance measurement 3 functions: v_start: = DBMS_PROFILER.START_PROFILER; v_flush: = DBMS_PROFILER.FLUSH_DATA; v_stop MuniQSoft GmbH: = DBMS_PROFILER.STOP_PROFILER; DOAG 2009 PL / SQL Performance Tuning Page 25 Performance Measurement 11g DBMS_HPROF: Hierarchical Relationships (Parents, Children) Number of Calls Duration (Function or Subtree) Separate Namespaces for SQL and PL / SQL Directory Object Necessary, Output ends up in file: DBMS_HPROF.START_PROFILING ('MYDIR', 'test.trc'); MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 26 Performance Measurement 11g From this tables (which have to be created beforehand) can be filled: v_id: = DBMS_HPROF.ANALYZE (LOCATION = & gt; 'MYDIR', FILENAME = & gt; ' test.trc ', RUN_COMMENT = & gt;' TEST '); Generation of HTML reports by plshprof (11.1.0.7): plshprof -output… \ tracetest… \ test.trc MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 27 Thank you for your attention ...? MuniQSoft GmbH DOAG 2009 PL / SQL Performance Tuning Page 28