oracle

oracle是國企里用得最多的關(guān)系型數(shù)據(jù)庫了，其對大并發(fā)、大訪問量支持力度更好；在企業(yè)級應(yīng)用比例達到40%

作為java后端開發(fā)人員，需要關(guān)注的oracle知識點：大概的執(zhí)行的流程；常接觸的對象；常用的sql操作；執(zhí)行計劃；索引及優(yōu)化；復(fù)雜的統(tǒng)計腳本

大概的執(zhí)行的流程

3875084-442eb6d4fdbd304e.png

1. client請求oracle服務(wù)時，建立基于TCP長連接的一個session，oracle單獨為該連接分配一個處理進程PID和內(nèi)存區(qū)域PGA；

2. oracle判斷sql語句語法和對象權(quán)限是否有誤；

3. oracle進行語法分析，去SGA查找是否有該sql（哈希得到散列值）的執(zhí)行計劃。沒有時，基于成本或者優(yōu)先級的，生成最優(yōu)的執(zhí)行計劃；以防后續(xù)復(fù)用，緩存到SGA中的sharedpool；

4. 執(zhí)行執(zhí)行計劃，首先會去SGA中的database buffer cache（存儲數(shù)據(jù)塊，是oracle進行操作的最小單位）找數(shù)據(jù)，沒有的話從底層datafile中取數(shù)據(jù)并放入SGA中，便于后續(xù)復(fù)用；

5. 最后取到數(shù)據(jù)返回給client

會話查詢及死鎖查詢

在 Oracle 系統(tǒng)中能自動發(fā)現(xiàn)死鎖，并選擇代價最小的，即完成工作量最少的事務(wù)予以撤消，釋放該事務(wù)所擁有的全部鎖


-- 查出鎖住該表的會話id,serial#

SELECT o.object_name,s.sid, s.serial#

      FROM v$locked_object l, dba_objects o, v$session s

            WHERE l.object_id　= o.object_id

                  AND l.session_id = s.sid

      AND o.object_name='SJ_AFFAIR'

--查詢除了用戶的，還包括系統(tǒng)內(nèi)的對象     

SELECT o.object_name,s.sid, s.serial#

      FROM v$lock l, dba_objects o, v$session s

            WHERE l.id1　= o.object_id

                  AND l.SID = s.sid

--刪除掉被鎖住的會話

alter system kill session 'sid, serial#';

查詢后oracle清除緩存

性能測試的時候，遇到第一次讀取數(shù)據(jù)庫很慢，以后幾次都瞬間讀取完成。應(yīng)該是Oracle緩存的作用，第一次讀完以后放入緩存，以后讀取就很快了。


ALTER SYSTEM FLUSH SHARED_POOL

ALTER SYSTEM FLUSH BUFFER_CACHE

ALTER SYSTEM FLUSH GLOBAL CONTEXT

sql中關(guān)鍵字的執(zhí)行順序

FROM

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

UNION

ORDER BY

常接觸的對象

表、視圖、索引；物化視圖、觸發(fā)器、存儲過程、函數(shù)、DBLink；分區(qū)表；

table和index


-- 在要統(tǒng)計的時候來一次同步，調(diào)producer

begin dbms_stats.gather_table_stats(OWNNAME =>'CSID', TABNAME => 'dba_extents',METHOD_OPT => 'FOR ALL');

end;

--查看所有的對象

select t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.status

  from dba_objects t

where t.OWNER = 'GTJAKH'

  and t.OBJECT_TYPE = 'TABLE'

--table

select t.TABLE_NAME,

      t.NUM_ROWS,--表記錄數(shù)

      t.BLOCKS, --高水平線下的數(shù)據(jù)塊個數(shù)

      t.EMPTY_BLOCKS,

      t.AVG_SPACE,

      t.CHAIN_CNT,

      t.AVG_ROW_LEN--行平均長度

  from user_tables t

where t.table_name = 'NRNG_NEWMATCH';

--查看表中index

select t.index_name,

      t.num_rows,-- 索引行

      t.leaf_blocks,--索引葉塊數(shù)

      t.distinct_keys,--索引不同鍵數(shù)

      t.blevel,--btree深度

      t.avg_leaf_blocks_per_key,--

      t.avg_data_blocks_per_key,

      t.clustering_factor--索引的聚簇因子，標識表中存儲數(shù)據(jù)順序和索引字段順序的符合程度

  from user_indexes t

where t.table_name = 'LOG_KHYW_BUSI_HISTORY'

  and t.index_name = 'INDEX_KHYWHIST3_CREATTIME';

--或者查詢整個dba的dba_tables、dba_indexes

高水位線（HWM）隨著表記錄增多，存儲塊也增多，HWM也會升高；但刪除數(shù)據(jù)（delete，truncate不會）不會導(dǎo)致HWM降低，這樣全包掃描時，從頭一直讀到HWM線，會很耗時；所以需要table move操作，以回收空間提高查詢效率；（以此衡量table是否要重構(gòu)）
索引的聚簇因子clustering_factor，標識表中存儲數(shù)據(jù)順序和索引字段順序的符合程度；值越接近表塊BLOCKS個數(shù)，性能越好，越接近表行數(shù)NUM_ROWS，性能越差（以此衡量索引的有效程度）

索引類型：

1. B樹索引，normal

創(chuàng)建B－tree索引，即構(gòu)建一個相同深度的二叉樹，索引值存儲在葉子節(jié)點--雙向鏈表；

適用于非稀疏值的列；精確查找、范圍查找、模糊查找等

多級索引/級聯(lián)索引，慎選第一列（第1列會被解析器作為索引使用）

2. 函數(shù)索引

針對函數(shù)的，只能是單行返回的；具體值使用normal或者bitmap則由系統(tǒng)確定

3. 位圖索引，bitmap

適用于稀疏值的索引，即對每個值創(chuàng)建一個所有記錄的位數(shù)組，0和1代表是否存在；命中時，根據(jù)begin rowid和end rowid和偏移量計算出記錄的rowid

位圖索引不適合并發(fā)環(huán)境，在并發(fā)環(huán)境下可能會造成大量事務(wù)的阻塞

不能在分區(qū)表上創(chuàng)建global位圖索引，因為對分區(qū)表而言，每個分區(qū)的物理存儲分開

5. 唯一索引，unique

主鍵時默認創(chuàng)建的索引，使用B樹結(jié)構(gòu)

oracle中index的unusable，usable ，disable，enable


--如果出現(xiàn)unusable的話，需要進行重建

alter index index_name rebuild;

drop index index_name;

create index index_name on xxxxx;

--enable和disable僅僅只針對函數(shù)索引

alter index index_name enable;

alter index index_name disable;

Procedure存儲過程

封裝連續(xù)的動作，手動控制事務(wù)，且處理異常

會預(yù)編譯，效率較高

plsql提供組件進行調(diào)試/測試

function函數(shù)

函數(shù)相比存儲過程可以return值，并在select中直接調(diào)用

解決嵌套查詢兩級以上不能使用最外層值的問題=》直接傳遞外部值為參數(shù)

materialized view 物化視圖

與一般視圖的區(qū)別：查詢已經(jīng)執(zhí)行并將結(jié)果集存入了一張表中，好處是預(yù)先計算了查詢結(jié)果并且在特定查詢執(zhí)行的時候可以直接調(diào)取該結(jié)果。

適用于查詢匯總的，考慮到速度的

占用存儲空間，并且可以設(shè)置索引，物理存儲為segment

``sql

--查看快照/物化視圖的刷新時間

select * from ALL_SNAPSHOT_REFRESH_TIMES;

--物化視圖

select * from dba_snapshots;

--日志物化視圖

select * from dba_snapshot_logs;

--刪除

drop snapshot log on cust_info;

create snapshot log on cust_info;


#### 觸發(fā)器trigger

> 觸發(fā)器類似過程和函數(shù)，都有聲明、執(zhí)行和異常處理過程的PL/SQL塊；

> 觸發(fā)器是由一個事件來啟動運行，觸發(fā)器不能接收參數(shù)

### 常用的sql操作

> rownum和rowid；merge；窗口函數(shù)、行列互換、操作父子數(shù)據(jù)；

### 執(zhí)行計劃

> oracle生成執(zhí)行計劃基于兩種方式：

> 1、RBO基于規(guī)則（根據(jù)oracle確定的具有優(yōu)先級的規(guī)則來計算，可以手動指定）；

> 2、CBO基于成本（oracle目前采用，默認where語句之間獨立）

> 數(shù)據(jù)庫優(yōu)化器的目標：產(chǎn)生讓SQL執(zhí)行總成本最低的執(zhí)行計劃(CBO, cost based optimizer,基于成本)

> 成本：（根據(jù)對象(表、索引、列)的統(tǒng)計信息計算出訪問成本）時間成本+從磁盤訪問1個數(shù)據(jù)塊的成本

#### 常見執(zhí)行計劃：

1\. TABLE ACCESS FULL    對于表小時有優(yōu)勢

2\. INDEX UNIQUE SCAN    等值查詢index（分層定位）

3\. INDEX RANGE SCAN    范圍查詢，針對葉子節(jié)點（有序、雙向鏈表）

4\. INDEX FAST FULL SCAN    

1、index比原table小；2、index是按塊訪問=》若要查詢非index的字段，則按rowid進行**回表操作**；

  cost=索引高度（0~4，索引的特點決定大數(shù)據(jù)量表的查找不是問題）+回表（0~1）+聚集系數(shù)

回表操作，考慮index表和table的聚集情況，按照table的特點，如時間，可對index進行排序后再回表操作

5\. INDEX FULL SCAN

絕大多數(shù)情況下，index fast full scan性能優(yōu)于index full scan，但前者在有order by時，一定會存在對讀取的塊重新排序的過程    

rowid掃描 oracle定位單行數(shù)據(jù)最快的方式

  - TABLE ACCESS BY USER ROWID  直接根據(jù)rowid值獲取

  - TABLE ACCESS BY INDEX ROWID 先根據(jù)index獲取rowid，再根據(jù)rowid獲取

6\. NESTED LOOPS

COST ≈驅(qū)動表訪問成本+驅(qū)動表返回記錄數(shù)*內(nèi)部表訪問成本

7\. HASH JOIN

探測成本受驅(qū)動表返回結(jié)果集影響，有3種情行：

1、結(jié)果集可放到內(nèi)存，探測成本可忽略

2、One pass，不能一次性放到內(nèi)存，探測成本≈驅(qū)動表返回結(jié)果集寫入與讀取成本+探測表返回結(jié)果集寫入與讀取成本

3、Multi pass，`探測成本≈驅(qū)動表返回結(jié)果集寫入與讀取成本+探測表返回結(jié)果集（寫入成本+讀取成本*次數(shù)）`

8\. MEGER JOIN

#### 執(zhí)行計劃結(jié)果分析

> 順序：從上往下，從左往右，直到葉子節(jié)點；執(zhí)行結(jié)果不斷上移到父節(jié)點

```sql

SELECT T.ID activityId,

        T.ACTIVITY_NAME ACTIVITYNAME,

        T.ACTIVITY_NO ACTIVITYNO,

        T.BONUS_RULE bonusRule,

        (SELECT T2.KEY_DESC

            FROM CSSWEB_DICTIONARY T2

          WHERE T2.PARENT_ID = 90

            AND T1.PROVINCE_ID = T2.KEY_CODE) PROVINCENAME,

        (SELECT TO_CHAR(WM_CONCAT(C.CHANNEL_CODE))

            FROM JF_ACTIVITY_CHANNEL C

          WHERE C.JF_ACTIVITY_ID = T.ID) CHANNELCODE,

        T1.PROVINCE_ID PROVINCEID,

        T.ACTIVITY_STATUS ACTIVITYSTATUS,

        TO_CHAR(T.BEGIN_TIME, 'yyyy-MM-dd') BEGINTIME,

        TO_CHAR(T.END_TIME, 'yyyy-MM-dd') ENDTIME,

        T.ACTIVITY_TOTAL_AMOUNT TOTALAMOUNT,

        T.ACTIVITY_TYPE ACTIVITYTYPE,

        TO_CHAR(T.JF_OVERDUE_DATE, 'yyyy-MM-dd') JFOVERDUEDATE,

        NVL(T.JF_TOTAL_FLAG,1) JFTOTALFLAG,

        T.JF_TOTAL JFTOTAL,

        NVL(O.HLWCT, 0) HLWCT,

          NVL(O.ZYTG, 0) ZYTG,

          O.TGDZ,

          NVL(O.TGGX, 0) TGGX,

          NVL(O.CZHF, 0) CZHF,

          T.SYN_BUSI_FLAG AS synBusiFlag ,

          case when t.parent_id=0 then to_char(t.parent_id) else (select tt.activity_name from JF_MONEY_ACTIVITY tt where tt.id=t.parent_id) end as parentName

    FROM JF_MONEY_ACTIVITY T

    LEFT JOIN JF_ACTIVITY_AREA T1

      ON T.ID = T1.JF_ACTIVITY_ID

    LEFT JOIN JF_ACTIVITY_OTHER O

      ON O.JF_ACTIVITY_ID = T.ID

    WHERE T.ID ='894'

3875084-a5620e9eb1dc8c51.jpg

索引及優(yōu)化

索引效率不高

原因：建立的字段如果經(jīng)常增刪改，或者按需求清楚歷史數(shù)據(jù)，但刪除記錄對應(yīng)的表和索引里占用的數(shù)據(jù)塊空間并沒有釋放
解決：
1. alter table tbl move; 可以釋放已刪除記錄表占用的數(shù)據(jù)塊空間，整理碎片
1. alter index idx_tbl_col rebuild; 重建索引可以釋放已刪除記錄索引占用的數(shù)據(jù)塊空間。重建索引不僅能增加索引表空間空閑空間大小，還能夠提高查詢性能
1. alter index idx_tbl_col rebuild online; 加online，可以防止rebuild會阻塞一切DML操作

強制指定索引：

一張表上創(chuàng)建了非常多的索引（不推薦），每一個索引都是針對特定業(yè)務(wù)查詢而增加的。這極易導(dǎo)致SQL由于個別索引的引入出現(xiàn)性能問題。自己的sql可能命中其中的索引，但不是自己想要的，則使用Hint的方法實現(xiàn)強制SQL不走特定索引或強制使用


--使用hint強制使用或者不使用

select /*+ NO_INDEX(t t_idx1) */ object_name from t where object_name = 'T'

索引失效：

1、<>

2、單獨的>,<

3、like "%_" 百分號在前

4、單獨引用復(fù)合索引里非第一位置的索引列

5、字符型字段為數(shù)字時在where條件里不添加引號，或者merge中的on條件=》主要問題，數(shù)據(jù)庫之間或者表之間的字段類型不一致，常用是number和char之間的比較

6、對索引列進行運算.需要建立函數(shù)索引

7、not in ,not exist

8、當變量采用的是times變量，而表的字段采用的是date變量時.或相反情況

9、索引失效

10、基于cost成本分析(oracle因為走全表成本會更小)：查詢小表,或者返回值大概在10%以上；會優(yōu)化走table access full

11、有時都考慮到了但就是不走索引；可能index效率不高，需要重建

12、B-tree索引 is null不會走，is not null會走；位圖索引 is null,is not null 都會走

13、聯(lián)合索引 is not null 只要在建立的索引列（不分先后）都會走；is null時，其它索引列都要涉及

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

oracle

oracle

oracle

大概的執(zhí)行的流程

會話查詢及死鎖查詢

查詢后oracle清除緩存

sql中關(guān)鍵字的執(zhí)行順序

常接觸的對象

table和index

索引類型：

Procedure存儲過程

function函數(shù)

materialized view 物化視圖

索引及優(yōu)化

索引效率不高

強制指定索引：

索引失效：

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

oracle

oracle

大概的執(zhí)行的流程

會話查詢及死鎖查詢

查詢后oracle清除緩存

sql中關(guān)鍵字的執(zhí)行順序

常接觸的對象

table和index

索引類型：

Procedure存儲過程

function函數(shù)

materialized view 物化視圖

索引及優(yōu)化

索引效率不高

強制指定索引：

索引失效：

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av