3.读书笔记收获不止Oracle之 物理体系

3.读书笔记收获不止Oracle之 物理体系

           

 

1.  体系结构

来看一张被看烂了的，但是可能没有完全理解的图1

 

2.  体系结构原理初探

当用户连接进来的时候，会在PGA中给用户创建私有的内存空间，只要改SESSION不断开连接，下次系统不用再去硬盘中读取数据，而直接从PGA内存区中获取。

           如果用户执行一个SQL语句，

           SQL会立即匹配成一条唯一的HASH值，然后进入SGA的共享池中。

           在共享池内查看是否存储过这个SQL指令（通过HASH值），如果没有，先查询语句语法、语义的正确性，是否有权限，如果都没有问题，那么这个SQL的HASH值就存储下来。

然后进行解析，解析就是生成、选择执行计划。和之前的HASH值对应在一起。

           然后就去数据缓存区取自己想要的数据了。

           如果数据缓存区中没有，就只能去磁盘中找了。

2.1      实验

创建一个用户比如toad

create user toad identifiedby toad;

grant sysdba to toad;

create table  t as select * from all_objects;

Create index idx_object_idon t(object_id);

Set autotrace on

Set linesize 10000

Set timing on

其中set autotrace on是开始跟踪SQL的执行计划和执行的统计信息。

Set timing on表示跟踪该语句执行完成的时间。

 

执行

SQL>Select object_namefrom t where object_id=29;

得到的执行计划如下：

OBJECT_NAME

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

C_COBJ#

 

Elapsed: 00:00:00.05

 

Execution Plan

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

Plan hash value:1296629646

 

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

| Id  | Operation                               | Name          | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                    |                       |           1 |          79 |            2  (0)| 00:00:01 |

|   1 | TABLE ACCESS BY INDEX ROWID BATCHED| T       |           1 |          79 |            2  (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN                         | IDX_OBJECT_ID |      1 |             |           1  (0)| 00:00:01 |

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

 

PredicateInformation (identified by operation id):

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

 

   2 - access("OBJECT_ID"=29)

 

Note

-----

   - dynamic statistics used: dynamic sampling(level=2)

 

 

Statistics

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

            11 recursive calls

             0  dbblock gets

            96 consistent gets

             1 physical reads

            0  redo size

           550 bytes sent via SQL*Net to client

           551 bytes received via SQL*Net from client

            2  SQL*Net roundtrips to/fromclient

            0  sorts (memory)

            0  sorts (disk)

            1  rows processed

 

再执行一次得到如下图：

SQL> Select object_name from t whereobject_id=29;

 

OBJECT_NAME

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

C_COBJ#

 

Elapsed: 00:00:00.01

 

Execution Plan

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

Plan hash value: 1296629646

 

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

| Id | Operation                               | Name          | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0| SELECT STATEMENT                    |                       |           1 |          79 |            2  (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID BATCHED| T      |           1 |          79 |            2  (0)| 00:00:01 |

|*  2|   INDEX RANGE SCAN                         | IDX_OBJECT_ID |      1 |             |           1  (0)| 00:00:01 |

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

 

Predicate Information (identified byoperation id):

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

 

   2- access("OBJECT_ID"=29)

 

Note

-----

   -dynamic statistics used: dynamic sampling (level=2)

 

 

Statistics

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

             0  recursive calls

             0  db block gets

             4  consistent gets

             0  physical reads

             0  redosize

           550  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

1      rows processed

 

第一次执行时间更长，而且需要的成本也更高。

第一次需要       11  次递归低啊用，96次逻辑读和1次物理读。

而第二次，只需要 4次逻辑读就可以了。

差异有如下几点：

1、  用户执行该SQL指令时，从磁盘中获取用户连接信息和相关权限信息，并保存在PGA中。再次执行就可以再PGA直接获取，避免了物理读。

2、  首次执行SQL指令后，SGA的共享池里已经保存了该SQL唯一指令HASH值，并保留了语法语义检查及执行计划等相关解析动作。

3、  首次执行SQL时，数据一般不再SGA中，只能从磁盘获取，不可避免的产生了物理读。第二次执行的时候就不需要物理读了，数据全在缓存中，效率高得多。

 

 

2.2      关于ORACLE 的代价

使用HINT来做实验

HINT可以强行让某SQL语句不走索引，或者强行让某SQL语句走索引。

在原来的语句如下

Select object_name from t whereobject_id=29;

增加HINT后如下(可以先清除下sharepool : alter system flush SHARED_POOL;)：

Select  /*+full(t)*/object_name from t where object_id=29;

 

SQL> Select  /*+full(t)*/ object_name from t whereobject_id=29;

 

OBJECT_NAME

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

C_COBJ#

 

Elapsed:00:00:00.02

 

Execution Plan

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

Plan hash value: 1601196873

 

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

| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time        |

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

|   0| SELECT STATEMENT  |        |    1 |    30 |   412  (1)| 00:00:01 |

|*  1|  TABLE ACCESS FULL| T       |    1 |    30 |   412  (1)| 00:00:01 |

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

 

Predicate Information (identified byoperation id):

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

 

   1- filter("OBJECT_ID"=29)

 

 

Statistics

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

            41  recursive calls

             0  dbblock gets

      1567  consistent gets

             1  physical reads

             0  redosize

           550  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             4 sorts (memory)

             0 sorts (disk)

1      rows processed

 

 

再执行以下如下：

SQL> Select /*+full(t)*/ object_name from t where object_id=29;

 

OBJECT_NAME

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

C_COBJ#

 

Elapsed: 00:00:00.01

 

Execution Plan

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

Plan hash value: 1601196873

 

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

| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time        |

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

|   0| SELECT STATEMENT  |        |    1 |    30 |   412  (1)| 00:00:01 |

|*  1|  TABLE ACCESS FULL| T       |    1 |    30 |   412  (1)| 00:00:01 |

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

 

Predicate Information (identified byoperation id):

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

 

   1- filter("OBJECT_ID"=29)

 

 

Statistics

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

             0  recursive calls

             0  db block gets

       1516 consistent gets

             0  physical reads

             0  redosize

           550  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

 

第二次执行后，消除了共享池解析、减少甚至消除物理读以及递归调用。

虽然不是特别明显的，但是时间上这个查询是走索引快一点的，

所以ORACLE是自动选择的索引方式，因为所以更快一点。