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-缓冲区缓存
逻辑读取:数据块在缓冲区缓存中,从缓存中读取。
物理读取:数据块不在缓存中,从磁盘中读取。
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
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
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.
select * from hr.employees e where e.department_id in (select d.department_id from hr.departments d)
select * from hr.employees e where e.department_id in (select /*+ NO_UNNEST*/ d.department_id from hr.departments d)
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
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 推进到视图中。
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