Oracle执行计划之入门（1）

第一种执行计划：

点击(此处)折叠或打开

1. SQL> explain plan
   
2.   2 set statement_id ='test1'
   
3.   3 into plan_table
   
4.   4 for select * from daiqi.t_baidu_doc
   
5.   5 ;
   
6. 
   
7. Explained.
   
8. 
   
9. SQL> select lpad(' ',level-1)||operation||' '||options||' '||
   
10.   2 object_name "PLan"
    
11.   3 from plan_table
    
12.   4 connect by prior id= parent_id
    
13.   5 and prior statement_id = statement_id
    
14.   6 start with id =0 and statement_id ='&1'
    
15.   7 order by id;
    
16. Enter value for 1: test1
    
17. old 6: start with id =0 and statement_id ='&1'
    
18. new 6: start with id =0 and statement_id ='test1'
    
19. 
    
20. PLan
    
21. --------------------------------------------------------------------------------
    
22. SELECT STATEMENT
    
23.  TABLE ACCESS FULL T_BAIDU_DOC

第二种执行计划：

点击(此处)折叠或打开

1. SQL> explain plan for
   
2.   2 select * from daiqi.t_baidu_doc;
   
3. 
   
4. Explained.
   
5. 
   
6. SQL> set linesize 130;
   
7. SQL> set pagesize 0;
   
8. SQL> select * from table(DBMS_XPLAN.DISPLAY);
   
9. Plan hash value: 149575921
   
10. 
    
11. ---------------------------------------------------------------------------------
    
12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
13. ---------------------------------------------------------------------------------
    
14. | 0 | SELECT STATEMENT | | 4848K| 795M| 31955 (1)| 00:06:24 |
    
15. | 1 | TABLE ACCESS FULL| T_BAIDU_DOC | 4848K| 795M| 31955 (1)| 00:06:24 |
    

第三种执行计划:

点击(此处)折叠或打开

1. SQL>@$ORACLE_HOME/rdbms/admin/utlxplan.sql
   
2. 
   
3. Autotrace特性.
   
4. 
   
5. SET AUTOTRACE ON EXPLAIN: 此选项仅生成执行计划，不执行查询本身.
   
6. SET AUTOTRACE ON STATISTICS: 此选项仅显示SQL语句的执行统计数据.
   
7. SET AUTOTRACE ON: 此选项既显示执行计划,又显示SQL语句的执行统计数据
   
8. 
   
9. SQL> set autotrace on explain;
   
10. SQL> select count(*) from daiqi.t_baidu_doc;
    
11. 
    
12.   COUNT(*)
    
13. ----------
    
14.    4848509
    
15. 
    
16. 
    
17. Execution Plan
    
18. ----------------------------------------------------------
    
19. Plan hash value: 4162034560
    
20. 
    
21. ------------------------------------------------------------------------------
    
22. | Id | Operation | Name | Rows | Cost (%CPU)| Time |
    
23. ------------------------------------------------------------------------------
    
24. | 0 | SELECT STATEMENT | | 1 | 2858 (2)| 00:00:35 |
    
25. | 1 | SORT AGGREGATE | | 1 | | |
    
26. | 2 | INDEX FAST FULL SCAN| SYS_C0010938 | 4848K| 2858 (2)| 00:00:35 |
    
27. ------------------------------------------------------------------------------
    
28. 
    
29. SQL>