Josepsson - Performance Accountability

Mo vin g P erf or mance Accounta bi li ty to the De vel oper

Gudmundur Josepsson Index Software Solutions and Consulting

Top ic • Application performance – whose responsibility? • Case study: The un-tunable application • The right way • Q&A

2

A ppl ica tion perf or mance • 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

DB A vs de vel oper • 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

Metr opol is / Or ac le • • • •

City Good life Big machine Workers

• • • •

Application Good response Oracle IT pros

5

Case st udy: T he un-tunable a pplic ation • 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 st udy: T he un-tunable a pplic ation • 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

T he da ta ba se is fin e • 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 chang e 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

Ar chitectur e Un-tunable application

Sub-app 1

Sub-app 2

Sub-app 3

...

Oracle 10

W ha t 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

Too ls 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

“T he wr ong castl e” • 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

Resul ts • 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

T he ri ght w ay • • • •

Work together Know what your application is doing Get facts Don’t fight the symptoms – solve the problem!

15

Thank you!

16