Oracle面试题及详细答案（实战版）

Oracle面试题及详细答案（实战版）一、Oracle基础概念类1、简述Oracle数据库的体系结构Oracle核心体系结构主要分为三大部分：数据库文件、实例、内存与进程，是日常运维和开发的基础核心。1）数据库文件：物理存储层面，包含数据文件（存储真实业务数据）、控制文件（记录数据库结构、文件位置、日志信息）、重做日志文件（记录所有数据修改操作，用于故障恢复）、参数文件、归档日志文件。2）数据库实例：Oracle启动后常驻内存的服务，由内存结构和后台进程组成，一个实例对应一套内存和进程，用于挂载、操作数据库文件。3）内存结构：主要包含SGA（系统全局区，共享内存，所有进程共享，包含数据缓冲区、日志缓冲区、共享池等）和PGA（程序全局区，专属每个用户进程，私有内存）。4）后台核心进程：DBWR（数据写入进程，将缓冲区数据写入数据文件）、LGWR（日志写入进程，实时写入重做日志）、CKPT（检查点进程，同步数据和日志）、SMON（系统监控，负责实例重启、碎片整理）、PMON（进程监控，清理异常断开的用户进程）。2、SGA和PGA的区别是什么？1）归属不同：SGA是共享内存区，数据库启动时分配，所有用户进程、后台进程共享；PGA是私有内存区，用户连接数据库时创建，仅当前会话独享。2）存储内容不同：SGA主要存放缓存数据、执行过的SQL语句、PL/SQL程序、日志缓存、数据字典等公共资源；PGA主要存放当前会话的排序数据、游标状态、栈空间、变量信息等私有数据。3）生命周期不同：SGA随数据库实例启动创建、实例关闭释放；PGA随用户会话创建、会话断开立即释放。4）调整方式不同：SGA通过sga_target、sga_max_size参数全局调整；PGA通过pga_aggregate_target统一管控，单会话自动分配。3、Oracle中表空间的作用及分类表空间是Oracle逻辑存储单元，是数据文件的容器，所有表、索引、视图等对象都存放在表空间中，核心作用是统一管理物理文件、方便数据迁移、权限管控和存储扩容。常见分类：1）系统表空间：SYSTEM、SYSAUX，存放系统数据、数据字典、系统视图，数据库核心空间，不可离线。2）临时表空间：TEMP，用于存放排序、分组、连接查询产生的临时数据，会话结束后数据自动清空，不持久化。3）回滚表空间：UNDOTBS，存放数据修改前的镜像数据，保障事务回滚、读一致性、闪回查询。4）用户自定义表空间：业务专用表空间，用于存放项目业务数据，可独立扩容、迁移、备份。二、SQL查询与语法类（高频）1、UNION和UNIONALL的区别1）数据处理：UNION会自动去重，合并结果集后删除重复数据；UNIONALL直接拼接所有结果集，不去重。2）执行效率：UNION需要排序、比对去重，性能较低；UNIONALL无排序去重操作，性能远高于UNION，是开发首选。3）适用场景：业务明确无重复数据时，一律用UNIONALL；需要自动去重的场景使用UNION。2、IN和EXISTS的区别及使用场景核心差异在于驱动表选择，直接决定查询性能：1）IN：先执行子查询，拿到所有结果集后，再遍历外表匹配数据。子查询结果集小、外表数据量大时效率高。若子查询返回上万条数据，会严重拖慢速度。2）EXISTS：先遍历外表，逐条匹配子查询是否存在数据，只判断真假、不返回具体值。外表数据量小、子查询表数据量大时效率极高。补充：NOTEXISTS性能优于NOTIN，NOTIN在子查询存在NULL值时，会直接查询不到结果，存在逻辑坑。3、Oracle中分页查询的实现方式Oracle无limit关键字，通用分页采用rownum伪列+嵌套查询，适配所有版本：基础分页语法（查询第10-20条数据）：SELECT*FROM(

SELECTt.*,ROWNUMrnFROM业务表tWHEREROWNUM<=20

)WHERErn>10;注意事项：rownum是查询结果生成后生成的伪列，不能直接使用rownum>10查询，必须嵌套分层筛选；排序分页必须在内层排序，否则分页数据错乱。4、DISTINCT的使用注意事项1）DISTINCT是对整行所有字段去重，而非单个字段，多字段去重时需注意业务逻辑。2）执行过程会触发全局排序，大数据量表使用会严重影响性能，尽量避免大结果集去重。3）可通过GROUPBY替代DISTINCT，多数场景下GROUPBY性能更优。三、索引核心面试题1、Oracle常用索引类型及适用场景1）B树索引：默认索引类型，均衡树结构，适用于等值查询、范围查询（><、between），日常业务90%场景均使用B树索引。2）唯一索引：限制字段值唯一，可空，用于手机号、身份证、订单号等唯一字段，自动约束重复数据。3）复合索引：多个字段组合索引，遵循最左前缀原则，查询条件匹配左侧字段时索引生效。4）位图索引：适合字段基数低（重复值多）的字段，如性别、状态、年份，适合统计、批量查询，不适合频繁更新的字段。5）函数索引：针对查询中使用函数的字段，如upper(name)、substr(code,1,6)，避免查询时索引失效。2、索引失效的常见场景（重点）1）违反最左前缀原则：复合索引查询时，未使用最左侧字段作为条件。2）索引字段参与运算、使用函数：如wheresubstr(name,1,2)='张'、whereage+1>20。3）隐式类型转换：字段是字符类型，查询传入数字（如wherephone=1380000），自动触发类型转换导致索引失效。4）使用!=、<>、notin、isnotnull：大部分场景下索引失效，全表扫描。5）or连接非索引字段：两个条件用or拼接，其中一个字段无索引，整体索引失效。6）数据表数据量小、索引碎片过多、统计信息过时，优化器会自动选择全表扫描。3、索引一定能提升查询速度吗？为什么？不一定，索引仅针对少量数据查询生效。1）查询结果为表中大部分数据（超过20%）时，走索引需要二次回表查询数据，开销大于全表扫描，优化器会放弃索引。2）频繁增删改的表，索引会大幅降低写入性能，因为数据变更时需要同步更新索引结构。3）小表无需建索引，全表扫描速度更快，索引会产生额外存储和查询开销。四、事务与锁机制1、Oracle事务四大特性（ACID）1）原子性：事务要么全部执行成功，要么全部回滚，不存在中间状态。2）一致性：事务执行前后，数据库数据完整性、约束规则保持一致，不会出现脏数据。3）隔离性：多个事务并发执行时，相互独立、互不干扰，通过隔离级别控制并发问题。4）持久性：事务提交后，数据永久写入数据库，即使服务器宕机、重启，数据也不会丢失。2、Oracle的事务隔离级别Oracle仅支持两种标准隔离级别，区别于MySQL：1）读已提交（默认）：事务只能读取其他事务已提交的数据，避免脏读，会出现不可重复读、幻读，是生产最常用级别。2）串行化：最高隔离级别，事务串行执行，完全避免脏读、不可重复读、幻读，并发性能极低，仅用于金融、核心对账场景。补充：Oracle通过undo回滚段实现读一致性，查询不会阻塞写，写不会阻塞读，并发性能优秀。3、常见锁类型及死锁产生原因和解决方式1）常用锁：行级锁（DML操作，精准锁定单行，并发高）、表级锁（锁定整张表，并发低，多用于批量操作、结构修改）。2）死锁原因：两个及以上事务，互相持有对方需要的锁，且互相等待释放，形成循环等待，程序卡死。典型场景：事务A锁行1、等行2；事务B锁行2、等行1。3）解决方式：Oracle会自动检测死锁，几秒内主动终止其中一个事务并报错；开发层面统一SQL执行顺序、缩短事务执行时间、避免长事务，从根源规避死锁。五、数据库优化类（面试核心）1、SQL优化的通用思路1）先定位慢SQL：通过执行计划、慢查询日志、AWR报告定位低效语句。2）优化查询逻辑：避免SELECT*、避免大表嵌套查询、优先用UNIONALL替代UNION、EXISTS替代IN。3）索引优化：合理建立复合索引、函数索引，清理失效索引、定期重建碎片索引。4）规避索引失效场景：杜绝字段运算、隐式转换、不合理的查询条件。5）分页、分组优化：大表分页用延迟查询，GROUPBY字段建立索引，减少排序开销。6）事务优化：拆分长事务，减少锁持有时间，提升并发能力。2、如何查看SQL执行计划？执行计划重点看什么？查看方式：PL/SQL、Navicat直接点击执行计划按钮，或执行explainplanfor+SQL语句。重点关注指标：1）访问方式：优先看是否走索引（INDEXUNIQUESCAN、INDEXRANGESCAN），避免FULLTABLESCAN全表扫描。2）执行耗时、成本、返回行数，判断SQL开销。3）表连接方式：嵌套循环、哈希连接、排序合并连接，根据数据量判断是否合理。4）是否存在大量排序、临时表操作，此类操作极易拖慢SQL性能。3、Oracle大表优化方案1）索引优化：精准建立索引，清理冗余索引，定期重建索引碎片。2）分区表优化：按时间、月份、地区做分区，查询仅扫描对应分区，减少扫描数据量。3）数据归档：历史冷数据迁移至归档表，减少业务表数据量。4）SQL优化：禁止全表查询、批量查询分页、避免大结果集排序。5）统计信息更新：定期收集大表统计信息，保证优化器生成正确执行计划。六、运维与故障基础题1、重做日志和归档日志的区别1）重做日志（redolog）：实时记录所有数据修改，循环覆盖写入，用于实例宕机后的崩溃恢复，保证事务持久性。2）归档日志（archivelog）：重做日志写满后，备份生成的日志文件，永久保存，用于数据异地恢复、时间点恢复、数据同步。生产环境必须开启归档模式，否则无法实现完整数据备份恢复。2、数据库启动的三个阶段1）nomount阶段：读取参数文件，启动实例、分配内存、启动后台进程，未关联数据库文件。2）mount阶段：读取控制文件，关联数据库物理文件，确认文件状态，可进行备份、日志维护操作