Oracle Database Performance: Are Database Users Telling Me The Truth?

Alfredo Krieg, DBA from Sherwin Williams
http://bitkode.blogspot.com


Performance Challenges

• difficult to tune for performance without a performance methodology
• what metrics do you use?
• is user report enough? no!
• ask questions, get more information

THe Method (or A method)

• set Goals
• establish tuning criteria
• define scope of tuning
• Measure
• Oracles Kernal instrumentation
• wait interface
• system views
• V$sysstat
• v$system_event
• v$sys_time_model
• Calculate DB response time
• service time 
• queue time
• user cals
• snapshot's time frame
• ResponseTime = (service time + queue time) / user calls
• formula from queue theory
• Choose a Unit of Work
• User calls (overall activity)
• number of logins, parses, execute calls during sampel period
• overall metric for activity
• Physical reads/write (i/o bound)
• logical read/writes (CPU bound)
• Graph this information
• make it easily parsable by humans!

DBA != Default Blame Acceptor

• Anticipate
• AWR is not real-time
• system views are almost realtime
• reconcile the two to identify potential issues
• Tools
• Oracle Enterprise Manager
• Response Time / Transaction
• Adaptive Thresholds
• Metric Extensions