jadethao
- 浏览: 766778 次
- 性别:
- 来自: 天津
-
文章分类
最新评论
-
HarryMa：
v
SpringMVC异常报406 (Not Acceptable)的解决办法 -
HarryMa：
[i][/i]。。
SpringMVC异常报406 (Not Acceptable)的解决办法 -
lingyun6100：
我也遇到同样问题，我的错误原因是错引用了JSONObject。 ...
SpringMVC异常报406 (Not Acceptable)的解决办法 -
cbn_1992：
972974124 写道换了json的jar包还是不好用，我把 ...
SpringMVC异常报406 (Not Acceptable)的解决办法 -
go_myself：
...
解决异常：java.lang.NoSuchMethodError: org.objectweb.asm.ClassWriter.<init>(Z)V
看懂Oracle执行计划是优化的第一步，让我们从下面的例子开始吧。
下面为补充内容
1、创建测试表
oracle优化器：RBO和CBO两种， 从oracle10g开始优化器已经抛弃了RBO，下面的列子说明CBO大概是怎样的
现象t表还没有被分析，提示/*+dynamic_sampling(t 0) */*的目的是让CBO无法通过动态采样获取表中的实际数据情况，此时CBO只能根据T表中非常有限的信息（比如表中的extents数量，数据块的数量）来猜测表中的数据。从结果中可以看到CBO猜出表中id=1的有195条，这个数值对于表的总数来说，是一个非常小的值，所以CBO选择了索引而不是全表扫描。
而实际情况如下所示：
通过动态取样，CBO估算出行数为49454，非常接近于真实50820数目。选择了全表扫描。
我们来收集一下统计信息
现在扫描过的行数为50815。
如果我们更新了所有的id为99看看。
因为没有对表进行分析，所以表中的分析数据还是之前的信息，CBO并不知道。我们可以看出Rows值为1，也就是说CBO人为表T中的ID=99的值只有1条，所有选择仍然是索引。
我们收集一把统计信息。
上面为补充内容，下面正式开始
1、 sql的执行计划
创建测试表
2、产生执行计划
看执行计划时，我们首先从缩进最大的行读取，它是最先被执行的步骤。在执行计划中：id=3和id=4是最先被执行的，
两行缩进一样的，最上面的最先被执行，在这里就是id=3
选择次之缩进的行数id=2，表连接方式为NESTED LOOPS。
然后是id=1，扫描表的方式为TABLE ACCESS BY INDEX ROWID
最后是id=0
我们翻译成语言大概如下，
从t2表第一行读取，查看每一行是否符合下面条件：
"T1"."ID"="T2"."ID"
如果符合就拿出一行来，扫描整个t2表，这个过程就叫NESTED LOOPS
当整个t2表被扫描完之后，会产生一个结果集，这个结果集是IND_T1的一个索引集，然后oracle根据索引键值上的rowid去T1表中找到相应的记录，就是这一步：TABLE ACCESS BY INDEX ROWID
然后将结果返回：SELECT STATEMENT
id列为：id=3->id=4->id=2->id=1->id=0
让我们再看一看表中每一行表示什么含义：
1）Operation 列：当前操作的内容。
2）Rows 列 ：就是当前操作的 cardinality ，Oracle估算当前操作的返回结果集。
3）Cost (%CPU) : Oracle计算出来的一个数值（代价），用于说明sql执行的代价。
4）Time 列：Oracle估算当前操作的时间。
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
这里有access和filter区别，access就表示这个谓词的条件的值将会影响数据的访问路径（一般针对索引），filter只起过滤作用。
举个例子
懂了吧。
下面我们来仔细分析Operation里面的内容
a、表访问方式
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(我觉得应该这样翻译:FTS扫描会使表使用上升到高水位(HWM),HWM标识了表最后写入数据的块,如果你用DELETE删除了所有的数据表仍然处于高水位(HWM),只有用TRUNCATE才能使表回归,FTS使用多IO从磁盘读取数据块)Query Plan
------------------------------------
--如果索引里就找到了所要的数据，就不会再去访问表
2.Index Lookup 索引扫描
There are 5 methods of index lookup:
1）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:
2）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. >
3）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.
4）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.
5）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.
3.Rowid 物理ID扫描
This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in.
--Rowid扫描是最快的访问数据方式
b、运算符
1.sort --排序，很消耗资源
There are a number of different operations that promote sorts:
(1)order by clauses (2)group by (3)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.
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.
3、让我们再看看统计信息部分
说明：Cost=（Single block I/O cost+ Multiblock I/O cost+ CPU cost）/sreadtim
原文URL：http://blog.csdn.net/rulev5/article/details/6984560
下面为补充内容
1、创建测试表
[sql] view plaincopy SQL> create table t as select 1 id,object_name from dba_objects; Table created SQL> update t set id=99 where rownum=1; 1 row updated SQL> commit; Commit complete SQL> create index t_ind on t(id); Index created
oracle优化器：RBO和CBO两种， 从oracle10g开始优化器已经抛弃了RBO，下面的列子说明CBO大概是怎样的
[sql] view plaincopy
SQL> select /*+dynamic_sampling(t 0) */* from t where id=1;
50819 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 195 | 15405 | 51 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 195 | 15405 | 51 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 78 | | 50 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
现象t表还没有被分析，提示/*+dynamic_sampling(t 0) */*的目的是让CBO无法通过动态采样获取表中的实际数据情况，此时CBO只能根据T表中非常有限的信息（比如表中的extents数量，数据块的数量）来猜测表中的数据。从结果中可以看到CBO猜出表中id=1的有195条，这个数值对于表的总数来说，是一个非常小的值，所以CBO选择了索引而不是全表扫描。
而实际情况如下所示：
[sql] view plaincopy
SQL> select * from t where id=1
2 ;
50819 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49454 | 3815K| 67 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 49454 | 3815K| 67 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
通过动态取样，CBO估算出行数为49454，非常接近于真实50820数目。选择了全表扫描。
我们来收集一下统计信息
[sql] view plaincopy
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);
SQL> select * from t where id=1;
50819 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50815 | 1339K| 67 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 50815 | 1339K| 67 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
现在扫描过的行数为50815。
如果我们更新了所有的id为99看看。
[sql] view plaincopy
SQL> update t set id=99;
50820 rows updated
SQL> select * from t where id=99;
Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 27 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=99)
因为没有对表进行分析，所以表中的分析数据还是之前的信息，CBO并不知道。我们可以看出Rows值为1，也就是说CBO人为表T中的ID=99的值只有1条，所有选择仍然是索引。
我们收集一把统计信息。
[sql] view plaincopy
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);
PL/SQL procedure successfully completed
SQL> select * from t where id=99;
50820 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50815 | 1339K| 67 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 50815 | 1339K| 67 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=99)
上面为补充内容，下面正式开始
1、 sql的执行计划
创建测试表
[sql] view plaincopy SQL> create table t1(id int,name varchar2(1000)); Table created SQL> create table t2(id int,name varchar2(1000)); Table created SQL> create index ind_t1 on t1(id); Index created SQL> create index ind_t2 on t2(id); Index created SQL> create index ind_t2_name on t2(name); Index created SQL> insert into t1 select a.OBJECT_ID,a.OBJECT_NAME from all_objects a; 50206 rows inserted SQL> insert into t2 select a.OBJECT_ID,a.OBJECT_NAME from all_objects a where rownum<=20; 20 rows inserted SQL> commit; Commit complete SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade => true); PL/SQL procedure successfully completed SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade => true); PL/SQL procedure successfully completed
2、产生执行计划
[sql] view plaincopy
SQL> select * from t1,t2 where t1.id= t2.id;
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 828990364
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 780 | 43 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 28 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 20 | 780 | 43 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 20 | 220 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_T1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
37 consistent gets
0 physical reads
0 redo size
1452 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
看执行计划时，我们首先从缩进最大的行读取，它是最先被执行的步骤。在执行计划中：id=3和id=4是最先被执行的，
[sql] view plaincopy | 3 | TABLE ACCESS FULL | T2 | 20 | 220 | 3 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IND_T1 | 1 | | 1 (0)| 00:00:01 |
两行缩进一样的，最上面的最先被执行，在这里就是id=3
[sql] view plaincopy | 3 | TABLE ACCESS FULL | T2 | 20 | 220 | 3 (0)| 00:00:01 |
选择次之缩进的行数id=2，表连接方式为NESTED LOOPS。
[sql] view plaincopy | 2 | NESTED LOOPS | | 20 | 780 | 43 (0)| 00:00:01 |
然后是id=1，扫描表的方式为TABLE ACCESS BY INDEX ROWID
[sql] view plaincopy | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 28 | 2 (0)| 00:00:01 |
最后是id=0
[sql] view plaincopy | 0 | SELECT STATEMENT | | 20 | 780 | 43 (0)| 00:00:01 |
我们翻译成语言大概如下，
从t2表第一行读取，查看每一行是否符合下面条件：
"T1"."ID"="T2"."ID"
如果符合就拿出一行来，扫描整个t2表，这个过程就叫NESTED LOOPS
当整个t2表被扫描完之后，会产生一个结果集，这个结果集是IND_T1的一个索引集，然后oracle根据索引键值上的rowid去T1表中找到相应的记录，就是这一步：TABLE ACCESS BY INDEX ROWID
然后将结果返回：SELECT STATEMENT
id列为：id=3->id=4->id=2->id=1->id=0
让我们再看一看表中每一行表示什么含义：
1）Operation 列：当前操作的内容。
2）Rows 列 ：就是当前操作的 cardinality ，Oracle估算当前操作的返回结果集。
3）Cost (%CPU) : Oracle计算出来的一个数值（代价），用于说明sql执行的代价。
4）Time 列：Oracle估算当前操作的时间。
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
这里有access和filter区别，access就表示这个谓词的条件的值将会影响数据的访问路径（一般针对索引），filter只起过滤作用。
举个例子
[sql] view plaincopy
SQL> select * from t1 where t1.name='AA';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 56 | 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 56 | 69 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."NAME"='AA')
懂了吧。
下面我们来仔细分析Operation里面的内容
[sql] view plaincopy
a、表访问方式
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(我觉得应该这样翻译:FTS扫描会使表使用上升到高水位(HWM),HWM标识了表最后写入数据的块,如果你用DELETE删除了所有的数据表仍然处于高水位(HWM),只有用TRUNCATE才能使表回归,FTS使用多IO从磁盘读取数据块)Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1 **INDEX UNIQUE SCAN EMP_I1
--如果索引里就找到了所要的数据，就不会再去访问表
2.Index Lookup 索引扫描
There are 5 methods of index lookup:
1）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> select empno,ename from emp where empno=10
SQL> select empno,ename from emp where empno=10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=10)
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
385 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
2）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. >
SQL> select empno from emp where EMPNO>=7902;
Execution Plan
----------------------------------------------------------
Plan hash value: 1567865628
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| PK_EMP | 2 | 26 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPNO">=7902)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
569 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
3）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.
SQL> select empno from emp order by empno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 179099197
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 182 | 2 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 182 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
4）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.
SQL> select empno from emp;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 366039554
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 182 | 2 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_EMP | 14 | 182 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
5）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.
SQL> create index i_emp on emp(empno, ename);
Index created.
SQL> select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH';
Execution Plan
----------------------------------------------------------
Plan hash value: 98078853
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | I_EMP | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='SMITH')
filter("ENAME"='SMITH')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
513 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3.Rowid 物理ID扫描
This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in.
--Rowid扫描是最快的访问数据方式
SQL> select * from emp where rowid='AAAjFUAAEAAABZ1AAM';
Execution Plan
----------------------------------------------------------
Plan hash value: 1116584662
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 99 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
983 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
b、运算符
1.sort --排序，很消耗资源
There are a number of different operations that promote sorts:
(1)order by clauses (2)group by (3)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.
SQL> select ename,tot from emp,(select empno,sum(empno) tot from emp group by empno) tmp where emp.empno = tmp.empno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 138960760
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 644 | 4 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 644 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 280 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_EMPNO | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 364 | 2 (50)| 00:00:01 |
| 5 | VIEW | | 14 | 364 | 1 (0)| 00:00:01 |
| 6 | HASH GROUP BY | | 14 | 182 | 1 (0)| 00:00:01 |
| 7 | INDEX FULL SCAN | PK_EMPNO | 14 | 182 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."EMPNO"="TMP"."EMPNO")
filter("EMP"."EMPNO"="TMP"."EMPNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
43 recursive calls
0 db block gets
61 consistent gets
0 physical reads
0 redo size
821 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
14 rows processed
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.
3、让我们再看看统计信息部分
SQL> set autotrace traceonly;
SQL> select count(*) from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls (归调用次数)
0 db block gets (从磁盘上读取的块数,即通过update/delete/select for update读的次数)
15 consistent gets (从内存里读取的块数,即通过不带for update的select 读的次数)
0 physical reads (物理读—从磁盘读到数据块数量,一般来说是'consistent gets' + 'db block gets')
0 redo size (重做数——执行SQL的过程中，产生的重做日志的大小)
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory) (在内存中发生的排序)
0 sorts (disk) (在硬盘中发生的排序)
1 rows processed
说明：Cost=（Single block I/O cost+ Multiblock I/O cost+ CPU cost）/sreadtim
| 序号 | 列名 | 解释 |
| 1 | db block gets | 从buffer cache中读取的block的数量 |
| 2 | consistent gets | 从buffer cache中读取的undo数据的block的数量 |
| 3 | physical reads | 从磁盘读取的block的数量 |
| 4 | redo siz | DML生成的redo的大小 |
| 5 | sorts (memory) | 在内存执行的排序量 |
| 6 | sorts (disk) | 在磁盘上执行的排序量 |
原文URL：http://blog.csdn.net/rulev5/article/details/6984560
分享到：
发表评论
-
ubuntu系统设置定时备份mysql
2013-12-13 16:01 5355第一步：创建mysql的备份执行脚本 1.新建备份脚本文件，在 ... -
mongodb的安装以及加入服务启动项（windows）
2013-12-12 13:32 170871、下载mongodb的windows版本，注意下载你操作系统 ... -
(转)保障MySQL安全的14个最佳方法
2013-11-23 13:35 1396MySQL数据库一贯以高性 ... -
redhat5下安装MySQL-5.6，默认密码无法进入的解决办法
2013-08-10 21:00 4969redhat5下安装MySQL-5.6，默认装完没有设 ... -
Oracle TNS:监听程序当前无法识别连接描述符中请求的服务
2012-11-12 15:14 11004当数据库服务一切启动正常，但连接数据库时报如下错误： ... -
oracle监听无法启动
2012-10-27 18:05 1846我这描述的无法启动Oracle监听是安装完成时可以正 ... -
Oracle执行计划 讲解（二）
2012-07-31 18:27 1976现在我们讲讲oracle执行计划里面每个参数的含义 我们以下面 ... -
Oracle 数据库导入,导出命令
2012-07-17 15:17 1381Oracle 数据导入导出imp/exp就相当于oracle数 ... -
提高游标批量读取的效率
2012-07-12 16:00 1692通过bulk collect减少loop处 ... -
提高游标批量读取的效率
2012-07-12 15:48 2通过bulk collect减少loop处 ... -
oracle dblink创建及其使用和使用中问题解决
2012-07-04 15:34 203921.创建dblink的语法： CREATE [PUBLIC] ... -
Oracle 用户的相关操作
2012-06-18 16:49 1236创建用户jade： create user jade id ... -
表空间的相关操作
2012-06-18 11:07 1306查看oracle所有的表空间的使用情况： 方法一、 selec ... -
oracle 表分区
2012-06-13 15:43 1007从以下几个方面来整理关于分区表的概念及操作: 1.表 ... -
Oracle 时间的应用小技巧
2012-06-13 09:27 32231、取得当前的日期 select sysdate from d ... -
Oracle中对时间的小技巧
2012-06-13 09:14 21、取得当前的日期 select sysdate from d ... -
字符函数
2012-04-23 21:39 1060一、字符函数——返回字符值 这些函数全都接收的是字符族 ... -
pl/sql 执行计划
2012-04-11 17:41 1372一段SQL代码写好以后， ... -
SQL优化规则
2012-04-06 13:24 10841、使用索引来更快地遍历表。 缺省情况下建立的索引是非 ... -
提高SQL查询性能
2012-03-29 22:53 1088适当遵循一些原则 ...
相关推荐
- `Oracle数据库(执行计划)第6讲.ppt`可能包含了讲解执行计划的第六部分内容，包括更深入的案例分析和技巧分享。 - `执行计划.txt`可能包含了一次或多次执行计划的输出，帮助我们理解执行过程和优化方向。 - `...
执行计划管理（SQL Plan Management, SPM）是Oracle 11g引入的一个新特性，用于管理和固定SQL语句的执行计划，防止由于执行计划的改变导致性能下降。在11g之前，管理员可以通过存储大纲（Stored Outlines）或SQL ...
【Oracle执行计划和SQL调优】是数据库管理中至关重要的环节，主要涉及到如何高效地运行SQL语句，提高数据库性能。下面将详细讲解执行计划的相关概念以及SQL调优的策略。 1. **Rowid的概念**：Rowid是Oracle数据库中...
在讲解执行计划时，创建了一个名为PLAN_TABLE的表，用于存储通过EXPLAIN PLAN获取的信息。这个表包含了各种执行计划相关的列，如操作类型、成本、行数预测等。 7. **Autotrace输出**： 在示例中，设置了AUTOTRACE...
本文档介绍了在Oracle中固定执行计划的三种方法，分别是outline、SQLProfile和SPM（SQL Plan Management），并且着重讲解了SQLProfile和SPM的使用。 1. Outline方法 Outline是一种较老的技术，在Oracle的9i版本中就...
标题与描述均指向了“Oracle数据库详细讲解”，这意味着文章将深度探讨Oracle数据库的相关知识，包括其功能、操作、管理及应用技巧。Oracle数据库是全球领先的数据库管理系统之一，由Oracle公司开发，广泛应用于企业...
PL/SQL包括变量、过程、函数、异常处理等元素，是Oracle数据库中不可或缺的一部分。 "T6_oracle索引、视图与序列.ppt"涵盖了数据库性能优化的重要概念。索引能显著提高查询速度，但也会增加写操作的开销；视图可以...
下面我们将详细讲解 oracle 中的定时执行存储过程。 一、创建简单的表 在 oracle 中，创建一个简单的表可以使用以下 SQL 语句： ```sql create table testdate( name varchar2(20), createdate date ); ``` 这...
本篇将详细讲解如何利用shell脚本来批量执行Oracle数据库脚本，以及涉及的相关知识点。 首先，我们来看标题中的"linux下批量执行oracle脚本的shell脚本"，这指的是在Linux操作系统中，通过编写shell脚本来自动化...
而SQL优化则是通过分析执行计划，选择最佳的访问路径和操作顺序来提高查询速度。 Oracle函数是SQL查询中不可或缺的部分。"oracle函数大全"可能包含了如数学函数、字符串函数、日期时间函数、转换函数等。例如，数学...
对于初学者来说，这份"Oracle很详细的讲解.pdf"文档无疑是一个宝贵的资源，它能以易于理解的方式介绍Oracle的各种概念和技术，帮助读者快速入门并逐步深入。无论你是打算成为数据库管理员还是开发者，理解并掌握...
Oracle进程则执行各种任务，如数据I/O、事务处理和安全管理。 Oracle数据库的"物理结构"由数据文件、重做日志文件和控制文件组成，这些都是操作系统的文件。而"逻辑结构"则包括表空间、段、范围、数据块和模式对象...
Oracle 触发器是一种特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。它主要有以下几个功能： 1. 允许/限制对表的修改：触发器可以控制对表的修改操作，例如限制某些用户对表的...
### 结合实例深入讲解Oracle中的直方图Histogram #### 一、直方图的基本概念 直方图是一种统计图表，在多种领域中都有广泛的应用，它并非Oracle数据库所独有的功能。直方图通常用来描绘一组数据的分布情况，通过一...
Oracle数据库系统是全球广泛使用的大型关系数据库管理系统，它在企业级数据存储、管理和处理方面具有卓越性能。Oracle的客户端/服务器架构...《Oracle语法实例讲解》这样的资源可以帮助你进一步提升Oracle技能。
本文将详细讲解如何在Oracle环境中利用批处理文件同时执行多个.sql文件，以及相关的工具和源码应用。 首先，Oracle提供了一种名为SQL*Plus的命令行工具，它是与Oracle数据库交互的主要接口之一，非常适合进行批处理...
Oracle数据库是甲骨文公司开发的一种关系型数据库管理系统，广泛应用于大型数据存储和管理。Oracle的详细介绍包括其软件体系结构、数据库基础、SQL语法、数据字典、数据类型、体系结构、数据操作语言(DDL)、数据操作...
Oracle触发器是数据库对象，用于在特定的数据库操作（如INSERT、UPDATE或DELETE）发生时自动执行一段PL/SQL代码。触发器可以帮助实现业务规则、数据验证和审计功能。以下是一些实例，详细解释了如何创建和使用Oracle...
成本基础优化器（CBO）是Oracle SQL优化器的一种，它基于统计信息和成本模型来决定执行计划。CBO会根据可能的成本选择最优的查询执行路径。 11. 如何统计数据库数据 在Oracle中，统计数据库数据是为了让优化器更好...
通过分析SQL执行计划、调整索引和内存参数，可以有效提升数据库性能。 七、安全性 Oracle 10g提供了丰富的安全特性，包括用户权限管理、角色、审计和透明数据加密。理解如何设置合适的权限、实施审计策略以及保护...