ADO in Oracle Standard Edition?

Automatic Data Optimisation (=ADO) in Oracle Standard Edition?


Time to inspect the Automatic Data Optimization feature that Oracle12c provides from a Standard Edition Database perspective.


Updated 22.6.2015: I feel privileged having peers reading my blog, and receiving feedback.  Standard Edition Community needs a blog with facts, so Thanks Franck Pachot for correcting me on Basic Compression. I would also like to thank my friend Joel Goodman for advice on this topic.

I am actually pleased that I made this mistake since it only reminded me on how easy it is to misinterpret and make wrong assumptions. 

I found this very good post on basic table compression by Jonathan Lewis. As a none native English speaking person, both the way the text below is written and the use of the word “core” made me make the wrong assumptions, and I undermined my own advice “always check and double-check the License Document”. 

compression_no

“Of the three mechanisms, index compression and basic table compression are part of the core product, while OLTP compression is part of the separately licensed “Advanced Compression Option (ACO)”. In this first article we will restrict ourselves to creating data using basic table compression, leaving the question of updating and deleting compressed data to the second article, which will lead us into “OLTP” compression in the third article. We’ll leave index compression to stand alone in the fourth and fifth articles.” (Jonathan Lewis).

NOTE!! The Basic Compression is not Standard Edition Database Proof.  So do not use it in a Standard Edition Database! 

So I checked the License information on Oracle9i:

compress_9i

So wait, doesn’t it tell us that Compression is ok to use like J Lewis said? No! Once again I made the wrong assumption! The structure of the Oracle9i document is not the same we are used to  (SE1 | SE | EE).

In Oracle9i the structure is  (SE | EE|Personal Edition) – Yes Oracle tells us if a feature is available in Personal Edition:

o91_license

compress_9i

EndOfUpdate 22.6.2015


Original post:

Explanation for a Business Management’s type of person.

The Whitepaper on ADO feature tells us:

The rapid growth of data presents daunting challenges for IT, both in cost and performance. Although the cost of storage keeps declining, fast-growing data volumes make storage one of the costliest elements of most IT budgets. In addition, the accelerating growth of data makes it difficult to meet performance requirements while staying within budget.

Explanation for a more Technical person:

Automatic Data Optimization (=ADO) automatically moves and compresses data according to user-defined policies based on the information collected by Heat Map. Heat Map automatically tracks modification and query timestamps at the row and segment levels, providing detailed insights into how data is being accessed.

Explanation in a Graphical view 

(c) Ann Sjökvist
(c) Ann Sjökvist

Note: I am not an expert on HW, so when making this graph the choice of storage might not be the best, but to my understanding both the raid50 and Hadoop HDFS alternative seem like good options. To my understanding HDFS is available as a plug-in to ADO, even thought by doing so, the full “data travel path” will not be automatically managed by the database, but that’s a whole different story.

Explanation from the License perspective:

ado_license

Clear and loud. We cannot use it in a Standard Edition Database environment.

This new Automatic Data Optimisation feature can exploit the functionality of the Partitioning Option and the Advanced Compression Option, and neither of those capabilities are available in a Standard Edition Database Environment.

Note: the partitioned views is not a requirement of ADO, but without the pertaining functionality you will lose the flexibility of using different storage tiers and different compression on different parts of the same table. And as you already maybe have read from my blog, we can create partitioning views in our Standard Edition Database Environment, and by doing so, we can achieve some degree of flexibility also in our Standard Edition Database Environment.

ADO feature allows active data to live on high performance storage like solid state technology, and older inactive data to live on high capacity drives, and allows the DBA to leverage different storage technologies for the same table.  The magic behind all this is the new technology called Heat Map.

Heat map tracks the last access or update time for segments and extents and can also track at the database block level. This includes the time of last modification and access of tables and partitions. There is also a Row level Heat Map that tracks modification times for individual rows (aggregated to the block level).

How does this magic come into place?

  1. You should enable Heat Map (alter system set heat_map = on scope=both)(manually performed)
  2. Heat Map Statistics are collected
    • V$HEAT_MAP_SEGMENT (MMON collects and “write” the info to this view), HEAT_MAP_STAT$ (dbms_scheduler regularly flushes the info to this table from previous view), DBA_HEAT_MAP_SEG_HISTOGRAM & DBA_HEAT_MAP_SEGMENT (are views based on the previous table)
  3. You create ADO Policy on table (manually)
  4. ADO Policy get evaluated
  5. ADO actions will be executed

A beautiful feature but we cannot use it in Standard Edition.


switch Can we enable ADO in Standard Edition? 

There are things a Standard Edition DBA can unintentionally perform, when not alert, and the software will not tell you if a license agreement violation will occur by continuing performing the task you were just about to perform.

Will Oracle give us a helping hand by performing the steps above to activate Heat Map and ADO feature in a Standard Edition Database?

 sandbox      Sandbox and Testcase

  • Oracle Standard Edition version 12.
  • Database Patch Set Update : 12.1.0.1.7
  • Linux se12t1.min.com 3.8.13-55.1.5.el6uek.x86_64
  • An out-of-the-box installation of the Standard Edition Database
SQL> SELECT sys_context('USERENV', 'AUTHENTICATED_IDENTITY') as Environment FROM dual union all SELECT sys_context('USERENV', 'CDB_NAME') FROM dual union all SELECT sys_context('USERENV', 'CON_NAME') FROM dual;
 ENVIRONMENT
 ---------------------
 oracle
 cdbse
 CDB$ROOT
SQL>ALTER SYSTEM SET heat_map=ON scope=both;
 alter system set heat_map=on scope=both
 *
 ERROR at line 1:
 ORA-02097: parameter cannot be modified because specified value is invalid
 ORA-00439: feature not enabled: Heat Map

Bang! We cannot even perform step one from the list above. Great and Thanks Oracle!


ape
By Ian Leonard

Can we mimic ADO in Standard Edition?

NOTE!! The Basic Compression is not Standard Edition Database Proof.  So do not use it in a Standard Edition Database!

Note: Starting from 12c we can use the SecureFile for LOBS, but remember the SecureFile Compression is part of the Advanced Compression option, and that is an Enterprise Edition option.

Basic Compression – NOT OK in Standard Edition

Basic Table Compression N N Y

The Oracle Advanced Compression option contains the following features:

SecureFiles Compression and Deduplication 
https://docs.oracle.com/cd/E11882_01/license.112/e47877.pdf

 

--CTAS with basic compression enabled
create table t_se1 compress basic
as
select * from [some_table] where [column] = [value];
--Direct path insert into empty table defined as compressed
create table t_se1 compress basic
as
select * from [some_table] where rownum = 0;

insert /*+ append */ into t_se1 select * from [some_table] where [column] = [value];

How about moving  data according to usage?

Archived data is usually cold, so that should be easy to move to a less expensive storage tier. But even so, I do think this requires a good data model, knowledge about not only the application capabilities, but also good knowledge about how the end-user is actually using the application and what is the business process that they are following. Again, the importance of good collaboration between people is vital.

(c) Ann Sjökvist Figuring out what can be moved in SE

This SQL shows information about segments read and write occurrence per date, and according to the License Document, these views are Standard Edition Proof :

WITH se_ado as (
     SELECT o.owner,o.object_name, o.object_type,s.db_block_changes_delta bcd,
to_char(begin_interval_time,'mm/dd') c1, 
         sum(physical_reads_total) reads, 
         sum(physical_writes_total) writes 
        FROM 
          dba_hist_seg_stat s, 
          dba_hist_seg_stat_obj o, 
          dba_hist_snapshot sn 
        WHERE o.owner='SEHC'and s.obj# = o.obj#
        and sn.snap_id = s.snap_id
        GROUP BY o.owner,o.object_name,o.object_type,s.db_block_changes_delta,to_char(begin_interval_time,'mm/dd')
        ORDER BY 4,1,3)
select se_ado.* from se_ado where se_ado.writes=0 or se_ado.reads=0
OWNER OBJECT_NAME OBJECT_TYP  BCD C1 READS WRITES
------------------------------------------------- 
SEHC SE_INDB_ARCH TABLE       0 06/21    0      8
SEHC SE_INDB_ARCH TABLE      16 06/21    0      8
 

With this script as a starting point, plus lots of evaluation on what are your company’s metrics on moving data to a less expensive storage tier destination, and both some scripting and some maintenance windows,  I think you could mimic ADO to some degrees.

When all your metrics have been evaluated, a scheduled job could perform the collection on a regular basis, that send an email with the results to the DBA.

The magic of moving objects around will be very difficult to mimic, because as the license document reminds us of, ONLINE functionality is only available in Enterprise Edition Database Environment.

online

online_df

So for us Standard Edition DBA’s, we need to have a maintenance window. In my experience, this is not a big issue, when all the parties have agreed and are committed to a solution that now a then requires a maintenance window.

Solution good enough for your company or not?  It depends.

Personally I am a strong believer of the concept that I came up with during a discussion at the Harmony15 Conference; a Mental Sauna” meeting. Briefly it’s about not “pull rank” on someone else. You have no uniform on, so no visible rank to fall back on. In a Finnish Sauna it’s tradition to join, having either only your birthday suit  or a swimming suit on, so it is understandable very difficult to tell is a person a priest, a CEO, a plumber, or what so ever, and that nurtures an environment were people freely exchange their thoughts.

Note: The Finnish Sauna tradition is something special, so if you have no experience of it, please read the article before making any assumptions. 

I believe the result after such a “Mental Sauna”-meeting on for example “Shall we mimic ADO or go for the genuine ADO solution?” would be more uniform since all parties (people like: Application Vendor, the Business Owner, the IT department, support, the end-user, and solution conductor etc) have already discussed the advantages and disadvantages of the topic, and therefore have a stronger commitment to the solution.


Conclusion

Pretty cool feature Oracle engineers have created, isn’t it?

If you are running on a Standard Edition Database, and find this ADO feature appealing, why not start a “mental sauna”-meeting and see which road you choose to take.


Take care, stay well, and enjoy the summer!

-Ann

21st June 2015

Leave a Reply

Your email address will not be published. Required fields are marked *