oracle sql执行计划autotrace 安装

 

        小生对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 ;---关闭自动计划跟踪

结果分析稍后补上