oracle学习笔记2:SQL执行

数据库原理理解
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

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,242评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,769评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,484评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,133评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,007评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,080评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,496评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,190评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,464评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,549评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,330评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,205评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,567评论 3赞 298
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,889评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,160评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,475评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,650评论 2赞 335

推荐阅读更多精彩内容