Oracle DBA – A lifelong learning experience

11.2.0.4 new features and a OPTIMIZER_DYNAMIC_SAMPLING change

Posted by John Hallas on December 9, 2013

As of 27th August  2013, 11.2.0.4, the final release of 11GR2 was made available – a new features document is available . I will give a quick bullet list of the new features and then discuss one very important one that is not mentioned.

  •  Oracle Data Redaction – provides a new ASO (cost £££) option to redact specified information, even at runtime, from within the database
  •  Trace File Analyzer and Collector, is a diagnostic collection utility to simplify diagnostic data collection on Oracle Clusterware, Oracle Grid Infrastructure  – looks well worth investigating
  •  RACcheck – The Oracle RAC Configuration Audit Tool – to perform regular health checks as well as pre- and post-upgrade best practices assessments. Only written for linux so needed adapting for HPUX and needs to have the root password – an issue for many DBAs
  •  Database Replay Support for Database Consolidation – run lots of scenarios- batch, online, monthly process all at the same time even though captured at different periods.
  •  Optimization for Flashback Data Archive History Tables – use the OPTIMIZE DATA clause when creating or altering a flashback data archive.

So the one that has not appeared in that list, probably because it is not a new feature as such is tan additional value for the init.ora parameter OPTIMIZER_DYNAMIC_SAMPLING. This comes into play when a table does not have any statistics and the parameter is enabled. The previous default setting of 2  was to use dynamic statistics if at least one table in the statement has no statistics and the number of blocks that the statistics would be based on would be 64 and the value could range between 2 and 10 , each value doubling  the number of blocks that will be sampled. The new value of 11  means that the optimizer will gather dynamic statistics automatically whenever the optimizer deems it necessary and based on the number of blocks it thinks appropriate.

My testing has shown a couple of anomalies between the two versions it exists on ( 11.2.0.4, 12.1.0.1).

Firstly I set up a test table and copied it between environments so it was consistent. I then gathered stats and then deleted them to get the details of blocks etc.

Create table fred.ds as select * from dba_objects;
Create index fred.DS_IND1 on fred.ds(object_name);

select num_rows,avg_row_len, blocks from dba_tables where owner = 'FRED' and table_ name = ‘DS’

NUM_ROWS AVG_ROW_LEN     BLOCKS
---------- ----------- ----------
87970          98       1261

execute dbms_stats.delete_table_stats('FRED','DS');

My idea was to be as simple and consistent as possible, so I ran the following test case, creating a new session each time.

alter system flush shared_pool;

alter session set optimizer_dynamic_sampling=11;

set lines 240 pages 0

alter session set tracefile_identifier = 'JOHN';

alter session set events='<em>10053 trace name context forever</em>, level 1';

Set autotrace traceonly explain

select count(*) from fred.ds where object_type='INDEX' and object_id >5000;

I was interested in seeing how many blocks would be sampled when I told the optimizer to manage this itself.

In an 11.2.0.3 database I would see the following entries in the trace file, when using the default value of 2

*** 2013-12-08 10:05:25.364

** Executed dynamic sampling query:
level : 2
sample pct. : 5.000000
actual sample size : 3842
filtered sample card. : 228
orig. card. : 102917
block cnt. table stat. : 1260
block cnt. for sampling: 1260
max. sample block cnt. : 64
sample block cnt. : 63
min. sel. est. : 0.00050000

which shows it selected 63 blocks – this should have been 64 but Jonathan Lewis has pointed out that discrepancy already

In both 11.2.0.4 and 12.1.0.1 that reporting remains the same for values other than the new one of 11

In 11.2.0.4  using the default value of 2 we get an explain plan  as follows

Execution Plan
----------------------------------------------------------
Plan hash value: 161315987
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    24 |   344   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| DS   |  3883 | 93192 |   344   (1)| 00:00:05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='INDEX' AND "OBJECT_ID">5000)

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

Notice that I stated the default value was 2, it has not been changed to 11 in either 11.2.0.4 or 12.1.0.1

In 11.2.0.4 using a value of 11

Plan hash value: 161315987

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    24 |   344   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| DS   |  2878 | 69072 |   344   (1)| 00:00:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='INDEX' AND "OBJECT_ID">5000)

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

In 12.1.0.1 using a value of 11

Execution Plan
----------------------------------------------------------
Plan hash value: 161315987
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    24 |   344   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| DS   |  4467 |   104K|   344   (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='INDEX' AND "OBJECT_ID">5000)
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)

In neither case does the trace file contain the same information it does when using any value between 2 and 10. It appears to be very difficult to tell what the sample size is when using a value of 11.

It can also be seen that the note line  seen in 11.2.0.4 is

- dynamic sampling used for this statement (level=11)
whereas it changes in 12.1.0.1 to
– dynamic statistics used: dynamic sampling (level=AUTO)

My small test case is not sufficient to make any sort of educated guess as to how efficient the auto level is and how much  overhead it will have. The actual value of the count was 4295.

11.2.0.3(2)  3883
11.2.0.4(11) 2878 - particularly inaccurate
12.1.0.1(11) 4467  - almost bang on.

Hopefully this will pique someones interest in experimenting with setting optimizer_dynamic_sampling=11 but remember, it is much better to gather accurate stats in the first place than rely on not having them and the optimizer having to work them out on the fly, both in terms of performance and accuracy.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 190 other followers

%d bloggers like this: