Database Tuning in – Philip K. Kuruvilla

Database Tuning in - Philip K. Kuruvilla

Login Database Tuning in 2

The World Wide Wait Database Tuning in 3

Looks Familiar ? Database Tuning in 4

Agenda Why tune databases Scope for tuning Parameters Design Monitor and Analyze Trivia Database Tuning in 5

Why is it so critical? Web Server Database The Database lies at the root of an e-commerce application ! Database Tuning in 6

Scope for Tuning System level Operating System, Hardware Database level Temporary space, Rollback segments Application level Query Tuning, Design Database Tuning in 7

Tuning Parameters Throughput and Response Time Throughput: Ratio of total data read to total time spent in servicing the request Response Time: Difference between time at which a request is completed and response is received Type of Application OLTP (requires better throughput) Batch processing (requires shorter response time) Database Tuning in 8

Design Normalization Not always a good idea to Normalize Better to have a single table here Banner ID Course Banner ID Professor B0000001 ECMM6000 B0000001 G. Sullivan B0000002 ECMM6010 B0000002 T. Trappenberg B0000003 ECMM6020 B0000003 S. Marche B0000004 ECMM6030 B0000004 A. Uteck Database Tuning in 9

Application Design Simple and efficient queries Minimise the number of query blocks SELECT * FROM ALL STUDENTS A WHERE A.NAME IN (SELECT GS.NAME FROM GRAD STUDENTS GS) SELECT * FROM ALL STUDENTS A, GRAD STUDENTS GS WHERE A.NAME GS.NAME Return efficient batch sizes Use Stored Procedures/ Functions/ Triggers Startup costs are high. Running costs are low Database Tuning in 10

Indexes What are they? Defined on a column Avoid full table scans How do they work When do they NOT work ! Trade off between insert/update and select Primary keys are automatically indexed Banner ID Row ID B001 1 B002 6 B003 3 B005 2 B008 5 B010 4 rowID (hidden ) Banner ID Name Age Tel 1 B001 Chris 20 23232 2 B005 Bates 21 23232 3 B003 Hugh 29 23902 4 B010 Mark 28 23902 5 B008 Martin 33 23920 6 B002 Jones 22 32902 Database Tuning in 11

Monitoring Plan table Shows how an SQL query is executed Shows use of indexes, effect of Optimizers Gives a “cost factor” for a query Database Tuning in 12

Explain Plan table PRODUCT OR DER CUSTOMER ORDER DETAI L CUST ID ORDER ID ORDER ID FNAME CUST ID PRODUCT ID LNAME TOTAL QTY SELECT POD.PROD ID, POD.QTY FROM CUSTOMER C, PRODUCT ORDER PO, PRODUCT ORDER DETAIL POD WHERE C.CUST ID 2 AND PO.CUST ID C.CUST ID AND PO.ORDER ID POD.ORDER ID SELECT POD.PROD ID, POD.QTY FROM CUSTOMER C, PRODUCT ORDER PO, PRODUCT ORDER DETAIL POD WHERE PO.CUST ID 2 Indexed Column Non Indexed Column SELECT POD.PROD ID, POD.QTY FROM PRODUCT ORDER PO, ORDER DETAIL POD WHERE PO.CUST ID 2 AND PO.ORDER ID POD.ORDER ID AND PO.CUST ID C.CUST ID AND PO.ORDER ID POD.ORDER ID SELECT STATEMENT Optimizer CHOOSE (Cost 3 Card 1 Bytes 51) NESTED LOOPS (Cost 3 Card 1 Bytes 51) NESTED LOOPS (Cost 2 Card 1 Bytes 39) INDEX (UNIQUE SCAN) OF SYS C003983 (UNIQUE) TABLE ACCESS (FULL) OF PRODUCT ORDER (Cost 1 Card 1 Bytes 13) SELECT STATEMENT Optimizer CHOOSE (Cost 3 Card 1 Bytes 38) NESTED LOOPS (Cost 3 Card 1 Bytes 38) NESTED LOOPS (Cost 2 Card 1 Bytes 26) INDEX (UNIQUE SCAN) OF SYS C003983 (UNIQUE) SELECT STATEMENT Optimizer CHOOSE (Cost 2 Card 1 Bytes 25) NESTED LOOPS (Cost 2 Card 1 Bytes 25) TABLE ACCESS (FULL) OF PRODUCT ORDER ( Cost 1 Card 1 Bytes 13) TABLE ACCESS (FULL) OF PRODUCT ORDER (Cost 1 Card 1 Bytes 13) TABLE ACCESS (FULL) OF PRODUCT ORDER DETAI Card 7 Bytes 84) TABLE ACCESS (FULL) OF PRODUCT ORDER DETAIL (Cost 1 TABLE ACCESS (FULL) OF PRODUCT ORDER DETAIL (Cost 1 Card 7 Bytes 84) Card 7 Bytes 84) Database Tuning in 13

Advanced Trace functionality Real time data Can identify deadlocks, excessive waits Optimizers within Oracle Rule Based Optimizer (RBO) Uses Pre–determined rules Independent of data Cost Based Optimizer (CBO) Optimizes based on table/ index information gathered Requires that database is analyzed frequently Default in Oracle Database Tuning in 14

Summary Tuning is a series of trade offs Always better to tune during the design phase than after implementation Sometimes it’s plain common sense ! Database Tuning in 15

Trivia Oracle has been the leading database manufacturer for the last 5 years. (source: IDC, 2003) Oracle: 39.4%, DB2: 33.6%, SQL Server: 11.1% Broad coverage of OS (Windows, Unix & Linux) “Unbreakable Campaign” Advertising campaign in Late 2001. Declared that Oracle 9i was “unbreakable” Open challenge to hackers Windows “Long Horn”. Will feature a new File system “WinFS” which will store files using “database technologies” (source: Microsoft) Database Tuning in 16

References Metalink – http://metalink.oracle.com Oracle Performance Tuning - Richard J.Niemiec, - Oracle Press Database Design Fundamentals – Naphtali Rishe,1988 – Prentice Hall “Oracle Market share” http://www.oracle.com/ip/index.html?database1.html “Unbreakable system” http://www.oracle.com/oramag/oracle/02-mar/index.html?o 22break.html “Microsoft LongHorn” http://msdn.microsoft.com/Longhorn/understandi ng/pillars/WinFS/default.aspx Database Tuning in 17