16.1 Introduction to SQL Tuning Identifying high load or top SQL statements that are responsible for a large share of the application workload and system resources, by reviewing past SQL execution history available in the system Verifying that the execution plans produced by the query optimizer for these statements perform reasonably Implementing corrective actions to generate better execution plans for poorly performing SQL statements OLTP 不推荐使用并行查询 16.3 Identifying High-Load SQL toad 有工具定义, 排序, 可以看到比较耗费资源的SQL情况 V$SQL view: V$SQLSTATS : The data in V$SQLSTATS should be ordered by resource usage V$SQLSTATS.BUFFER_GETS : Buffer gets ( for high CPU using statements) V$SQLSTATS.DISK_READS: Disk reads (for high I/O statements) V$SQLSTATS.SORTS : sorts (for many sorts) SQL Trace : TKPROF 读取 这个 SQL Trace 文件. After you have identified the candidate SQL statements, the next stage is to gather information that is necessary to examine the statements and tune them. Information to Gather During Tuning (1) Complete SQL text from V$SQLTEXT (2) Structure of the tables referenced in the SQL statement, usually by describing the table in SQL*Plus (3) Definitions of any indexes (columns, column orders), and whether the indexes are unique or non-unique (4) Optimizer statistics for the segments (including the number of rows each table, selectivity of the index columns), including the date when the segments were last analyzed (5) Definitions of any views referred to in the SQL statement (6) Repeat steps two, three, and four for any tables referenced in the view definitions found in step five (7) Optimizer plan for the SQL statement (either from EXPLAIN PLAN, V$SQL_PLAN, or the TKPROF output) (8) Any previous optimizer plans for that SQL statement 16.5 Developing Efficient SQL Statements The query optimizer uses statistics gathered on tables and indexes when determining the optimal execution plan. If these statistics have not been gathered, or if the statistics are no longer representative of the data stored within the database, then the optimizer does not have sufficient information to generate the best plan. When tuning (or writing) a SQL statement in an OLTP environment, the goal is to drive from the table that has the most selective filter. > The driving table has the best filter > The join order in each step returns the fewest number of rows to the next step (that is, the join order should reflect, where possible, going to the best not-yet-used filters). > The join method is appropriate for the number of rows being returned. For example, nested loop joins through indexes may not be optimal when the statement returns many rows. > The database uses views efficiently. Look at the SELECT list to see whether access to the view is necessary. > There are any unintentional Cartesian products (even with small tables) > Each table is being accessed efficiently: 16.5.3 Restructuring the SQL Statements 16.5.3.1 Compose Predicates Using AND and =, To improve SQL efficiency, use equijoins whenever possible 16.5.3.2 Avoid Transformed Columns in the WHERE Clause, 例如: good: WHERE a.order_no = b.order_no bad: WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1)) = TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1)) Avoid mixed-mode expressions, and beware of implicit type conversions. When you want to use an index on the VARCHAR2 column charcol, but the WHERE clause looks like this: AND charcol = numexpr, where numexpr is an expression of number type, Oracle Database translates that expression into: AND TO_NUMBER(charcol) = numexpr Avoid the following kinds of complex expressions: col1 = NVL (:b1,col1) NVL (col1,-999) = .... TO_DATE(), TO_NUMBER(), and so on EX: SELECT employee_num, full_name Name, employee_id FROM mtl_employees_current_view WHERE (employee_num = NVL (:b1,employee_num)) AND (organization_id=:1) ORDER BY employee_num; SELECT employee_num, full_name Name, employee_id FROM mtl_employees_current_view WHERE (employee_num = :b1) AND (organization_id=:1) ORDER BY employee_num; If a column of type NUMBER is used in a WHERE clause to filter predicates with a literal value, then use a TO_NUMBER function in the WHERE clause predicate to ensure you can use the index on the NUMBER column. For example, if numcol is a column of type NUMBER, then a WHERE clause containing numcol=TO_NUMBER('5') enables the database to use the index on numcol. If a query joins two tables, and if the join columns have different data types (for example, NUMBER and VARCHAR2), then Oracle Database implicitly performs data type conversion. For example, if the join condition is varcol=numcol, then the database implicitly converts the condition to TO_NUMBER(varcol)=numcol. If an index exists on the varcol column, then explicitly set the type conversion to varcol=TO_CHAR(numcol), thus enabling the database to use the index. 16.5.3.3 Write Separate SQL Statements for Specific Tasks It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator. SELECT info FROM tables WHERE ... AND somecolumn BETWEEN DECODE(:loval, 'ALL', somecolumn, :loval) AND DECODE(:hival, 'ALL', somecolumn, :hival); The database cannot use an index on the somecolumn column, because the expression involving that column uses the same column on both sides of the BETWEEN. 重写上边的语句, 可以走索引 SELECT /* change this half of UNION ALL if other half changes */ info FROM tables WHERE ... AND somecolumn BETWEEN :loval AND :hival AND (:hival != 'ALL' AND :loval != 'ALL') UNION ALL SELECT /* Change this half of UNION ALL if other half changes. */ info FROM tables WHERE ... AND (:hival = 'ALL' OR :loval = 'ALL'); 综上, 基本上是说, 你要对你查询中重要的列的类型做好控制, 尽量不要让隐式转换发生. 16.5.4 Controlling the Access Path and Join Order with Hints 你可以通过hint来指导oracle, 比如: SELECT /*+ FULL(e) */ e.last_name FROM employees e WHERE e.job_id = 'CLERK'; Join order can have a significant effect on performance. The main objective of SQL tuning is to avoid performing unnecessary work to access rows that do not affect the result Avoid a full-table scan if it is more efficient to get the required rows through an index. Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows. Choose the join order so as to join fewer rows to tables later in the join order. EX: SELECT info FROM taba a, tabb b, tabc c WHERE a.acol BETWEEN 100 AND 200 AND b.bcol BETWEEN 10000 AND 20000 AND c.ccol BETWEEN 10000 AND 20000 AND a.key1 = b.key1 AND a.key2 = c.key2; (1) Choose the driving table and the driving index (if any). (2) Choose the best join order, driving to the best unused filters earliest. (3) You can use the ORDERED or STAR hint to force the join order. 16.5.4.1 Use Caution When Managing Views 连接比较复杂的view时, 要特别小心 EX : CREATE OR REPLACE VIEW emp_dept AS SELECT d.department_id, d.department_name, d.location_id, e.employee_id, e.last_name, e.first_name, e.salary, e.job_id FROM departments d ,employees e WHERE e.department_id (+) = d.department_id; SELECT v.last_name, v.first_name, l.state_province FROM locations l, emp_dept v WHERE l.state_province = 'California' AND v.location_id = l.location_id (+); -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | | | | | | | FILTER | | | | | | | | NESTED LOOPS OUTER | | | | | | | | VIEW |EMP_DEPT | | | | | | | NESTED LOOPS OUTER | | | | | | | | TABLE ACCESS FULL |DEPARTMEN | | | | | | | TABLE ACCESS BY INDEX|EMPLOYEES | | | | | | | INDEX RANGE SCAN |EMP_DEPAR | | | | | | | TABLE ACCESS BY INDEX R|LOCATIONS | | | | | | | INDEX UNIQUE SCAN |LOC_ID_PK | | | | | | -------------------------------------------------------------------------------- 16.5.4.2 Store Intermediate Results materialized views 也是其中的一种 16.5.9.1 Combine Multiples Scans Using CASE Expressions EX: SELECT COUNT (*) FROM employees WHERE salary < 2000; SELECT COUNT (*) FROM employees WHERE salary BETWEEN 2000 AND 4000; SELECT COUNT (*) FROM employees WHERE salary>4000; 以上3个, 替换成一个更有效率的SQL, 利用 case SELECT COUNT (CASE WHEN salary < 2000 THEN 1 ELSE null END) count1, COUNT (CASE WHEN salary BETWEEN 2001 AND 4000 THEN 1 ELSE null END) count2, COUNT (CASE WHEN salary > 4000 THEN 1 ELSE null END) count3 FROM employees; 16.5.9.3 Modify All the Data Needed in One Statement 一个事务, 结合在一起 EX: BEGIN FOR pos_rec IN (SELECT * FROM order_positions WHERE order_id = :id) LOOP DELETE FROM order_positions -- 事务1 WHERE order_id = pos_rec.order_id AND order_position = pos_rec.order_position; END LOOP; DELETE FROM orders -- 事务2 WHERE order_id = :id; END; 以上, 事务1 和 事务2 其实是一个操作, 类似银行转账, 所以, 最好放在一个begin end 里.