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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30018455/viewspace-1418442/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30018455/viewspace-1418442/