联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
当我们没有权限访问业务表,但是需要查看shared pool中部分sql语句的执行计划,原则上来说,查询v$sql_plan视图结合hash_value可以实现,但是因为这个是表格形式,看起来不太美观,和我们长看的执行计划有一定的出入,这里提供两个脚本,实现查看该种情况下的执行计划。
oracle 9i
[oracle@xifenfei ~]$ more get_plan.sql set pagesize 0 set linesize 150 set serveroutput on size 10000 col plan_table_output format a125 undefine hash_value set verify off feedback off var hash_value varchar2(20) begin :hash_value := '&hash_value'; end; / insert into plan_table (statement_id,timestamp,operation,options,object_node,object_owner,object_name, optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag, partition_start,partition_stop,partition_id,other,distribution, cpu_cost,io_cost,temp_space,access_predicates,filter_predicates ) select distinct hash_value,sysdate,operation,options,object_node,object_owner,object_name, optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag, partition_start,partition_stop,partition_id,other,distribution, cpu_cost,io_cost,temp_space,access_predicates,filter_predicates from v$sql_plan where hash_value = :hash_value / col piece noprint select distinct piece,sql_text from v$sqltext where hash_value = :hash_value order by piece / @?/rdbms/admin/utlxplp.sql set linesize 80 set verify on feedback on pagesize 1000
oracle 10g/11g
[oracle@xifenfei ~]$ more get_plan.sql set pagesize 0 set linesize 150 set serveroutput on size 10000 col plan_table_output format a125 undefine hash_value set verify off feedback off var hash_value varchar2(20) begin :hash_value := '&hash_value'; end; / insert into plan_table (statement_id,timestamp,operation,options,object_node,object_owner,object_name, optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag, partition_start,partition_stop,partition_id,other,distribution, cpu_cost,io_cost,temp_space,access_predicates,filter_predicates, plan_id,OBJECT_ALIAS,DEPTH,PROJECTION,TIME,QBLOCK_NAME ) select distinct hash_value,sysdate,operation,options,object_node,object_owner,object_name, optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag, partition_start,partition_stop,partition_id,other,distribution, cpu_cost,io_cost,temp_space,access_predicates,filter_predicates, :hash_value,OBJECT_ALIAS,DEPTH,PROJECTION,TIME,QBLOCK_NAME from v$sql_plan where hash_value = :hash_value / col piece noprint select distinct piece,sql_text from v$sqltext where hash_value = :hash_value order by piece / @?/rdbms/admin/utlxplp.sql set linesize 80 set verify on feedback on pagesize 1000
使用方法
SQL> SELECT hash_value FROM V$SQL WHERE SQL_TEXT 2 LIKE 'SELECT * FROM SYS.SMON_SCN_TIME'; HASH_VALUE ---------- 3019898357 SQL> @get_plan.sql Enter value for hash_value: 3019898357 SELECT * FROM SYS.SMON_SCN_TIME ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS FULL| SMON_SCN_TIME | 1 | 1163 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------------
补充说明
其实9i和10g/11g中得出执行计划的出入就是在plan_table表上
在9i中:plan_table表需要通过脚本创建并且授权
SQL> connect / as sysdba; SQL> @?/rdbms/admin/utlxplan.sql; SQL> create public synonym plan_table for plan_table; --建立同义词 SQL> grant all on plan_table to public;--授权所有用户
在10g/11g中:plan_table表系统自带,不需要创建。因为plan_table表中含有plan_id列,而得出执行计划时该列不能为空,所以上面脚本中对于10/11g数据库必须要填充plan_id值