Oracle SQL Tuning

合集下载
  1. 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。

Optimizing Joins
Access paths
Full Table Scans----FTS is not recommended for large tables unless FTS you are reading >5-10% of it >5Table Access by Rowid Index Scans
Join Operations
Nested Loops (NL) Join Sort-Merge Join Hash Join (not available with the RBO)
Join Order
The ORDERED hint causes the join order to proceed in the order that the tables are listed in the FROM clause
EXAMPLE 1
set autotrace traceonly explain select ename,dname from emp, dept where emp.deptno=dept.deptno and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS'); 15 rows selected.
Affected Factors
OPTIMIZER_MODE Initialization Parameter Statistics in the Data Dictionary OPTIMIZER_GOAL Parameter of the ALTER SESSION Statement----By default, the goal of the CBO is the best throughput Changing the Goal with Hints
Hash Join 适用于记录集比较大的情况。需要注意的是:如果HASH表太大,无 法一次构造在内存中,则分成若干个partition,写入磁盘的temporary segment,则会多一个写的代价,会降低效率。
How the Optimizer Chooses the Join Method
A nested loops join (NL) is inefficient when a join returns a large number of rows If you are using the RBO, then a merge join is the most efficient join when a join returns a large number or rows. If you are using the CBO, then a hash join is the most efficient join when a join returns a large number or rows The optimizer uses a nested loops algorithm for NOT IN subqueries by default (anti-join)-----MERGE_AJ or HASH_AJ hint The optimizer uses a nested loops algorithm for EXISTS subqueries by default (semi-join)-----MERGE_SJ or HASH_SJ hint
CHOOSE ALL_ROWS FIRST_ROWS RULE
OPTIMIZER_MODE
CBO Estimator
Selectivity Cardinality Cost
EXPLAIN PLAN
set autotrace traceonly explain explain plan for … @?/rdbms/admin/utlxpls.sql--@?/rdbms/admin/utlxpls.sql---- Shows plan table output for serial processing. @?/rdbms/admin/utlxplp.sql--@?/rdbms/admin/utlxplp.sql---- Shows plan table output with parallel execution columns 9i ---select * from table(dbms_xplan.display);
Hints
Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the following criteria: Join order Join method Access method Parallelization
This produces the following explain plan: Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248) 1 0 HASH JOIN (Cost=3 Card=8 Bytes=248) 21 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36) 31 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)
Hints for Optimization Approaches and Goals
ALL_ROWS FIRST_ROWS CHOOSE RULE
Hints for Ac百度文库ess Methods
FULL ROWID INDEX NO_INDEX
Hints for Join Orders
ORDERED
EXAMPLE 2
explain plan for select * from range_part where col1=15; @?/rdbms/admin/utlxpls.sql
-------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1 | 13 |1 | | | |* 1 | TABLE ACCESS FULL | RANGE_PART | 1 | 13 |1 |2 |2 | -------------------------------------------------------------------------------------------------------------------
Hints for Join Operations
USE_NL USE_MERGE USE_HASH LEADING HASH_AJ and MERGE_AJ HASH_SJ and MERGE_SJ
Join
Nested Loops (NL) Join 适用于驱动表的记录集比较小(<10000)而且inner表需要有有效的访 问方法(Index)。需要注意的是:JOIN的顺序很重要,驱动表的记 录集一定要小,返回结果集的响应时间是最快的。 Sort-Merge Join
RBO模式 不等价关联(>,<,>=,<=,<>) HASH_JOIN_ENABLED=false 数据源已排序
Specifying a Full Set of Hints
SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb) */ b.application_id , b.set_of_books_id , b.personnel_id, p.vendor_id Personnel, p.segment1 PersonnelNumber, p.vendor_name Name FROM jl_br_journals j, jl_br_balances b, gl_code_combinations glcc, fnd_flex_values_vl glf, gl_periods gp, gl_sets_of_books gsb, po_vendors p WHERE . . . . . . . . . . . .
Introduction to SQL Tuning
-----Teresa -----Teresa Li 2006/08/14
SQL Processing Architecture
[1] Syntactic - checks the syntax of the query [2] Semantic - checks that all objects exist and are accessible [3] View Merging - rewrites query as join on base tables as opposed to using views [4] Statement Transformation - rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery unnesting, in/or transformation) [5] Optimization - determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects. [6] QEP Generation [7] QEP Execution The optimizer is the heart of the SQL processing engine.
相关文档
最新文档