oracle水位线怎么看,oracle 高水位线查询

水位线是什么意思 水位线如何解释

1、水位线（High Water Mark）在工程地质、岩土工程、水利水电工程等专业的地质剖面图等专业图件中表示地表水或地下水水位的线，称作水位线。线型为虚线，符号为▽下面加三条长度依次减小的线段。

创新互联专注于企业网络营销推广、网站重做改版、龙胜网站定制设计、自适应品牌网站建设、HTML5建站、商城网站定制开发、集团公司官网建设、成都外贸网站制作、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为龙胜等各大城市提供网站开发制作服务。

2、所有的oracle段(segments，在此，为了理解方便，建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限，我们把这个上限称为high water mark或HWM。这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。

oracle 如何查看表 高水位线

select blocks, empty_blocks from dba_tables where table_name='xxx' and owner='xx';

blocks就是已经分配的空间即HWM，实际分配的空间，不是实际大小

如何看懂ORACLE执行计划

一、什么是执行计划

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.一个简单的例子：

SQL select /*+parallel (e 4)*/ * from emp e;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=7134)

1 0 TABLE ACCESS* (FULL) OF 'EMP' (Cost=1 Card=82 Bytes=7134):Q5000

--[:Q5000]表示是并行方式

1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."EMPNO"

,A1."ENAME",A1."JOB",A1."MGR",A1."HI

优化模式是CHOOSE的情况下，看Cost参数是否有值来决定采用CBO还是RBO：

SELECT STATEMENT [CHOOSE] Cost=1234--Cost有值，采用CBO

SELECT STATEMENT [CHOOSE] --Cost为空，采用RBO(9I是如此显示的)

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(我觉得应该这样翻译: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:

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. = = between) .

eg:SQL 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 /*+ index_ss(emp i_emp)*/ 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扫描是最快的访问数据方式

很精辟的oracle高水位线，终于知道DELETE和TRUNCATE为什么不一样

两个操作都是删除表数据的但是实际却又很大的不同

delete只是单纯的删掉表里的数据，他可以添加删除条件，但是不会将空间回收，删除的时候有记录日志，方便恢复，但是速度比较慢

truncate table是全表清空的操作，回收空间，无日志，基本上是不可恢复的