Send the link below via email or IMCopy
Present to your audienceStart 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.
Make your likes visible on Facebook?
You can change this under Settings & Account at any time.
Transcript of 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?
* 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)
Usually rquires elevated priviledges (root)
vendor tools (EMC, NETAPP)
Helpful don'ts from Don McMillan's
Death by Powerpoint
* 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
Real Time Diagnostics
After the fact repositories
Diagnostics for Infrastructure
Platform dependent (vmstat, iostat); may need SYSADM "assistance"
Oracle v$OSSTAT - very high level
* oratop (no packs required)
* snapper (no packs required)
* sql monitor (tuning packs)
Objective of this presentation
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)
* 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/