Oracle高频面试题及详细实战答案（接地气版）

Oracle高频面试题及详细实战答案（接地气版）一、Oracle基础核心面试题1、简述Oracle数据库的体系结构Oracle核心体系主要分三大块，分别是数据库实例、数据库文件、后台进程，日常运维和开发接触最多的就是这几部分：第一是实例（Instance），也就是内存+后台进程，是Oracle运行的核心，数据库启动本质就是启动实例。内存主要包含SGA和PGA，SGA是共享内存，所有用户进程共享，包含数据缓冲区、日志缓冲区、共享池等；PGA是私有内存，每个客户端连接单独分配，存放会话私有数据。第二是数据库文件，是物理存储载体，核心三类：数据文件（存真实表数据、索引数据）、控制文件（记录数据库结构、文件位置、日志信息，极其重要，一般多路复用）、重做日志文件（记录所有数据修改操作，用于故障恢复）。除此之外还有参数文件、密码文件、归档日志文件等辅助文件。第三是后台进程，常驻后台运行，保障数据库正常工作，核心有DBWR（写数据到数据文件）、LGWR（写重做日志）、CKPT（检查点进程，同步数据和日志）、SMON（系统监控，实例崩溃后自动恢复、整理碎片）、PMON（进程监控，清理异常断开的会话、释放资源）。2、SGA和PGA的区别，分别包含哪些核心组件SGA（系统全局区）：数据库启动时分配的共享内存区域，所有会话、进程共用，数据库关闭后释放。核心组件：数据缓冲区（DatabaseBufferCache）：缓存从磁盘读取的数据，减少磁盘IO，提升查询效率，是读写最频繁的区域。共享池（SharedPool）：存放SQL语句、PL/SQL代码的解析结果、执行计划、数据字典信息，日常SQL硬解析、软解析都依赖这里。重做日志缓冲区（RedoLogBuffer）：缓存用户的增删改操作日志，由LGWR进程定时写入磁盘重做日志文件。大池、Java池：用于备份恢复、并行查询、Java程序调用等场景。PGA（程序全局区）：每个数据库连接会话独立私有内存，会话创建时分配，会话断开立即释放，会话之间互不共享。核心作用：存放会话的排序数据、游标信息、连接变量、临时运算数据，我们写SQL用到的orderby、groupby排序，基本都是在PGA中完成。3、Oracle数据库启动和关闭的完整流程启动三步流程（循序渐进）第一步：启动实例（nomount阶段）。读取参数文件，初始化SGA内存、启动后台进程，此时只开启实例，不加载任何数据库文件，无数据访问能力，一般用于修改参数、重建控制文件。第二步：加载数据库（mount阶段）。实例读取控制文件，获取数据文件、日志文件的位置和状态，校验文件完整性，此时数据库关闭普通访问，仅管理员可操作，常用于备份恢复、修改归档模式。第三步：打开数据库（open阶段）。校验所有数据文件、日志文件状态正常，打开所有文件，数据库对外提供正常读写服务，用户可正常连接操作数据。关闭四种模式shutdownnormal：正常关闭，等待所有会话主动断开，禁止新连接，耗时最长，一般不用。shutdowntransactional：等待所有正在执行的事务提交或回滚，断开会话，关闭数据库，兼顾安全和效率。shutdownimmediate：最常用，立即终止所有会话、未完成事务强制回滚，关闭数据库，安全无数据丢失，日常维护首选。shutdownabort：强制宕机关闭，相当于断电，不回滚事务、不刷新内存数据，重启后需要SMON进程自动实例恢复，非紧急故障不推荐使用。二、SQL查询与函数高频面试题1、union和unionall的区别，工作中如何选择核心区别第一、去重差异：union会自动去除两个结果集中的重复数据，同时默认排序；unionall直接拼接两个结果集，不去重、不排序。第二、性能差异：union需要做去重和排序运算，消耗CPU和内存，性能差；unionall无额外运算，性能远高于union。第三、结果顺序：union结果有序，unionall结果为原始查询拼接顺序。使用选择原则业务明确需要去重，数据量小的场景用union；绝大多数生产场景，确认数据无重复、或重复数据不影响业务，优先用unionall，保证查询性能。2、where、groupby、having、orderby的执行顺序真实执行顺序（从先到后）：where筛选数据→groupby分组→聚合函数计算→having筛选分组结果→orderby排序核心区别和使用规则where是分组前筛选，过滤单条数据，不能使用聚合函数（sum、count、max等）；having是分组后筛选，过滤分组后的整体结果，专门用于聚合函数条件判断。举个实战例子：统计部门员工人数大于3的部门，排除薪资低于5000的员工SELECTdept_id,COUNT(*)FROMempWHEREsal>5000GROUPBYdept_idHAVINGCOUNT(*)>3ORDERBYCOUNT(*)DESC;3、decode和casewhen的区别，各自适用场景两者都是条件判断函数，用于SQL中实现分支逻辑。decode：Oracle独有函数，语法简洁，只支持等值判断，不支持区间、大于小于、多条件复杂判断。适合简单的一对一值匹配，比如性别转换、状态码翻译。示例：decode(sex,'1','男','2','女','未知')casewhen：标准SQL语法，兼容性强，支持等值、区间、多条件、逻辑运算，灵活度极高。适合复杂分支判断、范围匹配、多条件嵌套。工作选择：简单等值翻译用decode，代码简洁；复杂条件判断一律用casewhen。4、Oracle分页查询怎么写，优缺点对比Oracle没有limit关键字，分页核心依靠rownum伪列，分两种常用写法：第一种：基础分页（适合小数据量）SELECT*FROM(SELECTt.*,ROWNUMrnFROMemptWHEREROWNUM<=20)WHERErn>10;原理：内层查询取前N条数据，外层过滤起始行数，实现11-20条分页。缺点：大数据量深分页（比如查1000页后）效率极低，需要扫描大量数据。第二种：排序分页优化版（生产常用）SELECT*FROM(SELECTt.*,ROWNUMrnFROM(SELECT*FROMempORDERBYid)tWHEREROWNUM<=20)WHERErn>10;所有分页必须内层先排序，否则rownum取值无序，分页数据会错乱。12C新特性：支持fetchfirst分页，语法更简洁：SELECT*FROMempORDERBYidOFFSET10ROWSFETCHNEXT10ROWSONLY;三、索引核心面试题（重点高频）1、Oracle常用索引类型及适用场景B树索引（默认索引）：最常用，平衡二叉树结构，适合等值查询、范围查询（><、between）、排序分组。主键、唯一键默认创建B树索引，日常业务字段优先使用。位图索引：适合字段基数低、重复值多的字段，比如性别、状态、月份。查询效率高，但增删改性能极差，锁粒度大，只适用于静态数据、数据仓库场景，业务表禁止使用。函数索引：针对字段使用函数查询的场景，比如whereupper(name)='TEST'，普通索引失效，创建函数索引可优化查询。复合索引：多个字段联合创建索引，遵循最左前缀原则，查询条件匹配左侧字段才能走索引。2、索引失效的常见场景（面试必考）1、对索引字段使用函数、运算，比如wheresubstr(name,1,1)='张'、whereage+10>30，索引失效。2、隐式类型转换，字段是字符串类型，查询传数字，比如wherephone=1380000，索引失效。3、使用!=、<>、notin、isnotnull，B树索引失效（B树索引不存储空值）。4、or左右字段有非索引字段，无联合索引的情况下，索引失效。5、复合索引不满足最左前缀原则，比如索引(a,b,c)，查询条件只写b=?、c=?，不走索引。6、数据库统计信息过旧，优化器误判，选择全表扫描而非索引。7、查询数据量超过表数据30%左右，优化器认为全表扫描比索引更快，主动放弃索引。3、复合索引最左前缀原理，如何设计索引原理：复合索引是按照字段顺序依次排序存储的，索引(a,b,c)，数据先按a排序，a相同再按b排序，b相同再按c排序。只有前置字段作为查询条件，才能精准定位索引区间，跳过前置字段无法匹配索引结构。索引设计原则等值条件字段放前面，范围条件放后面；高频查询字段优先建索引；区分度高的字段优先；避免过度建索引，索引会降低增删改性能。4、索引一定能提升查询速度吗？什么时候不适合建索引不一定，索引只优化查询，会降低增删改性能，因为数据修改时，索引数据需要同步更新。不适合建索引的场景：表数据量极小，全表扫描速度已经最快，索引无意义；字段重复值极高、区分度极低，比如状态字段只有0/1两种值；表增删改极其频繁，查询极少，索引维护成本远大于查询收益；字段频繁为空，大量空值数据，B树索引存储效率极低。四、事务、锁机制面试题1、Oracle事务四大特性ACID详解原子性（Atomicity）：事务不可分割，要么全部执行成功提交，要么全部失败回滚，不会出现部分执行的情况。一致性（Consistency）：事务执行前后，数据库数据完整性、约束规则保持一致，不会出现数据错乱、约束失效。比如转账事务，总金额保持不变。隔离性（Isolation）：多个事务并发执行时，相互独立互不干扰，每个事务都感知不到其他事务的执行细节。持久性（Durability）：事务一旦提交成功，数据永久写入磁盘，即使数据库宕机、重启，数据也不会丢失。2、Oracle事务隔离级别，默认级别是什么Oracle只支持两种标准隔离级别，外加一个只读级别：1、读已提交（ReadCommitted）：Oracle默认隔离级别。事务只能读取其他事务已经提交的数据，看不到未提交的脏数据，会出现不可重复读、幻读问题。2、可重复读（Serializable）：最高隔离级别，事务开启后，读取的数据全程不变，杜绝脏读、不可重复读、幻读，但并发性能极差，极少使用。补充：Oracle不支持读未提交级别，保证不会读取脏数据。3、Oracle常见锁类型，行锁和表锁的区别行锁（行级锁）：DML操作（insert、update、delete）默认触发，只锁定修改的单行数据，其他行数据可正常读写，锁粒度最小，并发性能极高。行锁不会阻塞其他行的操作，只阻塞当前行的修改操作。表锁（表级锁）：锁定整张数据表，所有会话无法修改表中任何数据，并发度极低。一般手动添加，或DDL操作、异常场景触发。常见表锁有共享锁、排他锁。死锁产生条件：两个事务互相持有对方需要的行锁，同时互相等待对方释放锁，循环等待导致卡死。Oracle会自动检测死锁，主动终止其中一个事务，抛出死锁异常。死锁规避方案：统一业务数据操作顺序、缩短事务执行时间、避免长事务。五、数据库调优高频面试题1、SQL执行缓慢的排查步骤1、查看执行计划：explainplanfor查看SQL执行计划，判断是否全表扫描、索引失效、排序过度、表连接不合理。2、检查索引：确认查询字段、条件字段是否建索引，是否存在索引失效场景。3、分析SQL语句：是否存在冗余关联、无效排序、重复查询、大量聚合运算。4、查看数据库状态：是否存在锁等待、会话阻塞、SGA内存不足、PGA排序溢出磁盘。5、更新统计信息：老旧统计信息会导致优化器选错执行计划，执行execdbms_stats.gather_table_stats更新表统计信息。2、硬解析和软解析的区别，如何减少硬解析软解析：SQL语句之前执行过，共享池中存在相同的SQL文本和执行计划，直接复用执行计划，无需重新解析、生成计划，速度快、消耗资源低。硬解析：SQL首次执行、或语句不一致、统计信息更新，需要重新进行语法解析、语义校验、生成最优执行计划，消耗大量CPU和内存，是数据库性能瓶颈主要原因。减少硬解析方案业务使用绑定变量，避免SQL文本微小差异导致无法复用；统一SQL编写规范，避免大小写、空格差异；合理设置共享池大小，避免执行计划被频繁刷新；减少临时动态SQL的使用。3、大表优化常用方案1、分区表：超大表按时间、地区、类型做分区，查询只扫描对应分区，减少扫描数据量，同时方便分区清理、归档数据。常用范围分区、列表分区。2、索引优化：精简无效索引，优化复合索引顺序，创建分区索引，避免全局索引失效。3、SQL优化：避免select*、避免全表扫描、优化分页、减少大表关联。4、数据归档：历史冷数据迁移至归档表，减少业务表数据量。5、并行查询：超大表统计、查询场景开启并行度，提升执行速度。六、分区表、备份恢复面试题1、常用分区表类型及适用业务场景范围分区：最常用，按数值、时间范围分区，比如按月份、年份分区日志表、交易记录表，适合时间维度业务数据。列表分区：按固定离散值分区，比如按省份、业务类型、状态分区，适合分类固定的业务数据。哈希分区：通过哈希算法均匀分配数据，打散大表数据，均衡IO压力，适合无规则、无明显分区字段的超大表。复合分区：两种分区结合，比如范围+列表，适配复杂业务场景。2、Oracle常用备份方式区别（冷备、热备、RMAN）冷备份：数据库关闭状态下，直接拷贝数据文件、控制文件、日志文件。操作简单，无日志损耗，但需要停机，无法实现数据实时备份，适合测试库、低频业务库。热备份：数据库正常运行、对外服务状态下备份，无需停机，依赖归档日志，可实现数据一致性备份，适合生产核心库。RMAN备份（生产