Oracle里头优化查询常用的那些语句都有哪些啊，能不能说详细点

首先得明白，优化查询就像是在一个巨大的图书馆里找书，如果你的方法不对，你可能要跑遍所有书架；但如果你会用图书索引、知道书的大致位置，就能很快找到，Oracle里的这些优化语句，就是帮你创建和使用“索引”的工具，让你能看清数据库这个“图书管理员”到底是怎么找数据的。

最基础也是最关键的一步，就是看数据库实际是怎么执行你的SQL语句的，这里就必须用到执行计划，执行计划是Oracle数据库为了一条SQL语句而制定的一套详细的执行步骤，比如先扫描哪个表，用哪种方式扫描，怎么连接表等等，看执行计划最常用的语句是 EXPLAIN PLAN。

怎么用呢？你不需要直接运行原SQL，而是把它包在 EXPLAIN PLAN 语句里,具体操作是这样的：

EXPLAIN PLAN FOR 你的SQL语句；

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;，这条命令执行后，执行计划并不会直接显示出来，而是被Oracle存储到了一个叫 PLAN_TABLE 的特定表中，你需要用查询语句把这个计划查出来看，最常用的是 DBMS_XPLAN.DISPLAY 这个Oracle自带的函数。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

执行完这条查询，你会看到一个表格形式的结果,这里面有几个关键列要重点看：

• Operation（操作）：表示这一步干什么，TABLE ACCESS FULL（全表扫描，通常需要避免）、INDEX RANGE SCAN（索引范围扫描，效率高）、NESTED LOOPS（嵌套循环连接）等。
• Name（名称）：操作的对象名,比如表名或索引名。
• Rows（预估行数）：Oracle预估这一步会返回多少行数据，这个预估的准确性非常重要，如果和实际情况差太多,说明数据库的统计信息可能过时了。
• Cost（成本）：Oracle根据内部算法计算出的这一步的代价（消耗多少CPU、I/O等），通常成本越低，执行速度理论上越快，比较不同写法或不同索引时,Cost是个重要参考。

光看预估的计划有时还不够，因为预估可能不准，这时候就需要看语句实际执行时的真实情况，Oracle提供了跟踪功能，能记录下SQL执行过程中所有的等待事件、物理读写次数等详细信息，常用的是用 DBMS_MONITOR 包开启会话跟踪：

ALTER SESSION SET SQL_TRACE = TRUE;

或者更精确地用： EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => YOUR_SID, serial_num => YOUR_SERIAL#, waits => TRUE, binds => TRUE);

开启跟踪后，你去运行那个需要优化的SQL语句，然后关闭跟踪，跟踪会产生一个trace文件，这个文件是二进制的，看不懂，需要用Oracle提供的 tkprof 工具把它转换成可读的文本格式。tkprof 转换后的文件会清晰列出SQL执行消耗的CPU时间、物理读次数、逻辑读次数、执行次数等信息。物理读（从硬盘读数据）次数多通常意味着性能瓶颈，因为硬盘速度远慢于内存，通过对比优化前后的trace文件,能非常直观地看到优化效果。

知道了数据库怎么执行的，接下来就要想办法让它执行得更快，创建合适的索引是最立竿见影的方法,比如在经常用于查询条件的字段上建索引：

CREATE INDEX idx_emp_dept ON employees(department_id);

或者在一个查询条件涉及多个字段时建组合索引： CREATE INDEX idx_emp_name_dept ON employees(last_name, department_id);

优化器可能因为信息不全而“犯傻”，选了一个不好的执行计划，这时候你可以用提示（Hints） 来直接告诉优化器应该怎么做，提示是写在SQL语句里的一种特殊注释，你明确想让优化器使用某个索引,可以这样写：

SELECT /*+ INDEX(employees idx_emp_dept) */ employee_id, last_name FROM employees WHERE department_id = 10;

常用的提示还有：

• /*+ FULL(表别名) */：强制全表扫描。
• /*+ ORDERED */：强制按FROM子句中表的顺序进行连接。
• /*+ USE_NL(表别名1 表别名2) */：强制使用嵌套循环连接方式。

但是提示要慎用，因为数据是变化的,今天高效的提示可能明天就变成瓶颈了。

数据库优化器之所以能生成执行计划，依赖于它对表、索引等对象的数据分布情况的了解，这些信息就是统计信息，如果一张表刚被灌入大量数据，或者数据被删改了很多，但统计信息没有及时更新，优化器就会基于过时的信息做出错误判断，定期收集统计信息至关重要，DBA通常会使用 DBMS_STATS 包来收集：

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'EMPLOYEES', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE);

这条命令会收集SCOTT用户下EMPLOYEES表的统计信息，并同时收集其上索引的统计信息（cascade => TRUE）。estimate_percent 设置为自动采样,让Oracle自己决定采样多少数据来保证统计信息的准确性和效率。

除了这些，还有一些辅助的查询语句能帮你找到需要优化的目标，查询消耗资源最多的SQL：

SELECT sql_text, cpu_time, elapsed_time, disk_reads, buffer_gets FROM v$sqlarea ORDER BY cpu_time DESC;

视图 v$sqlarea 存储了所有执行过的SQL的统计信息，通过按CPU时间、耗费时间、磁盘读取次数等排序，你能快速定位到系统里最“耗油”的语句,从而优先优化它们。

检查缺失索引的情况也很重要，Oracle会在动态性能视图 v$sql_plan 中记录下那些执行计划中出现了“全表扫描”（TABLE ACCESS FULL）且代价较高的SQL语句，通过查询这个视图，可以分析出如果为某个字段增加索引，可能会带来多大的性能提升，这为创建新索引提供了直接依据，虽然有一个通用的思路是查询那些扫描了大量数据块但返回行数不多的SQL,这通常意味着索引缺失。

有时候查询慢不是因为单次执行慢，而是因为被锁住了，在等待其他事务释放锁,可以查询锁信息：

SELECT sid, serial#, username, blocker_sid, final_blocking_session FROM v$session WHERE blocking_session IS NOT NULL OR final_blocking_session IS NOT NULL;

这个查询能帮你找到正在阻塞其他会话的“罪魁祸首”,有时候解决锁问题比优化SQL本身见效更快。

Oracle查询优化是一个循环的过程：先通过执行计划或资源视图找到慢的SQL，然后分析其执行计划，判断问题是全表扫描、错误的连接方式还是统计信息过时，接着通过创建索引、使用提示或更新统计信息等手段进行干预，最后再次查看执行计划和实际性能来验证优化效果,这些语句就是贯穿这个过程的得力工具。