個人原創，轉載請在文章頭部明顯位置註明出處：https://www.cnblogs.com/sunshine5683/p/10080102.html

在上一篇進階中大概講解了一些關於進階方面的知識，今天就將進階內容做個總結！

一、OracleServer實例DB的概念：

1、Oracle關係資料庫管理系統（RDMS）

• 集成了開放的、全面的、集成的信息管理方法.
• 一個實例對應一個OracleServer，要查詢oracleServer，直接查詢後臺進程就可以了
• 查看實例個數：

            ps -ef|grep ora_|cut -d '_' -f3|uniq

            ps -ef|grep ora_|cut -d '_' -f3|sort|uniq（有幾條就幾個oracleServer）

• 後臺進程是在實例啟動的時啟動的，時將文件寫到磁碟上的

        訪問實例：V$instance，訪問資料庫: v$database

        語句：select instance_name from v$instance_name;

              select db_unique_name from v$database;

              show parameter service_name  (查看服務名)

    一個實例返回一個資料庫 

註意：查看實例或資料庫名時，保證資料庫啟動到mount（此階段僅僅物理階段可用）階段或者open（此階段打開物理機構和邏輯結構）階段，在nomout階段不可以查詢

    註意：所有的12c跑應用程式都在pdb裡面跑，而不再cdb裡面跑

• 實例名跟庫名不一定相同，這是允許的

二、如何連接到資料庫

1、連接到伺服器：

所有連接資料庫都需要有相應的翻譯工具或者相應的客戶端工具，eg：Java訪問工具：jdbc等。

• 10g裡面連接工具：ojdbc14.jar   classes12.jar   ojdbc14.jar
• 11g 裡面的連接工具:ojdbc6.jar   ojdbc7.jar

    所以要訪問資料庫，只需要安裝以上任意連接工具的驅動包就可以

2、oracle中的客戶端工具：

• sqlplus：
• sqldevelop：
• toad：
• pl/sql develop:

    以上客戶端實現的時候都是通過ORACLE NET實現的

查看服務名：

也可以通過以下命令查看服務名

>show parameter dispathcer

3、windows上面訪問資料庫：

三、Oracle DB體繫結構概覽      

1、用戶進程

用戶進程要連接資料庫，有本地連接，有遠程連接，但都會在伺服器端產生用戶進程

• 連接到資料庫實例：

        連接：用戶進程和實例之間的通信

        會話：用戶通過用戶進程與實例建立的特定連接

• 遠程連接：tcp三次握手
• 本地連接：資料庫和客戶端在同一臺電腦：通過套接字連接

    本地遠程連接資料庫，然後進入服務端，desc v$session 就可以看到連接用戶客戶端信息

• 專有連接：一個用戶進程對應一個服務進程，連接模式為NONE:共用連接
• 共用連接：一個服務進程對應多個用戶進程， 連接模式DEDICATED:專有連接

2、伺服器進程

         1>訪問伺服器進程在v$process視圖中查看，伺服器進程是接收用戶進程請求並做相應的處理的。

         2>用戶進程產生會話之後，該會話將保留在記憶體當中，每個用戶進程都有自己的獨立的PGA區。

         3>查看訪問資料庫的操作系統進程id語句：

         Select spid from v$process where addr in(select paddr from v$session where username=’SCOTT’);

         4>看操作系統進程：

• ho ps –ef|grep 2036
• ho ps –ef|grep 4407

5>查看該進程消耗資源情況：top –p 4407

每一個視圖都可以進程跟蹤，使用desc v$process可以看到TRACEFILE（跟蹤文件）文件。

6>只要dba的用戶，在Oracle裡面都可以使用：show parameter pga_a查看pga的記憶體大小，pga的值本身是自動管理的，但在11g中，可以通過權值修改其大小。

7>修改pga的大小：

• alter system set pga_aggregate_target=5g;
• show parameter pga

    8>對於耗資源的進程，可以殺掉（假如是4406進程）：kill -9 4406

四、sga

         在10g中可以用show parameter sga_target查看sga大小，在11g和12c中可能看到的大小

         查看sga的信息：select * from v$sgainfo;

1、共用池

    1>庫高速緩存：用來接收pga傳遞過來的hashvalue，如果該值存在，做軟分析，否則，做硬分析，定義語句是全標掃描還是索引掃描

    2>數據字典高速緩存：提供訪問的對象是表還是索引還是同義詞，表空間、數據文件等，進行遞歸調用

3>伺服器進程用來讀取數據文件到數據高速緩存區中，然後進行的讀取

2、資料庫高速緩衝區

有兩種block，讀的block和寫的block，與塊的讀取有關

3、重做日誌緩衝區

與塊的更改有關，其大小需要手工設置。

五、後臺進程

1、CKPT

• 只要在用戶進程裡面執行除了查詢之外的的任何操作，都會觸發CKPT，觸發後，CKPT除了通知控制文件和數據文件外，還會通知DBWn將在資料庫緩衝區高速緩存區執行過的DDL或者DCL的操作寫到數據文件中去（註意：通知DBWn去寫，但是DBWn未必就立馬去寫）。
• 當CKPT觸發了，LGWR也寫完了，大師DBWn還沒寫完，在下次啟動時候，會自動將LGWR記錄在聯機重做日誌文件中保存的undo內容寫到數據文件中。
• SGA裡面的操作由SMON(系統監視進程)和PMON(進程監視進程)協調。
• Oracle中最重要的5個進程：SMON ,PMON,DBWn,CKPT,LGWR。
• 顯示相應的視圖：desc v$process，查看後臺進程：desc v$bgprocess視圖中:
• 查看當前正在使用的後臺進程：select paddr,name from v$bgprocess where paddr<>’00’；
• 查看數據文件：select name from v$datafile;
• 查看日誌文件位置：select member from v$logfile;
• 查看控制文件位置：select name from v$controlfile;
• 參數文件：show parameter spfile

1>數據文件：表空間、表、索引都在數據文件中

六、sga的其他組件

1、數據高速緩衝區

2、重做日誌緩衝區

3、大型池

4、Java池和流池

5、程式全局區（PGA）

6、系統全局區（SGA）

7、資料庫寫進程（DWRn）

         將資料庫緩衝區高速緩存區中經過修改的緩衝區寫入磁碟：有兩種寫入方式：

         1>在執行其它處理時非同步執行

         2>推進檢查點

• 查看：show parameter db_wr;
• 資料庫寫進程是可以修改的:alter system set db_writer_processes=30; a scope=spfile; /(表示執行)

8、日誌寫進程（LGWR）

         1>將重做日誌緩衝區中的內容寫入磁碟上的重做日誌文件中

         2>在以下情況下執行寫操作：

• 用戶進程提交事務處理時
• 重做日誌緩衝區的三分之一已滿時
• 在DBWn進程將經過修改的緩衝區寫入磁碟之前
• 每隔3秒

9、檢查點進程（CKPT）

         1>將檢查點信息記錄在以下位置

• 控制文件
• 每隔數據文件頭

10、系統件事進程（SMON）

         1>在實例啟動時候執行恢復

         2>清除不使用的臨時段

11、進程監器視進程（PMON）

         在用戶進程失敗時候執行進程恢復

1>     清楚資料庫緩衝區高速緩存

2>     釋放該用戶進程使用的資源

3>     監視會話是否發生空閑會話超時

4>     將資料庫服務動態註冊到監聽程式

12、恢復器進程：

1>     用於分散式資料庫配置

2>     自動連接到其它那些與有問題的分散式處理有關的資料庫

3>     自動解決所有有問題的事務處理

4>     刪除對應於所有有問題的事務處理所有行

13、歸檔進程（ARCn）

是一個可選進程。

1>     在發生日誌切換之後，將重做日誌文件複製到指定的存儲設備

2>     可以手機事務處理重做數據，並將該數據傳輸到備用目標位置

14、告警日誌

         1>查看告警日誌的路徑：show parameter background_dump_dest;

3>     讀該告警日誌：ho ls  /u01/oracle/admin/wyzc10g/bdump/alert_wyzc10g.log

在任何的版本都可以通過以上路徑去訪問，只是不同的版本可能路徑不一樣，如：在11g當中查看路徑：show parameter backgr; 可以看到路徑為：/u01/oracle/diag/rdbms/wyzc11g/wyzc11g/trace/alert_wyzc11g.log

同樣可以讀該日誌：

ho ls / u01/oracle/diag/rdbms/wyzc11g/wyzc11g/trace/alert_wyzc11g.log

七、邏輯結構

1、邏輯和物理資料庫結構：

• 方案：語句、表、索引、函數、過程、包等
• 資料庫：一個資料庫可以放32272個表空間
• 表空間：一個表空間可以放1023個數據文件
• 段；自動管理後，空間分配等都可自動管理，減少dba的工作量。一個表空間可以放多個段。
• 區：區裡面存放的都是block，如果採用的是10g及其以後的版本，都採用的是本地管理，字典管理已經不使用了。
• Oracle數據塊：Oracle最大的block只有32k
  • 數據文件：
  • 操作系統塊：

2、物理結構：

3、SYSTEM和SYSAUX表空間

1>     SYSTEM和SYSAUX表空間是在創建資料庫時創建的必須存在的表空間，這些表空間必須聯機。

2>     SYSTEM表空間用於核心功能（例如數據字典表）

3>     輔助的SYSAUX表空間用於附加的資料庫組件（如Oracle Enterprise Repository）

4>     數據塊會映射到磁碟塊，不建議使用SYSTEM和SYSAUX表空間來存儲應用程式的數據。

4、自動存儲管理

1>     是可移植的高性能集群文件系統

2>     管理Oracle DB文件

3>     通過ASM集群文件系統（ACFS）管理應用程式文件

4>     將數據分配到各個次盤中以平衡負載

5>     建立數據鏡像以防範故障

6>     解決存儲管理挑戰

5、ASM存儲組件

ASM最終調的是硬碟

八、12c體繫結構的不同之處

         Oracle中的三大容器：CDB$ROOT容器、PDBSEED容器和PDBContainer容器

九、DML語句：

註意：在寫sql語句時候如：selet * from aaa ;此時發現報錯’selet’,返回重新糾正就都的重寫，此時只需要替換一下就可以，’c/selet/select’然後回車，就會將錯誤字元改正並正常執行。

1.buffer cache --undo block

  修改前的data block 數據進行鏡像

2.undo 變化 所有redo記錄undo變化的過程

3.buffer cache --data block 修改

4.data block 變化  redo記錄變化過程

5.Insert操作成功

Insert執行時候1，2，3，4，5執行完成後，Oracle數據要進行：提交 回退|不提交。

    不提交>此時undo數據屬於修改後的狀態，未提交，undo數據只能被當前的事務所在的會話調用，其他會話不能看到DML修改後的結果，看到的是上次提交的狀態

    回退>此時使用修改前的undo鏡像，覆蓋修改的data block，這樣就撤銷了修改的信息，標記undo事務數據不需要

    提交>標記ITL信息為新的SCN，undo數據為提交狀態，變為歷史undo數據，寫到磁碟上，將塊數據標記為臟數據，該臟數據需要下次dbwr寫到磁碟上，該數據也將成為其他會話看到的提交的數據

長時間不提交，DML事務達，很長時間沒有結束，可能影像其他的會話執行所在行的DML

DML在對一張表進行操作的時候，會將該表鎖定，到commit或著collback後釋放

十、DDL語句：

    1、其中上面1、2看到DDL是否修改數據字典 ，若修改字典， 1，2執行，2、4都要執行

    2、DDL操作成功

十一、執行計劃

1、EXPLAIN PLAN 命令模擬sql執行跟蹤

2、查詢V$sql_plan視圖

3、查詢AWR

4、SQL*PLUS AUTOTRACE:統計信息

取當前的執行計劃：在記憶體中已經存在一個sql情況：

1>  找到對應sql語句的游標id：select sql_id from v$sql where sql_text like 'select * from t1%';

2>  使用嵌套表查詢當前語句的執行計劃：

select * from table(dbms_explan.display_cursor('alln3qb4qvzrt')); 得到執行計劃

    常用查看執行計劃語句：1>ls /u01/oracle/11g/sqlplus/admin/

5、執行計劃的縮進執行順序：

    1、縮進不同，少的為父親，多的為兒子。父子關係執行時候：先兒子後父親

    2、縮進相同，上面的為哥哥，下麵的為弟弟，執行時候：先哥哥後弟弟

    3、縮進是可以嵌套的：以父親/祖父同級的為一個家族

6、SQL跟蹤工具：

    1>通常在會話級啟用

    2>收集按照會話分組的sql語句的會話統計信息

    3>產生可通過tkprof格式化輸出

    4>方法：對於當前會話：EXEC dbms_monitor(待完成)

7、SQL 優化器：

    rule：表示使用rule優化器，現已被淘汰，用與10g   使用rbo

    choose：有統計信息的優化器，使用cbo不使用rbo

    10gr2 ：開始統計信息，可以動態採樣，沒有統計信息，也不使用rbo

    all_rows   first_rows   first_rows_n

    其中  rule基於規則，

    如果沒有統計信息，將採用rbo

8、生成優化程式跟蹤

    1>設置一個事件-10053優化程式跟蹤

        建索引：

        Create index emp_d_i on emp(deptno);

        create index emp_s_d_i on emp();

        create index emp_s_d_i on emp(sal,deptno);

    2>執行相關語句

十二、Oracle中的函數—單行函數之字元函數

單行函數：不管輸入幾行，返回的只有一行。

1、Character：字元函數

大小寫：

• LOWER（String）:全部轉換為小寫
• upper(String)：全部轉換為大寫
• initcap（String）：將首字母轉換為大寫

字元處理函數：

• concat（String，String）：字元串連接函數，要連接三個字元串，需用嵌套，其嵌套為：concat（concat（StringValue，StringValue），StringValue）
• substr（expr，m，n）：截取字元串expr，其中m表示從哪裡開始，m可為負數，表示反向截取，n表示取多少個,省略n表示從第m個字元把後面所有的字元都截取出來。應用場景：如找到第二個字元是A的員工：select ename from emp where substr(ename,2,1)=’A’;
• length（）：該函數有四種形式，lengthc、lengthb、length2、length4
• instr（StringValue,’a’）:返回字元在第一個字元串中的位置，若找到，返回位置，沒找到，返回0，應用場景：找到第一個字元是A開頭的員工，select ename from emp where instr(ename,’A’)=1。

2、Gerneral：通用函數

3、Numbers：數字函數

4、Conversion：轉化函數

5、DateValue日期函數

以上空著的部分將在後續總結中進行填補！