Hot or Not? How to Release Changes Without Impacting Customers

1. Hot or Not?How to Release Changes Without Impacting Customers David Webster April 2014

2. What is this session NOT about?

3. What this session is about? • How do we make code and table changes to an Oracle database without impacting any of the users? • How do we do that even on a really complex database with 1000s of users/sessions?

4. What is this session about, specifically? • Why put code in the DB? • How to change that code while the DB is active. • Under what circumstances is that challenging? (24x7, high executions, daemons, DB Links, dependencies) • What can we do about it? • ELEPHANT IN THE ROOM : EDITIONS

5. Just before I start…. • All tests are on 11.2.0.3 • HOT or NOT…. • Hot doesn’t mean hot, cold doesn’t mean cold

6. About Betfair

7. ABOUT BETFAIR • Pioneered online person-to-person betting. Betfair is the broker, the eBay of betting, not the “house” • Company started in 1999 with 2 founders • over 1500 employees across the world • Data centres across Europe, in Australia and US • full offering of betting products, including Sportsbook, Poker, Casino and Arcade.

8. About David Webster • 15 years working with Oracle • Site Reliability Engineer – Oracle performance • Blog : http://anotherdavewebster.wordpress.com/

9. Why put code in the database? • Centralised logic and versioning (I know…) • PL/SQL APIs. Abstraction and encapsulation • Performance • Dependency tracking • Others……

10. Why should I not put code in the database? • Performance….. • DIGRESSION ALERT!!! • PREFETCH & Read Consistency • Makes releasing code more difficult!

11. What makes OUR exchange a complex db? It’s 4 databases Over 100k executions a second 1000s of sessions, 100s of clients Lots and lots of code Background jobs 24x7 operation

12. (RE)Compiling packages Q : what does oracle need to compile a package body? A : Oracle requires an exclusive lock on the object Q : DOES ANYTHING GET invalidated WHEN I COMPILE A BODY? A : NO Q : What about a package spec? A : Compiling a package spec invalidates calling bodies* Q : Is my (re)compilation limited to the code that I change? A : NO….. Compile only what you need AVOID INVALIDATING PACKAGES

13. Getting the lock • Stop background jobs • Need a window 60 50 10 40 0 20 30 Minute

14. Package state • What is package state? ORA-04068: existing state of packages has been discarded

15. Package state • ConsTants • CONSTANT workaround ORA-04068: existing state of packages has been discarded

16. Unnecessary invalidations : FGDT

17. Unnecessary invalidations : FGDT • Fine Grained dependency tracking

18. Unnecessary invalidations : FGDT • NOT Needed • Needed

19. Changing Package Specifications • Changing the package specification invalidates all calling packages • Except that’s not true

20. Changing Package Specifications • END means after the last referenced procedure! To be safe, add to the very end

21. Changing Package Specifications

22. Changing Package Specifications

23. Left with a mess? When overloading, refactor internally If you can’t delete, throw an error Do a cold release

24. DATABASE Links Don’t use them Session-tastic Confuse the poor CBO Don’t use them Edge cases and complexity

25. DATABASE Links fixed user database link CONNECTED USER Database LINK

26. Database links RED_TIGER RED_TIGER RED_SCOTT BLUE_SCOTT Package PACKAGE reads BLUE_SCOTT.TABLE@BLUE TABLE GRANT EXECUTE ON PACKAGE TO RED_TIGER GRANT SELECT ON TABLE TO RED_SCOTT GRANT SELECT ON TABLE TO RED_TIGER RED_SCOTT Bug 13415892 - UNNECESSARY INVALIDATION OF REMOTE STUBS

27. SUMMARY • To compile (or recompile) a package, you need an exclusive lock • Package state limits options • MINIMISE UNECESSARY COMPILATION • SCOPE PL/SQL VARIABLES • MODIFY SPECIFICATION CAREFULLY • Beware database links

28. Thank you Q&A David.Webster@betfair.com