简答题1. 如何列举某个用户下所有表的注释及列的注释?
可以使用DBA_TAB_COMMENTS视图来查询表的注释,使用DBA_COL_COMMENTS视图来查询列的注释。某个用户下所有表的注释:
SELECT D.OWNER,D.TABLE_NAME,D.COMMENTS FROM DBA_TAB_COMMENTS D WHERE D.OWNER='LHR' AND D.COMMENTS IS NOTNULL;
其结果如下:
某个用户下某个表的所有列的注释:
SELECT D.OWNER, D.TABLE_NAME,D.COLUMN_NAME,D.COMMENTS
FROM DBA_COL_COMMENTS D WHERE D.OWNER='LHR' AND D.TABLE_NAME='CMMND_INFO_HSTRY' AND D.COMMENTS IS NOT NULL;
其结果如下:
2. Oracle 10g和11g告警日志文件的位置在哪里?
告警日志文件有两种类型,一种是纯文本格式的,另外一种是xml文件格式的,平时查看最多的是纯文本格式的告警日志。告警日志的内容包含消息和错误的类型,ORA-600内部错误,ORA-1578块损坏错误,ORA-12012作业队列错误,实例启动关闭、恢复等信息,特定的DDL命令,影响表空间、数据文件及回滚段的命令,可持续的命令被挂起,LGWR不能写入日志文件,归档进程启动信息、调度进程的相关信息和动态参数的修改信息等。
无论是Oracle 10g还是11g,其告警日志的位置都可以由参数BACKGROUND_DUMP_DEST来查询,只不过在Oracle 11g中位置有所变化。在Oracle 10g中,告警日志一般在$ORACLE_BASE/admin/$ORACLE_SID/bdump目录下:
在Oracle 11g中,告警日志一般在$ORACLE_BASE/diag/rdbms/$DBNAME/$ORACLE_SID/traca目录下:
从Oracle 12.1.0.1开始,参数BACKGROUND_DUMP_DEST已废弃,告警日志的位置应该由以下SQL来查询:
SQL>SELECT d.VALUE FROM V$DIAG_INFO d WHERE d.NAME='Diag Trace';
VALUE
--------------------------------------------
/u01/app/oraele/diag/rdbms/lhrdb/lhrdb/trace
需要注意的是,该SQL也适用于Oracle 11g的版本。
3. 若告警日志文件过大,则如何有效管理告警日志文件?
由于告警日志按时间的先后顺序不断累积存储,久而久之,势必造成告警日志的过大,难以维护,查找相关的信息也不方便。另外,若告警日志超大,也会严重影响系统的性能。这里提供3种办法来管理告警日志:①使用外部表方式来管理告警日志将大大简化维护工作量,也可以更直观地获取所需的信息;②视图V$DIAG_ALERT_EXT对应的基表里存储了告警日志的内容,可以根据该视图将告警日志的内容存储在历史表中;③利用SHELL脚本定时将告警日志进行备份,防止告警日志过大而影响系统性能。
4. 数据库处于RESTRICT、QUIESCE和SUSPEND状态的区别是什么?
在Oracle中,数据库可以有3种限制性状态,见下表。
需要注意的是,可以使用“SELECT*FROM SESSION_ROLES;”查询当前用户具有的角色。
5. 数据库的启动经历几个过程?
Oracle数据库的启动,严格来说应该是实例的启动,数据库仅仅是在实例启动后进行装载。Oracle数据库启动的过程被划分为几个不同的步骤,在不同的启动过程中,可以对其实现不同的操作。
启动命令:STARTUP [FORCE][RESTRICT][PFILE=...][NOMOUNT][MOUNT][OPEN]
启动过程:NOMOUNT--->MOUNT--->OPEN,详细情况见下表。
6. 关闭数据库有几种方式?
数据库的关闭也有一些参数需要注意,关闭命令为
SHUTDOWN ABORT|IMMEDIATE|TRANSACTIONAL|NORMAL
其中,SHUTDOWN不带参数将默认为NORMAL。一般情况下,关闭数据库都使用“SHUTDOWN IMMEDIATE”命令。这几个参数的区别见下表。
SHUTDOWN
|
NORMAL
|
TRANSACTIONAL
|
IMMEDIATE
|
ABORT
|
是否允许新连接
|
N
|
N
|
N
|
N
|
是否允许新事务
|
Y
|
N
|
N
|
N
|
是否等待所有来提交事务 完成提交
|
Y
|
Y
|
N
|
N
|
等待所有会话断开连接
|
Y
|
N
|
N
|
N
|
强制检查点
|
Y
|
Y
|
Y
|
N
|
实例恢复
|
N
|
N
|
N
|
Y
|
告警日志
|
Shutting down instance (normal)
|
Shutting down instance (transactional)
|
Shutting down instance (immediate)
|
Shutting down instance (abort)
|
1)NORMAL表示不准许新的连接,等待当前所有连接到数据库系统的用户都断开连接(DISCONNECT),强制检查点并关闭文件,下次启动不需要实例恢复。
2)TRANSACTIONALE表示不准许新的连接,不允许开启新事务,数据库等待所有事务完成后断开当前连接到数据库的所有用户,强制检查点并关闭文件,下次启动不需要实例恢复。
3)IMMEDIATE表示不准许新的连接,不允许开启新事务,任何未提交的事务都进行回滚。数据库显式回滚活动的事务并断开所有当前连接到数据库的用户,强制检查点并关闭文件,下次启动不需要实例恢复。
4)ABORT表示不准许新的连接,不允许开启新事务,任何未提交的事务不进行回滚。数据库立刻中断所有sQL的执行,并断开所有用户的连接。不做强制检查点,下次启动需要做实例恢复。
7. 索引包括那几个特性?
一般来说索引有3大特性,即索引高度比较低、索引存储列值及索引本身有序,对这3大特性的应用见下表。
索引特性
|
带来的优势
|
应用的常见SQL
|
高度比较低
|
索引高度低有利于索引范围扫描,这也是通过索引可以非常迅速地从 海量数据中获取少量数据的原因。索引的高度越高,访问索引需要读取 的数据块数越多,效率越差
|
SELECT*FROM T WHERE ID=1;
|
索引存储列值
|
由于索引比表一般要小得多,所以在通过索引本身就可以查找到所需 要的数据的情况下,可以将表看成是一个“瘦表”或“小表”,无须索引 回表读这个过程(索引回表读是非常耗费性能的),这样访问路径就会大 大减少。利用该特性可以优化的SQL包括COUNT(*)、SUM(索引列)等 聚合语句
|
SELECT COUNT(*)FROM T; SELECT SUM(OBJECT_ID)FROM T;
|
索引本身有序
|
能够利用索引本身已有的排序特性来消除目标SQL的排序操作,从而 优化ORDER BY等排序语句
|
SELECT*FROM T WHERE OBJECT_ID>2 ORDER BY OBJECT_ID;
|
能够用索引有序的特性在叶子块的最左边或最右边找到最小和最大 值,从而优化类型MAX、MIN语句
|
SELECT MAX(OBJECT_ID)FROM T;
|
另外,需要特别注意的是,索引不存储空值。
[考点] 索引
8. 如何预估即将创建索引的大小?
如果当前表大小是1TB,那么在某一列上创建索引的话索引大概占用多大的空间?对于这个问题,Oracle提供了2种可以预估将要创建的索引大小的办法:
1)利用系统包DBMS_SPACE.CREATE_INDEX_COST直接得到。利用DBMS_SPACE.CREATE_TABLE_COST可以获得将要创建的表的大小。
2)利用Oracle 11g新特性NOTE RAISED WHEN EXPLAIN PLAN FOR CREATE INDEX。
[考点] 索引
9. 如何监控索引的使用状况?
在开发应用程序时,可能会建立很多索引,那么这些索引的使用到底怎么样,是否有些索引一直都没有用到过,在这种情况下就需要对这些索引进行监控,以便确定它们的使用情况,并为是否可以清除它们给出依据。
监控索引有两种方式:
(1)直接监控索引的使用情况
1)设置所要监控的索引:ALTER INDEX IDX_T_XX MONITORING USAGE;
2)查看该索引有没有被使用:SELECT*FROM V$OBJECT_USAGE;
3)关闭监控:ALTER INDEX IDX_T_XX NOMONITORING USAGE;
查询V$OBJECT_USAGE就可以知道数据库对索引的使用情况。通过一段时间的监控,就可以确定哪些是无用的索引。另外,为了避免使用V$OBJECT_USAGE只能查询到当前用户下索引的监控情况,可以使用如下语句查询数据库中所有被监控索引的使用情况:
SELECT U.NAME OWNER,IO.NAME INDEX_NAME, T.NAME TABLE_NAME,DECODE(BITAND(I.FLAGS,65536),0,'NO','YES') MONITORING,
DECODE(BITAND(OU.FLAGS,1),0,'NO','YES')USED, OU.START_MONITORING START_MONITORING,
OU.END_MONITORING END_MONITORING
FROM SYS.USERS U, SYS.OBJ$IO, SYS.OBJ$ T. SYS.IND$ I, SYS.OBJECT_USAGE OU
WHERE I.OBJ#=OU.OBJ# AND IO.OBJ#=OU.OBJ# AND T.OBJ#=I.BO# AND U.USER#=IO.OWNER#;
(2)通过查看历史的执行计划,分析索引的使用情况
可以从视图DBA_HIST_SQL_PLAN中获取到数据库中所有索引的扫描次数情况,然后根据扫描次数和开发人员沟通是否需要保留索引。
[考点] 索引
10. 哪些操作会导致索引失效?
当某些操作导致数据行的ROWID改变,索引就会完全失效。可以分普通表和分区表来讨论哪些操作将导致索引失效。
1)普通表索引失效的情形如下:
①手动置索引无效:ALTER INDEX IND_OBJECT_ID UNUSABLE;。
②如果对表进行MOVE操作(包含移动表空间和压缩操作)或在线重定义表后,那么该表上所有的索引状态会变为UNUSABLE。MOVE操作的SQL语句为ALTER TABLE TT MOVE;。
③SQL*Loader加载数据。
在SQL*Loader加载过程中会维护索引,由于数据量比较大,在SQL*Loader加载过程中出现异常情况,也会导致Oracle来不及维护索引,导致索引处于失效状态,影响查询和加载。异常情况主要有在加载过程中杀掉SQL*Loader进程、重启或表空间不足等。
2)分区表索引失效的情形如下:
①对分区表的某个含有数据的分区执行了TRUNCATE、DROP操作可以导致该分区表的全局索引失效,而分区索引依然有效,如果操作的分区没有数据,那么不会影响索引的状态。需要注意的是,对分区表的ADD操作对分区索引和全局索引没有影响。
②执行EXCHANGE操作后,全局索引和分区索引都会被无条件地置为UNUSABLE(无论分区是否含有数据)。但是,若包含INCLUDING INDEXES子句(默认情况下为EXCLUDING INDEXES),则全局索引会失效,而分区索引依然有效。
③如果执行SPLIT的目标分区含有数据,那么在执行SPLIT操作后,全局索引和分区索引都会被置为UNUSABLE。如果执行SPLIT的目标分区没有数据,那么不会影响索引的状态。
④对分区表执行MOVE操作后,全局索引和分区索引都会被置于无效状态。
⑤手动置其无效:ALTER INDEX IND_OBJECT_ID UNUSABLE;。
对于分区表而言,除了ADD操作之外,TRUNCATE、DROP、EXCHANGE和SPLIT操作均会导致全局索引失效,但是可以加上UPDATE GLOBAL INDEXES子句让全局索引不失效。重建分区索引的命令为ALTER INDEX IDX_RANG_LHR REBUILD PARTITION P1;。
[考点] 索引
11. 聚簇因子(Clustering Factor)是什么?
Oracle数据库中最普通、最为常用的即为堆表,堆表的数据存储方式为无序存储,当对数据进行检索的时候,非常消耗资源,这个时候就可以为表创建索引了。在索引中,数据是按照一定的顺序排列起来的。当新建或重建索引时,索引列上的顺序是有序的,而表上的顺序是无序的,这样就存在了差异,即表现为聚簇因子(Chastering Factor,CF),也称为群集因子或集群因子等,本书统一称为聚簇因子。聚簇因子值的大小对CBO判断是否选择相关的索引起着至关重要的作用。
在Oracle数据库中,聚簇因子是指按照索引键值排序的索引行和存储于对应表中数据行的存储顺序的相似程度,也就是说,表中数据的存储顺序和某些索引字段顺序的符合程度。CF是基于表上索引列上的一个值,每一个索引都有一个CF值。
Oracle按照索引块所存储的ROWID来标识相邻索引记录在表块中是否为相同块。好的CF值接近于表上的块数,而差的CF值则接近于表上的行数。CF值越小,相似度越高,CF值越大,相似度越低。如果CF的值接近块数,那么说明表的存储和索引存储排序接近,也就是说表中的记录很有序,这样在做INDEX RANGE SCAN的时候,读取少量的数据块就能得到想要的数据,代价比较小。如果CF值接近表记录数,那么说明表的存储和索引排序差异很大,在做INDEX RANGE SCAN的时候,由于表记录分散,所以会额外读取多个块,代价较高。
由于聚簇因子高的索引走索引范围扫描时比相同条件下聚簇因子低的索引要耗费更多的物理I/O,所以聚簇因子高的索引走索引范围扫描的成本会比相同条件下聚簇因子低的索引走索引范围扫描的成本高。Oracle选择索引范围扫描的成本可以近似看作是和聚簇因子成正比,因此,聚簇因子值的大小实际上对CBO判断是否走相关的索引起着至关重要的作用。其实,聚簇因子决定着索引回表读的开销。在Oracle数据库中,能够降低目标索引的聚簇因子的唯一方法就是对表中数据按照目标索引的索引键值排序后重新存储。需要注意的是,这种方法可能会同时增加该表上存在的其他索引的聚簇因子的值。
可以通过如下的命令显式地设置聚簇因子的值:
EXEC DBMS_STATS.SET_INDEX_STATS(OWNNAME=>'LHR',INDNAME=>'IND2',CLSTFCT=>400000000,NO_INVALIDATE=>FALSE);
CF值可以通过查询视图DBA_INDEXES中的CLUSTERING_FACTOR列来获取。下边的SQL是查询索引的相关信息,通过视图DBA_INDEXES、DBA_OBJECTS和DBA_TABLES关联得到,可以查询当前索引的大小、行数、创建日期、索引高度和聚簇因子等信息。
[考点] 索引
12. 什么是索引的选择性(Index Selectivity)?
索引的选择性(Index selectivity,索引的选择度或索引的选择率)是指索引列中不同值的记录数与表中总记录数的比值。索引的选择性的取值范围是[0,1]。例如,某个表的记录数是1000条,而该表的索引列的值只有900个不同的值(有100个是相同或者空),所以,该列索引的选择性为900/1000=0.9。对于索引的选择性,值越高表示该列索引的效率也就越高。可以使用如下的SQL来计算索引的选择性:
SELECT COUNT(DISTINCT NAME)/COUNT(*)FROM TB_A;
这种方法的优点是在创建索引前就能评估索引的选择性。当索引被收集了最新的统计信息时,可以使用如下的SQL语句查询索引的选择性:
SELECT INDEX_NAME,DISTINCT_KEYS/NUM_ROWS SELECTIVEITY FROM DBA_INDEXES;
毋庸置疑,主键的选择性为1。选择性越接近1,那么该索引就越好。
[考点] 索引
13. 哪几种情况不能使用索引?
有多种原因会导致索引不能被使用。首要的原因就是统计信息不准,第二原因就是索引的选择度不高,使用索引比使用全表扫描效率更差。还有一个比较常见的原因,就是对索引列进行了函数、算术运算或其他表达式等操作,或出现隐式类型转换,导致无法使用索引。还有很多其他原因会导致不能使用索引,这个问题在MOS(MOS即My Oracle Support)“文档1549181.1为何在查询中索引未被使用”中有非常详细的解释。详细情况见下表。
分类
|
原 因
|
解 释
|
快速 检查
|
表上是甭存在索引
|
检查那些认为应该通过索引访问的表上是否真的创建了索引。那些索引可能已经被删掉或者在创 建的时候就失败了。例如,一种可能的场景是,在对表做导入操作后,由于软件或人为错误造成索 引没有被创建。通过DBA_INDEXES视图可以检查索引是否存在
|
索引是否应该被使用
|
Oracle不会仅仅因为有索引存在就一定要使用索引。如果一个查询需要检索出这个表里所有的记 录,那么只需要单独访问表的数据会更快。对所有的查询而言,Oracle优化器会基于统计信息来计 算各种访问路径,包括索引,从而选出最优的一条路径
|
索引本 身的问 题
|
索引的索引列是否在 WHERE条件中(Predicate List)
|
对于单列索引而言,只有当索引列出现在查询的WHERE条件中时,Oracle才能使用到索引。对 于组合索引而言,如果索引的前置列没有出现在WHERE条件中,而是用到了组合索引的其他索引 列,那么这时候Oracle可能会选择索引跳跃扫描(Index Skip Scan,INDEX_SS)或不会选择索引 扫描
|
索引列是否用在连接谓 词中(Join Predicates)
|
如果索引列是连接谓词的一部分,那么需要查看使用了哪种类型的连接方式?在两张表连接中, 且内表的目标列上建有索引时,只有NL连接才能有效地利用到该索引。SMJ即使相关列上建有索 引,最多只能因索引的存在,避免数据排序过程。HJ由于须做HASH运算,索引的存在对数据查 询速度几乎没有影响
|
连接顺序(Join Order)是 否允许使用索引
|
查看连接顺序(Join Order)是否允许使用相关索引。假设表A的ID列上有索引,表B的列ID 上无索引,WHERE语句有A.ID=B.ID条件,并且查询中没有与A.ID相关的其他谓词。在做NL 连接时,表A作为外部表,先被访问,由于连接机制原因,外部表的数据访问方式是全表扫描, A.ID上的索引显然是用不上
|
索引列是否在IN或者多 个OR语句中
|
如果索引列在IN或OR子句中,那么查询可能已经被转化为不能使用索引的语句
|
是否对索引列进行了函 数、算术运算或其他表达式 等操作
|
应尽量避免在WHERE子句中对索引字段进行函数、算术运算或其他表达式等操作,因为这样可 能会使索引失效,查询时要尽可能将操作移至等号右边
|
索引列是否出现了隐式 类型转换(Implicit Type Conversion)
|
如果进行比较的两个值的数据类型不同,那么Oracle必须将其中一个值进行类型转换使其 能够比较,这就是所谓的隐式类型转换。通常当开发人员将数字存储在字符列时会导致这种问 题的产生。Oracle在运行时会在索引字符列使用TO_NUMBER函数强制转化字符类型为数值 类型。由于添加函数到索引列,导致索引不被使用。实际上,Oracle也只能这么做,类型转换 是一个应用程序设计因素。由于转换是在每行都进行的,这会导致性能问题。一般情况下,当 比较不同数据类型的数据时,Oracle自动地从复杂向简单的数据类型转换。所以,字符类型的 字段值应该加上引号
|
是否在语义(semantically) 上无法使用索引
|
出于对查询整体成本的考虑,一个成本较低的执行计划中可能是无法使用索引的。某索引可能已 经被考虑在某种连接排序及方法中,但是成本最低的那个执行计划中却无法从“语义”角度使用该 索引
|
(续)
|
分类
|
原因
|
解释
|
索引本 身的问 题
|
错误类型的索引扫描
|
可以定义索引的排序顺序为递增或递减。Oracle对待降序索引就好像它是基于函数的索引,因此 与默认使用的升序的执行计划不同。通过查看执行计划是看不到使用升序或降序的,需要额外检查 视图DBA_IND_COLUMNS的DESCEND列。如果系统中经常使用索引范围扫描进行读取数据(例 如在WHERE子句中使用“BETWEEN AND”语句或比较运算符“>”“<”“>=”“<=”等),那么 反向键索引将不会被使用,此时Oracle会选择全表扫描。只有对反向键索引列进行“=”操作时, 其反向键索引才会使用
|
索引列是否可以为空
|
除了联合索引(即多列索引)和位图索引外,其他索引都不存储NULL值。只有至少有一个索引 列有值,联合索引才存储空值。联合索引中尾部的空值也会被存放在索引中。如果所有列的值都为 空,这行将不会存储在索引中。由于索引中缺乏NULL值,那么一些结果中可能会返回NULL值 (例如,COUNT)的操作可能会被禁用索引。这是因为优化器不能保证在单独使用索引时可以获得 准确的信息。位图索引允许存储空值。因此,无论它们的结果可信与否,优化器都会使用这些索引
|
NLS_SORT是否设置为 二进制(BINARY)
|
如果NLS_SORT未设置为二进制,索引将不会被使用。这是因为索引是基于Key值的二进制顺 序来建立的(pre-sorted使用二进制值)。无论优化器设置为何种方法,当NLS_SORT不是二进制时, 将使用全表扫描
|
是否使用的是不可见索 引(Invisible Indexes)
|
从Oracle 11gR1开始,可以创建不可见索引或将一个已经存在的索引标记为不可见。如果在 SESSION或SYSTEM级别将参数OPTIMIZER_USE_INVISIBLE_INDEXES设置为TRUE(默认为 FALSE,表示Oracle优化器不会考虑不可见索引),那么Oracle优化器就会考虑不可见索引。无论 该参数的值为TRUE还是FALSE,DML操作还是会维护这些不可见索引的
|
优化器 和成本 计算相 关问题
|
是否存在准确且合适的 统计信息(Statistics)
|
CBO依赖于准确的、最新的和完整的统计信息来确定一个特定查询的最佳执行计划。如果使用 CBO,需确保统计信息已经收集。如果没有统计信息,CBO将使用预定义的统计信息,这样很可 能不会产生良好的计划或让应用程序使用索引。请注意,CBO会根据开销(COST)来决定使用不 同的索引。除了基本的表和索引的信息之外,如果在某些列上数据分布是不均匀的,那么还需要收 集这些列的数据的分布情况,即直方图。一般情况下,对象的数据或结构的改变会使以前的统计信 息不准确,因此应该重新收集新的统计信息。例如,对表装载了大量的数据后,需要收集新的统计 信息。安装新补丁集(Patchset)后,也建议重新收集统计信息。表访问最佳效果是统计信息在相同 版本的数据库中生成的
|
一个索引是否与其他的 索引有相同的等级或者成本 (Cost)
|
对于相同开销(COST)的索引,CBO会使用多种办法将不同的索引区分开,如将索引名称按字 母顺序排序,完全匹配的索引扫描会选择更大的NDK(不同键值的个数)的索引(不适用于快速 全扫描)或选择叶块数量较少的索引
|
索引的选择度是否不高
|
CBO会假定列数据不会倾斜,并均匀分布。如果数据分布不是这种情况,那么统计信息可能没 有反映真实情况,即使某些值的选择度高,索引也会因为整个列的选择度不高而不使用索引。如果 是这种情况,那么应考虑采用直方图记录更准确的列的数据分布或者采用提示(Hint)
|
在总体成本中,表扫描的 成本是否占大部分
|
通常来说,当使用索引的时候,需要再次检索表本身来找到索引中不存在的字段的值(索引回表 读),这个操作比检索索引本身的开销要大很多。由于优化器是基于总体的成本来计算执行计划的, 如果通过索引检索表的成本很大,并且超过了某个阀值,优化器就会考虑其他的访问路径。优化器 (Optimizer)使用聚簇因子(Clustering Factor)来判断若使用索引的话需要对表做多少次访问,因 此当索引的聚簇因子很大的时候Oracle会选择全表扫描
|
访问空索引并不意味着 比访问有值的索引高效
|
重组、TRUNCATE或DELETE操作不一定会影响SQL语句执行的成本。需要注意的是,删除 操作并不会从对象中真正释放空间。也就是说,DELETE操作不会重置对象的高水位。TRUNCATE 操作会重黄高水位。空块的存在会使索引和表扫描的成本比实际应该的成本高
|
参数设置是否正确
|
某些参数的设置可能会影响索引的使用。比如在大多数情况下都建议使用 DB_FILE_MULTIBLOCK_READ_COUNT和OPTIMIZER_INDEX_COST_ADJ的默认值。除非某 些特定的操作有特定的建议,使用其他值会使索引的成本不现实地减少或变大,从而极大地降低查 询的性能
|
其他 问题
|
是否存在远程表(Remote Table)
|
通常远程表不会使用索引。索引在分布式查询中的使用依赖于被发送到远程的查询。CBO将评 估远程访问的成本,并评估比较发送或者不发送索引的谓词到远程站点的成本。因此,CBO可以做 出有关远程表上使用索引的更加明智的决定。一种非常有效的方法就是,在远程建立包含相关谓词 的视图并强制使用索引,之后在本地查询中使用这个视图
|
是否使用了并行执行 (PX)
|
在并行执行时索引的采用比在串行执行(Serial Execution)时更加严格。一种快速检测的方法就 是禁用并行,然后查看该索引是否被使用。并行查询将不会用到索引
|
是否包含了子查询的 UPDATE语句
|
在一些情况下,基于成本的考虑,索引没有被使用是因为它依赖于一个子查询返回的值。这种情 况下,可以使用提示(Hint)来强制使用索引
|
查询是否使用了绑定变 量
|
CBO对LIKE或范围谓词的绑定变量不能产生准确的成本,或绑定变量窥探操作都可能会导致索 引不被选择
|
查询是否引用了带有延 迟约束的列
|
如果一个表中的某一列上含有延迟约束(比如NOT NULL)并且这一列上有索引,那么不管这 个约束当前是延迟状态或者被显式地设置为立即使用,Oracle都不会考虑使用这一列上的索引
|
索引提示(Hint)是否不 工作
|
语法不正确、使用了RBO、Hint的对象名不正确、使用了非空索引列来计算行数等情况都可能 导致索引提示不工作
|
(续)
|
分类
|
原因
|
解释
|
其他 问题
|
索引列是否使用了前置 通配符(%)
|
在WHERE子句中,如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始,索引 将不被采用
|
索引列是否使用了非等 值连接符
|
应尽量避免在WHERE子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。可 以通过改写为IN或UNION ALL来使用索引
|
是否在WHERE子句中对索 引列进行了IS NULL值判断
|
应尽量避免在WHERE子句中对字段进行IS NULL值判断,否则将导致引擎放弃使用索引而进 行全表扫描。可以通过加伪列创建伪联合索引来使得IS NULL使用索引
|
查询转换失败,走不了 索引
|
查询转换是非常复杂的过程,Oracle CBO的查询转换有子查询展开、视图合并、星型转换、连 接谓词推入、表扩展等。如果查询转换失败,那么必将影响后续优化器的一些操作,比如JPPD中 JOIN谓词无法推入视图中,那么很可能视图就无法选择索引了。另外,查询转换有很多BUG,若 触发BUG则需要找到原因,比如设置隐含参数、fix control,或者改写SQL绕过BUG等
|
是否使用了视图或子查询
|
查询涉及视图或者子查询时可能会被改写,导致不使用索引(尽管该改写的目标之一是扩展更多 的访问路径)。这些改写(Rewrite)一般来说都是合并(Merging)操作
|
建议读者对每种类型的情况都做实验,以便加深理解和印象。
14. IS NULL如何用到索引?
IS NULL用于判断某一列中的值是否为空。当IS NULL作为WHERE条件的时候,该列是不会用到索引的,但是可以加伪列创建伪联合索引来使得IS NULL使用索引。
[考点] 索引
15. 模糊查询可以使用索引吗?
可以分为以下几种情况:
1)若SELECT子句只检索索引字段,那么模糊查询可以使用索引,例如,“SELECT ID FROM TB WHERE ID LIKE '%123%';”可以使用索引。
2)若SELECT子句不只检索索引字段还检索其他非索引字段,那么分为以下几种情况:
①模糊查询形如“WHERE COL_NAME LIKE 'ABC%';”可以用到索引。
②模糊查询形如“WHERE COL_NAME LIKE '%ABC';”不能使用索引,但是可以通过REVERSE函数来创建函数索引才能使用到索引。
③模糊查询形如“WHERE COL_NAME LIKE '%ABC%';”不能使用索引,但是,如果所查询的字符串有一定的规律,那么还是可以使用到索引的,分以下几种情况:
a.如果字符串ABC始终从原字符串的某个固定位置出现,那么可以创建SUBSTR函数索引进行优化。
b.如果字符串ABC始终从原字符串结尾的某个固定位置出现,那么可以创建函数组合索引进行优化。
c.如果字符串ABC在原字符串中位置不固定,那么可以通过改写SQL进行优化。改写的方法主要是通过先使用子查询查询出需要的字段,然后在外层嵌套,这样就可以使用到索引了。
④建全文索引后使用CONTAINS也可以用到域索引。
16. Hint是什么?
Oracle的Hint是用来提示Oracle的优化器,用来选择用户期望的执行计划。Oracle推出了一个隐含参数“_OPTIMIZER_IGNORE_HINTS”,其取值为TRUE或FALSE,默认值是FALSE。Oracle可以通过将该隐含参数设置为TRUE,使得Oracle优化器忽略语句中所有的Hint。显然,Oracle提供此参数的目的是在不修改应用的前提下,忽略所有Hint,让Oracle优化器自己来选择执行路径。
[考点] Hint
17. NOLOGGING是一种Hint吗?
下面几条SQL都是使用NOLOGGING时的错误用法:
INSERT INTO T1 NOLOGGING;
INSERT INTO T1 SELECT*FROM T2 NOLOGGING;
INSERT/*+NOLOGGING*/INTO T1 VALUES ('0');
INSERT/*+NOLOGGING*/INTO T1 SELECT*FROM T2;
DELETE/*+NOLOGGING*/FROM T1;
UPDATE/*+NOLOGGING*/T1 SET A='1';
实际上,上述所有的SQL没有一个能够实现“不产生”日志的数据更改操作。事实上,NOLOGGING并不是Oracle的一个有效的Hint,而是一个SQL关键字,通常用于DDL语句中。这里NOLOGGING相当于给SELECT的表指定了一个别名为“NOLOGGING”。下面是NOLOGGING的一些正确用法:
CREATE TABLE T1 NOLOGGING AS SELECT *FROM T2;
CREATE INDEX T1_IDX ON T1(A)NOLOGGING;
ALTER INDEX T1_IDX REDUILD ONLINE NOLOGGING;
ALTER TABLE T1 NOLOGGING;
若面试官问如何强制一个SQL语句使用索引,此时就可以回答使用Hint,/*+INDEX(TABLEINDEX_NAME)*/来完成。
[考点] Hint
18. Oracle Hint中的DRIVING_SITE的作用是什么?
日常工作中经常会用到分布式数据库查询,即通过DBLINK同时查询本地表和远程表。分布式查询一般有两种处理方式:一种将远程表数据取回本地,然后和本地表关联查询,获取最终结果;另一种将本地表数据传到远程和远程表关联查询后,再将关联结果取回。前一种处理方式可理解为只有一次网络传输操作,比后一种少,也就作为了数据库的默认处理方式。DRIVING_SITE提示能够指定执行计划在远程还是本地做,使用DRIVING_SITE,特别是本地小结果集,远程大结果集,最终结果集较小时,希望计划在远程驱动,这样远程执行完毕,将结果集传输到本地,避免了大结果集的网络传输,从而达到整体优化的效果。使用DRIVING_SITE可以减少总体的网络传输数据量。
当DRWING_SITE驱动的对象嵌套在视图中时,可通过DRIVING_SITE(V.T)方式来指定,其中,V表示视图别名或名称,T表示视图里表的别名或名称。
需要注意的是,对于DML和DDL语句,DRIVING_SITE提示是失效的,会自动被Oracle忽略掉,此时将以目标表所在库为主计划驱动,相当于DRIVING_SITE(目标表库),此时可以通过视图转换来达到优化的目的。DML和DDL中如果是对本地表做DML,主计划总是在本地执行,会将远程数据拉到本地,相当于DRIVING_SITE(本地表)。如果是对远程表做DML,那么主计划总是在远程执行,会将本地数据送到远程,相当于自动DRIVING_SITE(远程表)。
[考点] Hint
19. Oracle数据库中库缓存(Library Cache)的作用有哪些?
库缓存是SGA中共享池(Shared Pool)中的一块内存区域,主要作用就是缓存执行过的SQL语句和PL/SQL语句(例如存储过程、函数、包、触发器)及其所对应的解析树(Parse Tree)和执行计划等信息。当同样的SQL语句和PL/SQL语句再次被执行的时候就可以直接利用已经缓存在库缓存中的那些相关对象而无须再次从头开始解析,这样就提高了这些SQL语句和PL/SQL语句在重复执行时的执行效率。库缓存在SGA中的位置如下图所示。
[考点] 游标
20. 游标分为哪几类?父游标和子游标的区别是什么?
游标(Cursor)是Oracle数据库中SQL解析和执行的载体,它可以分为共享游标(Shared Cursor)和会话游标(Session Cursor)。共享游标是指缓存在库缓存(Library Cache)里的一种库缓存对象,其实就是指缓存在库缓存里的SQL语句和匿名PL/SQL块所对应的库缓存对象。共享游标是Oracle缓存在库缓存中的几十种库缓存对象之一,它所对应的库缓存对象句柄的Namespace属性的值是CRSR(也就是Cursor的缩写)。共享游标会存储目标SQL的SQL文本、解析树、该SQL所涉及的对象定义、该SQL所使用的绑定变量类型和长度,以及该SQL的执行计划等信息。共享游标可以细分为父游标(Parent Cursor)和子游标(Child Cursor),可以通过视图V$SQLAREA来查看当前缓存在库缓存(Library Cache)中的父游标,而通过V$SQL来查看缓存在库缓存中的子游标。Oracle设计这种嵌套的Parent Cursor和Child Cursor并存的结构是为了能尽量减少对应的Hash Bucket中库缓存对象句柄链表的长度。
Oracle中游标的分类如下图所示。
父游标和子游标的对比见下表。
|
父游标
|
子游标
|
查询视图
|
V$SQLAREA
|
V$SQL
|
存储内容
|
父游标存储SQL文本(库缓存对象句柄的属性 NAME中);父游标的heap 0中存储着子游标的句柄 地址
|
子游标存储解析树(Parse Tlree)及执行计划(Execution Plan,实际上存储在库缓存对象句柄的heap 6中),以及该 SQL所使用的绑定变量的类型和长度
|
库缓存对象句柄的属 性NAME存储内容
|
存储SQL文本
|
空
|
联系
|
1)父游标和子游标的结构是一样的,它们都是以库缓存对象句柄的方式缓存在厍缓存中,Namespace属性的值 均为CRSR 2)由于子游标所对应的库缓存对象句柄的NAME属性值为空,所以,只能通过父游标才能找到相应的子游标 3)任意一个经过解析的目标SQL一定会同时对应两个共享游标,一个是父游标,另一个则是子游标
|
[考点] 游标
21. 会话游标的含义是什么?共享游标和会话游标的区别有哪些?
会话游标(Session Cursor)是当前会话(Session)解析和执行SQL的载体,即会话游标用于在当前会话中解析和执行SQL,会话游标是以哈希表的方式缓存在PGA中(共享游标是缓存在SGA的库缓存里)。在目标SQL的执行过程中,会话游标起承上启下的作用。因为Oracle依靠会话游标来将目标SQL所涉及的数据从Buffer Cache的对应数据块读到PGA里,然后在PGA里做后续的排序、表连接等处理,最后将最终的处理结果返回给用户,所以,会话游标是当前会话解析和执行SQL的载体。
共享游标和会话游标的对比见下表。
|
共享游标
|
会话游标
|
缓存位置
|
缓存在SGA中的共享池里的库 缓存中
|
缓存在每个会话的PGA中
|
共享
|
共享游标在所有会话之间共享
|
会话游标与会话是一一对应的,不同会话的会话游标之间不能共享,这是与共享游标的 本质区别。
|
生命周期
|
共享游标无生命周期,会进行 缓存
|
会话游标是有生命周期的,每个会话游标在使用的过程中都至少会经历一次Open、 Parse、Bind、Execute、Fetch和Close中的一个或多个阶段。Oracle会根据参数SESSION_ CACHED_CURSORS的值来决定是否将已经用过的会话游标缓存在对应会话的PGA中
|
联系
|
1)会话游标足以哈希表的方式缓存在PGA中,意味着Oracle会通过棚关的哈希运算来存储和访问在当前会话的PGA中 的对应会话游标。这种访问机制和共享游标是一样的,可以简单地认为Oracle是根据目标SQL的SQL文本的哈希值去PGA 中的相应Hash Bucket中找匹配的会话游标。由于在缓存会话游标的哈希表的对应Hash Bucket中,Oracle会存储目标SQL对 应的父游标的库缓存对象句柄地址,所以,Oracle可以通过会话游标找到对应的父游标,进而就可以找到对应子游标中目标 SQL的解析树和执行计划,然后Oracle就可以重用目标SQL的解析树和执行计划来执行SQL语句了 2)一个会话游标只能对应一个共享游标,而一个共享游标却可以同时对应多个会话游标
|
[考点] 游标
22. 会话游标分为哪几类?
会话游标的详细分类参考下表。
在上表中需要注意的是,动态游标是Oracle数据库中最灵活的一种会话游标,它的灵活性表现在:①动态游标的定义方式非常灵活,它可以有多种定义方式;②动态游标可以作为存储过程的输入参数和函数的输出参数。上表中的各种游标希望读者可以通过做大量的练习题来掌握,毕竟游标是存储过程开发过程中必不可少的内容。
[考点] 游标
23. 会话游标有哪些属性?
会话游标有4个属性,见下表。
属性
|
类型
|
简 介
|
适用对象
|
适用SQL
|
SQL%FOUND
|
布尔型
|
最近的FETCH是否提取到数据,表示一条SQL语句被执行 成功后受其影响而改变的记录数是否大于或等于1,若是则赋值 为TRUE,否则为FALSE。在一条DML语句被执行前, SQL%FOUND的值是NULL
|
隐式游标、显式游标
|
INSERT、 DELETE、 UPDATE、 SELECT... INTO...
|
SQL%NOTFOUND
|
布尔型
|
最近的FETCH是否没有提取到数据,表示一条SQL语句被 执行成功后受其影响而改变的记录数是否为0,若是则赋值为 TRUE,否则为FALSE。在一条DML语句被执行前, SQL%NOTFOUND的值是NULL
|
隐式游标、显式游标
|
SQL%ROWCOUNT
|
数值型
|
表示最近的一条SQL语句成功执行后受其影响而改变的记 录的数最,后续执行的SQL会覆盖SQL%ROWCOUNT的值
|
隐式游标、显式游标
|
SQL%ISOPEN
|
布尔型
|
游标是否打开,当游标打开时返回TRUE。对于隐式游标而 言,SQL%ISOPEN的值永远是FALSE
|
显式游标
|
当执行一条DML语句后,DML语句的结果保存在这4个游标属性中,这些属性用于控制程序流程或者了解程序的状态。当运行DML语句时,PL/SQL打开一个内建游标并处理结果。在这些属性中,SQL%FOUND和SOL%NOTFOUND是布尔值,SQL%ROWCOUNT是整数值。需要注意的是,若游标属于隐式游标,则在PL/SOL中可以直接使用上表中的属性;若游标属于显式游标,则上表中的属性里“SQL%”需要替换为自定义显式游标的名称。上表中的这4个属性对于动态游标依然适用。
[考点] 游标
24. 硬解析、软解析、软软解析的区别是什么?
在Oracle中,每条SQL语句在正式执行之前都需要经过解析(Parse),根据解析的过程可以分为3种类型:硬解析(Hard Parse)、软解析(Soft Parse)和软软解析(Soft Soft Parse),软软解析也叫快速解析(Fast Parse)。DDL语句是从来不会共享使用的,也就是说,DDL语句每次执行都需要进行硬解析。但是,DML语句和SELECT语句会根据情况选择是进行硬解析,还是进行软解析或者进行软软解析。SQL的解析过程大致可以参考下图。
Oracle在解析和执行目标SQL时,会先去当前会话的PGA中查找是否存在匹配的缓存会话游标。当Oracle第一次解析和执行目标SQL时(显然是硬解析),当前会话的PGA中肯定不存在匹配的会话游标,这时Oracle会新生成一个会话游标和一对共享游标(包含一个父游标和一个子游标),这其中的共享游标会存储能被所有会话共享、重用的内容(比如目标s0L的解析树、执行计划等),而会话游标则会经历一次Open、Parse、Bind、Execute、Fetch和Close中的一个或多个阶段。
对会话游标和共享游标之间的关联关系如下总结:
1)无论是硬解析、软解析还是软软解析,Oracle在解析和执行目标SQL时,始终会先去当前会话的PGA中寻找是否存在匹配的缓存会话游标。
2)如果在当前会话的PGA中找不到匹配的缓存会话游标,那么Oracle就会去SGA的库缓存中查找是否存在匹配的父游标。如果在库缓存中找不到匹配的父游标,那么Oracle就会新生成一个会话游标和一对共享游标(即父游标和子游标);如果找到了匹配的父游标,但找不到匹配的子游标,那么Oracle就会新生成一个会话游标和一个子游标(这个子游标会被挂在之前找到的匹配父游标下)。无论哪一种情况,这两个过程对应的都是硬解析。
3)如果在当前会话的PGA中找不到匹配的缓存会话游标,但在库缓存中找到了匹配的父游标和子游标,那么Oracle会新生成一个会话游标并重用刚刚找到的匹配父游标和子游标,这个过程对应的就是软解析。
4)如果在当前会话的PGA中找到了匹配的缓存会话游标,那么此时Oracle就不再需要新生成一个会话游标,并且也不再需要像软解析那样去SGA的库缓存中查找匹配的父游标,因为Oracle此时可以重用找到的匹配会话游标,并且可以通过这个会话游标直接访问到该SQL对应的父游标,这个过程就是软软解析。
硬解析、软解析和软软解析的比对参考下表。
|
硬解析
|
软解析
|
软软解析
|
简介
|
硬解析是指Oracle在执行目标SQL时,在库缓 存中找不到可以重用的解析树和执行计划,而不得 不从头开始解析目标SQL并生成相应的父游标和 子游标的过程。硬解析实际上有两种类型:一种是 在库缓存中找不到匹配的父游标,此时Oracle会 从头开始解析目标SQL,新生成一个父游标和一 个子游标,并把它们挂在对应的Hash Bucket中: 另外一种是找到了匹配的父游标但未找到匹配的 子游标,此时Oracle也会从头开始解析该目标 SQL,新生成一个子游标,并把这个子游标挂在对 应的父游标下
|
软解析是指Oracle在执行目标 SQL时,在库缓存中找到了匹配的父 游标和子游标,并将存储在子游标中 的解析树和执行计划直接拿过来重 用而无须从头开始解析的过程
|
软软解析也叫快速解析(Fast Parse),是 指若参数SESSION_CACHED_CURSORS 的值大于0,并且该会话游标所对应的目标 SQL解析和执行的次数超过3次,则此时 该会话游标会被直接缓存在当前会话的 PGA中。若该SQL再次执行的时候,则只 需要对其进行语法分析、语义检查和权限 对象分析之后就可以直接从当前会话的 PGA中将之前缓存的匹配会话游标直接拿 过来用就可以了,这就是软软解析
|
优缺 点
|
缺点: 1)硬解析可能会导致Shared Pool Latch的争用 2)硬解析可能会导致库缓存相关Latch(如 Library Cache Latch)和Mutex的争用
|
和硬解析相比,软解析的优势: 1)软解析不会导致Shared Pool Latch的争用 2)软解析虽然也可能会导致库缓 存相关Latch(如Library Cache Latch) 和Mutex的争用,但软解析持有库缓 存相关Latch的次数要少,时间更短
|
和软解析相比,软软软解析的优势: 1)软软解析省去了OPEN一个新的会 话游标和CLOSE一个现有会话游标所需 要耗费的资源和时间 2)软软解析在持有库缓存相关Latch的 次数方面更少
|
绑定 变量 窥探
|
绑定变量窥探的动作只在硬解析时发生,在软 解析和软软解析时都不进行窥探
|
不窥探
|
不窥探
|
(续)
|
|
硬解析
|
软解析
|
软软解析
|
备注
|
1)创建解析树、生成执行计划对于SQL的执行 来说是开销昂贵的动作,所以,应当极力避免硬解 析。在项目开发中,开发设计人员对功能相同的代 码要努力保持代码的一致性,以及要在程序中多使 用绑定变量 2)对于OLTP类型的系统而言,硬解析是万恶 之源!如果有大量的并发硬解析导致Shared Pool Latch的争用,那么系统的性能和可扩展性是会受 到严重影响的(常常表现为CPU的占用率居高不 下,接近100%) 3)对于OLAP/DSS类型的系统而言,在一般情 况下,系统的并发量较少,目标SQL也很少被并 发重复执行,而且在执行目标SQL时硬解析所耗 费的时间和资源与该SQL总的执行时间和资源消 耗相比是微不足道的,这种情况下用硬解析是没问 题的,此时硬解析对系统性能的影响微乎其微,可 以忽略不计 4)Oracle在做硬解析时对Shared Pool Lacch和 Libraryr Cache Latch的持有过程:Oracle首先持有 Library Cache Latch,然后在不释放Library Cache Latch的情况下持有Shared Pool Latch,以便从 Shared Pool中申请分配内存,成功申请后就会释放 Shared Pool Latch,最后再释放Library Cache Latch
|
如果OLTP类型的系统在执行目标 SQL时能够广泛使用软解析,那么系 统的性能和可扩展性就会比全部使 用硬解析时有显著的提升,执行目标 SQL时需要消耗的系统资源(主要体 现在CPU上)也会显著降低。
|
当一个SQL语句以硬解析的方式解析和 执行完毕后,这个目标SQL所对应的共享 游标就已经被缓存在库缓存中,它所对应 的会话游标也已使用完毕,这时候会根据 参数SESSION_CACHED_CURSORS的不 同而存在如下这两种情况: 1)如果参数SESSION_CACHED_ CURSORS的值等于0,那么会话游标就会 正常执行Close操作。在这种情况下,当同 一条SQL再次执行时(显然是软解析), Oracle就必须为该SQL新生成一个会话游 标 2)如果参数SESSION_CACHED_ CURSORS的值大于0,并且该会话游标所 对应的目标SQL解析和执行的次数超过3 次,那么Oracle就不会对会话游标执行 Close操作,而是会将其标记为Soft Closed, 同时将其缓存在当前会话的PGA中。在这 种情况下,当目标SQL再次被执行时就是 所谓的软软解析
|
[考点] 游标
25. 什么是High Version Count?
一个父游标下对应的子游标个数被称为Version count,每个子游标对应一个执行计划。对于一个特定的游标有多少个版本(Version Count)属于高版本游标是没有明确定义的。对于不同的系统有不同的数量界定。High Version Count不仅产生的原因是多种多样的,而且会导致各种令人头痛的问题,轻则导致数据库的性能急剧下降,CPU利用率剧增,重则导致数据库挂起,触发ORA-04031或者其他BuG导致宕机。
在AWR报告中,Version Count大于20就会被报告出来,如下图所示。
SQL执行计划的生成,是受到很多因素影响的。相同父游标只是表示输入SQL的字面值相同。子游标对应的因素,如优化器类型、优化器的模式(OPTIMIZER_MODE)、对应对象权限等的差异,都会影响到子游标的共享。
在Oracle 11g中,V$SQL SHARED_CURSOR可以用来诊断子游标不共享问题的原因。该视图通过SOL_ID和CHILD_NUMBER就可以定义某个特定子游标的信息。该视图中大部分列都是以VARCHAR2(1)的Y/N取值,每列的含义都是一个不能共享的理由。需要注意的是,这个理由N表示的是不能与第一个子游标(CHILD_NUMBER=0)共享的理由。
[考点] 游标