加载中…
Oracle Performance Tuning 执行原理
(2006-11-28 10:18:08)| 分类： Oracle DB |
指标：
Time & Cost
--------------------------------------
SQL语句执行原理：
1.Parse
Syntex Check
Sementic Check (Data Dictionary Cache)
Lock
Check Privilege
Statement Match
Check Privilege
Statement Match
Size determined by the Shared Pool sizing
2.Optimizer
Execution Plan
Evalution of expression_r and condition
Statement Transform
Choice Approach (CBO , RBO)
Access Path
Join Order
Join Method
3.Row Source Generator
Statement Transform
Choice Approach (CBO , RBO)
Access Path
Join Order
Join Method
3.Row Source Generator
Query Transform
1)View Merging
2)Predicate Pushing
3)Subquery Unnesting
4)Query Rewrite with Materialized Views
1)View Merging
2)Predicate Pushing
3)Subquery Unnesting
4)Query Rewrite with Materialized Views
Estimator
1)Selectivity
2)Cardinality
3)Cost (I/O,CPU,Memory)
1)Selectivity
2)Cardinality
3)Cost
Plan Generator
4.SQL Execution Engine
如何查看执行计划？
TABLE ACCESS FULL --表示全表扫描
TABLE ACCESS BY INDEX --表示走索引
HASH JOIN --通常大表的连接使用
MERGE JOIN CARTESIAN --通常小表的连接使用
NESTED LOOPS --通常对于不等于的条件使用这种连接会更优
UNIQUE INDEX UNIQUE SCAN --索引唯一扫描
UNIQUE INDEX RANGE SCAN --索引序列扫描
UNIQUE INDEX FULL SCAN --索引全扫描
NON-UNIQUE INDEX RANGE SCAN --N索引序列扫描
BUFFER SORT --排序
--------------------------------------
喜欢
0
赠金笔