Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

131

积分

1

好友

13

主题
1#
发表于 2012-4-17 12:14:26 | 查看: 7569| 回复: 12
  1. SQL> var a varchar2(10);
  2. SQL> exec :a:='INVALID';

  3. PL/SQL procedure successfully completed.

  4. SQL> set autotrace on
  5. SQL> select object_id,status from t1 where status=:a;
  6. OBJECT_ID STATUS
  7. ---------- -------
  8.         92 INVALID
  9.         93 INVALID
  10.         94 INVALID
  11.         95 INVALID
  12.         96 INVALID
  13.         97 INVALID
  14.         98 INVALID
  15.         99 INVALID
  16.        100 INVALID
  17.      73871 INVALID
  18.      73846 INVALID




  19. Execution Plan
  20. ----------------------------------------------------------
  21. Plan hash value: 3617692013

  22. --------------------------------------------------------------------------
  23. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  24. --------------------------------------------------------------------------
  25. |   0 | SELECT STATEMENT  |      | 23680 |   462K|   284   (1)| 00:00:04 |
  26. |*  1 |  TABLE ACCESS FULL| T1   | 23680 |   462K|   284   (1)| 00:00:04 |
  27. --------------------------------------------------------------------------

  28. Predicate Information (identified by operation id):
  29. ---------------------------------------------------

  30.    1 - filter("STATUS"=:A)


  31. Statistics
  32. ----------------------------------------------------------
  33.         403  recursive calls
  34.           0  db block gets
  35.          99  consistent gets
  36.          19  physical reads
  37.           0  redo size
  38.        2543  bytes sent via SQL*Net to client
  39.         589  bytes received via SQL*Net from client
  40.           8  SQL*Net roundtrips to/from client
  41.           7  sorts (memory)
  42.           0  sorts (disk)
  43.          99  rows processed

  44. SQL> select status,count(1) from t1 group by status;

  45. STATUS    COUNT(1)
  46. ------- ----------
  47. IND              2
  48. VALID        70939
  49. INVALID         99

  50. SQL> select object_id ,status from t1 where status='INVALID';

  51. OBJECT_ID STATUS
  52. ---------- -------
  53.         92 INVALID
  54.         93 INVALID
  55.         94 INVALID
  56.         95 INVALID
  57.         96 INVALID
  58.         97 INVALID
  59.         98 INVALID
  60.         99 INVALID
  61.        100 INVALID
  62.      73871 INVALID
  63.      73846 INVALID
  64. Execution Plan
  65. ----------------------------------------------------------
  66. Plan hash value: 14748218

  67. --------------------------------------------------------------------------------
  68. -------

  69. | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
  70. --------------------------------------------------------------------------------

  71. |   0 | SELECT STATEMENT            |         |    79 |  1580 |     3   (0)| 00:00:01 |
  72. |   1 |  TABLE ACCESS BY INDEX ROWID| T1      |    79 |  1580 |     3   (0)| 00:00:01 |
  73. |*  2 |   INDEX RANGE SCAN          | INDE_T1 |    79 |       |     1   (0)| 00:00:01 |

  74. --------------------------------------------------------------------------------
  75. Predicate Information (identified by operation id):
  76. ---------------------------------------------------

  77.    2 - access("STATUS"='INVALID')

  78. SQL>
复制代码


为什么使用绑定变量的时候执行计划是全表扫描而带入真实的值就走索引?难道是autotrace的问题吗?如果是的话这种情况应该查找真正的执行计划?麻烦刘总解答一下多谢
2#
发表于 2012-4-17 15:07:17
看了一些文章说的是删除直方图信息再使用 绑定变量的时候可以走正确的执行计划,但是我测试的还是走第一次生成的执行计划....不知是因为版本问题还是我测试的问题,刘总能否解答一下啊多谢了

回复 只看该作者 道具 举报

3#
发表于 2012-4-17 16:35:21
把   :a  改为 :b 试一下

回复 只看该作者 道具 举报

4#
发表于 2012-4-17 16:39:23
  1. SQL> select * from v$version where rownum<2;

  2. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
复制代码


表信息是刚刚收集的

回复 只看该作者 道具 举报

5#
发表于 2012-4-17 16:42:16
表结构拿出来看一下呗

回复 只看该作者 道具 举报

6#
发表于 2012-4-17 17:00:29
表结果就是dba_objects

回复 只看该作者 道具 举报

7#
发表于 2012-4-17 17:01:54
10.2.0.1.0正常,不用绑定变量也不会走索引

回复 只看该作者 道具 举报

8#
发表于 2012-4-17 18:16:54
我来解答一下 我在11.2.0.3.0 数据库上测试的。
测试有点长,我就先给说结论:
可能 Oracle 11g 在autot trace 分析SQL语句带绑定变量时假,可能会显示错误执行计划 。
  1. SQL> select * from v$version;

  2. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  5. PL/SQL Release 11.2.0.3.0 - Production
  6. CORE    11.2.0.3.0      Production
  7. TNS for Linux: Version 11.2.0.3.0 - Production
  8. NLSRTL Version 11.2.0.3.0 - Production

  9. SQL>



  10. SQL> create table t2 as select * from t1;

  11. Table created.

  12. SQL>
  13. SQL> create index ind_t2_status on t2(status) ;

  14. Index created.

  15. SQL>
  16. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('ZYF','T2',method_opt => 'for all indexed columns  size skewonly',cascade => TRUE,no_invalidate => FALSE);


  17. PL/SQL procedure successfully completed.


  18. SQL> select status,count(1) from t1 group by status;

  19. STATUS    COUNT(1)
  20. ------- ----------
  21. INVALID        130
  22. VALID      2016984
  23. IND             62


  24. SQL>  var a varchar2(10);
  25. SQL>  exec :a:='INVALID';

  26. PL/SQL procedure successfully completed.

  27. SQL>
  28. SQL>  set autot trace
  29. SELECT  object_id,status   FROM  T2  WHERE  STATUS = :a;SQL>

  30. 130 rows selected.


  31. Execution Plan
  32. ----------------------------------------------------------
  33. Plan hash value: 1513984157

  34. --------------------------------------------------------------------------
  35. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  36. --------------------------------------------------------------------------
  37. |   0 | SELECT STATEMENT  |      |   672K|    12M|  7898   (1)| 00:01:35 |
  38. |*  1 |  TABLE ACCESS FULL| T2   |   672K|    12M|  7898   (1)| 00:01:35 |
  39. --------------------------------------------------------------------------

  40. Predicate Information (identified by operation id):
  41. ---------------------------------------------------

  42.    1 - filter("STATUS"=:A)


  43. Statistics
  44. ----------------------------------------------------------
  45.           1  recursive calls
  46.           0  db block gets
  47.         112  consistent gets               --->这个很奇怪,表那么大,如果走全表的话,consistent gets 这个值太不正常了
  48.           0  physical reads
  49.           0  redo size
  50.        3378  bytes sent via SQL*Net to client
  51.         612  bytes received via SQL*Net from client
  52.          10  SQL*Net roundtrips to/from client
  53.           0  sorts (memory)
  54.           0  sorts (disk)
  55.         130  rows processed

  56. --查看

  57. SQL> set lines 180
  58. SQL>
  59. SQL>
  60. SQL> select object_id,status   FROM t2 where status='INVALID';

  61. 130 rows selected.


  62. Execution Plan
  63. ----------------------------------------------------------
  64. Plan hash value: 1740296156

  65. ---------------------------------------------------------------------------------------------
  66. | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
  67. ---------------------------------------------------------------------------------------------
  68. |   0 | SELECT STATEMENT            |               |   361 |  7220 |     9   (0)| 00:00:01 |
  69. |   1 |  TABLE ACCESS BY INDEX ROWID| T2            |   361 |  7220 |     9   (0)| 00:00:01 |
  70. |*  2 |   INDEX RANGE SCAN          | IND_T2_STATUS |   361 |       |     3   (0)| 00:00:01 |
  71. ---------------------------------------------------------------------------------------------

  72. Predicate Information (identified by operation id):
  73. ---------------------------------------------------

  74.    2 - access("STATUS"='INVALID')


  75. Statistics
  76. ----------------------------------------------------------
  77.           1  recursive calls
  78.           0  db block gets
  79.         112  consistent gets              -->这个竟然和全表扫描的值一样
  80.           0  physical reads
  81.           0  redo size
  82.        3378  bytes sent via SQL*Net to client
  83.         612  bytes received via SQL*Net from client
  84.          10  SQL*Net roundtrips to/from client
  85.           0  sorts (memory)
  86.           0  sorts (disk)
  87.         130  rows processed
复制代码

[ 本帖最后由 不了峰 于 2012-4-17 18:27 编辑 ]

11g_autotrace_绑定变量.txt

9.07 KB, 下载次数: 810

回复 只看该作者 道具 举报

9#
发表于 2012-4-17 18:24:04
2) --接下来我们看一下强制全表扫描的情况
  1. SQL> SELECT  /*+ full(T2) */*    FROM  T2  WHerE  STATUS = :a;

  2. 130 rows selected.


  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 1513984157

  6. --------------------------------------------------------------------------
  7. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  8. --------------------------------------------------------------------------
  9. |   0 | SELECT STATEMENT  |      |   672K|    62M|  7903   (1)| 00:01:35 |
  10. |*  1 |  TABLE ACCESS FULL| T2   |   672K|    62M|  7903   (1)| 00:01:35 |
  11. --------------------------------------------------------------------------

  12. Predicate Information (identified by operation id):
  13. ---------------------------------------------------

  14.    1 - filter("STATUS"=:A)


  15. Statistics
  16. ----------------------------------------------------------
  17.           1  recursive calls
  18.           0  db block gets
  19.       28743  consistent gets                          -->从这个看,说明第一个full t2的全表扫描显示的有问题.
  20.           0  physical reads
  21.           0  redo size
  22.       10986  bytes sent via SQL*Net to client
  23.         612  bytes received via SQL*Net from client
  24.          10  SQL*Net roundtrips to/from client
  25.           0  sorts (memory)
  26.           0  sorts (disk)
  27.         130  rows processed

  28. SQL>


  29. --这里我们做个10046的trace跟踪一下 最原始的带绑定变量的sql
  30. 注意 我把select 从大写改为小写,为生成一个新的执行计划

  31. sql> exit

  32. sqlplus   zyf/zyf


  33. SQL>
  34. SQL> alter session set events '10046 trace name context forever, level 12';

  35. Session altered.

  36. SQL>  select   object_id,status   from   T2  WHERE  STATUS = :a;

  37. OBJECT_ID STATUS
  38. ---------- -------
  39.      56921 INVALID
  40.      56966 INVALID
  41.      64886 INVALID
  42.      ....
  43.      

  44. SQL>
  45. SQL> alter session set events '10046 trace name context off';

  46. Session altered.


  47. --分析trace

  48. tkprof nitmsdb1_ora_1462.trc zyf.log

  49. $ more zyf.log

  50.         select   object_id,status   
  51.         from
  52.            T2  WHERE  STATUS = :a
  53.        
  54.        
  55.         call     count       cpu    elapsed       disk      query    current        rows
  56.         ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  57.         Parse        1      0.00       0.00          0          0          0           0
  58.         Execute      1      0.00       0.00          0          0          0           0
  59.         Fetch       10      0.00       0.00          0        112          0         130
  60.         ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  61.         total       12      0.00       0.00          0        112          0         130
  62.        
  63.         Misses in library cache during parse: 1
  64.         Misses in library cache during execute: 1
  65.         Optimizer mode: ALL_ROWS
  66.         Parsing user id: 59  
  67.         Number of plan statistics captured: 1
  68.        
  69.         Rows (1st) Rows (avg) Rows (max)  Row Source Operation
  70.         ---------- ---------- ----------  ---------------------------------------------------
  71.                130        130        130  TABLE ACCESS BY INDEX ROWID T2 (cr=112 pr=0 pw=0 time=3113 us cost=9 size=722
  72.         0 card=361)
  73.                130        130        130   INDEX RANGE SCAN IND_T2_STATUS (cr=12 pr=0 pw=0 time=244 us cost=3 size=0 ca
  74.         rd=361)(object id 65914)     
  75.        
  76.         发现query=112 ,访问表不是全表扫描,而是访问索引
  77.        
  78.        
  79.         所以上面的分析,可能说明11g 在autot trace SQL语句绑定变量时,显示的可能是错误 的信息
复制代码

回复 只看该作者 道具 举报

10#
发表于 2012-4-17 18:24:52
3) 我们再通过另一个方法证明

  1. exit

  2. sqlplus zyf/zyf


  3. SQL>  var a varchar2(10);
  4. SQL>  exec :a:='INVALID';

  5. PL/SQL procedure successfully completed.

  6. SQL>  select  object_id,status   from   T2  WHERE  STATUS = :a;

  7. OBJECT_ID STATUS
  8. ---------- -------
  9.      56921 INVALID
  10.      56966 INVALID
  11.      64886 INVALID
  12.      ........

  13. SQL> set lines 180
  14. SQL>  SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));

  15. PLAN_TABLE_OUTPUT
  16. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  17. SQL_ID  3t6wf2h7agmyh, child number 0
  18. -------------------------------------
  19. select  object_id,status   from   T2  WHERE  STATUS = :a

  20. Plan hash value: 1740296156

  21. -------------------------------------------------------------------------------------------------------
  22. | Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
  23. -------------------------------------------------------------------------------------------------------
  24. |   0 | SELECT STATEMENT            |               |      1 |        |    130 |00:00:00.01 |     112 |
  25. |   1 |  TABLE ACCESS BY INDEX ROWID| T2            |      1 |    361 |    130 |00:00:00.01 |     112 |

  26. PLAN_TABLE_OUTPUT
  27. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  28. |*  2 |   INDEX RANGE SCAN          | IND_T2_STATUS |      1 |    361 |    130 |00:00:00.01 |      12 |
  29. -------------------------------------------------------------------------------------------------------

  30. Predicate Information (identified by operation id):
  31. ---------------------------------------------------

  32.    2 - access("STATUS"=:A)


  33. 19 rows selected.

  34. SQL>
  35. 说明带绑定变量的这句SQL也是走索引扫描 .
复制代码

回复 只看该作者 道具 举报

11#
发表于 2012-4-17 18:50:22
不了峰  同学的 演示 很好!

也可以参考 autotrace在绑定变量情况下不准确的问题 http://www.oracledatabase12g.com ... 97%AE%E9%A2%98.html


autotrace 给出的执行计划未必是真实的, 最可靠的是 10046 、10053 trace 中的real execution plan

回复 只看该作者 道具 举报

12#
发表于 2012-4-17 22:04:59
原来刘大09年已经搞清楚这事了

班门弄斧了

回复 只看该作者 道具 举报

13#
发表于 2012-4-18 10:04:04
多谢解答啊!!!!!

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-25 00:42 , Processed in 0.054625 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569