- 浏览: 214164 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
bluky999:
jiakechong 写道跟啊里爸爸那个差不多吧跟 dubb ...
淘宝-HSF -
jiakechong:
跟啊里爸爸那个差不多吧
淘宝-HSF -
lookqlp:
您好!我写了个关键词关联度统计的mapreduce,排序非常慢 ...
排序--MapReduce -
liubang201010:
DB Audit更多参考资料:
http://www.inno ...
8--《DB Audit》 -
裴小星:
这些数据库专家,同样不了解程序员。SQL(或PL/SQL,TS ...
MapReduce:一个重大的倒退
一、什么是执行计划
An explain plan is a representation of the access path that is taken when a query is executed within Oracle.
二、如何访问数据
At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:
Full Table Scan (FTS) --全表扫描
Index Lookup (unique & non-unique) --索引扫描(唯一和非唯一)
Rowid --物理行id
三、执行计划层次关系
When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing that is executed. --采用最右最上最先执行的原则看层次关系,在同一级如果某个动作没有子ID就最先执行
1.看一个简单的例子:
Query Plan
-----------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1234
**TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED] --[:Q65001]表示是并行方式,[ANALYZED]表示该对象已经分析过了
优化模式是CHOOSE的情况下,看Cost参数是否有值来决定采用CBO还是RBO:
SELECT STATEMENT [CHOOSE] Cost=1234 --Cost有值,采用CBO
SELECT STATEMENT [CHOOSE] Cost= --Cost为空,采用RBO
2.层次的父子关系,看比较复杂的例子:
PARENT1
**FIRST CHILD
****FIRST GRANDCHILD
**SECOND CHILD
Here the same principles apply, the FIRST GRANDCHILD is the initial operation then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT collates the output.
四、例子解说
Execution Plan
----------------------------------------------------------
0 **SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248)
1 0 **HASH JOIN (Cost=3 Card=8 Bytes=248)
2 1 ****TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)
3 1 ****TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)
左侧的两排数据,前面的是序列号ID,后面的是对应的PID(父ID)。
A shortened summary of this is:
Execution starts with ID=0: SELECT STATEMENT but this is dependand on it's child objects
So it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on it's child objects
So it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF 'DEPT'
Then the second child step: ID=3 PID=2 TABLE ACCESS (FULL) OF 'EMP'
Rows are returned to the parent step(s) until finished
五、表访问方式
1.Full Table Scan (FTS) 全表扫描
In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. --全表扫描模式下会读数据到表的高水位线(HWM即表示表曾经扩展的最后一个数据块),读取速度依赖于Oracle初始化参数 db_block_multiblock_read_count
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
**INDEX UNIQUE SCAN EMP_I1 --如果索引里就找到了所要的数据,就不会再去访问表了
2.Index Lookup 索引扫描
There are 5 methods of index lookup:
index unique scan --索引唯一扫描
Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.
eg:
SQL> explain plan for select empno,ename from emp where empno=10;
index range scan --索引局部扫描
Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > >= explain plan for select mgr from emp where mgr = 5;
index full scan --索引全局扫描
Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.
eg:
SQL> explain plan for select empno,ename from big_emp order by empno,ename;
index fast full scan --索引快速全局扫描,不带order by情况下常发生
Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.
eg:
SQL> explain plan for select empno,ename from big_emp;
index skip scan --索引跳跃扫描,where条件列是非索引的前导列情况下常发生
Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.
eg:
SQL> create index i_emp on emp(empno, ename);
SQL> select job from emp where ename='SMITH';
3.Rowid 物理ID扫描
This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid扫描是最快的访问数据方式
六、表连接方式
有三种连接方式:
1.Sort Merge Join (SMJ) --由于sort是非常耗资源的,所以这种连接方式要避免
Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently.
SQL> explain plan for
select e.deptno,d.deptno
from emp e,dept d
where e.deptno = d.deptno
order by e.deptno,d.deptno;
Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost=17
**MERGE JOIN
****SORT JOIN
******TABLE ACCESS FULL EMP [ANALYZED]
****SORT JOIN
******TABLE ACCESS FULL DEPT [ANALYZED]
Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.
2.Nested Loops (NL) --比较高效的一种连接方式
Fetches the first batch of rows from row source 1, Then we probe row source 2 once for each row returned from row source 1.
For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.
SQL> explain plan for
select a.dname,b.sql
from dept a,emp b
where a.deptno = b.deptno;
Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost=5
**NESTED LOOPS
****TABLE ACCESS FULL DEPT [ANALYZED]
****TABLE ACCESS FULL EMP [ANALYZED]
3.Hash Join --最为高效的一种连接方式
New join type introduced in 7.3, More efficient in theory than NL & SMJ, Only accessible via the CBO. Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory.
SQL> explain plan for
select empno
from emp,dept
where emp.deptno = dept.deptno;
Query Plan
----------------------------
SELECT STATEMENT [CHOOSE] Cost=3
**HASH JOIN
****TABLE ACCESS FULL DEPT
****TABLE ACCESS FULL EMP
Hash joins are enabled by the parameter HASH_JOIN_ENABLED=TRUE in the init.ora or session. TRUE is the default in 7.3.
3.Cartesian Product --卡迪尔积,不算真正的连接方式,sql肯定写的有问题
A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data. Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out.
It can be useful in some circumstances - Star joins uses cartesian products.Notice that there is no join between the 2 tables:
SQL> explain plan for
select emp.deptno,dept,deptno
from emp,dept
Query Plan
------------------------------
SLECT STATEMENT [CHOOSE] Cost=5
**MERGE JOIN CARTESIAN
****TABLE ACCESS FULL DEPT
****SORT JOIN
******TABLE ACCESS FULL EMP
The CARTESIAN keyword indicate that we are doing a cartesian product.
七、运算符
1.sort --排序,很消耗资源
There are a number of different operations that promote sorts:
order by clauses
group by
sort merge join
2.filter --过滤,如not in、min函数等容易产生
Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another, functions such as min may introduce filter steps into query plans.
3.view --视图,大都由内联视图产生
When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.
eg:
SQL> explain plan for
select ename,tot
from emp,(select empno,sum(empno) tot from big_emp group by empno) tmp
where emp.empno = tmp.empno;
Query Plan
------------------------
SELECT STATEMENT [CHOOSE]
**HASH JOIN
**TABLE ACCESS FULL EMP [ANALYZED]
**VIEW
****SORT GROUP BY
******INDEX FULL SCAN BE_IX
4.partition view --分区视图
Partition views are a legacy technology that were superceded by the partitioning option. This section of the article is provided as reference for such legacy systems.
(本文转自:http://hi.baidu.com/edeed/blog/item/73c46538d2614d2796ddd864.html)
发表评论
-
OCP认证考试指南全册10g
2012-01-06 18:49 1016常用命令: 1,lsnrctl status;emctl s ... -
exits 和in 深度分析(转载,出处不明)
2011-08-22 21:03 888EXISTS的执行流程 select * from t1 wh ... -
索引有点靠后
2011-08-10 21:51 735记得在taobao时候需要统计一个数据,但是通过不了SQLWE ... -
《MySQL 性能调优与架构设计》
2011-08-10 21:47 800MySQL的基本架构:SQL Layer和Storage En ... -
《Oracle Database 10g 性能调整与优化》
2011-08-02 21:31 10418.2 当从表中访问数据 ... -
Oracle零数据丢失
2011-07-25 20:28 929Oracle在版本7的时候, ... -
硬盘存储和磁盘存储比较
2011-05-30 14:02 756如图 -
解析oracle的ROWNUM -- 作者: chen_liang
2011-05-17 11:32 821对于rownum来说它是oracle系统顺序分配为从查询 ... -
flashback 需要的条件
2011-04-13 19:00 784flashback table 需要下面几个条件 1. ... -
oracle表列名大小写
2011-03-23 22:06 4018不是我写的sql,比较烂,但是有一个可以关注的点 oracle ... -
MapReduce:一个重大的倒退
2011-03-22 23:09 1642转载http://www.cnblogs.com/c ... -
ORA-12154
2011-03-20 23:16 907看看监听: lsnrctl status 是不是正常 ... -
Oracle -other
2011-03-12 13:10 814JackJw 的杂货铺 http://jackjw.itpu ... -
数据库的最佳布局
2011-03-09 23:26 8051,并发访问对象的分离 2,数据和相应的索引的分开 3,回 ... -
优化器
2011-03-07 23:47 754访问路径:FULL TABLE SCAN 全表扫描;Index ... -
B树索引和位图索引的使用环境
2011-03-07 00:18 844B树索引:是联机事务处理应用的最好选择,键值列是最适合的 位 ... -
sqlplus autotrace
2011-03-05 14:14 752出自eygle http://www.eygle.com/fa ... -
Oracle 重要文件系统解释 转载
2011-02-25 23:01 821Oracle 中要了解的文件实在太多了,一不小心就被弄 ... -
聚族索引、非聚族索引、组合索引的含义和用途 转载
2011-02-25 21:50 1257http://blog.csdn.net/zhaohuabin ... -
Oracle analyze
2011-02-25 21:19 8931、三大功能 搜集和删除索引、表和簇的统计信息 验证表、索 ...
相关推荐
KingbaseES V8R3 执行计划详解是数据库管理和优化的重要工具,它为数据库管理员提供了深入理解SQL查询如何在数据库内部执行的详细信息。执行计划是数据库系统根据查询语句生成的一种操作序列,用于高效地获取和处理...
ORACLE 执行计划详解文档 本文档详细解释了 ORACLE 的执行计划背景知识,包括共享 SQL 语句、执行计划的存储和共享、绑定变量的使用、执行计划的生成和共享、执行计划的优化等方面的内容。 一、共享 SQL 语句 ...
oracle 执行计划详解 Oracle 执行计划是数据库性能调整的关键部分,对于想要学习 Oracle 数据库的朋友非常有帮助。下面是Oracle 执行计划的背景知识和重要概念。 共享 SQL 语句 Oracle 将 SQL 语句及解析后得到的...
Oracle执行计划详解,包括oracle执行顺序和索引详细介绍
"Oracle 执行计划详解" Oracle 执行计划是数据库性能优化的关键。为了更好地理解和优化 Oracle 数据库的执行计划,我们需要了解执行计划的生成过程、优化方法和执行计划的解读方法。 执行计划生成过程 执行计划的...
根据提供的文件内容,我们可以从中提取出关于MySQL执行计划的相关知识点。以下是对知识点的详细介绍: ### 执行计划的ID - `id` 表示查询的标识符,用于区分执行计划中的查询块。在简单的查询中,所有行的`id`值...
### HiveSQL执行计划详解 #### 一、前言 在大数据处理领域,Hive作为一款广泛使用的数据仓库工具,提供了高效的数据查询与分析能力。为了更深入地理解HiveSQL的执行过程,掌握其执行计划变得尤为重要。执行计划是...
本文档详细解析了MySQL执行计划的各种特性以及具体操作参数的含义,涵盖不同类型的SQL执行情况(简单查询、子查询等),以及各类访问方法的具体含义。通过解释ID、类型和附加信息等相关字段的功能来展示SQL查询执行...
MySQL执行计划是数据库管理员和开发人员优化SQL查询性能的关键工具。它揭示了MySQL如何解析查询,确定数据检索的顺序,以及使用哪些索引。在本文中,我们将深入探讨MySQL执行计划,了解其重要性,如何生成,以及如何...
Oracle 执行计划详解 Oracle 执行计划是指 Oracle 数据库在执行查询语句时所采取的访问路径。了解执行计划对于优化查询语句、提高数据库性能至关重要。 什么是执行计划 执行计划是 Oracle 数据库在执行查询语句时...
### MySQL执行计划详解 在数据库查询优化领域,理解MySQL执行计划是至关重要的技能之一。执行计划,也称为查询计划或查询执行计划,是MySQL优化器为执行SQL查询所选择的一系列步骤。它揭示了数据库如何处理查询,...
###### 1.2.4 执行计划详解 执行计划的结果由多个列组成,每个列都提供了有关查询执行的信息: - **ID列**:描述SELECT查询的序列号,表示查询中执行SELECT子句或操作表的顺序。 - ID相同:执行顺序由上至下。 -...
本文将深入探讨Oracle的执行计划和表分区两个重要概念,以及它们如何影响数据库性能。 执行计划是Oracle数据库处理SQL查询的一种步骤序列,它详细描述了数据如何从表中提取、如何进行排序或聚合,以及如何返回到...
### Oracle优化器及执行计划详解 #### 一、性能调整概览 Oracle 数据库作为一款高性能的数据库管理系统,其性能优化对于确保系统高效稳定运行至关重要。性能调整涉及到多个层面,包括但不限于应用程序设计、数据库...
### Oracle执行计划详解 在数据库管理与优化领域中,理解并掌握如何查看及解读Oracle执行计划是一项非常重要的技能。执行计划对于SQL语句的性能优化至关重要,它可以帮助我们快速定位到可能存在的性能瓶颈,并据此...
### SQL Server执行计划详解 #### 一、引言 在数据库管理与优化的过程中,了解SQL Server执行计划至关重要。本文将详细介绍SQL Server执行计划的基本概念、如何捕获执行计划、解读执行计划以及如何利用这些信息来...
### SQL Server 执行计划详解 #### 一、执行计划概览 在进行SQL Server的优化过程中,查询优化是一项常见且重要的任务。对于数据库优化而言,它涵盖了多个层面,包括但不限于查询语句、索引设计、表结构等。本文将...