笔记：Oracle SQL 高级编程 第2章 SQL 执行

1、Oracle 架构基础

SGA：系统全局内存区，每一个实例只有一个

PGA：程序共享内存区，每一个服务器进程有一个

2. SGA 之 共享池

库高速缓存：解析每一句语句之前，Oracle会检查库高速缓存中是否存在相同语句，如果存在，则直接取出，若无再进行解析。通过最近最少使用算法，管理其中对象。

数据字典高速缓存：存储所有的数据库对象信息

3、库高速缓存

SQL解析过程：验证语句的语法，检验提及的对象，确认该对象的用户权限。

软解析：若SQL执行过，则从库高速缓存中取出解析的信息，直接使用。

硬解析：若SQL没有执行过，则做完全的SQL解析过程，生成执行计划，并放在缓存中以便重用。

软解析比硬解析快得多。

4、完全相同的语句

(1)  视图 v$sql 可以查询存放在库高速缓存中的语句。

(2)  此处的相同含义：完全的相同，不能有大小写、注释等不相同的情况。

如以下的几句是不相同的：

select * from employees where department_id = 60

SELECT * FROM employees WHERE department_id = 60

select   /*  my commment  */ * from employees where department_id = 60

(3)   尽量使用绑定变量，如以下语句:

select * from employees where department_id = :my_id

然后使用 不同的 my_id， 该语句还可以复用。

(4).  锁存器

锁存器可以保护库高速缓存避免被两个同时进行的会话修改，或一个会话正要读取信息被另一个会话修改而导致的损坏。

锁存器是串行的，影响性能和可扩展性，减少硬解析即减少使用锁存器。

5、SGA-缓冲区缓存

逻辑读取：数据块在缓冲区缓存中，从缓存中读取。

物理读取：数据块不在缓存中，从磁盘中读取。

 

6、查询转换

最常见的是将独立的查询转换为直接连接。

如：select * from A where  x in  (select x  from B where y=value) 会被转换为

select  a.*  from A a, B b where a.x = b.x and  b.y  = value

可以配置 NO_QUERY_TRANSFORMATION 禁用查询转换。

查询转换包括：

视图合并

子查询解嵌套

谓语前推

物化视图重新查询

7、视图合并

(1). 若SQL不包含解析函数、聚合函数、集合运算、Order By、ROWNUM 等，会默认开启视图合并：

select * from oe.orders o1 , (select o2.sales_rep_id from oe.orders o2) o_view
    where o1.sales_rep_id = o_view.sales_rep_id(+) and o1.order_total > 10000 ;

其执行计划为：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1066515912
--------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |   602 | 24080 |     3   (0)| 00:
|   1 |  NESTED LOOPS OUTER|                  |   602 | 24080 |     3   (0)| 00:
|*  2 |   TABLE ACCESS FULL| ORDERS           |   101 |  3737 |     3   (0)| 00:
|*  3 |   INDEX RANGE SCAN | ORD_SALES_REP_IX |     6 |    18 |     0   (0)| 00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("O1"."ORDER_TOTAL">10000)
   3 - access("O1"."SALES_REP_ID"="O2"."SALES_REP_ID"(+))
       filter("O2"."SALES_REP_ID"(+) IS NOT NULL)
17 rows selected

可见使用了视图合并。

可以用 NO_MERGE 注释防止视图合并：

select * from oe.orders o1 , (select /*+ NO_MERGE */ o2.sales_rep_id from oe.orders o2) o_view
where o1.sales_rep_id = o_view.sales_rep_id(+) and o1.order_total > 10000 ; 

其执行计划为：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2722607751
------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |   602 | 30100 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER    |        |   602 | 30100 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL | ORDERS |   101 |  3737 |     3   (0)| 00:00:01 |
|   3 |   VIEW              |        |   105 |  1365 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| ORDERS |   105 |   315 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("O1"."SALES_REP_ID"="O_VIEW"."SALES_REP_ID"(+))
   2 - filter("O1"."ORDER_TOTAL">10000)
17 rows selected

(2). 若SQL包含解析函数、聚合函数、集合运算、Order By、ROWNUM 等，会默认关闭视图合并：

select e1.last_name, e1.salary, v.avg_salary  from hr.employees e1 , 
(select e2.department_id, AVG(e2.salary) as avg_salary from hr.employees e2 group by e2.department_id) v
where e1.department_id = v.d and e1.salary > avg_salary. 

假如MERGE提示可以强制使用视图合并

select e1.last_name, e1.salary, v.avg_salary  from hr.employees e1 , 
(select /*+ MERGE(v) */ e2.department_id, AVG(e2.salary) as avg_salary from hr.employees e2 group by e2.department_id) v
where e1.department_id = v.d and e1.salary > avg_salary. 

8、子查询解嵌套

(1) . 子查询不相关的时候，默认开启子查询解嵌套，转化为表连接

select  * from hr.employees e where e.department_id in (select d.department_id from hr.departments d)  

可使用NO_UNNEST 强制关闭子查询解嵌套

select  * from hr.employees e where e.department_id in (select /*+ NO_UNNEST*/ d.department_id from hr.departments d)  

(2)  连接子查询也默认开启解嵌套，转化为内嵌视图。

如执行以下SQL

select o.* from outer o where o.salary > ( select avg(in.salary) from inner i where i.depart_id = o.depart_id  )

将会转为如下的表与视图连接：

select o.* from outer o, ( select depart_id, avg(salary) as avg_salary from inner  group by depart_id) i where  i.depart_id = o.depart_id  and o.salary > i.avg_salary

(3). 可在设置参数 unnest_subquery 开启或关闭。 

9、谓语前推

将过滤条件前移，尽快过滤掉不需要的数据。

 (1). 以下语句

select o.* from outer o, ( select depart_id, avg(salary) as avg_salary from inner  group by depart_id) i where  i.depart_id = o.depart_id  and o.salary > i.avg_salary where o.depart_id = 60

最后一个条件, o.depart_id 会推进到视图 i 中，使得视图中只需计算一个depart 的平均salary。


(2).  使用 rownum 会组织谓语前推，同时也会阻止视图合并

select o.* from outer o, ( select depart_id, avg(salary) as avg_salary from inner where rownum>1  group by depart_id) i where  i.depart_id = o.depart_id  and o.salary > i.avg_salary where o.depart_id = 60

在视图中添加了 rownum >1, 从而阻止条件 o.depart_id =60 推进到视图中。

10、物化视图

当一个查询被保存为一个物化视图，转换器将使用预先计算好的物化视图，而不需要执行当前查询的表。

例如，建立以下物化视图后

create materialized view table sales_time_product enable query rewrite as 
select s.*, t.*, p.* from sales s, time t, product p where s.time_id=t.time_id and s.product_id = p.product_id

以后再执行同样的查询，将从物化视图中查询。在sql 中添加 rewrite 提示，可以强制不再使用物化视图：

select  /*+ rewrite(sales_time_product) */ s.*, t.*, p.* from sales s, time t, product p where s.time_id=t.time_id and s.product_id = p.product_id

11、确定执行计划

oracle 完成sql 的解析后，会收集数据词典的统计信息，计算各种执行计划的成本，确定成本最低的执行计划。

统计信息包括有：

表统计信息：表空间、分块、行数、块数、平均行长度、状态 等等

column 统计信息：是否可为Null、非Null数、Null数、桶数、平均长度、最大值、最小值

index 统计信息：level、行数、key、uniq、聚集因子等。

12、执行计划并取得数据行

列大小(Arraysize)： 在每次的Fetch 过程中，每次返回的行数。SqlPlus默认每次返回15行，JDBC默认返回10行，通过SetDefaultRowPrefetch更改。

更大的Arraysize 可以减少fetch 调用次数，减少网络往返。

13、SQL执行总览