Oracle SQL Tuning

1. http://www.peerlabs.com Oracle SQL Tuning Presented By Akin S Walter-Johnson Ms Principal PeerLabs, Inc Email akin@peerlabs.com

2. 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 ( DBA) • SQL level ( User)

3. 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

4. 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

5. 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

6. OVERVIEW OF SQL PROCESSING

7. 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.

8. 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)

9. 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 )

10. SQLPLUS ENVIRONMENT LAB • Log on • Set timing • Auto Trace to see plan ( How SQL is processed ) • Set optimizer • Review Plan

11. 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

12. ANALYZE OBJECT STATISTICS LAB • Create Table • Create Index • Review tables • Review indexes

13. 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 ;

14. 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

15. TABLE TUNNING LAB

16. 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 ;

17. 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

18. 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

19. 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)

20. 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 )

21. OPTIMIZER ACCESS by MULTIPLE UNIQUE SCAN • Multiple Unique Scan

22. 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

23. 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

24. 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

25. 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

26. Nested Loop

27. 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 ;

28. 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.

29. OPTIMIZER JOIN METHOD SORT MERGE JOIN • Merge Join

30. 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

31. 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) */

32. 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

33. OPTIMIZER JOIN METHOD HASH JOIN • HASH JOIN

34. 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

35. OPTIMIZER JOIN METHOD HASH JOIN • HINT • SELECT /*+use_hash(emp, dept )*/ • EMPNO, ENAME, DEPT.DEPTNO • FROM EMP, DEPT • WHERE EMP.DEPTNO = DEPT.DEPTNO ;

36. 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.

37. 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

38. 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;

39. 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;

40. 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.

41. 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.

42. 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

43. REPAIR YOUR SQL STATEMENT LAB • Understand the purpose of a statement re-writing 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

44. 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.

45. 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