ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql performance tunning 15--怎样阅读执行计划

sql performance tunning 15--怎样阅读执行计划

原创 Linux操作系统 作者:oracle_db 时间:2012-05-25 16:51:54 0 删除 编辑
QL> set lines 200;


select ename,dname,grade
from emp,dept,salgrade
where emp.deptno=dept.deptno
and emp.sal between salgrade.losal and salgrade.hisal;



Execution Plan
----------------------------------------------------------
Plan hash value: 721498669

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    36 |     9  (23)| 00:00:01 |
|   1 |  NESTED LOOPS                |          |     1 |    36 |     9  (23)| 00:00:01 |
|   2 |   MERGE JOIN                 |          |     1 |    23 |     8  (25)| 00:00:01 |
|   3 |    SORT JOIN                 |          |     5 |    50 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | SALGRADE |     5 |    50 |     3   (0)| 00:00:01 |
|*  5 |    FILTER                    |          |       |       |            |          |
|*  6 |     SORT JOIN                |          |    14 |   182 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | EMP      |    14 |   182 |     3   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    13 |     1   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   5 - filter("EMP"."SAL"<="SALGRADE"."HISAL")
   6 - access("EMP"."SAL">="SALGRADE"."LOSAL")
       filter("EMP"."SAL">="SALGRADE"."LOSAL")
   9 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
        721  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed
怎么看这个执行计划呢?
可以先把计划转成树图,然后读取这个树图,以从下到上,从左到右的方式去阅读树图

但是实际上,很可能通过EXPLAIN 得出的执行计划是不准确的,通过SQL_TRACE我们可以得到实际运行时的执行计划。会话的环境对执行计划的正确性有很大影响。

测试:

会话1:
QL> create table t2
  2  as
  3  select * from all_objects;

Table created.

SQL> alter table t2 add constraint t2_pk primary key(object_id);

Table altered.

SQL> begin
  2  dbms_stats.gather_table_stats
  3  (user,'T2',
  4   method_opt => 'for all columns size auto',
  5    cascade => TRUE);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> explain plan for 
  2  select * from t2
  3  where object_id>32000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 20494 |  1861K|   160   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T2   | 20494 |  1861K|   160   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("OBJECT_ID">32000)

13 rows selected.

SQL> 

会话2:
SQL> alter session set optimizer_index_cost_adj=10;

Session altered.

SQL> delete from plan_table;

0 rows deleted.

SQL> explain plan for
  2  select * from t2 where object_id > 32000;

Explained.

SQL> set echo off
SQL> set lines 300      
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2609044213

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 20494 |  1861K|    50   (2)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    | 20494 |  1861K|    50   (2)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T2_PK | 20494 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("OBJECT_ID">32000)

14 rows selected.

SQL> 

那个是真的呢?为了获取真实的执行计划可以

insert into plan_table(
statement_id,timestamp,remarks,operation,
options,object_node,object_owner,object_name,
optimizer,search_columns,id,parent_id,
position,cost,cardinality,bytes,other_tag,
partition_start,partition_stop,partition_id,
other,distribution,cpu_cost,
io_cost,temp_space)
select rawtohex(address)||'_'||child_number,
sysdate,null,operation,options,
object_node,object_owner,object_name,
optimizer,search_columns,id,parent_id,
position,cost,cardinality,bytes,other_tag,
partition_start,partition_stop,partition_id,
other,distribution,cpu_cost,io_cost,
temp_space
from v$sql_plan
where (address,child_number) in
(select address,child_number
from v$sql
where sql_text= 
'select * from t2 where object_id >32000'
and child_number=0)


不过最好还是用AUTOTRACE

Capture.JPG

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-730976/,如需转载,请注明出处,否则将追究法律责任。

上一篇: sqlplus
请登录后发表评论 登录
全部评论

注册时间:2008-11-13

  • 博文量
    158
  • 访问量
    313982