Oracle Sql高级编程 第2章 SQL 执行过程

Oracle架构基础

实例由系统全局内存区域( SGA）以及一系列的后台进程组成。每一个连接到数据库的用户都是通过一个客户端进程来进行管理的。客户端进程是与服务器进程相连接的。每个服务器进程都会被分配一块私有的内存区域，称为程序共享内存区域或进程共享内存区( PGA)。

---

SGA- 共享池

共享池是内存中最关键的部分之一，特别是对于SQL的执行来说。你写SQL语句的方法并不仅仅会影响到这一句SQL本身。数据库中正在执行的所有SQL语句结合在一起会因为它们对共享池的影响而对总体性能和可扩展性带来巨大的影响。

共享池是Oracle缓存程序数据的地方。执行过的每一条SQL在共享池中都存有解析后的内存。共享池中存储这些语句的地方称为 库高速缓存。即在解析每一件SQL之前，Oracle都会检查库高速缓存看是否已经存在相同的语句。如果存在，Oracle会直接从缓存中读取而不是在解析一遍。

因为共享池大小有限。使用最近最少使用算法在更新共享池。

在写SQL语句时，需要牢记：为了更高的使用共享池，语句需要可以共享。

---

库高速缓存

执行每一条SQL时必然会发生的事就是它必须被解析并装载到库高速缓存中。解析包括验证语句的语法，验证提及的对象，以及确认该对象的用户权限。如果检查都通过，下一步就是看语句之前是否执行过。如果执行过，oracle 会使用之前解析的信息，这种类型的解析叫   软解析。如果语句之前没有被执行过，那边oracle就将执行所有的工作来为当前语句生成 执行计划，并将它放在缓存中以便以后使用。这种解析叫 硬解析。

硬解析比软解析要做很多很多的工作。

---

完全相同的语句

可以通过查询 v$sql视图来查看存放在库高速缓存中的语句。在执行一条sql时，会首先将字符串转换为散列值。所有语句必须 严格一致，才会得到相同的散列值。所以任何把变量拼接到sql中的方式，其实都会造成硬解析。

---

锁存器是Oracle为了读取存放在库高速缓存或其他内存结构中的信息时必须获得的一种锁。在读取库高速缓存中的任何信息之前，Oracle都会获得一个锁存器，其他所有会话都必须等待，直到该锁存器被释放。如果锁存器已经被使用，Oralce基本上将会迭代轮询（使用CPU资源），继续查看锁存器是否可用，知道一段时间之后(隐藏参数_spin_count指定的次数，默认2000次),该请求就会被暂停，然后排到其他请求之后。

---

SGA- 缓冲区缓存

在数据库块从硬盘中读取出来后或写入硬盘之前，用于存储数据库块。块是Oracle进行操作的最小单位。块中含有表数据行或索引条目。Oracle必须读取块来获得SQL语句需要的数据行。

---

总的来说 共享池中保存要执行SQL相关信息，缓冲区中保存实际的数据块。现在来看下硬解析，软解析，物理读取，逻辑读取究竟差多少。

SQL>   alter system set events 'immediate trace name flush_cache';
System altered.
SQL>   alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly statistics

SQL> select * from sysadm.ps_job;
Statistics
----------------------------------------------------------
         5581  recursive calls
          0  db block gets
         1868  consistent gets
           35  physical reads
          0  redo size
      12977  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off

---

QL> alter system set events 'immediate trace name flush_cache';
System altered.
SQL>  set autotrace traceonly statistics
SQL>  select * from sysadm.ps_job;
Statistics
----------------------------------------------------------
            1  recursive calls
          0  db block gets
            7  consistent gets
            6  physical reads
          0  redo size
      12977  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

---

SQL> select * from sysadm.ps_job;
Statistics
----------------------------------------------------------
            0  recursive calls
          0  db block gets
          7  consistent gets
            0  physical reads
          0  redo size
      12977  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

---

查询转换

该步骤发生在一个查询进行完语法和权限检查后，优化器为了决定最终的执行计划而为不同的计划计算成本之前。

查询会被转换为一系列查询块。查询块是通过SELECT关键字定义的。例如 select * from employees 只有一个查询块，而select * from employees where deptid in (select deptid from departments) 有2个查询块。

查询转换的目的是确定如果改变查询的写法会不会有更好的执行计划。所以， 查询转换可能会重新查询。

查询转换器可能会改变最初所写的查询结构，只要不会影响结果集。最常进行的改变就是将独立的查询块转换为直接联结。例如 上面 带 IN 的查询会被转换为联结。

可以通过查看执行计划来了解是否发生了查询转换。也可以通过 NO_QUERY_TRANSFORMATION提示，禁止查询转换(除了条件前推)。

---

视图合并

视图合并是一种能将内嵌或存储式视图展开为能够独立分析或者与查询剩余部分合并成总体执行计划的查询转换。改写后的语句基本上不包含视图。转换后的查询将会由优化器进行复查，优化器会选择成本最低的执行计划。

例如：

SELECT   *

    FROM   PS_JOB A, PS_DEPT_TBL_VW B

  WHERE   A.DEPTID = B.DEPTID

     AND   A.SETID_DEPT = B.DEPTID;

 

 

SELECT   /*+ NO_QUERY_TRANSFORMATION */

 *

    FROM   PS_JOB A, PS_DEPT_TBL_VW B

  WHERE   A.DEPTID = B.DEPTID

     AND   A.SETID_DEPT = B.DEPTID

---