數(shù)據(jù)庫原理理解
oracle 體系結(jié)構(gòu)
共享池是oracle緩存程序數(shù)據(jù)的地方
庫高速緩存：執(zhí)行過的每一句ＳＱＬ語句捆交，都存有解析后的內(nèi)容
解析包括語句的語法进萄，檢驗(yàn)提及的對(duì)象缤骨，以及確認(rèn)對(duì)象的用戶權(quán)限上鞠。
數(shù)據(jù)字典高速緩存區(qū)：oracle使用的系統(tǒng)參數(shù)
使用最近最少使用算法(Least Recently Used, LRU),用來管理共享池中的對(duì)象。
寫ＳＱＬ語句時(shí)顶岸，一定要考慮如果高效的使用共享池腔彰。
共享池
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認(rèn)為他們是不同的霹抛，因?yàn)閛racle首先將字符串轉(zhuǎn)換為散列值。這個(gè)散列值就是放在庫高速緩存中的關(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)中的信息時(shí)必須獲得的一種鎖杯拐。鎖存器可以保護(hù)庫高速速緩存或其它內(nèi)存結(jié)構(gòu)中的信息被兩個(gè)同時(shí)進(jìn)行的會(huì)話修改，或一個(gè)會(huì)話正要讀取的信息被另一個(gè)會(huì)話修改而導(dǎo)致的損壞世蔗。
互拆鎖：是一個(gè)序列化組件端逼，用來阻止多個(gè)線程同時(shí)訪問一個(gè)共享結(jié)構(gòu)。優(yōu)點(diǎn)是占用內(nèi)存少污淋，可以快速獲取或釋放裳食。
語法解析仍然要使用鎖存器。
oracle獲取鎖存器的頻率越多芙沥，越可能觸發(fā)爭(zhēng)奪诲祸。越需要等待較長的時(shí)間浊吏。因此正確編寫代碼，較少使用鎖存器救氯，也就是硬解析找田，是非常重要的。
Paste_Image.png
測(cè)試物理讀和邏輯讀
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í)行計(jì)劃而為不同的計(jì)劃計(jì)算成本預(yù)估之前。轉(zhuǎn)換和優(yōu)化是兩個(gè)不同的任務(wù)甲抖。
- 視圖合并
--對(duì)于大多數(shù)select-project-join(SPJ)類型查詢會(huì)自動(dòng)應(yīng)用簡(jiǎn)單視圖合并
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;
--不使用提示
--當(dāng)查詢包含聚合計(jì)算如group by, distinct或外關(guān)聯(lián)時(shí)漆改，就會(huì)使用復(fù)雜的視圖合并，從而消除包含聚合計(jì)算的視圖准谚，使用更少的資源生成結(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，也不會(huì)進(jìn)行視圖合并
- 子查詢解嵌套
--不相關(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提示
--不進(jìn)行查詢轉(zhuǎn)換將會(huì)選用filter運(yùn)算而不是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é)是如何完全消除的柱衔，之所以能夠消除樊破，是因?yàn)閐epartments表中沒有任何一列出現(xiàn)在查詢列表中。并且由于主－外鍵約束唆铐，使得對(duì)于employees表中的每一行哲戚，在departments表中最多有一行匹配的記錄。
/*
外聯(lián)結(jié)表消除 外聯(lián)表確保employees表中的每一行在結(jié)果集中至少出現(xiàn)一次艾岂，在jobs.job_id列上的唯一健約束顺少，確保了對(duì)于employees表中的
每一行，在jobs表中最多有一行與之相匹配王浴。這兩個(gè)屬性保證了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é)消除叼耙。
如果主外鍵約束包含多個(gè)列，則不支持聯(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)華器不要對(duì)查詢進(jìn)行移除排序的轉(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);
- 謂詞推進(jìn)
--謂詞推進(jìn)
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不僅會(huì)禁止謂詞推進(jìn)，而且也會(huì)禁止視圖合并
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);
- 使用物化視圖重寫查詢
--準(zhǔn)備測(cè)試數(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);
--使用物化視圖進(jìn)行查詢重寫
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ù)默認(rèn)啟用的
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);
當(dāng)發(fā)生硬解析時(shí)癞松，oracle將會(huì)確定哪個(gè)計(jì)劃對(duì)于該查詢是最優(yōu)的爽撒。就是oracle訪問查詢所使用的對(duì)象并返回相應(yīng)結(jié)果數(shù)據(jù)將會(huì)采用的一系列步驟。
統(tǒng)計(jì)信息包括針對(duì)對(duì)象如表和索引收集統(tǒng)計(jì)信息响蓉，系統(tǒng)統(tǒng)計(jì)信息硕勿。
優(yōu)化器是oracle內(nèi)核中的代碼路徑，負(fù)責(zé)為查詢確定最佳執(zhí)行計(jì)劃（使用統(tǒng)計(jì)信息）枫甲。
執(zhí)行一個(gè)ＳＱＬ查詢源武，解析扼褪，綁定，執(zhí)行粱栖，提取的步驟话浇。
一次fetch調(diào)用將會(huì)訪問緩沖區(qū)緩存中的一個(gè)或多個(gè)數(shù)據(jù)塊。每次訪問一個(gè)數(shù)據(jù)塊時(shí)闹究，oracle都會(huì)從該塊中取出數(shù)據(jù)行然后在一次回路中返回給客戶端幔崖。一次返回的行數(shù)是可配置的。
SQL*Plus 默認(rèn)為15, 通過 set arraysize n來更改
jdbc 默認(rèn)為1０, 通過 ((OracleConnection)conn).setDefaultRowPrefetch(n)來更改渣淤。
列大小是如影響邏輯讀取的
SQL> set arraysize 5
SQL> select * from employees;
已選擇9行赏寇。
統(tǒng)計(jì)信息
----------------------------------------------------------
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)計(jì)信息
----------------------------------------------------------
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)計(jì)信息
----------------------------------------------------------
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í)行時(shí)的步驟匯總
Reference:
Oracle Concepts Guide
