Oracle常用问题及解答
Oracle基础操作类
1.请写出如何添加数据文件的语句
Alter tablespace data add datafile ‘/oracle/oradata/CFGDB/data01.dbf’ size 100m
2.请写出如何启动归档模式
Shutdown immediate startup mount alter database archivelog alter database open
3.请写出如何查看当前被锁的表
Select t1.object_name from dba_objects t1,v$session t2,v$locked_object t3 where t1.object_id=t3.object_id and t2.sid=t3.session_id;
4.请写出如何查看某张表的索引
Select * from dba_indexes where table_name=’’;
5.请写出如何查看某张表的大小
Select * from dba_segments where segment_name=’’ and segment_type=’TABLE’;
6.请写出如何查看某用户使用的默认表空间
Select username,default_tablespace,temporary_tablespace from dba_users where username=’’;
7.请写出如何查看各个表空间的大小
select tablespace_name,bytes/1024/1024 from dba_data_files;
8.请写出如何查看当前数据库存在的等待事件
select * from v$session_wait; select * from v$waitstat; desc v$waitstat
9.请写出如何生成AWR报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
10.请写出切换日志的语句
alter system switch logfile;
11.请写出如何通过sqlid获得该SQL的执行计划
explain plan for select * from ...
select * from table(DBMS_XPLAN.DISPLAY_AWR(SQL_ID));10G以后。
SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR ('danxh10kwn2s3',NULL,NULL，‘BASIC’));可以把BASIC 换成 ADVANCE 和TYPICAL试试。
12.请写出如何锁住某个用户
alter user SN account lock;
alter user SN account unlock;
13.请写出如何查询某表的统计信息
dbms_stats.gather_table_stats(user,'table',cascade=>true);
DBMS_STATS包中用于收集统计信息的过程包括：
dbms_stats.gather_table_stats 收集表、列和索引的统计信息；
dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息；
dbms_stats.gather_index_stats 收集索引的统计信息；
dbms_stats.gather_system_stats 收集系统统计信息。
dbms_stats.delete_table_stats 删除表的统计信息
dbms_stats.export_table_stats 输出表的统计信息
dbms_stats.create_state_table
dbms_stats.set_table_stats 设置 表的统计
dbms_stats.auto_sample_size
dbms_stats.gather_database_stats： 收集数据库中所有对象的统计信息；
14.请写出如何跟踪某session执行的sql
exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);
select sid,serial# from v$session where sid = (select sid from v$mystat where rownum = 1);
exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,'');
15.请写出如何查看当前数据库在运行的事务
select * from v$transaction;
16.请写出设置sga_target为10G的语句
alter system set sga_target=10240m
17.请写出如何根据sqlid获得该SQL是否使用绑定变量
select sql_id,sql_text,executions from v$sqlarea; 看executions的数量特别大说明没有绑定变量
减产前40个字符相同, 有多于5个不同sql版本的sql语句
select substr(sqltext,1,40) sql_text,count(*) from v$sql group by substr(sqltext,1,40) having count(*) >= 5;
18.请写出如何获得某latch的sleep数量
select name,gets,misses,immediate_gets,immediate_misses,sleeps from v$latch;
19.请写出如何根据操作系统进程号获得oracle 会话号
select s.sid from v$session s,v$process p where p.addr=s.PADDR and p.spid='25783'
select sid from v$session where process='25817';
SQL语句杀掉长期没有释放非正常的锁：
alter system kill session 'sid,serial#';
20.请写出如何查询当前数据库日志的切换时间
select archived,status,first_time from v$log;
Oracle概念类
1.请简述oracle SGA中各内存区域的功能
buffer cache,log buffer,shared pool,big pool,java pool,streams pool
数据高速缓冲区（buffer cache）：
用来保存用户最近从磁盘数据文件访问的数据块的副本，这样当再由用户访问相同的数据时，则可以直接从数据高速缓冲区读取，不用再访问磁盘（注：磁盘I/O的速率是毫米级的，而内存I/O的速率为纳秒级）
包括三个类型区
脏的区（dirty buffer）：用来保存从磁盘数据文件读取来的、已经修改过、并且还没有写入磁盘数据文件的数据。
自由区（free buffer）：没有任何数据，并且可以写入数据的空闲内存区；
保留区（pinned buffer）：用来保存正在被使用，或者明确留作将来使用的数据，主要指，从磁盘数据文件内读取到、等待服务器进程更改的数据，或者正在被更改的数据。
数据高速缓冲区可配置一个或2个或3个缓冲区池，我们在create或alter表或索引时，可以指定该表或索引属于哪个缓冲区池；
保持缓冲区池（db_keep_cache_size）：用来保存一些需要频繁访问的小表，防止这些小表的数据被LRU命中；
回收缓冲区池（db_recycle_cache_size）：用来保存一些很少使用的大表，在缓区处理完之后，立即清除，节省缓存空间；
默认缓冲区池（db_cache_size）：用来保存除db_keep_cache_size和db_recycle_cache_size之外的所有数据；
子高速缓存(db_nk_cache_size)：主要针对不同的oracle块尺寸来设置的，
数据高速缓冲区尺寸=db_keep_cache_size+db_recycle_cache_size+db_cache_size+db_nk_cache_size；
重做日志缓存（log buffer):
用来保存数据库内的变更，由LGWR立即写入联机重做日志文件
共享池（shared_pool_size）：
包括库高速缓存和数据字典高速缓冲两部分
库高速缓存：主要用来保存分析过的sql代码，所谓的“分析”是指，校验用户请求sql代码的语法，并且判断sql代码涉及的表和字段在数据库内是否存在；
调优手段：通过动态视图v$librarycache，判断公式：sum(reloads)/sum(pins)，如果结果约等于0，则证明库高速缓存命中率是合适的；如果结果>1，则需要增大共享池设置(shared_pool_size)；
数据字典高速缓存：主要用来保存服务器最近从磁盘数据字典内访问过的数据，主要包括表结构、用户、实体等信息，如果再有用户请求时，则服务器进程会首先从数据字典高速缓存内检索，在缓冲区内是否存在该请求用户的相关权限信息，如果没有的话，需要访问磁盘数据字典（注：访问磁盘数据字典是最消耗资源的，相对于访问其他磁盘文件来说）
调优手段：通过动态视图v$rowcache，判断公式：(1-sum(getmisses)/(sum(getmiss)+sum(getmisses)))*100，如果结果>90，证明命中率是合适的，否则需要增大共享池设置；（注：oracle没有直接设置库高速缓冲和数据字典高速缓冲的地方，需要通过调整共享池尺寸实现）
大型池(large_pool_size):
主要为执行一些非常复杂并且耗时很长的sql代码提供空间，例如在执行备份操作时，需要存放备份缓冲区；
Java池(java_pool_size):
主要为实例化的Java对象提供的堆空间，如果数据库中存在大量的java代码，就可以使用该设置，不用占用常规的sga；
流池(streams_pool_size):
oracle支持流传输，主要用来实现与不同类型数据库，或者在不同应用环境下的数据共享；
其他结构（包括数据控制和锁的管理）
pga（程序全局区）：每个用户会话都会在pga上占用一段独占的内存空间，pga上从属于该用户会话的内存空间，只允许该用户使用，其他用户不能访问。pga通过pga_size_mode可以设置自动或是手动内存配置。
2.如何查看执行计划，查看执行计划的作用是什么
一、explain plan for select * from ......
select * from table(DBMS_XPLAN.Display)
二、set autotrace on;
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询
三、SQL_TRACE
1.alter session set sql_trace=true;
2.执行sql
3.alter session set sql_trace=false;
4.查看相应的sql trace文件。
作用：
语句所引用表的顺序；
语句所涉及的表的访问方法；
语句中连接操作所影响到的各表的连接方法。
----各种表访问方式需要明白
3.请写出控制文件的作用
作用：
二进制文件
记录了数据库当前实例的结构和行为，数据文件日志文件的信息，维护数据库一致性
参数文件中定义了控制文件的位置和大小
很小的二进制文件，一般不超过100m
mount阶段open以后，一直在用
一套控制文件只能连接一个database
分散放置，至少一份，至多八份
control files作用:
参数文件init.ora 记录了控制文件的位置，控制文件是一个非常小的二进制文件，最大可以增长到64MB，控制文件包括如下主要信息：
·数据库的名字，检查点信息，数据库创建的时间戳
·所有的数据文件，联机日志文件，归档日志文件信息
·备份信息等
有了这些信息，Oracle 就知道那些文件是数据文件，现在的重做日志文件是哪些，这些都是系统启动和运行的基本条件，所以他是Oracle 运行的根本。如果没有控制文件系统是不可能启动的。控制文件是非常重要的，一般采用多个镜相复制来保护控制文件，或采用RAID 来保护控制文件。控制文件的丢失，将使数据库的恢复变的很复杂。控制文件信息可以从V$Controlfile 中查询获得
一.控制文件的作用
控制文件记录了物理数据库的当前状态。一般为3个，三个控制文件都是一摸一样的，最好是分开放在不同的磁盘上。
二.控制文件的内容
2.1数据库的名字；
数据库的标示符：创建数据库是oracle自动生成的；
数据库创建的时间戳：它是在数据库创建时生成的；
联机重做日志文件的名字和准确位置；
当前日志的学历号：它是在日志切换时oracle记录的；
日志的历史信息：它是在日志切换时oracle记录的；
归档日志文件的准确位置和状态；
数据文件的名字和准确位置；
表空间的信息；
备份的准确位置和状态；
三.添加和移动控制文件
5.1在一个已经安装的的oracle数据库中使用正文初始化参数文件（pfile）添加或者移动控制文件的具体步骤。
a).利用数据字典v$controlfile来获取现有的控制文件名字；
b).正常关闭数据库，用命令shutdown或者shutdown immediate;
c).将新的控制文件名添加到参数文件的control_files参数中；//区别
d).使用操作系统命令将现有的控制文件复制到指定位置；
e).重新启动数据库，用命令startup;
f).利用数据字典v$controlfile来验证新的控制文件名字是否正确；
g).如果有误，重复上述操作；如果无误，就删除无用的旧的控制文件；
5.2如果使用了服务器初始化参数（spfile），步骤会有些区别。下面是具体步骤；
a).利用数据字典v$controlfile来获取现有的控制文件名字；
b).修改spfile,使用alter system set control_files命令来改变控制文件的位置；//区别
c).正常关闭数据库，用命令shutdown或者shutdown immediate;
d).使用操作系统命令将现有的控制文件复制到指定位置；
e).重新启动数据库，用命令startup;
f).利用数据字典v$controlfile来验证新的控制文件名字是否正确；
g).如果有误，重复上述操作；如果无误，就删除无用的旧的控制文件；
相关视图：
v$controlfile ---- 列出实例的所有控制文件的名字和状态
v$parameter ---- 列出所有参数的位置和状态（where name='control_files'）
v$controlfile_recode_section ---- 提供控制文件的记录部分的信息
show parameter control_files ---- 列出控制文件的名字、状态和位置
查看控制文件内容：
strings $ORACLE_BASE/oradata/$ORACLE_SID/control01.ctl
alter database backup controlfile to trace as ''
转储控制文件：
alter system set events 'immediate trace name controlf level 10'
level 1 块头
level 2 文件内容
level 3 1+2
level 10 全部
管理控制文件：
spfile 修改spfile参数control_files（alter……）
一致性关闭数据库
增加或减少控制文件
启动数据库使用spfile
验证结果
pfile 一致性关闭数据库
修改pfile参数
增加或减少控制文件
启动数据库使用spfile
验证结果
状态查询：参数和具体文件是否对应、磁盘空间剩余、控制文件<100M
热备控制文件：
数据库归档模式下，alter database backup controlfile to ''
其他模式下：alter database backup controlfile to trace as ''得到建立控制文件脚本
数据库归档模式下rman备份：
rman target /
>backup current controlfile; or
>backup database include current controlfile;
rman自动备份控制文件：
>show all中的configure controlfile autobackup设置为on
>configure controlfile autobackup on
问题解决：
控制文件不一致：
1. 拷贝新版本的控制文件到旧版本文件
2. 修改参数文件，只使用新版本的文件（alter system set……），重启
控制文件丢失：
【mount阶段，使用archive log list可以查看数据库是否在归档模式下】
1. 控制文件无备份
a.数据库非归档，数据文件联机日志文件存在 --- 重建控制文件
>create controlfile reuse database noarchivelog noresetlogs datafile <'datafiles'> logfile
2. 控制文件有备份
a.数据库归档，数据文件联机和归档日志文件存在 --- 还原控制文件
先全备，再将控制文件拷贝到参数指定的路径，然后>alter database recover database using backup controlfile until change;
4.请写出SCN的作用
SCN是当Oracle数据库更新后，由DBMS自动维护去累积递增的一个数字。Oracle数据库中一共有4种SCN分别为
系统检查点SCN： 系统检查点SCN位于控制文件中，当检查点进程启动时(ckpt)，Oracle就把系统检查点的SCN存储到控制文件中。该SCN是全局范围的，当发生文件级别的SCN时，例如将表空间置于只读状态，则不会更新系统检查点SCN。
数据文件检查点scn：当ckpt进程启动时，包括全局范围的（比如日志切换）以及文件级别的检查点（将表空间置为只读、begin backup或将某个数据文件设置为offline等），这时会在控制文件中记录的scn。
结束scn：每个数据文件都有一个结束scn，在数据库的正常运行中，只要数据文件在线且是可读写的，结束scn为null。否则则存在具体的scn值。结束scn也记录在控制文件中。
开始scn：不同于上述的SCN数据文件开始scn记录在每个数据文件中。当发生系统及文件级别的检查点后，不仅将这时的SCN号记录在控制文件中，同样也记录在数据文件中。
SCN的机制 数据库运行时的SCN
Oracle事务
我们先看下oracle事务中的数据变化是如何写入数据文件的：
一、事务开始；
二、在buffer cache中找到需要的数据块，如果没有找到，则从数据文件中载入buffer cache中；
三、事务修改buffer cache的数据块，该数据被标识为“脏数据”，并被写入log buffer中； ----这个“脏数据”是什么进程写入到log buffer中的
四、事务提交，LGWR进程将log buffer中的“脏数据”写入redo log file中；
五、当发生checkpoint，CKPT进程更新所有数据文件的文件头中的信息，DBWr进程则负责将Buffer Cache中的脏数据写入到数据文件中。
5.请写出log file sync等待事件的产生原因
①．高提交频率
解决方法是简单的消除不必要的提交，事务是工作单元。工作单元应该是全部成功或全部失败。
②．缓慢的I/O子系统
较高的IO吞吐量可以改善log file sync和log file parallel write事件的平均等待时间。频繁的提交会弄乱数据库布局和IO子系统。
解决办法是将日志文件放裸设备上或绑定在RAID 0或RAID 0+1中，而不是绑定在RAID 5中。
③．过大的日志缓冲区
过大的日志缓冲区也可能延长log file sync等待。大型的日志缓冲区减少后台写入的数量，允许LGWR变得懒惰，并导致更多的重做条目堆积在日志缓冲区中。同时可以调整参数_LOG_IO_SIZE参数，其默认值是LOG_BUFFER的1/3或1MB，取两者之中较小的值。换句话说，你可以具有较大的日志缓冲区，但较小的_LOG_IO_SIZE将增加后台写入，从而减少log file sync的等待时间。
Statspack之十四-"log file sync" 等待事件：
当一个用户提交(commits)或者回滚(rollback),session的redo信息需要写出到redo logfile中.
用户进程将通知LGWR执行写出操作,LGWR完成任务以后会通知用户进程.
这个等待事件就是指用户进程等待LGWR的写完成通知.
对于回滚操作，该事件记录从用户发出rollback命令到回滚完成的时间.
如果该等待过多，可能说明LGWR的写出效率低下，或者系统提交过于频繁.
针对该问题，可以关注:
log file parallel write等待事件
user commits,user rollback等统计信息可以用于观察提交或回滚次数
解决方案:
1.提高LGWR性能。尽量使用快速磁盘，不要把redo log file存放在raid 5的磁盘上
2.使用批量提交
3.适当使用NOLOGGING/UNRECOVERABLE等选项
6.请写出同一条记录insert，delete，update操作中产生undo信息的大致内容
insert操作无论是1条或者多条，一次产生一条undo数据
delete操作产生的undo条数取决于delete操作本身影响的记录数
update操作产生的undo条数取决于被update的数据的原值和新值存在差异的记录数量
测试中表没有主键,如果有主键,所有操作都会增加一条undo数据.在删除或者update大量数据时候会产生大量undo,
所以建议这两种操作进行分批提交.
select used_ublk,used_urec from v$session s, v$transaction t where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
7.请写出latch的工作原理
latch是一种轻量级用于保护oracle共享内存结构，用户并发操作一致性的串行化锁定机制，如SGA中，各种数据被反复从磁盘读取到内存，又被重新写回到磁盘上，如果有并发用户做相同的事情，oracle必须使用一种机制来保证数据在读取的时候，只能由一个会话来完成，这就是latch,latch 不会造成阻塞，是只会等待，与每个latch相联系的还有一个清除过程，当持有latch的进程成为死进程时，系统清除过程就会被调用，系统lock导致 用户等待，需要考虑系统的逻辑设计是否有问题，如多用户对主键的删除或者修改，是否有用户使用select… for update这样的语法，外键是否创建索引。latch 争用多半要考虑系统及数据库自身设计问题，如绑定变量，热块及参数设置是否合理。
spin：比如数据缓存中的某个块要被读取，我们会获得这个块的 latch，这个过程叫做spin，另外一个进程恰好要修改这个块，他也要spin这个块，此时他必须等待，当前一个进程释放latch后才能spin 住，然后修改，如果多个进程同时请求的话，他们之间将出现竞争，没有一个入队机制，一旦前面进程释放锁定，后面的进程就蜂拥而上，没有先来后到的概念，并且这一切都发生的非常快，因为Latch的特点是快而短暂。
spin与休眠：
休眠意味着暂时的放弃CPU，进行上下文切换（context switch），这样CPU要保存当前进程运行时的一些状态信息，比如堆栈，信号量等数据结构，然后引入后续进程的状态信息，处理完后再切换回原来的进程状态，这个过程如果频繁的发生在一个高事务，高并发进程的处理系统里面，将是个很昂贵的资源消耗，所以Oracle选择了spin，让进程继续占有 CPU，运行一些空指令，之后继续请求，继续spin，直到达到_spin_count值，这时会放弃CPU，进行短暂的休眠，再继续刚才的动作。
oracle中，latch是一种轻量级的锁。一般来说，latch由三种内存元素成：pid（进程id），内存地址和内存长度。Latch保证对共享数据结构的排它性访问，以此来保证内存结构的完整性不受到损坏。在多个会话同时修改或者检视（inspect）sga中同一个内存结构时，必须串行化访问以保证sga中数据结构的完整性。
进程获取latch有两种模式：willing-to-wait和No_wait。no-wait模式只在少数latch中使用。通过no-wait模式 获取latch的统计信息记录在immediate_gets immediate_misses列中，这些列在v$latch，v$latch_parent，v$latch_children视图中都存在。一般来 说，no-wait模式在第一次获取一些有很多子latch的latch比如redo copy时使用。如果一个进程第一次获取这些子latch中的任何一个失败，它会立即使用no-wait模式询问下一个。只有当采用no-wait模式试图获取所有的子latch都失败以后，才会转而采用willing-to-wait模式。
通过willing-to-wait模式获取latch的统计信息存放在gets和misses列中。每当一个进程用willing-to-wait模式去获取一个latch时，gets都会增加。
如果进程在第一次请求latch时，latch可用，就会直接获得该latch。在修改任何受到保护的数据结构之前，进程会将一些恢复信息写入到latch恢复区，这样当获得latch的进程发生异常时，pmon进程才能够清理该进程持有的latch。
如果请求latch时，该latch不可用，进程就会在cpu中等待一小段时间（spin）然后重新请求latch。如果latch一直不可用，该过程（spin一段时间然后重新请求）会一直重复。重复的次数由隐含参数_spin_count决定，默认值2000。如果在请_spin_count次之内 获得了latch，就对spin_gets和misses列各加一，否则，进程v$session_wait中记录latch free等待事件，然后释放cpu，转入睡眠状态。睡眠一定时间后，进程被唤醒并重复上面的过程，一直到获得latch。在成功获得latch后，才会更行sleep列得统计信息。
由于进程只有在获得latch后才会停止对latch得请求，如果某个持有latch的进程发生异常，其他请求该latch的进程该怎么办？岂不是要一直等待下去？不会的。当一个进程请求latch失败一定次数后，它会请求pmon进程查看该latch的持有者，如果持有进程异常，pmon就会清理该进程，释放latch。
每个latch都有一个从0到13的优先级编号。父latch和独立latch的优先级编号是在oracle内核代码中固定的。子latch是在实例启动时创建，其优先级编号从其父latch继承。使用优先级可以避免死锁。
当一个进程请求no-wait模式的latch时，该latch的优先级编号必须和它当前已经持有的latch的优先级编号相同。
当一个进程请求willing-to-wait模式的latch时，该latch的优先级编号必须比它当前已经持有的latch的优先级编号要大。
进程获取Latch的过程：
任何时候，只有一个进程可以访问内存中的某一个数据块，如果进程因为别的进程正占用块而无法获得Latch时，他会对CPU进行一次spin（旋转），时间非常的短暂，spin过后继续获取，不成功仍然spin，直到 spin次数到达阀值限制（这个由隐含参数_spin_count指定），此时进程会停止spin,进行短期的休眠，休眠过后会继续刚才的动作，直到获取块上的Latch为止。进程休眠的时间也是存在算法的，他会随着spin次数而递增，以厘秒为单位，休眠的阀值限制由隐含参数 _max_exponential_sleep控制，默认是2秒，如果当前进程已经占用了别的Latch，则他的休眠时间不会太长（过长会引起别的进程的 Latch等待），此时的休眠最大时间有隐含参数_max_sleep_holding_latch决定，默认是4厘秒。这种时间限制的休眠又称为短期等待。另外一种情况是长期等待锁存器（Latch Wait Posting），此时等待进程请求Latch不成功，进入休眠，他会向锁存器等待链表（Latch Wait List）压入一条信号，表示获取Latch的请求，当占用进程释放Latch时会检查Latch Wait List，向请求的进程传递一个信号，激活休眠的进程。Latch Wait List是在SGA区维护的一个进程列表，他也需要Latch来保证其正常运行，默认情况下share pool latch和library cache latch是采用这个机制。
如果将隐含参数_latch_wait_posting设置为2，则所有Latch都采用这种等待方式，使用这种方式能够比较精确的唤醒某个等待的进程， 但维护Latch Wait List需要系统资源，并且对Latch Wait List上Latch的竞争也可能出现瓶颈。
数据缓冲池Latch争用
访问频率非常高的数据块被称为热快（Hot Block），当很多用户一起去访问某几个数据块时，就会导致一些Latch争用，最常见的latch争用有：
（1） buffer busy waits
（2） cache buffer chain
Cache buffer chian产生原因：
当一个会话需要去访问一个内存块时，它首先要去一个像链表一样的结构中去搜索这个数据块是否在内存中，当会话访问这个链表的时候需要获得一个Latch，如果获取失败，将会产生Latch cache buffer chain 等待，导致这个等待的原因是访问相同的数据块的会话太多或者这个列表太长（如果读到内存中的数据太多，需要管理数据块的hash列表就会很长，这样会话扫描列表的时间就会增加，持有chache buffer chain latch的时间就会变长，其他会话获得这个Latch的机会就会降低，等待就会增加）。
Buffer busy waits 产生原因：
当一个会话需要访问一个数据块，而这个数据块正在被另一个用户从磁盘读取到内存中或者这个数据块正在被另一个会话修改时，当前的会话就需要等待，就会产生一个buffer busy waits等待。
产生这些Latch争用的直接原因是太多的会话去访问相同的数据块导致热块问题，造成热块的原因可能是数据库设置导致或者重复执行的SQL频繁访问一些相同的数据块导致。
latch有40余种，但作为DBA关心的主要应有以下几种：
Cache buffers chains latch: 当用户进程搜索SGA寻找database cache buffers时需要使用此latch。
Cache buffers LRU chain latch: 当用户进程要搜索buffer cache中包括所有 dirty blocks的LRU (least recently used) 链时使用该种latch。
Redo log buffer latch: 这种latch控制redo log buffer中每条redo entries的空间分配。
Row cache objects latch: 当用户进程访问缓存的数据字典数值时，将使用Row cache objects latch。
下 面我们将着重介绍一下如何检测和减少redo log buffer latch的冲突。对redo log buffer的访问是由redo log buffer latch来控制的，这种latch有两种类型， redo allocation latch和redo copy latch。
Redo allocation latch控制redo entries在redo log buffer中的空间分配。Oracle的一个用户进程只有得到redo allocation latch后才能为redo entries在redo log buffer中分配空间，又由于一个instance只有一个redo allocation latch，所以一次只有一个用户进程在buffer中分配空间。当用户进程获得latch后，首先为redo entry分配空间，然后进程继续持有latch并拷贝entry到buffer中，这种拷贝称为“在redo allocation latch上的拷贝”(copying on the redo allocation latch)，拷贝完毕后，用户进程释放该latch。
一个“在redo allocation latch上的拷贝”的redo entry的最大值是由初始化参数LOG_SMALL_ENTRY_MAX_SIZE定义的，根据操作系统的不同而不同。
Redo Copy Latch只应用于多CPU的系统。在多CPU的instance中，如果一个redo entry太大，超过了LOG_SMALL_ENTRY_MAX_SIZE定义值，则不能进行“在redo allocation latch上的拷贝”, 此时用户进程必须获取redo copy latch。一个instance中可以有多个redo copy latch，其数目由初始参数LOG_SIMULTANEOUS_COPIES决定，缺省值为CPU数目。
在单CPU情况下，不存在redo copy latch，所有的redo entry无论大小, 都进行“在redo allocation latch上的拷贝”。
对redo log buffer的过多访问将导致redo log buffer latch的冲突，latch冲突将降低系统性能，我们可通过如下查询来检测这种latch冲突：
col name for a40
SELECT ln.name,gets,misses,immediate_gets,immediate_misses
FROM v$latch l,v$latchname ln
WHERE ln.name IN('redo allocation','redo copy') AND ln.latch#=l.latch#
/
若misses与gets的比例超过1%或immediate_misses与(immediate_gets+immediate_misses)比例超过1%时，应考虑采取措施减少latch的冲突。
大 多数的redo log buffer latch冲突是在多个CPU情况下，两个或多个Oracle进程试图同时得到相同的latch发生的。由于一个instance只有一个redo allocation latch，为减少redo allocation latch的冲突，应减少单个进程持有latch的时间，这可以通过减小初始参数LOG_SMALL_ENTRY_MAX_SIZE以减小redo entry的数目和大小来实现。如果观察到有redo copy latch冲突，可以通过增大LOG_SIMULTANEOUS_COPIES 初始参数来加大latch数目，其缺省值为CPU数目，最大可增大到CPU数目的两倍。
8.请写出rownum与rowid的区别
rownum只是你查询出来结果集的一个临时排列序号，必须从1开始，可以看作是随便可以变化的！
rowid是对于每一条数据的地址，你可以把它看作是固定的（频繁更新，删除操作除外）
当你从某个表中查询数据的时候，返回的结果集中都会带有rownum这个字段，而且有时候也可以使用rownum进行一些条件查询。
rownum是在得到结果集的时候产生的，用于标记结果集中结果顺序的一个字段，这个字段被称为“伪数列”，也就是事实上不存在的一个数列。它的特点是按顺序标记，而且是逐次递加的，换句话说就是只有有rownum=1的记录，才可能有rownum=2的记录。
和rownum相似，oracle还提供了另外一个伪数列：rowid。不过rowid和rownum不同，一般说来每一行数据对应的rowid是固定而且唯一的，在这一行数据存入数据库的时候就确定了。可以利用rowid来查询记录，而且通过rowid查询记录是查询速度最快的查询方法。(这个我没有试过，另外要记住一个长度在18位，而且没有太明显规律的字符串是一个很困难的事情，所以我个人认为利用rowid查询记录的实用性不是很大)rowid只有在表发生移动(比如表空间变化，数据导入/导出以后)，才会发生变化。
1、ROWNUM
定义： ROWNUM是一个伪列，标识了select从一个表或一组连接(JOIN)的表中查询数据时，返回记录的顺序。
Oracle在执行select查询时，会按照返回的row的顺序，依次为row分配一个序号：
返回的第一条row的序号为1，第二条row的序号为2，以此类推。
这个序号即为每条row的rownum。
注意1：同一个查询语句中，如果ROWNUM后，含有ORDER BY子句时：
Oracle先返回未应用ORDER BY处理的结果，分配ROWNUM后，再根据ORDER BY子句的要求排序。
因此，返回的结果中，ROWNUM顺序是混乱的。如：
SQL> SELECT ROWNUM,DEPT.* FROM SCOTT.DEPT ORDER BY DNAME;
ROWNUM DEPTNO DNAME LOC
-------------- ------------- -------------------- -------------
1 10 ACCOUNTING NEW YORK
4 40 OPERATIONS BOSTON
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
未应用ORDER BY的结果为：
SQL> SELECT ROWNUM,DEPT.* FROM SCOTT.DEPT;
ROWNUM DEPTNO DNAME LOC
-------------- ------------- ---------------------- -------------
1 10 ACCOUNTING NEW YORK
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
4 40 OPERATIONS BOSTON
欲使ORDER BY后的ROWNUM连续，可将ORDER BY放入子查询，如：
SQL> SELECT ROWNUM,T.* FROM (SELECT DEPT.* FROM SCOTT.DEPT ORDER BY LOC) T;
ROWNUM DEPTNO DNAME LOC
-------------- ------------- --------------------- -------------
1 40 OPERATIONS BOSTON
2 30 SALES CHICAGO
3 20 RESEARCH DALLAS
4 10 ACCOUNTING NEW YORK
注意2：对ROWNUM应用>,>=,=,between...and条件，返回结果都为空：
因为，当返回第一条记录，作为结果的第1条，分配rownum=1，
应用>,>=,=,between...and条件判断时，不满足条件，记录删除；
返回下一条记录时，仍作为结果的第1条，分配rownum=1，仍不满足条件，依次类推，结果为空。
select rownum from test where rownum=1; //返回1条记录（结果集中的第一条）
select rownum from test where rownum=2;
//返回0条。根据ROWNUM定义，不难看出，返回第1条记录时，因ROWNUM=1，因此过滤掉。
select rownum from test where rownum>10;
select rownum from test where rownum between 2 and 4;
应用：
1）可通过ROWNUM限制返回结果的记录数（行数）
SQL> select rownum from test where rownum<10;
2）通过ROWNUM为表中某列产生一个唯一（UNIQUE）值
SQL> UPDATE table_name SET column_name = ROWNUM;
//将rownum指定为该行某列的值。
二、ROWID
(一)、概念：行的ROWID标识了该行数据的地址，ROWID包含如下信息：
数据对象编号、
该行数据，在数据文件中的块编号
该行在数据块中的位置（第一行为0）
保存该行数据的数据文件的编号（第一个数据文件为1）
ROWID在插入数据时创建，在删除数据时，删除。
不能手动设置或删除ROWID，ORACLE内部通过ROWID组建索引；
(二)、类型
1、物理ROWID：保存普通表（不含索引组织的表IOT）、聚合表（clustered table）、分区和子分区表、索引、分区和子分区索引中行的地址；
2、逻辑ROWID：保存索引组织表（IOT）的地址；
(三)、物理ROWID
1、类型：物理ROWID包含扩展ROWID与受限ROWID两类
1)、扩展ROWID(extended rowid)
支持表空间关联的数据块地址，有效标识分区表、分区索引以及普通表和索引中的行。
Oracle 8i及更高版本支持extended rowid。
2)、受限ROWID(restricted rowid)
为了向后兼容，如oracle 7及更低版本。
2、扩展ROWID(extended rowid)
a.扩展ROWID使用base64为每一行数据的物理地址进行编码，包含A-Z,a-z,0-9，+以及/。如下查询：
SQL> select rowid,dept.* from dept;
ROWID DEPTNO DNAME LOC
-------------------------------- ------------ -------------------- ----------------
AAAMfKAAEAAAAAQAAA 10 ACCOUNTING NEW YORK
AAAMfKAAEAAAAAQAAB 20 RESEARCH DALLAS
AAAMfKAAEAAAAAQAAC 30 SALES CHICAGO
AAAMfKAAEAAAAAQAAD 40 OPERATIONS BOSTON
b.扩展ROWID格式
扩展ROWID共18位，包含4部分，OOOOOOFFFBBBBBBRRR
a)000000：数据对象编号，标识了数据库中的段；
b)FFF：表空间相关的数据文件编号；
c)BBBBBB：数据文件中的数据块编号；
d)RRR：在数据块中的行编号；
c.DBMS_ROWID包
a)dbms_rowid.rowid_object通过ROWID，获取该数据的对象编号
SQL> select dbms_rowid.rowid_object('AAAMfKAAEAAAAAQAAC') as object_id from dual;
OBJECT_ID
----------------
51146
b)dbms_rowid.rowid_relative_fno通过ROWID获取数据文件编号
SQL> select dbms_rowid.rowid_relative_fno('AAAMfKAAEAAAAAQAAC') as file_no from dual;
FILE_NO
-------------
4
c)dbms_rowid.rowid_block_number通过ROWID，获取该数据的数据块编号
SQL> select dbms_rowid.rowid_block_number('AAAMfKAAEAAAAAQAAC') as block_number from dual;
BLOCK_NUMBER
------------------------
16
d)dbms_rowid.rowid_row_number通过ROWID，获取数据块中的行编号
SQL> select dbms_rowid.rowid_row_number('AAAMfKAAEAAAAAQAAC') as row_no from dual;
ROW_NO
----------
2
3、受限ROWID(resticted rowid)
a.受限rowid用二进制表示每行数据的物理地址，当使用SQL Plus查询时，二进制表示法被转换为varchar2或16进制表示。
SQL> select dbms_rowid.rowid_to_restricted(rowid,1) as restricted_rowid,dept.* from scott.dept;
RESTRICTED_ROWID DEPTNO DNAME LOC
------------------------------- ------------- --------------------- ------------------
00000010.0000.0004 10 ACCOUNTING NEW YORK
00000010.0001.0004 20 RESEARCH DALLAS
00000010.0002.0004 30 SALES CHICAGO
00000010.0003.0004 40 OPERATIONS BOSTON
b.受限rowid格式：
共16位，包含3部分：AAAAAAAA.BBBB.CCCC
a)AAAAAAAA：保存该行数据的数据块编号
b)BBBB：该行数据在数据块中的行编号
c)CCCC：包含该行数据的数据文件编号
(四)、逻辑ROWID(logical rowid)
1、概述：索引组织的表(IOT)中，row保存在索引的叶子节点，可以在块内或块间移动。
因此，这些rows没有固定的物理地址，无法根据物理地址来唯一标识。
Oracle提供了逻辑ROWID，来标识IOT中的行，逻辑ROWID是基于表的主键；
Oracle可根据这些逻辑ROWID为IOT创建第二索引。
每个第二索引使用的逻辑ROWID都包含一个physical guess；physical guess标识了当创建第二索引时，IOT中每个row的块位置；
9.请写出丢失非当前重做日志时应如何处理
Oracle的重做日志文件(Online redo logfile)循环记录了数据库所有的事务。它的大小、个数和存储位置对数据库性能和恢复有重要影响。它一般由大小相同的几组文件构成。我们可以查看数据库视图v$logfile知道redo logfile的个数和存储位置。对每一个Oracle数据库都要求至少具有两个联机重做日志。
每一次新的事务提交时，Oracle将该事务写入日志文件，但并非此时也将修改的数据块写回原数据文件。由于内存读写和磁盘I/O存在几个数量级的效率差别，Oracle通过减少数据文件的物理I/O读写来大大提高数据库的性能；同时，又通过优先写日志文件来保证数据的正确性和一致性。基于这种机制，重做日志文件在数据库的实例恢复和介质恢复时至关重要，是oracle数据库最重要的物理文件之一。
如果数据库在启动时检测到重做日志丢失，数据库将无法启动。如果数据库在运行时切换日志文件组，检测到下一组或者全部的重做日志丢失，数据库将会崩溃。由于磁盘介质损坏或者人为的误删除文件，造成严重后果的事件近期时有发生。本文列举了重做日志丢失的数据库恢复，但如果按照冗余原则合理分布日志文件组的成员，如果工程师了解日志文件的基本原理和使用原则，就完全可以避免出现下列问题。
恢复方法
故障现象
SQL> startup mount
Oracle Instance Started
Database mounted
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/ORACLE/ORADATA/H817/REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
恢复注意事项
以下所列举的恢复方法，都属于不完全恢复或者强制恢复，会丢失当前重做日志中的事务数据。一旦操作不当，将带来数据丢失等严重后果，请遵循以下几个恢复原则：
1.请勿在生产系统上试用。
2.如果生产系统出现重做日志文件丢失的故障，请勿自行操作破坏现场，应该立刻联系Oracle工程师。
3.恢复成功之后，需要马上做一次数据库的全备份。
4.建议重做日志文件一定要实现镜象在不同的磁盘上，避免这种情况的发生。
恢复方法
1.首先检查重做日志文件状态，看看报错的日志文件的状态是否为Current
SQL> select * from v$log;
SQL> select * from v$logfile;
2.如果重做日志文件状态为Inactive，我们可以直接清除该日志文件的内容：
SQL> alter database clear logfile '/ORACLE/ORADATA/H817/REDO01.LOG';
3.如果重做日志文件状态为Current，恢复工作较为复杂，有以下四种情况：
1）通过下面步骤，数据库顺利打开
SQL> recover database until cancel;
Type Cancel when prompted
SQL>alter database open resetlogs;
2）第一种情况的'recover database until cancel' 操作遇到ORA-01547，ORA-01194，ORA-01110错误，需要整个数据库的物理备份，并根据归档日志恢复到错误时间点，前提是数据库是归档模式。
restore old backup
SQL> startup mount
SQL> recover database until cancel using backup controlfile;
SQL> alter database open resetlogs;
3）如果数据库是非归档模式，只能恢复整个物理备份，然后直接打开数据库。这种情况将丢失物理备份至故障发生前的全部数据。
4）如果数据库是非归档模式，且没有物理备份，只能通过特殊的隐含参数，允许数据库不一致的状况下打开数据库。这种恢复方法是没有办法之后的恢复方法，将导致数据库不一致，一般情况下不要采用。如确有需要，请在Oracle的技术人员指导下使用该方法。
l 关闭数据库
SQL>shutdown immediate
l 在init.ora中加入如下参数
_allow_resetlogs_corruption=TRUE
l 重新启动数据库,利用until cancel恢复
SQL>recover database until cancel;
Cancel
l 打开数据库
SQL>alter database open resetlogs;
l 数据库被打开后，马上执行一个全库导出。
关闭数据库，在init.ora中去掉_all_resetlogs_corrupt参数
10.请写出参数文件的作用
下面是是我对参数文件的一点总结
分为二类：
1.初始参数文件：pfile(initialization parameter file)
2.服务器参数文件：spfile(server-side parameter file)
主要的区别是：
1.pfile是文本文件，spfile是二进制文件
2.pfile可以通过文本编辑器修改，spfile主要是通过alter system set parameter=value [scope=memory|spfile|both]修改
oracle启动时，读取参数文件的顺序是：
1.spfile$ORACLE_SID.ORA(创建数据库时创建的spfile)
2.spfile.ora(oracle缺省spfile文件)
3.init$ORACLE_SID.ora(创建数据库时创建的pfile)
4.init.ora(oracle缺省pfile文件)
oracle会按顺序读出这些参数文件，如果在1没有找到就去找2，直到找到。
和参数文件相关常用SQL如下：
1.查看当前使用的spfile和pfile的位置：
show parameter spfile/show parameter pfile
2.查看参数文件的具体设置
show parameters
3.从spfile获取pfile
create pfile='pfilesid.ora' from spfile或者
create pfile='pfilesid.ora' from spfile='/$ORACLE_HOME/dbs/spfile.ora'
4.修改参数
alter system set parameter=value [scope=memory|spfile|both]
1.修改后当前就起作用，重启数据库不起作用
scope=memory
2.修改后当前不起作用，下次重启数据库才起作用
scope=spfile
3.修改后当前起作用，下次重启数据库也起作用
scope=both
Notice：命令alter system set parameter=value在没有加scope选项时，默认值是什么呢？多数朋友可能不加思索就会回答both这答案，实际的情况是，当oracle在以spfile参数启动时，默认值是both,而以pfile启动时，默认值是memory,也就是只修改当前值。所以如果不能确定启动的参数文件，可以用show parameter spfile查看。
5.指定初始参数文件启动数据库
startup pfile='/$ORACLE_HOME/dbs/init$ORACLE_SID.ora'
不能以指定服务器参数文件spfile来启动数据库
可以先将spfile转换成pfile，再用这个命令来达到目的
6.查看系统是以pfile还是spfile启动
show parameter spfile
如果是null值，就是pfile启动的
11.请写出event 10053的作用是什么
借助Oracle的10053事件event，我们可以监控到CBO对SQL进行成本计算和路径选择的过程和方法。
10053事件有两个级别：
Level 2：2级是1级的一个子集，它包含以下内容：
Column statistics
Single Access Paths
Join Costs
Table Joins Considered
Join Methods Considered (NL/MS/HA)
Level 1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：
Parameters used by the optimizer
Index statistics
启用10053事件
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 2';
关闭10053事件：
ALTER SESSION SET EVENTS '10053 trace name context off';
说明：
1、sqlplus中打开autotrace看到的执行计划实际上是用explain plan 命令得到的，explain plan 命令不会进行bind peeking。
应该通过v$sql_plan查看SQL的真实的执行计划。
2、10053只对CBO有效，而且如果一个sql语句已经解析过，就不会产生新的trace信息。
3、10053事件产生的trace文件不能用tkprof格式化。
12.请写出hash join 与nest loop的实现原理
nest loop:多用于联机业务 OLTP (exists)
嵌套循环连接的工作方式是这样的：
1、 Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（Outer Table）。由驱动表进行驱动连接的表或数据源称为内部表（Inner Table）。
2、 提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。在这个过程中，Oracle首先提取驱动表中符合条件的第一条记录，再与内部表的连接列进行关联查询相应的记录行。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。在这里需要说明的是，由于Oracle最小的IO单位为单个数据块，因此在这个过程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有行，再与内部表进行关联连接查询的，然后提取下一个数据块中的记录持续地循环连接下去。当然，如果单行记录跨越多个数据块的话，就是一次单条记录进行关联查询的。
Hash join:多用于批处理业务 (in)
一般用于一张小表和一张大表进行join时。Hash join的过程大致如下（下面所说的内存就指sort area，关于过程，后面会作详细讨论）：
1.一张小表被hash在内存中。因为数据量小，所以这张小表的大多数数据已经驻入在内存中，剩下的少量数据被放置在临时表空间中；
2.每读取大表的一条记录，就和小表中内存中的数据进行比较，如果符合，则立即输出数据（也就是说没有读取临时表空间中的小表的数据）。而如果大表的数据与小表中临时表空间的数据相符合，则不直接输出，而是也被存储临时表空间中。
3.当大表的所有数据都读取完毕，将临时表空间中的数据以其输出。
如果小表的数据量足够小（小于hash area size），那所有数据就都在内存中了，可以避免对临时表空间的读写。
如果是并行环境下，前面中的第2步就变成如下了：
2.每读取一条大表的记录，和内存中小表的数据比较，如果符合先做join，而不直接输出，直到整张大表数据读取完毕。如果内存足够，Join好的数据就保存在内存中。否则，就保存在临时表空间中。
NESTED LOOP
对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是 nested loops。
一般在nested loop中， 驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样就走nestedloop。如果驱动表返回记录太多，就不适合nested loops了。如果连接字段没有索引，则适合走hash join，因为不需要索引。
可用ordered提示来改变CBO默认的驱动表，可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。
HASH JOIN
hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。
当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。
至于两个表都进行分区，其好处是可以使用parallel query，就是多个进程同时对不同的分区进行join，然后再合并。但是复杂。
使用hash join时，HASH_AREA_SIZE初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET即可。
以下条件下hash join可能有优势：
两个巨大的表之间的连接。
在一个巨大的表和一个小表之间的连接。
可用ordered提示来改变CBO默认的驱动表，可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。
SORT MERGE JOIN
sort merge join的操作通常分三步：对连接的每个表做table access full；对table access full的结果进行排序；进行merge join对排序结果进行合并。sort merge join性能开销几乎都在前两步。一般是在没有索引的情况下，9i开始已经很少出现了，因为其排序成本高，大多为hash join替代了。
通常情况下hash join的效果都比sort merge join要好，然而如果行源已经被排过序，在执行sort merge join时不需要再排序了，这时sort merge join的性能会优于hash join。
在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下，sort merge join会比nested loops性能更佳。
可用USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。
13.请描述行连接与行迁移的原理
有两种情况会导致表中某行数据过大，一个数据块（data block）无法容纳。
行链接：当一行数据被插入时一个数据块就无法容纳。在这种情况下Oracle将这行数据存储在段内的一个数据块链（chain）中。在插入数据量大的行时常会发生行链接（row chaining），例如一个包含数据类型为 LONG 或 LONG RAW 列的数据行。此时行链接不可避免。
行迁移：原本存储在一个数据块（data block）内的数据行，因为更新操作导致长度增长，而所在数据块的可用空间也不能容纳增长后的数据行。在这种情况下，Oracle将此行数据迁移（migrate）到新的数据块中。Oracle在被迁移数据行原来所在位置保存一个指向新数据块的指针。被迁移数据行的 rowid 保持不变。
当数据行发生链接（chain）或迁移（migrate）时，对其访问将会造成 I/O 性能降低，因为Oracle为获取这些数据行的数据时，必须访问更多的数据块（data block）。
14.请写出pctfree及pctused的作用
PCTFREE 参数用来设置一个数据块（data block）中至少需要保留（reserve）多少可用空间（百分比值），为数据块中已有数据更新时可能发生的数据量增长做准备。例如，当用户用 CREATE TABLE 语句创建表时指定了以下参数：PCTFREE 20
这个参数设定了此表对应的数据段（data segment）中的每个数据块（data block）至少保留20%的可用空间，以备块中已有数据更新时使用。只要数据块中行数据区与数据块头的容量之和不超过数据块总容量的80%，用户就可以向其中插入新数据，数据行被放入行数据区（row data area），相关信息被写入数据块头（overhead area）。
PCTUSED 参数用于决定一个数据块（data block）是否可被用于插入新数据，她的依据是数据区（row data）与数据块头（overhead）的容量之和占数据块全部容量的最大百分比。当一个数据块中的可用空间比例小于 PCTFREE 参数的规定时，Oracle就认为此数据块无法被用于插入新数据，直到数据块中的占用容量比例小于 PCTUSED 参数的限定。在占用容量比例大于 PCTUSED 参数的限定之前，Oracle只在更新数据块内已有数据时才会使用此数据块的可用空间。例如，当用户用 CREATE TABLE 语句创建表时指定了以下参数：PCTUSED 40
在例子中，当此表的某数据块占用容量比例高于40%时，Oracle不会将此数据块用于插入新数据行（假设此数据块的可用空间曾经低于 PCTFREE 的限定）。
15.请写出分区表的作用
分区表的作用：提高查询速度（查询数据的时候不会再对表进行全部扫描，而扫描相应的分区表)，在表进行分区之后，逻辑上还是一张表，实际上已经把插入的数据分到了各个分区表中，数据物理的分到不同的分区表中。当一个分区损坏后，不影响其他分区中的数据。
分区表的类型：1)按范围进行分区2)列表分区3）散列分区4)范围列表组合5）范围散弄组合。
分区技术能够带来以下好处：
1)利用分区技术，用户可以在分区级（partition level）进行数据加载（data load），索引创建及重建，或备份恢复等数据管理操作，而非针对整个表执行。这大大减少了此类操作所需时间。
2）分区技术能够提高查询性能。在很多情况下，查询的结果集可能来自几个分区，而非整个表。 对于某些查询，这种技术（称为分区剪除（partition pruning））能够带来几个数据量级的性能提升。
3）分区技术能够显著缩短维护操作导致的停机时间。
4）由于对各分区的维护操作可以相互独立地进行，用户可以同时对表或索引的不同分区进行维护操作。用户还能在维护的同时对未受维护操作影响的分区执行 SELECT 及 DML 操作。
5）利用分区技术存储数据库中的关键表及索引，能够缩短此类对象的维护窗口（maintenance window），及恢复时间，并减少此类对象发生故障时对系统的影响，从而提高数据库的可用性。
6)采用分区技术时，用户无需对原有应用程序进行任何修改。例如，当用户将一个非分区表转化为分区表后，无需修改访问此表的 SELECT 语句及 DML 语句。用户无需重写应用程序代码就可以发挥分区技术的优势。
16.请写出参数session_cached_cursors 的作用
open_cursors:该参数含义是同一个session同时打开最多在使用的游标数。在Oracle10.2.0.1.0版本中默认为300。
session_cached_cursors：SESSION_CACHED_CURSORS, 就是说的是一个session可以缓存多少个cursor，让后续相同的SQL语句不再打开游标,从而避免软解析的过程来提高性能。(绑定变量是解决硬解析的问题)，软解析同硬解析一样,比较消耗资源.所以这个参数非常重要。在Oracle10.2.0.1.0版本中默认为20。
show parameter open_cursors ----每个session（会话）最多能同时打开多少个cursor（游标）
show parameter session_cached_cursors ----每个session（会话）最多可以缓存多少个关闭掉的cursor
select count(*) from v$open_cursor; ----是指当前实例的某个时刻的打开的cursor数目 COUNT(*)
a、两个参数之间没有任何关系，相互也不会有任何影响。
b、两个参数有着相同的作用：让后续相同的sql语句不在打开游标，从而避免软解析过程来提供应用程序的效率。
cursor的作用 open ->close. 频繁的开关会产生开销。SESSION_CACHED_CURSORS适当的增加提高了缓存open cursor的数量,减少了频繁开关的开销。
使用下面的sql判断session_cached_cursors的使用情况。如果使用率为100%则增大这个参数值。
Select 'session_cached_cursors' Parameter,
Lpad(Value, 5) Value,
Decode(Value, 0, ' n/a', To_Char(100 * Used / Value, '990') || '%') Usage
From (Select Max(s.Value) Used
From V$statname n, V$sesstat s
Where n.Name = 'session cursor cache count'
And s.Statistic# = n.Statistic#),
(Select Value From V$parameter Where Name = 'session_cached_cursors')
Union All
Select 'open_cursors',
Lpad(Value, 5),
To_Char(100 * Used / Value, '990') || '%'
From (Select Max(Sum(s.Value)) Used
From V$statname n, V$sesstat s
Where n.Name In
('opened cursors current', 'session cursor cache count')
And s.Statistic# = n.Statistic#
Group By s.Sid),
(Select Value From V$parameter Where Name = 'open_cursors');
17.请写出临时表空间的作用
1、临时表空间的作用：
临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。
当临时表空间不足时，表现为运算速度异常的慢，并且临时表空间迅速增长到最大空间（扩展的极限），并且一般不会自动清理了。
2、迅速增长的原因：
一般原因是sql算法不正确，可能导致出现了迪卡尔积。
另外一个原因是视图的创建问题（本人遇到的正是该问题！）。Oracle数据库中多张表联合查询时，极限的表单数量为4张，一般将以为3张！有时需要大于4张表联合查询时，怎么办呢？可以建立子视图：
如view0(联合table0,table1,table2),view1(联合table0,table3,table4),view2(联合table0,table5,table6)
然后将view0、view1、view2联合成view。
这样可以解决临时表空间迅速增长的问题！
临时表空间使用注意:
1.临时表空间 是用于在进行排序操作(如大型查询,创建索引和联合查询期间存储临时数据)
每个用户都有一个临时表空间
2.对于大型操作频繁,(大型查询,大型分类查询,大型统计分析等),应指定单独的临时表空间,以方便管理
3.分配用户单独临时表空间,一般是针对 大型产品数据库,OLTP数据库,数据库仓库
对于小型产品不需要单独制定临时表空间,使用默认临时表空间
当然解决方法也很简单:1、设置临时数据文件自动扩展，或者2、增大临时表空间。
扩展临时表空间：
方法一、增大临时文件大小：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m;
Database altered.
方法二、将临时数据文件设为自动扩展：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;
Database altered.
方法三、向临时表空间中添加数据文件：
SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;
18.请写出全局索引及本地索引的区别
局部索引local index
1. 局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区数，一句话，局部索引的分区机制和表的分区机制一样。
2. 如果局部索引的索引列以分区键开头，则称为前缀局部索引。
3. 如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。
4. 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。
5. 局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。
6. 局部分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区， 对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。
7. 位图索引只能为局部分区索引。
8. 局部索引多应用于数据仓库环境中。
全局索引global index
1. 全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。
2. 全局索引可以分区，也可以是不分区索引，全局有分区索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。
3. 全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只动，截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。
4. 全局索引多应用于oltp系统中。
5. 全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。
6. oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。
7. 表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。
19.请写出如何定位消耗资源多的SQL
statspack-- 在你库上业务最忙得时候抓15分钟的report,看里面的top sql///////awr/ash
1 top找到消耗资源多的pid
2 确定是oracle的应用进程还是后台进程
3 根据v$session,v$process,v$sqlarea定位
用oem带的top session
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC
1. 值得怀疑的SQL
select substr(to_char(s.pct, '99.00'), 2) || '%' load, s.executions executes, p.sql_text from (select address,disk_reads,executions,pct,rank() over(order by disk_reads desc) ranking from (select address,disk_reads,executions,100 * ratio_to_report(disk_reads) over() pct from sys.v_$sql where command_type != 47) where disk_reads > 50 * executions) s, sys.v_$sqltext p where s.ranking <= 5 and p.address = s.address order by 1, s.address, p.piece;
2、查看消耗内存多的sql
select b.username,a. buffer_gets,a.executions,a.disk_reads / decode(a.executions, 0, 1, a.executions),a.sql_text SQL from v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and a.disk_reads > 10000 order by disk_reads desc;
3.逻辑读多的SQL
select * from (select buffer_gets, sql_text from v$sqlarea where buffer_gets > 500000 order by buffer_gets desc) where rownum <= 30;
4.执行次数多的SQL
select sql_text, executions from (select sql_text, executions from v$sqlarea order by executions desc) where rownum < 81;
5. 读硬盘多的SQL
select sql_text, disk_reads from (select sql_text, disk_reads from v$sqlarea order by disk_reads desc) where rownum < 21;
6. 排序多的SQL
select sql_text, sorts from (select sql_text, sorts from v$sqlarea order by sorts desc) where rownum < 21;
7.分析的次数太多，执行的次数太少，要用绑变量的方法来写sql
set pagesize 600;
set linesize 120;
select substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs" from v$sqlarea where executions < 5 group by substr(sql_text, 1, 80) having count(*) > 30 order by 2;
8.游标的观察
set pages 300;
select sum(a.value), b.name from v$sesstat a, v$statname b where a.statistic# = b.statistic# and b.name = 'opened cursors current'group by b.name;
select count(0) from v$open_cursor;
select user_name, sql_text, count(0) from v$open_cursor group by user_name, sql_text having count(0) > 30;
9.查看当前用户&username执行的SQL
select sql_text from v$sqltext_with_newlines where (hash_value, address) in (select sql_hash_value, sql_address from v$session where username = '&username') order by address, piece;
20.请写出oracle主要后台进程的作用
Oracle中的进程共分为三类：用户进程、服务进程、后台进程。其中后台进程伴随实例的启动而启动，他们主要是维护数据库的稳定，相当于一个企业中的管理者及内部服务人员。他们并不会直接给用户提过服务。
一：database write：DBWn数据写入
作用：把SGA中被修改的数据同步到磁盘文件中。保证Buffer Cache中有足够的空闲数据块数量。
触发条件：1，检查点
2，一个服务进程在设定的时间内没有找到空闲块。
3，每3秒自动唤醒一次。
设置：DB_WRITER_PROCESSES用来定义DBWn进程数量。（commit命令只是把记录修改写入日志文件，不是把修改后的数据写入数据文件，下面还会提到。）
二：log write LGWR：日志文件写入
作用：把log buffer中的日志内容写入联机的日志文件中，释放log用户buffer空间呢。
触发条件：1，用户发出commit指令。（在oracle中称为快速提交机制（fast commit）：把redo log buffer中的记录写入日志文件，写入一条已提交的记录）。
2，三秒钟定时唤醒。
3，log buffer超过1/3，或日志数量超过1M。
4，DBWR进程触发：DBWn试图将脏数据块写入磁盘先检查他的相关redo记录是否写入联机日志文件，如果没有就通知LGWR进程。
在oracle中称为提前写机制（write ahead）：redo记录先于数据记录被写入磁盘。
三：checkpoint CKPT：检查点事件
作用：维护数据库一致性状态。检查点时刻数据文件与SGA中的内容一致。这不是一个单独的进程，要和前两个进程一起工作。DBWR写入脏数据，同时触发LGWR进程。
CKPT更新控制文件中的检查点记录。
触发条件：日志切换（log switch）会触发检查点。
四：process monitor PMON ：维护用户进程
作用：1,发现用户进程异常终止，并进行清理。释放占用资源。（清理异常终止用户使用的锁）。
2，向监听程序动态的注册实例。
触发条件：定时被唤醒，其他进程也会主动唤醒他。
五：system monitor SMON：实例维护
作用：1，负责实例恢复，前滚（Roll Forward）恢复到实例关闭时刻的状态，使用最后一次检查点后的日志进行重做。这时包括提交和未提交的事务。打开数据库，进行回滚（Roll Back）：回滚未提交的事务。
（Oracle承诺commit之后的数据不会丢失，现在我们可以大致的了解Oracle是如何实现这个承诺的，以及在数据的安全性和数据库性能之间的平衡选择。）
2，负责清理临时段，以释放空间；
触发条件：定期被唤醒或者被其他事务主动唤醒。
六：archive ARCn：归档操作
作用：发生日志切换时把写满的联机日志文件拷贝到归档目录中。
触发条件：日志切换时被LGWR唤醒。
设置：LOG_ARCHIVE_MAX_PROCESSES可以设置oracle启动的时候ARCn的个数。
七：manageability monitor MMON：AWR主要的进程。
作用：1收集AWR必须的统计数据，把统计数据写入磁盘。10g中保存在SYSAUX表空间中。
2，生成server-generated报警。
每一个小时把shard pool中的统计信息写入磁盘，或shard pool占用超过5%。
八：manageability monitor light MMNL：轻量级的MMON；
九：memory manager MMAN：自动内存管理
作用：每分钟都检查AWR性能信息，并根据这些信息来决定SGA组件最佳分布。
设置：STATISTICS_LEVEL：统计级别
SGA_TARGET：SGA总大小
十：job queue coordinator CJQO：数据库定时任务
十一：recover writer RVWR：为flashback database提供日志记录。把数据块的前镜像写入日志。
十二：change tracking writer CTWR：跟踪数据块的变化，把数据块地址记录到change_tracking file文件中。RMAN的增量备份将使用这个文件来确定那些数据块发生了变化，并进行备份。
以上是oracle中比较常见的后台进程，不是所有的进程。其中DBWn，ARCn可以设置数据库启动时的数量。这些进程在系统中可以不止一个。
Oracle实战类：
1.表tab1,由5列组成，存在1000万条记录，现查询该表非常慢，请问如何解决该问题
首先先用查询条件(order by等)查询一下 如果还慢，查看是否有索引，没有建立索引，有索引重新建立索引。
索引结构
如何再不行，重新将表分区。
2.数据库突然变得非常缓慢，请写出诊断思路
看是否有哪些sql语句发生死锁，堵塞
看看alert文件有什么错误或警告没有,此外在慢的时候查查v$session_wait有什么等待事件
看回滚空间占用率是多少，temp空间占用率是多少。
如果回滚空间占用率太多，就看是什么程序在其中占用了大量的回滚空间，而且没有自我释放，一直在运行，从中，可以判断出是什么问题。
然后在杀数据库的进程，将死循环或者其他死进程给杀了。
还有可能是临时表空间满了
awr
3.数据库alert日志中报错ORA-01555，请写出诊断思路
Linux下执行：oerr ora 1555查看错误解释
先看报错，是否因为undo表空间太小，如果是，扩大undo表空间；如果不是，增加参数undo_retention；
undo基本信息:
select inst_id,name,value from gv$parameter where upper(name) like '%UNDO%';
select distinct status,count(*) extent_num , sum(bytes/1024/1024) undosize from dba_undo_extents group by status;
4.数据库在备份时报错块损坏，请写出处理思路
如果是物理损坏，换盘，如果是逻辑损坏，先定位到块损坏的位置。////如何定位数据块的位置。
停止数据库备份：用bbed来修复坏块
recover datafile'/u01/oracle/oracle10g/oradata/TEST/test.dbf';恢复数据文件。
5.数据库突然异常关闭，请写出处理思路
先重新启动如果不行查看alert
总结：
当发生1102错误时，可以按照以下流程检查、排错：
如果是HA系统，检查其他节点是否已经启动实例；
检查Oracle进程是否存在，如果存在则杀掉进程；
检查信号量是否存在，如果存在，则清除信号量；
检查共享内存段是否存在，如果存在，则清除共享内存段；
检查锁内存文件lk和sgadef.dbf是否存在，如果存在，则删除。
服务器异常断电，导致oracle 数据库无法打开：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 1 sequence# 8 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 1 thread 1: '/oracle/oradate/orcl/redo1.log'
处理过程是：
SQL> select group#, sequence# from v$log;
GROUP# SEQUENCE#
------- ----------
1 10
2 11
3 12
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
当时没能创建归档日志的原因是对归档文件夹的操作权限不够。赋予足够权限以后
SQL> alter system archive log start;
System altered.
某系统突然掉电，系统启动后发现Oracle无法启动。启动时报如下错误：
ORA-01102 cannot mount database in EXCLUSIVE mode
出现1102错误可能有以下几种可能：
一、在HA系统中，已经有其他节点启动了实例，将双机共享的资源（如磁盘阵列上的裸设备）占用了；
二、说明Oracle被异常关闭时，有资源没有被释放，一般有以下几种可能，
1、 Oracle的共享内存段或信号量没有被释放；
2、 Oracle的后台进程（如SMON、PMON、DBWn等）没有被关闭；
3、 用于锁内存的文件lk和sgadef.dbf文件没有被删除。
首先，虽然我们的系统是HA系统，但是备节点的实例始终处在关闭状态，这点通过在备节点上查数据库状态可以证实。
其次、是因系统掉电引起数据库宕机的，系统在接电后被重启，因此我们排除了第二种可能种的1、2点。最可疑的就是第3点了。
查$ORACLE_HOME/dbs目录：
$ cd $ORACLE_HOME/dbs$ ls sgadef*sgadef* not found$ ls lk*lkORA92
果然，lk文件没有被删除。将它删除掉
$ rm lk*
再启动数据库，成功。
如果怀疑是共享内存没有被释放，可以用以下命令查看：
$ipcs -mopIPC status from /dev/kmem
as of Thu Jul 6 14:41:43 2006T ID KEY MODE OWNER GROUP NATTCH CPID LPIDShared Memory:m 0 0x411c29d6
--rw-rw-rw- root root 0 899 899m 1 0x4e0c0002
--rw-rw-rw- root root 2 899 901m 2 0x4120007a
--rw-rw-rw- root root 2 899 901m 458755 0x0c6629c9
--rw-r----- root sys 2 9113 17065m 4 0x06347849
--rw-rw-rw- root root 1 1661 9150m 65541 0xffffffff
--rw-r--r-- root root 0 1659 1659m 524294 0x5e100011
--rw------- root root 1 1811 1811m 851975 0x5fe48aa4
--rw-r----- oracle oinstall 66 2017 25076
然后它ID号清除共享内存段：
$ipcrm –m 851975
对于信号量，可以用以下命令查看：
$ ipcs -sopIPC status from /dev/kmem
as of Thu Jul 6 14:44:16 2006T ID KEY MODE OWNER GROUPSemaphores:s 0 0x4f1c0139
--ra------- root root... ...s 14 0x6c200ad8
--ra-ra-ra- root roots 15 0x6d200ad8
--ra-ra-ra- root roots 16 0x6f200ad8
--ra-ra-ra- root roots 17 0xffffffff
--ra-r--r-- root roots 18 0x410c05c7
--ra-ra-ra- root roots 19 0x00446f6e
--ra-r--r-- root roots 20 0x00446f6d
--ra-r--r-- root roots 21 0x00000001
--ra-ra-ra- root roots 45078 0x67e72b58
--ra-r----- oracle oinstall
根据信号量ID，用以下命令清除信号量：
$ipcrm -s 45078
如果是Oracle进程没有关闭，用以下命令查出存在的oracle进程：
$ ps -ef|grep ora oracle 29976 1 0 Jun 22 ? 0:52 ora_dbw0_ora92 oracle 29978 1 0 Jun 22 ? 0:51 ora_dbw1_ora92 oracle 5128 1 0 Jul 5 ? 0:00 oracleora92 (LOCAL=NO)... ...
然后用kill -9命令杀掉进程
$kill -9
1.请写出如何添加数据文件的语句
Alter tablespace data add datafile ‘/oracle/oradata/CFGDB/data01.dbf’ size 100m
2.请写出如何启动归档模式
Shutdown immediate startup mount alter database archivelog alter database open
3.请写出如何查看当前被锁的表
Select t1.object_name from dba_objects t1,v$session t2,v$locked_object t3 where t1.object_id=t3.object_id and t2.sid=t3.session_id;
4.请写出如何查看某张表的索引
Select * from dba_indexes where table_name=’’;
5.请写出如何查看某张表的大小
Select * from dba_segments where segment_name=’’ and segment_type=’TABLE’;
6.请写出如何查看某用户使用的默认表空间
Select username,default_tablespace,temporary_tablespace from dba_users where username=’’;
7.请写出如何查看各个表空间的大小
select tablespace_name,bytes/1024/1024 from dba_data_files;
8.请写出如何查看当前数据库存在的等待事件
select * from v$session_wait; select * from v$waitstat; desc v$waitstat
9.请写出如何生成AWR报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
10.请写出切换日志的语句
alter system switch logfile;
11.请写出如何通过sqlid获得该SQL的执行计划
explain plan for select * from ...
select * from table(DBMS_XPLAN.DISPLAY_AWR(SQL_ID));10G以后。
SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR ('danxh10kwn2s3',NULL,NULL，‘BASIC’));可以把BASIC 换成 ADVANCE 和TYPICAL试试。
12.请写出如何锁住某个用户
alter user SN account lock;
alter user SN account unlock;
13.请写出如何查询某表的统计信息
dbms_stats.gather_table_stats(user,'table',cascade=>true);
DBMS_STATS包中用于收集统计信息的过程包括：
dbms_stats.gather_table_stats 收集表、列和索引的统计信息；
dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息；
dbms_stats.gather_index_stats 收集索引的统计信息；
dbms_stats.gather_system_stats 收集系统统计信息。
dbms_stats.delete_table_stats 删除表的统计信息
dbms_stats.export_table_stats 输出表的统计信息
dbms_stats.create_state_table
dbms_stats.set_table_stats 设置 表的统计
dbms_stats.auto_sample_size
dbms_stats.gather_database_stats： 收集数据库中所有对象的统计信息；
14.请写出如何跟踪某session执行的sql
exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);
select sid,serial# from v$session where sid = (select sid from v$mystat where rownum = 1);
exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,'');
15.请写出如何查看当前数据库在运行的事务
select * from v$transaction;
16.请写出设置sga_target为10G的语句
alter system set sga_target=10240m
17.请写出如何根据sqlid获得该SQL是否使用绑定变量
select sql_id,sql_text,executions from v$sqlarea; 看executions的数量特别大说明没有绑定变量
减产前40个字符相同, 有多于5个不同sql版本的sql语句
select substr(sqltext,1,40) sql_text,count(*) from v$sql group by substr(sqltext,1,40) having count(*) >= 5;
18.请写出如何获得某latch的sleep数量
select name,gets,misses,immediate_gets,immediate_misses,sleeps from v$latch;
19.请写出如何根据操作系统进程号获得oracle 会话号
select s.sid from v$session s,v$process p where p.addr=s.PADDR and p.spid='25783'
select sid from v$session where process='25817';
SQL语句杀掉长期没有释放非正常的锁：
alter system kill session 'sid,serial#';
20.请写出如何查询当前数据库日志的切换时间
select archived,status,first_time from v$log;
Oracle概念类
1.请简述oracle SGA中各内存区域的功能
buffer cache,log buffer,shared pool,big pool,java pool,streams pool
数据高速缓冲区（buffer cache）：
用来保存用户最近从磁盘数据文件访问的数据块的副本，这样当再由用户访问相同的数据时，则可以直接从数据高速缓冲区读取，不用再访问磁盘（注：磁盘I/O的速率是毫米级的，而内存I/O的速率为纳秒级）
包括三个类型区
脏的区（dirty buffer）：用来保存从磁盘数据文件读取来的、已经修改过、并且还没有写入磁盘数据文件的数据。
自由区（free buffer）：没有任何数据，并且可以写入数据的空闲内存区；
保留区（pinned buffer）：用来保存正在被使用，或者明确留作将来使用的数据，主要指，从磁盘数据文件内读取到、等待服务器进程更改的数据，或者正在被更改的数据。
数据高速缓冲区可配置一个或2个或3个缓冲区池，我们在create或alter表或索引时，可以指定该表或索引属于哪个缓冲区池；
保持缓冲区池（db_keep_cache_size）：用来保存一些需要频繁访问的小表，防止这些小表的数据被LRU命中；
回收缓冲区池（db_recycle_cache_size）：用来保存一些很少使用的大表，在缓区处理完之后，立即清除，节省缓存空间；
默认缓冲区池（db_cache_size）：用来保存除db_keep_cache_size和db_recycle_cache_size之外的所有数据；
子高速缓存(db_nk_cache_size)：主要针对不同的oracle块尺寸来设置的，
数据高速缓冲区尺寸=db_keep_cache_size+db_recycle_cache_size+db_cache_size+db_nk_cache_size；
重做日志缓存（log buffer):
用来保存数据库内的变更，由LGWR立即写入联机重做日志文件
共享池（shared_pool_size）：
包括库高速缓存和数据字典高速缓冲两部分
库高速缓存：主要用来保存分析过的sql代码，所谓的“分析”是指，校验用户请求sql代码的语法，并且判断sql代码涉及的表和字段在数据库内是否存在；
调优手段：通过动态视图v$librarycache，判断公式：sum(reloads)/sum(pins)，如果结果约等于0，则证明库高速缓存命中率是合适的；如果结果>1，则需要增大共享池设置(shared_pool_size)；
数据字典高速缓存：主要用来保存服务器最近从磁盘数据字典内访问过的数据，主要包括表结构、用户、实体等信息，如果再有用户请求时，则服务器进程会首先从数据字典高速缓存内检索，在缓冲区内是否存在该请求用户的相关权限信息，如果没有的话，需要访问磁盘数据字典（注：访问磁盘数据字典是最消耗资源的，相对于访问其他磁盘文件来说）
调优手段：通过动态视图v$rowcache，判断公式：(1-sum(getmisses)/(sum(getmiss)+sum(getmisses)))*100，如果结果>90，证明命中率是合适的，否则需要增大共享池设置；（注：oracle没有直接设置库高速缓冲和数据字典高速缓冲的地方，需要通过调整共享池尺寸实现）
大型池(large_pool_size):
主要为执行一些非常复杂并且耗时很长的sql代码提供空间，例如在执行备份操作时，需要存放备份缓冲区；
Java池(java_pool_size):
主要为实例化的Java对象提供的堆空间，如果数据库中存在大量的java代码，就可以使用该设置，不用占用常规的sga；
流池(streams_pool_size):
oracle支持流传输，主要用来实现与不同类型数据库，或者在不同应用环境下的数据共享；
其他结构（包括数据控制和锁的管理）
pga（程序全局区）：每个用户会话都会在pga上占用一段独占的内存空间，pga上从属于该用户会话的内存空间，只允许该用户使用，其他用户不能访问。pga通过pga_size_mode可以设置自动或是手动内存配置。
2.如何查看执行计划，查看执行计划的作用是什么
一、explain plan for select * from ......
select * from table(DBMS_XPLAN.Display)
二、set autotrace on;
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询
三、SQL_TRACE
1.alter session set sql_trace=true;
2.执行sql
3.alter session set sql_trace=false;
4.查看相应的sql trace文件。
作用：
语句所引用表的顺序；
语句所涉及的表的访问方法；
语句中连接操作所影响到的各表的连接方法。
----各种表访问方式需要明白
3.请写出控制文件的作用
作用：
二进制文件
记录了数据库当前实例的结构和行为，数据文件日志文件的信息，维护数据库一致性
参数文件中定义了控制文件的位置和大小
很小的二进制文件，一般不超过100m
mount阶段open以后，一直在用
一套控制文件只能连接一个database
分散放置，至少一份，至多八份
control files作用:
参数文件init.ora 记录了控制文件的位置，控制文件是一个非常小的二进制文件，最大可以增长到64MB，控制文件包括如下主要信息：
·数据库的名字，检查点信息，数据库创建的时间戳
·所有的数据文件，联机日志文件，归档日志文件信息
·备份信息等
有了这些信息，Oracle 就知道那些文件是数据文件，现在的重做日志文件是哪些，这些都是系统启动和运行的基本条件，所以他是Oracle 运行的根本。如果没有控制文件系统是不可能启动的。控制文件是非常重要的，一般采用多个镜相复制来保护控制文件，或采用RAID 来保护控制文件。控制文件的丢失，将使数据库的恢复变的很复杂。控制文件信息可以从V$Controlfile 中查询获得
一.控制文件的作用
控制文件记录了物理数据库的当前状态。一般为3个，三个控制文件都是一摸一样的，最好是分开放在不同的磁盘上。
二.控制文件的内容
2.1数据库的名字；
数据库的标示符：创建数据库是oracle自动生成的；
数据库创建的时间戳：它是在数据库创建时生成的；
联机重做日志文件的名字和准确位置；
当前日志的学历号：它是在日志切换时oracle记录的；
日志的历史信息：它是在日志切换时oracle记录的；
归档日志文件的准确位置和状态；
数据文件的名字和准确位置；
表空间的信息；
备份的准确位置和状态；
三.添加和移动控制文件
5.1在一个已经安装的的oracle数据库中使用正文初始化参数文件（pfile）添加或者移动控制文件的具体步骤。
a).利用数据字典v$controlfile来获取现有的控制文件名字；
b).正常关闭数据库，用命令shutdown或者shutdown immediate;
c).将新的控制文件名添加到参数文件的control_files参数中；//区别
d).使用操作系统命令将现有的控制文件复制到指定位置；
e).重新启动数据库，用命令startup;
f).利用数据字典v$controlfile来验证新的控制文件名字是否正确；
g).如果有误，重复上述操作；如果无误，就删除无用的旧的控制文件；
5.2如果使用了服务器初始化参数（spfile），步骤会有些区别。下面是具体步骤；
a).利用数据字典v$controlfile来获取现有的控制文件名字；
b).修改spfile,使用alter system set control_files命令来改变控制文件的位置；//区别
c).正常关闭数据库，用命令shutdown或者shutdown immediate;
d).使用操作系统命令将现有的控制文件复制到指定位置；
e).重新启动数据库，用命令startup;
f).利用数据字典v$controlfile来验证新的控制文件名字是否正确；
g).如果有误，重复上述操作；如果无误，就删除无用的旧的控制文件；
相关视图：
v$controlfile ---- 列出实例的所有控制文件的名字和状态
v$parameter ---- 列出所有参数的位置和状态（where name='control_files'）
v$controlfile_recode_section ---- 提供控制文件的记录部分的信息
show parameter control_files ---- 列出控制文件的名字、状态和位置
查看控制文件内容：
strings $ORACLE_BASE/oradata/$ORACLE_SID/control01.ctl
alter database backup controlfile to trace as '
转储控制文件：
alter system set events 'immediate trace name controlf level 10'
level 1 块头
level 2 文件内容
level 3 1+2
level 10 全部
管理控制文件：
spfile 修改spfile参数control_files（alter……）
一致性关闭数据库
增加或减少控制文件
启动数据库使用spfile
验证结果
pfile 一致性关闭数据库
修改pfile参数
增加或减少控制文件
启动数据库使用spfile
验证结果
状态查询：参数和具体文件是否对应、磁盘空间剩余、控制文件<100M
热备控制文件：
数据库归档模式下，alter database backup controlfile to '
其他模式下：alter database backup controlfile to trace as '
数据库归档模式下rman备份：
rman target /
>backup current controlfile; or
>backup database include current controlfile;
rman自动备份控制文件：
>show all中的configure controlfile autobackup设置为on
>configure controlfile autobackup on
问题解决：
控制文件不一致：
1. 拷贝新版本的控制文件到旧版本文件
2. 修改参数文件，只使用新版本的文件（alter system set……），重启
控制文件丢失：
【mount阶段，使用archive log list可以查看数据库是否在归档模式下】
1. 控制文件无备份
a.数据库非归档，数据文件联机日志文件存在 --- 重建控制文件
>create controlfile reuse database
2. 控制文件有备份
a.数据库归档，数据文件联机和归档日志文件存在 --- 还原控制文件
先全备，再将控制文件拷贝到参数指定的路径，然后>alter database recover database using backup controlfile until change;
4.请写出SCN的作用
SCN是当Oracle数据库更新后，由DBMS自动维护去累积递增的一个数字。Oracle数据库中一共有4种SCN分别为
系统检查点SCN： 系统检查点SCN位于控制文件中，当检查点进程启动时(ckpt)，Oracle就把系统检查点的SCN存储到控制文件中。该SCN是全局范围的，当发生文件级别的SCN时，例如将表空间置于只读状态，则不会更新系统检查点SCN。
数据文件检查点scn：当ckpt进程启动时，包括全局范围的（比如日志切换）以及文件级别的检查点（将表空间置为只读、begin backup或将某个数据文件设置为offline等），这时会在控制文件中记录的scn。
结束scn：每个数据文件都有一个结束scn，在数据库的正常运行中，只要数据文件在线且是可读写的，结束scn为null。否则则存在具体的scn值。结束scn也记录在控制文件中。
开始scn：不同于上述的SCN数据文件开始scn记录在每个数据文件中。当发生系统及文件级别的检查点后，不仅将这时的SCN号记录在控制文件中，同样也记录在数据文件中。
SCN的机制 数据库运行时的SCN
Oracle事务
我们先看下oracle事务中的数据变化是如何写入数据文件的：
一、事务开始；
二、在buffer cache中找到需要的数据块，如果没有找到，则从数据文件中载入buffer cache中；
三、事务修改buffer cache的数据块，该数据被标识为“脏数据”，并被写入log buffer中； ----这个“脏数据”是什么进程写入到log buffer中的
四、事务提交，LGWR进程将log buffer中的“脏数据”写入redo log file中；
五、当发生checkpoint，CKPT进程更新所有数据文件的文件头中的信息，DBWr进程则负责将Buffer Cache中的脏数据写入到数据文件中。
5.请写出log file sync等待事件的产生原因
①．高提交频率
解决方法是简单的消除不必要的提交，事务是工作单元。工作单元应该是全部成功或全部失败。
②．缓慢的I/O子系统
较高的IO吞吐量可以改善log file sync和log file parallel write事件的平均等待时间。频繁的提交会弄乱数据库布局和IO子系统。
解决办法是将日志文件放裸设备上或绑定在RAID 0或RAID 0+1中，而不是绑定在RAID 5中。
③．过大的日志缓冲区
过大的日志缓冲区也可能延长log file sync等待。大型的日志缓冲区减少后台写入的数量，允许LGWR变得懒惰，并导致更多的重做条目堆积在日志缓冲区中。同时可以调整参数_LOG_IO_SIZE参数，其默认值是LOG_BUFFER的1/3或1MB，取两者之中较小的值。换句话说，你可以具有较大的日志缓冲区，但较小的_LOG_IO_SIZE将增加后台写入，从而减少log file sync的等待时间。
Statspack之十四-"log file sync" 等待事件：
当一个用户提交(commits)或者回滚(rollback),session的redo信息需要写出到redo logfile中.
用户进程将通知LGWR执行写出操作,LGWR完成任务以后会通知用户进程.
这个等待事件就是指用户进程等待LGWR的写完成通知.
对于回滚操作，该事件记录从用户发出rollback命令到回滚完成的时间.
如果该等待过多，可能说明LGWR的写出效率低下，或者系统提交过于频繁.
针对该问题，可以关注:
log file parallel write等待事件
user commits,user rollback等统计信息可以用于观察提交或回滚次数
解决方案:
1.提高LGWR性能。尽量使用快速磁盘，不要把redo log file存放在raid 5的磁盘上
2.使用批量提交
3.适当使用NOLOGGING/UNRECOVERABLE等选项
6.请写出同一条记录insert，delete，update操作中产生undo信息的大致内容
insert操作无论是1条或者多条，一次产生一条undo数据
delete操作产生的undo条数取决于delete操作本身影响的记录数
update操作产生的undo条数取决于被update的数据的原值和新值存在差异的记录数量
测试中表没有主键,如果有主键,所有操作都会增加一条undo数据.在删除或者update大量数据时候会产生大量undo,
所以建议这两种操作进行分批提交.
select used_ublk,used_urec from v$session s, v$transaction t where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
7.请写出latch的工作原理
latch是一种轻量级用于保护oracle共享内存结构，用户并发操作一致性的串行化锁定机制，如SGA中，各种数据被反复从磁盘读取到内存，又被重新写回到磁盘上，如果有并发用户做相同的事情，oracle必须使用一种机制来保证数据在读取的时候，只能由一个会话来完成，这就是latch,latch 不会造成阻塞，是只会等待，与每个latch相联系的还有一个清除过程，当持有latch的进程成为死进程时，系统清除过程就会被调用，系统lock导致 用户等待，需要考虑系统的逻辑设计是否有问题，如多用户对主键的删除或者修改，是否有用户使用select… for update这样的语法，外键是否创建索引。latch 争用多半要考虑系统及数据库自身设计问题，如绑定变量，热块及参数设置是否合理。
spin：比如数据缓存中的某个块要被读取，我们会获得这个块的 latch，这个过程叫做spin，另外一个进程恰好要修改这个块，他也要spin这个块，此时他必须等待，当前一个进程释放latch后才能spin 住，然后修改，如果多个进程同时请求的话，他们之间将出现竞争，没有一个入队机制，一旦前面进程释放锁定，后面的进程就蜂拥而上，没有先来后到的概念，并且这一切都发生的非常快，因为Latch的特点是快而短暂。
spin与休眠：
休眠意味着暂时的放弃CPU，进行上下文切换（context switch），这样CPU要保存当前进程运行时的一些状态信息，比如堆栈，信号量等数据结构，然后引入后续进程的状态信息，处理完后再切换回原来的进程状态，这个过程如果频繁的发生在一个高事务，高并发进程的处理系统里面，将是个很昂贵的资源消耗，所以Oracle选择了spin，让进程继续占有 CPU，运行一些空指令，之后继续请求，继续spin，直到达到_spin_count值，这时会放弃CPU，进行短暂的休眠，再继续刚才的动作。
oracle中，latch是一种轻量级的锁。一般来说，latch由三种内存元素成：pid（进程id），内存地址和内存长度。Latch保证对共享数据结构的排它性访问，以此来保证内存结构的完整性不受到损坏。在多个会话同时修改或者检视（inspect）sga中同一个内存结构时，必须串行化访问以保证sga中数据结构的完整性。
进程获取latch有两种模式：willing-to-wait和No_wait。no-wait模式只在少数latch中使用。通过no-wait模式 获取latch的统计信息记录在immediate_gets immediate_misses列中，这些列在v$latch，v$latch_parent，v$latch_children视图中都存在。一般来 说，no-wait模式在第一次获取一些有很多子latch的latch比如redo copy时使用。如果一个进程第一次获取这些子latch中的任何一个失败，它会立即使用no-wait模式询问下一个。只有当采用no-wait模式试图获取所有的子latch都失败以后，才会转而采用willing-to-wait模式。
通过willing-to-wait模式获取latch的统计信息存放在gets和misses列中。每当一个进程用willing-to-wait模式去获取一个latch时，gets都会增加。
如果进程在第一次请求latch时，latch可用，就会直接获得该latch。在修改任何受到保护的数据结构之前，进程会将一些恢复信息写入到latch恢复区，这样当获得latch的进程发生异常时，pmon进程才能够清理该进程持有的latch。
如果请求latch时，该latch不可用，进程就会在cpu中等待一小段时间（spin）然后重新请求latch。如果latch一直不可用，该过程（spin一段时间然后重新请求）会一直重复。重复的次数由隐含参数_spin_count决定，默认值2000。如果在请_spin_count次之内 获得了latch，就对spin_gets和misses列各加一，否则，进程v$session_wait中记录latch free等待事件，然后释放cpu，转入睡眠状态。睡眠一定时间后，进程被唤醒并重复上面的过程，一直到获得latch。在成功获得latch后，才会更行sleep列得统计信息。
由于进程只有在获得latch后才会停止对latch得请求，如果某个持有latch的进程发生异常，其他请求该latch的进程该怎么办？岂不是要一直等待下去？不会的。当一个进程请求latch失败一定次数后，它会请求pmon进程查看该latch的持有者，如果持有进程异常，pmon就会清理该进程，释放latch。
每个latch都有一个从0到13的优先级编号。父latch和独立latch的优先级编号是在oracle内核代码中固定的。子latch是在实例启动时创建，其优先级编号从其父latch继承。使用优先级可以避免死锁。
当一个进程请求no-wait模式的latch时，该latch的优先级编号必须和它当前已经持有的latch的优先级编号相同。
当一个进程请求willing-to-wait模式的latch时，该latch的优先级编号必须比它当前已经持有的latch的优先级编号要大。
进程获取Latch的过程：
任何时候，只有一个进程可以访问内存中的某一个数据块，如果进程因为别的进程正占用块而无法获得Latch时，他会对CPU进行一次spin（旋转），时间非常的短暂，spin过后继续获取，不成功仍然spin，直到 spin次数到达阀值限制（这个由隐含参数_spin_count指定），此时进程会停止spin,进行短期的休眠，休眠过后会继续刚才的动作，直到获取块上的Latch为止。进程休眠的时间也是存在算法的，他会随着spin次数而递增，以厘秒为单位，休眠的阀值限制由隐含参数 _max_exponential_sleep控制，默认是2秒，如果当前进程已经占用了别的Latch，则他的休眠时间不会太长（过长会引起别的进程的 Latch等待），此时的休眠最大时间有隐含参数_max_sleep_holding_latch决定，默认是4厘秒。这种时间限制的休眠又称为短期等待。另外一种情况是长期等待锁存器（Latch Wait Posting），此时等待进程请求Latch不成功，进入休眠，他会向锁存器等待链表（Latch Wait List）压入一条信号，表示获取Latch的请求，当占用进程释放Latch时会检查Latch Wait List，向请求的进程传递一个信号，激活休眠的进程。Latch Wait List是在SGA区维护的一个进程列表，他也需要Latch来保证其正常运行，默认情况下share pool latch和library cache latch是采用这个机制。
如果将隐含参数_latch_wait_posting设置为2，则所有Latch都采用这种等待方式，使用这种方式能够比较精确的唤醒某个等待的进程， 但维护Latch Wait List需要系统资源，并且对Latch Wait List上Latch的竞争也可能出现瓶颈。
数据缓冲池Latch争用
访问频率非常高的数据块被称为热快（Hot Block），当很多用户一起去访问某几个数据块时，就会导致一些Latch争用，最常见的latch争用有：
（1） buffer busy waits
（2） cache buffer chain
Cache buffer chian产生原因：
当一个会话需要去访问一个内存块时，它首先要去一个像链表一样的结构中去搜索这个数据块是否在内存中，当会话访问这个链表的时候需要获得一个Latch，如果获取失败，将会产生Latch cache buffer chain 等待，导致这个等待的原因是访问相同的数据块的会话太多或者这个列表太长（如果读到内存中的数据太多，需要管理数据块的hash列表就会很长，这样会话扫描列表的时间就会增加，持有chache buffer chain latch的时间就会变长，其他会话获得这个Latch的机会就会降低，等待就会增加）。
Buffer busy waits 产生原因：
当一个会话需要访问一个数据块，而这个数据块正在被另一个用户从磁盘读取到内存中或者这个数据块正在被另一个会话修改时，当前的会话就需要等待，就会产生一个buffer busy waits等待。
产生这些Latch争用的直接原因是太多的会话去访问相同的数据块导致热块问题，造成热块的原因可能是数据库设置导致或者重复执行的SQL频繁访问一些相同的数据块导致。
latch有40余种，但作为DBA关心的主要应有以下几种：
Cache buffers chains latch: 当用户进程搜索SGA寻找database cache buffers时需要使用此latch。
Cache buffers LRU chain latch: 当用户进程要搜索buffer cache中包括所有 dirty blocks的LRU (least recently used) 链时使用该种latch。
Redo log buffer latch: 这种latch控制redo log buffer中每条redo entries的空间分配。
Row cache objects latch: 当用户进程访问缓存的数据字典数值时，将使用Row cache objects latch。
下 面我们将着重介绍一下如何检测和减少redo log buffer latch的冲突。对redo log buffer的访问是由redo log buffer latch来控制的，这种latch有两种类型， redo allocation latch和redo copy latch。
Redo allocation latch控制redo entries在redo log buffer中的空间分配。Oracle的一个用户进程只有得到redo allocation latch后才能为redo entries在redo log buffer中分配空间，又由于一个instance只有一个redo allocation latch，所以一次只有一个用户进程在buffer中分配空间。当用户进程获得latch后，首先为redo entry分配空间，然后进程继续持有latch并拷贝entry到buffer中，这种拷贝称为“在redo allocation latch上的拷贝”(copying on the redo allocation latch)，拷贝完毕后，用户进程释放该latch。
一个“在redo allocation latch上的拷贝”的redo entry的最大值是由初始化参数LOG_SMALL_ENTRY_MAX_SIZE定义的，根据操作系统的不同而不同。
Redo Copy Latch只应用于多CPU的系统。在多CPU的instance中，如果一个redo entry太大，超过了LOG_SMALL_ENTRY_MAX_SIZE定义值，则不能进行“在redo allocation latch上的拷贝”, 此时用户进程必须获取redo copy latch。一个instance中可以有多个redo copy latch，其数目由初始参数LOG_SIMULTANEOUS_COPIES决定，缺省值为CPU数目。
在单CPU情况下，不存在redo copy latch，所有的redo entry无论大小, 都进行“在redo allocation latch上的拷贝”。
对redo log buffer的过多访问将导致redo log buffer latch的冲突，latch冲突将降低系统性能，我们可通过如下查询来检测这种latch冲突：
col name for a40
SELECT ln.name,gets,misses,immediate_gets,immediate_misses
FROM v$latch l,v$latchname ln
WHERE ln.name IN('redo allocation','redo copy') AND ln.latch#=l.latch#
/
若misses与gets的比例超过1%或immediate_misses与(immediate_gets+immediate_misses)比例超过1%时，应考虑采取措施减少latch的冲突。
大 多数的redo log buffer latch冲突是在多个CPU情况下，两个或多个Oracle进程试图同时得到相同的latch发生的。由于一个instance只有一个redo allocation latch，为减少redo allocation latch的冲突，应减少单个进程持有latch的时间，这可以通过减小初始参数LOG_SMALL_ENTRY_MAX_SIZE以减小redo entry的数目和大小来实现。如果观察到有redo copy latch冲突，可以通过增大LOG_SIMULTANEOUS_COPIES 初始参数来加大latch数目，其缺省值为CPU数目，最大可增大到CPU数目的两倍。
8.请写出rownum与rowid的区别
rownum只是你查询出来结果集的一个临时排列序号，必须从1开始，可以看作是随便可以变化的！
rowid是对于每一条数据的地址，你可以把它看作是固定的（频繁更新，删除操作除外）
当你从某个表中查询数据的时候，返回的结果集中都会带有rownum这个字段，而且有时候也可以使用rownum进行一些条件查询。
rownum是在得到结果集的时候产生的，用于标记结果集中结果顺序的一个字段，这个字段被称为“伪数列”，也就是事实上不存在的一个数列。它的特点是按顺序标记，而且是逐次递加的，换句话说就是只有有rownum=1的记录，才可能有rownum=2的记录。
和rownum相似，oracle还提供了另外一个伪数列：rowid。不过rowid和rownum不同，一般说来每一行数据对应的rowid是固定而且唯一的，在这一行数据存入数据库的时候就确定了。可以利用rowid来查询记录，而且通过rowid查询记录是查询速度最快的查询方法。(这个我没有试过，另外要记住一个长度在18位，而且没有太明显规律的字符串是一个很困难的事情，所以我个人认为利用rowid查询记录的实用性不是很大)rowid只有在表发生移动(比如表空间变化，数据导入/导出以后)，才会发生变化。
1、ROWNUM
定义： ROWNUM是一个伪列，标识了select从一个表或一组连接(JOIN)的表中查询数据时，返回记录的顺序。
Oracle在执行select查询时，会按照返回的row的顺序，依次为row分配一个序号：
返回的第一条row的序号为1，第二条row的序号为2，以此类推。
这个序号即为每条row的rownum。
注意1：同一个查询语句中，如果ROWNUM后，含有ORDER BY子句时：
Oracle先返回未应用ORDER BY处理的结果，分配ROWNUM后，再根据ORDER BY子句的要求排序。
因此，返回的结果中，ROWNUM顺序是混乱的。如：
SQL> SELECT ROWNUM,DEPT.* FROM SCOTT.DEPT ORDER BY DNAME;
ROWNUM DEPTNO DNAME LOC
-------------- ------------- -------------------- -------------
1 10 ACCOUNTING NEW YORK
4 40 OPERATIONS BOSTON
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
未应用ORDER BY的结果为：
SQL> SELECT ROWNUM,DEPT.* FROM SCOTT.DEPT;
ROWNUM DEPTNO DNAME LOC
-------------- ------------- ---------------------- -------------
1 10 ACCOUNTING NEW YORK
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
4 40 OPERATIONS BOSTON
欲使ORDER BY后的ROWNUM连续，可将ORDER BY放入子查询，如：
SQL> SELECT ROWNUM,T.* FROM (SELECT DEPT.* FROM SCOTT.DEPT ORDER BY LOC) T;
ROWNUM DEPTNO DNAME LOC
-------------- ------------- --------------------- -------------
1 40 OPERATIONS BOSTON
2 30 SALES CHICAGO
3 20 RESEARCH DALLAS
4 10 ACCOUNTING NEW YORK
注意2：对ROWNUM应用>,>=,=,between...and条件，返回结果都为空：
因为，当返回第一条记录，作为结果的第1条，分配rownum=1，
应用>,>=,=,between...and条件判断时，不满足条件，记录删除；
返回下一条记录时，仍作为结果的第1条，分配rownum=1，仍不满足条件，依次类推，结果为空。
select rownum from test where rownum=1; //返回1条记录（结果集中的第一条）
select rownum from test where rownum=2;
//返回0条。根据ROWNUM定义，不难看出，返回第1条记录时，因ROWNUM=1，因此过滤掉。
select rownum from test where rownum>10;
select rownum from test where rownum between 2 and 4;
应用：
1）可通过ROWNUM限制返回结果的记录数（行数）
SQL> select rownum from test where rownum<10;
2）通过ROWNUM为表中某列产生一个唯一（UNIQUE）值
SQL> UPDATE table_name SET column_name = ROWNUM;
//将rownum指定为该行某列的值。
二、ROWID
(一)、概念：行的ROWID标识了该行数据的地址，ROWID包含如下信息：
数据对象编号、
该行数据，在数据文件中的块编号
该行在数据块中的位置（第一行为0）
保存该行数据的数据文件的编号（第一个数据文件为1）
ROWID在插入数据时创建，在删除数据时，删除。
不能手动设置或删除ROWID，ORACLE内部通过ROWID组建索引；
(二)、类型
1、物理ROWID：保存普通表（不含索引组织的表IOT）、聚合表（clustered table）、分区和子分区表、索引、分区和子分区索引中行的地址；
2、逻辑ROWID：保存索引组织表（IOT）的地址；
(三)、物理ROWID
1、类型：物理ROWID包含扩展ROWID与受限ROWID两类
1)、扩展ROWID(extended rowid)
支持表空间关联的数据块地址，有效标识分区表、分区索引以及普通表和索引中的行。
Oracle 8i及更高版本支持extended rowid。
2)、受限ROWID(restricted rowid)
为了向后兼容，如oracle 7及更低版本。
2、扩展ROWID(extended rowid)
a.扩展ROWID使用base64为每一行数据的物理地址进行编码，包含A-Z,a-z,0-9，+以及/。如下查询：
SQL> select rowid,dept.* from dept;
ROWID DEPTNO DNAME LOC
-------------------------------- ------------ -------------------- ----------------
AAAMfKAAEAAAAAQAAA 10 ACCOUNTING NEW YORK
AAAMfKAAEAAAAAQAAB 20 RESEARCH DALLAS
AAAMfKAAEAAAAAQAAC 30 SALES CHICAGO
AAAMfKAAEAAAAAQAAD 40 OPERATIONS BOSTON
b.扩展ROWID格式
扩展ROWID共18位，包含4部分，OOOOOOFFFBBBBBBRRR
a)000000：数据对象编号，标识了数据库中的段；
b)FFF：表空间相关的数据文件编号；
c)BBBBBB：数据文件中的数据块编号；
d)RRR：在数据块中的行编号；
c.DBMS_ROWID包
a)dbms_rowid.rowid_object通过ROWID，获取该数据的对象编号
SQL> select dbms_rowid.rowid_object('AAAMfKAAEAAAAAQAAC') as object_id from dual;
OBJECT_ID
----------------
51146
b)dbms_rowid.rowid_relative_fno通过ROWID获取数据文件编号
SQL> select dbms_rowid.rowid_relative_fno('AAAMfKAAEAAAAAQAAC') as file_no from dual;
FILE_NO
-------------
4
c)dbms_rowid.rowid_block_number通过ROWID，获取该数据的数据块编号
SQL> select dbms_rowid.rowid_block_number('AAAMfKAAEAAAAAQAAC') as block_number from dual;
BLOCK_NUMBER
------------------------
16
d)dbms_rowid.rowid_row_number通过ROWID，获取数据块中的行编号
SQL> select dbms_rowid.rowid_row_number('AAAMfKAAEAAAAAQAAC') as row_no from dual;
ROW_NO
----------
2
3、受限ROWID(resticted rowid)
a.受限rowid用二进制表示每行数据的物理地址，当使用SQL Plus查询时，二进制表示法被转换为varchar2或16进制表示。
SQL> select dbms_rowid.rowid_to_restricted(rowid,1) as restricted_rowid,dept.* from scott.dept;
RESTRICTED_ROWID DEPTNO DNAME LOC
------------------------------- ------------- --------------------- ------------------
00000010.0000.0004 10 ACCOUNTING NEW YORK
00000010.0001.0004 20 RESEARCH DALLAS
00000010.0002.0004 30 SALES CHICAGO
00000010.0003.0004 40 OPERATIONS BOSTON
b.受限rowid格式：
共16位，包含3部分：AAAAAAAA.BBBB.CCCC
a)AAAAAAAA：保存该行数据的数据块编号
b)BBBB：该行数据在数据块中的行编号
c)CCCC：包含该行数据的数据文件编号
(四)、逻辑ROWID(logical rowid)
1、概述：索引组织的表(IOT)中，row保存在索引的叶子节点，可以在块内或块间移动。
因此，这些rows没有固定的物理地址，无法根据物理地址来唯一标识。
Oracle提供了逻辑ROWID，来标识IOT中的行，逻辑ROWID是基于表的主键；
Oracle可根据这些逻辑ROWID为IOT创建第二索引。
每个第二索引使用的逻辑ROWID都包含一个physical guess；physical guess标识了当创建第二索引时，IOT中每个row的块位置；
9.请写出丢失非当前重做日志时应如何处理
Oracle的重做日志文件(Online redo logfile)循环记录了数据库所有的事务。它的大小、个数和存储位置对数据库性能和恢复有重要影响。它一般由大小相同的几组文件构成。我们可以查看数据库视图v$logfile知道redo logfile的个数和存储位置。对每一个Oracle数据库都要求至少具有两个联机重做日志。
每一次新的事务提交时，Oracle将该事务写入日志文件，但并非此时也将修改的数据块写回原数据文件。由于内存读写和磁盘I/O存在几个数量级的效率差别，Oracle通过减少数据文件的物理I/O读写来大大提高数据库的性能；同时，又通过优先写日志文件来保证数据的正确性和一致性。基于这种机制，重做日志文件在数据库的实例恢复和介质恢复时至关重要，是oracle数据库最重要的物理文件之一。
如果数据库在启动时检测到重做日志丢失，数据库将无法启动。如果数据库在运行时切换日志文件组，检测到下一组或者全部的重做日志丢失，数据库将会崩溃。由于磁盘介质损坏或者人为的误删除文件，造成严重后果的事件近期时有发生。本文列举了重做日志丢失的数据库恢复，但如果按照冗余原则合理分布日志文件组的成员，如果工程师了解日志文件的基本原理和使用原则，就完全可以避免出现下列问题。
恢复方法
故障现象
SQL> startup mount
Oracle Instance Started
Database mounted
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/ORACLE/ORADATA/H817/REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
恢复注意事项
以下所列举的恢复方法，都属于不完全恢复或者强制恢复，会丢失当前重做日志中的事务数据。一旦操作不当，将带来数据丢失等严重后果，请遵循以下几个恢复原则：
1.请勿在生产系统上试用。
2.如果生产系统出现重做日志文件丢失的故障，请勿自行操作破坏现场，应该立刻联系Oracle工程师。
3.恢复成功之后，需要马上做一次数据库的全备份。
4.建议重做日志文件一定要实现镜象在不同的磁盘上，避免这种情况的发生。
恢复方法
1.首先检查重做日志文件状态，看看报错的日志文件的状态是否为Current
SQL> select * from v$log;
SQL> select * from v$logfile;
2.如果重做日志文件状态为Inactive，我们可以直接清除该日志文件的内容：
SQL> alter database clear logfile '/ORACLE/ORADATA/H817/REDO01.LOG';
3.如果重做日志文件状态为Current，恢复工作较为复杂，有以下四种情况：
1）通过下面步骤，数据库顺利打开
SQL> recover database until cancel;
Type Cancel when prompted
SQL>alter database open resetlogs;
2）第一种情况的'recover database until cancel' 操作遇到ORA-01547，ORA-01194，ORA-01110错误，需要整个数据库的物理备份，并根据归档日志恢复到错误时间点，前提是数据库是归档模式。
restore old backup
SQL> startup mount
SQL> recover database until cancel using backup controlfile;
SQL> alter database open resetlogs;
3）如果数据库是非归档模式，只能恢复整个物理备份，然后直接打开数据库。这种情况将丢失物理备份至故障发生前的全部数据。
4）如果数据库是非归档模式，且没有物理备份，只能通过特殊的隐含参数，允许数据库不一致的状况下打开数据库。这种恢复方法是没有办法之后的恢复方法，将导致数据库不一致，一般情况下不要采用。如确有需要，请在Oracle的技术人员指导下使用该方法。
l 关闭数据库
SQL>shutdown immediate
l 在init
_allow_resetlogs_corruption=TRUE
l 重新启动数据库,利用until cancel恢复
SQL>recover database until cancel;
Cancel
l 打开数据库
SQL>alter database open resetlogs;
l 数据库被打开后，马上执行一个全库导出。
关闭数据库，在init
10.请写出参数文件的作用
下面是是我对参数文件的一点总结
分为二类：
1.初始参数文件：pfile(initialization parameter file)
2.服务器参数文件：spfile(server-side parameter file)
主要的区别是：
1.pfile是文本文件，spfile是二进制文件
2.pfile可以通过文本编辑器修改，spfile主要是通过alter system set parameter=value [scope=memory|spfile|both]修改
oracle启动时，读取参数文件的顺序是：
1.spfile$ORACLE_SID.ORA(创建数据库时创建的spfile)
2.spfile.ora(oracle缺省spfile文件)
3.init$ORACLE_SID.ora(创建数据库时创建的pfile)
4.init.ora(oracle缺省pfile文件)
oracle会按顺序读出这些参数文件，如果在1没有找到就去找2，直到找到。
和参数文件相关常用SQL如下：
1.查看当前使用的spfile和pfile的位置：
show parameter spfile/show parameter pfile
2.查看参数文件的具体设置
show parameters
3.从spfile获取pfile
create pfile='pfilesid.ora' from spfile或者
create pfile='pfilesid.ora' from spfile='/$ORACLE_HOME/dbs/spfile.ora'
4.修改参数
alter system set parameter=value [scope=memory|spfile|both]
1.修改后当前就起作用，重启数据库不起作用
scope=memory
2.修改后当前不起作用，下次重启数据库才起作用
scope=spfile
3.修改后当前起作用，下次重启数据库也起作用
scope=both
Notice：命令alter system set parameter=value在没有加scope选项时，默认值是什么呢？多数朋友可能不加思索就会回答both这答案，实际的情况是，当oracle在以spfile参数启动时，默认值是both,而以pfile启动时，默认值是memory,也就是只修改当前值。所以如果不能确定启动的参数文件，可以用show parameter spfile查看。
5.指定初始参数文件启动数据库
startup pfile='/$ORACLE_HOME/dbs/init$ORACLE_SID.ora'
不能以指定服务器参数文件spfile来启动数据库
可以先将spfile转换成pfile，再用这个命令来达到目的
6.查看系统是以pfile还是spfile启动
show parameter spfile
如果是null值，就是pfile启动的
11.请写出event 10053的作用是什么
借助Oracle的10053事件event，我们可以监控到CBO对SQL进行成本计算和路径选择的过程和方法。
10053事件有两个级别：
Level 2：2级是1级的一个子集，它包含以下内容：
Column statistics
Single Access Paths
Join Costs
Table Joins Considered
Join Methods Considered (NL/MS/HA)
Level 1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：
Parameters used by the optimizer
Index statistics
启用10053事件
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 2';
关闭10053事件：
ALTER SESSION SET EVENTS '10053 trace name context off';
说明：
1、sqlplus中打开autotrace看到的执行计划实际上是用explain plan 命令得到的，explain plan 命令不会进行bind peeking。
应该通过v$sql_plan查看SQL的真实的执行计划。
2、10053只对CBO有效，而且如果一个sql语句已经解析过，就不会产生新的trace信息。
3、10053事件产生的trace文件不能用tkprof格式化。
12.请写出hash join 与nest loop的实现原理
nest loop:多用于联机业务 OLTP (exists)
嵌套循环连接的工作方式是这样的：
1、 Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（Outer Table）。由驱动表进行驱动连接的表或数据源称为内部表（Inner Table）。
2、 提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。在这个过程中，Oracle首先提取驱动表中符合条件的第一条记录，再与内部表的连接列进行关联查询相应的记录行。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。在这里需要说明的是，由于Oracle最小的IO单位为单个数据块，因此在这个过程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有行，再与内部表进行关联连接查询的，然后提取下一个数据块中的记录持续地循环连接下去。当然，如果单行记录跨越多个数据块的话，就是一次单条记录进行关联查询的。
Hash join:多用于批处理业务 (in)
一般用于一张小表和一张大表进行join时。Hash join的过程大致如下（下面所说的内存就指sort area，关于过程，后面会作详细讨论）：
1.一张小表被hash在内存中。因为数据量小，所以这张小表的大多数数据已经驻入在内存中，剩下的少量数据被放置在临时表空间中；
2.每读取大表的一条记录，就和小表中内存中的数据进行比较，如果符合，则立即输出数据（也就是说没有读取临时表空间中的小表的数据）。而如果大表的数据与小表中临时表空间的数据相符合，则不直接输出，而是也被存储临时表空间中。
3.当大表的所有数据都读取完毕，将临时表空间中的数据以其输出。
如果小表的数据量足够小（小于hash area size），那所有数据就都在内存中了，可以避免对临时表空间的读写。
如果是并行环境下，前面中的第2步就变成如下了：
2.每读取一条大表的记录，和内存中小表的数据比较，如果符合先做join，而不直接输出，直到整张大表数据读取完毕。如果内存足够，Join好的数据就保存在内存中。否则，就保存在临时表空间中。
NESTED LOOP
对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是 nested loops。
一般在nested loop中， 驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样就走nestedloop。如果驱动表返回记录太多，就不适合nested loops了。如果连接字段没有索引，则适合走hash join，因为不需要索引。
可用ordered提示来改变CBO默认的驱动表，可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。
HASH JOIN
hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。
当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。
至于两个表都进行分区，其好处是可以使用parallel query，就是多个进程同时对不同的分区进行join，然后再合并。但是复杂。
使用hash join时，HASH_AREA_SIZE初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET即可。
以下条件下hash join可能有优势：
两个巨大的表之间的连接。
在一个巨大的表和一个小表之间的连接。
可用ordered提示来改变CBO默认的驱动表，可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。
SORT MERGE JOIN
sort merge join的操作通常分三步：对连接的每个表做table access full；对table access full的结果进行排序；进行merge join对排序结果进行合并。sort merge join性能开销几乎都在前两步。一般是在没有索引的情况下，9i开始已经很少出现了，因为其排序成本高，大多为hash join替代了。
通常情况下hash join的效果都比sort merge join要好，然而如果行源已经被排过序，在执行sort merge join时不需要再排序了，这时sort merge join的性能会优于hash join。
在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下，sort merge join会比nested loops性能更佳。
可用USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。
13.请描述行连接与行迁移的原理
有两种情况会导致表中某行数据过大，一个数据块（data block）无法容纳。
行链接：当一行数据被插入时一个数据块就无法容纳。在这种情况下Oracle将这行数据存储在段内的一个数据块链（chain）中。在插入数据量大的行时常会发生行链接（row chaining），例如一个包含数据类型为 LONG 或 LONG RAW 列的数据行。此时行链接不可避免。
行迁移：原本存储在一个数据块（data block）内的数据行，因为更新操作导致长度增长，而所在数据块的可用空间也不能容纳增长后的数据行。在这种情况下，Oracle将此行数据迁移（migrate）到新的数据块中。Oracle在被迁移数据行原来所在位置保存一个指向新数据块的指针。被迁移数据行的 rowid 保持不变。
当数据行发生链接（chain）或迁移（migrate）时，对其访问将会造成 I/O 性能降低，因为Oracle为获取这些数据行的数据时，必须访问更多的数据块（data block）。
14.请写出pctfree及pctused的作用
PCTFREE 参数用来设置一个数据块（data block）中至少需要保留（reserve）多少可用空间（百分比值），为数据块中已有数据更新时可能发生的数据量增长做准备。例如，当用户用 CREATE TABLE 语句创建表时指定了以下参数：PCTFREE 20
这个参数设定了此表对应的数据段（data segment）中的每个数据块（data block）至少保留20%的可用空间，以备块中已有数据更新时使用。只要数据块中行数据区与数据块头的容量之和不超过数据块总容量的80%，用户就可以向其中插入新数据，数据行被放入行数据区（row data area），相关信息被写入数据块头（overhead area）。
PCTUSED 参数用于决定一个数据块（data block）是否可被用于插入新数据，她的依据是数据区（row data）与数据块头（overhead）的容量之和占数据块全部容量的最大百分比。当一个数据块中的可用空间比例小于 PCTFREE 参数的规定时，Oracle就认为此数据块无法被用于插入新数据，直到数据块中的占用容量比例小于 PCTUSED 参数的限定。在占用容量比例大于 PCTUSED 参数的限定之前，Oracle只在更新数据块内已有数据时才会使用此数据块的可用空间。例如，当用户用 CREATE TABLE 语句创建表时指定了以下参数：PCTUSED 40
在例子中，当此表的某数据块占用容量比例高于40%时，Oracle不会将此数据块用于插入新数据行（假设此数据块的可用空间曾经低于 PCTFREE 的限定）。
15.请写出分区表的作用
分区表的作用：提高查询速度（查询数据的时候不会再对表进行全部扫描，而扫描相应的分区表)，在表进行分区之后，逻辑上还是一张表，实际上已经把插入的数据分到了各个分区表中，数据物理的分到不同的分区表中。当一个分区损坏后，不影响其他分区中的数据。
分区表的类型：1)按范围进行分区2)列表分区3）散列分区4)范围列表组合5）范围散弄组合。
分区技术能够带来以下好处：
1)利用分区技术，用户可以在分区级（partition level）进行数据加载（data load），索引创建及重建，或备份恢复等数据管理操作，而非针对整个表执行。这大大减少了此类操作所需时间。
2）分区技术能够提高查询性能。在很多情况下，查询的结果集可能来自几个分区，而非整个表。 对于某些查询，这种技术（称为分区剪除（partition pruning））能够带来几个数据量级的性能提升。
3）分区技术能够显著缩短维护操作导致的停机时间。
4）由于对各分区的维护操作可以相互独立地进行，用户可以同时对表或索引的不同分区进行维护操作。用户还能在维护的同时对未受维护操作影响的分区执行 SELECT 及 DML 操作。
5）利用分区技术存储数据库中的关键表及索引，能够缩短此类对象的维护窗口（maintenance window），及恢复时间，并减少此类对象发生故障时对系统的影响，从而提高数据库的可用性。
6)采用分区技术时，用户无需对原有应用程序进行任何修改。例如，当用户将一个非分区表转化为分区表后，无需修改访问此表的 SELECT 语句及 DML 语句。用户无需重写应用程序代码就可以发挥分区技术的优势。
16.请写出参数session_cached_cursors 的作用
open_cursors:该参数含义是同一个session同时打开最多在使用的游标数。在Oracle10.2.0.1.0版本中默认为300。
session_cached_cursors：SESSION_CACHED_CURSORS, 就是说的是一个session可以缓存多少个cursor，让后续相同的SQL语句不再打开游标,从而避免软解析的过程来提高性能。(绑定变量是解决硬解析的问题)，软解析同硬解析一样,比较消耗资源.所以这个参数非常重要。在Oracle10.2.0.1.0版本中默认为20。
show parameter open_cursors ----每个session（会话）最多能同时打开多少个cursor（游标）
show parameter session_cached_cursors ----每个session（会话）最多可以缓存多少个关闭掉的cursor
select count(*) from v$open_cursor; ----是指当前实例的某个时刻的打开的cursor数目 COUNT(*)
a、两个参数之间没有任何关系，相互也不会有任何影响。
b、两个参数有着相同的作用：让后续相同的sql语句不在打开游标，从而避免软解析过程来提供应用程序的效率。
cursor的作用 open ->close. 频繁的开关会产生开销。SESSION_CACHED_CURSORS适当的增加提高了缓存open cursor的数量,减少了频繁开关的开销。
使用下面的sql判断session_cached_cursors的使用情况。如果使用率为100%则增大这个参数值。
Select 'session_cached_cursors' Parameter,
Lpad(Value, 5) Value,
Decode(Value, 0, ' n/a', To_Char(100 * Used / Value, '990') || '%') Usage
From (Select Max(s.Value) Used
From V$statname n, V$sesstat s
Where n.Name = 'session cursor cache count'
And s.Statistic# = n.Statistic#),
(Select Value From V$parameter Where Name = 'session_cached_cursors')
Union All
Select 'open_cursors',
Lpad(Value, 5),
To_Char(100 * Used / Value, '990') || '%'
From (Select Max(Sum(s.Value)) Used
From V$statname n, V$sesstat s
Where n.Name In
('opened cursors current', 'session cursor cache count')
And s.Statistic# = n.Statistic#
Group By s.Sid),
(Select Value From V$parameter Where Name = 'open_cursors');
17.请写出临时表空间的作用
1、临时表空间的作用：
临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。
当临时表空间不足时，表现为运算速度异常的慢，并且临时表空间迅速增长到最大空间（扩展的极限），并且一般不会自动清理了。
2、迅速增长的原因：
一般原因是sql算法不正确，可能导致出现了迪卡尔积。
另外一个原因是视图的创建问题（本人遇到的正是该问题！）。Oracle数据库中多张表联合查询时，极限的表单数量为4张，一般将以为3张！有时需要大于4张表联合查询时，怎么办呢？可以建立子视图：
如view0(联合table0,table1,table2),view1(联合table0,table3,table4),view2(联合table0,table5,table6)
然后将view0、view1、view2联合成view。
这样可以解决临时表空间迅速增长的问题！
临时表空间使用注意:
1.临时表空间 是用于在进行排序操作(如大型查询,创建索引和联合查询期间存储临时数据)
每个用户都有一个临时表空间
2.对于大型操作频繁,(大型查询,大型分类查询,大型统计分析等),应指定单独的临时表空间,以方便管理
3.分配用户单独临时表空间,一般是针对 大型产品数据库,OLTP数据库,数据库仓库
对于小型产品不需要单独制定临时表空间,使用默认临时表空间
当然解决方法也很简单:1、设置临时数据文件自动扩展，或者2、增大临时表空间。
扩展临时表空间：
方法一、增大临时文件大小：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m;
Database altered.
方法二、将临时数据文件设为自动扩展：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;
Database altered.
方法三、向临时表空间中添加数据文件：
SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;
18.请写出全局索引及本地索引的区别
局部索引local index
1. 局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区数，一句话，局部索引的分区机制和表的分区机制一样。
2. 如果局部索引的索引列以分区键开头，则称为前缀局部索引。
3. 如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。
4. 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。
5. 局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。
6. 局部分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区， 对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。
7. 位图索引只能为局部分区索引。
8. 局部索引多应用于数据仓库环境中。
全局索引global index
1. 全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。
2. 全局索引可以分区，也可以是不分区索引，全局有分区索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。
3. 全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只动，截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。
4. 全局索引多应用于oltp系统中。
5. 全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。
6. oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。
7. 表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。
19.请写出如何定位消耗资源多的SQL
statspack-- 在你库上业务最忙得时候抓15分钟的report,看里面的top sql///////awr/ash
1 top找到消耗资源多的pid
2 确定是oracle的应用进程还是后台进程
3 根据v$session,v$process,v$sqlarea定位
用oem带的top session
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC
1. 值得怀疑的SQL
select substr(to_char(s.pct, '99.00'), 2) || '%' load, s.executions executes, p.sql_text from (select address,disk_reads,executions,pct,rank() over(order by disk_reads desc) ranking from (select address,disk_reads,executions,100 * ratio_to_report(disk_reads) over() pct from sys.v_$sql where command_type != 47) where disk_reads > 50 * executions) s, sys.v_$sqltext p where s.ranking <= 5 and p.address = s.address order by 1, s.address, p.piece;
2、查看消耗内存多的sql
select b.username,a. buffer_gets,a.executions,a.disk_reads / decode(a.executions, 0, 1, a.executions),a.sql_text SQL from v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and a.disk_reads > 10000 order by disk_reads desc;
3.逻辑读多的SQL
select * from (select buffer_gets, sql_text from v$sqlarea where buffer_gets > 500000 order by buffer_gets desc) where rownum <= 30;
4.执行次数多的SQL
select sql_text, executions from (select sql_text, executions from v$sqlarea order by executions desc) where rownum < 81;
5. 读硬盘多的SQL
select sql_text, disk_reads from (select sql_text, disk_reads from v$sqlarea order by disk_reads desc) where rownum < 21;
6. 排序多的SQL
select sql_text, sorts from (select sql_text, sorts from v$sqlarea order by sorts desc) where rownum < 21;
7.分析的次数太多，执行的次数太少，要用绑变量的方法来写sql
set pagesize 600;
set linesize 120;
select substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs" from v$sqlarea where executions < 5 group by substr(sql_text, 1, 80) having count(*) > 30 order by 2;
8.游标的观察
set pages 300;
select sum(a.value), b.name from v$sesstat a, v$statname b where a.statistic# = b.statistic# and b.name = 'opened cursors current'group by b.name;
select count(0) from v$open_cursor;
select user_name, sql_text, count(0) from v$open_cursor group by user_name, sql_text having count(0) > 30;
9.查看当前用户&username执行的SQL
select sql_text from v$sqltext_with_newlines where (hash_value, address) in (select sql_hash_value, sql_address from v$session where username = '&username') order by address, piece;
20.请写出oracle主要后台进程的作用
Oracle中的进程共分为三类：用户进程、服务进程、后台进程。其中后台进程伴随实例的启动而启动，他们主要是维护数据库的稳定，相当于一个企业中的管理者及内部服务人员。他们并不会直接给用户提过服务。
一：database write：DBWn数据写入
作用：把SGA中被修改的数据同步到磁盘文件中。保证Buffer Cache中有足够的空闲数据块数量。
触发条件：1，检查点
2，一个服务进程在设定的时间内没有找到空闲块。
3，每3秒自动唤醒一次。
设置：DB_WRITER_PROCESSES用来定义DBWn进程数量。（commit命令只是把记录修改写入日志文件，不是把修改后的数据写入数据文件，下面还会提到。）
二：log write LGWR：日志文件写入
作用：把log buffer中的日志内容写入联机的日志文件中，释放log用户buffer空间呢。
触发条件：1，用户发出commit指令。（在oracle中称为快速提交机制（fast commit）：把redo log buffer中的记录写入日志文件，写入一条已提交的记录）。
2，三秒钟定时唤醒。
3，log buffer超过1/3，或日志数量超过1M。
4，DBWR进程触发：DBWn试图将脏数据块写入磁盘先检查他的相关redo记录是否写入联机日志文件，如果没有就通知LGWR进程。
在oracle中称为提前写机制（write ahead）：redo记录先于数据记录被写入磁盘。
三：checkpoint CKPT：检查点事件
作用：维护数据库一致性状态。检查点时刻数据文件与SGA中的内容一致。这不是一个单独的进程，要和前两个进程一起工作。DBWR写入脏数据，同时触发LGWR进程。
CKPT更新控制文件中的检查点记录。
触发条件：日志切换（log switch）会触发检查点。
四：process monitor PMON ：维护用户进程
作用：1,发现用户进程异常终止，并进行清理。释放占用资源。（清理异常终止用户使用的锁）。
2，向监听程序动态的注册实例。
触发条件：定时被唤醒，其他进程也会主动唤醒他。
五：system monitor SMON：实例维护
作用：1，负责实例恢复，前滚（Roll Forward）恢复到实例关闭时刻的状态，使用最后一次检查点后的日志进行重做。这时包括提交和未提交的事务。打开数据库，进行回滚（Roll Back）：回滚未提交的事务。
（Oracle承诺commit之后的数据不会丢失，现在我们可以大致的了解Oracle是如何实现这个承诺的，以及在数据的安全性和数据库性能之间的平衡选择。）
2，负责清理临时段，以释放空间；
触发条件：定期被唤醒或者被其他事务主动唤醒。
六：archive ARCn：归档操作
作用：发生日志切换时把写满的联机日志文件拷贝到归档目录中。
触发条件：日志切换时被LGWR唤醒。
设置：LOG_ARCHIVE_MAX_PROCESSES可以设置oracle启动的时候ARCn的个数。
七：manageability monitor MMON：AWR主要的进程。
作用：1收集AWR必须的统计数据，把统计数据写入磁盘。10g中保存在SYSAUX表空间中。
2，生成server-generated报警。
每一个小时把shard pool中的统计信息写入磁盘，或shard pool占用超过5%。
八：manageability monitor light MMNL：轻量级的MMON；
九：memory manager MMAN：自动内存管理
作用：每分钟都检查AWR性能信息，并根据这些信息来决定SGA组件最佳分布。
设置：STATISTICS_LEVEL：统计级别
SGA_TARGET：SGA总大小
十：job queue coordinator CJQO：数据库定时任务
十一：recover writer RVWR：为flashback database提供日志记录。把数据块的前镜像写入日志。
十二：change tracking writer CTWR：跟踪数据块的变化，把数据块地址记录到change_tracking file文件中。RMAN的增量备份将使用这个文件来确定那些数据块发生了变化，并进行备份。
以上是oracle中比较常见的后台进程，不是所有的进程。其中DBWn，ARCn可以设置数据库启动时的数量。这些进程在系统中可以不止一个。
Oracle实战类：
1.表tab1,由5列组成，存在1000万条记录，现查询该表非常慢，请问如何解决该问题
首先先用查询条件(order by等)查询一下 如果还慢，查看是否有索引，没有建立索引，有索引重新建立索引。
索引结构
如何再不行，重新将表分区。
2.数据库突然变得非常缓慢，请写出诊断思路
看是否有哪些sql语句发生死锁，堵塞
看看alert文件有什么错误或警告没有,此外在慢的时候查查v$session_wait有什么等待事件
看回滚空间占用率是多少，temp空间占用率是多少。
如果回滚空间占用率太多，就看是什么程序在其中占用了大量的回滚空间，而且没有自我释放，一直在运行，从中，可以判断出是什么问题。
然后在杀数据库的进程，将死循环或者其他死进程给杀了。
还有可能是临时表空间满了
awr
3.数据库alert日志中报错ORA-01555，请写出诊断思路
Linux下执行：oerr ora 1555查看错误解释
先看报错，是否因为undo表空间太小，如果是，扩大undo表空间；如果不是，增加参数undo_retention；
undo基本信息:
select inst_id,name,value from gv$parameter where upper(name) like '%UNDO%';
select distinct status,count(*) extent_num , sum(bytes/1024/1024) undosize from dba_undo_extents group by status;
4.数据库在备份时报错块损坏，请写出处理思路
如果是物理损坏，换盘，如果是逻辑损坏，先定位到块损坏的位置。////如何定位数据块的位置。
停止数据库备份：用bbed来修复坏块
recover datafile'/u01/oracle/oracle10g/oradata/TEST/test.dbf';恢复数据文件。
5.数据库突然异常关闭，请写出处理思路
先重新启动如果不行查看alert
总结：
当发生1102错误时，可以按照以下流程检查、排错：
如果是HA系统，检查其他节点是否已经启动实例；
检查Oracle进程是否存在，如果存在则杀掉进程；
检查信号量是否存在，如果存在，则清除信号量；
检查共享内存段是否存在，如果存在，则清除共享内存段；
检查锁内存文件lk
服务器异常断电，导致oracle 数据库无法打开：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 1 sequence# 8 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 1 thread 1: '/oracle/oradate/orcl/redo1.log'
处理过程是：
SQL> select group#, sequence# from v$log;
GROUP# SEQUENCE#
------- ----------
1 10
2 11
3 12
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
当时没能创建归档日志的原因是对归档文件夹的操作权限不够。赋予足够权限以后
SQL> alter system archive log start;
System altered.
某系统突然掉电，系统启动后发现Oracle无法启动。启动时报如下错误：
ORA-01102 cannot mount database in EXCLUSIVE mode
出现1102错误可能有以下几种可能：
一、在HA系统中，已经有其他节点启动了实例，将双机共享的资源（如磁盘阵列上的裸设备）占用了；
二、说明Oracle被异常关闭时，有资源没有被释放，一般有以下几种可能，
1、 Oracle的共享内存段或信号量没有被释放；
2、 Oracle的后台进程（如SMON、PMON、DBWn等）没有被关闭；
3、 用于锁内存的文件lk
首先，虽然我们的系统是HA系统，但是备节点的实例始终处在关闭状态，这点通过在备节点上查数据库状态可以证实。
其次、是因系统掉电引起数据库宕机的，系统在接电后被重启，因此我们排除了第二种可能种的1、2点。最可疑的就是第3点了。
查$ORACLE_HOME/dbs目录：
$ cd $ORACLE_HOME/dbs$ ls sgadef*sgadef* not found$ ls lk*lkORA92
果然，lk
$ rm lk*
再启动数据库，成功。
如果怀疑是共享内存没有被释放，可以用以下命令查看：
$ipcs -mopIPC status from /dev/kmem
as of Thu Jul 6 14:41:43 2006T ID KEY MODE OWNER GROUP NATTCH CPID LPIDShared Memory:m 0 0x411c29d6
--rw-rw-rw- root root 0 899 899m 1 0x4e0c0002
--rw-rw-rw- root root 2 899 901m 2 0x4120007a
--rw-rw-rw- root root 2 899 901m 458755 0x0c6629c9
--rw-r----- root sys 2 9113 17065m 4 0x06347849
--rw-rw-rw- root root 1 1661 9150m 65541 0xffffffff
--rw-r--r-- root root 0 1659 1659m 524294 0x5e100011
--rw------- root root 1 1811 1811m 851975 0x5fe48aa4
--rw-r----- oracle oinstall 66 2017 25076
然后它ID号清除共享内存段：
$ipcrm –m 851975
对于信号量，可以用以下命令查看：
$ ipcs -sopIPC status from /dev/kmem
as of Thu Jul 6 14:44:16 2006T ID KEY MODE OWNER GROUPSemaphores:s 0 0x4f1c0139
--ra------- root root... ...s 14 0x6c200ad8
--ra-ra-ra- root roots 15 0x6d200ad8
--ra-ra-ra- root roots 16 0x6f200ad8
--ra-ra-ra- root roots 17 0xffffffff
--ra-r--r-- root roots 18 0x410c05c7
--ra-ra-ra- root roots 19 0x00446f6e
--ra-r--r-- root roots 20 0x00446f6d
--ra-r--r-- root roots 21 0x00000001
--ra-ra-ra- root roots 45078 0x67e72b58
--ra-r----- oracle oinstall
根据信号量ID，用以下命令清除信号量：
$ipcrm -s 45078
如果是Oracle进程没有关闭，用以下命令查出存在的oracle进程：
$ ps -ef|grep ora oracle 29976 1 0 Jun 22 ? 0:52 ora_dbw0_ora92 oracle 29978 1 0 Jun 22 ? 0:51 ora_dbw1_ora92 oracle 5128 1 0 Jul 5 ? 0:00 oracleora92 (LOCAL=NO)... ...
然后用kill -9命令杀掉进程
$kill -9
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26845409/viewspace-1695753/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
