oracle 命令行查看sql执行计划

oracle命令行查看SQL的执行计划

1.定义变量

var d1 varchar2(10)
exec :d1:='20091001'

 

２.打开执行计划开关

set autotrace traceonly explain

 

３.绑定变量到SQL中，并执行SQL

select count(c.storageplatformid) from ispace_content_info c where c.storageplatformid <> '58' and c.storageplatformid <>'62' and c.contentid in( select t.contentid from ispace_flv_content t where t.flv_state<>3 and t.uploadtime < to_date(:"d1",'YYYY-MM-dd'));

 

4.查看结果

 

Execution Plan
----------------------------------------------------------
Plan hash value: 2058968918

--------------------------------------------------------------------------------
-------------------------

| Id  | Operation                      | Name                   | Rows  | Bytes
| Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
-------------------------

|   0 | SELECT STATEMENT               |                        |     1 |    83
|     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE                |                        |     1 |    83
|            |          |

|   2 |   NESTED LOOPS                 |                        |       |
|            |          |

|   3 |    NESTED LOOPS                |                        |     1 |    83
|     3   (0)| 00:00:01 |

|   4 |     TABLE ACCESS BY INDEX ROWID| ISPACE_FLV_CONTENT     |     1 |    44
|     2   (0)| 00:00:01 |

|*  5 |      INDEX RANGE SCAN          | IX_ISPACE_FLV_CONTENT  |     1 |
|     1   (0)| 00:00:01 |

|*  6 |     INDEX UNIQUE SCAN          | PK_ISPACE_CONTENT_INFO |     1 |
|     0   (0)| 00:00:01 |

|*  7 |    TABLE ACCESS BY INDEX ROWID | ISPACE_CONTENT_INFO    |     1 |    39
|     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------
-------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T"."UPLOADTIME"<TO_DATE(:d1,'YYYY-MM-dd'))
       filter("T"."FLV_STATE"<>3)
   6 - access("C"."CONTENTID"="T"."CONTENTID")
   7 - filter("C"."STORAGEPLATFORMID"<>'58' AND "C"."STORAGEPLATFORMID"<>'62')

 

 

 

----------

查看数据表的索引,大写表名

select p.index_name,p.column_name from user_ind_columns p where p.table_name='ISPACE_MESSAGE_INFO';

 

----------

给SQL加提示，使用强制索引，注意提示的格式．

select /*+ INDEX(ISPACE_MESSAGE_INFO IX_ISPACE_MSG_PID) */ count(MSGID) from ISPACE_MESSAGE_INFO where PARENTCATALOGID in (select * from table (cast( in_list(#parentCatalogIDs#) as cid_type)))