Department of Computer and Information Science, School of Science, IUPUI

1. Department of Computer and Information Science,School of Science, IUPUI SQL Tuning Introduction Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

2. Oracle Data Dictionary Data Dictionary: stores all information that is used to manage the objects in the database. Nomenclature: USER_% objects you own ALL_% objects you own, have been granted access to, or public DBA_% all objects in database. Road Map Tables: DICTIONARY (DICT) - describes the Oracle data dictionary tables. select table_name, comments from DICT where table_name like ‘USER_%’;

3. Oracle Data Dictionary • Tables about Database Objects: • USER_CATALOG (CAT) - tables, views, synonyms, sequences • USER_OBJECTS (OBJ) - clusters, links, functions, indexes, packages, procedures, sequences, synonyms, tables, triggers, views • USER_TABLES (TABS) • USER_TAB_COLUMNS (COLS) • USER_VIEWS • USER_SYNONYMS (SYN) • USER_SEQUENCES (SEQ) • Constraints: • USER_CONSTRAINTS • USER_CONS_COLUMNS • EXCEPTIONS • Comments: • USER_TAB_COMMENTS • USER_COL_COMMENTS • Indexes: • USER_INDEXES (IND) • USER_IND_COLUMNS • Clusters: • USER_CLUSTERS (CLU) • USER_CLU_COLUMNS

4. Oracle Data Dictionary • Links, Snapshots: • USER_DB_LINKS • USER_SNAPSHOTS • USER_SNAPSHOT_LOGS • Procedures: • USER_TRIGGERS • USER_SOURCE • USER_ERRORS • USER_OBJECT_SIZE • USER_ARGUMENTS • Space Allocation: • USER_TABLESPACES • USER_SEGMENTS • USER_EXTENTS • USER_FREE_SPACE • User Tables: • USER_USERS • USER_RESOURCE_LIMITS • USER_TAB_PRIVS • USER_SYS_PRIVS • Roles: • USER_ROLE_PRIVS • ROLE_TAB_PRIVS - DBA privilege required • ROLE_SYS_PRIVS - DBA privilege required • ROLE_ROLE_PRIVS - DBA privilege required

5. Oracle Data Dictionary • Auditing: (only if auditing turned on by DBA) • USER_AUDIT_OBJECT • USER_AUDIT_SESSION • USER_AUDIT_STATEMENT • USER_OBJ_AUDIT_OPTS • Dynamic Tables: (DBA ONLY) • V$ACCESS - locks currently held on objects • V$FILESTAT - file read/write statistics • V$LOCK - non-DDL locks and resources • V$PROCESS - currently active processes • V$SESSION - currently active sessions • V$SGA - size of each component in the SGA (System Global Area) • V$SYSSTAT - current statistics on entire database • V$TRANSACTION- info about transactions. • Chained Rows: • Create table CHAINED_ROWS from $ORA_HOME/rdbms/admin/utlchain.sql • Analyze a table like this:analyze LEDGER list chained rows into CHAINED_ROWS; • Plan Table: • Create table PLAN_TABLE from $ORA_HOME/rdbms/admin/utlxplan.sql • Analyze a query like this:explain plan set Statement_id = ‘A’ for select ...;

6. Good Design Has a Human Touch • Understand the Applications Tasks • What data is to be captured? • How should the data be processed? • How should the data be reported?

7. Performance and Design • Ways to Increase DB Performance: • Relax normalization rules by using meaningful keys, pp. 609-612 • Put redundant data into 3NF tables, pp. 612-614 • More memory, p. 614 • Combining smaller tables, “Kitchen Junk Drawer” approach, p. 615 • Computation Table, p. 616 • Snapshots, Chapter 23

8. SQL Tuning • Why tune SQL statements: • Improve response time of interactive programs. • Improve batch throughput. • To ensure scalability of applications (load v. perf.) • Reduce system load for other uses besides DB. • Avoid hardware upgrades. • Scalability: • Well-tuned applications deliver good performance as number of users or data volume increases. • Applications which have a linear degradation pattern degrade predictably, usually they are problems responsive to hardware upgrades. • Exponential degradation patterns are more serious. They tend to be problems that creep up over time, starting benign and unrecognized, later becoming huge problems. • Bottlenecks are performance problems which are abrupt, like “hitting a brick wall”. Usually no warning, and no hardware solution.

9. SQL Tuning • Objections to SQL tuning: • “The Oracle optimizer will tune my statements for me automatically.” • “I’m not a SQL programmer, I’m a ... VB/PowerBuilder/C++/Java programmer.” • “I’ll write the SQL, someone else (DBA) can tune it for me later.” • “I’ll tune it later” • “We can’t afford to tune it” • When to tune: • Early is least costly & better. • Changing SQL/table designs in the design phase means that no applications need to be re-written. • Tuning SQL performance when SQL is first written usually means lower testing costs. • In production systems, testing SQL can sometimes be difficult... change control, production system availability, extra time to deal with larger data volumes. • Tune SQL early as possible to be most effective and economical.

10. SQL Tuning

11. SQL Tuning • Retrieving Data: • Full Table Scan - gets data from row 1 to high water mark • Row ID - gets data by physical location. Quickest way to get a row. • Index Lookup - matches up key value with Row ID • Hash Key Lookup - computes a Row ID with a mathematical formula applied to key value. • Joining Data: • Sort Merge Join • sorts each table’s key columns • merges data together • does not use indexes • Nested Loops Join • full table scan used on smaller table • key values of 1st table joined with the larger table’s index • Hash Join • hash key is built for larger table, constructs index on the fly • smaller table is then fully scanned • data from smaller table is joined with the hash key index.

12. SQL Tuning • Oracle Optimizers: • Rule Based Optimizer • older optimizer, used since first versions of Oracle. • set of rules which rank access paths. • always picks an index over doing full table scan. • Cost Based Optimizer • new in Oracle 7. • takes advantage of database statistics to pick optimal access path. • To collect table statistics:ANALYZE TABLE tablename CALCULATE STATISTICS; • Optimizer Goals: • RULE, picks only rule based optimizer. • CHOOSE, picks cost based optimizer if any table in query has been analyzed. • ALL_ROWS, picks the cost based optimizer and finds an execution plan which is best for the entire query. Good for batch reporting. • FIRST_ROWS, pick the cost based optimizer and finds an execution plan which is best for the first row. Good for interactive applications.

13. SQL Tuning • How to set the optimizer goal: • Change the database configuration file (init.ora). OPTIMIZER_MODE=FIRST_ROWS • Change settings for your session in SQL*Plus. ALTER SESSION SET OPTIMIZER_GOAL=RULE; • “Influence” the optimizer with “hints”. • Example, use rule based optimizer: SELECT /*+ RULE */ * FROM EMPLOYEE WHERE SALARY > 100000; • Example, use full table scan: select /*+ FULL(E) FULL(D) */ e.employee_id, e.surname, e.firstname from employee e, department d where d.location=‘Indianapolis’ and d.dept_id = e.dept_id; • Example, recommend an index: select /*+ index(E, emp_dept_idx) */ e.employee_id, e.surname, e.firstname from employee e, department d where d.location=‘Indianapolis’ and d.dept_id = e.dept_id;

14. SQL Tuning • Tracing Oracle SQL: • EXPLAIN PLAN, shows the execution plan. • SQL_TRACE, generates a trace file containing SQL executed by your session and resources used. • tkprof, formats SQL_TRACE output. • AUTOTRACE, SQL*Plus command to show exuction plans and statistics in your SQL*Plus session. • 3rd Party Tools. Numerous GUI tools to quickly show this information, usually expensive.

15. SQL Tuning • EXPLAIN PLAN • Executing: • explain plan forselect /*+RULE */ e.surname, e.firstname, e.date_of_birth from employee e, customers c where e.surname=c.contact_surname • and e.firstname=c.contact_firstname • and e.date_of_birth=c.date_of_birth • order by e.surname, e.firstname; • Formatting Plan Table in a Query: • select rtrim(lpad(‘ ‘,2*level)|| rtrim(operation)||‘ ‘|| rtrim(options)||‘ ‘|| object_name) as query_plan from plan_table • connect by prior id=parent_id • start with id=0; • Execution Plan Query: • query_plan • ---------------------------------------- • SELECT STATEMENT • SORT ORDER BY • NETED LOOPS • TABLE ACCESS FULL CUSTOMERS • TABLE ACCESS BY ROWID EMPLOYEES • INDEX RANGE SCAN EMP_BOTH_IDX

16. Acknowledgements • Loney, Oracle Database 10g The Complete Reference