em12c performance tuning outside the box
DESCRIPTION
ODTUG Webinar May 2013TRANSCRIPT
![Page 1: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/1.jpg)
K E L L Y N POT ’VIN
S R . T E C H N I C A L C O N S U L T A N T
EM12c Performance Diagnosis and Tuning Outside the Box
![Page 2: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/2.jpg)
Kellyn Pot’Vin
Westminster, Colorado
Oracle ACE Director, Sr. Technical Specialist at Enkitec
Specialize in performance and management of large enterprise environments.
Board of directors for RMOUG, Director of Training Days and Database Track Lead for KSCOPE 2013
Blog: DBAKevlar.com
Twitter: @DBAKevlar
![Page 3: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/3.jpg)
Performance Diagnostics in EM12c
Simple access to performance, resource usage and demands.
Data collection to investigate performance issues- current, recent and historical.
Capacity planning.
Have the real answer, not assumptions.
![Page 4: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/4.jpg)
Presentation Agenda
Performance Out of the Box with EM12c
Top Activity
SQL Monitor
ASH Analytics
Real-time ADDM
Compare ADDM
![Page 5: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/5.jpg)
Tools at your Disposal
Requires the Diagnostics Pack
![Page 6: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/6.jpg)
Top Activity, “The Grid”
Graphical display of performance usage.
15 second refresh, manual refresh or historical.
![Page 7: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/7.jpg)
When to Worry
Out of the Ordinary Activity, (KNOW YOUR DB!)
Colors outside of green and [some] blue.
Large amounts of blue, (high IO)
Remember that pink, (unknown) red, (concurrency/application) tan, (network) and orange, (commit) in the grid should be investigated.
Brown or black? Run for the hills! (JK)
![Page 8: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/8.jpg)
Here’s our spike, which waits?
Commonly, focus on pink, orange, red and brown for issues.
Network and queuing do have opportunities for tuning, as well.
Green and blue are expected, but also part of problems when over utilized.
![Page 9: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/9.jpg)
We’re in the Red, (Orange, too!)
Inspect High % use.
Note that
the update and execution may be impacting each other.
![Page 10: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/10.jpg)
Session Details
Seeing Red…
![Page 11: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/11.jpg)
Next?
Two sessions are executing
Option to run an AWR or ASH report, (right hand side)
![Page 12: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/12.jpg)
What ASH Tells Us
![Page 13: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/13.jpg)
The Icing on the Cake
Duh, add some memory to the EM12c box!
![Page 14: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/14.jpg)
SQL Monitor for Performance
• Elapsed Time
• SQL_ID, Beginning SQL Text.
• Parallel, Waits and Execution Time
![Page 15: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/15.jpg)
Digging in
• Choose your session, SQL_ID or SQL_Text • Shows active, completed sessions for amount of time chosen. • Shows high level wait events, dbtime, IO usage and duration.
![Page 16: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/16.jpg)
Digging Down
By SQL_ID, we can inspect: • Duration • DB Time • PL/SQL Java time • Wait Activity • Buffer Gets • IO Requests and IO Bytes • If Exadata, Offload Efficiency
![Page 17: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/17.jpg)
Monitoring Procedural Call
All SQL_ID’s called will show, along with duration so it’s simple to pinpoint trouble statements.
![Page 18: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/18.jpg)
SQL Details
• Note that the SQL Statement, along with elapsed time is shown.
• Data sources from Top Activity, not AWR data.
![Page 19: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/19.jpg)
And More Detail
Session info, wait info, cursors and stats.
![Page 20: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/20.jpg)
Added Data
Along with the main stats-
Activity information on the statement.
The execution plan
If there is a SQL Plan or outline in place.
If there have been any tuning advisors run against the statement
And a direct link to SQL Monitoring
![Page 21: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/21.jpg)
How to Use SQL Monitoring
Active Monitoring of database processing.
Investigation of performance.
Save off reports, which provide a graphical image of performance differing from Top Activity or ASH Analytics.
Distinct diagnosis at a session or statement level.
![Page 22: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/22.jpg)
ASH Analytics
Future of Top Activity
Package installation to database.
Always on, non-impact of Top Activity performance data gathering.
More defined, more accurate.
Historical data enhanced over Top Activity historical views.
![Page 23: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/23.jpg)
Pick Your View
Ability to choose timelines by:
Hour Day
Week Month
Calendar Custom
![Page 24: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/24.jpg)
Custom Review Pane
• You can choose to change the overview pane to display data for any amount of time.
• Just click on the pane and drag it to the area you are interested in or extend it to cover the areas you are interested to investigate.
• Choose your filters or view all data and you are ready to go!
![Page 25: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/25.jpg)
Familiar Interface
Similar to Top Activity when in “Activity” mode.
![Page 26: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/26.jpg)
Sql Details View
![Page 27: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/27.jpg)
Pick Your Poison
View data very similar to the SQL and Session data in Top Activity.
All data is sourced by AWR data and dependent on samples and AWR retention/interval info in the respository.
![Page 28: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/28.jpg)
It’s All in the Details
![Page 29: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/29.jpg)
Activity Details
Activity shows wait detail over time.
Processes, including parallel sessions involved during shaded time.
Option to run AWR or ASH report.
![Page 30: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/30.jpg)
The Rest of the Story
For standard SQL- Plan, Plan Control and Tuning History is shown under individual tabs.
SQL Monitor is minimized access to the SQL Monitor view.
![Page 31: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/31.jpg)
Load Map
New Visual Way of Showing Data, Multiple
Ways!
![Page 32: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/32.jpg)
Data Break Down
Display offers incredible diversity in wait, resource usage and other critical event choices.
![Page 33: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/33.jpg)
ASH Analytics – When to Use It
Need the more defined ASH data for EM diagnostics.
Want a second way to present data to less “DBA” centric groups, (load map)
Database level OR session/statement level performance diagnosis.
Dig down deep, present data in numerous formats to get the most complete picture of a complex issue.
Can be used for Real-time or historical analysis.
![Page 34: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/34.jpg)
Real-Time ADDM
Yes, it requires a PL/SQL installation for the view data.
Uses ADDM data for the source.
Always on, low to no impact.
Normal Mode or Emergency Mode when Emergency Monitoring is required.
![Page 35: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/35.jpg)
On Your Mark, Get Set…
This is a recorded ADDM session, beginning from the time you click “Start”.
![Page 36: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/36.jpg)
In Progress Data
Ability to stop and restart.
Findings gathered during progress.
Check progress notifies of any issues.
![Page 37: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/37.jpg)
Finished!
Once finished, verify no failures/errors occurred in the collection.
Use the tabs to investigate findings, activity, hang data and statistics.
The number of findings are shown.
![Page 38: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/38.jpg)
The Findings
Example shows low priority SQL statements using significant db time, but not other issues at this time.
If any issues are found that are high priority, will be listed in red and details below the main pane, (low, medium, high priority levels.)
![Page 39: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/39.jpg)
Activity Tab
Activity Data, but sourced from ADDM.
Similar output to Top Activity and ASH Analytics.
![Page 40: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/40.jpg)
Wait Details
• By highlighting a wait link on the right, you can detail down to the actual wait information for that wait event.
![Page 41: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/41.jpg)
Hanging out
If a database hang situation occurred and the real-time ADDM was used to diagnose, then the HANG DATA tab will show any diagnostic data it has collected during the collection.
Statistics Data:
![Page 42: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/42.jpg)
Last but not Least…
Initialization Parameter data for the database instance.
Any undocumented of non-recommended parameter settings will be identified and listed in the findings section.
![Page 43: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/43.jpg)
Compare Period ADDM
How is it different from Real-Time ADDM?
Ability to compare TWO snapshots in time, side by side of ADDM data.
Compares ADDM snapshots against each other, (dependent on snapshot intervals and retention.)
All comparisons can be saved off or mailed from the console, (mailed through EM12c settings)
![Page 44: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/44.jpg)
Choosing a Comparison Time
![Page 45: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/45.jpg)
Comparison Activity
• Clear comparison from previous day, same time to see performance issue vs. the right hand side snapshot.
• Commonality comparison of the SQL for snapshots being compared. • Note the concurrency, commits and increased application waits.
![Page 46: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/46.jpg)
It’s all in the Details
First tab shows any configuration differences between the two snapshots and what the configuration parameter is.
![Page 47: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/47.jpg)
Findings Summary Detail
Shows comparison increases or decreases in waits.
Lists the percentage of change between each period compared.
Upon highlighting, details data regarding the increase or decrease.
![Page 48: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/48.jpg)
SQL Changes
We can dig down into each of the SQL Statements found to be the highest impacts to the system and diagnose further.
![Page 49: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/49.jpg)
Finding Detail Descriptions
As shown above, the wait on Checkpoints to Tablespace are describe below once you highlight the section in the findings tab.
And for RAC, some waits can be broken down by instance.
![Page 50: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/50.jpg)
Resource Usage: CPU
CPU Usage is viewable by instance and total usage.
If no CPU bound wait issues were seen, its stated by comparison snapshot.
![Page 51: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/51.jpg)
Resource Usage: Memory
• If you note, Memory has a warning alert by the tab to point you to it after the comparison is completed.
• The base and comparison is in red, meaning that Virtual paging was an issue in both snapshots.
• Data is separated by instance in RAC, showing clear usage for better diagnostics.
![Page 52: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/52.jpg)
Resource Usage: IO
I/O is separated by Throughput and Single block read latency.
Again, if there was an issue, a warning would be on the IO tab and the Base and Comparison would show in red instead of green.
![Page 53: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/53.jpg)
Resource Usage: Interconnect
As this is RAC, note that we also have an interconnect tab with data on the speed and performance.
Total vs. rate on throughput is viewed through a radio button choice.
![Page 54: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/54.jpg)
So What Changed?
The graphs show us where we need to focus:
![Page 55: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/55.jpg)
How to Use the Comparison ADDM
Excellent to diagnose “what has changed”.
“Just the Facts” information on a comparison of time.
Dependent upon retention time settings and intervals for AWR.
Historical data, can be set by date, custom, by previous snapshot.
Will move to next snapshot window if mid-snapshot time span is chosen.
![Page 56: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/56.jpg)
EM12c blogs-
Leighton Nelson- http://blogs.griddba.com/
Rob Zoeteweij-http://oemgc.files.wordpress.com/
Gokhan Atil- http://www.gokhanatil.com/
Martin Bach- http://martincarstenbach.wordpress.com
Niall Litchfield- http://orawin.info/blog/
Info for Me!
Company Website: www.enkitec.com
Twitter: @DBAKevlar
RMOUG: www.rmoug.org
Linkedin: Kellyn Potvin and/or Rocky Mountain Oracle User Group
Email: [email protected] or [email protected] or [email protected]
Blog: https://dbakevlar.com
Reference
![Page 57: Em12c performance tuning outside the box](https://reader033.vdocuments.us/reader033/viewer/2022051515/54c68ade4a7959fb258b4646/html5/thumbnails/57.jpg)
Kscope13 features more than 300 educational sessions, full-day symposiums, hands-on training courses, informal networking sessions, and a plethora of chances to increase your technical know-how by learning from the best. • Application Express • ADF and Fusion Dev. • Developer's Toolkit • The Database • Building Better Software • Business Intelligence • Essbase • Planning • Financial close • EPM Reporting • EPM Foundations and Data
Management • EPM Business Content http://kscope13.com/registration