CS 422 Principles of Database Systems Introduction to

CS 422 Principles of Database Systems Introduction to Query Tuning Chengyu Sun California State University, Los Angeles

Performance Tuning Problem Schema Design Tables Attributes Constraints Database Implementation Query Application Development Client Applications • • • Indexes Views Procedures Triggers Privileges … Performance Tuning

SQL query parse Query Execution parse tree convert answer logical query plan apply laws “improved” l. q. p estimate result sizes l. q. p. +sizes consider physical plans execute statistics Pi pick best {(P 1, C 1), (P 2, C 2). . . } estimate costs {P 1, P 2, …. . } HGM Notes

Query Tuning Identify the SQL statement that need to be tuned Find out the current execution plan Find a better execution plan Tell the DBMS to use the better plan

About Query Tuning rests on a foundation of informed common sense. This makes it both easy and hard. Dennis Shasha and Philippe Bonnet Database Tuning: Principles, Experiements and Troubleshooting Techniques

So What are the Biggest “Common Sense” in DB? ? ?

Tables – Heap File table heap file A 1 A 2 A 3 10 “abc” 32. 3 11 “bcdc” 100. 45 . . (10, ”abc”, 32. 3)(11, ” bcdc”, 100. 45). . . disk page by tuple unordered – low update cost . . . disk page

Indexes – B+-tree (dense) 20 11 18 64 83 75 disk pages 95 98

Clustered vs. Non-clustered Clustered (primary) index n Tuples are clustered by the indexed attribute(s) Non-clustered (secondary) index “Cluster” has a different meaning in Oracle – store attributes from different tables in the same disk block

Composite Index Composite index – an index which involves multiple attributes

View Query Plans EXPLAIN PLAN SET AUTOTRACE ON/OFF More detailed information n n TIMED_STATISTICS SQL_TRACE

Control Query Plans – RBO or CBO? Rule-based Optimizer (RBO) n n n Perform well in most cases Produce robust query plans Code-freeze since Oracle 7 w No improvement in the future w Cannot handle new object types Cost-based Optimizer (CBO) n n n More statistics about the data, better query plan Keep improving Produce bad plans if the statistics are not up-todate

Control Query Plans – Using Optimizer Hints are special comments that pass instructions to the query optimizer Hints syntax: {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]. . . */ or {DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]. . .

Common Optimizer Hints Access path n n INDEX( table_name index_name ) FULL( table_name ) Join order n n ORDERED LEADING (table_name …) Join method n USE_NL, USE_MERGE, USE_HASH

Further Readings Oracle Database Performance Tuning Guide SQL Tuning by Dan Tow Database Tuning by Denise Shasha and Philippe Bonnet Expert One-on-One – Oracle by Thomas Kyte

About FINAL Topics that will be covered n n SQL PL/SQL (stored procedures and triggers) OO features JDBC Topics that may be covered in extra credit problems n n ORM and hibernate SQLJ Topics that won’t be covered n SQL tuning