查看oracle数据库sql的实际执行计划

        在生产环境中，如果发现某SQL执行慢，要查看它的执行计划，有如下方法：

方法一：

        先查v$sqltext获得HASH_VALUE值，再通过HASH_VALUE值查询v$sql_plan获得此SQL的实际执行计划。

        实例：

        想知道如下SQL的实际执行计划

SELECT /*+ index(tt IX_TT_CREATED_DATE) */ to_char(tt.created_date,'YYYY-MM-DD'),count(1)
 FROM test_table tt
WHERE  tt.code IN('X01', 'X02', 'X333', 'X365')
 AND tt.created_time >= TO_DATE('2014-12-20', 'yyyy-mm-dd')
 AND tt.created_time < TO_DATE('2015-08-20', 'yyyy-mm-dd')
 group by to_char(tt.created_time,'YYYY-MM-DD');

        先查询v$sqltext获得HASH_VALUE值，如果未查询到记录，则可以先执行一下上面的SQL后再查

select * from v$sqltext t where t.SQL_TEXT like 'SELECT /*+ index(pbi IX_PBI_CREATED_DATE) */%'

        上面SQL执行的结果中的HASH_VALUE的值为3042406205，那么我们可以以它作为条件再查询v$sql_plan获得实际的执行计划。

select * from v$sql_plan t where t.HASH_VALUE = '3042406205';

        运行结果如下所示：

 

方法二：

        sqlpuls登录成功后（关于sqlplus的登陆配置，请参考Oracle的tnsnames.ora配置），先设置set linesize 200，然后输入explain plan for select * from table_name...，再执行SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);查询执行计划。

        还可以通过如下语句查看统计信息、SQL资源消耗。

select * from dba_tables a where a.table_name = upper('table_name');

select * from v$sql a where a.SQL_TEXT like 'select * from ...';

        如下所示：

 

PS：v$sql说明

        v$sql：一条语句可以映射多个 cursor,因为对象所指的 cursor可以有不同用户 (如例 1)。如果有多个 cursor(子游标 )存在，在 V$SQLAREA为所有 cursor提供集合信息。

实例：

        这里介绍以下 child cursor

        user A: select * from tbl

        user B: select * from tbl

        大家认为这两条语句是不是一样的啊，可能会有很多人会说是一样的，但我告诉你不一定，那为什么呢？

        这个 tbl A看起来是一样的，但是不一定哦，一个是 A用户的，一个是 B用户的，这时他们的执行计划分析代码差别可能就大了哦，改下写法大家就明白了：

        select * from A.tbl

        select * from B.tbl

　　在个别 cursor上， v$sql可被使用。该视图包含 cursor级别资料。当试图定位 session或用户以分析 cursor时被使用。

　　 PLAN_HASH_VALUE列存储的是数值表示的 cursor执行计划。可被用来对比执行计划。PLAN_HASH_VALUE让你不必一行一行对比即可轻松鉴别两条执行计划是否相同。

V$SQL中的列说明：

        SQL_TEXT： SQL文本的前 1000个字符

        SHARABLE_MEM：占用的共享内存大小 (单位： byte)

        PERSISTENT_MEM：生命期内的固定内存大小 (单位： byte)

        RUNTIME_MEM：执行期内的固定内存大小

        SORTS：完成的排序数

        LOADED_VERSIONS：显示上下文堆是否载入， 1是 0否

        OPEN_VERSIONS：显示子游标是否被锁， 1是 0否

        USERS_OPENING：执行语句的用户数

        FETCHES： SQL语句的 fetch数。

        EXECUTIONS：自它被载入缓存库后的执行次数

        USERS_EXECUTING：执行语句的用户数

        LOADS：对象被载入过的次数

        FIRST_LOAD_TIME：初次载入时间

        INVALIDATIONS：无效的次数

        PARSE_CALLS：解析调用次数

        DISK_READS：读磁盘次数

        BUFFER_GETS：读缓存区次数

        ROWS_PROCESSED：解析 SQL语句返回的总列数

        COMMAND_TYPE：命令类型代号

        OPTIMIZER_MODE： SQL语句的优化器模型

        OPTIMIZER_COST：优化器给出的本次查询成本

        PARSING_USER_ID：第一个解析的用户 ID

        PARSING_SCHEMA_ID：第一个解析的计划 ID

        KEPT_VERSIONS：指出是否当前子游标被使用 DBMS_SHARED_POOL包标记为常驻内存

        ADDRESS：当前游标父句柄地址

        TYPE_CHK_HEAP：当前堆类型检查说明

        HASH_VALUE：缓存库中父语句的 Hash值

        PLAN_HASH_VALUE：数值表示的执行计划。

        CHILD_NUMBER：子游标数量

        MODULE：在第一次解析这条语句是通过调用 DBMS_APPLICATION_INFO.SET_MODULE设置的模块名称。

        ACTION：在第一次解析这条语句是通过调用 DBMS_APPLICATION_INFO.SET_ACTION设置的动作名称。

        SERIALIZABLE_ABORTS：事务未能序列化次数

        OUTLINE_CATEGORY：如果 outline在解释 cursor期间被应用，那么本列将显示出 outline各类，否则本列为空

        CPU_TIME：解析 /执行 /取得等 CPU使用时间 (单位，毫秒 )

        ELAPSED_TIME：解析 /执行 /取得等消耗时间 (单位，毫秒 )

        OUTLINE_SID： outline session标识

        CHILD_ADDRESS：子游标地址

        SQLTYPE：指出当前语句使用的 SQL语言版本

        REMOTE：指出是否游标是一个远程映象 (Y/N)

        OBJECT_STATUS：对象状态 (VALID or INVALID)

        IS_OBSOLETE：当子游标的数量太多的时候，指出游标是否被废弃 (Y/N)

 

参考资料：http://lizhiyu.iteye.com/blog/966626