Oracle tuning: a tutorial

1. Oracle tuning: a tutorial Saikat Chakraborty

2. Introduction • In this session we will try to learn how to write optimized SQL statements in Oracle 8i • We will also try to learn about some common mistakes and how to avoid them.

3. Agenda • Basic introduction to Oracle optimizer • Tools needed for tuning of oracle statements • How to write better SQL and how not to write.

4. Optimizer Overview • What is optimizer? • Rule based. Obsolete in 1997-still used today!! • Cost based. The modern approach that can give great performance and take away!! Finally perfected in Oracle 8i.

5. Rule based optimizer (RBO) • Fixed set of rules that ranks table access method • The most preferred is scan by rowid • The most costly is full table scan

6. RBO continued…. • Never considered the data volume. • Oracle stopped modifying it from version 7.3 upwards • Is still used in some installations but on is way out as of Oracle 10i • It still comes out when it is least needed!

7. Cost based analyzer (CBO) • The new thing just got better. • Depends heavily on data distribution • New activity for DBA-Analyzing the tables and indexes. • Oracle parameter OPTIMIZER_MODE

8. CBO continued.. • Oracle upgrading this from version 7.3 • Supports new oracle features. • Is really intelligent. • But sometimes you need to override this also

9. Features for CBO • Functional Index • Bitmap Index • For high volume of Data “Hash Join”. • Parallel Query. • Partition pruning.

10. Function-based Index • You can create indexes on functions and expressions that involve one or more columns in the table being indexed. • A function-based index precomputes the value of the function or expression and stores it in the index.

11. Bitmap Index • Used to for indexing data of low cardinality. • Takes less space and can merge with other bitmap index. • DML against bitmap index is slow.

12. Join Methods • Nested Loop • Used when small amount of datasets are joined. Mainly in small transactions like CAC • Sort Merge • Used to join large volume of datasets.

13. Join Methods continued • Hash join • Used to join huge amount of data sets. The only requirement being at least one equi-join. • Very fast algorithm. • Very effective when joining one large and one smaller table. • Takes lots of resource. • Only CBO can use this.

14. Parallel Query • Multiple process can process a DML • Optimal use of multiple CPU • Can be set in Statement, Table/Index , Database level. • High overhead. Should be used only for high volume data.

15. Partitions • Oracle 8 introduced this feature. • Addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces. • Three types of Partitioning as of Oracle 8i • Range • Hash • Composite

16. Partition Pruning • If The CBO finds out that only a known number of partitions are needed for a query, only those partitions are scanned. • Effectively you scan much less data hence get good performance.

17. Tools for optimizing SQL • Explain plan from SQL*Plus. • Toad and other third party tools. • TKPROF.

18. Plans • Shows you the path optimizer is taking. • Shows how your statement is slow. • There are ways to modify the plans. • That’s my job!

19. Method to see plan • Create a plan Table • Run $ORACLE_HOME/rdbms/admin/utlxplan.sql • Create the plan • EXPLAIN PLAN [SET STATEMENT_ID = <string in single quotes>] [INTO <plan table name>]FOR <SQL statement>;

20. Method to see plan… • See the plan • Run the script$ORACLE_HOME/rdbms/admin/utlxpls.sql.

21. How a plan looks like? The query is SELECT a.customer_name, b.invoice_number, b.invoice_date, b.total_amount, c.line_number, c.part_number, c.quantity, c.unit_cost FROM customers a, invoices b, invoice_items c WHERE c.invoice_id = :b1 AND c.line_number = :b2 AND b.invoice_id = c.invoice_id AND a.customer_id = b.customer_id;

22. The plan will be ID PARENT OPERATION OBJECT_NAME ---- ------ ----------------------------------- ------------------------------ 0 SELECT STATEMENT 1 0 NESTED LOOPS 2 1 NESTED LOOPS 3 2 TABLE ACCESS BY INDEX ROWID INVOICE_ITEMS 4 3 INDEX UNIQUE SCAN INVOICE_ITEMS_PK 5 2 TABLE ACCESS BY INDEX ROWID INVOICES 6 5 INDEX UNIQUE SCAN INVOICES_PK 7 1 TABLE ACCESS BY INDEX ROWID CUSTOMERS 8 7 INDEX UNIQUE SCAN CUSTOMERS_PK

23. Tough isn’t it? • One easy way to ask your DBA to setup and grant you the PLUSTRACE role • After that you give the command in SQL*PlusSet autotrace onAnd after all statements it will show you the plan and some statistics for the statement.

24. Third party tools • Toad ,spotlight and other third party tools can also show you the plan.

25. Tkprof • A tool for the DBA • You have to set SQL trace on in a session. • Can be done from other sessions alsoDBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION • From own session the command isALTER SESSION SET SQL_TRACE = true; • Creates a raw file in the user_dump_destination • This can be converted to a text file by the utility TKPROF

26. How to write better SQL • DO • Use bind variables for frequently used SQL • Think of data volumes and use hints if needed • Use parallel queries only when needed • Try to use IN in place of EXISTS • Try to NOT IN !Can not be done in all cases.

27. Hints to the Optimizer • /*+ index(..) */ forces an index used • /*+ full(..) */ does a full table scan. • /*+ use_nl(..) */ forces nested loop join. • /*+ use_hash(..) */ forces hash join • /*+ hash_sj(..) */ converts exists into hash join. • /*+ hash_aj(..) */ converts not in into hash join. • /*+ parallel(..) */ does parallel query.

28. Look out for updates! • Think of this update UPDATE BG_INVOICE A SET A.BILLCYCLENO = ( SELECT B.BILLCYCLE FROM TMP_BILLCYCLE B WHERE A.CONSOLIDATEDINVOICENO = B.BILLNO AND B.FLAG <> 0 ); • Fact: • BG_INVOICE - 1.2Million • TMP_BILLCYCLE 1 Million

29. Now look at the plan Operation Object Name Rows UPDATE STATEMENT Hint=CHOOSE 1 M UPDATE BG_INVOICE TABLE ACCESS FULL BG_INVOICE 1 M TABLE ACCESS FULL TMP_BILLCYCLE 1M • For every record of BG_INVOICE a Full Table Scan of TMP_BILLCYCLE is done!!! • How to make it fast?

30. Look at the update again UPDATE BG_INVOICE A SET A.BILLCYCLENO = ( SELECT B.BILLCYCLE FROM TMP_BILLCYCLE B WHERE A.CONSOLIDATEDINVOICENO = B.BILLNO AND B.FLAG <> 0 ); • Action: • Added an index on table TMP_BILLCYCLE on field BILLNO

31. Now look at the plan again Operation Object Name UPDATE STATEMENT Hint=CHOOSE UPDATE BG-INVOICE TABLE ACCESS FULL BG-INVOICE TABLE ACCESS BY INDEX ROWID TMP-BILLCYCLE INDEX RANGE SCAN IDX1 • The index being in place, now it is fast.

32. How not to write SQL • Heavy use of updates. • If you can, merge them into a single insert • Inserting a (almost!!) blank row in table and then update it. • Unnecessary use of DISTINCT • Unnecessary use of database • If you can do with one SQL do not use two

33. Example of BAD sql INSERT INTO INTF_SAP_RPT_FI_FINAL K VALUES ( add_months(sysdate,-1), null, NULL, NULL, NULL, NULL, 'J', '+'); UPDATE INTF_SAP_RPT_FI_FINAL K SET AMOUNT = (SELECT distinct B.CHARGEAMNT FROM PK_NRC A,AP_ACCOUNTNRC B WHERE A.NONRECURRINGCHARGEID = B.NONRECURRINGCHARGEID AND B.BILLEDFLAG=0 AND B.INSTALLMENTFLAG=0 AND TO_CHAR(B.CHARGEDATE,'MM.YYYY') = TO_char(K.PERIOD,'MM.YYYY')); UPDATE INTF_SAP_RPT_FI_FINAL K SET REVENUEACCOUNTTRANS=(SELECT distinct A.GLCODE FROM PK_NRC A, AP_ACCOUNTNRC B WHERE A.NONRECURRINGCHARGEID = B.NONRECURRINGCHARGEID AND B.BILLEDFLAG=0 AND B.INSTALLMENTFLAG=0 AND TO_CHAR(B.CHARGEDATE,'MM.YYYY') = TO_char(K.PERIOD,'MM.YYYY')); Can you spot the errors on this script?

34. Where to Get More Information • http://otn.oracle.com Oracle Technet • Oracle concept Manual • Oracle tuning Manual.

35. Thank You