求惑，关于“cpu costing is off (consider enabling it)”

26856649

red hat 5.6
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
使用 dbms_xplan.display_cursor 查看 V$ACTIVE_SESSION_HISTORY 中的执行计划，发现一句
note
   - cpu costing is off (consider enabling it)

以前看资料没注意到有这一句。现网上查询也没看到具体的解释。
希望Maclean或其他人能给解释下
如何开启 cpu costing
cpu costing off 与 cpu costing on 对sql执行有何影响

yanshoupeng

1. SQL> select
   
2.   2    x.ksppinm  name,
   
3.   3    y.ksppstvl  value,
   
4.   4    y.ksppstdf  isdefault,
   
5.   5    decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,
   
6.   6    decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj
   
7.   7  from
   
8.   8    sys.x$ksppi x,
   
9.   9    sys.x$ksppcv y
   
10. 10  where
    
11. 11    x.inst_id = userenv('Instance') and
    
12. 12    y.inst_id = userenv('Instance') and
    
13. 13    x.indx = y.indx and
    
14. 14    x.ksppinm like '%_&par%'
    
15. 15  order by
    
16. 16    translate(x.ksppinm, ' _', ' ')
    
17. 17  /
    
18. 输入 par 的值:  model
    
19. 原值   14:   x.ksppinm like '%_&par%'
    
20. 新值   14:   x.ksppinm like '%_model%'
    
21. 
    
22. NAME                           VALUE                     ISDEFAULT ISMOD      ISADJ
    
23. ------------------------------ ------------------------- --------- ---------- -----
    
24. _optimizer_cost_model          CHOOSE                    TRUE      FALSE      FALSE
    
25. _sql_model_unfold_forloops     RUN_TIME                  TRUE      FALSE      FALSE
    
26. 
    
27. SQL>
    
28. SQL> set autot traceonly exp
    
29. SQL> select * from dual;
    
30. 
    
31. 执行计划
    
32. ----------------------------------------------------------
    
33. Plan hash value: 272002086
    
34. 
    
35. --------------------------------------------------------------------------
    
36. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
37. --------------------------------------------------------------------------
    
38. |   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
    
39. |   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
    
40. --------------------------------------------------------------------------
    
41. 
    
42. SQL> alter session set "_optimizer_cost_model"=io;
    
43. 
    
44. 会话已更改。
    
45. 
    
46. SQL> select * from dual;
    
47. 
    
48. 执行计划
    
49. ----------------------------------------------------------
    
50. Plan hash value: 272002086
    
51. 
    
52. ----------------------------------------------------------
    
53. | Id  | Operation         | Name | Rows  | Bytes | Cost  |
    
54. ----------------------------------------------------------
    
55. |   0 | SELECT STATEMENT  |      |     1 |     2 |     2 |
    
56. |   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2 |
    
57. ----------------------------------------------------------
    
58. 
    
59. Note
    
60. -----
    
61.    - cpu costing is off (consider enabling it)
    
62. 
    
63. SQL>
    

复制代码

yanshoupeng

COST=IOCOST+CPUCOST
禁止了CPU COST ,CBO在计算成本的时候 ，不会用到
CPUSPEEDNW
IOSEEKTIM
IOTFRSPEED
这些信息，计算出来的成本比原来小点。

这些信息可以从aux_stats$查询到。

26856649

yanshoupeng 发表于 2013-1-18 11:01
COST=IOCOST+CPUCOST
禁止了CPU COST ,CBO在计算成本的时候 ，不会用到
CPUSPEEDNW

我的参数是CHOOSE,没人动过，workload是发现cpu costint is off后才统计的，原来是noworkload

1. Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
   
2. Connected as SYS
   
3. 
   
4. 
   
5. SQL>
   
6. SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
   
7.   2  FROM SYS.x$ksppi x, SYS.x$ksppcv y
   
8.   3  WHERE x.inst_id = USERENV ('Instance')
   
9.   4  AND y.inst_id = USERENV ('Instance')
   
10.   5  AND x.indx = y.indx
    
11.   6  AND x.ksppinm LIKE '%&par%'
    
12.   7  /
    
13. 
    
14. NAME                                                                             VALUE                                                                            DESCRIB
    
15. -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    
16. _optimizer_cost_model                                                            CHOOSE                                                                           optimizer cost model
    
17. 
    
18. SQL>

复制代码

1. SQL> select pname, pval1,pval2,sname from sys.aux_stats$;
   
2. 
   
3. PNAME                               PVAL1 PVAL2                                                                            SNAME
   
4. ------------------------------ ---------- -------------------------------------------------------------------------------- ------------------------------
   
5. STATUS                                    COMPLETED                                                                        SYSSTATS_INFO
   
6. DSTART                                    01-18-2013 09:23                                                                 SYSSTATS_INFO
   
7. DSTOP                                     01-18-2013 10:23                                                                 SYSSTATS_INFO
   
8. FLAGS                                   0                                                                                  SYSSTATS_INFO
   
9. CPUSPEEDNW                       2069.392                                                                                  SYSSTATS_MAIN
   
10. IOSEEKTIM                           8.893                                                                                  SYSSTATS_MAIN
    
11. IOTFRSPEED                       26253.22                                                                                  SYSSTATS_MAIN
    
12. SREADTIM                        99299.138                                                                                  SYSSTATS_MAIN
    
13. MREADTIM                        18765.121                                                                                  SYSSTATS_MAIN
    
14. CPUSPEED                             2083                                                                                  SYSSTATS_MAIN
    
15. MBRC                                   15                                                                                  SYSSTATS_MAIN
    
16. MAXTHR                          642671616                                                                                  SYSSTATS_MAIN
    
17. SLAVETHR                         10209280                                                                                  SYSSTATS_MAIN
    
18. 
    
19. 13 rows selected
    
20. 
    
21. SQL>

复制代码

26856649

cpu costing is off的语句是这种

1. INSERT INTO table( ...) VALUES ( :B26 ,:B25 ,:B24 ,:B23 ,:B22 ,:B21 ,:B20 ,:B19 , :B18 ,:B17 ,:B16 ,:B15 ,:B14 ,:B13 , :B12 ,:B11 ,:B10 ,:B9 , :B8 ,:B7 ,:B6 ,:B5 ,:B4 ,:B3 , :B2 ,:B1 )

复制代码

26856649

现象重现的语句，及10053 trace文件 如下

1. DROP TABLE TEST
   
2. /
   
3. CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 1<0
   
4. /
   
5. BEGIN
   
6.   FOR CUR IN (SELECT * FROM DBA_OBJECTS WHERE ROWNUM <= 10000) LOOP
   
7.     INSERT INTO TEST
   
8.       (OWNER,
   
9.        OBJECT_NAME,
   
10.        SUBOBJECT_NAME,
    
11.        OBJECT_ID,
    
12.        DATA_OBJECT_ID,
    
13.        OBJECT_TYPE,
    
14.        CREATED,
    
15.        LAST_DDL_TIME,
    
16.        TIMESTAMP,
    
17.        STATUS,
    
18.        TEMPORARY,
    
19.        GENERATED,
    
20.        SECONDARY,
    
21.        NAMESPACE,
    
22.        EDITION_NAME)
    
23.     VALUES
    
24.       (CUR.OWNER,
    
25.        CUR.OBJECT_NAME,
    
26.        CUR.SUBOBJECT_NAME,
    
27.        CUR.OBJECT_ID,
    
28.        CUR.DATA_OBJECT_ID,
    
29.        CUR.OBJECT_TYPE,
    
30.        CUR.CREATED,
    
31.        CUR.LAST_DDL_TIME,
    
32.        CUR.TIMESTAMP,
    
33.        CUR.STATUS,
    
34.        CUR.TEMPORARY,
    
35.        CUR.GENERATED,
    
36.        CUR.SECONDARY,
    
37.        CUR.NAMESPACE,
    
38.        CUR.EDITION_NAME);
    
39.     COMMIT;
    
40.   END LOOP;
    
41. END;
    
42. /
    
43. SELECT * FROM v$sql v WHERE v.SQL_TEXT LIKE '%TEST%'
    
44. /
    
45. select plan_table_output from table(dbms_xplan.display_cursor('39qtntf5j6nch',null,'peeked_binds'));

复制代码