數(shù)據(jù)庫原理理解

oracle 體系結(jié)構(gòu)

共享池是oracle緩存程序數(shù)據(jù)的地方
庫高速緩存：執(zhí)行過的每一句ＳＱＬ語句，都存有解析后的內(nèi)容
解析包括語句的語法，檢驗提及的對象，以及確認對象的用戶權(quán)限。
數(shù)據(jù)字典高速緩存區(qū)：oracle使用的系統(tǒng)參數(shù)
使用最近最少使用算法(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

三條語句，返回相同的結(jié)果，但oracle認為他們是不同的，因為oracle首先將字符串轉(zhuǎn)換為散列值。這個散列值就是放在庫高速緩存中的關(guān)鍵字。

在解析中綁定變量

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為了讀取存放在庫高速緩存或者其它內(nèi)存結(jié)構(gòu)中的信息時必須獲得的一種鎖。鎖存器可以保護庫高速速緩存或其它內(nèi)存結(jié)構(gòu)中的信息被兩個同時進行的會話修改，或一個會話正要讀取的信息被另一個會話修改而導(dǎo)致的損壞。

互拆鎖：是一個序列化組件，用來阻止多個線程同時訪問一個共享結(jié)構(gòu)。優(yōu)點是占用內(nèi)存少，可以快速獲取或釋放。
語法解析仍然要使用鎖存器。

oracle獲取鎖存器的頻率越多，越可能觸發(fā)爭奪。越需要等待較長的時間。因此正確編寫代碼，較少使用鎖存器，也就是硬解析，是非常重要的。

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;

應(yīng)開發(fā)出更多重用共享池和緩沖區(qū)緩存中信息的代碼。

查詢轉(zhuǎn)換

發(fā)生在查詢執(zhí)行完語法和權(quán)限檢查之后，優(yōu)化器為了決定最終的執(zhí)行計劃而為不同的計劃計算成本預(yù)估之前。轉(zhuǎn)換和優(yōu)化是兩個不同的任務(wù)。

• 視圖合并

    --對于大多數(shù)select-project-join(SPJ)類型查詢會自動應(yīng)用簡單視圖合并
    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或外關(guān)聯(lián)時，就會使用復(fù)雜的視圖合并，從而消除包含聚合計算的視圖，使用更少的資源生成結(jié)果集。
    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;
    
    --關(guān)閉_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，也不會進行視圖合并

• 子查詢解嵌套

    --不相關(guān)子查詢的解嵌套轉(zhuǎn)換

    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提示
    --不進行查詢轉(zhuǎn)換將會選用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);

    --相關(guān)子查詢的解嵌套轉(zhuǎn)換
    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);
    --相關(guān)列變成了聯(lián)結(jié)條件而子查詢的剩余部分用來生成內(nèi)嵌視圖

• 聯(lián)結(jié)消除

    --主外鍵表消除
    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表的聯(lián)結(jié)是如何完全消除的，之所以能夠消除，是因為departments表中沒有任何一列出現(xiàn)在查詢列表中。并且由于主－外鍵約束，使得對于employees表中的每一行，在departments表中最多有一行匹配的記錄。

/*
外聯(lián)結(jié)表消除　外聯(lián)表確保employees表中的每一行在結(jié)果集中至少出現(xiàn)一次，在jobs.job_id列上的唯一健約束，確保了對于employees表中的
每一行，在jobs表中最多有一行與之相匹配。這兩個屬性保證了employees表中的每一行在結(jié)果集中出現(xiàn)并且僅出現(xiàn)一次。
*/
  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:
如果在查詢的任何地方使用了聯(lián)結(jié)鍵，則不支持聯(lián)結(jié)消除。
如果主外鍵約束包含多個列，則不支持聯(lián)結(jié)消除。

• 排序消除

--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提示，讓優(yōu)華器不要對查詢進行移除排序的轉(zhuǎn)換
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);

• 使用物化視圖重寫查詢

--準備測試數(shù)據(jù)
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提示來打開查詢重寫轉(zhuǎn)換
 --通過guery-rewrite_enabled參數(shù)默認啟用的
 
 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);

當發(fā)生硬解析時，oracle將會確定哪個計劃對于該查詢是最優(yōu)的。就是oracle訪問查詢所使用的對象并返回相應(yīng)結(jié)果數(shù)據(jù)將會采用的一系列步驟。
統(tǒng)計信息包括針對對象如表和索引收集統(tǒng)計信息，系統(tǒng)統(tǒng)計信息。
優(yōu)化器是oracle內(nèi)核中的代碼路徑，負責(zé)為查詢確定最佳執(zhí)行計劃（使用統(tǒng)計信息）。

執(zhí)行一個ＳＱＬ查詢，解析，綁定，執(zhí)行，提取的步驟。

一次fetch調(diào)用將會訪問緩沖區(qū)緩存中的一個或多個數(shù)據(jù)塊。每次訪問一個數(shù)據(jù)塊時，oracle都會從該塊中取出數(shù)據(jù)行然后在一次回路中返回給客戶端。一次返回的行數(shù)是可配置的。
SQL*Plus 默認為15, 通過　set arraysize n來更改
jdbc 默認為1０, 通過　((OracleConnection)conn).setDefaultRowPrefetch(n)來更改。

列大小是如影響邏輯讀取的

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

已選擇9行。


統(tǒng)計信息
----------------------------------------------------------
          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行。


統(tǒng)計信息
----------------------------------------------------------
          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行。


統(tǒng)計信息
----------------------------------------------------------
          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

ＳＱＬ語句執(zhí)行時的步驟匯總

Reference:

Oracle Concepts Guide