Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


SQL tuning

No description

Leo Stefa

on 15 October 2012

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of SQL tuning

with SQL, you say "what you want" - not "how to get it“, is there any way to control how to do it?? SQL - Context for tuning A query could be “right” in terms of semantic, but “wrong” or “bad” in terms of performance. RDBMs Optimizers are getting smarter every time, but that is not enough to ensure every query will run fast. Bad performance in a sql statement could be related to a bad design on data model. There is no a recipe for tuning all sql statements , all engines, but there are some common tools and tips. SQL SQL> SELECT last_name employee, TO_CHAR(salary, '$99,990.99')
2 FROM employees
3 WHERE department_id = 80 AND last_name = 'Russell';

------------------------- ---------------------------------
Russell $14,000.00 We are just covering sql statements,
but tuning involves more than that... Context for tuning Optimizer Optimizer The optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time Optimizer statistics are a collection of data that describe more details about the database and the objects in the database.

These statistics are used by the query optimizer to choose the best execution plan for each SQL statement Statistics Statistics Table statistics
Number of rows
Number of blocks
Average row length

Column statistics
Number of distinct values (NDV) in column
Number of nulls in column
Data distribution (histogram)

Index statistics
Number of leaf blocks
Clustering factor

System statistics
I/O performance and utilization
CPU performance and utilization Statistics • Cardinality– Estimate of the number of rows coming out of each of the operations. Execution Plan • Parallel Execution – In case of parallel execution, is each operation in the plan being conducted in parallel? Is the right data redistribution method being used? • Partition pruning – Are only the necessary partitions being accessed to answer the query? • Join order – The order in which the tables are joined to each other. • Join type – The type of join (e.g., outer, anti, semi, etc.). • Join method – The method (e.g., hash, sort-merge, etc.) used to join tables with each other. • Access method – The way in which the data is being accessed, via either a table scan or index access. Full table scan - Reads all rows from a table and filters out those that do not meet the where clause
Table access by ROWID – The rowid of a row specifies the data file, the data block within that file,and the location of the row within that block About access methods Directly to the table... Through an index... Indexes
B-tree indexes: the default and the most common

Bitmap indexes: compact; work best for columns with a small set of values

Global and local indexes: relate to partitioned tables and indexes

Function-based indexes: contain the precomputed value of a function/expression Indexes are optional structures associated
with tables that allow SQL statements to
execute more quickly against a table B-tree index In many RDBMs, it’s the default index type
General purpose index.
B-tree indexes are used to avoid large sorting operations. Through an index... Access method Index unique scan Fast full index scan Index range scan Index skip scan Index join Nested loops join Join method Sorted merge join Hash join Cases when they are not being used

Using the not-equal operator
Using LIKE
Using functions
Comparing mismatched data types Index not being used? Full scan vs Index scans ..
...is full scan always bad?

select * from employees
where department_id = 1;

When should I use an index? Indexes .. always convinient? Optimizer hints Optimizer hints can be used with SQL statements to alter execution plans Execution path
ALL_ROWS Access method
INDEX Query transformation
NO_MERGE Join Operations
PARALLEL Divide and conquer Other topics to consider... Partitioning Parallel execution Store intermediate results Materialized views More... :) Partitioning Parallel execution Thanks!!!! 6 - Restructuring the data Some Tips to optimize queries 1 - Verifying Optimizer Statistics 2 - Reviewing the Execution Plan 3 - Restructuring the SQL Statements 4 - Store Intermediate Results 5 - Restructuring the Indexes Tips and tricks SQL tuning Real case with index
Full transcript