oracle学习笔记2:SQL执行

作者: 猫猫_tomluo | 来源:发表于2016-07-07 21:39 被阅读300次

数据库原理理解

oracle 体系结构

共享池是oracle缓存程序数据的地方
库高速缓存：执行过的每一句ＳＱＬ语句，都存有解析后的内容
解析包括语句的语法，检验提及的对象，以及确认对象的用户权限。
数据字典高速缓存区：oracle使用的系统参数
使用最近最少使用算法(Least Recently Used, LRU),用来管理共享池中的对象。
写ＳＱＬ语句时，一定要考虑如果高效的使用共享池。

共享池

SQL> select sql_text,sql_id,child_number,hash_value,executions from v$sql where upper(sql_text) like '%EMPLOYEES%';
 
SQL_TEXT                                                                         SQL_ID        CHILD_NUMBER HASH_VALUE EXECUTIONS
-------------------------------------------------------------------------------- ------------- ------------ ---------- ----------
 select * from employees where department_id=60                                  3advtjun8csb4            0 2827379044          3
 select /* a comment */ * from employees where department_id=60                  fs9k1uvtkk817            0 4079558695          1
 SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=60                                  86sbrvcmd3mv5            0  651284325          1

三条语句，返回相同的结果，但oracle认为他们是不同的，因为oracle首先将字符串转换为散列值。这个散列值就是放在库高速缓存中的关键字。

在解析中绑定变量

SQL> variable v_dept number
SQL> exec :v_dept := 10
SQL> select * from employees where department_id = :v_dept;
SQL> exec :v_dept := 60
SQL> select * from employees where department_id = :v_dept;

锁存器是oracle为了读取存放在库高速缓存或者其它内存结构中的信息时必须获得的一种锁。锁存器可以保护库高速速缓存或其它内存结构中的信息被两个同时进行的会话修改，或一个会话正要读取的信息被另一个会话修改而导致的损坏。

互拆锁：是一个序列化组件，用来阻止多个线程同时访问一个共享结构。优点是占用内存少，可以快速获取或释放。
语法解析仍然要使用锁存器。

oracle获取锁存器的频率越多，越可能触发争夺。越需要等待较长的时间。因此正确编写代码，较少使用锁存器，也就是硬解析，是非常重要的。

Paste_Image.png

测试物理读和逻辑读
E:\plustrce.sql

set echo on

drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

set echo off

SQL> conn sys/0524 as sysdba;
SQL> @'E:\plustrce.sql'
SQL>grant plustrace to scott;
SQL> grant all on employees to scott;
SQL> conn scott/scott
SQL> alter system flush buffer_cache;
SQL> alter system flush shared_pool;
SQL> set autotrace traceonly statistics;
SQL> select * from employees where department_id=60;
SQL> set autotrace off;
SQL> alter system flush buffer_cache;
SQL> alter system flush shared_pool;
SQL> set autotrace traceonly statistics;
SQL> select * from employees where department_id=60;
SQL> set autotrace off;

应开发出更多重用共享池和缓冲区缓存中信息的代码。

查询转换

发生在查询执行完语法和权限检查之后，优化器为了决定最终的执行计划而为不同的计划计算成本预估之前。转换和优化是两个不同的任务。

视图合并

    --对于大多数select-project-join(SPJ)类型查询会自动应用简单视图合并
    select *
    from orders o, (select sales_rep_id from orders) o_view
    where o.sales_rep_id = o_view.sales_rep_id(+)
    and o.order_total > 10000;
    
    --不使用提示
    select *
    from orders o, (select /*+ NO_MERGE */ sales_rep_id from orders) o_view
    where o.sales_rep_id = o_view.sales_rep_id(+)
    and o.order_total > 10000;
    
    --不使用提示
    --当查询包含聚合计算如group by, distinct或外关联时，就会使用复杂的视图合并，从而消除包含聚合计算的视图，使用更少的资源生成结果集。
    select e1.last_name,e1.salary,v.avg_salary
    from employees e1,
    (select  department_id,avg(salary) avg_salary from employees e2 group by department_id) v
    where e1.department_id=v.department_id and e1.salary>v.avg_salary;
    
    --使用merge提示
    select /*+ MERGE(v) */ e1.last_name,e1.salary,v.avg_salary
    from employees e1,
    (select  department_id,avg(salary) avg_salary from employees e2 group by department_id) v
    where e1.department_id=v.department_id and e1.salary>v.avg_salary;
    
    --关闭_complex_view_merging
    alter session set "_complex_view_merging"=FALSE;
    
    
    explain plan for 
    select /*+ MERGE(v) */ e1.last_name,e1.salary,v.avg_salary
    from employees e1,
    (select  department_id,avg(salary) avg_salary from employees e2 group by department_id) v
    where e1.department_id=v.department_id and e1.salary>v.avg_salary;
    select * from table(dbms_xplan.display);
    --如果_complex_view_merging=False,即使用了MERGE，也不会进行视图合并

子查询解嵌套

    --不相关子查询的解嵌套转换

    select *
      from employees
     where employee_id in (select manager_id from departments);

    explain plan for 
    select *
      from employees
     where employee_id in (select manager_id from departments);

    select * from table(dbms_xplan.display);

    --使用NO_UNNEST提示
    --不进行查询转换将会选用filter运算而不是nested loops连接 filter效率更低
    select *
      from employees
     where employee_id in (select /*+ NO_UNNEST */ manager_id from departments);

    explain plan for
    select *
      from employees
     where employee_id in (select /*+ NO_UNNEST */ manager_id from departments);
     
     select * from table(dbms_xplan.display);

    --相关子查询的解嵌套转换
    select outer.employee_id,
           outer.last_name,
           outer.salary,
           outer.department_id
      from employees outer 
      where outer.salary >
                 (select avg(inner.salary)
                    from employees
                   inner where inner.department_id = outer.department_id);
                   

    explain plan for
    select outer.employee_id,
           outer.last_name,
           outer.salary,
           outer.department_id
      from employees outer 
      where outer.salary >
                 (select avg(inner.salary)
                    from employees
                   inner where inner.department_id = outer.department_id);

    select * from table(dbms_xplan.display);
    --相关列变成了联结条件而子查询的剩余部分用来生成内嵌视图

联结消除

    --主外键表消除
    select e.*
      from employees e, departments d
     where e.department_id = d.department_id;

    explain plan for
      select e.*
        from employees e, departments d
       where e.department_id = d.department_id;

    select * from table(dbms_xplan.display);

departments表的联结是如何完全消除的，之所以能够消除，是因为departments表中没有任何一列出现在查询列表中。并且由于主－外键约束，使得对于employees表中的每一行，在departments表中最多有一行匹配的记录。

/*
外联结表消除　外联表确保employees表中的每一行在结果集中至少出现一次，在jobs.job_id列上的唯一健约束，确保了对于employees表中的
每一行，在jobs表中最多有一行与之相匹配。这两个属性保证了employees表中的每一行在结果集中出现并且仅出现一次。
*/
  select e.first_name, e.last_name, e.job_id
    from employees e, jobs j
   where e.job_id = j.job_id(+);
  
  explain plan for
    select e.first_name, e.last_name, e.job_id
      from employees e, jobs j
     where e.job_id = j.job_id(+);
  
  select * from table(dbms_xplan.display);

Ｎote:
如果在查询的任何地方使用了联结键，则不支持联结消除。
如果主外键约束包含多个列，则不支持联结消除。

排序消除

--order by消除
select count(*)
  from (select d.department_name
          from departments d
         where d.manager_id = 7
         order by d.department_name);

explain plan for
  select count(*)
    from (select d.department_name
            from departments d
           where d.manager_id = 7
           order by d.department_name);

select * from table(dbms_xplan.display);

--使用no_query_transformation提示，让优华器不要对查询进行移除排序的转换
select /*+ no_query_transformation */
 count(*)
  from (select d.department_name
          from departments d
         where d.manager_id = 7
         order by d.department_name);

explain plan for
  select /*+ no_query_transformation */
   count(*)
    from (select d.department_name
            from departments d
           where d.manager_id = 7
           order by d.department_name);

select * from table(dbms_xplan.display);

谓词推进

--谓词推进
select e1.last_name, e1.salary, v.avg_salary
  from employees e1,
       (select department_id, avg(salary) avg_salary
          from employees e2
         group by department_id) v
 where e1.department_id = v.department_id
   and e1.salary > v.avg_salary
   and e1.department_id = 60;

explain plan for
  select e1.last_name, e1.salary, v.avg_salary
    from employees e1,
         (select department_id, avg(salary) avg_salary
            from employees e2
           group by department_id) v
   where e1.department_id = v.department_id
     and e1.salary > v.avg_salary
     and e1.department_id = 60;

select * from table(dbms_xplan.display);

--rownum不仅会禁止谓词推进，而且也会禁止视图合并
select e1.last_name, e1.salary, v.avg_salary
  from employees e1,
       (select department_id, avg(salary) avg_salary
          from employees e2
         where rownum > 1 --rownum prohibits predicate pushing!
         group by department_id) v
 where e1.department_id = v.department_id
   and e1.salary > v.avg_salary
   and e1.department_id = 60;
explain plan for
  select e1.last_name, e1.salary, v.avg_salary
    from employees e1,
         (select department_id, avg(salary) avg_salary
            from employees e2
           where rownum > 1 --rownum prohibits predicate pushing!
           group by department_id) v
   where e1.department_id = v.department_id
     and e1.salary > v.avg_salary
     and e1.department_id = 60;

select * from table(dbms_xplan.display);

使用物化视图重写查询


--准备测试数据
drop table sales;
create table sales(
channel_id number,
promo_id number,
cust_id number,
amount_sold  number,
time_id number,
prod_id number
);
drop table products;
create table products(
prod_id number,
prod_name varchar2(20)
);
drop table times;
create table times(
time_id number,
week_ending_day number
);

insert into products values(1,'衣服');
insert into products values(2,'鞋子');
insert into products values(3,'裤子');

insert into times values(1,1);
insert into times values(2,3);
insert into times values(3,5);

insert into sales values(1,1,1000,500,1,1);
insert into sales values(1,1,1000,500,2,2);
insert into sales values(1,1,1000,500,3,3);
insert into sales values(1,1,1000,500,1,3);
insert into sales values(1,1,1000,500,2,2);

--使用物化视图进行查询重写
select p.prod_id,
       p.prod_name,
       t.time_id,
       t.week_ending_day,
       s.channel_id,
       s.promo_id,
       s.cust_id,
       s.amount_sold
  from sales s, products p, times t
 where s.time_id = t.time_id
   and s.prod_id = p.prod_id;
   
   explain plan for
   select p.prod_id,
       p.prod_name,
       t.time_id,
       t.week_ending_day,
       s.channel_id,
       s.promo_id,
       s.cust_id,
       s.amount_sold
  from sales s, products p, times t
 where s.time_id = t.time_id
   and s.prod_id = p.prod_id;
   
   select * from table(dbms_xplan.display);

create materialized view sales_time_product_mv
enable query rewrite as 
  select p.prod_id,
       p.prod_name,
       t.time_id,
       t.week_ending_day,
       s.channel_id,
       s.promo_id,
       s.cust_id,
       s.amount_sold
  from sales s, products p, times t
 where s.time_id = t.time_id
   and s.prod_id = p.prod_id;
 
 select /*+ rewrite(sales_time_product_mv) */
  p.prod_id,
  p.prod_name,
  t.time_id,
  t.week_ending_day,
  s.channel_id,
  s.promo_id,
  s.cust_id,
  s.amount_sold
   from sales s, products p, times t
  where s.time_id = t.time_id
    and s.prod_id = p.prod_id;
    
 --rewrite提示来打开查询重写转换
 --通过guery-rewrite_enabled参数默认启用的
 
 explain plan for
   select /*+ rewrite(sales_time_product_mv) */
    p.prod_id,
    p.prod_name,
    t.time_id,
    t.week_ending_day,
    s.channel_id,
    s.promo_id,
    s.cust_id,
    s.amount_sold
     from sales s, products p, times t
    where s.time_id = t.time_id
      and s.prod_id = p.prod_id;
 
 select * from table(dbms_xplan.display);

当发生硬解析时，oracle将会确定哪个计划对于该查询是最优的。就是oracle访问查询所使用的对象并返回相应结果数据将会采用的一系列步骤。
统计信息包括针对对象如表和索引收集统计信息，系统统计信息。
优化器是oracle内核中的代码路径，负责为查询确定最佳执行计划（使用统计信息）。

执行一个ＳＱＬ查询，解析，绑定，执行，提取的步骤。

一次fetch调用将会访问缓冲区缓存中的一个或多个数据块。每次访问一个数据块时，oracle都会从该块中取出数据行然后在一次回路中返回给客户端。一次返回的行数是可配置的。
SQL*Plus 默认为15, 通过　set arraysize n来更改
jdbc 默认为1０, 通过　((OracleConnection)conn).setDefaultRowPrefetch(n)来更改。

列大小是如影响逻辑读取的

SQL> set arraysize 5
SQL> select * from employees;

已选择9行。


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
       1282  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> set arraysize 15
SQL> select * from employees;

已选择9行。


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1152  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL> set arraysize 45
SQL> select * from employees;

已选择9行。


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1152  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

ＳＱＬ语句执行时的步骤汇总

Reference:

Oracle Concepts Guide

oracle学习笔记2:SQL执行

查询转换

Reference:

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

首页投稿（暂停使用，暂停投稿）

程序员

iOS开发

编程学习

dotNET

Oracle数据库管理之道

我爱编程