Transcript Efficient Streaming of Data from the Oracle database

HOT OR NOT?
HOW TO RELEASE CHANGES WITHOUT IMPACTING
CUSTOMERS
David Webster
April 2014
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
WHAT IS THIS SESSION NOT ABOUT?
2
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
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?
3
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
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
4
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
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
5
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
ABOUT BETFAIR
6
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
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.
7
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
ABOUT DAVID WEBSTER
15 YEARS WORKING WITH ORACLE
SITE RELIABILITY ENGINEER – ORACLE
PERFORMANCE
BLOG :
HTTP://ANOTHERDAVEWEBSTER.WORDPRESS.COM/
8
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
WHY PUT CODE IN THE DATABASE?
•
CENTRALISED LOGIC AND VERSIONING (I KNOW…)
•
PL/SQL APIS. ABSTRACTION AND ENCAPSULATION
•
PERFORMANCE
•
DEPENDENCY TRACKING
•
OTHERS……
9
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
WHY SHOULD I NOT PUT CODE IN THE
DATABASE?
•
PERFORMANCE…..
DIGRESSION ALERT!!!
PREFETCH & READ CONSISTENCY
•
MAKES RELEASING CODE MORE DIFFICULT!
10
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
WHAT MAKES OUR EXCHANGE A COMPLEX DB?
1. IT’S 4 DATABASES
2. OVER 100K EXECUTIONS A SECOND
3. 1000S OF SESSIONS, 100S OF CLIENTS
4. LOTS AND LOTS OF CODE
5. BACKGROUND JOBS
6. 24X7 OPERATION
11
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
(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
12
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
GETTING THE LOCK
• STOP BACKGROUND JOBS
• NEED A WINDOW
0
10
20
30
40
Minute
13
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
50
60
PACKAGE STATE
WHAT IS PACKAGE STATE?
14
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
PACKAGE STATE
CONSTANTS
CONSTANT WORKAROUND
15
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
UNNECESSARY INVALIDATIONS : FGDT
16
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
UNNECESSARY INVALIDATIONS : FGDT
FINE GRAINED DEPENDENCY TRACKING
17
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
UNNECESSARY INVALIDATIONS : FGDT
NEEDE
D
18
NOT NEEDED
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
CHANGING PACKAGE SPECIFICATIONS
CHANGING THE PACKAGE SPECIFICATION
INVALIDATES ALL CALLING PACKAGES
EXCEPT THAT’S NOT TRUE
19
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
CHANGING PACKAGE SPECIFICATIONS
END MEANS AFTER THE LAST REFERENCED
PROCEDURE!
TO BE SAFE, ADD TO THE VERY END
20
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
CHANGING PACKAGE SPECIFICATIONS
21
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
CHANGING PACKAGE SPECIFICATIONS
22
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
LEFT WITH A MESS?
1. WHEN OVERLOADING, REFACTOR
INTERNALLY
2. IF YOU CAN’T DELETE, THROW AN ERROR
3. DO A COLD RELEASE
23
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
DATABASE LINKS
1. DON’T USE THEM
2. SESSION-TASTIC
3. CONFUSE THE POOR CBO
4. DON’T USE THEM
5. EDGE CASES AND COMPLEXITY
24
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
DATABASE LINKS
FIXED USER DATABASE LINK
CONNECTED USER DATABASE LINK
25
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
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
26
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
SUMMARY
1. TO COMPILE (OR RECOMPILE) A PACKAGE,
YOU NEED AN EXCLUSIVE LOCK
2. PACKAGE STATE LIMITS OPTIONS
3. MINIMISE UNECESSARY COMPILATION
a. SCOPE PL/SQL VARIABLES
b. MODIFY SPECIFICATION CAREFULLY
4. BEWARE DATABASE LINKS
27
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
THANK YOU
Q&A
[email protected]
28
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.
29
CONFIDENTIAL and not for reproduction without prior written consent. © of The Sporting Exchange Limited.