Send the link below via email or IMCopy
Present to your audienceStart 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.
Make your likes visible on Facebook?
You can change this under Settings & Account at any time.
Transcript of SQL tuning
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
Number of distinct values (NDV) in column
Number of nulls in column
Data distribution (histogram)
Number of leaf blocks
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 NULL, IS NOT NULL
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