麻烦帮我看一下执行计划，我看不懂~~~

Question

SQL> set arraysize 1
SQL> set line 100
SQL> column op format a40
SQL> column object_name format a20
SQL> column options format a20
SQL> 
SQL> select lpad(' ',2*(level-1))||operation op,options,object_name,position
  2  from plan_table
  3  start with id = 0 and statement_id = '0001'
  4  connect by prior id = parent_id and
  5  statement_id = '0001'
  6  /OP                                       OPTIONS              OBJECT_NAME            POSITION
---------------------------------------- -------------------- -------------------- ----------
SELECT STATEMENT
  SORT                                   ORDER BY                                           1
    MERGE JOIN                           OUTER                                              1
      SORT                               JOIN                                               1
        MERGE JOIN                       OUTER                                              1
          SORT                           JOIN                                               1
            NESTED LOOPS                 OUTER                                              1
              NESTED LOOPS               OUTER                                              1
                TABLE ACCESS             BY INDEX ROWID       PRODUCT_INFO                  1
                  INDEX                  RANGE SCAN           IPRODUCT_INFO_VENDOR          1
                                                              IDOP                                       OPTIONS              OBJECT_NAME            POSITION
---------------------------------------- -------------------- -------------------- ----------                TABLE ACCESS             BY INDEX ROWID       CUSTOMER                      2
                  INDEX                  UNIQUE SCAN          PK_CUSTOMER                   1
              TABLE ACCESS               BY INDEX ROWID       PRODUCT_CODE_LIST             2
                INDEX                    UNIQUE SCAN          PK_PRODUCT_CODE_LIST          1
          SORT                           JOIN                                               2
            TABLE ACCESS                 FULL                 FG_CATEGORY                   1
      SORT                               JOIN                                               2
        TABLE ACCESS                     FULL                 FG_CATEGORY                   1選取了 18 列SQL> 

Answer 1

plan_table各字段描述看一下应该就懂了吧!

Answer 2

TABLE ACCESS和INDEX 看扫描类型和索引执行情况吧
应该还有个cost列可以看成本

Answer 3

我们一般都直接使用pl/sql developer中的explain plan按钮SQL> explain plan set statement_id='abc' for 
  2  
  2  select '2010',
  3    to_char(dense_rank()over(order by flag),'0000')||'00'||pro_code,
  4    to_char(dense_rank()over(partition by div_name1 order by flag),'00')||pro_code,
  5    pro_name,xm_name||'_'||pro_name,1,null,2,'440304',null,
  6    null,div_name1
  7  from (
  8    select a.*,max(div_name)over(partition by rownum-level) div_name1 from(
  9        select div_name,max(xm_code)over(partition by rownum-level) xm_code,
 10        max(xm_name)over(partition by rownum-level) xm_name,
 11        pro_code,pro_name,px,rownum-level flag
 12        from aa_temp
 13        start with xm_code is not null
 14        connect by prior px=px-1 and xm_code is null)a
 15     start with div_name is not null
 16     connect by px=prior px+1 and div_name is null)
 17  ;
Explained
 
SQL> 
SQL> SELECT
  2    id ||
  3    DECODE(id, 0, '', LPAD(' ', 2*(level - 1))) || ' ' ||
  4    operation || ' ' ||
  5    options || ' ' ||
  6    object_name || ' ' ||
  7    object_type || ' ' ||
  8    DECODE(cost, NULL, '', 'Cost = ' || position)
  9  AS execution_plan
 10  FROM plan_table
 11  CONNECT BY PRIOR id = parent_id
 12  AND statement_id = 'abc'
 13  START WITH id = 0
 14  AND statement_id = 'abc';
 
EXECUTION_PLAN
--------------------------------------------------------------------------------
0 SELECT STATEMENT    Cost = 6
1   WINDOW SORT   Cost = 1
2     WINDOW SORT   Cost = 1
3       VIEW    Cost = 1
4         WINDOW SORT   Cost = 1
5           COUNT
6             CONNECT BY NO FILTERING WITH START-WITH
7               VIEW    Cost = 1
8                 WINDOW SORT   Cost = 1
9                   COUNT
10                     CONNECT BY NO FILTERING WITH START-WITH
11                       TABLE ACCESS FULL AA_TEMP TABLE Cost = 1
 
12 rows selected
 
SQL> 

Answer 4

上面使用的explain plan代码如下
SELECT
  id ||
  DECODE(id, 0, '', LPAD(' ', 2*(level - 1))) || ' ' ||
  operation || ' ' ||
  options || ' ' ||
  object_name || ' ' ||
  object_type || ' ' ||
  DECODE(cost, NULL, '', 'Cost = ' || position)
AS execution_plan
FROM plan_table
CONNECT BY PRIOR id = parent_id
AND statement_id = '&&v_statement_id'
START WITH id = 0
AND statement_id = '&v_statement_id';你上面的position实际上就是cost

Answer 5

每个人都有自己的知识积累呀. 我一般也是在Toad里点点.. 很少去写SQL来执行explain plan。我一般用下面这个语句来执行：
SQL>EXPLAIN PLAN FOR 你的sql语句; 
然后 
SELECT plan_table_output 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE')); 
查看结果就是前面SQL语句的执行计划。 SQL> EXPLAIN PLAN FOR SELECT * FROM SCOTT.EMP;已解释。SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------Plan hash value: 3956160932--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Operation 选项里能看到是走全表扫描还是走索引的，
cost 能看出cost是多少，
Time 用了多少时间我觉得这样也挺清楚的.. 楼主的那种SQL，没用过..

Answer 6

 TABLE ACCESS   BY INDEX ROWID(采用的是index rowid扫描) PRODUCT_INFO（该对象即表的名称）
 INDEX      RANGE SCAN索引范围扫描    IPRODUCT_INFO_VENDOR用到的索引名称
下面的亦同，OPTIONS 表示扫描方式
TABLE ACCESS  FULL   FG_CATEGORY全表扫描，没走索引

Answer 7

FG_CATEGORY,CUSTOMER, PRODUCT_CODE_LIST,PRODUCT_INFO  貌似在做全表扫描

Answer 8

http://hi.csdn.net/link.php?url=http://blog.csdn.net%2Fwh62592855看一下这个吧 可能会对你有些帮助

Answer 9

里面讲了几种ORACLE扫描数据的方法还有 执行计划的查看方法是从上到下 从内至外

Answer 10

非常感谢各位的回复！~ 我发现我的SQL在加上order by后执行效率下降了，而去掉order by后恢复正常，请问这是什么原因？..

Answer 11

在数据集很大的情况下尽量避免使用order by
order by会在内存及临时段中对结果集进行排序
在数据量大的时候会降低执行效率

Answer 12

如果经常需要对这个和表进行查询并且需要排序的话个人感觉可以考虑一下使用IOT表

Answer 13

学习Oracle索引组织表（IOT)

Answer 14

两表JOIN，我该将哪个表变成索引组织表呢？...

Answer 15

这个执行计划非常清楚，两次FULL TABLE SCAN on FG_CATEGORY，然后通过INDEX 循环查询 PRODUCT_CODE_LIST， CUSTOMER， PRODUCT_INFO。我猜FG_CATEGORY本身是个小表，所以FULL TABLE SCAN应该不是问题。执行成本这么小，是不是你没有做过表分析？ORDER by的效率取决于你最后的结果的大小。IOT是指按照某个索引来组织表的物理存贮，其优点是根据索引查询的时候可以省略掉TABLE SCAN BY ROWID一步。但IOT表对插入、更新有效率有极大的影响。向表中如果插入一个记录而索引列为中间值或者更新索引列为中间值会导致对表的存储作极大的更新。所以IOT主要用来处理静态表或者顺序增长的表。从多表中查询然后根据业务需求排序和这个IOT完全风马牛不相及。

Answer 16

哦……对哦 刚刚没仔细看是多个表 还以为是单表查询
那就不要用IOT 了

Answer 17

我的SQL如下：
SELECT   e.card_no, e.cust_partno, sales.get_process (e.card_no) AS process,
         NVL (sales.get_in_process_tax (e.card_no, 'N'), 0) AS in_production,
         NVL (sales.get_stock_qty (e.ref_card_no, 'A', 'N'), 0) AS a_qty,
         NVL (sales.get_stock_qty (e.ref_card_no, 'B', 'N'), 0) AS b_qty,
         NVL (sales.get_stock_qty (e.ref_card_no, 'D', 'N'), 0) AS d_qty,
         NVL (sales.get_max_produceno (e.card_no), '') AS max_produceno,
         NVL (sales.get_card_prod_mat (e.card_no), '') AS prod_mat,
         NVL (e.size_l, '') || '*' || NVL (size_w, '') AS sizex,
         sales.get_card_all_color (e.card_no) AS color_no,
         sales.get_prod_mat_detail (e.card_no) AS specw, g.weight,
         x.category_text AS industry_category,
         y.category_text AS product_category, z.c_short, e.insert_date
    FROM sales.product_info e,
         sales.product_code_list g,
         (SELECT category_id, category_text
            FROM erpuser.fg_category
           WHERE category_type = '1') x,
         (SELECT category_id, category_text
            FROM erpuser.fg_category
           WHERE category_type = '2') y,
         (SELECT customerid, c_short
            FROM erpuser.customer
           WHERE customerid >= 'A') z
   WHERE e.vendorid = '120100'
     AND e.CANCEL <> 'Y'
     AND e.card_no = g.card_no(+)
     AND e.fg_category1 = x.category_id(+)
     AND e.fg_category2 = y.category_id(+)
     AND e.brand_customerid = z.customerid(+)
ORDER BY e.card_no用不到分析函數进行排序吧？...

Answer 18

要对最后的结果集进行排序就只能用order by
只是结果集数据量很大的话，不知这个order by是否还有必要

Answer 19

 (SELECT category_id, category_text
            FROM erpuser.fg_category
           WHERE category_type = '1') x,
像这样的子查询没有必要，将条件加到where连接条件后面即可

Answer 20

    FROM sales.product_info e,
         sales.product_code_list g,
         erpuser.fg_category x,
         erpuser.fg_category y,
         erpuser.customer z
            
   WHERE e.vendorid = '120100'
     AND e.CANCEL <> 'Y'
     AND e.card_no = g.card_no(+)
     AND e.fg_category1 = x.category_id(+)
     AND e.fg_category2 = y.category_id(+)
     AND e.brand_customerid = z.customerid(+)
     and x.category_type(+) = '1'
     and y.category_type(+) = '2'
     and z.customerid(+) >= 'A'

Answer 21

才1000多条记录排序不对产生太大影响
可能影响效率的在
1.连接使用的表是否数据量比较大
2.还跟使用的自定义函数的性能有关如果表的数据量很大，但用条件过滤后记录数会大大降低，可以使用临时表
  FROM sales.product_info e,
         sales.product_code_list g,
         (SELECT category_id, category_text
            FROM erpuser.fg_category
           WHERE category_type = '1') x,
         (SELECT category_id, category_text
            FROM erpuser.fg_category
           WHERE category_type = '2') y,
         (SELECT customerid, c_short
            FROM erpuser.customer
           WHERE customerid >= 'A') z
比如建立一个临时表e,来存储sales.product_info中cancel<>'Y'的记录，x,y,z同理
然后用建立的临时表来关联查询

Answer 22

create global temporary table x on commit preserve rows
as SELECT category_id, category_text 
            FROM erpuser.fg_category 
          WHERE category_type = '1'

Answer 23

1、最大两张表记录都不超过1W条
2、跟自定义函数的性能有关怎么解释？..难道也跟order by能扯上关系？..

Answer 24

不是说和order by
既然是要对结果进行排序，而且结果集只有1000条记录，order by暂时排除出讨论之列
自定义函数的性能可能会影响到运用该函数的sql性能，和排序无关。函数是公司提供的，必须使用，这点可以略过。
效率太低的话要看看索引是否还能优化，或者建临时表
你这个语句执行需要多长时间啊？
上面NVL (sales.get_max_produceno (e.card_no), '') 这样的nvl就不用加了吧，若为空，转换为空，这个转换是多余的。

Answer 25

语句执行需要4秒多...去掉nvl效果一样...KingSunSha说没有注意表分析，我想知道这个表分析指的什么？..

Answer 26

4秒多比较正常了
那个nvl是多余的，但对效率影响不大
表分析指的是DBMS_STATS.GATHER_SCHEMA_STATS()
收集统计信息的目的是为了使基于CBO的执行计划更加准确

Answer 27

调整了一下格式
SELECT   e.card_no, e.cust_partno, sales.get_process (e.card_no) process
       , NVL (sales.get_in_process_tax (e.card_no, 'N'), 0) in_production
       , NVL (sales.get_stock_qty (e.ref_card_no, 'A', 'N'), 0) a_qty
       , NVL (sales.get_stock_qty (e.ref_card_no, 'B', 'N'), 0) b_qty
       , NVL (sales.get_stock_qty (e.ref_card_no, 'D', 'N'), 0) d_qty
       , NVL (sales.get_max_produceno (e.card_no), '') max_produceno
       , NVL (sales.get_card_prod_mat (e.card_no), '') prod_mat
       , NVL (e.size_l, '') || '*' || NVL (size_w, '') sizex
       , sales.get_card_all_color (e.card_no) color_no
       , sales.get_prod_mat_detail (e.card_no) specw, g.weight
       , x.category_textindustry_category, y.category_textproduct_category
       , z.c_short, e.insert_date
    FROM sales.product_info e
       , sales.product_code_list g
       , erpuser.fg_category x
       , erpuser.fg_category y
       , customer z
   WHERE e.vendorid = '120100'
     AND e.CANCEL <> 'Y'
     AND e.card_no = g.card_no(+)
     AND e.fg_category1 = x.category_id(+)
     AND e.fg_category2 = y.category_id(+)
     AND e.brand_customerid = z.customerid(+)
     AND x.category_type = '1'
     AND y.category_type = '2'
     AND z.customerid >= 'A'
ORDER BY e.card_no;

Answer 28

然后我们来分析问题可能出在那里*. 有些NVL是多余的，但对性能几乎没有影响
*. ORDER BY对1000多条结果集几乎不耗时，所以加不加ORDER BY应该执行时间相同。有一个可能，Oracle对原纪录集e表中的card_no排序而不是对结果集排序，（我只听说过在某个版本上），但看你的执行计划，SORT是对结果集的，应该不是这个的原因。如果你要排除这个因素，可以改成"ORDER　BY 1"试一下。
*. 自定义函数的问题。通常执行自定义函数对性能有可能有比较大的影响，因为Oracle做成本分析的时候是不看函数内部的。但是你说了不带ORDER BY的时候执行时间很短。那自定义函数的印象应该很小。我看不出来会在哪里出问题，你做一个trace吧。set autotrace on
set timing on然后执行两次你的查询,先不带order by， 再加上order by
把trace的结果贴出来

Answer 29

SQL> set autotrace on
无法找到阶段作业是别字。请检查PLUSTRACE角色是否可用
启动STATISTICS报表时发生错误
SQL> set timing on

Answer 30

SQL> set autotrace on
无法找到阶段作业识别字。请检查PLUSTRACE角色是否可用
启动STATISTICS报表时发生错误
SQL> set timing on

Answer 31

权限设置问题,请参见
http://www.itpub.net/thread-381342-1-1.html

Answer 32

非要在sys用户下么...我在system用户下没有权限...真郁闷..sys密码忘了

Answer 33

数据库跑在什么平台上?如果是unix或者linux，用oracle用户登陆，执行
export ORACLE_SID=mysid
sqlplus "/ as sysdba"就应该能以sys登陆如果不知道oracle系统用户口令，可以以root登陆，然后su - oracle

Answer 34

在服务器上，操作系统认证，不用密码conn / as sysdba 

Answer 35

加上order by后執行結果如下：
統計數值
----------------------------------------------------------
          0  recursive calls
         76  db block gets
         56  consistent gets
          0  physical reads
          0  redo size
       2092  bytes sent via SQL*Net to client
        673  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed不加order by執行結果分析如下：
統計數值
----------------------------------------------------------
          0  recursive calls
         72  db block gets
         54  consistent gets
          0  physical reads
         60  redo size
       1976  bytes sent via SQL*Net to client
        662  bytes received via SQL*Net from client
         20  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         17  rows processed貌似差别好像不是很大...

Answer 36

不加order by分析：
执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONNECT BY
   2    1     TABLE ACCESS (FULL) OF 'PLAN_TABLE'
   3    1     TABLE ACCESS (BY USER ROWID) OF 'PLAN_TABLE'
   4    1     TABLE ACCESS (FULL) OF 'PLAN_TABLE'
统计数值
----------------------------------------------------------
          0  recursive calls
         72  db block gets
         53  consistent gets
          0  physical reads
          0  redo size
       1976  bytes sent via SQL*Net to client
        662  bytes received via SQL*Net from client
         20  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         17  rows processed

Answer 37

加上order by分析：
執行計劃
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONNECT BY
   2    1     TABLE ACCESS (FULL) OF 'PLAN_TABLE'
   3    1     TABLE ACCESS (BY USER ROWID) OF 'PLAN_TABLE'
   4    1     TABLE ACCESS (FULL) OF 'PLAN_TABLE'
統計數值
----------------------------------------------------------
          0  recursive calls
         76  db block gets
         56  consistent gets
          0  physical reads
          0  redo size
       2092  bytes sent via SQL*Net to client
        673  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed

Answer 38

加不加ORDER BY结果几乎一样，那执行时间是否一样？set timing on

Answer 39

加order by：real: 31836  不加order by：real: 31633MS也不是很明显....问题到底出在哪里呢？另请问：Windows这个执行时间怎样换算？...

Answer 40

怎么看加不加Order By对于执行成本都没有什么区别，所以我问执行时间有区别吗。
不明白你问的"Windows这个执行时间怎样换算"是什么意思，set timing on是跟踪Query在服务器端的执行时间，如果执行成本相同，那执行时间也应该相同。你的问题到底是什么？

Answer 41

现在的加与不加order by执行时间又不大的差异算是执行成本差异大么？.."Windows这个执行时间怎样换算"的意思是想说：我看到一般的执行时间格式都是：Elapsed: 00:00:01.02或Executed in 0.219 seconds不知是在哪个操作系统下？..而我在Windows操作系统下只显示real: 31633 这样的格式，这样我不知道该怎样换算时间..

Answer 42

SQL> select * from dept;    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON4 rows selected.SQL> set timing on
SQL> select * from dept;    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON4 rows selected.Elapsed: 00:00:00.02
SQL>

Answer 43

就直接在SQL*PLUS里set timing on就好了呀
不管什么操作系统

Answer 44

是这样么？... 那我执行set timing on后显示的real: 31633是什么意思？...

Answer 45

你是在SQL*PLUS里操作的吗你把执行代码贴出来看看

Answer 46

楼主有什么疑问呀如果是有关autotr的权限问题的话可以参考我以前的一个实例
http://blog.csdn.net/inthirties/archive/2009/08/05/4405780.aspx

Answer 47

Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - ProductionSQL> SELECT   e.card_no, e.cust_partno,
  2           sales.get_card_all_color (e.card_no) AS color_no,
  3           sales.get_prod_mat_detail (e.card_no) AS specw, g.weight,
  4           x.category_text AS industry_category,
  5           y.category_text AS product_category, z.c_short, e.insert_date
  6      FROM sales.product_info e,
  7           sales.product_code_list g,
  8           erpuser.fg_category x,
  9           erpuser.fg_category y,
 10           erpuser.customer z
 11     WHERE e.vendorid = '120100'
 12       AND e.CANCEL <> 'Y'
 13       AND e.card_no = g.card_no(+)
 14       AND e.fg_category1 = x.category_id(+)
 15       AND e.fg_category2 = y.category_id(+)
 16       AND x.category_type(+) = '1'
 17       AND y.category_type(+) = '2'
 18       AND z.customerid(+) >= 'A'
 19       AND e.brand_customerid = z.customerid(+)
 20       AND ROWNUM = 1
 21  ORDER BY e.card_no
 22  /
絯侥跋祇ネ犯. 叫ノ SET ㏑搭ぶ ARRAYSIZE ┪糤 MAXDATA
SQL> set arraysize 1
SQL> set pagesize 200
SQL> set linesize 100
SQL> set timing on
SQL> /CARD_NO      CUST_PARTNO
------------ --------------------
COLOR_NO
---------------------------------------------------------------------------------------
SPECW
---------------------------------------------------------------------------------------
    WEIGHT
----------
INDUSTRY_CATEGORY
---------------------------------------------------------------------------------------
PRODUCT_CATEGORY
---------------------------------------------------------------------------------------
C_SHORT
---------------------------------------------------------------------------------------
INSERT_DATE
------------------
1201000003S1
3
0珇&都--皊弘摸都珇
翲夹乓--猔種/牡夹乓
礚珇礟め
26-11る-07
 real: 110
SQL> 

Answer 48

……好吧 我确实不知道那个real是什么
那你试试set timing off
看看还有没有那个real出现

Answer 49

呵呵  繁体操作系统 繁体版Oracle 不好意思

Answer 50

这个是跟Oracle的版本有关系么？... 网上找了半天也没找到...real到底表示的什么时间啊？...

Answer 51

珇&都--皊弘摸都珇
翲夹乓--猔種/牡夹乓
礚珇礟め
26-11る-07
 real: 110这已经不算繁体字了吧?是乱码吧
会不会real: 110就是显示的时间的乱码你用的是ORACLE8  我这里没环境
我机子上有10G和9i好像都是可以的