Transcript SQL Tuning

http://www.peerlabs.com
Oracle SQL Tuning
Presented By
Akin S Walter-Johnson Ms
Principal PeerLabs, Inc
Email [email protected]
SCOPE
• How data is accessed and reconstituted
joins
• Inform the user on how identify problems
with SQL
• Repair of SQL
• Tuning can occur at 2 levels
– Server
– SQL level
( DBA)
( User)
IMPORTANCE OF TUNING
• Reduce response time for SQL processing
• To find a more efficient way to process
workload
• Improve search time by using indexes
• Join data efficiently between 2 or more
tables
HOW TO TUNE
• Review the access path, Join methods and
index usage
• Test response through SQPLUS directly
( May mask performance )
• Test response through an Application front end
( Usually takes longer )
• Test response through a web interface
ROLE OF HARDWARE & DESIGN
• All the hardware in world will not save you
• Memory, Disk & CPU speed can improve
performance
• Increased hardware does not always result into
better performance
• Poor application design accounts for over 70%
of performance issues
• Do Performance design review early in
development
OVERVIEW OF SQL
PROCESSING
OVERVIEW OF SQL
PROCESSING
• The Parser checks both syntax and semantic analysis of
SQL statement
• Optimizer determines the most efficient way of producing
the result of the query also known as the EXPLAIN
PLAN. How best to get the data.
• Oracle Optimizer types ( Cost Based and Rule Based )
– CBO based Optimizer uses cost associated with each execution
requires you to analyze objects for statistics
– RULE based Optimizer internal rules ( not encouraged by oracle)
• The SQL Execution Engine operates on the execution
plan associated with a SQL statement and then
produces the results of the query.
SETTING OPTIMIZER
• SERVER Level by DBA in parameter file (init.ora)
• CLIENT Level SQLPLUS command < alter session set
optimizer_mode=choose>
• STATEMENT Level using hints
– a. select /*+RULE */ * from dual ;
– b. select /*+ CHOOSE */ * from dual ;
•
•
•
•
Order of Precedence
SERVER->CLIENT->STATEMENT
Users can set both client and statement
To use CBO you need to analyze the tables (see Analyze
objects)
OPTIMIZER OPERATIONS THAT AFFECT
PERFORMANCE
•
•
•
•
•
•
•
•
•
•
•
•
•
•
The Optimizer is the brain behind the process of returning data to user it
needs to make the following choices.
OPTIMIZER APPROACH
ACCESS PATH
JOIN ORDER
JOIN METHOD
Choice of optimizer approaches
CBO or RULE
Choice of Access Paths ( How data is Scanned )
Use an index if not reading all records ( faster)
Read or scan all records
Choice of Join Orders
Determine which table to join first when you have more than two tables in
an SQL
Choice of Join Methods
Determine how to join the tables ( Merge, Sort, Hash )
SQLPLUS ENVIRONMENT LAB
• Log on
• Set timing
• Auto Trace to see plan ( How SQL is
processed )
• Set optimizer
• Review Plan
ANALYZE OBJECT STATISTICS
•
•
•
•
•
•
Statistics describe physical attributes of an object such as
– Number of rows, average space, empty blocks
All objects need to have statistics to use CBO
Stored in user_tables and user_indexes
Not update automatically use analyze
Table Statistics
– Table Name
– Number of rows
– Average space
– Total number of blocks
– Empty blocks
Index Statistics
– Index_Name
– Index_Type
– Table_Name
– Distinct_Keys
– Avg_Leaf_Blocks_Per_Key
– Avg_Data_Blocks_Per_Key
ANALYZE OBJECT STATISTICS
LAB
•
•
•
•
Create Table
Create Index
Review tables
Review indexes
TABLE TUNING (i)
•
•
•
•
•
•
•
•
A Table in oracle store data
Resides in a schema within a Table-space
Contains actual data stored in oracle blocks
An oracle block is a multiple of the OS block (Ask your DBA)
Row Chaining (Performance killer)
– A row is too large to fit into on data block so oracle uses more than one chaining
them
– Chaining occurs when you try to inset or update
Row migration (Performance killer)
– There is not enough place in the BLOCK for UPDATES
– Oracle tries to find another Block with enough free space to hold the entire row.(
Unnecessary scanning)
– If a free block is available Oracle moves the entire ROW to the NEW BLOCK.
– Oracle keeps the original Row piece of a Migrated row row to POINT to the NEW
BLOCK
Queries that select from chained or migrated rows must perform double read and
write (I/O.
To find Chained or Migrated table run
– SQL> ANALYZE TABLE SCHEMA_NAME.TABLE_NAME LIST CHAINED
ROWS;
– SQL> select CHAIN_CNT from user_tables ;
TABLE TUNING (ii)
• Too many empty blocks
• Occurs after a massive delete then
inserting few records
• Select statement takes a very long time
with only one record in table
• Solution is to TRUNCATE the table and
copy to new table
TABLE TUNNING LAB
WHY USE AN INDEX
• What is an Index
– A pointer or a hand that directs to something
– Similar to index at the end of a book
• Oracle Index
– Binary tree Structure with entries know as ROWID
– Left nodes contain key and rowid
– ROWID is internal and points to direct location of
record on disk
– ROWID is fasted way to reach a record.
– SQL> Select rowid, id, name from mytable ;
OPTIMIZER ACCESS by ROWID
• ROWID SCAN
– The fastest way to get a row
– Based on the file and the data block where
record is located
– Used also during an index scan
OPTIMIZER ACCESS by INDEX
UNIQUE SCAN
•
•
•
•
The scan returns only one row
It requires an index (Primary key)on the Table
Index is automatically created for primary key
Used by Optimizer
– When an index exist on a column with a where clause
– When the optimizer is told to use an index (hint) Index
hints are not really used.
• Reading Explain Plan
– Do a unique scan of the index and obtain ROWID
– Access the table by ROWID
OPTIMIZER ACCESS by INDEX
RANGE SCAN
• The scan may return more than one row
• Used by optimizer when
– where clause has > or < sign
– where clause has between 10 and 20
– where clause has like * ( wild card)
OPTIMIZER ACCESS by
MULTIPLE UNIQUE SCAN
• Optimizer will search for ROWID in the
statement
• Concatenate all records into one row set
– Combining all rows selected by the unique
scan into I row set
• Used by Optimizer when
– where clause has an in condition id IN ( 123,
456, 678 )
OPTIMIZER ACCESS by
MULTIPLE UNIQUE SCAN
• Multiple Unique Scan
Index unique scan 123
Table access by
rowid
Index unique scan 456
Table access by
rowid
Index unique scan 456
Table access by
rowid
Concatenation
OPTIMIZER ACCESS by FULL
TABLE SCAN
• Each record is read one by one
• A sequential search for data no index is
used
• The slowest search
• Occurs when
– There is no index or index disabled
– When the Optimizer is hinted not to use the
index
OPTIMIZER ACCESS by FAST
FULL INDEX SCAN
• Alternative to a full table scan
• Used by optimizer when
– Index contains all the columns that are
needed for the query
– If I want to display only your SSN, you don’t
have to access the table if I have SSN as an
index
• A fast full scan accesses the data in the
index itself, without accessing the table
OPTIMIZER JOIN METHOD
• A query with more than one table requires
to have a Join Order
• Join Order are steps taken to assemble
rows of data from more than one table
• Select From A,B,C Where A.col1 = B.Col1
And B.Col2 = C.Col2
• NESTED LOOP
• SORT-MERGE
• HASH JOIN
OPTIMIZER JOIN METHOD
NESTED LOOP
• Nested Loop
• Uses a Looping method to join 2 table
• For every record in A we look thru all rows
in B using an index to find a match
• Table A is Outer Loop or Driving table
• Table B is Inner Loop
Nested Loop
Table A (OuterLoop or Driving
Table B ( Inner Loop )
Loop Thru
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 INDEX (UNIQUE SCAN) OF 'PK_DEPTNO' (UNIQUE)
Nested Loop
• Good when you expect a small number of rows back
• Good for Small driving table so not Good if driving table
is large
• Good when Index on B exist ( will perform poor when no
index on B )
• Good if you want to quickly returns data to the screen (
ONLINE USERS )
• HINT select
• SELECT /*+ ORDERED USE_NL(DEPT) to get first row
faster */
• EMPNO, ENAME, DEPT.DEPTNO
• FROM EMP, DEPT
• WHERE EMP.DEPTNO = DEPT.DEPTNO ;
OPTIMIZER JOIN METHOD SORT
MERGE JOIN
• In a merge join, there is no concept of a
driving table
• The join consists of two steps:
• Sort join operation: Both the inputs are
sorted on the join key.
• Merge join operation: The sorted lists are
merged together.
OPTIMIZER JOIN METHOD SORT
MERGE JOIN
• Merge Join
FULL TABLE Scan
Table A
SORT
Table A
MERGE
A&B
FULL TABLE Scan
Table B
SORT
Table A
SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'DEPT'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'EMP'
OPTIMIZER JOIN METHOD SORT
MERGE JOIN
• The Merge can’t begin until data sorted from both tables
• Since there is a waiting period, this join method will not
be good for ONLINE users
• Good when you don’t have an index on the join columns,
if Index exist a NESTED LOOP is done
• Good when NESTED LOOP does not perform when
• Good if rows are loaded in a sorted fashion
• Not Good if you want to quickly return data to the screen
( ONLINE USERS ) need to wait for sorting.
• Not Good is one of the tables is very,very large because
a Full table scan will be done.
• Good if working with a oracle parallel options because
SORTING can be done in parallel
OPTIMIZER JOIN METHOD SORT
MERGE JOIN
• Optimizer will use SORT JOIN when index
does not exist (May be a warning)
• Optimizer will use SORT JOIN when
OPTIMIZER_MODE is Rule
• Optimizer will use SORT JOIN when
HASH_JOIN_ENABLED is false.
• HINT
• SELECT /*+ USE_NL(l h) */
OPTIMIZER JOIN METHOD HASH
JOIN
• HASH join compares tow tables in memory to
find matching rows
• Must set HASH_JOIN_ENABLED to True(DBA )
• Read first table into memory via Full table scan
• Apply hashing function to data to prepare for join
on key fields
• Read second table via Full table scan
• Apply hashing function to compare the second to
the first table
OPTIMIZER JOIN METHOD HASH
JOIN
• HASH JOIN
FULL TABLE Scan
Table A
HASH JOIN
A&B
FULL TABLE Scan
Table B
0
1
2
3
SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=
0 HASH JOIN (Cost=5 Card=82 Bytes=3772)
1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=2706)
1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1066)
OPTIMIZER JOIN METHOD HASH
JOIN
• Good only when you have parallel options for
oracle because of FTS
• Good if you have more memory set aside for
hashing functions
• Good if you indexes don't perform well with
NESTED LOOP
• May be faster than NESTED LOOP because you
are reading in memory as supposed to using
index
• Better than SORT MERGE because only on
table has to be sorted
OPTIMIZER JOIN METHOD HASH
JOIN
•
•
•
•
•
HINT
SELECT /*+use_hash(emp, dept )*/
EMPNO, ENAME, DEPT.DEPTNO
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
;
OPTIMIZER JOIN ORDER
• Avoid performing unnecessary work to
access rows that do not affect the result.
• Choose the best join order, driving to the
best unused filters earliest.
OPTIMIZER JOIN ORDER
•
•
•
•
•
•
•
•
•
Query 1
SELECT info
FROM taba a, tabb b, tabc c
WHERE
a.key1 = b.key1
AND a.key2 = c.key2
AND a.acol BETWEEN 100 AND 200
AND b.bcol BETWEEN 10000 AND 20000
AND c.ccol BETWEEN 10000 AND 20000
OPTIMIZER JOIN ORDER
•
•
•
•
•
•
•
•
•
Query 2
SELECT info
FROM taba a, tabb b, tabc c
WHERE
a.acol BETWEEN 100 AND 200
AND b.bcol BETWEEN 10000 AND 20000
AND c.ccol BETWEEN 10000 AND 20000
AND a.key1 = b.key1
AND a.key2 = c.key2;
OPTIMIZER JOIN ORDER
•
•
•
•
•
•
•
•
•
Query3
SELECT info
FROM taba a, tabb b, tabc c
WHERE
b.bcol BETWEEN 10000 AND 20000
AND c.ccol BETWEEN 10000 AND 20000
AND a.acol BETWEEN 100 AND 200
AND a.key1 = b.key1
AND a.key2 = c.key2;
OPTIMIZER JOIN ORDER
• The work of the following join can be
reduced by first joining to the table with the
best still-unused filter.
• Thus, if "bcol BETWEEN ..." is more
restrictive (rejects a higher percentage of
the rows seen) than "ccol BETWEEN ...",
the last join can be made easier (with
fewer rows) if tabb is joined before tabc.
OPTIMIZER JOIN ORDER
• The driving table is the one containing the filter
condition that eliminates the highest percentage of
the table.
• Thus, because the range of 100 to 200 is narrow
compared with the range of acol,
• but the ranges of 10000 and 20000 are relatively
large, taba is the driving table, all else being equal.
• HINT
• The ORDERED hint causes Oracle to join tables in
the order in which they appear in the FROM clause.
INDEX TUNING
•
•
•
•
•
Rebuild you index often ( index oil change)
Gather statistics
Do not over use Indexes
Restrict to cols that return a few records
Use Bitmapped index when number of
values is small e.g ( Sex male, female)
• Suppression of index
• Select * from mytable where total + 3 = 20
REPAIR YOUR SQL STATEMENT
LAB
• Understand the purpose of a statement rewriting may improve performance.
• Use equi joins on where clause
• Avoid column transformation
–
–
–
–
–
–
Where to_number(a.id) = b.id
Do not use function in predicate
Where to_string(a.id) = b.id
col1 = NVL (:b1,col1)
NVL (col1,-999) = ....
TO_DATE(), TO_NUMBER(), and so on
REPAIR YOUR SQL STATEMENT
LAB
• WRITE SEPARATE SQL STATEMENTS
FOR SPECIFIC TASKS
• It is better to use IN rather than EXISTS. ,
if the selective predicate is in the
subquery, then use IN. If the selective
predicate is in the parent query, then use
EXISTS.
SQL TUNING CHECK LIST
• Ask DBA if Cost based optimizer is default in
init.ora
• Check if you have statistics for tables and
indexes
• Check if you have a high number of empty
blocks on tables(due to large deletes)
• Check if you have row chaining or row migration
on Tables
• Check index cluster
• Review SQL plan
• Use more packages and stored procedures