#$1、Row Source Generation（行源生成）
优化器在确定执行计划后，生成执行代码的阶段。这个代码（行源树）告诉数据库引擎如何实际执行查询,过程如下：
SQL 文本------解析 (Parsing) → 语法/语义检查------优化 (Optimization) → 选择最佳执行计划------**行源生成 (Row Source Generation)** → 生成执行代码----执行 (Execution) → 实际执行SQL，以下列SQL为例：
SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptnoAND e.sal > 5000;
产生执行计划
SELECT STATEMENT||-- HASH JOIN| || |-- TABLE ACCESS FULL (EMP) → 行源#1| || |-- TABLE ACCESS FULL (DEPT) → 行源#2||-- FILTER (sal > 5000) → 行源#3
行源代码参考如下：
// 伪代码表示 class RowSource {open();fetch(row); // 获取下一行close(); }class HashJoin : RowSource {open() {build_hash_table(left_child);}fetch(row) {probe_hash_table(right_child);return joined_row;} }
访问方法
-- TABLE ACCESS FULL -- TABLE ACCESS BY INDEX ROWID -- INDEX FULL SCAN -- INDEX RANGE SCAN -- INDEX UNIQUE SCAN-- 示例：不同的访问方法生成不同的行源 EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno = 7900; -- 可能生成: TABLE ACCESS BY INDEX ROWID (索引行号访问)SELECT * FROM emp WHERE sal > 3000; -- 可能生成: TABLE ACCESS FULL (全表扫描)
连接方法
-- NESTED LOOPS -- HASH JOIN -- MERGE JOIN -- CARTESIAN JOIN-- 查看执行计划中的连接方法 SELECT /*+ USE_HASH(e d) */ e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
监控源产生
-- 使用 DBMS_XPLAN EXPLAIN PLAN FOR SELECT * FROM emp WHERE deptno = 10;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);-- 输出示例： --------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 114 | 3| |* 1 | TABLE ACCESS FULL | EMP | 3 | 114 | 3| --------------------------------------------------------- -- 每个 Operation 对应一个行源
实际执行统计
-- 启用行源执行统计 ALTER SESSION SET statistics_level = ALL;-- 执行查询 SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE e.sal > 3000;-- 查看详细的执行统计 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST' ));
行源产生执行统计
-- 关键统计信息 SELECT operation, -- 操作类型（行源类型）options, -- 选项（如 FULL, BY INDEX ROWID）object_name, -- 操作对象cardinality, -- 预估行数bytes, -- 预估字节数cost, -- 预估成本cpu_cost, -- CPU成本io_cost, -- IO成本time -- 预估时间 FROM v$sql_plan WHERE sql_id = 'your_sql_id' ORDER BY id;
行源高级特性
-- 自适应连接方法 SELECT /*+ ADAPTIVE */ e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;-- 查看自适应决策 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADAPTIVE' ));
识别问题行源
-- 查找执行时间最长的行源 WITH sql_plan_stats AS (SELECT sql_id,plan_hash_value,id,operation || ' ' || options as operation,object_name,cardinality,last_output_rows as actual_rows,last_elapsed_time as elapsed_timeFROM v$sql_plan_monitorWHERE last_elapsed_time > 1000000 -- 超过1秒 ) SELECT * FROM sql_plan_stats ORDER BY elapsed_time DESC;
生成行源等待事件
-- 常见的行源执行等待 SELECT event, total_waits, time_waited FROM v$system_event WHERE event IN ('db file sequential read', -- 索引读取等待'db file scattered read', -- 全表扫描等待'direct path read', -- 直接路径读取'PX qref latch', -- 并行查询等待'row cache lock' -- 字典缓存等待 ) ORDER BY time_waited DESC;
监控行源统计信息
-- 定期收集统计信息 EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'EMP',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE );-- 检查统计信息质量 SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name = 'EMP';
#$1、执行计划（从下往上看）
计划示例： --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost | Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 45 | 00:00:01 | | 1 | SORT ORDER BY | | 100 | 45 | 00:00:01 | | 2 | HASH GROUP BY | | 100 | 44 | 00:00:01 | | 3 | HASH JOIN | | 500 | 30 | 00:00:01 | | 4 | TABLE ACCESS FULL | EMPLOYEES | 1000 | 15 | 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| DEPTS | 100 | 2 | 00:00:01 | | 6 | INDEX RANGE SCAN | DEPT_IDX | 100 | 1 | 00:00:01 | --------------------------------------------------------------------------------- 阅读顺序：6 → 5 → 4 → 3 → 2 → 1 → 0
统计信息收集
-- 收集表统计信息 EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HR',tabname => 'EMPLOYEES',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => TRUE -- 收集索引统计 );-- 收集列直方图（数据倾斜时） EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HR',tabname => 'SALES',method_opt => 'FOR COLUMNS SIZE 254 customer_id, product_id' );-- 锁定统计信息（防止变化） EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');
SQL实时监控
-- 监控长时间运行的SQL SET LINES 200 PAGES 1000 COL sql_text FOR a60 COL status FOR a15SELECT sql_id,status,sql_text,elapsed_time/1000000 as elapsed_sec,cpu_time/1000000 as cpu_sec,buffer_gets,disk_reads,rows_processed FROM v$sql_monitor WHERE status = 'EXECUTING' ORDER BY elapsed_time DESC;-- 生成HTML监控报告 SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => 'monitored_sql',type => 'HTML',report_level => 'ALL' ) AS report FROM dual;
日常监控脚本
-- 查找性能差的SQL执行计划 SELECT s.sql_id,substr(s.sql_text, 1, 100) as sql_text,s.executions,s.elapsed_time/1000000/ NULLIF(s.executions, 0) as avg_elapsed_sec,s.buffer_gets/ NULLIF(s.executions, 0) as avg_buffer_gets,s.rows_processed/ NULLIF(s.executions, 0) as avg_rows,p.plan_hash_value FROM v$sql s JOIN v$sql_plan p ON s.sql_id = p.sql_id WHERE s.last_active_time > SYSDATE - 1/24 -- 过去1小时AND s.elapsed_time > 10000000 -- 总耗时超过10秒AND s.executions > 0AND p.id = 1 -- 获取执行计划哈希 ORDER BY avg_elapsed_sec DESC FETCH FIRST 20 ROWS ONLY;