Implementing the Performance Improvements

1. Implementing the Performance Improvements

2. Objectives • After completing this lesson, you should be able to: • List the compiler changes and explain how the changes impact native compilation • Use the new SIMPLE_INTEGER data type • Describe the process of inlining • Use caching for optimization • Use flashback to store and track all transactional changes to a record

3. Lesson Agenda • Compiler changes and how the changes impact native compilation • The SIMPLE_INTEGER data type • Inlining • Caching • SQL result cache • PL/SQL function result cache • Flashback enhancements • To store and track all transactional changes to a record over its lifetime

4. Real Native Compilation • The compiler translates PL/SQL source directly to the dynamic-link library (DLL) for the current hardware. • The compiler does the linking and loading so that the file system directories are no longer needed. • The PL/SQL native compilation works out of the box, without requiring a C compiler on a production box. • The PLSQL_CODE_TYPE parameter is the on/off switch. • And, real native compilation is faster than the C native compilation.

5. Lesson Agenda • Compiler changes and how the changes impact native compilation • The SIMPLE_INTEGER data type • Inlining • Caching • SQL result cache • PL/SQL function result cache • Flashback enhancements • To store and track all transactional changes to a record over its lifetime

6. SIMPLE_INTEGER Data Type • Definition: • Is a predefined subtype • Has the range –2147483648 .. 2147483648 • Does not include a null value • Is allowed anywhere in PL/SQL where the PLS_INTEGER data type is allowed • Benefits: • Eliminates the overhead of overflow checking • Is estimated to be 2–10 times faster when compared with the PLS_INTEGERtype with native PL/SQL compilation

7. SIMPLE_INTEGER Data Type: Example CREATE OR REPLACE PROCEDURE p IS t0 NUMBER :=0; t1 NUMBER :=0; $IF $$Simple $THEN SUBTYPE My_Integer_t IS SIMPLE_INTEGER; My_Integer_t_Name CONSTANT VARCHAR2(30) := 'SIMPLE_INTEGER'; $ELSE SUBTYPE My_Integer_t IS PLS_INTEGER; My_Integer_t_Name CONSTANT VARCHAR2(30) := 'PLS_INTEGER'; $END v00 My_Integer_t := 0; v01 My_Integer_t := 0; v02 My_Integer_t := 0; v03 My_Integer_t := 0; v04 My_Integer_t := 0; v05 My_Integer_t := 0; two CONSTANT My_Integer_t := 2; lmt CONSTANT My_Integer_t := 100000000; -- continued on next page

8. SIMPLE_INTEGER Data Type: Example -- continued from previous page BEGIN t0 := DBMS_UTILITY.GET_CPU_TIME(); WHILE v01 < lmt LOOP v00 := v00 + Two; v01 := v01 + Two; v02 := v02 + Two; v03 := v03 + Two; v04 := v04 + Two; v05 := v05 + Two; END LOOP; IF v01 <> lmt OR v01 IS NULL THEN RAISE Program_Error; END IF; t1 := DBMS_UTILITY.GET_CPU_TIME(); DBMS_OUTPUT.PUT_LINE( RPAD(LOWER($$PLSQL_Code_Type), 15)|| RPAD(LOWER(My_Integer_t_Name), 15)|| TO_CHAR((t1-t0), '9999')||' centiseconds'); END p; /

9. SIMPLE_INTEGER Data Type: Example ALTER PROCEDURE p COMPILE PLSQL_Code_Type = NATIVE PLSQL_CCFlags = 'simple:true' REUSE SETTINGS; Procedure altered. EXECUTE p() native simple_integer 51 centiseconds PL/SQL procedure successfully completed. ALTER PROCEDURE p COMPILE PLSQL_Code_Type = native PLSQL_CCFlags = 'simple:false' REUSE SETTINGS; Procedure altered. EXECUTE p() native pls_integer 884 centiseconds PL/SQL procedure successfully completed. 1 2 3 4

10. Lesson Agenda • Compiler changes and how the changes impact native compilation • The SIMPLE_INTEGER data type • Inlining • Caching • SQL result cache • PL/SQL function result cache • Flashback enhancements • To store and track all transactional changes to a record over its lifetime

11. Intra Unit Inlining • Definition: • Inlining is defined as the replacement of a call to subroutine with a copy of the body of the subroutine that is called. • The copied procedure generally runs faster than the original. • The PL/SQL compiler can automatically find the calls that should be inlined. • Benefits: • Inlining can provide large performance gains when applied judiciously by a factor of 2–10 times.

12. Use of Inlining • Influence implementing inlining via two methods: • Oracle parameter PLSQL_OPTIMIZE_LEVEL • PRAGMA INLINE • Recommend that you: • Inline small programs • Inline programs that are frequently executed • Use performance tools to identify hot spots suitable for inline applications: • plstimer

13. Inlining Concepts • Noninlined program: CREATE OR REPLACE PROCEDURE small_pgm IS a NUMBER; b NUMBER; PROCEDURE touch(x IN OUT NUMBER, y NUMBER) IS BEGIN IF y > 0 THEN x := x*x; END IF; END; BEGIN a := b; FOR I IN 1..10 LOOP touch(a, -17); a := a*b; END LOOP; END small_pgm;

14. Inlining Concepts • Examine the loop after inlining: ... BEGIN a := b; FOR i IN 1..10 LOOP IF –17 > 0 THEN a := a*a; END IF; a := a*b; END LOOP; END small_pgm; ...

15. Inlining Concepts • The loop is transformed in several steps: a := b; FOR i IN 1..10 LOOP ... IF false THEN a := a*a; END IF; a := a*b; END LOOP; a := b; FOR i IN 1..10 LOOP ... a := a*b; END LOOP; a := b; a := a*b; FOR i IN 1..10 LOOP ... END LOOP; a := b*b; FOR i IN 1..10 LOOP ... END LOOP;

16. Inlining: Example • Set the PLSQL_OPTIMIZE_LEVEL session-level parameter to a value of 2 or 3: • Setting it to 2 means no automatic inlining is attempted. • Setting it to 3 means automatic inlining is attempted and no pragmas are necessary. • Within a PL/SQL subroutine, use PRAGMAINLINE • NO means no inlining occurs regardless of the level and regardless of the YES pragmas. • YES means inline at level 2 of a particular call and increase the priority of inlining at level 3 for the call. ALTER PROCEDURE small_pgm COMPILE PLSQL_OPTIMIZE_LEVEL = 3 REUSE SETTINGS;

17. Inlining: Example • After setting the PLSQL_OPTIMIZE_LEVEL parameter, use a pragma: CREATE OR REPLACE PROCEDURE small_pgm IS a PLS_INTEGER; FUNCTION add_it(a PLS_INTEGER, b PLS_INTEGER) RETURN PLS_INTEGER IS BEGIN RETURN a + b; END; BEGIN pragma INLINE (small_pgm, 'YES'); a := add_it(3, 4) + 6; END small_pgm;

18. Inlining: Guidelines • Pragmas apply only to calls in the next statement following the pragma. • Programs that make use of smaller helper subroutines are good candidates for inlining. • Only local subroutines can be inlined. • You cannot inline an external subroutine. • Cursor functions should not be inlined. • Inlining can increase the size of a unit. • Be careful about suggesting to inline functions that are deterministic.

19. Lesson Agenda • Compiler changes and how the changes impact native compilation • The SIMPLE_INTEGER data type • Inlining • Caching • SQL result cache • PL/SQL function result cache • Flashback enhancements • To store and track all transactional changes to a record over its lifetime

20. SQL Query Result Cache • Definition: • Cache the results of the current query or query fragment in memory and then use the cached results in future executions of the query or query fragments. • Cached results reside in the result cache memory portion of the SGA. • Benefits: • Improved performance

21. SQL Query Result Cache • Scenario: • You need to find the greatest average value of credit limit grouped by state over the whole population. • The query results in a huge number of rows analyzed to yield a few or one row. • In your query, the data changes fairly slowly (say every hour) but the query is repeated fairly often (say every second). • Solution: • Use the new optimizer hint /*+ result_cache */ in your query: SELECT /*+ result_cache */ AVG(cust_credit_limit), cust_state_province FROM sh.customers GROUP BY cust_state_province;

22. PL/SQL Function Result Cache • Definition: • Enables data that is stored in cache to be shared across sessions • Stores the function result cache in a shared global area (SGA), making it available to any session that runs your application • Benefits: • Improved performance • Improved scalability

23. PL/SQL Function Result Cache • Scenario: • You need a PL/SQL function that derives a complex metric. • The data that your function calculates changes slowly, but the function is frequently called. • Solution: • Use the new result_cache clause in your function definition.

24. Enabling Result Caching • Include the RESULT_CACHE option in the function definition. • Optionally, include the RELIES_ON clause. CREATE OR REPLACE FUNCTION productName (prod_id NUMBER, lang_id VARCHAR2) RETURN NVARCHAR2 RESULT_CACHE RELIES_ON (product_descriptions) IS result VARCHAR2(50); BEGIN SELECT translated_name INTO result FROM product_descriptions WHERE product_id = prod_id AND language_id = lang_id; RETURN result; END;

25. Lesson Agenda • Compiler changes and how the changes impact native compilation • The SIMPLE_INTEGER data type • Inlining • Caching • SQL result cache • PL/SQL function result cache • Flashback enhancements • To store and track all transactional changes to a record over its lifetime

26. Flashback Data Archives • Provide the ability to store and track all transactional changes to a record over its lifetime • Save development resources because you no longer need to build this intelligence into your application • Are useful for compliance with record stage policies and audit reports

27. Flashback Data Archive Process 1. Create the Flashback Data Archive. 2. Specify the default Flashback Data Archive. 3. Enable the Flashback Data Archive. 4. View Flashback Data Archive data.

28. Flashback Data Archive Scenario • Using Flashback Data Archive to access historical data: CONNECT sys/oracle@orcl AS sysdba -- create the Flashback Data Archive CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE example QUOTA 10G RETENTION 5 YEAR; 1 -- Specify the default Flashback Data Archive ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT; 2 -- Enable Flashback Data Archive ALTER TABLE oe1.inventories FLASHBACK ARCHIVE; ALTER TABLE oe1.warehouses FLASHBACK ARCHIVE; 3

29. Flashback Data Archive Scenario • Using Flashback Data Archive to access historical data: • Examine the data: • Change the data: • Examine the flashback data: SELECT product_id, warehouse_id, quantity_on_hand FROM oe1.inventories WHERE product_id = 3108; 1 UPDATE oe1.inventories SET quantity_on_hand = 300 WHERE product_id = 3108; 2 SELECT product_id, warehouse_id, quantity_on_hand FROM oe1.inventories AS OF TIMESTAMP TO_TIMESTAMP ('2007-06-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS') WHERE product_id = 3108; 3

30. Flashback Data Archive Dictionary Views • Viewing the results:

31. Flashback Data Archive Dictionary Views • Viewing information about tables that are enabled for flashback archiving: DESCRIBE dba_flashback_archive_tables Name Null? Type ----------------------------------- -------- --------------- TABLE_NAME NOT NULL VARCHAR2(30) OWNER_NAME NOT NULL VARCHAR2(30) FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255) ARCHIVE_TABLE_NAME VARCHAR2(53) SELECT * FROM dba_flashback_archive_tables; TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME ------------- ---------- ---------------------- ------------------- INVENTORIES OE FLA1 SYS_FBA_HIST_70355 WAREHOUSES OE FLA1 SYS_FBA_HIST_70336

32. Flashback Data Archive DDL Restrictions • Using any of the following DDL statements on a table enabled for Flashback Data Archive causes the error ORA-55610: Invalid DDL statement on history-tracked table • ALTERTABLE statement that does any of the following: • Drops, renames, or modifies a column • Performs partition or subpartition operations • Converts a LONG column to a LOB column • Includes an UPGRADETABLE clause, with or without an INCLUDINGDATA clause • DROP TABLE statement • RENAME TABLE statement • TRUNCATE TABLE statement

33. Summary • In this lesson, you should have learned how to: • List the compiler changes and explain how the changes impact native compilation • Use the new SIMPLE_INTEGER data type • Describe the process of inlining • Use caching for optimization • Use flashback to store and track all transactional changes to a record

34. Practice 5 Overview: Implementing Performance Improvements • This practice covers the following topics: • Testing the performance of the SIMPLE_INTEGER data type • Writing code to use SQL caching • Writing code to use PL/SQL caching • Examining inlined code and practicing how to influence inlining