Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


Performance Tuning

No description

bruce mccartney

on 30 January 2014

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Performance Tuning

Performance Tuning
Methodology of Tuning
Method*R Cary Millsap
Process (Tuning Intitative Docs)
can you reproduce? can i trace?
is it happening Now?
did it use to work?
does it perform better elsewhere?
Performance Repositories
* Automated Workload Reportitory (AWR - need Diag Pack)
* ash analytics (tuning pack)
* Active Session History (ASH - need Diag Pack)
* Statspack (no $pack)
* 3rd Party (Confio, Precise)
Infrastructure Tracing
Usually rquires elevated priviledges (root)
vendor tools (EMC, NETAPP)
Presentation Tips
Helpful don'ts from Don McMillan's
Death by Powerpoint
Tuning Infrastructure
* Rule out the database?
tkprof summary of time spent in database vs elapsed time
* Slow Storage?
Events related to storage (db file%, log file% are main ones)
Industry averages ( <10ms ok, <5ms good, >10ms unacceptable)
if average ok, then address the number of IOs required (cache, sql, indexing etc)
Redo - lgwr trc file, Sorage latency != Oracle 'log file sync' (coherency or queuing)
* CPU (waiting or over-consuming (i.e parsing) -> bind variables)
* Slow Network?
look for row-at-a-time operations

Tuning Database
Real Time Diagnostics
After the fact repositories
Database Issues
Application(SQL) Issues
Diagnostics for Infrastructure
Platform dependent (vmstat, iostat); may need SYSADM "assistance"
Oracle v$OSSTAT - very high level

Real-Time Performance
* oratop (no packs required)
* snapper (no packs required)
* sql monitor (tuning packs)
Objective of this presentation
Presentation tips
Chapter 1 - please read!
Method R®: A Response Time–Based Performance Improvement

Method That Yields Maximum Economic Value to Your Business
1. Select the user actions for which the business needs improved performance.
2. Collect properly scoped diagnostic data that will allow you to identify the causes
of response time consumption for each selected user action while it is performing
3. Execute the candidate optimization activity that will have the greatest net payoff
to the business. If even the best net-payoff activity produces insufficient net payoff,
then suspend your performance improvement activities until something
4. Go to step 1.

Tuning the Database
Oracle Configuration (DBA Control -easy)
* wait events due to RDBMS (gc% px%, latch%, buffer busy, etc) ADDM
* I/O bottleneck due to mis-sized SGA/REDO LOG/PGA etc (memory adviser)
* Indexing opportunities (segment adviser)

Application (SQL/Model - Most difficult to isolate/fix)
* SQL Plans - the optimizer; Automatic SQL Tuning Advisor
* enq waits - locking
* Code structure (Binds, Row-at-a-time processing)

Sequence Diagram
* Examine response time of all tiers
* Examine queuing and service times
SQL Tuning by Cardinality Feedback
dbms_xplan (ALLSTATS Option)
Make best statistics available
Event 10053 trace - last resort as to why optimizer chooses one path over another
Oracle Optimizer Blog https://blogs.oracle.com/optimizer/ Maria Colgan
Jonathan Lewis Blog http://jonathanlewis.files.wordpress.com/
Riyaj Shamsudeen http://orainternals.wordpress.com/
Tanel Poder http://blog.tanelpoder.com/ snapper, scripts galore
Cary Millsap http://carymillsap.blogspot.ca/
Full transcript