如何从内存中获取SQL语句的执行计划
SQL语句的性能数据可以从视图v$sql中获取，针对可能有性能问题的SQL语句，可以根据该语句对应的plan_hash_value从视图v$sql_plan获取到该语句的执行计划。
创建UDF - REPEAT，后面SQL中有用到，格式化输出用
|
CREATE OR REPLACE FUNCTION repeat(p_str VARCHAR, p_repeat INT) RETURN VARCHAR IS v_str VARCHAR(1000); BEGIN v_str := ''; IF p_repeat > 0 THEN FOR i in 1..p_repeat LOOP v_str := v_str || p_str; END LOOP; END IF; RETURN v_str; END; / |
根据plan_hash_value获取SQL文本（以2609204012为例）
|
SELECT sql_fulltext FROM v$sql WHERE plan_hash_value = 2609204012; |
SQL文本输出示例：
|
SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock s WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = ? AND d_id = ? ) ) |
获取内存中SQL语句的执行计划
|
SELECT id, repeat('||', depth) || operation as operation, object_name FROM v$sql_plan WHERE plan_hash_value = 2609204012 AND id IS NOT NULL ORDER BY id; |
输出示例：
