Writing Optimal SQL / Database Troubleshooting & Tuning with Jonathan Lewis

Total time
Logo Oracle University

Need more information? Get more details on the site of the provider.

Starting dates and places

There are no known starting dates for this product.

Description

DAY 1 : Writing optimal SQL

This first day has two targets.

  • First: how do you improve the performance of a production system by attacking inefficient SQL
  • Secondly: how do you design a system so that you can get data into and out of it efficiently

For most people, the design has already happened, nevertheless you need to be aware that the two targets overlap significantly - as you struggle to address a particularly inefficient piece of SQL, you need to be aware that there are structural features of the database that may help you find a cost effective solution despite the problems imposed by the SQL itself. This part of the course will cover methods of reviewing data distribution patterns, u…

Read the complete description

Frequently asked questions

There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.

Didn't find what you were looking for? See also: SQL & MySQL, Reading & Writing, PL/SQL, Programming (general), and IT Security.

DAY 1 : Writing optimal SQL

This first day has two targets.

  • First: how do you improve the performance of a production system by attacking inefficient SQL
  • Secondly: how do you design a system so that you can get data into and out of it efficiently

For most people, the design has already happened, nevertheless you need to be aware that the two targets overlap significantly - as you struggle to address a particularly inefficient piece of SQL, you need to be aware that there are structural features of the database that may help you find a cost effective solution despite the problems imposed by the SQL itself. This part of the course will cover methods of reviewing data distribution patterns, use of indexes, use of views (stored and inline), analytic functions, sub query factoring, as well as statistics, hints, and a brief review of execution plans.

DAY 2 : Troubleshooting &Tuning

Troubleshooting or Tuning:

  • What's the difference?
  • What are the strategies?
  • Why tuning is hard but trouble-shooting is easy?

Key targets, indicators and mechanism for producing a well-tuned system on day one. Strategies for dealing with badly performing systems after go-live.

Frequently Occurring Problems:

Some of the most commonly occurring issues that affect performance after a system has gone into production. Methods for spotting them, measuring the impact, and dealing with the cost / risk / benefit triangle involved in fixing them. Getting into the habit of pre-emptive analysis and pro-active fixing.

Quick Fixes:

Methods, workarounds, dirty tricks and parameters for dealing with classic performance problems when the system is in production. There aren't many quick fixes that can be applied across the board - each one needs careful examination of costs, risk, and benefits. In this session we consider some of the options that are most likely to be worthwhile.

V$ and X$:

This session will describe the views that are most commonly of use, and explain the meaning of some of the more useful items.


Audience
  • Database Designers
  • PL/SQL Developer
  • Database Administrators

Course Topics Addressing the problem, not the SQL Making the SQL readable Structural Optimization A strategy for designing and debugging the SQL Sundry strategies for awkward problems Troubleshooting or Tuning Frequently Occurring Problems Quick Fixes V$ and X$
Course Objectives
  • Recognize the importance of being able to see the SQL business function & make the SQL easy to read
  • See how the data volumes and distribution have an important contribution to write efficient SQL
  • Learn a strategy for first-cut design of new SQL, and debugging of existing SQL
  • Gain some understanding of where and why it may be necessary to control the SQL using hints

There are no reviews yet.

Share your review

Do you have experience with this course? Submit your review and help other people make the right choice. As a thank you for your effort we will donate $1.- to Stichting Edukans.

There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.