1 / 67

Module 4 Database SQL Tuning

Module 4 Database SQL Tuning. Section 3 Application Performance. Explain Plan Basics. The Explain Plan Utility and the Execution Plan Why Execution Plans Are Important Execution Plan Format Sample Execution Tree. The Explain Plan Utility and the Execution Plan. SQL Statement.

buffy-strong
Download Presentation

Module 4 Database SQL Tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 4 Database SQL Tuning Section 3 Application Performance

  2. Explain Plan Basics • The Explain Plan Utility and the Execution Plan • Why Execution Plans Are Important • Execution Plan Format • Sample Execution Tree

  3. The Explain Plan Utility and the Execution Plan SQL Statement Explain Plan Utility Other Tools: OEM SQL Studio Precise SQL TRACE TKPROF, etc.. plan_table Explain Report Execution Plan

  4. Why is the Explain Plan Utility Important? • An execution plan shows how Oracle is going to process a statement. • This helps tune statements = Better Performance • Builds SQL tuning skills = $$$ • Builds Application tuning skills = $$$ • When used by developers early in the development cycle, can uncover data model, index, and performance issues before its too late!

  5. Explain Report SQL SELECT LPAD(' ',2*(LEVEL-1))||operation “OPERATION” ,options “OPTIONS” ,DECODE(TO_CHAR(id),'0','COST = ' || NVL(TO_CHAR(position),'n/a'), object_name)“OBJECT NAME” ,id ||'-'|| NVL(parent_id, 0)||'-'|| NVL(position, 0) “ORDER” ,SUBSTR(optimizer,1,6) “OPT” FROM plan_table START WITH id = 0 AND statement_id = 'X' CONNECT BY PRIOR id = parent_id AND statement_id = 'X';

  6. Execution Plan Example SELECT cust_no ,cust_address ,cust_last_name ,cust_first_name FROM customer WHERE cust_phone = '3035551234'; OPERATION OPTIONS OBJECT NAME ORDER OPT ----------------------- ------------ ----------------- ------- ----- SELECT STATEMENT COST = n/a 0-0-0 RULE TABLE ACCESS BY ROWID CUSTOMER 1-0-1 INDEX RANGE SCAN IX_CUST_PHONE 2-1-1

  7. Execution Tree Example 1 TABLE ACCESS BY ROWID ON CUSTOMER 2 INDEX RANGE SCAN ON IX_CUST_PHONE

  8. OPERATIONS • ROW OPERATION • SET OPERATION

  9. ROW OPERATION • EXECUTED ON ONE ROW AT A TIME. • THE USER CAN SEE THE FIRST RESULT BEFORE THE LAST ROW IS FETCHED.

  10. SET OPERATION • EXECUTED ON A RESULT SET OF ROWS. • THE USER CANNOT SEE THE FIRST RESULT UNTIL ALL ROWS ARE FETCHED AND PROCESSED.

  11. SQL OPERATIONS • AND-EQUAL: ROW OPERATION • CONCATENATION: ROW OPERATION • CONNECT BY: ROW OPERATION • COUNT: ROW OPERATION • COUNT STOPKEY: ROW OPERATION • FILTER: ROW OPERATION • FOR UPDATE: SET OPERATION • INDEX RANGE SCAN: ROW OPERATION

  12. SQL OPERATIONS • INDEX UNIQUE SCAN: ROW OPERATION • INTERSECTION: SET OPERATION • MERGE JOIN: SET OPERATION • MINUS: SET OPERATION • NESTED LOOPS: ROW OPERATION • OUTER JOIN: SET OPERATION OR ROW OPERATION • PROJECTION: ROW OPERATION • REMOTE: ROW OPERATION OR SET OPERATION • VIEW: SET OPERATION

  13. SQL OPERATIONS • SEQUENCE: ROW OPERATION • SORT AGGREGATE: SET OPERATION • SORT GROUP BY: SET OPERATION • SORT JOIN: SET OPERATION • SORT ORDER BY: SET OPERATION • SORT UNIQUE: SET OPERATION • TABLE ACCESS BY ROWID: ROW OPERATION • TABLE ACCESS FULL: ROW OPERATION • UNION: SET OPERATION

  14. ORACLE OPTIMIZER MODES RULE-BASED COST-BASED

  15. ORACLE HINTS (Some of them) • RULE • ALL_ROWS • FIRST_ROWS • AND-EQUAL • FULL • HASH • INDEX • INDEX_ASC • INDEX_DESC

  16. ORACLE HINTS Continued • NO_MERGE • ORDERED • ROWID • USE_HASH • USE_NL • USE_MERGE • USE_STAR

  17. RULE Rule hint tells the optimizer to use the rule-based optimization for the query. All other hints within the query will be ignored.

  18. All_ROWS All_Rows hint optimizes the query for the best throughput- that is, to minimize the time it takes for all rows returned by the query.

  19. FIRST_ROWS First_Rows hint optimizes the query for the shortest response time to return the first row from the query.

  20. AND-EQUAL And-Equal hint tells the optimizer to perform an And-Equal operation on the indexes listed within the hint.

  21. FULL Full hint will perform a Full table scan. This hint might be used, if one knows the index scan used by the query would be a poor choice given the data distribution.

  22. INDEX • If a single index is used in the hint, that index will be used. • If multiple indexes are used in the hint, the optimizer will choose which indexes to use. • If only table name is used in the hint, then the optimizer will choose an index or indexes to use for that table.

  23. INDEX-ASC This hint is same as the Index hint.

  24. INDEX_DESC INDEX_DESC hint tells the optimizer to scan an index in the descending order of the indexed values.

  25. NO_MERGE This hint is available as of Oracle 7.3, which tells the optimizer not to merge a view’s SQL syntax with the syntax of a query that uses the view.

  26. ORDERED This hint will influence the order in which the tables are joined.

  27. USE_NL This hint tells the optimizer to perform a nested loop join, using the specified table as the driving table in the join.

  28. USE-MERGE This hint tells the optimizer to use a merge join between specified tables.

  29. USE_STAR This hint is available as of Oracle 7.3, tells the optimizer to use a composite key/star query execution path when resolving a join. These hints are typically used in data warehousing applications, which have a master table with small detail tables.

  30. Access Path and Operation Examples • Data Model for Examples • Access Paths: • Table Access Full • Table Access By ROWID • Index Unique Scan • Index Range Scan • Operations: • And-Equal • Nested Loops

  31. Data Model For Examples CUSTOMER *cust_no cust_last_name cust_first_name cust_phone cust_address cust_zip ORDER_HDR *order_no order_status order_create_dt order_desc order_cust_no order_amt ux_customer ux_order_hdr ix_cust_phone ix_order_status ix_cust_zip ix_order_cust_no

  32. Access Path:Table Access Full SELECT cust_no ,cust_last_name ,cust_first_name ,cust_address FROM customer;

  33. Access Path:Table Access Full SELECT cust_no ,cust_last_name ,cust_first_name ,cust_address FROM customer; OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE TABLE ACCESS FULL CUSTOMER 1-0-1

  34. Access Path:Table Access Full 1 TABLE ACCESS FULL ON CUSTOMER SELECT cust_no ,cust_last_name ,cust_first_name ,cust_address FROM customer; OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE TABLE ACCESS FULL CUSTOMER 1-0-1

  35. Access Path:Table Access By ROWID SELECT cust_no ,cust_last_name ,cust_first_name ,cust_address FROM customer WHERE ROWID = CHARTOROWID( :host );

  36. Access Path:Table Access By ROWID SELECT cust_no ,cust_last_name ,cust_first_name ,cust_address FROM customer WHERE ROWID = CHARTOROWID( :host ); OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE TABLE ACCESS BY ROWID CUSTOMER 1-0-1

  37. Access Path:Table Access By ROWID SELECT cust_no ,cust_last_name ,cust_first_name ,cust_address FROM customer WHERE ROWID = CHARTOROWID( :host ); OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE TABLE ACCESS BY ROWID CUSTOMER 1-0-1 1 TABLE ACCESS BY ROWID ON CUSTOMER

  38. Access Path:Index Unique Scan SELECT cust_last_name ,cust_first_name ,cust_address FROM customer WHERE cust_no = :host;

  39. Access Path:Index Unique Scan SELECT cust_last_name ,cust_first_name ,cust_address FROM customer WHERE cust_no = :host; OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE TABLE ACCESS BY ROWID CUSTOMER 1-0-1 INDEX UNIQUE SCAN UX_CUSTOMER 2-1-1

  40. Access Path:Index Unique Scan 1 TABLE ACCESS BY ROWID ON CUSTOMER SELECT cust_last_name ,cust_first_name ,cust_address FROM customer WHERE cust_no = :host; OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE TABLE ACCESS BY ROWID CUSTOMER 1-0-1 INDEX UNIQUE SCAN UX_CUSTOMER 2-1-1 2 INDEX UNIQUE SCAN ON UX_CUSTOMER

  41. Access Path:Index Range Scan SELECT cust_last_name ,cust_first_name ,cust_address FROM customer WHERE cust_phone = '3035551234';

  42. Access Path:Index Range Scan SELECT cust_last_name ,cust_first_name ,cust_address FROM customer WHERE cust_phone = '3035551234'; OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE TABLE ACCESS BY ROWID CUSTOMER 1-0-1 INDEX RANGE SCAN IX_CUST_PHONE 2-1-1

  43. Access Path:Index Range Scan 1 TABLE ACCESS BY ROWID ON CUSTOMER SELECT cust_last_name ,cust_first_name ,cust_address FROM customer WHERE cust_phone = '3035551234'; OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE TABLE ACCESS BY ROWID CUSTOMER 1-0-1 INDEX RANGE SCAN IX_CUST_PHONE 2-1-1 2 INDEX RANGE SCAN ON IX_CUST_PHONE

  44. And-Equal Operation SELECT cust_no ,cust_last_name ,cust_first_name ,cust_address FROM customer WHERE cust_phone = :host1 AND cust_zip = :host2;

  45. And-Equal Operation SELECT cust_no ,cust_last_name ,cust_first_name ,cust_address FROM customer WHERE cust_phone = :host1 AND cust_zip = :host2; OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE TABLE ACCESS BY ROWID CUSTOMER 1-0-1 AND-EQUAL 2-1-1 INDEX RANGE SCAN IX_CUST_PHONE 3-2-1 INDEX RANGE SCAN IX_CUST_ZIP 4-2-2

  46. And-Equal Operation 1 TABLE ACCESS BY ROWID ON CUSTOMER SELECT cust_no ,cust_last_name ,cust_first_name ,cust_address FROM customer WHERE cust_phone = :host1 AND cust_zip = :host2; OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE TABLE ACCESS BY ROWID CUSTOMER 1-0-1 AND-EQUAL 2-1-1 INDEX RANGE SCAN IX_CUST_PHONE 3-2-1 INDEX RANGE SCAN IX_CUST_ZIP 4-2-2 2 AND-EQUAL 3 INDEX RANGE SCAN ON IX_CUST_PHONE 4 INDEX RANGE SCAN ON IX_CUST_ZIP

  47. Nested Loop Operation SELECT a.cust_last_name ,a.cust_first_name ,a.cust_address ,b.order_desc ,b.order_create_dt FROM customer a ,order_hdr b WHERE cust_phone = :host1 AND b.order_cust_no = a.cust_no AND b.order_status = 'OPEN';

  48. Nested Loop Operation SELECT a.cust_last_name ,a.cust_first_name ,a.cust_address ,b.order_desc ,b.order_create_dt FROM customer a ,order_hdr b WHERE cust_phone = :host1 AND b.order_cust_no = a.cust_no AND b.order_status = 'OPEN'; OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE NESTED LOOPS 1-0-1 TABLE ACCESS BY ROWID ORDER_HDR 2-1-1 INDEX RANGE SCAN IX_ORDER_STATUS 3-2-1 TABLE ACCESS BY ROWID CUSTOMER 4-1-2 INDEX UNIQUE SCAN UX_CUSTOMER 5-4-1

  49. Nested Loop Operation 1 NESTED LOOPS SELECT a.cust_last_name ,a.cust_first_name ,a.cust_address ,b.order_desc ,b.order_create_dt FROM customer a ,order_hdr b WHERE cust_phone = :host1 AND b.order_cust_no = a.cust_no AND b.order_status = 'OPEN'; OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------ ------------ -------------------- ------- ------ SELECT STATEMENT COST = n/a 0-0-0 RULE NESTED LOOPS 1-0-1 TABLE ACCESS BY ROWID ORDER_HDR 2-1-1 INDEX RANGE SCAN IX_ORDER_STATUS 3-2-1 TABLE ACCESS BY ROWID CUSTOMER 4-1-2 INDEX UNIQUE SCAN UX_CUSTOMER 5-4-1 2 TABLE ACCESS BY ROWID ON ORDER_HDR 4 TABLE ACCESS BY ROWID ON CUSTOMER 3 INDEX RANGE SCAN ON IX_ORDER_STATUS 5 INDEX UNIQUE SCAN ON UX_CUSTOMER

  50. Tuning Examples • Index Suppression • Table Order in FROM Clause

More Related