Troubleshooting Oracle Performance

290 Pages·2014·3.87 MB·English

Troubleshooting Trivadis AG
Oracle Christian Antognini
Performance [email protected]
BASEL BERN LAUSANNE ZÜRICH DÜSSELDORF FRANKFURT A.M. FREIBURG I.BR. HAMBURG MÜNCHEN STUTTGART WIEN

1 2014 © Trivadis
Troubleshooting Oracle Performance
2014-04-28
@ChrisAntognini
 Senior principal consultant, trainer and partner
at Trivadis in Zurich (CH)
 [email protected]
 http://antognini.ch
 Focus: get the most out of Oracle Database
 Logical and physical database design
 Query optimizer
 Application performance management
 Author of Troubleshooting Oracle Performance
(Apress, 2008/2014)
 Proud member of OakTable Network, Oracle ACE Director
2 2014 © Trivadis
Troubleshooting Oracle Performance
2014-04-28
Our company
Trivadis is a market leader in IT consulting, system integration,
solution engineering and the provision of IT services focusing
on and technologies in Switzerland,
Germany and Austria.
We offer our services in the following strategic business fields:

O P E R A T I O N

Trivadis Services takes over the interacting operation of your IT systems.
3 2014 © Trivadis
Troubleshooting Oracle Performance
2014-04-28
With over 600 specialists and IT experts in your region
12 Trivadis branches and more than
Hamburg
600 employees

200 Service Level Agreements

Over 4,000 training participants
Düsseldorf
Research and development budget:
CHF 5.0 / EUR 4 million
Frankfurt

Financially self-supporting and
sustainably profitable
Stuttgart

Wien Experience from more than 1,900
Freiburg
projects per year at over 800
München
customers
Brugg
Basel
Bern Zurich
Lausanne
4 2014 © Trivadis
4 Troubleshooting Oracle Performance
2014-04-28
Troubleshooting Oracle Performance, Apress 2014
 Foundations  Query Optimizer (cont.)
1. Performance Problems 8. Object Statistics
2. Key Concepts 9. Configuring the Query Optimizer
10. Execution Plans
 Identification
3. Analysis of Reproducible Problems  Optimization
4. Real-Time Analysis of 11. SQL Optimization Techniques
Irreproducible Problems 12. Parsing
5. Postmortem Analysis of 13. Optimizing Data Access
Irreproducible Problems
14. Optimizing Joins
15. Beyond Data Access and Join
 Query Optimizer
Optimization
6. Introducing the Query Optimizer
16. Optimizing the Physical Design
7. System Statistics
5 2014 © Trivadis
Troubleshooting Oracle Performance
2014-04-28
Which Versions Are Covered?
 Oracle Database 10g Release 2, up to version 10.2.0.5.0
 Oracle Database 11g Release 1, up to version 11.1.0.7.0
 Oracle Database 11g Release 2, up to version 11.2.0.4.0
 Oracle Database 12c Release 1, version 12.1.0.1.0
7 2014 © Trivadis
Troubleshooting Oracle Performance
2014-04-28
THANK YOU.
BASEL BERN LAUSANNE ZÜRICH DÜSSELDORF FRANKFURT A.M. FREIBURG I.BR. HAMBURG MÜNCHEN STUTTGART WIEN

8 2014 © Trivadis
Troubleshooting Oracle Performance
2014-04-28
Troubleshooting Key Concepts
Oracle
Performance
BASEL BERN LAUSANNE ZÜRICH DÜSSELDORF FRANKFURT A.M. FREIBURG I.BR. HAMBURG MÜNCHEN STUTTGART WIEN

1 2014 © Trivadis
Troubleshooting Oracle Performance - Key Concepts
2014-04-28
Agenda
1. Selectivity and Cardinality
2. What Is a Cursor?
3. Life Cycle of a Cursor
4. How Parsing Works
2 2014 © Trivadis
Troubleshooting Oracle Performance - Key Concepts
2014-04-28
selectivity.sql
Selectivity
 It’s a value between 0 and 1.
 It can also represented as a percentage.
 It represents the fraction of rows filtered by an operation.
 Example: an operation that reads 120 rows and returns 18 of them has a
selectivity of 0.15 (18/120).
3 2014 © Trivadis
Troubleshooting Oracle Performance - Key Concepts
2014-04-28