|
- select dbms_stats.get_prefs(pname => 'STALE_PERCENT')
- from dual;
-
- select * from dba_tab_stat_prefs p where p.owner='SH' and p.table_name='SALES';
-
- select dbms_stats.get_prefs(pname=>'STALE_PERCENT', ownname => 'SH',
- tabname => 'SALES') from dual;
-
- begin
- dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','13');
- end;
- begin
- dbms_stats.set_global_prefs(pname => 'STALE_PERCENT',pvalue => 9);
- end;
复制代码- begin
- dbms_stats.set_table_prefs('SH','SALES','METHOD_OPT',
- 'for all columns size auto for columns amount_sold size 254');
- end;
复制代码
for all columns size skewonly
--------------------------------------------------
动态采样和pending area 还是有关系的:
- SQL> ! vim show_dynamic_stats.sql
- SQL> @show_dynamic_stats.sql
- SQL> -- Try without dynamic sampling
- SQL> --
- SQL> explain plan for select * from tabjfv where c1 = 1 and c2 = 1;
- Explained.
- SQL>
- SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 349803950
- --------------------------------------------
- | Id | Operation | Name | Rows |
- --------------------------------------------
- | 0 | SELECT STATEMENT | | 1 |
- | 1 | TABLE ACCESS FULL| TABJFV | 1 |
- --------------------------------------------
- 8 rows selected.
- SQL>
- SQL>
- SQL> -- Try the queries with dynamic sampling level 3. Dynamic sampling will not
- SQL> -- kick in. It will use multi column stats and produce the same estimate
- SQL> -- (100 rows) as before.
- SQL> explain plan for select /*+ dynamic_sampling(3) */ * from tabjfv where c1 = 1 and c2 = 1;
- Explained.
- SQL>
- SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 349803950
- --------------------------------------------
- | Id | Operation | Name | Rows |
- --------------------------------------------
- | 0 | SELECT STATEMENT | | 200 |
- | 1 | TABLE ACCESS FULL| TABJFV | 200 |
- --------------------------------------------
- 8 rows selected.
- SQL>
- SQL>
- SQL> -- Try with level 4. Dynamic sampling kicks in. we should see the improved
- SQL> -- estimate (200 rows)
- SQL> explain plan for select /*+ dynamic_sampling(4) */ * from tabjfv where c1 = 1 and c2 = 1;
- Explained.
- SQL>
- SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 349803950
- --------------------------------------------
- | Id | Operation | Name | Rows |
- --------------------------------------------
- | 0 | SELECT STATEMENT | | 200 |
- | 1 | TABLE ACCESS FULL| TABJFV | 200 |
- --------------------------------------------
- 8 rows selected.
- SQL>
- SQL> show parameter optimi
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- optimizer_capture_sql_plan_baselines boolean FALSE
- optimizer_dynamic_sampling integer 0
- optimizer_features_enable string 11.2.0.1
- optimizer_index_caching integer 0
- optimizer_index_cost_adj integer 100
- optimizer_mode string ALL_ROWS
- optimizer_secure_view_merging boolean TRUE
- optimizer_use_invisible_indexes boolean FALSE
- optimizer_use_pending_statistics boolean FALSE
- optimizer_use_sql_plan_baselines boolean TRUE
- plsql_optimize_level integer 2
- SQL> alter session set optimizer_use_pending_statistics=true;
- Session altered.
- SQL> @show_dynamic_stats.sql
- SQL> -- Try without dynamic sampling
- SQL> --
- SQL> explain plan for select * from tabjfv where c1 = 1 and c2 = 1;
- Explained.
- SQL>
- SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 349803950
- --------------------------------------------
- | Id | Operation | Name | Rows |
- --------------------------------------------
- | 0 | SELECT STATEMENT | | 100 |
- | 1 | TABLE ACCESS FULL| TABJFV | 100 |
- --------------------------------------------
- 8 rows selected.
- SQL>
- SQL>
- SQL> -- Try the queries with dynamic sampling level 3. Dynamic sampling will not
- SQL> -- kick in. It will use multi column stats and produce the same estimate
- SQL> -- (100 rows) as before.
- SQL> explain plan for select /*+ dynamic_sampling(3) */ * from tabjfv where c1 = 1 and c2 = 1;
- Explained.
- SQL>
- SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 349803950
- --------------------------------------------
- | Id | Operation | Name | Rows |
- --------------------------------------------
- | 0 | SELECT STATEMENT | | 100 |
- | 1 | TABLE ACCESS FULL| TABJFV | 100 |
- --------------------------------------------
- 8 rows selected.
- SQL>
- SQL>
- SQL> -- Try with level 4. Dynamic sampling kicks in. we should see the improved
- SQL> -- estimate (200 rows)
- SQL> explain plan for select /*+ dynamic_sampling(4) */ * from tabjfv where c1 = 1 and c2 = 1;
- Explained.
- SQL>
- SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 349803950
- --------------------------------------------
- | Id | Operation | Name | Rows |
- --------------------------------------------
- | 0 | SELECT STATEMENT | | 200 |
- | 1 | TABLE ACCESS FULL| TABJFV | 200 |
- --------------------------------------------
- 8 rows selected.
- SQL>
- SQL>
复制代码
|
|