awr, ash with em13 at hotsos 2016
TRANSCRIPT
![Page 1: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/1.jpg)
![Page 2: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/2.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Performance Data with EM13c and DB12cAWR , ASH and Other Performance Data Deep Dive
Kellyn Pot’Vin-GormanConsulting Member of Technical Staff, SCPEnterprise ManagerMarch, 2016
![Page 3: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/3.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
DBA Kevlar, the Geek Goth Girl
![Page 4: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/4.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
![Page 5: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/5.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Why an Updated Session
While building and testing the Performance Tuning DB12c exam, it became apparent is was needed.
![Page 6: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/6.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Revisit to AWR
Top Changes
Automatic Workload Repository
Active Session History
The Rest of the Cool Stuff
1
2
3
4
5
![Page 7: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/7.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda with Highlight
Revisit to AWR
Top Changes
Automatic Workload Repository
Active Session History
The Rest of the Cool Stuff
1
2
3
4
5
![Page 8: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/8.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
History of AWR and ASH
• ASH= Active Session History
• AWR= Automatic Workload Repository
• Introduced in Oracle 10g
• Evolution to statspack, requests for performance reporting improvements.
• “Always on” approach to performance metrics with requirement of non-locking collection process.
• Requires Management Diagnostic Pack License from Oracle.
![Page 9: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/9.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
AWR and ASH Architecture
DBA_HIST Views
V$ Views
Along with AWR Data
Memory Buffers write one way, users read the opposite direction!
and MMON
![Page 10: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/10.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Why Do We Combine Diagnostic and Tuning Packs?
Tuning Pack includes the Following:
• SQL Monitor
• SQL Access Advisor
• SQL Tuning Advisor
These products source their data from AWR….
![Page 11: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/11.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Other Important Reports to know:
$ORACLE_HOME/rdbms/admin/awrrpt.sql;
$ORACLE_HOME/rdbms/admin/ashrpt.sql;
$ORACLE_HOME/rdbms/admin/awrsqlrpt.sql;awrinfo.sql General AWR Info –Very important for AWR Warehouse!
awrddrpt.sql Comparison report between snapshots
awrgrpt.sql RAC Aware AWR Report.
![Page 12: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/12.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
What NOT to DO to the AWR
• Underscore parameter changes to spfile that change the default settings for ASH samples, intervals, etc.
– DON’T DO IT. Step away….walk away from the keyboard. NOW.
![Page 13: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/13.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda with Highlight
Revisit to AWR
Top Changes
Automatic Workload Repository
Active Session History
Rest of the Cool Stuff
1
2
3
4
5
![Page 14: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/14.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
A lot of additions for In-Memory!
• Manageability support for In-Memory Column Store
• The new Oracle Database In-Memory Column Store (IM column store) feature accelerates database performance of analytics, data warehousing, and online transaction processing (OLTP) applications.
• SQL Monitor report, ASH report, and AWR report now show statistics for various in-memory operations.
![Page 15: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/15.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Configuring is Now a Breeze
![Page 16: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/16.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Managing Even Easier with In-Memory Central
![Page 17: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/17.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
New to ASH Analytics
Average Active Sessions chart
• Click on CPU wait class to see its breakdown by CPU usage for various in-memory operations
• View various CPU used for operations other than in-memory operations.
![Page 18: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/18.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Why Are These Changes Important?
In-memory Column store data is very quick to query, but just like other features, if advanced sorting and “massaging” of the data is required, complex performance issues can arise.
AWR and Enterprise Manager offers a window into these when challenges occur.
![Page 19: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/19.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda with Highlight
Revisit to AWR
Top Changes
Automatic Workload Repository
Active Session History
The Rest of the Cool Stuff
1
2
3
4
5
![Page 20: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/20.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Report Changes
Top ADDM Findings as Part of the AWR Report
![Page 21: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/21.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Foreground Events
Displays Top TEN Foreground Events
CPU Should be top…to a point…
![Page 22: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/22.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Top Elapsed Time is Still King
Tune for time or you’re wasting time…
![Page 23: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/23.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Changes in AWR Report for In-Memory
In-Memory Segments Statistics
• Shows in-memory segment consumption based on various attributes, such as, scans, DB block changes, populate CUs, and repopulate CUs.
• Time Model Statistics section shows statistics related to in-memory CPU usage and Instance Activity Statistics section shows statistics related to in-memory activities.
![Page 24: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/24.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Wait Class by Total Wait Time
![Page 25: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/25.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
The IO Profile
For OLAP and DSS Systems, Great Section in Report
![Page 26: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/26.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Other New Sections
• Goldengate
• Xstreams
• Resource Stats
• Advanced Queuing Data
![Page 27: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/27.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Example of Why AWR is Still the Gold Standard
• EM Health Review in Preparation to Upgrade to EM13c
• EM Diagnostics is Run on Repository, OMS and Agents.
• Author of Diagnostics starts from OMS tier, unsure of what is impact
Where do I start in the Diagnostics data as an Optimization DB Specialist?
![Page 28: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/28.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
AWR Data in Part of EM Diagnostics, (OMSVFY)
There are no mviews in the
OMR!
Never heard of you!
![Page 29: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/29.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Physical Reads
You both are an unknown!
![Page 30: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/30.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
The Folly
Created Materialized View on top of Metric Tables Used by Enterprise Mgr
Mview created pressure on roll up processing that aggregate the data from
source tables.
To eliminate the overhead, the rollup
jobs were shut off
Mview AND EM console slow to unusable state as raw metric tables become
huge.
Creates own
schema objects
Loads more
into own objects
![Page 31: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/31.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Time Requirements
Without Using AWR Data for Healthcheck to Diagnose Performance Issues
With Using AWR Data for Healthcheck to Diagnose Performance Issues
8 Months and No Answer
12 Hours and Resolution
![Page 32: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/32.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda with Highlight
Revisit to AWR
Top Changes
Automatic Workload Repository
Active Session History
The Rest of the Cool Stuff
1
2
3
4
5
![Page 33: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/33.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
The Best of ASH
1. Active Session History work off ASH Time with ASH samples and not exclusive to AWR snapshots.
2. Small Windows of time to isolate database challenges.
3. Shows the “Forrest for the Trees”. Great for Complex Issues.
4. Includes Blocking, DOP, Procedural calls and session information.
![Page 34: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/34.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
ASH Report Changes
• ASH report header table shows the size of in-memory pool under In Memory Area column.
• Top Events, Top SQL, and Activity Over Time sections show CPU consumption by various in-memory operations.
![Page 35: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/35.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
P Value Top Events
Table Presented
![Page 36: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/36.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Saves You From Having to do This:
The values represent:P1—The absolute file number for the data file involved in the wait.P2—The block number within the data file referenced in P1 that is being waited upon.P3—The reason code describing why the wait is occurring.
select
p1 "File #“, p2 "Block #", p3 "Reason Code“ from
v$session_wait
where event = 'xxx';
![Page 37: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/37.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Know the Module, Know the Action
Shows Percentage of Activity for Action
![Page 38: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/38.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Top SQL and PL/SQL Procedures
![Page 39: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/39.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Blocking Sessions Displayed Clearly
![Page 40: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/40.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
How Did it Change Over The Time Elapse?
![Page 41: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/41.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
ASH Analtyics, the New Top Activity
![Page 42: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/42.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Details of SQL and Sessions…Plus More
Capability to update each column to demonstrate different data.
![Page 43: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/43.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
The Chameleon
View Data by Wait Class and Blocking Sessions
![Page 44: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/44.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Using ASH Analtyics with Search SQL
![Page 45: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/45.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Searching SQL with ASH Analytics
• Copy SQL_ID from ASH Analtyics
• Go to Search SQL
![Page 46: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/46.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Change Defaults
Include AWR, (ALL) and AWR Baselines. Paste in the
SQL_ID before clicking on “RUN”
![Page 47: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/47.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Remember the Tabs
Click on SQL_ID to proceed to Details
![Page 48: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/48.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Why ASH shouldn’t be used for Auditing
Thanks to Graham, Uri and
JB for this wonderful graphic!
![Page 49: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/49.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Why Time is Important
Database Time
• Total time spent by sessions in the database server actively working, (CPU) or actively waiting, (aka non-idle.)
Active Sessions
• The number of active sessions, (including waiting) in the database server at a specific time is the rate of change of database time during that period, (aka workload.)
![Page 50: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/50.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Understanding ASH Time
DBTime is calculated as an approximate made up by multiplying sampling interval by the total count of the samples.
Keeping in mind that each sample represents 1 second of DBTime…..
Then ASH Math results in:
COUNT(*) = <DBTime>
GROUP BY <Wait Event>
![Page 51: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/51.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
ASH Mining
Querying ASH, (V$ or GV$ACTIVE_SESSION_HISTORY) Data DIRECTLY to Answer a Question….
• What are the top ten SQL ID’s in the last ten minutes?
![Page 52: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/52.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
How Often Were SQL ID’s on CPU vs. Waiting on CPU?
![Page 53: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/53.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Monitoring Database Activity, New School
SQL Monitor:
Is it done yet? Did it use parallel? What important data do we know?
![Page 54: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/54.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Change My View, Change My Outlook
By updating the “Top 100 by” options, the data returned changes-
![Page 55: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/55.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Details Top Info
• If it’s Exadata, It will show offload percentage.
• If it’s In-memory, it will include In-memory information.
![Page 56: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/56.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
What Kind of Resources in Layman Terms?
Very little and I can prove it….
![Page 57: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/57.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
SQL Monitor and Adaptive Plans
The Execution plan shows
The Plan Statistics tab contains a drop down list to show current plan, final plan, and full plan. It also contains Plan Note button, which when clicked, shows the notes that are generated in the explain plan for the SQL statement.
![Page 58: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/58.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Monitoring
Use SQL Monitor.
Execute on DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
View on V$SQL_MONITOR and V$SQL_PLAN_MONITOR
![Page 59: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/59.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Adaptive Plans in SQL Monitor
![Page 60: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/60.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda with Highlight
Revisit to AWR
Top Changes
Automatic Workload Repository
Active Session History
The Rest of the Cool Stuff
1
2
3
4
5
![Page 61: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/61.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
ASH Report Running Slow
ASH Report taking over 8 hrs to complete!
Sticking while pulling plan information on top SQL.
Pulled Execution plan- joins to X$ tables to GV$Active_Session__History
Merge Join Cartesians and million row hash table that wasn’t possible for amount of data in ash buffer.
Collected fixed stats, (X$ tables) to correct.
![Page 62: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/62.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Enterprise Manager Slowness
Shut off SQL Baselines, (EM is NEVER GOING TO USE THEM!!
SQL> ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=false SCOPE=both;
SQL> ALTER SYSTEM SET optimizer_use_sql_plan_baselines=false SCOPE=both;
Tune the Job Pool, 1st get recommendation
$ repvfy verify jobs -test 6007 -details
Then update to recommended value:
$ emctl set property -name oracle.sysman.core.conn.maxConnForJobWorkers
-value <recommended> -module emoms
![Page 63: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/63.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Tips to Tuning the Enterprise Manager-
Java Heap is used heavily with the JVMD built into EM13c.
Update from the default to 8Gb is highly recommended:
$ emctl set property -name JAVA_EM_MEM_ARGS-value "-Xms256m –Xmx8192m -XX:MaxPermSize=768M -
XX:+UseConcMarkSweepGC-XX:+UseParNewGC -XX:+CMSClassUnloadingEnabled-XX:CompileThreshold=8000 -XX:PermSize=128m"
![Page 64: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/64.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
The AWR Warehouse• Central warehouse configured for
long term AWR data retention
• Historical and ongoing AWR snapshots collected from databases enabled for AWR warehouse
• ETL jobs moves snapshots from source databases into AWR warehouse
• Retention period configurable for weeks, months, years or forever (default)
![Page 65: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/65.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
AWR ETL Jobs
DBMS Job on Source Database to directory
Job in EM Job Service that pulls file from Source Target and then pushes to AWR Warehouse Target Directory Final DBMS Job Inserts data
into AWR Warehouse
All data identified by OLD/NEW DBID and with the EM_ID, (CAW_DBID_MAPPING in AWR WAREHOUSE)
Source Target
Enterprise Manager
AWR Warehouse
![Page 66: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/66.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
AWR Warehouse Interface• Warehouse dashboard tracking
ETL jobs
• All AWR features available on long term AWR data
• Performance page
• AWR report
• ASH analytics
• Compare Period ADDM
• Compare Period Report
• Integrated seamlessly into EM UI
• Zero runtime overhead on source Production databases
![Page 67: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/67.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
New More Efficient Job Interface
![Page 68: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/68.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
ADDM Comparison in AWR Warehouse
Yes, you can compare one snapshot against another snapshot in DIFFERENT database!
![Page 69: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/69.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
CAW_DBID_MAPPING Table and AWR Mining
Most important table for anyone querying the AWR Warehouse!
![Page 70: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/70.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
How Current AWR Mining Queries Change
from dba_hist_sys_time_model stm, dba_hist_snapshot s, gv$parameter p, dbsnmp.caw_dbid_mapping m
where stm.stat_name in ('DB CPU','background cpu time')
and LOWER(m.target_name)= '&dbname'
and s.dbid= m.new_dbid and s.snap_id = stm.snap_id
and s.dbid = stm.dbid and s.instance_number = stm.instance_number
and p.name = 'cpu_count' and p.inst_id = s.instance_number)
![Page 71: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/71.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
CPU Usage Issues
“Nothing’s changed in months…”
![Page 72: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/72.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Answer Specific IT Questions
![Page 73: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/73.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Questions?
![Page 74: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/74.jpg)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Connect with me via Social Media:
![Page 75: AWR, ASH with EM13 at HotSos 2016](https://reader033.vdocuments.us/reader033/viewer/2022052318/586e8d021a28aba0038b871d/html5/thumbnails/75.jpg)