Bo's Oracle Station

查看: 2062|回复: 0

课程第28次(2018-08-17星期五)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-8-17 19:56:45 | 显示全部楼层 |阅读模式
  1. select   dbms_stats.get_prefs(pname => 'STALE_PERCENT')
  2. from dual;

  3. select  * from  dba_tab_stat_prefs p  where p.owner='SH' and p.table_name='SALES';

  4. select   dbms_stats.get_prefs(pname=>'STALE_PERCENT', ownname => 'SH',
  5. tabname => 'SALES') from dual;

  6. begin
  7.    dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','13');
  8. end;

  9. begin
  10.   dbms_stats.set_global_prefs(pname => 'STALE_PERCENT',pvalue => 9);
  11. end;
复制代码
  1. begin
  2.    dbms_stats.set_table_prefs('SH','SALES','METHOD_OPT',
  3.       'for all columns size auto for columns amount_sold size 254');
  4. end;
复制代码

for all columns  size skewonly

--------------------------------------------------
动态采样和pending area 还是有关系的:

  1. SQL> ! vim show_dynamic_stats.sql

  2. SQL> @show_dynamic_stats.sql
  3. SQL> -- Try without dynamic sampling
  4. SQL> --
  5. SQL> explain plan for select * from tabjfv where c1 = 1 and c2 = 1;

  6. Explained.

  7. SQL>
  8. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

  9. PLAN_TABLE_OUTPUT
  10. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  11. Plan hash value: 349803950

  12. --------------------------------------------
  13. | Id  | Operation          | Name   | Rows  |
  14. --------------------------------------------
  15. |   0 | SELECT STATEMENT  |           |         1 |
  16. |   1 |  TABLE ACCESS FULL| TABJFV |         1 |
  17. --------------------------------------------

  18. 8 rows selected.

  19. SQL>
  20. SQL>
  21. SQL> -- Try the queries with dynamic sampling level 3. Dynamic sampling will not
  22. SQL> -- kick in. It will use multi column stats and produce the same estimate
  23. SQL> -- (100 rows) as before.
  24. SQL> explain plan for select /*+ dynamic_sampling(3) */ * from tabjfv where c1 = 1 and c2 = 1;

  25. Explained.

  26. SQL>
  27. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

  28. PLAN_TABLE_OUTPUT
  29. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  30. Plan hash value: 349803950

  31. --------------------------------------------
  32. | Id  | Operation          | Name   | Rows  |
  33. --------------------------------------------
  34. |   0 | SELECT STATEMENT  |           |   200 |
  35. |   1 |  TABLE ACCESS FULL| TABJFV |   200 |
  36. --------------------------------------------

  37. 8 rows selected.

  38. SQL>
  39. SQL>
  40. SQL> -- Try with level 4. Dynamic sampling kicks in. we should see the improved
  41. SQL> -- estimate (200 rows)
  42. SQL> explain plan for select /*+ dynamic_sampling(4) */ * from tabjfv where c1 = 1 and c2 = 1;

  43. Explained.

  44. SQL>
  45. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

  46. PLAN_TABLE_OUTPUT
  47. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  48. Plan hash value: 349803950

  49. --------------------------------------------
  50. | Id  | Operation          | Name   | Rows  |
  51. --------------------------------------------
  52. |   0 | SELECT STATEMENT  |           |   200 |
  53. |   1 |  TABLE ACCESS FULL| TABJFV |   200 |
  54. --------------------------------------------

  55. 8 rows selected.

  56. SQL>
  57. SQL> show parameter optimi

  58. NAME                                     TYPE         VALUE
  59. ------------------------------------ ----------- ------------------------------
  60. optimizer_capture_sql_plan_baselines boolean         FALSE
  61. optimizer_dynamic_sampling             integer         0
  62. optimizer_features_enable             string         11.2.0.1
  63. optimizer_index_caching              integer         0
  64. optimizer_index_cost_adj             integer         100
  65. optimizer_mode                             string         ALL_ROWS
  66. optimizer_secure_view_merging             boolean         TRUE
  67. optimizer_use_invisible_indexes      boolean         FALSE
  68. optimizer_use_pending_statistics     boolean         FALSE
  69. optimizer_use_sql_plan_baselines     boolean         TRUE
  70. plsql_optimize_level                     integer         2
  71. SQL> alter session set optimizer_use_pending_statistics=true;

  72. Session altered.

  73. SQL> @show_dynamic_stats.sql
  74. SQL> -- Try without dynamic sampling
  75. SQL> --
  76. SQL> explain plan for select * from tabjfv where c1 = 1 and c2 = 1;

  77. Explained.

  78. SQL>
  79. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

  80. PLAN_TABLE_OUTPUT
  81. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  82. Plan hash value: 349803950

  83. --------------------------------------------
  84. | Id  | Operation          | Name   | Rows  |
  85. --------------------------------------------
  86. |   0 | SELECT STATEMENT  |           |   100 |
  87. |   1 |  TABLE ACCESS FULL| TABJFV |   100 |
  88. --------------------------------------------

  89. 8 rows selected.

  90. SQL>
  91. SQL>
  92. SQL> -- Try the queries with dynamic sampling level 3. Dynamic sampling will not
  93. SQL> -- kick in. It will use multi column stats and produce the same estimate
  94. SQL> -- (100 rows) as before.
  95. SQL> explain plan for select /*+ dynamic_sampling(3) */ * from tabjfv where c1 = 1 and c2 = 1;

  96. Explained.

  97. SQL>
  98. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

  99. PLAN_TABLE_OUTPUT
  100. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  101. Plan hash value: 349803950

  102. --------------------------------------------
  103. | Id  | Operation          | Name   | Rows  |
  104. --------------------------------------------
  105. |   0 | SELECT STATEMENT  |           |   100 |
  106. |   1 |  TABLE ACCESS FULL| TABJFV |   100 |
  107. --------------------------------------------

  108. 8 rows selected.

  109. SQL>
  110. SQL>
  111. SQL> -- Try with level 4. Dynamic sampling kicks in. we should see the improved
  112. SQL> -- estimate (200 rows)
  113. SQL> explain plan for select /*+ dynamic_sampling(4) */ * from tabjfv where c1 = 1 and c2 = 1;

  114. Explained.

  115. SQL>
  116. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

  117. PLAN_TABLE_OUTPUT
  118. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  119. Plan hash value: 349803950

  120. --------------------------------------------
  121. | Id  | Operation          | Name   | Rows  |
  122. --------------------------------------------
  123. |   0 | SELECT STATEMENT  |           |   200 |
  124. |   1 |  TABLE ACCESS FULL| TABJFV |   200 |
  125. --------------------------------------------

  126. 8 rows selected.

  127. SQL>
  128. SQL>
复制代码







回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 19:13 , Processed in 0.039437 second(s), 24 queries .

快速回复 返回顶部 返回列表