dba学习

第一章：数据库的启动和关闭
oracle server 由instance和database组成。
instance是一组后台进程/线程和一块共享内存区域。
database是存储在磁盘上的一组物理文件。
1.1 数据库的启动
启动的3个步骤：nomount、mount、open
1.1.1 启动数据库到nomount状态
oracle首先找到参数文件(spfile/pfile),根据参数文件中的设置创建实例，分配内存，启动后台进程。
这一步不需要控制文件和数据文件的参与。如果出现问题，肯定是内核参数(需要检查参数文件和系统配置)。
C:/> lsnrctl start --启动数据库监听程序
C:/> sqlplus /nolog --启动sqlplus
sql> conn sys/orcl as sysdba; --用管理员登录。 windows系统提示 "ora-12560: TNS: 协议适配器错误"
解决ORA-12560: TNS: 协议适配器错误，与大家共享
今天遭遇ORA-12560: TNS: 协议适配器错误的问题，经过一番努力问题已经解决，与大家共享。
造成ORA-12560: TNS: 协议适配器错误的问题的原因有三个：
1.监听服务没有起起来。windows平台个一如下操作：开始---程序---管理工具---服务，打开服务面板，启动oraclehome92TNSlistener服务。
2.database instance没有起起来。windows平台如下操作：开始---程序---管理工具---服务，打开服务面板，启动oracleserviceXXXX,XXXX就是你的database SID.
3.注册表问题。regedit，然后进入HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0将该环境变量ORACLE_SID设置为XXXX,XXXX就是你的database SID.或者右几我的电脑，属性--高级--环境变量---系统变量--新建
，变量名=oracle_sid,变量值=XXXX,XXXX就是你的database SID.或者进入sqlplus前，在command line下输set oracle_sid=XXXX,XXXX就是你的database SID.
经过以上步骤，就可以解决问题。
意思就是说：必须要在服务里启动oracleserviceXXXX，才能在在命令行中conn sys，这样在命令行中启动数据库就没有意义了。
sql> $net start oracleserviceorcl; --通过该命令启动数据库服务
sql> $net stop oracleserviceorcl; --通过该命令启动数据库服务
sql> startup nomount; --启动到nomount状态，使用参数文件spfile<sid>，输出警报日志alert_<sid>.log 可用视图:v$parameter
oracle选择参数文件的顺序：
首选spfile<sid>.ora，其次选择spfile.ora，最后选择init<sid>.ora，如果上述3个文件都不存在，oracle将无法启动。
show parameter命令：
show parameter 参数名; --返回参数名称、类型、参数值
例如：show parameter spfile; --从返回值可知使用的是spfile<sid>.ora文件。
show parameter dump_dest; --日志文件路径
show parameter control_files; --控制文件路径
参数文件中最少的参数是db_name,设置该参数后实例就可启动。Linux/UNIX下：
[]$ export ORACLE_SID=test --指定（创建）实例
[]$ sqlplus "/ as sysdba"
sql> startup nomount; --找不到参数文件
sql>! echo "db_name=test">/opt/oracle/product/9.2.0/dbs/inittest.ora
sql> startup nomount; --实例启动
1.1.2 启动数据库到mount状态
启动到nomount后，根据参数文件可找到控制文件，
alter database mount; --使用参数文件找到控制文件controlfile 可用视图:v$controlfile
oracle锁定控制文件，开始启动Heartbeat(心跳)，每3秒更新一次控制文件。
alter session set events 'immediate trace name controlf level 10'; --每隔3秒转储2次控制文件信息
查询Heartbeat的值：select cphbt from x$kcccp;
等待事件control file heartbeat, select event#,name from v$event_name where name like '%heart%';
口令文件：$oracle_home/dbs/orapw<sid> 只找到orapw.exe可执行文件。
与口令相关的参数:select * from v$parameter where name='remote_login_passwordfile';
1.1.3 启动数据库到open状态
控制文件中记录了数据文件、日志文件、检查点信息，open数据库时包括两次检查， 
第一次检查数据文件头中的检查点计数(checkpoint cnt)是否和控制文件中的检查点计数(checkpoint cnt)一致。用于确认数据文件是否来自同一版本。
检查点计数(checkpoint cnt)的作用：
alter session set events 'immediate trace name controlf level 10';--转储正常状态下的控制文件
alter tablespace system begin backup; --将system表空间置于热备份状态（热备份状态会冻结表空间数据文件的检查点），checkpoint加1
alter session set events 'immediate trace name controlf level 10';--再转储控制文件
alter system checkpoint; --手工执行检查点，checkpoint加1，SCN不变
alter session set events 'immediate trace name controlf level 10';--再转储控制文件
alter tablespace system end backup; --结束system表空间的热备份状态，checkpoint加1，SCN随之变化
alter session set events 'immediate trace name controlf level 10';--再转储控制文件
第二次检查数据文件头的开始SCN和控制文件中记录的该文件的结束SCN是否一致。
对每个数据文件都完成检查后，打开数据库，锁定数据文件，同时将每个数据文件的结束SCN设置为无穷大。
alter database open; --使用控制文件找到数据文件、日志文件、检查点信息，检查数据文件头中checkpoint cnt/system change number SCN是否与控制文件中的一致
shutdown immediate; --关闭数据库
startup force; --重启

1.2 进阶内容
1.2.1 SCN(system change number)系统改变号
SCN用于标识数据库在某个确切时刻提交的版本。事务提交时被赋予标识事务的scn,可作为内部时钟机制，全局唯一。
scn常见于事务表、控制文件、数据文件头、日志文件、数据块头等。
SCN的获取方式：
select dbms_flashback.get_system_change_number from dual; --9i之后
select max(ktuxescnw*power(2,32)+ktuxescnb) from x$ktuxe; --9i之前
SCN的进一步说明：
SCN在事务提交或回滚是改变，在控制文件、数据文件头、数据块、日志文件头、日志文件change vector中的SCN作用各不相同。
数据文件头中包含了该数据文件的checkpoint SCN，表示该数据文件最近一次执行检查点操作时的SCN。
日志文件头中包含了Low SCN 和Next SCN，只日志文件包含介于Low SCN到Next SCN的重做信息，当前日志文件(redo logfile)Next SCN为无穷大。
select * from v$log;--查看日志文件
select dbms_flashback.get_system_change_number from dual; --获取SCN
alter system switch logfile; --切换日志文件
select * from v$log;--查看日志文件
控制文件的转储，不清楚有什么功能？？？
select * from v$logfile;--查看dump日志文件
alter system dump logfile 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG';
1.2.2 检查点(Checkpoint)
检查点用于减少崩溃恢复(Cresh Recovery)时间。
修改数据时将数据读入内存，在内存中修改，记录重做(redo)信息，只有在提交时才将数据写回磁盘。redo可用于断电后，重启数据库时进行事务的重演(前滚操作)，再对未提交的事务进行回滚。
检查点就是为了缩短前滚时间的。
当检查点发生时，oracle启用DBWR进程把修改过的数据写入磁盘，写完之后启用CKPT进程更新控制文件和数据文件头，记录检查点信息，标识已更改。
checkpoint scn查询
select file#,name,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') cpt from v$datafile;
select dbid,checkpoint_change# from v$database;
常规检查点与增量检查点
常规检查点(Conventional Checkpoint):oracle8之前的检查点,触发条件有log_checkpoint_interval、log_checkpoint_timeout参数设置及log switch等条件触发。
增量检查点(Incremental Checkpoint):oracle8之后引入的检查点队列(Checkpoint Queue)机制，每条脏数据被移动到检查点队列，按照Low RBA(redo byte address)的顺序排列，
当执行检查点时，DBWR从检查点队列按Low RBA顺序写出，ckpt进程更新将当前最低RBA写入控制文件。
增量检查点可以连续进行，检查点RBA更接近数据库最后的状态，可以减少恢复时间；DBWR可以持续写出，避免了常规检查点写出时的I/O征用。
select * from v$version;--版本信息
select * from v$option where parameter='Fast-Start Fault Recovery';
增量检查点组件(Fast-Start Fault Recovery)特性：
Fast-Start Checkpointing特性：参数fast_start_io_target(8i)/fast_start_mttr_target(9i)
Fast-Start On-Demand Rollback特性：
Fast-Start Paraller Rollback特性：
fast_start_mttr_target参数：定义数据库进行Crash恢复的时间，范围在0~3600s之间。
select * from v$parameter where name in ('fast_start_mttr_target','fast_start_io_target','log_checkpoint_interval','log_checkpoint_timeout');
建议用参数fast_start_mttr_target替换fast_start_io_target、log_checkpoint_interval、log_checkpoint_timeout
v$mttr_target_advice视图：select * from v$mttr_target_advice; --用于评估在不同的fast_start_mttr_target参数设置下，执行I/O的次数。
statistics_level参数：show parameter statistics_level;参数设置为typical或者all
v$statistics_level视图：select * from v$statistics_level where STATISTICS_NAME='MTTR Advice';
v$instance_recovery实例恢复状态视图：
select recovery_estimated_ios reio,actual_redo_blks arb,target_redo_blks trb,log_file_size_redo_blks lfsrb,log_chkpt_timeout_redo_blks lctrb,
log_chkpt_interval_redo_blks lcirb,fast_start_io_target_redo_blks fsiotrb,target_mttr "期望回复时间",estimated_mttr "平均回复时间",ckpt_block_writes "检查点已经写出的数据块的数量" 
from v$instance_recovery;
一个例子：
执行查询，发现estimated_mttr>target_mttr ,estimated_mttr仍有上升趋势
select recovery_estimated_ios reio,actual_redo_blks arb,target_redo_blks trb,log_file_size_redo_blks lfsrb,log_chkpt_timeout_redo_blks lctrb,
log_chkpt_interval_redo_blks lcirb,fast_start_io_target_redo_blks fsiotrb,target_mttr "期望回复时间",estimated_mttr "平均回复时间",ckpt_block_writes "检查点已经写出的数据块的数量" 
from v$instance_recovery;
查询等待事件：发现checkpoint incomplete等待
select sid,seq#,event from v$session_wait;
查询v$log视图：发现除当前组外，其余组处于active状态
select * from v$log;
通过os查看iostat状态信息，发现系统swap严重(si,sw)，cpu等待io(wa)高：# vmstat 2
oracle 10g 自动检查点调整:
当fast_start_mttr_target参数未设置时，自动检查点调整生效。
v$instance_recovery实例恢复状态视图：
select recovery_estimated_ios reios,target_mttr "期望回复时间",estimated_mttr "平均回复时间",
writes_mttr wmttr,writes_other_settings woset,ckpt_block_writes "检查点已经写出的数据块的数量",
writes_autotune "自动调整检查点执行的写出次数",writes_full_thread_ckpt wftckpt 
from v$instance_recovery;
从控制文件获取检查点信息: 
在控制文件的转储中，可以看到关于检查点进程进度的记录.
low cache rba(recovery block address)指在Cache中，最低的RBA地址，在实例恢复或崩溃恢复中，从这里开始恢复。
on disk rba 是磁盘上的最高的重做值，在进行恢复时应用重做至少要达到这个值。
1.2.3 正常关闭数据库的状况

第二章 参数及参数文件 p62
2.1 初始化参数的分类
2.1.1 推导参数Derived Parameters
由其他参数计算得出，不需要设置。如：sessions=(1.1×processses)+5
2.1.2 操作系统依赖参数
受操作系统的限制。如：db_cache_size
2.1.3 可变参数
可以调整的参数。如：open_cursors
注：又可分为{静态参数、动态参数}、{显示参数、隐含参数} 显示参数在V$parameter视图中
2.1.4 初始化参数的获取
--获取显示参数
select * from v$parameter;
--获取隐含参数
select x.ksppinm name,y.ksppstvl value,y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj 
from sys.x$ksppi x,sys.x$ksppcv y 
where x.inst_id=userenv('Instance') and y.inst_id=userenv('Instance') and 
x.indx=y.indx and x.ksppinm like '%_&par%' order by translate(x.ksppinm,'_','');
2.2 参数文件
9i之前初始化参数文件，pfile文本文件。通过手工修改，实例重启后生效。
9i之后服务器参数文件，spfile二进制文件。通过alter system/alter session修改，动态参数立即生效，静态参数重启后生效。可以用RMAN备份。
2.2.1 创建数据库脚本
c:/oracle/product/10.2.0/admin/orcl2/scripts 数据库创建脚本
2.2.2 spfile的创建：
create spfile[='spfile路径及名称'] from pfile[='pfile路径及名称']; --需要sysdba权限
视图：v$spparameter
alter system set db_cache_size=24M scope=both/spfile;--设置参数
host rename spfileorcl.ora.bak spfileorcl.ora; --更名
2.2.3 spfile的搜索顺序
spfile<ORACLE_SID>.ora 目录 UNIX：$ORACLE_HOME/dbs/ NT：%ORACLE_HOME%/database C:/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora
spfile.ora 目录 UNIX：$ORACLE_HOME/dbs/ NT：%ORACLE_HOME%/database C:/oracle/product/10.2.0/admin/orcl/pfile/init.ora.310201120914
init<ORACLE_SID>.ora 目录 UNIX：$ORACLE_HOME/dbs/ NT：%ORACLE_HOME%/database C:/oracle/product/10.2.0/db_1/database/initorcl.ora
2.2.4 使用pfile/spfile 启动数据库
sql> startup pfile='C:/oracle/product/10.2.0/db_1/database/initorcl.ora'; 在winxp上成功。
SQL> alter system set log_archive_start=true scope=spfile;
SQL> alter system set log_archive_start=false scope=spfile;
通过在pfile中调用spfile,使用后设置的参数覆盖spfile中的参数设置，是解决spfile中参数设置错误的一种方法。
2.2.5 修改参数 alter system
alter system命令的scope参数有三种取值：
memory:只改变当前实例，重启db后失效；spfile:只改变spfile的设置，不改变当前实例，重启db后生效；both:即改变当前实例，也改变spfile的设置，重启db后仍然有效。
alter system set db_cache_advice=off scope=memory; 重启startup force;后失效。
alter system set db_cache_advice=off scope=spfile; 重启startup force;后生效。静态参数只能指定scope=spfile进行修改。
alter system set db_cache_advice=on scope=spfile; 对当前实例无效，但可以通过v$spparameter试图查询。
alter system set db_cache_advice=on scope=both; 默认设置,与不加scope参数效果一致。
rac环境中的修改:
不指定SID名称，或者指定位“*”，对所有实例生效。alter system set open_cursors=500 SID='*' scope=memory;
指定SID参数，仅对指定的实例生效。alter system set open_cursors=150 scope=spfile sid='orcl';
通过：select sid,name,value from v$spparameter where name='×××'/'undo_tablespace';查询，对于非数值等值，修改时务必加上sid参数。
在关闭数据库状态修改spfile:
alter system set db_block_buffers=1000 scope=spfile; --错误的修改了参数
shutdown immediate; --停掉数据库
create pfile from spfile; 如：create pfile='C:/oracle/product/10.2.0/db_1/dbs/PFILEORCL.ORA' from spfile='C:/oracle/product/10.2.0/db_1/dbs/SPFILEORCL.ORA';
--将spfile复制到pfile，在pfile进行修改
connect sys/orcl as sysdba; --连接数据库
create spfile from pfile; 如：create spfile='C:/oracle/product/10.2.0/db_1/dbs/SPFILEORCL.ORA' from pfile='C:/oracle/product/10.2.0/db_1/dbs/PFILEORCL.ORA';
startup; --将修改後的pfile复制到spfile中，启动数据库
spfile的重要意义是将pfile文件中的静态参数区分为部分动态参数、部分静态参数，动态参数通过alter system命令可以直接生效，不用重启数据库。
限制：spfile是二进制文件，不能手工修改，否则将损坏spfile文件。
2.2.6 重置spfile中设置的参数
恢复参数的默认设置：alter system reset parameter名 <scope=memory|spfile|both> sid='sid名|*';
2.2.7 检查spfile是否使用
查询v$parameter动态试图,select name,value from v$parameter where name='spfile';为null则使用的是pfile
show命令,show parameter spfile;
查询v$spparameter试图,select count(*) from v$spparameter where value is not null;为0则使用的是pfile
select isspecified,count(*) from v$spparameter group by isspecified;TRUN为0则使用的是pfile
select decode(count(*),1,'spfile','pfile') used from v$spparameter where rownum=1 and isspecified='TRUE';
2.2.8 spfile的备份与恢复
用rman备份恢复spfile。当数据库发生重大变化时（如增减表空间），自动备份控制文件和spfile文件（需要配置控制文件自动备份）。

第三章 数据字典
3.1 数据字典概述
数据字典(Data dictionary)是oracle元数据(Metadata)的存储地点。
数据字典包括的内容：
所有数据库Schema对象的定义(表、视图、索引、聚簇、同义词、序列、过程、函数、包、触发器等)；
数据库的空间分配和使用情况；
字段的缺省值；
完整性约束信息；
Oracle的用户名称、角色、权限等信息；
审计信息；
其他数据库信息。
数据字典的组成：内部RDBMS(X$)表、数据字典表、动态性能(V$)视图、数据字典视图。
3.2 内部RDBMS(X$)表
X$表是oracle的核心，用于跟踪内部数据库信息，维持数据库的正常运行。X$表被加密，建立的试图可用于查询，
X$表在数据库启动时由oracle应用程序动态创建，只能由sysdba用户访问，不能授权给其他用户。
常用到的X$表：X$BH、X$KSMSP、X$KSPPI、X$KSPPCV、X$KVIT(和实例相关的内部参数设置)
研究方法：可以通过autotrace或分析计划窗口查看视图以获知底层表的作用。
sql> set autotrace trace explain;
sql> select * from v$parameter; --底层表X$KSPPI、X$KSPPCV
sql> select * from X$KVIT; --和实例相关的内部参数设置
触发后台进程DBWR写动作的两个条件（X$KVIT）：
脏缓冲(drity buffer)阀值(threshold)达到。kcbldq=25 large dirty queue if kcbclw reaches this。
no free buffer,当进程扫描LRU一定数量的Block后，没有足够的free空闲，则触发DBWR执行写出。kcbfsp=40 。
这两个阀值是数据库的内部限制，可以通过X$KVIT查询，不能调整。
3.3 数据字典表(Data Dictionary Table)
数据字典表用于存储表、索引、约束以及其他数据库结构的信息。表名以$结尾，如：tab$,obj$,ts$,bootstrap$,undo$等。数据字典表在创建数据库时通过运行sql.bsq脚本来创建。
sql.bsq包含数据字典表的定义及注释说明，存放在$oracle_home/rdbms/admin（c:/oracle/product/10.2.0/db_1/rdbms/admin/sql.bsq）目录下。
3.4 动态性能(V$)视图(Dynamic Performance View)
动态性能(V$)视图:记录db运行信息和统计数据，实时更新反映db的状态。
3.4.1 GV$和V$视图
在X$表的基础上创建GV$（global V$）和V$视图,大部分V$都有一个对应的GV$。
GV$和V$区别在于：GV$是多个实例的信息，V$是单个实例的信息。V$包含下列语句：where inst_id=USERENV('Instance')
select inst_id,instance_name,status,version from gv$instance;
select instance_number,instance_name,status,version from v$instance;
用于记录其他视图创建方式的视图，如：v$fixed_view_definition.
select view_definition from v$fixed_view_definition where view_name='V$FIXED_TABLE';
select view_definition from v$fixed_view_definition where view_name='GV$FIXED_TABLE';
可以看出V$视图是基于GV$视图创建的，GV$试图是基于X$表创建的。
3.4.2 GV_$、V_$视图和GV$、V$的同义词
X$表、GV$、V$试图只对sysdba开放，依据GV$、V$试图创建GV_$、V_$视图，然后再针对GV_$、V_$视图创建公共的同义词。
上述功能由catalog.sql脚本实现，脚本位置：$oracle_home/rdbms/admin（c:/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql）目录下。 
create or replace view v_$sqlarea as select * from v$sqlarea; --创建v_$sqlarea视图
create or replace public synonym v$sqlarea for v_$sqlarea; --创建v_$sqlarea视图的同义词v$sqlarea
grant select on v_$sqlarea to select_catalog_role; --给同义词v$sqlarea授select_catalog_role角色的select权限。
其他用户访问的v$对象是V_$视图的同义词，而非v$试图。oracle的访问顺序是：表--》视图--》同义词。
验证如下：create table x$test_users as select username from dba_users; --创建x$表
create view v$test_users as select * from x$test_users; --创建v$试图
create view v_$test_users as select * from v$test_users; --创建v_$试图
create public synonym v$test_users for v_$test_users; --创建v_$试图的公共v$test_users同义词
connect tt/tt; --切换用户
create view v$test_users as select username,user_id from dba_users; --创建与同义词重名的视图v$test_users
select * from v$test_users; --访问的是本用户下的视图v$test_users
drop view v$test_users; --drop本用户下的视图v$test_users
select * from v$test_users; --访问的是sysy用户下的同义词v$test_users
3.4.3 进一步的说明 v$fixed_table， v$fixed_view_definition
v$fixed_table视图可以查看X$表、GV$和V$试图。
select count(*) from v$fixed_table where name like 'X$%'; --613张x$表
select count(*) from v$fixed_table where name like 'GV$%'; --372张gv$试图 
select count(*) from v$fixed_table where name like 'V$%'; --396张v$试图
select count(*) from v$fixed_table; 总计1383, X$表、GV$和V$试图 合计 613+372+396=1381，相差了两条记录
select * from (select * from (select * from v$fixed_table where name not like 'X$%') where name not like 'GV$%') where name not like 'V$%'; --另外两条记录的查询
name: GO$SQL_BIND_CAPTURE ,O$SQL_BIND_CAPTURE (捕获动态绑定的参数)
type: view 
v$fixed_view_definition视图可以查看GV$和V$试图的定义
select count(*) from v$fixed_view_definition where view_name like 'X$%'; --0 即该视图不包括x$表
select count(*) from v$fixed_view_definition where view_name like 'GV$%'; --372张gv$试图
select count(*) from v$fixed_view_definition where view_name like 'V$%'; --396张v$试图
select count(*) from v$fixed_view_definition; 总计770， GV$和V$试图 合计 372+396=768，相差了两条记录（视图的定义）
select * from (select * from v$fixed_view_definition where view_name not like 'GV$%') where view_name not like 'V$%'; --另外两条记录的查询
view_name：GO$SQL_BIND_CAPTURE,O$SQL_BIND_CAPTURE (捕获动态绑定的参数)
view_definition： 由定义可以看出看出O$SQL_BIND_CAPTURE是基于GO$SQL_BIND_CAPTURE创建的,带G是全局的，不带G是本实例的
select INST_ID,KQLFBC_PADD,KQLFBC_HASH,KQLFBC_SQLID,KQLFBC_CADD,KQLFBC_CHNO,substr(KQLFBC_NAME, 1, 30),KQLFBC_POS,to_number(decode(KQLFBC_DUPPOS, 65535, NULL, KQLFBC_DUPPOS)),KQLFBC_OACDTY,substr(KQLFBC_DTYSTR, 1, 15),decode(KQLFBC_OACCSI, 0, to_number(null), KQLFBC_OACCSI),decode(KQLFBC_OACPRE, 0, to_number(null), KQLFBC_OACPRE),decode(KQLFBC_OACSCL, 0, to_number(null), KQLFBC_OACSCL),KQLFBC_OACMXL,decode(KQLFBC_WCAP, 0, 'NO', 'YES'),decode(KQLFBC_WCAP, 0, to_date(NULL), KQLFBC_LCAP),KQLFBC_STRVAL,decode(KQLFBC_WCAP, 0, NULL,sys.sys$rawtoany(KQLFBC_BINVAL, KQLFBC_OACDTY,KQLFBC_OACCSF, KQLFBC_OACCSI)) from x$kqlfbc;
select ADDRESS, HASH_VALUE, SQL_ID, CHILD_ADDRESS,CHILD_NUMBER, NAME,POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING,CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, WAS_CAPTURED,LAST_CAPTURED, VALUE_STRING, VALUE_ANYDATA from go$sql_bind_capture where inst_id = USERENV('Instance');
3.5 数据字典视图 
数据字典视图：是在X$表和数据字典表之上创建的视图，由catalog.sql创建，脚本位置：$oracle_home/rdbms/admin（c:/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql）目录下。
按前缀不同分为3类：user_(某个用户拥有的相关对象信息)，all_(某个有权限访问的所有对象信息)，dba_(数据库的所有相关对象信息).
user_tables (o.owner#=userenv('SCHEMAID')用来限制当前用户的schema对象信息)。
all_tables (o.owner# = userenv('SCHEMAID') or .. sys.objauth$ oa .. or ..v$enabledprivs .. 由or可以看出user_tables是all_tables的子集)。
dba_tables (没有了o.owner# = userenv('SCHEMAID')这一项，不仅仅限于当前用户，而是扩展的整个数据库)。
3.6 最后的验证 v$parameter
3.6.1 通过v$parameter视图追踪数据库架构。
select * from v$fixed_view_definition where lower(view_name)='v$parameter';
view_definition: select num,name,type,value,display_value,isdefault,isses_modifiable,issys_modifiable,isinstance_modifiable,ismodified,isadjusted,isdeprecated,description,update_comment,hash from gv$parameter where inst_id = userenv('Instance')
select * from v$fixed_view_definition where lower(view_name)='gv$parameter';
view_definition: select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl,ksppstdvl,ksppstdf,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE'),decode(bitand(ksppiflg,4),4,'FALSE',decode(bitand(ksppiflg/65536,3),0,'FALSE','TRUE')),decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),decode(bitand(ksppilrmflg/64,1),1,'TRUE','FALSE'),ksppdesc,ksppstcmnt,ksppihash 
from x$ksppi x,x$ksppcv y 
where (x.indx = y.indx) and ((translate(ksppinm,'_','#') not like '##%') and ((translate(ksppinm,'_','#') not like '#%') or (ksppstdf = 'FALSE') or (bitand(ksppstvf,5) > 0)))
gv$parameter源自x$ksppi和x$ksppcv，x$ksppi和x$ksppcv用于包含所有的数据库参数，gv$parameter展现非隐含参数(非"_"开头的参数)。
3.6.2 视图还是同义词
sys用户访问v$parameter是访问的v$parameter视图；
非sys用户访问v$parameter是访问的v$parameter视图创建的v_$parameter视图的同义词。
3.6.3 oracle如何通过同义词定位对象 
通过10046事件跟踪查询：
sql> connect sys/orcl as sysdba;
sql> grant alter session to tt; --给用户授session权限
sql> connect tt/tt;
sql> alter session set events '10046 trace name context forever,level 12';
sql> select * from v$test_users; 
C:/oracle/product/10.2.0/admin/orcl/bdump/***.trc文件中记录：
首先，验证表和视图
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
bind 0: value=69 /0 owner#=69是tt用户，owner#=0是sys用户
bind 1: value="V$TEST_USERS"
bind 2: value=1
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from sys.obj$ 
where owner#=0 and name='V$TEST_USERS' and namespace=1 and remoteowner is null and linkname is null and subname is null;
这个查询owner#=69查不出结果，说明V$TEST_USERS不是tt用户下的对象Schema；owner#=0查出结果，说明V$TEST_USERS是sys用户下的对象Schema。
接着，验证同义词
select node,owner,name from syn$ where obj#=63261;
select obj#,node,owner,name from syn$ where name='V_$TEST_USERS';
obj#=63261
select object_name,object_id,object_type from dba_objects where object_name='V$TEST_USERS'
select object_name,object_id,object_type from dba_objects where object_name='V$TEST_USERS' and object_type='SYNONYM'
select text from view$ where rowid=:1;
select obj# from view$ where dbms_rowid.rowid_to_restricted(rowid,0)='000001CD.0013.0001';
select text from view$ where obj#=63260;
总结sql语句中oracle对于对象名的解析顺序：
oracle首先查看在发出命令的用户模式中是否存在表或视图；
如果表或视图存在，则使用该表或视图。
如果表或视图不存在，oracle检查私有同义词是否存在；
如果私有同义词存在，将使用这个同义词所引用的对象。
如果私有同义词不存在，oracle检查同名的公共同义词是否存在；
如果公共同义词存在，将使用这个公共同义词所引用的对象。
如果公共同义词不存在，oracle返回消息"ora-00942 table or view does not exist"。
10046事件的使用 http://www.eygle.com/case/ Use.sql_trace.to.Diagnose.database.html

第四章 内存管理
oracle实例启动时需要分配共享内存，启动后台进程。
4.1 SGA管理
4.1.1 什么是SGA
sga(system global area)系统全局区，是一块用于加载数据、对象并保存运行状态和数据库控制信息的一块内存区域，SGA隶属于实例，在实例启动时分配，实例关闭时释放。
在数据库启动到nomount状态时，SGA已经分配，
sql> show sga;
连接到数据库的用户可以共享SGA中的数据，加大SGA区的大小，可以有效地较少物理读，从而提高性能。
SGA的组成：
1).Buffer Cache 缓冲区高速缓存，用户存储最近使用的数据块。
9i前，Buffer Cache的设置：db_block_buffers设置Buffer Cache 缓冲区数量(block块的数量) 乘以 db_block_size(block块的大小设置) 才是Buffer Cache的大小。
sql> select name,value from v$parameter where name in ('db_block_buffers','db_block_size');
9i开始，引入的新的参数：db_cache_size(用于定义主block size的Default缓冲池的大小)。
SGA总大小由sga_max_size参数定义。
粒度(granule)：是连续虚拟内存分配的单位，由SGA总大小（sga_max_size参数）确定。sga<128M,粒度=4M；否则粒度=16M.
sql> select name,value from v$parameter where name in ('db_cache_size','sga_max_size'); 
粒度大小受内部隐含参数_ksmg_granule_size的控制：
sql> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ from sys.x$ksppi x,sys.X$ksppcv y where x.inst_id=userenv('Instance') and y.inst_id=userenv('Instance') and x.indx=y.indx and x.ksppinm like '%&par%';
_ksmg_granule_size=4194304 4M 粒度的大小
多缓冲池技术：指根据不同数据的不同访问方式，将Buffer Cache分为Default(未指定存储池的数据)、keep(经常使用的数据)和Recycle(一次性读取的数据)池。
Default池大小参数db_cache_size（8i是 db_block_size * db_block_buffers）,默认Default池的大小就是缓冲区Buffer Cache的大小。
建表时指定存储子句storage(buffer_pool keep),该表就使用keep缓冲区，初始参数db_keep_cache_size。
建表时指定存储子句storage(buffer_pool recycle),该表就使用recycle缓冲区，初始参数db_recycle_cache_size。
sql> show parameter cache_size; --db_cache_size =0 ？？？
sql> alter system set db_keep_cache_size=4M;
sql> show parameter cache_size; --db_keep_cache_size值由0更改为16M ？？？
缓冲区设置的查询：
sql> select id,name,block_size,current_size,target_size from v$buffer_pool;
该视图中查到的Default池为400M。
2).shared pool(共享池) 包含共享内存结构，如sql区等。sql区包含sql解析树、执行计划等信息，多次执行的sql可以在session间共享。
共享池大小由参数shared_pool_size定义，最小为1个粒度。
3).redo log buffer(日志缓冲区) 存储重做日志条目(redo entries)，日志记录数据库变更，被写出到重做日志文件中，用于数据库恢复；
如果数据库运行在归档模式下，日志被写出到归档日志中，可用于数据恢复。
日志缓冲区大小由参数log_buffer定义.
4).large pool(大池) 用于共享服务器模式(MTS)、并行计算或RMAN的备份恢复等操作。参数：large_pool_size
5).java pool(java池) 用于jvm等Java选件。参数：java_pool_size
6).streams pool 为oracle的streams功能所使用，才从sgread pool中分出来。
通过视图v$sga查询sga大概设置：
sql> select * from v$sga;/show sga;
fixed size sga中的固定部分，包含数据库和实例的状态信息。不存储用户数据。
variable size 包括：shared_pool_size(共享池)、java_pool_size(java池)、large_pool_size(大池) 
sql> select sum(value) from v$parameter where name in ('shared_pool_size','java_pool_size','large_pool_size');
database buffer 指Buffer Cache 包括：db_cache_size，db_keep_cache_size，db_recycle_cache_size
redo buffer 指日志缓冲区 log_buffers
通过试图v$sgastat查询sga具体信息：
sql> select * from v$sgastat;
sql> select * from v$sgainfo; resizeable为yes说明是可以调整大小的；为no的则不能调整。
4.1.2 SGA与共享内存
oracle SGA设置与操作系统的关联：
windows系统采用多线程服务器(即oracle server process是一个进程中的线程) ，不存在共享内存的问题，无需设置。
Linus/UNIX系统需要设置参数shmmax,参数的问题在不同的系统上可能不同，Solaris上/etc/system文件中shmsys:shminfo_shmmax定义；Linux上/proc/sys/kernel/shmmax参数定义。
shmmax内核参数定义的是系统允许的单个共享内存段的最大值，一般建议大于oracle SGA；如果小于oracle SGA，那么SGA被分配到多个共享内存段中。
Linus例子：shmmax默认32MB
[root]# more/proc/sys/kernel/shmmax --查看shmmax内核参数
33554432 =32M
[root]# cat/etc/sys/redhat-release
Red Hat Enterprise Linux AS release 3 --操作系统版本：红帽子3
[root]# uname -r
2.4.21-15.ELsmp
[root]# ipcs -sa --查看共享内存的分配，为创建oracle SGA分配了27个共享内存段
[root]# ps -ef|grep dbw --使用pmap工具查看共享内存段的地址空间
[root]# echo 1073741824 > /proc/sys/kernel/shmmax 为了避免多个共享内存段，可以修改shmmax内核参数为1G
[root]# more /proc/sys/kernel/shmmax --查看shmmax内核参数
1073741824 =1G --对shmmax文件的修改系统重启后复位，可以修改etc/sysctl.conf文件使更改永久化
在etc/sysctl.conf文件中添加一行 kernel.shmmax = 1073741824 重启系统生效，重启数据库
sql> shutdown immediate; --关闭数据库
[root]# ipcs -sa --查看共享内存的分配，共享内存段已经释放
没有修改shmmax参数，oracle在启动过程中就会在alert_<sid>.log文件中记录警告。
4.1.3 SGA管理的变迁
oracle 8i 静态SGA管理：
修改SGA参数，先关闭实例，修改参数文件init.ora,重启数据库才能生效。
oracle 9i 动态SGA管理：不需要重启数据库

第八章 等待事件 p354
通过等待事件发现性能瓶颈（从动态性能视图展现），进行性能分析和优化。
8.1 等待事件的起源
等待事件(v$event_name)的数目与数据库版本(v$version)相关。
select * from v$version;--查看数据库版本
select count(*) from v$event_name;--等待事件数量(oracle10gR2 := 872)
所有的800多等待事件分为12类：
select distinct wait_class#,wait_class from v$event_name order by wait_class#;
wait_class#=6,wait_class=idle 空闲等待事件：指oracle正在等待任务，与诊断和优化无关；其余等待事件则与诊断和优化相关。
各类等待事件的个数：
select wait_class#,wait_class,count(1) from v$event_name group by wait_class#,wait_class_id,wait_class order by wait_class#;
v$event_name中字段说明：
select event#,name,event_id,wait_class#,wait_class,wait_class_id,parameter1,parameter2,parameter3 from v$event_name where (parameter1 is not null or parameter2 is not null or parameter3 is not null) and wait_class#<>6;
event#,name,event_id 等待事件的编号、名称、序号；wait_class#,wait_class,wait_class_id 等待事件分类的编号、名称、序号；parameter1,parameter2,parameter3 参数值，非常重要的。
v$system_wait_class 获取每个等待事件分类的总计等待时间和次数：
select wait_class#,wait_class,wait_class_id,total_waits,time_waited from v$system_wait_class order by time_waited;
空闲等待事件：
select wait_class,event#,name,parameter1,parameter2,parameter3 from v$event_name e where wait_class='Idle';
8.2 从等待发现瓶颈
v$session :是数据库当前连接的session信息。
v$session_wait :是数据库当前连接的活动session正在等待的资源或事件信息。
v$system_event :是数据库启动以来所有等待事件的汇总信息。
8.2.1 v$session和v$session_wait
v$session_wait字段说明：
select event,p1text,p1,p1raw,seq#,wait_time,seconds_in_wait,state from v$session_wait w;
event 事件名；p1text,p1,p1raw 对应v$event_name中的参数值、十进制参数值、十六进制参数值；seq#,wait_time,seconds_in_wait,state 等待信息
oracle 10 R1 后 v$session_wait被整合到v$session中，此外还增加了blocking_session。R2 后增加：service_name,sql_trace,sql_trace_waits,sql_trace_binds用于显示当前session连接方式及是否启用sql_trace跟踪等。
8.2.2 v$sqltext
select sid,event,p1,p1text from v$session_wait order by event;
发现大量db file scattered read 及db file sequential read等待，全表扫描位于文件号为17的数据文件上。
db file scattered read(DB文件分散读取)与全表扫描相关的等待，将分散的数据读入Buffer Cache,没有创建索引或索引不合适导致。
v$sqltext的字段：
select address,hash_value,sql_id,command_type,piece,sql_text from v$sqltext t;
sql_text存放sql语句
8.2.3 捕获相关sql 
v$sqltext与v$session的关联： 
select sid, sql_text from v$sqltext t,v$session s where t.hash_value=s.sql_hash_value;
检查sql执行计划：在cmd中有效
C..>sqlplus /nolog --启动sqlplus
sql> connect tt/tt; --连接用户
sql> set autotrace trace explain; --启用执行计划
sql> select .. from .. where ..; --需要分析的sql
分析结果及展现方式与pl/sql developer下的Explain Plan Window 完全一致。
8.3 oracle 10g 的增加
v$session_wait会随session的消失而消失，即不保留历史信息。
8.3.1 v$session_wait_history视图：用以记录每个活动session最近10次的等待事件。
select sid,seq#,event#,event,wait_time,wait_count,p1text,p1,p2text,p2,p3text,p3 from v$session_wait_history;
sid :session编号；seq#：session下等待事件的编号；event#：等待事件的编号；event：等待事件的名称；wait_time：等待时间；wait_count：等待次数；p1text：参数值1；p1：十进制参数值1
每个sid下有10个seq#。
8.3.2 ASH新特性
Active Session History(ASH)活动session历史信息记录，记录活动会话等待的事件。每秒从v$session采样一次，采样由后台进程MMNL完成。
查看oracle内部隐含参数：不建议用户查询或者修改。
select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ from sys.x$ksppi x,sys.x$ksppcv y
where x.inst_id=USERENV('Instance') and y.inst_id=USERENV('Instance') and x.indx=y.indx;
ASH功能是否启用由参数_ash_enable控制：
select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ from sys.x$ksppi x,sys.x$ksppcv y
where x.inst_id=USERENV('Instance') and y.inst_id=USERENV('Instance') and x.indx=y.indx and x.ksppinm='_ash_enable';
ASH采样时间由参数_ash_sampling_interval控制：
select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ from sys.x$ksppi x,sys.x$ksppcv y
where x.inst_id=USERENV('Instance') and y.inst_id=USERENV('Instance') and x.indx=y.indx and x.ksppinm='_ash_sampling_interval';
ASH记录通过视图v$active_session_history访问：
select * from v$active_session_history;
ASH信息被设计为在内存中滚动，在SGA（系统全局区域）中分配：
select * from v$sgastat where name like '%ASH%';
select * from v$sgastat; --SGA（系统全局区域）的内存分配
ASH buffers的大小算法：
Max(Min(cpu_count*2MB,5%*SHARED_POOL_SIZE,30MB),1MB) 即：最大30MB,最小1MB
如果SHARED_POOL_SIZE没有定义时，将5%*SHARED_POOL_SIZE替换为2%*SGA_TARGET
select name,value,display_value from v$parameter where name in ('shared_pool_size','cpu_count','sga_target');
生成ASH报告的两种方式：
1.调用$ORACLE_home/rdbms/admin/ashrpt.sql脚本，
sql> @?/rdbms/admin/ashrpt.sql;
文件类型:text,开始时间:-15，结束时间:当前时间，报表名称:
2.使用oem图形方式：性能页--》点击“运行ASH报告”即可生成。
ASH概况信息，等待事件信息，等待参数信息，TopSQL信息。
8.3.3 自动负载信息库AWR的引入(automatic workload repositoy)
AWR将数据库的操作统计信息和其他统计信息由每后台进程MMNL通过direct-path insert(直接路径插入)60分钟写入磁盘一次，并保存一周。
参数：statistics_level(basic,typical,all)统计信息的收集设置,_ash_disk_filter_ratio(隐含参数) ASH的写出比例.
select name,value,display_value from v$parameter where name in ('statistics_level');
select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ from sys.x$ksppi x,sys.x$ksppcv y
where x.inst_id=USERENV('Instance') and y.inst_id=USERENV('Instance') and x.indx=y.indx and x.ksppinm='_ash_disk_filter_ratio';
AWR的基础表wrh$_active_session_history(分区表)，视图dba_hist_active_sess_history.
V$session-->v$session_wait-->v$session_wait_history(记录了最近10次等待)-->v$active_session_history(记录1个小时的内容)-->wrh$_active_session_history(存储1星期)-->dba_hist_active_sess_history视图
sql> select occupant_name,occupant_desc,schema_name,space_usage_kbytes/1024 "MB" from v$sysaux_occupants where occupant_name like '%AWR%';
8.3.4 自动数据库诊断监控ADDM(automatic database diagnostic monitor)的引入
ADDM可以定期检查数据库的状态，确定性能瓶颈，提供调整建议。
8.4 顶级等待事件
视图v$system_event是数据库自启动以来等待事件的汇总，
sql> select * from (select event,time_waited from v$system_event order by time_waited desc) where rownum<10;
视图v$session_longops
sql> select opname from v$session_longops;
从中发现问题sql及问题sql查询的物理表，给表增加适当的索引即可。
8.5 重要等待事件
8.5.1 db file sequential read(数据文件顺序读取)
显示与单个数据块相关的读取操作，读取一个索引块或者通过索引块读取一个数据块时记录该等待事件。
sql> select name,wait_class,parameter1,parameter2,parameter3 from v$event_name where name='db file sequential read';
参数p1:要读取的文件的绝对文件号；参数p2:开始读取的数据块块号；参数p3:读取的BLOCK数量。
该等待事件显著表明：多表连接中连接顺序有误，索引使用有问题。
视图v$segment_statistics: 找出物理读取显著的索引段或者表段，通过重建或分区、调整存储参数等手段降低其I/O访问。
sql> select * from v$segstat_name;
sql> select * from v$segment_statistics;
8.5.2 db file scattered read(数据文件离散读取)
sql> select name,wait_class,parameter1,parameter2,parameter3 from v$event_name where name in ('db file sequential read','db file scattered read');
参数p1:文件号；参数p2:起始数据块号；参数p3:数据块数量。

第9章 性能诊断与sql优化
9.1 使用autotrace功能辅助sql优化
9.1.1 autotrace功能的启用（10g之前，autotrace功能并未打开，需要通过以下步骤手工启动）
1.创建基础表
运行$ORACLE_HOME/rdbms/admin/utlplan.sql脚本，用于创建plan_table表： 
C:/> sqlplus /nolog
sql> connect / as sysdba;
sql> @?/rdbms/admin/utlplan.sql; --运行脚本创建plan_table表
sql> create public synonym plan_table for plan_table; --创建同义词
sql> grant all on plan_table to public; --授权
2.创建plustrace角色
运行$ORACLE_HOME/sqlplus/admin/plustrce.sql脚本， 
sql> @?/sqlplus/admin/plustrce.sql; --运行脚本创建plustrace角色
3.一点增强
dba用户被授予了plustrace角色,在给其他用户也授权。
sql> grant plustrace to public; --授权后就可以使用autotrace功能
autotrace的选项说明：
set autotrace off : 不生成autotrace报告，默认设置 （查询结果）
set autotrace trace explain : （执行计划）
set autotrace trace statistics : （统计信息）
set autotrace on explain : autotrace只显示优化器执行路径报告 （查询结果+执行计划）
set autotrace on statistics : 只显示执行统计信息 （查询结果+统计信息）
set autotrace traceonly : 同set autotrace on,但不显示查询输出。（执行计划+统计信息）
set autotrace on : 包含执行计划和统计信息。 （查询结果+执行计划+统计信息）
9.1.2 oracle 10g autotrace功能的增强（良好的格式化及简要注解）
dbms_xplan用于格式化和查看sql的执行计划。
sql> select * from table(dbms_xplan.display(format=>'BASIC')); ???
sql> explain plan for select count(*) from dual;
sql> @?/rdbms/admin/utlxplp.sql;
select * from table(dbms_xplan.display()); 
oracle 10g autotrace 自动完成了输出的格式化，oracle 10g不需要创建基础表plan_table，在字典表中已经存在plan_table$,并存在同义词plan_table。
9.1.3 autotrace功能的内部操作（启动2个session连接，一个用于执行查询，一个用于记录执行计划和输出最终结果）
启用autotrace之前的session连接：
sql> set autotrace off; --关闭autotrace
sql> select sid,serial#,username from v$session where username is not null;
sql> set autotrace on; --启用autotrace，有两个session
sql> select a.sid,a.serial#,a.username,b.pid,b.spid from v$session a,v$process b where a.paddr=b.addr and a.username is not null; 
v$process.spid是操作系统进程号，即一个进程在数据库中可能对应多个session.
p423

9.3 使用sql_trace/10046事件进行数据库诊断
sql_trace/10046事件是进行sql跟踪的手段，是强有力的辅助诊断工具。
9.3.1 sql_trace/10046事件的基础
1.sql_trace说明
sql_trace是静态参数，设置为true可以收集信息用于性能优化或问题诊断（dbms_system包功能与之类似），但会严重影响系统性能，只对特定session启用跟踪，可以使用alter session 或 dbms_system.set_trace_in_session；对数据库启用sql_trace则需满足：至少有25%的cpu idle,为user_dump_dest分配足够的空间，条带化磁盘以减轻IO负担。
使用alter session set sql_trace 来修改session级设置，不会更改v$parameter视图，所以说sql_trace是静态参数。
sql_trace启用前的设置：
timed_statistics设置为true,否则一些重要信息不会被收集。
MAX_DUMP_FILE_SIZE设置为unlimited(默认)，sql> alter session set MAX_DUMP_FILE_SIZE=unlimited;
p440 

本文转自东方之子736651CTO博客，原文链接：http://blog.51cto.com/ecloud/1389412 ，如需转载请自行联系原作者