Loading presentation...
Prezi is an interactive zooming presentation

Present Remotely

Send the link below via email or IM

Copy

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.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Performance Tuning

No description
by

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)
Questions
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)
truss
strace
dtrace
vendor tools (EMC, NETAPP)
Presentation Tips
Helpful don'ts from Don McMillan's
Death by Powerpoint
Overview
Introduction
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
Methodology
Approach
Process
Tuning
Infrastructure
Database
Application
https://drive.google.com/a/first4db.com/file/d/0BwsQThnZEjYnNTJlMmVhNDItMWEwOC00OGEwLTliMWEtMDJjOWRjMWYzMzUz/edit?usp=sharing
Chapter 1 - please read!
Method*R
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
sub-optimally.
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
changes.
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
References
SQL Tuning by Cardinality Feedback
dbms_xplan (ALLSTATS Option)
Make best statistics available
Hints
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/
http://blog.tanelpoder.com/?s=Sqlplus+is+my+second+home
SQLPLUS TIPS/TRICKS
Full transcript