oracle sql执行计划autotrace 安装

最新推荐文章于 2024-03-01 09:34:09 发布

iteye_6961

最新推荐文章于 2024-03-01 09:34:09 发布

阅读量178

点赞数

分类专栏： oracle 文章标签： oracle sql执行计划autotrace 安装

本文链接：https://blog.csdn.net/iteye_6961/article/details/82300686

版权

oracle 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

小生对oracle了解不多，平时也仅是简单的应用。写的sql多了，问题也就多，经常闹得DBA很蛋疼。网上搜罗了一下，oracle10 有个explain 工具，autotrace开启后，sqlplus里执行sql就可以看到执行计划分析，这个可以帮助自己做个简单的sql性能分析。

SQL> connect sys/sys as sysdba;
SQL> @/opt/oracle/product/10.2/db_1/sqlplus/admin/plustrce.sql;
SQL> create public synonym plan_table_explain for plan_table;--建立同义词
SQL> grant all on plan_table_explain to public;--授权所有用户
SQL>@/opt/oracle/product/10.2/db_1/sqlplus/admin/plustrce.sql;
SQL>grant plustrace to public;
SQL>set autotrace on；---开启autotrace
SQL> set timing on;--显示执行时间

SQL>spool d:\test.txt;
SQL> select * from nm_sp_type ;

    TYPEID TYPENAME
---------- --------------------
         1 内部
         2 外部
         3 测试

执行计划
----------------------------------------------------------
Plan hash value: 688850876

--------------------------------------------------------------------------------
| Id | Operation         | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     3 |    24 |     3   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| NM_SP_TYPE |     3 |    24 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          8 consistent gets
          0 physical reads
          0 redo size
        450 bytes sent via SQL*Net to client
        338 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          3 rows processed

SQL> select * from nm_sp_type where typeid = 0 ;

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 4154109726

--------------------------------------------------------------------------------------------
| Id | Operation                   | Name         | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |     8 |     1   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| NM_SP_TYPE   |     1 |     8 |     1   (0)| 00:00:01 |
|* 2 |   INDEX UNIQUE SCAN         | SYS_C0019321 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("TYPEID"=0)

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          1 consistent gets
          0 physical reads
          0 redo size
        295 bytes sent via SQL*Net to client
        327 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> spool off ;---关闭保存

SQL>set autotrace off ;---关闭自动计划跟踪

结果分析稍后补上