Moving Performance Accountability to the Developer
Gudmundur Josepsson Index Software Solutions and Consulting
Topic • Application performance – whose responsibility? • Case study: The un-tunable application • The right way • Q&A
2
Application performance • Traditionally considered the DBA’s responsibility • “I don’t need to know this. This is DBA stuff.” • The code-tennis • The end-users and the business suffer
3
DBA vs developer • Bickering about “who killed who” • What it really comes down to is DBAs and developers versus the end-users • I’ve never seen a problem fixed by fingerpointing • The Metropolis analogy (free movie)
4
Metropolis / Oracle • • • •
City Good life Big machine Workers
• • • •
Application Good response Oracle IT pros
5
Case study: The un-tunable application • An interactive banking application • Displayed summary information from many other applications • Ran thousands of times during business hours • Slow: waiting end-users, waiting customers • “As fast as it’s going to be” • “Make it faster, anyway” 6
Case study: The un-tunable application • Previous attempts to optimize had failed • The usual stuff did not work – – – –
Statistics Re-org Session parameters Database parameters
• “Do you think it will run faster on SQL Server?” 7
The database is fine • Nothing irregular in system-wide database monitoring tools • OS performance stats were good – – – –
CPU load is fine Memory usage is fine Disk response times are fine Network is fine
8
“We can’t change the code” • Application developed in-house • All supporting applications developed in-house • A decision was made that the application had to use a layer of views to get data from the other applications • If more or different data was needed the views were made bigger
9
Architecture Un-tunable application
Sub-app 1
Sub-app 2
Sub-app 3
...
Oracle 10
What can we do? • The database seems to be fine, the DBAs swear it’s not their problem • We can’t touch the code • The manager is yelling • Help... • ...please
11
Tools and methods • Figure out which part of the application is the slowest, where is the time being spent • Instrument the code • Extended SQL trace • A profiler (well, THE profiler)
12
“The wrong castle” • Playing around with database parameters was not the solution • The right answer was to refuse to use the generic views and specify exactly what was needed • The result was more views but each view was smaller, faster and more manageable
13
Results • Lookup for biggest customers down from 30+ seconds to less than 8 seconds • Average customer from ~8 seconds to less than 1 second • Happy developers • Happy DBAs • HAPPY END-USERS!
14
The right way • • • •
Work together Know what your application is doing Get facts Don’t fight the symptoms – solve the problem!
15
Thank you!
16