“Tune that SQL” now what do I do?

PRESENTER: Gary Gordhamer, Managing Principal Consultant, Viscosity North America

Tuning SQL statements in Oracle is more of an art than a science. In this session we will cover the basic tools and steps to tuning a SQL statement. Starting with finding the SQL statement and collecting the right information. How does the Optimizer work to transform SQL into an execution plan? What information is an execution plan is trying to show? Finally, what are some of the things that can be tuned as well as tools Oracle has provided to make things easier. If you are new to tuning SQL in Oracle or trying to freshen up your skills, this session should have enough information for everyone.

Finding SQL statement
– Top SQL by?
– what information about a SQL to collect

The Oracle Optimizer
– steps
– explain plan – execution plan
– bind peaking
– soft parse vs hard parse

Details in explain plan
– basic plan
– collected statistics
– estimate vs actual

What to tune?
– change the code
– change the statistics
– change the way Oracle works

Tools to help
– SPM
– Profiles
– Patches
– SQL Tuning Advisor

Q&A

PRESENTATION:

09_14_2021-Tune-that-SQL-NYOUG-Viscosity