Oracle DBA面试必知：SQL优化与索引策略

"Oracle DBA面试和笔试题集，涵盖了SQL优化、索引管理、查询分析、性能调整等多个核心知识点。" 在Oracle数据库管理(DBA)领域，掌握SQL优化是至关重要的技能。面试中，可能会遇到如下的问题： 1. 表连接方式：Oracle支持多种表连接方式，包括Merge Join（合并连接）、Hash Join（哈希连接）和Nested Loop（嵌套循环）。Merge Join通过比较两个数据源的排序键进行连接，适合于大数据量的高效连接；Hash Join将一个表的数据加载到内存中，与另一个表进行哈希匹配；Nested Loop则是一行一行地遍历一个表，对另一个表进行查找，适合小数据量或连接条件简单的场景。 2. 查看SQL执行计划：在SQL*Plus中，可以通过设置`SET AUTOTRACE`开启自动追踪，或者使用`UTL_XPLAN.SQL`创建并使用PLAN_TABLE来显示执行计划。这有助于理解SQL的执行过程和性能瓶颈。 3. CBO（Cost-Based Optimizer）与RULE（基于规则的优化器）的使用：CBO根据统计信息选择最优执行路径，而RBO则遵循固定的规则。在Oracle中，通过设置`optimizer_mode`参数可以选择优化器模式，CBO通常能提供更好的性能，因为它考虑了实际的成本和数据分布。 4. 定位资源消耗大的SQL：可以监控`V$SQLAREA`视图中的逻辑读和磁盘读，找出CPU使用高的会话，通过查询找出当前会话的SQL语句。此外，也可以通过监控工具在Windows环境下分析Oracle运行情况。 5. 跟踪特定session的SQL：首先确定session的`sid, serial#`，然后使用`DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION`系统包来启动SQL跟踪。 6. SQL调整的关注点：主要关注逻辑读，因为逻辑读直接影响I/O操作的数量，从而影响查询性能。 7. 索引的理解：索引通常采用B-Tree结构，对DML（插入、删除、更新）操作有影响。插入可能导致索引分裂，删除会标记节点为删除，更新索引字段会修改索引键值。索引加快了查询速度，但过多的索引可能影响写操作性能。 8. 索引查询的性能：并不总是能提高查询性能。如果查询返回大量行，全表扫描可能更快，因为避免了索引查找的额外开销。 9. 绑定变量：绑定变量是SQL语句中的占位符，用于减少因变量值变化而解析新语句的次数，提高性能。但过度使用绑定变量可能导致硬解析增加，影响性能。 10. 稳定执行计划：通过使用Stored Outline，可以固定执行计划，防止因数据变化导致的执行计划自动优化。 11. 排序内存调整与临时表空间：在Oracle 8i中，可以调整`SORT_AREA_SIZE`和`HASH_AREA_SIZE`，每个会话单独分配。而在9i中，使用`PGA_AGGREGATE_TARGET`统一管理排序内存。临时表空间用于存储排序、联接和创建临时表时所需的中间结果，当内存不足以处理这些操作时，会转到磁盘上的临时表空间。 了解并熟练掌握这些知识点，对于准备Oracle DBA的面试或笔试至关重要，同时也对日常数据库管理和性能优化有着深远的影响。