课程第28次（2018-08-17星期五）
select dbms_stats.get_prefs(pname => 'STALE_PERCENT')from dual;
select* fromdba_tab_stat_prefs pwhere 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 columnssize 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>
页:
[1]