Skip to main content
Disciplines
Automotive Business IT + Informatics Construction + Real Estate Electrical Engineering + Electronics Energy + Sustainability Insurance + Risk Finance + Banking Management + Leadership Marketing + Sales Mechanical Engineering + Materials
Events
DE
EN
Books
Journals
Topic Page
Marketing
Start single access now
Access for companies
EXTENDED SEARCH
Log in
Top
Published in:
Understand Relational Databases Read chapter Read first chapter

2019 | OriginalPaper | Chapter

18. Improve SQL Performance

Author : Jon Heller

Published in: Pro Oracle SQL Development

Publisher: Apress

Log in

Activate our intelligent search to find suitable subject content or patents.

search-config
loading …

Abstract

It’s time to use our theories and start building practical SQL tuning solutions. This chapter starts with high-level applications and then drills down through databases and SQL statements, until we get to operations.

Dont have a licence yet? Then find out more about our products and how to get one now:

Springer Professional "Wirtschaft+Technik"

Online-Abonnement

Mit Springer Professional "Wirtschaft+Technik" erhalten Sie Zugriff auf:

  • über 102.000 Bücher
  • über 537 Zeitschriften

aus folgenden Fachgebieten:

  • Automobil + Motoren
  • Bauwesen + Immobilien
  • Business IT + Informatik
  • Elektrotechnik + Elektronik
  • Energie + Nachhaltigkeit
  • Finance + Banking
  • Management + Führung
  • Marketing + Vertrieb
  • Maschinenbau + Werkstoffe
  • Versicherung + Risiko

Jetzt Wissensvorsprung sichern!

inform now

Springer Professional "Technik"

Online-Abonnement

Mit Springer Professional "Technik" erhalten Sie Zugriff auf:

  • über 67.000 Bücher
  • über 390 Zeitschriften

aus folgenden Fachgebieten:

  • Automobil + Motoren
  • Bauwesen + Immobilien
  • Business IT + Informatik
  • Elektrotechnik + Elektronik
  • Energie + Nachhaltigkeit
  • Maschinenbau + Werkstoffe




 

Jetzt Wissensvorsprung sichern!

inform now

Springer Professional "Wirtschaft"

Online-Abonnement

Mit Springer Professional "Wirtschaft" erhalten Sie Zugriff auf:

  • über 67.000 Bücher
  • über 340 Zeitschriften

aus folgenden Fachgebieten:

  • Bauwesen + Immobilien
  • Business IT + Informatik
  • Finance + Banking
  • Management + Führung
  • Marketing + Vertrieb
  • Versicherung + Risiko




Jetzt Wissensvorsprung sichern!

inform now
previous chapter Understand SQL Tuning Theories
next chapter Solve Challenging Problems with Arcane SQL Features
Footnotes
1
This chapter assumes you have licensed the Diagnostics and Tuning Packs and are using Enterprise Edition. In general, the Diagnostics Pack covers AWR and ASH, and the Tuning Pack covers DBMS_SQLTUNE and advisors. If your edition or license doesn’t allow access to the tools discussed in this chapter the concepts still apply but you will need to look for free alternatives. For example, Statspack can be used in place of AWR and Simulated ASH can be used in place of ASH.
 
3
All values in V$SQL are cumulative, but only since the SQL statement has been in the shared pool. It’s hard to predict how long statements will be in the shared pool. In practice, the slow statements we care about will stay around for a long time.
 
4
Like with real atoms, operations can be divided into smaller pieces. We could trace operations to C functions, or operating system calls, or machine instructions, etc. In practice, the operation is as deep as we want to go.
 
5
If SQL monitoring data ages out we can still partially recreate the format of the reports using AWR and ASH data. I’ve built an open source program for historical SQL monitoring: https://github.com/jonheller1/hist_sql_mon .
 
6
See my answer here for a long list of things that affect the DOP: https://stackoverflow.com/a/21132027/409172
 
7
For example, there are at least four cases when the total cost of the execution plan is less than the cost of the child operations. See my answer here for details: https://stackoverflow.com/a/25394748/409172
 
8
The default is supposed to automatically decide whether or not to gather index statistics. But in practice the default is effectively TRUE. See this question for details: https://dba.stackexchange.com/q/12226/3336 .
 
Metadata
Title
Improve SQL Performance
Author
Jon Heller
Copyright Year
2019
Publisher
Apress
Book
Pro Oracle SQL Development

Print ISBN: 978-1-4842-4516-3

Electronic ISBN: 978-1-4842-4517-0

Copyright Year: 2019

DOI
https://doi.org/10.1007/978-1-4842-4517-0_18

Premium Partner

    Image Credits