oracle結構梳理---資料字典

資料字典（data dictionary）是 Oracle 資料庫的一個重要組成部分，這是一組用於記錄資料庫資訊的表 

u 資料庫中所有方案物件（schema object）的定義(包括表，檢視，索引，簇，同義詞，序列，過程，函式，包，觸發器等等) 

u 資料庫為一個方案物件分配了多少空間，以及該物件當前使用了多少空間 

u 列的預設值 

u 完整性約束（integrity constraint）資訊 

u 資料庫使用者資訊

u 每個使用者被授予（grant）的許可權（privilege）與角色（role） 

u 審計（audit）資訊，例如哪個使用者對某個方案物件進行了訪問或更新操作 

u 資料庫中的其他概要資訊  

資料字典，分為資料字典表(檢視)和動態效能表(檢視) 

--資料字典: 

基表(以 $ 結尾 ):用於存放所有資料庫物件資訊 

--資料字典檢視:

DBA_  資料庫中所有物件的資訊。 

ALL_  使用者有許可權訪問的所有的物件. 

USER_  使用者自己建立的 

---常用的資料字典檢視: 

user_tables: 該使用者表的資訊 

all_tables 

dba_tables 

user_indexes: 該使用者索引的資訊 

all_ 

dba-_ 

user_views: 該使用者檢視的資訊 

user_users : 當前使用者的資訊 

dba_constraints  約束資訊 

dba_extents、dba_segments 區，段的資訊 

dba_sys_privs,dba_roles 許可權角色資訊等 

dba_data_files 資料檔案資訊 

1. Oracle server使用它來獲取使用者、schema物件、儲存結構資訊 

2. 執行DDL語句時，oracle server會去修改它 

3.使用者和DBA把它作為只讀查詢來獲取資料庫的資訊 

 

--基表(以 $ 結尾 ):用於存放所有資料庫物件資訊 

基於資料字典基表的資料字典檢視(DBA_, ALL_, USER_) 

資料字典表 由$ORACLE_HOME/rdbms/admin/sql.bsq 指令碼建立，

資料字典檢視 由指令碼$ORACLE_HOME/rdbms/admin/catalog.sql 建立

動態效能表並不是真正的表, 由 SYS 使用者所擁有,它是記憶體表 

動態效能表 

--- 動態效能表（X$開頭） 

--動態效能檢視（V_$開頭或GV_$開頭）

動態效能檢視的結構資訊在v$fixed_view_definition 

v_ 單個例項 

gv_所有的例項,RAC  

v$session 

gv$session 

--- 動態效能檢視的同義詞(V$開頭或GV$開頭) 

動態效能檢視也叫v$ 檢視，它包含如下內容：

（1） system 和session 的資料

（2） 記憶體的使用和分配

（3） 檔案的狀態，包括RMAN 備份檔案

（4） Jobs 和task（任務）的程式

（5） 執行的SQL

（6） 統計資訊

-----資料字典的使用1：

-----chain_row

ANALYZE TABLE  LIST CHAINED ROWS;

這一個臨時表的意思 首先我們進行一個analyze的分佈 其次： 資料就進了CHAINED_ROWS 臨時表裡面

---dba_segments

select * from  dba_segments;

資料庫大小就在dba_segments中的bytes或size，blocks和dba_tables裡面的是有區別的，dba_tables中的是假的，是評估的。。。

object_id 和data_object_id，當表被truncate之後，data_object_id會改變，object_id不會變

----dba_indexes

select * from  dba_indexes;

dba_indexs中有一個cluster_factor

---dba_tables

dba_tables中的compression欄位表示的是這個表是否是壓縮表

---v$session 

select * from  v$session; --其中的Paddr（v$process） TADDR(V$TRANSACTION)

---V$TRANSACTION

SELECT * FROM V$TRANSACTION;

---v$process

select * from  v$process;

1. 如何透過資料字典從sql_text裡面找到儲存過程？

select * from dba_source where upper(text) like '%xxx%';

SELECT * from Dba_Source AS OF TIMESTAMP sysdate-2/1440 WHERE NAME ='FLASH_BACK';-----sql_test修改之前的文字

對於正在執行的sql，v$sql 裡面有個 program_id，可以透過 program_id，可以透過其查詢正在執行到sql屬於哪個物件

select a.program_id,

       c.spid,

       b.sid,

       b.USERNAME, 

       a.child_number,

       a.sql_id, 

       a.SQL_TEXT,

       a.SQL_FULLTEXT, 

       a.EXECUTIONS ex,

      trunc( a.ROWS_PROCESSED/case when a.EXECUTIONS =0 then 1 else a.EXECUTIONS  end) as "rows/exe" ,

       a.FIRST_LOAD_TIME,

       a.LAST_ACTIVE_TIME,

       b.MACHINE,

       b.MODULE, 

       AUDSID,

       d.EVENT,

       d.STATE,

       d.WAIT_TIME,

       d.SECONDS_IN_WAIT,       

       c.PGA_ALLOC_MEM,

       b.service_name,

       'alter system kill session ''' || to_char(b.SID) || ',' ||

       to_char(b.SERIAL#) || ''';' killse

  from  v$sql a,  v$session b,  v$process c,  v$session_wait d

 where a.SQL_ID = b.SQL_ID

   and b.PADDR = c.ADDR

   and b.SID = d.SID 

   and b.STATUS='ACTIVE'

   AND B.SID<>(SELECT SID FROM V$MYSTAT WHERE ROWNUM<2) 

 order by b.username, a.SQL_TEXT;

select* from all_objects where object_id='57473'---代入program_id

透過修改表結構達到硬解析的功能

grant select on a.tt to scott;--許可權修改了 執行計劃就肯定改了

CREATE OR REPLACE VIEW V$SESS_STAT AS

SELECT ms.SID,

     sn.statistic#,

     sn.name,

     sn.class,

     ms.value

FROM

     v$sesstat    ms,

     v$statname  sn

WHERE sn.statistic# = ms.statistic#

      and ms.value <>0;

--select * from  V$SESS_STAT where sid='77' and name like '%redo%';

---------------------------------------------------------------

v$sql_plan ---SQL的真實 執行計劃

dba_hist_sql_plan---如果查詢歷史資料

v$sql ----鑑別建立索引表的效果，尋找cursor包括的存取路徑鑑別索引是不是最優的，檢視執行計劃，監控執行計劃

v$sess_stat ---自己建立的檢視，檢視會話層面各種資訊

v$sqlstats ----sql層面的各種資訊

elapsed_time=cpu time + wait_time ---消耗的總時間 buffer_gets 邏輯讀 plan_hash_value 執行計劃id，根據這個id+v$sql_plan可以找出執行計劃，如果有兩個

                                  ----plan_hash_value說明執行計劃改變了，我們可以比較一下開銷，不是有sql_id就可以找到這個sql的繫結變數值 ，

                                  ---預設是900秒，才會重新開始捕獲。在900內，繫結變數值的改變不會反應在這個檢視中

v$active_session_history  dba_hist_active_session_history

v$active_session_history                      

-----就是大名鼎鼎的ash

ASH以V$SESSION為基礎，每秒取樣一次，記錄活動會話等待的事件。不活動的會話不會取樣，取樣工作由新引入的後臺程式MMNL來完成。

ASH buffers 的最小值為1MB,最大值不超過30MB。記憶體中記錄資料。期望值是記錄一小時的內容。

把session_id 傳進去然後對於sql_id 進行group by就可以知道整個會話最耗時的是哪個sql

select * FROM v$active_session_history;

select sql_id,count(1) from v$active_session_history group by sql_id;

select * from v$sql where sql_id='48wqjkfmkxu2f';

@?/rdbms/admin/awrrpt

@?/rdbms/admin/addmrpt

@?/rdbms/admin/awrsqrpi (檢視具體SQL的執行計劃)

v$session                                      (當前正在發生)

v$session_wait                            (當前正在發生)

v$session_wait_history              (會話最近的10次等待事件)

v$active_session_history           (記憶體中的ASH採集資訊，理論為1小時)

wrh$_active_session_history    (寫入AWR庫中的ASH資訊，理論為1小時以上)

dba_hist_active_sess_history   (根據wrh$_active_session_history生成的檢視)

create or replace procedure p_monit_active_session as

--將active_session寫入歷史表

begin

  --當前時間

  vCurtime := sysdate;

  delete from datasync_prc.Active_Session_History

   where sample_time >= vCurtime - 20 / (24 * 60);

  insert into datasync_prc.Active_Session_History

    select *

      from Gv$active_Session_History

     where sample_time > vCurtime - 20 / (24 * 60);

  commit;

  /*p_log('p_monit_active_session', '將active_session寫入歷史表');*/

  EXCEPTION

  when others THEN

    sendmail(Subject => 'p_monit_active_session',v_Msg => SQLERRM,Receipint => 'huangchao@richinfo.cn');

end;