Performance Tuning

1. Performance Tuning

2. Database Tuning Options • Hardware Solution (determine bottleneck) • More Memory • Faster or additional Processors • Faster Disk IO (possibly via RAID) • More Network Bandwidth • Software Solution • Database/Instance Tuning(Location of files, block size, db buffer cache size, etc) • Object Tuning (Index creation, table reorganization, etc) • SQL Tuning (application tuning)

3. Sequential Table Access RowID Name Street Zip Dept Salary 1 Conway 1 Main St. 24142 Finc $ 50,000 2 Harrington 12 Elm Ave. 24142 IT $ 45,000 3 Lytle 11 Southern St. 24060 Finc $ 33,000 4 Johnson 12 Elm Ave. 24142 IT $ 81,000 5 Bosworth 3 Main St. 24060 Actg $ 50,000 6 Fulton 18 Oak Rd. 24142 Actg $ 44,000 7 Adams 14 Pine Ct. 24142 Finc $ 50,000 8 Truman 11 Pine Ct. 24142 Finc $ 45,000 9 Waters 18 Main St. 24142 Actg $ 33,000 10 Rhineman 23 Main St. 24142 IT $ 81,000 11 Matthews 31 Elm Ave. 24060 Finc $ 50,000 12 Smith 2 Oak Rd. 24060 Actg $ 44,000 13 Darlington 8 Ridge Rd. 24060 Actg $ 81,000 14 Potter 5 Elm Ave. 24142 IT $ 50,000 15 Varner 1 Elm Ave. 24142 Actg $ 44,000

4. Binary Tree Lytle Darlington Smith Bosworth Harrington Potter Varner Adams Conway Fulton Johnson Matthews Rhineman Truman Waters

5. Worse Case and Average Search Moves Balanced Full Binary Tree Search Sequential Search Tree Depth Nodes Worse Case Average Case Worse Case Average Case 1 1 1 1.00 1 1.00 2 3 2 1.75 3 1.50 3 7 3 2.55 7 3.50 4 15 4 3.38 15 7.50 5 31 5 4.26 31 16.50 6 63 6 5.18 63 31.50 7 127 7 6.11 127 63.50 8 255 8 7.07 255 127.50 9 511 9 8.04 511 255.50 10 1,023 10 9.03 1,023 511.50 11 2,047 11 10.01 2,047 1,023.50 12 4,095 12 11.01 4,095 2,047.50 13 8,191 13 12.01 8,191 4,095.50 14 16,383 14 13.00 16,383 8,191.50 15 32,767 15 14.00 32,767 16,383.50 16 65,535 16 15.00 65,535 32,767.50 17 131,071 17 16.00 131,071 65,535.50 18 262,143 18 17.00 262,143 131,071.50 19 524,287 19 18.00 524,287 262,143.50 20 1,048,575 20 19.00 1,048,575 524,287.50

6. Binary Tree Index BOF 3 1 Conway N N Lytle 2 Harrington 6 4 3 Lytle 13 12 4 Johnson N N Smith Darlington 5 Bosworth 7 1 6 Fulton N N 7 Adams N N Bosworth Varner 8 Truman N N 9 Waters N N Harrington Potter 10 Rhineman N N 11 Matthews N N Adams Conway Truman Waters 12 Smith 14 15 13 Darlington 5 2 Fulton Johnson Matthews Rhineman 14 Potter 11 10 15 Varner 8 9 EOF N

7. Cluster Index RowID Name Street Zip Dept Salary 1 Conway 1 Main St. 24142 Finc $ 50,000 N N 2 Harrington 12 Elm Ave. 24142 IT $ 45,000 6 4 3 Lytle 11 Southern St. 24060 Finc $ 33,000 13 12 4 Johnson 12 Elm Ave. 24142 IT $ 81,000 N N 5 Bosworth 3 Main St. 24060 Actg $ 50,000 7 1 6 Fulton 18 Oak Rd. 24142 Actg $ 44,000 N N 7 Adams 14 Pine Ct. 24142 Finc $ 50,000 N N 8 Truman 11 Pine Ct. 24142 Finc $ 45,000 N N 9 Waters 18 Main St. 24142 Actg $ 33,000 N N 10 Rhineman 23 Main St. 24142 IT $ 81,000 N N 11 Matthews 31 Elm Ave. 24060 Finc $ 50,000 N N 12 Smith 2 Oak Rd. 24060 Actg $ 44,000 14 15 13 Darlington 8 Ridge Rd. 24060 Actg $ 81,000 5 2 14 Potter 5 Elm Ave. 24142 IT $ 50,000 11 10 15 Varner 1 Elm Ave. 24142 Actg $ 44,000 8 9

8. Cluster (not cluster index) Database Page Row from employees table Conway 1 Main St. 24142 Finc $ 50,000 Related Rows from Skills Table Conway COBOL Excellent Conway JAVA Good Conway VB Excellent Conway C # Novice

9. SQL Tuning Goals • Determine Optimal Execution Plan for each SQL statement. • Lock down the execution plan for each statement . (careful here) • Maintain Indexes, Perform Routine Table Analysis, and otherwise maintain instance in a fashion that supports the execution plans. • Locate problematic SQL statements and retune (implies monitoring)

10. SQL Tuning Options • Change SQL syntax (not completely stable, may want hints or outlines also) The structure of an SQL query can effect the execution plan of that query. In oracle this is particularly true when the rule based optimizer is being used. • Add Optimizer hints Provide optimizer hints in the SQL that indicate: - which optimizer mode to use - whether or not to use available indexes - the order in which tables are to be joined - the method by which Oracle should join the tables • Store an Outline in the DB for given SQL statements

11. Oracle Optimizers An optimizer determines the best way to execute the SQL query. Oracle has two optimizers. • The Oracle rules based optimizer follows a strict set of rules when determining how to execute the query. For example, one rule is that all available indexes should be used. • The Oracle cost based optimizer looks at statistics, such as the number of rows in a table or the variance of values in a column, to determine the best way to execute the SQL statement. For example, the cost based optimizer may choose to ignore an index if it sees that the table is very small and will be completely pulled into memory with a single block (page) read anyway.

12. Oracle Optimizer Modes • RULE Uses the rules based optimizer only. • CHOOSE Uses the cost based optimizer if any statistics are available for the query tables. • FIRST_ROWS Uses the cost based optimizer but biases the execution plan toward retrieving the first rows to the user as fast as possible. This is the default for the CHOOSE mode. • ALL_ROWS Uses the cost based optimizer but biases the execution plan toward minimizing the total retrieval time, even if that means a longer delay before the first rows are returned to the user.

13. Analyzing Tables Generally, the cost based optimizer provides better performance. For the cost based optimizer to run statistics on tables must be periodically gathered. This can be done with either: • Analyze table <tablename> compute statistics | for all indexed columns | Or • Analyze table <tablename> estimate statistics | for all indexed columns | Estimating statistics forces Oracle to make estimates based on only a partial examination of tables rows. This saves time when there is a need to gather statistics on very large tables. DBAs try to schedule table analysis during off-peak hours to avoid performance issues.

14. Changing Optimizer Modes For session: Alter session set optimizer_mode = <mode> Alter session set optimizer_mode = RULE For Query: Select /*+ <mode> */first, last from students; Select /*+ FIRST_ROWS */first, last from students; Select /*+ FIRST_ROWS(50) */first, last from students; Select /*+ RULE */first, last from students; Select /*+ CHOOSE */first, last from students; Select /*+ ALL_ROWS */first, last from students;

15. Examining Execution Plans Execution Plans can be captured and examined by using either the Oracle EXPLAIN PLAN command which operates on a single query or by using the Oracle AUTOTRACE feature which turns on plan tracing for all queries. To control autotrace use one of the following: • Set autotrace on (gives plan, performance stats, and query results) • Set autotrace traceonly (gives plan and perf. stats but not query results) • Set autotrace on explain (gives plan and query results) • Set autotrace on statistics (gives performances stats and query results) • Set autotrace off (turns autotrace off) • Set timing on (times each query but is effect by screen delay so repress query results) Neither EXPLAIN PLAN or autotrace will work unless you first create a table in your schema to hold the execution plan steps. To create the plan_table table run \\neelix\dropbox\itec\itec340\_instructorFiles\scripts\utlxplan.sql

16. Sample Query select drivername, nickname, phone from drivers, performance where race='Daytona 500' and drivers.drivername=performance.driver intersect select drivername, nickname, phone from drivers, performance where race='Brickyard 400' and drivers.drivername=performance.driver * Retrieves driver info for those who drove in both races

17. Execution Plan for Sample Query 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 INTERSECTION 2 1 SORT (UNIQUE) 3 2 NESTED LOOPS 4 3 TABLE ACCESS (FULL) OF 'PERFORMANCE' 5 3 TABLE ACCESS (BY INDEX ROWID) OF 'DRIVERS' 6 5 INDEX (UNIQUE SCAN) OF 'DRIVERS_DRIVERNAME_PK' (UN IQUE) 7 1 SORT (UNIQUE) 8 7 NESTED LOOPS 9 8 TABLE ACCESS (FULL) OF 'PERFORMANCE' 10 8 TABLE ACCESS (BY INDEX ROWID) OF 'DRIVERS' 11 10 INDEX (UNIQUE SCAN) OF 'DRIVERS_DRIVERNAME_PK' (UN IQUE) • The second column shows the parent process of any subprocess. Execution order is generally from The HIGHEST number is in the second column to the lowest number with some exceptions such as nested loops. For nested loops the table access order is from top to bottomFULL table access indicates a sequential read of the entire table. A scan indicates the use of an index. Access by ROWID is direct access to a table row after an index has been consulted to obtain the ROWID.

18. HINTS • OPTIMIZER MODE • USE OF INDEX • TYPE OF JOIN and DRIVING TABLE • ORDER OF JOINS General Syntax is: <COMMAND>/*+ HINT */

19. INDEX DECISION Should the Index Be used? - what is table size - what is column(s) selectivity (if you are selecting >20% don’t bother with the index) Is the optimizer making the correct decision?

20. EXAMPLE HINT (index use) Select /*+ FULL(hollywood.movies)*/ title, review from hollywood.movies where rating='PG' -- hints to not use an index Select /*+ INDEX(hollywood.movies) */ title, review from hollywood.movies where rating='NC-17' -- hints to use an index of Oracle's choice Select /*+ INDEX(hollywood.movies movies_rating)*/ title, review from hollywood.movies where rating='NC-17' -- hints to use a specific index

21. JOIN TYPE and DRIVING TABLE Which Join? - Large tables are best joined with Nested Loops - Nested Loops return first rows fastest - A Sort Merge gives the fasted total time for small tables - A Hash Merge on Small to Medium tables will cause the least I/O but be more processor intensive Driving Table for Nested Loops - Larger table or table with least selectivity should be Driving Table - Avoid Full Scans on Inner Table (if only one table is indexed make it the inner table) Is the optimizer making the correct decision?

22. EXAMPLE HINT (join type) Select * /*+ USE_NL(movie_genres)*/ from movies, movie_genres where genre='Comedy' and movies.title = movie_genres.movie; Select * /*+ USE_MERGE*/ from movies, movie_genres where genre='Comedy' and movies.title = movie_genres.movie; Select * /*+ USE_HASH(movie_genres, movies) */ from movies, movie_genres where genre='Comedy' and movies.title = movie_genres.movie;

23. JOIN ORDER Small tables or Tables with High Selectivity should be joined before large tables or tables with low selectivity. Under RULE optimization tables are joined left to right as they appear in the FROM clause.

24. Sample Query Performance Stats Statistics ---------------------------------------------------------- 0 recursive calls 8 db block gets 18 consistent gets 2 physical reads 0 redo size 319 bytes sent via SQL*Net to client 313 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 0 rows processed

25. Exercise 1 Request: A list of all movie titles in alphabetic order by title Query 1 Select title from movies order by title; Examine the execution plan for the above query. What is interesting about this query of the MOVIES table?

26. Exercise 2 Request: Title and Rating of every movie that Tom Hanks has starred in. Query 1 Select title, rating from movies where title in (select movie from starred_in where actor='Tom Hanks'); Query 2 Select title, rating from movies, starred_in where movies.title = starred_in.movie and actor='Tom Hanks'; Which query is more efficient? Can you tune the other?

27. Exercise 3 Request: A list of Actors that are also Directors Query 1 Select name from actors intersect Select name from directors; Query 2 Select actors.name from actors, directors where actors.name = directors.name; Which query is more efficient? Which requires less DISK I/O? Which do you think requires less Memory? Which has the fastest clock time?

28. Exercise 4 Request: A non-repeating list of the genres of movies that Tom Hanks has starred in. select distinct genre from movie_genres, movies, starred_in where movie_genres.movie = movies.title and starred_in.movie = movies.title and actor='Tom Hanks'; Tune the above query. Hint: I had success with hints, indexing, and restructuring

29. Exercise 5 Query: Select * from movies where upper(title) = 'ALIENS'; Examine the execution plan for this query. What is wrong? How can it be fixed/tuned?

30. Exercise 6 (OR vs UNION part 1) Create a schema on your server named REC Connect to that account @\\neelix\oracle\scripts\utlxplan -- get a plan table @\\neelix\oracle\scripts\admin\random -- get random package @\\neelix\oracle\scripts\admin\makesales – make tables @\\neelix\oracle\scripts\admin\populatesales -- takes a minute Query 1: Select * from salesorders where sonum=15000 or sonum=22000; Query 2: Select * from salesorders where sonum=15000 UNION Select * from salesorders where sonum=22000; Which query is more efficient?

31. Exercise 7 (OR vs UNION part 2) ALTER TABLE salesorders ADD CONSTRAINT salesorders_sonum_pk PRIMARY KEY(sonum); ANALYZE TABLE salesorders COMPUTE STATISTICS FOR ALL INDEXED COLUMNS; Query 1: Select * from salesorders where sonum=15000 or sonum=22000; Query 2: Select * from salesorders where sonum=15000 UNION Select * from salesorders where sonum=22000; Which query is more efficient?

32. Exercise 8 (OR vs UNION part 3) CREATE INDEX salesorders_custid ON salesorders(custid); ANALYZE TABLE salesorders COMPUTE STATISTICS FOR ALL INDEXED COLUMNS; Query 1: Select * from salesorders where sonum=15000 or custid=321; Query 2: Select * from salesorders where sonum=15000 UNION Select * from salesorders where custid=321; Which query is more efficient?