SQL Performance Analyzer Active Report

135 Pages·2015·7.79 MB·English

by  Kurt Engeleiter

Workshop
Oracle Database
Performance
Management Best
Practices
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 2
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.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 3
Agenda
• Introduction
• Oracle Database Performance Tuning Fundamentals
• Find, Fix and Validate: Methodology and Toolset
• Find Fix and Validate: An Example
• Real-World Best Practices & Tips
• New Features and Enhancements (DB 12.1.0.2)

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 4
Downtime resulting from
78%
Top Challenges untested changes
Database Management

Use SQL tuning to address
48%
performance issues
Data stores growing by
46%
20%+
?
Handle more than 50 DBs

39%
each
Key Takeaway:
Improve & Ensure Higher Quality of Service
Source: From Database Clouds to Big Data: 2013 IOUG Survey On Database Manageability
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 5
Continuous Innovation
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 6
Agenda
• Introduction
• Oracle Database Performance Tuning Fundamentals
• Find, Fix and Validate: Methodology and Toolset
• Find Fix and Validate: An Example
• Real-World Best Practices & Tips
• New Features and Enhancements (DB 12.1.0.2)

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 7
Database Time (DB Time)
• Total time in database calls by foreground sessions

• Includes CPU time, IO time and non-idle wait time

• DB Time <> response time

• New metric for Oracle performance analysis

Database time is total time spent by user processes either actively working or actively
waiting in a database call.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 8
Fundamental Concepts
Active Session =
Session currently spending time in a database call
Database Time (DB Time) =
Total time session spent in all database calls
Average Activity of the Session (% Activity) =
The ratio of time active to total wall-clock time
Browse Read Reviews
Checkout
Add to Cart
Books For One Book
= time spent in database TIME
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 9
DB Time Theory to Practice: EM Performance page
• Active Sessions by wait class over time
• Colored area = Amount of DB time , More the DB time , More the Problem
• “Click on the big stuff…”
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 10