Search

OakieTags

Who's online

There are currently 0 users and 22 guests online.

Recent comments

Oakies Blog Aggregator

Dynamic Sampling And Indexes

There is a lot more to say about Dynamic Sampling and indexes, and I'll try to cover these basics in my Dynamic Sampling series on AllThingsOracle.com, but two recent discussions on the OTN forums and on Charles Hooper's blog prompted me to publish this blog post.

These discussions revolved around the following issues with Dynamic Sampling and indexes:

1. CREATE INDEX On Empty Table

There is an interesting change in behaviour that has been introduced with release 11.2. Whereas pre-11.2 versions do not automatically gather index statistics on empty tables, 11.2 does so. So a simple CREATE TABLE followed by a CREATE INDEX command (or an ALTER TABLE add constraint that implicitly/explictly creates indexes) will now lead to a table without statistics, but index statistics with every value set to 0 (rows, distinct keys, clustering factor, leaf blocks etc.)

Since Oracle 10g a CREATE INDEX command automatically computes statistics for an index (since Oracle 9i you could add optionally a COMPUTE STATISTICS clause to the CREATE INDEX command). The interesting point is that there is no "NOCOMPUTE STATISTICS" clause, so you cannot prevent this from happening via the syntax.

There is a way to prevent it, but only indirectly by locking the table statistics. Funny enough, if the table statistics are locked, there is again no way to overwrite the lock by using a FORCE keyword or similar as part of the CREATE INDEX command, whereas DBMS_STATS offers such a FORCE option.

Note that creating constraints requiring indexes as part of the CREATE TABLE command shows a different behaviour: The indexes do not have statistics in this case.

This shouldn't be too relevant for real-life cases, but is probably more important to setups of test cases / demonstrations. Of course it can become relevant to real-life deployment tasks that add new objects to a database. If statistics are not handled properly afterwards you now end up with tables missing statistics but indexes with 0 statistics.

Of course when deliberately leaving a table without statistics you need to do something about it anyway, because otherwise the default statistics job (since 10g) will turn this table into a potential threat by gathering statistics during the maintenance window where such tables might be in some state that is not representative for the actual workload during processing.

Either the default statistics job has to be disabled or re-configured via the AUTOSTATS_TARGET option of the SET_GLOBAL_PREFS / SET_PARAM procedures of DBMS_STATS, or the table statistics need to be locked individually via LOCK_TABLE_STATS.

So a reasonable order of commands for such a table at deployment time could look like this:

CREATE TABLE...

EXEC DBMS_STATS.LOCK_TABLE_STATS(...)

CREATE INDEX...

This way the statistics will be handled consistently for both table and indexes.

Note that all this doesn't apply to Global Temporary Tables (GTTs) as they are not analyzed by the default statistics job and creating an index on them doesn't automatically gather statistics for that index either, even in 11.2.

The change introduced in 11.2 can be seen by simply trying the following in 11.2 and pre-11.2 versions to see the difference:

drop table t;

purge table t;

create table t (id number(*, 0) not null, id2 number(*, 0), filler varchar2(100));

create index t_idx on t (id);

select num_rows, blocks from user_tab_statistics where table_name = 'T';

select blevel, num_rows, leaf_blocks, distinct_keys, clustering_factor from user_ind_statistics where table_name = 'T';

2. Inconsistent Costing

The reason why this change in behaviour is particularly relevant is the fact that the optimizer behaves inconsistently if a table doesn't have statistics but a suitable index does.

If Dynamic Sampling gets used (very likely from 10g on because the table is missing statistics and the default level of 2 will trigger Dynamic Sampling for such a table in this case) the optimizer will not only obtain basic statistics information about the table but also overwrite the existing index statistics, but only partially.

The problem is that the existing index leaf blocks statistics get overwritten by the Dynamic Sampling code, but the clustering factor is not.

You can see this from the following optimizer trace snippet:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T6 Alias: T6 (NOT ANALYZED)
#Rows: 82 #Blks: 1 AvgRowLen: 100.00
Index Stats::
Index: IND_T6_C2 Col#: 2
LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00

Access path analysis for T6
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T6[T6]

*** 2012-02-17 16:28:49.182
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: IND_T6_C2, blocks=227
** Dynamic sampling index access candidate : IND_T6_C2
** Dynamic sampling updated table stats.: blocks=4529

So although the index has statistics gathered (there is no (NOT ANALYZED) mentioned for the index) the Dynamic Sampling updates the index stats "blocks".

This is not consistent with the expected behaviour of Dynamic Sampling, because by default it is not supposed to overwrite existing statistics. If you want to force Dynamic Sampling to do so you need to specify the (not really) documented table level hint DYNAMIC_SAMPLING_EST_CDN.

However, another pretty important number, the clustering factor of the index, doesn't get updated. Since the clustering factor is important for accessing the table via an index any execution plan that needs to visit the table in such a way will now be costed inconsistently: The index access part will be based on the index block statistics updated by Dynamic Sampling whereas the table access part will be costed using the clustering factor (and potentially other existing index statistics left unmodified by Dynamic Sampling).

This will potentially lead to rather unreasonable plans, as the following test case demonstrates:

set echo on linesize 200

drop table t;

purge table t;

create table t (
c1 number not null,
c2 number not null,
c3 varchar2(300) not null);

create index t_idx on t(c2);

create index t_pk on t(c1);

insert into
t
select
rownum as c1,
1 as c2,
rpad('A',300,'A') as c3
from
dual
connect by
level<=100000;

commit;

alter session set optimizer_dynamic_sampling = 8;

explain plan for
select * from t where c2 = 1;

select * from table(dbms_xplan.display);

select index_name, blevel, num_rows, leaf_blocks, distinct_keys, clustering_factor from user_ind_statistics where table_name = 'T';

exec dbms_stats.delete_index_stats(null, 't_idx', no_invalidate=>false)

explain plan for
select * from t where c2 = 1;

select * from table(dbms_xplan.display);

exec dbms_stats.gather_index_stats(null, 't_idx', no_invalidate=>false)

explain plan for
select /*+ index(t(c2)) */ * from t where c2 = 1;

select * from table(dbms_xplan.display);

I get the following execution plans from 11.2.0.1:

SQL>
SQL> alter session set optimizer_dynamic_sampling = 8;

Session altered.

SQL>
SQL> explain plan for
2 select * from t where c2 = 1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 16M| 177 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100K| 16M| 177 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | T_IDX | 100K| | 177 (0)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C2"=1)

Note
-----
- dynamic sampling used for this statement (level=8)

18 rows selected.

SQL>
SQL> select index_name, blevel, num_rows, leaf_blocks, distinct_keys, clustering_factor from user_ind_statistics where table_name = 'T';

INDEX_NAME BLEVEL NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
T_IDX 0 0 0 0 0
T_PK 0 0 0 0 0

SQL>
SQL> exec dbms_stats.delete_index_stats(null, 't_idx', no_invalidate=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
2 select * from t where c2 = 1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 16M| 978 (1)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100K| 16M| 978 (1)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | T_IDX | 100K| | 177 (0)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C2"=1)

Note
-----
- dynamic sampling used for this statement (level=8)

18 rows selected.

SQL>
SQL> exec dbms_stats.gather_index_stats(null, 't_idx', no_invalidate=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
2 select /*+ index(t(c2)) */ * from t where c2 = 1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 16M| 4533 (1)| 00:00:55 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100K| 16M| 4533 (1)| 00:00:55 |
|* 2 | INDEX RANGE SCAN | T_IDX | 100K| | 179 (2)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C2"=1)

Note
-----
- dynamic sampling used for this statement (level=8)

18 rows selected.

Notice how the optimizer for the first execution plan gets a reasonable cardinality estimate from the Dynamic Sampling (due to the increased level it is spot on in this case) but still thinks that reading 100,000 rows from a 100,000 table via an index is a good idea.

While the index access is costed reasonably based on the updated index blocks statistics, the table access is "cost-free" due to the clustering factor left unmodified at 0.

When deleting the index statistics we can see that a default clustering factor of 800 gets assumed (you can see this in the optimizer trace file), which still makes the index access more favourable.

Only with index statistics gathered a more reasonable plan gets selected.

There is a bug filed against 11.2 (bug no 12942119 "Suboptimal plan if index has statistics but table has no statistics and dynamic sampling occurs") that seems to address this issue, but it is not clear from the bug description what exactly the bug fix does. It hasn't made it into the 11.2.0.3 patch set but it is available as one-off patch and part of some Windows platform intermediate patch sets.

3. 11.2.0.3 Change In Behaviour

The 11.2.0.3 patch set introduces another interesting change in behaviour: As I've just demonstrated even with index statistics missing and hence consistent optimizer behaviour the default clustering factor assumed might still favour unreasonable execution plans.

There is a bug fix 12399886:"OPTIMIZER STILL USES DEFAULT INDEX STATISTICS AFTER DYNAMIC SAMPLING WAS USED" that is by default enabled in 11.2.0.3 that addresses this issue and uses a different clustering factor derived from the Dynamic Sampling results (it turns out to be based on the blocks of the table, not the rows, see below for more details).

This is a good thing in principle because the cost estimates now might be closer to reality, but as always this might cause a lot of headaches after applying the patch sets because execution plans might change. This applies of course only to those scenarios that rely on Dynamic Sampling and can make use indexes.

Note that the inconsistent behaviour described under 2. is still there in 11.2.0.3, so tables without statistics having index statistics gathered still don't mix very well.

Here are the execution plans that I got from 11.2.0.3 for above test case:

SQL> alter session set optimizer_dynamic_sampling = 8;

Session altered.

SQL>
SQL> explain plan for
2 select * from t where c2 = 1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98120 | 16M| 370 (0)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 98120 | 16M| 370 (0)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | T_IDX | 98120 | | 370 (0)| 00:00:05 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C2"=1)

Note
-----
- dynamic sampling used for this statement (level=8)

18 rows selected.

SQL>
SQL> select index_name, blevel, num_rows, leaf_blocks, distinct_keys, clustering_factor from user_ind_statistics where table_name = 'T';

INDEX_NAME BLEVEL NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
T_IDX 0 0 0 0 0
T_PK 0 0 0 0 0

SQL>
SQL> exec dbms_stats.delete_index_stats(null, 't_idx', no_invalidate=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
2 select * from t where c2 = 1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98120 | 16M| 1230 (1)| 00:00:15 |
|* 1 | TABLE ACCESS FULL| T | 98120 | 16M| 1230 (1)| 00:00:15 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("C2"=1)

Note
-----
- dynamic sampling used for this statement (level=8)

17 rows selected.

SQL>
SQL> explain plan for
2 select /*+ index(t(c2)) */ * from t where c2 = 1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98120 | 16M| 36602 (1)| 00:07:20 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 98120 | 16M| 36602 (1)| 00:07:20 |
|* 2 | INDEX RANGE SCAN | T_IDX | 98120 | | 370 (0)| 00:00:05 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C2"=1)

Note
-----
- dynamic sampling used for this statement (level=8)

18 rows selected.

SQL>
SQL> exec dbms_stats.gather_index_stats(null, 't_idx', no_invalidate=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
2 select /*+ index(t(c2)) */ * from t where c2 = 1;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98120 | 16M| 4898 (1)| 00:00:59 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 98120 | 16M| 4898 (1)| 00:00:59 |
|* 2 | INDEX RANGE SCAN | T_IDX | 98120 | | 371 (1)| 00:00:05 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C2"=1)

Note
-----
- dynamic sampling used for this statement (level=8)

18 rows selected.

Notice 11.2.0.3 suffers from the same cost-free table access with the zero index statistics in place, but after deleting the index statistics a full table scan will be chosen. It looks like that the new clustering factor is based on table blocks * 8 rather than a hard coded value of 800. Both constants (8, 800) might depend on the default block size - these tests were performed on 8KB.

Weather

I thought it was pretty cold in Sarajevo a couple of weeks ago, and therefore fairly mild in Minneapolis and Denver when the temperature was only just around freezing point – but after reading this report I don’t think I’m every going to say anything more about cold weather.

(Note: with my scientific/skeptic hat on, I have yet to be convinced that the story is completely true – there is one obvious weak point.)

 

Upgrades

A couple of weeks ago I posted a reference list of links to the bug fix notes for several of the most recent versions of Oracle - and several of the links recorded a surprisingly large number of clicks very rapidly, especially the 11.2.0.3 link. As a follow-up on the difficulties of upgrading, then, and with an insight into the number of enhancements and fixes to the optimizer that take place I decided to take a look at recent developments in the “fix control” list, and the “optimizer environment” parameters. Here’s a breakdown of the number of entries in recent versions of Oracle.

v$sys_optimizer_env / x$qkcesys

Version       v$ count       x$ count
10.2.0.3            25            194
10.2.0.5            25            207
11.1.0.7            41            270
11.2.0.3            49            321

v$system_fix_control / x$qksbgsys

Version          count
10.2.0.3            38
10.2.0.5           243
11.1.0.7           229
11.2.0.3           652

It’s interesting to note the difference between the number of parameters in the v$ and x$ versions of the optimizer environment – and when you see all the hidden parameters in the x$ it may give you some idea of how many things might explain a strange change in execution path on an upgrade. Just for reference, here’s a simple query, with output, from 11.2.0.3 (the commented column doesn’t exist in 10g):

set linesize 180
set trimspool on
set pagesize 60

column name		format a40
column feature		format a24
column value		format a15
column def_value	format a15

break on feature skip 1

select
--	FID_QKSCESYROW          feature,	-- vc64
	PNAME_QKSCESYROW	name,		-- vc40
	PVALUE_QKSCESYROW	value,		-- vc25
	DEFPVALUE_QKSCESYROW	def_value,	-- vc25
	KSPNUM_QKSCESYROW	parameter,	-- number
	PNUM_QKSCESYROW		opt_param,	-- number
	FLAGS_QKSCESYROW	flags		-- number
from
	X$QKSCESYS
order by
--	feature,
	name
;


FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_ALL                _add_stale_mv_to_dependency_list         true            true                  2009        140          6
                         _bloom_filter_enabled                    true            true                  2195        160          7
                         _bloom_folding_enabled                   true            true                  2200        265          7
                         _bloom_minmax_enabled                    true            true                  2387        314          6
                         _bloom_pruning_enabled                   true            true                  2256        211          7
                         _db_file_optimizer_read_count            8               8                     1075         22          6
                         _direct_path_insert_features             0               0                     2298        226          6
                         _disable_parallel_conventional_load      false           false                 2142        202          6
                         _dm_max_shared_pool_pct                  1               1                     2224        163          6
                         _dml_monitoring_enabled                  true            true                  2114        117          6
                         _enable_dml_lock_escalation              false           false                 2213        159          6
                         _enable_pmo_ctas                         0               0                    65535        232         14
                         _enable_row_shipping                     true            true                  2268        215          6
                         _enable_type_dep_selectivity             true            true                  2023         84          7
                         _fic_area_size                           131072          131072                2117        133          6
                         _first_k_rows_dynamic_proration          true            true                  2266        207          7
                         _fix_control_key                         0               0                    65535        191         14
                         _force_datefold_trunc                    false           false                 2104        111          6
                         _force_slave_mapping_intra_part_loads    false           false                 2092        192          6
                         _force_temptables_for_gsets              false           false                 2051        100          6
                         _force_tmp_segment_loads                 false           false                 2091        193          6
                         _full_pwise_join_enabled                 true            true                  2011         79          6
                         _globalindex_pnum_filter_enabled         true            true                  2211        189          7
                         _gs_anti_semi_join_allowed               true            true                  2068        103          7
                         _hash_multiblock_io_count                0               0                     1794         21          6
                         _improved_row_length_enabled             true            true                  2019         82          7
                         _is_lock_table_for_ddl_wait_lock         0               0                    65535        236         14
                         _kdt_buffering                           true            true                  2141        206          6
                         _left_nested_loops_random                true            true                  2018         81          7
                         _local_communication_costing_enabled     true            true                  2015        122          7
                         _local_communication_ratio               50              50                    2016        123          6
                         _minimal_stats_aggregation               true            true                  2045         99          7
                         _mv_generalized_oj_refresh_opt           true            true                  2333        266          6
                         _nlj_batching_enabled                    1               1                     1991        255          7
                         _optim_new_default_join_sel              true            true                  2073        104          7
                         _optimizer_adaptive_cursor_sharing       true            true                  1908        225          7
                         _optimizer_aw_join_push_enabled          true            true                  2285        230          6
                         _optimizer_aw_stats_enabled              true            true                  2267        210          6
                         _optimizer_block_size                    8192            8192                  2163         19          6
                         _optimizer_cache_stats                   false           false                 1912        130          6
                         _optimizer_connect_by_combine_sw         true            true                  2101        231          7
                         _optimizer_connect_by_elim_dups          true            true                  2102        283          7
                         _optimizer_cost_hjsmj_multimatch         true            true                  2227        164          7
                         _optimizer_enable_extended_stats         true            true                  2297        235          7
                         _optimizer_extended_cursor_sharing_rel   simple          simple                1907        224          7
                         _optimizer_fast_access_pred_analysis     true            true                  2326        278          7
                         _optimizer_fast_pred_transitivity        true            true                  2325        277          7
                         _optimizer_ignore_hints                  false           false                 2180        146          6
                         _optimizer_min_cache_blocks              10              10                    2244        178          6
                         _optimizer_mode_force                    true            true                  1891         43          7
                         _optimizer_percent_parallel              101             101                   2147         40          6
                         _optimizer_random_plan                   0               0                     2181        147          6
                         _optimizer_rownum_pred_based_fkr         true            true                  2241        175          7
                         _optimizer_skip_scan_enabled             true            true                  1934        134          7
                         _optimizer_skip_scan_guess               false           false                 2269        214          6
                         _optimizer_sortmerge_join_inequality     true            true                  2250        208          7
                         _optimizer_squ_bottomup                  true            true                  1928        132          7

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_ALL                _optimizer_system_stats_usage            true            true                  1911        113          7
                         _optimizer_transitivity_retain           true            true                  2228        165          7
                         _parallel_broadcast_enabled              true            true                  1939         62          7
                         _partial_pwise_join_enabled              true            true                  2012         80          6
                         _partition_view_enabled                  true            true                  1900         44          7
                         _pga_max_size                            204800 KB       204800 KB             2054         25          6
                         _project_view_columns                    true            true                  1979        121          6
                         _px_broadcast_fudge_factor               100             100                   1940         63          6
                         _px_minus_intersect                      true            true                  1923        190          7
                         _px_numa_support_enabled                 true            true                  2397        323          6
                         _px_pwg_enabled                          true            true                  2230        166          7
                         _px_ual_serial_input                     true            true                  1922        213          7
                         _replace_virtual_columns                 true            true                  2139        204          7
                         _result_cache_auto_size_threshold        100             100                   1759        252          6
                         _result_cache_auto_time_threshold        1000            1000                  1760        253          6
                         _right_outer_hash_enable                 true            true                  2126        128          7
                         _row_shipping_explain                    false           false                 2264        217          6
                         _row_shipping_threshold                  80              80                    2263        216          6
                         _slave_mapping_enabled                   true            true                  2013        125          6
                         _smm_auto_cost_enabled                   true            true                  2058        102          6
                         _smm_auto_max_io_size                    248 KB          248 KB                2057         29          6
                         _smm_auto_min_io_size                    56 KB           56 KB                 2056         28          6
                         _smm_max_size                            52428 KB        52428 KB              2062         31          6
                         _smm_min_size                            262 KB          262 KB                2061         30          6
                         _smm_px_max_size                         131072 KB       131072 KB             2063         32          6
                         _sort_multiblock_read_count              2               2                     1863         20          6
                         _spr_push_pred_refspr                    true            true                  2162        129          6
                         _sql_compatibility                       0               0                     2370        305          6
                         _sql_model_unfold_forloops               run_time        run_time              2212        158          7
                         _suppress_scn_chk_for_cqn                nosuppress_1466 nosuppress_1466      65535        296         14
                         _table_scan_cost_plus_one                true            true                  2036         93          7
                         _trace_virtual_columns                   false           false                 2138        203          6
                         _update_bji_ipdml_enabled                0               0                    65535        161         14
                         _virtual_column_overload_allowed         true            true                  2140        205          6
                         _with_subquery                           OPTIMIZER       OPTIMIZER             2305        242          6
                         active_instance_count                    1               1                    65535         12          2
                         bitmap_merge_area_size                   1048576         1048576               1753         15          2
                         cpu_count                                2               2                    65535         11          2
                         db_file_multiblock_read_count            128             128                   1073        264          2
                         dst_upgrade_insert_conv                  true            true                  2346        286          2
                         flashback_table_rpi                      non_fbt         non_fbt              65535        169         10
                         hash_area_size                           131072          131072                1793         14          2
                         iot_internal_cursor                      0               0                    65535        293         10
                         is_recur_flags                           0               0                    65535        257          2
                         kkb_drop_empty_segments                  0               0                    65535        310         10
                         only_move_row                            0               0                    65535        315         10
                         optimizer_mode                           all_rows        all_rows              1890         38          3
                         optimizer_secure_view_merging            true            true                  2229        167          2
                         parallel_ddl_mode                        enabled         enabled              65535         37          2
                         parallel_dml_mode                        disabled        disabled             65535         36          2
                         parallel_query_mode                      enabled         enabled              65535         35          2
                         pga_aggregate_target                     262144 KB       262144 KB             2052         24          2
                         result_cache_mode                        MANUAL          MANUAL                1758        212          2
                         sort_area_retained_size                  0               0                     1862         17          2
                         sort_area_size                           65536           65536                 1861         16          2
                         total_cpu_count                          2               2                    65535        275          2
                         total_processor_group_count              1               1                    65535        324          2

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_ALL                transaction_isolation_level              read_commited   read_commited        65535        218          2
                         workarea_size_policy                     auto            auto                  2055        101          2

QKSFM_AQ                 advanced_queuing_internal_cursor         0               0                    65535        287         10

QKSFM_CBO                _and_pruning_enabled                     true            true                  2365        300          7
                         _b_tree_bitmap_plans                     true            true                  1902         49          7
                         _cpu_to_io                               0               0                     1905         33          7
                         _default_non_equality_sel_check          true            true                  2038         95          6
                         _disable_datalayer_sampling              false           false                 1986         69          6
                         _disable_function_based_index            false           false                 2186        150          6
                         _extended_pruning_enabled                true            true                  2210        154          6
                         _fast_full_scan_enabled                  true            true                  1933         60          7
                         _gby_hash_aggregation_enabled            true            true                  2209        188          7
                         _generalized_pruning_enabled             true            true                  2085        109          7
                         _improved_outerjoin_card                 true            true                  2025         85          7
                         _index_join_enabled                      true            true                  2021         83          7
                         _like_with_bind_as_equality              false           false                 2035         92          6
                         _nested_loop_fudge                       100             100                   1978        120          6
                         _new_initial_join_orders                 true            true                  2039         96          7
                         _new_sort_cost_estimate                  true            true                  1913         52          7
                         _oneside_colstat_for_equijoins           true            true                  2040         97          6
                         _optim_adjust_for_part_skews             true            true                  2094        110          7
                         _optim_enhance_nnull_detection           true            true                  1936         61          7
                         _optim_peek_user_binds                   true            true                  2042         98          7
                         _optimizer_adjust_for_nulls              true            true                  2026         86          6
                         _optimizer_better_inlist_costing         all             all                   2242        176          7
                         _optimizer_cartesian_enabled             true            true                  2206        152          6
                         _optimizer_complex_pred_selectivity      true            true                  2254        186          7
                         _optimizer_compute_index_stats           true            true                  2095        144          7
                         _optimizer_connect_by_cb_whr_only        false           false                 2359        295          6
                         _optimizer_connect_by_cost_based         true            true                  2100        187          7
                         _optimizer_correct_sq_selectivity        true            true                  2169        149          7
                         _optimizer_cost_filter_pred              false           false                 2168        135          6
                         _optimizer_cost_model                    choose          choose                1909         51          7
                         _optimizer_enable_density_improvements   true            true                  2279        229          7
                         _optimizer_extended_cursor_sharing       udo             udo                   1906        162          7
                         _optimizer_extended_stats_usage_control  192             192                   2323        260          7
                         _optimizer_fkr_index_cost_bias           10              10                    2308        243          7
                         _optimizer_improve_selectivity           true            true                  2284        227          7
                         _optimizer_join_order_control            3               3                     2193        151          7
                         _optimizer_join_sel_sanity_check         true            true                  1935        137          7
                         _optimizer_max_permutations              2000            2000                  1981         23          7
                         _optimizer_mjc_enabled                   true            true                  2127        127          6
                         _optimizer_new_join_card_computation     true            true                  2077        107          7
                         _optimizer_or_expansion_subheap          true            true                  2302        249          6
                         _optimizer_rownum_bind_default           10              10                    2280        222          7
                         _optimizer_search_limit                  5               5                     2148         10          6
                         _optimizer_self_induced_cache_cost       false           false                 2243        177          6
                         _optimizer_sortmerge_join_enabled        true            true                  2128        136          6
                         _optimizer_star_trans_min_cost           0               0                     2303        240          6
                         _optimizer_star_trans_min_ratio          0               0                     2304        241          6
                         _optimizer_undo_changes                  false           false                 2146        118          6
                         _optimizer_undo_cost_change              11.2.0.3        11.2.0.3              1910         34          7
                         _optimizer_use_feedback                  true            true                  2371        306          7
                         _optimizer_use_subheap                   true            true                  2301        244          6
                         _ordered_nested_loop                     true            true                  1977         64          7

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_CBO                _sort_elimination_cost_ratio             0               0                     1894         18          6
                         _system_index_caching                    0               0                     1984         68          6
                         _use_column_stats_for_function           true            true                  2028         88          7
                         cursor_sharing                           exact           exact                 1755         48          2
                         optimizer_capture_sql_plan_baselines     false           false                 2288        238          2
                         optimizer_dynamic_sampling               2               2                     2075        105          3
                         optimizer_features_enable                11.2.0.3        11.2.0.3              1858          9          2
                         optimizer_features_hinted                0.0.0           0.0.0                65535          1         10
                         optimizer_index_caching                  0               0                     1983         67          2
                         optimizer_index_cost_adj                 100             100                   1982         66          2
                         optimizer_mode_hinted                    false           false                65535          0         10
                         optimizer_use_pending_statistics         false           false                 2283        228          2
                         optimizer_use_sql_plan_baselines         true            true                  2289        239          2
                         parallel_ddl_forced_degree               0               0                    65535          5         10
                         parallel_ddl_forced_instances            0               0                    65535          6         10
                         parallel_dml_forced_dop                  0               0                    65535          4         10
                         parallel_execution_enabled               true            true                 65535          2          2
                         parallel_query_forced_dop                0               0                    65535          3         10
                         parallel_threads_per_cpu                 2               2                     1954         13          2
                         skip_unusable_indexes                    true            true                  2149        114          3
                         sqlstat_enabled                          false           false                65535         39         10
                         statistics_level                         typical         typical               2105        112          2

QKSFM_COMPILATION        _allow_level_without_connect_by          false           false                 2179        319          6
                         _connect_by_use_union_all                true            true                  2103        285          7

QKSFM_CVM                _complex_view_merging                    true            true                  1914         53          7

QKSFM_DML                _dml_frequency_tracking                  false           false                 2353        308          6

QKSFM_EXECUTION          _aggregation_optimization_settings       0               0                     2343        282          7
                         _deferred_constant_folding_mode          DEFAULT         DEFAULT               1867        301          6
                         _rdbms_internal_fplib_enabled            false           false                 1869        263          6
                         _rowsrc_trace_level                      0               0                     2240        173          6
                         cell_offload_compaction                  ADAPTIVE        ADAPTIVE              1879        267          2
                         cell_offload_plan_display                AUTO            AUTO                  1880        268          2
                         cell_offload_processing                  true            true                  1876        262          2

QKSFM_FBA                flashback_data_archive_internal_cursor   0               0                    65535        259         10

QKSFM_INDEX              optimizer_use_invisible_indexes          false           false                 2322        258          2

QKSFM_JPPD               _bloom_predicate_enabled                 true            true                  2198        234          6
                         _optimizer_extend_jppd_view_types        true            true                  2098        157          7
                         _optimizer_interleave_jppd               true            true                  2307        309          7
                         _optimizer_multi_level_push_pred         true            true                  2272        220          7

QKSFM_OR_EXPAND          _optimizer_or_expansion                  depth           depth                 2246        179          7

QKSFM_PARTITION          deferred_segment_creation                true            true                  2275        317          2
                         partition_pruning_internal_cursor        0               0                    65535        303         10

QKSFM_PQ                 _bloom_predicate_pushdown_to_storage     true            true                  2199        269          6
                         _bloom_pushing_max                       512             512                   2203        271          6
                         _bloom_vector_elements                   0               0                     2197        270          6
                         _optimizer_degree                        0               0                     2027         87          6
                         _optimizer_instance_count                0               0                     2331        294          6

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_PQ                 _parallel_cluster_cache_policy           adaptive        adaptive              1957        291          6
                         _parallel_scalability                    50              50                    2314        292          6
                         _parallel_syspls_obey_force              true            true                  2315        261          6
                         _parallel_time_unit                      10              10                    2311        248          6
                         _px_partition_scan_enabled               true            true                  2377        311          7
                         _px_partition_scan_threshold             64              64                    2378        312          6
                         parallel_autodop                         0               0                    65535        289          2
                         parallel_ddldml                          0               0                    65535        290          2
                         parallel_degree                          0               0                    65535        246          2
                         parallel_degree_limit                    65535           65535                 2312        272          2
                         parallel_degree_policy                   manual          manual                1945        245          2
                         parallel_force_local                     false           false                 2313        273          2
                         parallel_hinted                          none            none                 65535        304         10
                         parallel_max_degree                      4               4                    65535        274          2
                         parallel_min_time_threshold              10              10                    2310        247          2
                         parallel_query_default_dop               0               0                    65535        256          2

QKSFM_STAR_TRANS         _optimizer_star_tran_in_with_clause      true            true                  2249        185          7
                         _optimizer_starplan_enabled              true            true                  2207        153          6
                         _optimizer_try_st_before_jppd            true            true                  2373        307          7
                         _optimizer_use_cbqt_star_transformation  true            true                  2362        298          7
                         star_transformation_enabled              false           false                 1903         50          2

QKSFM_TRANSFORMATION     _always_anti_join                        choose          choose                1898         41          7
                         _always_semi_join                        choose          choose                1976         42          7
                         _always_star_transformation              false           false                 1901         45          6
                         _bt_mmv_query_rewrite_enabled            true            true                  2008        139          6
                         _convert_set_to_join                     false           false                 1920         57          7
                         _cost_equality_semi_join                 true            true                  2037         94          7
                         _dimension_skip_null                     true            true                  2252        183          7
                         _distinct_view_unnesting                 false           false                 1925        141          6
                         _eliminate_common_subexpr                true            true                  1918         55          7
                         _enable_query_rewrite_on_remote_objs     true            true                  2281        223          6
                         _force_rewrite_enable                    false           false                 2253        184          6
                         _hash_join_enabled                       true            true                  1792         47          6
                         _max_rwgs_groupings                      8192            8192                  2010        320          6
                         _mmv_query_rewrite_enabled               true            true                  2007        138          7
                         _no_or_expansion                         false           false                 1980         65          6
                         _optimizer_cbqt_factor                   50              50                    1929        131          6
                         _optimizer_cbqt_no_size_restriction      true            true                  2234        170          7
                         _optimizer_coalesce_subqueries           true            true                  2278        276          7
                         _optimizer_cost_based_transformation     linear          linear                1927        126          7
                         _optimizer_dim_subq_join_sel             true            true                  2177        142          7
                         _optimizer_disable_strans_sanity_checks  0               0                     2178        143          6
                         _optimizer_distinct_agg_transform        true            true                  2342        281          7
                         _optimizer_distinct_elimination          true            true                  2270        219          7
                         _optimizer_distinct_placement            true            true                  2277        302          7
                         _optimizer_eliminate_filtering_join      true            true                  2345        284          7
                         _optimizer_enable_table_lookup_by_nl     true            true                  2388        316          7
                         _optimizer_enhanced_filter_push          true            true                  2237        171          7
                         _optimizer_false_filter_pred_pullup      true            true                  2382        313          7
                         _optimizer_filter_pred_pullup            true            true                  2099        172          7
                         _optimizer_filter_pushdown               true            true                  2393        318          6
                         _optimizer_free_transformation_heap      true            true                  2299        250          6
                         _optimizer_full_outer_join_to_outer      true            true                  2396        322          7
                         _optimizer_group_by_placement            true            true                  2276        221          7
                         _optimizer_join_elimination_enabled      true            true                  2208        168          7

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_TRANSFORMATION     _optimizer_join_factorization            true            true                  2360        297          7
                         _optimizer_native_full_outer_join        force           force                 2296        233          7
                         _optimizer_nested_rollup_for_gset        100             100                   2316        254          6
                         _optimizer_null_aware_antijoin           true            true                  1899        156          7
                         _optimizer_order_by_elimination_enabled  true            true                  2248        180          7
                         _optimizer_outer_join_to_inner           true            true                  2395        321          7
                         _optimizer_outer_to_anti_enabled         true            true                  2247        181          7
                         _optimizer_push_down_distinct            0               0                     1926        116          7
                         _optimizer_push_pred_cost_based          true            true                  2097        155          7
                         _optimizer_reuse_cost_annotations        true            true                  2306        251          6
                         _optimizer_table_expansion               true            true                  2364        299          7
                         _optimizer_unnest_all_subqueries         true            true                  1917        288          6
                         _optimizer_unnest_corr_set_subq          true            true                  2341        280          7
                         _optimizer_unnest_disjunctive_subq       true            true                  2340        279          7
                         _or_expand_nvl_predicate                 true            true                  2034         91          7
                         _pivot_implementation_method             choose          choose                2300        237          7
                         _pre_rewrite_push_pred                   true            true                  2076        106          7
                         _pred_move_around                        true            true                  1919         56          7
                         _predicate_elimination_enabled           true            true                  1970        119          6
                         _push_join_predicate                     true            true                  1930         58          7
                         _push_join_union_view                    true            true                  1931         59          7
                         _push_join_union_view2                   true            true                  1932        145          7
                         _query_cost_rewrite                      true            true                  1997         72          6
                         _query_mmvrewrite_maxcmaps               20              20                    2261        198          6
                         _query_mmvrewrite_maxdmaps               10              10                    2260        197          6
                         _query_mmvrewrite_maxinlists             5               5                     2259        196          6
                         _query_mmvrewrite_maxintervals           5               5                     2258        195          6
                         _query_mmvrewrite_maxpreds               10              10                    2257        194          6
                         _query_mmvrewrite_maxqryinlistvals       500             500                   2265        201          6
                         _query_mmvrewrite_maxregperm             512             512                   2262        199          6
                         _query_rewrite_1                         true            true                  1999         74          6
                         _query_rewrite_2                         true            true                  1998         73          6
                         _query_rewrite_drj                       true            true                  2004         78          6
                         _query_rewrite_expression                true            true                  2001         75          6
                         _query_rewrite_fpc                       true            true                  2003         77          6
                         _query_rewrite_fudge                     90              90                    2000          8          6
                         _query_rewrite_jgmigrate                 true            true                  2002         76          6
                         _query_rewrite_maxdisjunct               257             257                   2005         27          6
                         _query_rewrite_or_error                  false           false                 1893         46          6
                         _query_rewrite_setopgrw_enable           true            true                  2184        148          7
                         _query_rewrite_vop_cleanup               true            true                  2006        124          6
                         _remove_aggr_subquery                    true            true                  1924        115          7
                         _selfjoin_mv_duplicates                  true            true                  2251        182          7
                         _simple_view_merging                     true            true                  1915        174          6
                         _subquery_pruning_enabled                true            true                  2031         89          7
                         _subquery_pruning_mv_enabled             false           false                 2032         90          7
                         _union_rewrite_for_gs                    yes_gset_mvs    yes_gset_mvs          2083        108          7
                         _unnest_subquery                         true            true                  1916         54          7
                         query_rewrite_enabled                    true            true                  1995         70          3
                         query_rewrite_integrity                  enforced        enforced              1996         71          2

321 rows selected.

There are a few parameters in there which look a little unexpected – why, for example, should deferred_segment_creation affect the optimizer ? One day I might get around to checking how many of these parameters do result in new child cursors being generated if you change them with an alter session or alter system – but it’s not a job I’m going to do in a hurry.

Mark Ferguson: I (almost) didn’t recognize you with your clothes off…

On Sunday evenings I usually pop over to see some mates. Jodey fills me in on the latest gossip, while Steve tries to convince me that vitamin C can cure every problem know to mankind. We do some yoga, then watch whatever they’ve got on series link before I go home. At the moment, we are watching Spartacus: Vengeance. We watched the previous series together, so we knew what to expect. Last night we decided to play “body-part bingo”, which involved deciding on a quota of various human body-parts we expected to see during the episode and trying to keep count to see if we guessed right. :)

All was going well until one particular scene, where the master of the household was “enjoying” himself with two slaves (one female and one male). There was a classic quote, which I will not repeat here for fear of offending the more sensitive among you, but at that point I lost count and said something to the tune of, “Wait a minute, I know him!” This caused raised eyebrows and the pause button was hit giving me chance to explain…

I have a great memory for faces, but struggle to remember names or where I remember the face from. Out came the iPad and a quick search revealed the master of the household was an actor called Mark Ferguson. I checked out some of his other acting credits and it all fell into place. When I wrote about last years NZOUG Conference I mentioned the murder-mystery theme. The compere for the night was an actor from Auckland. Guess what his name was? :)

He was a top bloke and now I’ve seen rather more of him that I would have expected to see. If I ever see him again I will not be able to stop myself from shouting one of his lines from this episode… :)

Cheers

Tim…




Directly Executing a Query is Slower than Returning a REFCURSOR from a PL/SQL Procedure – What is Wrong with this Quote?

February 20, 2012 My copy of the ”Oracle Database 11gR2 Performance Tuning Cookbook” arrived from Amazon, and I will say that I like the seven steps for solving performance problems that is found on page 12, although the diagram of the process on page 14 may lead to a condition known as Compulsive Tuning Disorder. I am [...]

Hotsos Symposium 2012

It’s almost time for Hotsos’s 10th annual Symposium. This year’s conference will be held March 4 – 8 in Irving, Texas. The Hotsos Symposium is probably the best performance oriented Oracle conferences in the world. I am happy and humbled to be speaking at it again this year as the lineup of speakers is once again world class. It’s great to have several other Enkitec’ies on the bill as well. Both Karen Morton and Tanel Poder will be presenting too. Enkitec is also a sponsor of the event this year, so we’ll be hosting a couple of Exadata focused happy hours as well. There’s still time to register. Hope to see you there.

 

RMOUG Debrief

I’m back home after a very pleasant few days in Denver. As always the RMOUG conference was great fun, the location delightful, and the people were friendly, and the organizers did a great job of looking after the presenters and making things happen. It’s a terrific event – just big enough to have a real buzz, but small enough (I’d guess about 700 – 800 people) that you don’t feel lost in the crowd.

All three of my presentations seemed to go well - one person even started to applaud (which got the whole room going) when I got to the really important slide in the first session.

Jonathan Gennick of Apress had brought 100 copies of Oracle Core and was selling them off at a special event price of USD 20 – and I think he only had one or two copies left by the end of the event; I know that I autographed an easy 40 or 50 copies.

One of the nicest things about the event happened just a few minutes ago, though. Just as I was about to start typing up this little note, I got the following email, which I reproduce with permission:

I just wanted to say I appreciate the time and effort you put into your presentations on table access and joins at RMOUG.  They were excellent, as always.  Based on what you showed us, I have rewritten one problem query in our application such that I am getting an 82 percent performance gain.  I am excited about the possibilities and look forward to sharing what you showed us at RMOUG to our development team.

Thank you again for the support you have shown for RMOUG and Oracle professionals.

Sandy Becker
Database Operations Manager
Oildex, a service of Transzap, Inc.

It’s really nice to hear that someone has taken a mechanism you’ve described and found examples where it can be applied to such good effect and done it so soon after the event.

 

 

Friday Philosophy – Tosh Talked About Technology

Sometimes I can become slightly annoyed by the silly way the media puts out total tosh and twaddle(*) that over-states the impact or drawbacks about technology (and science ( and especially medicine (and pretty much anything the media decides to talk about)))). Occasionally I get very vexed indeed.

My attention was drawn to some such thing about SSDs (solid State Discs) via a tweet by Gwen Shapira yesterday {I make no statement about her opinion in this in any way, I’m just thanking her for the tweet}. According to Computerworld

SSDs have a ‘bleak’ future, researchers say

So are SSDs somehow going to stop working or no longer be useful? No, absolutely not. Are SSDs not actually going to be more and more significant in computing over the next decade or so? No, they are and will continue to have a massive impact. What this is, is a case of a stupidly exaggerated title over not a lot. {I’m ignoring the fact that SSDs can’t have any sort of emotional future as they are not sentient and cannot perceive – the title should be something like “the future usefulness of SSDs looks bleak”}.

What the article is talking about is a reasonable little paper about how if NAND-based SSDS continue to use smaller die sizes, errors could increase and access times increase. That is, if the same technology is used in the same way and manufacturers continue to shrink die sizes. It’s something the memory technologists need to know about and perhaps find fixes for. Nothing more, nothing less.

The key argument is that by 2024 we will be using something like 6.4nm dies and at that size, the physics of it all means everything becomes a little more flaky. After all, Silicon atoms are around 0.28nm wide (most atoms of things solid at room temperature are between 0.2nm and 0.5nm wide), at that size we are building structures with things only an order of magnitude or so smaller. We have all heard of quantum effects and tunneling, which means that at such scales and below odd things can happen. So error correction becomes more significant.

But taking a reality check, is this really an issue:

  • I look at my now 4-year-old 8GB micro-USB stick (90nm die?) and it is 2*12*30mm, including packaging. The 1 TB disc on my desk next to it is 24*98*145mm. I can get 470 of those chips in the same space as the disc, so that’s 3.8TB based on now-old technology.
  • Even if the NAND materials stay the same and the SSD layout stays the same and the packaging design stays the same, we can expect about 10-50 times the current density before we hit any problems
  • The alternative of spinning platers of metal oxides is pretty much a stagnant technology now, the seek time and per-spindle data transfer rate is hardly changing. We’ve even exceeded the interface bottleneck that was kind-of hiding the non-progress of spinning disk technology

The future of SSD technology is not bleak. There are some interesting challenges ahead, but things are certainly going to continue to improve in SSD technology between now and when I hang up my keyboard. I’m particularly interested to see how the technologists can improve write times and overall throughput to something closer to SDRAM speeds.

I’m willing to lay bets that a major change is going to be in form factor, for both processing chips and memory-based storage. We don’t need smaller dies, we need lower power consumption and a way to stack the silicon slices and package them (for processing chips we also need a way to make thousands of connections between the silicon slices too). What might also work is simply wider chips, though that scales less well. What we see as chips on a circuit board is mostly the plastic wrapper. If part of that plastic wrapper was either a porous honeycomb air could move through or a heat-conducting strip, the current technology used for SSD storage could be stacked on top of each other into blocks of storage, rather then the in-effect 2D sheets we have at present.

What could really be a cause of technical issues? The bl00dy journalists and marketing. Look at digital cameras. Do you really need 12, 16 mega-pixels in your compact point-and-shoot camera? No, you don’t, you really don’t, as the optics on the thing are probably not up to the level of clarity those megapixels can theoretically give you, the lens is almost certainly not clean any more and, most significantly, the chip is using smaller and smaller areas to collect photons (the sensor is not getting bigger with more mega-pixels you know – though the sensor size is larger in proper digital SLRs which is a large part of why they are better). This less-photons-per-pixel means less sensitivity and more artefacts. What we really need is maybe staying with 8MP and more light sensitivity. But the mega-pixel count is what is used to market the camera at you and I. As a result, most people go for the higher figures and buy something technically worse, so we are all sold something worse. No one really makes domestic-market cameras where the mega-pixel count stays enough and the rest of the camera improves.

And don’t forget. IT procurement managers are just like us idiots buying compact cameras.

(*) For any readers where UK English is not a first language, “twaddle” and “tosh” both mean statements or arguments that are silly, wrong, pointless or just asinine. oh, Asinine means talk like an ass :-) {and I mean the four-legged animal, not one’s bottom, Mr Brooks}

Tables and Their Indexes Should be Located on Different Disks – What is Wrong with this Quote?

February 17, 2012 As I mentioned in a previous blog article, I placed an order for the book “Oracle Database 11gR2 Performance Tuning Cookbook“, mostly because I was curious how the book would compare with the “Oracle Database 11g Performance Tuning Recipes” book (my review of that book was a total of about 35 typewritten pages in [...]

Subquery Factoring

When I wrote a note last week about the fixes to the subquery factoring optimizer code in 11.2.0.3, I finished with a comment about having more to say on the test case if I materialized the subquery. Today’s the day to talk about it. As a reminder, here’s the query, but with the /*+ materialize */ hint in place:

with subq as (
	select
	/*+ materialize */
		outer.*
	from
		emp outer
	where
		sal > 1000000
	and	outer.sal > (
			select
				avg(inner.sal)
 			from	emp inner
			where	inner.dept_no = outer.dept_no
		)
	)
select	*
from	subq
;

Here are the three plans – from 10.2.0.5, 11.1.0.7, and 11.2.0.3 respectively:


PLAN_TABLE_OUTPUT 10.2.0.5
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 834666169

----------------------------------------------------------------
| Id  | Operation                  | Name                      |
----------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |
|   1 |  TEMP TABLE TRANSFORMATION |                           |
|   2 |   LOAD AS SELECT           |                           |
|*  3 |    HASH JOIN               |                           |
|*  4 |     TABLE ACCESS FULL      | EMP                       |
|   5 |     VIEW                   | VW_SQ_1                   |
|   6 |      SORT GROUP BY         |                           |
|   7 |       TABLE ACCESS FULL    | EMP                       |
|   8 |   VIEW                     |                           |
|   9 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6602_AAB1DB |
----------------------------------------------------------------

PLAN_TABLE_OUTPUT 11.1.0.7
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 706934928

-----------------------------------------------------------------
| Id  | Operation                  | Name                       |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            |
|   1 |  TEMP TABLE TRANSFORMATION |                            |
|   2 |   LOAD AS SELECT           |                            |
|*  3 |    HASH JOIN               |                            |
|*  4 |     TABLE ACCESS FULL      | EMP                        |
|   5 |     VIEW                   | VW_SQ_1                    |
|   6 |      SORT GROUP BY         |                            |
|   7 |       TABLE ACCESS FULL    | EMP                        |
|   8 |   VIEW                     |                            |
|   9 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D660E_50EDB63 |
-----------------------------------------------------------------

PLAN_TABLE_OUTPUT (11.2.0.3)
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2718753531

------------------------------------------------------------------
| Id  | Operation                  | Name                        |
------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |
|   1 |  TEMP TABLE TRANSFORMATION |                             |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D662A_40002053 |
|*  3 |    HASH JOIN               |                             |
|*  4 |     TABLE ACCESS FULL      | EMP                         |
|   5 |     VIEW                   | VW_SQ_1                     |
|   6 |      SORT GROUP BY         |                             |
|   7 |       TABLE ACCESS FULL    | EMP                         |
|   8 |   VIEW                     |                             |
|   9 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D662A_40002053 |
------------------------------------------------------------------

Can you spot the problem ?
Apart from anything else, the 11.2.0.3 plan has two references to the “temporary table” while the other two plans only have one reference – so the plan_hash_value for 11.2.0.3 is going to be different from the plan_hash_value for the other two (the plan hash value is derived from the line numbers, operations, and object names).

But then again, 10.2.0.5 and 11.1.0.7 have exactly the same plan but different plan_hash_values – and that’s because you can see (when you look closely) that they have different name for the temporary table. In fact, every time the statement is optimised you’ll see a new sequential value for (the middle section of) the temporary table name. At least, that’s what happens until 11.2.0.3 where bug fix 10162430 comes into play and any object names starting with SYS_TEMP are ignored. (Thanks to Timur Ahkmadeev  for pointing that out).

Why does this matter? Because when Oracle tries to use an SQL Baseline, it optimizes the query with the baseline in place and checks to see if the resulting plan has the same plan_hash_value as the one stored with the baseline. If the values don’t match, the baseline isn’t used. So if, just before upgrading, you were planning to create some baselines for queries that have materialized factored subqueries (or any other constructs, such as star transformations and grouping set queries, that create temporary tables) then any baselines you do generate won’t work after the upgrade, unless you’re upgrading FROM 11.2.0.3.

One option, perhaps, is to fake in a new baseline after the upgrade to 11.2.0.3, and this might be relatively easy if you first try to run the system with parameter optimizer_features_enable set to your earlier release but switch on the fix for bug 10162430 and capture baselines with that configuration in place. This will give you a fixed plan_hash_value for the plan and if most cases will probably give you the plan you had seen in the previous release; the only little oddity will be that the outline will include the entries: OPT_PARAM(‘_fix_control’ ’10162430:1′) and OPTIMIZER_FEATURES_ENABLE(‘{your chosen version}’).

Footnote:
Bug 10162430 was reported against 10.2.0.4, and is reported as fixed in 12.1. To revert to older behaviour in 11.2.0.3 you can use the fix_control mechanism:

alter session set "_fix_control"='10162430:OFF';

To revert to an older set of optimizer features but enable this bug fix you would do something like:

alter session set optimizer_features_enable='10.2.0.5';
alter session set "_fix_control"='10162430:ON';