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('') 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 里.
