1 / 29

Hot or Not? How to Release Changes Without Impacting Customers

Hot or Not? How to Release Changes Without Impacting Customers. David Webster. April 2014. What is this session NOT about?. What this session is about?. How do we make code and table changes to an Oracle database without impacting any of the users?

polly
Download Presentation

Hot or Not? How to Release Changes Without Impacting Customers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related