TECHNOLOGY: Database Cloud
Automatic Data OptimizationBy Arup Nanda
Learn how to enable information lifecycle management to automatically move data to lower-cost storage tiers and compress it.
John, the principal database architect at Acme Bank, sips his coffee while listening to his visitors’ woes. The storage demand for databases at Acme is rising rapidly, due to the seemingly endless stream of data points. Because data—however old—can be good for marketing analytics, no one wants to purge old data, though. Although Acme does have an information lifecycle management (ILM) policy to put older and infrequently accessed data on lower-cost storage, the implementation has not been very effective, and unclear identification of “infrequently accessed” data is the primary reason. Everyone agrees on the time period during which the lack of access defines infrequently accessed data but not the exact mechanics of identifying it. The standard approach for identifying the data is to put a trigger on the tables to flag data that hasn’t changed, but Acme’s DBAs eschew such an approach, because it negatively affects performance. Additionally, everyone agrees that an automated system to move old, unchanged data to lower-cost storage would be ideal. Is there, all want to know, a better approach to achieving Acme’s ILM objectives?
There is, responds John: using the Automatic Data Optimization feature of Oracle Database 12c.
Acme deals with several currencies, including the British pound, the euro, and the Canadian dollar, in addition to its primary currency, the US dollar. It’s vital to record the exact exchange rate of the currencies. The rate can change every day, so the bank stores the rates along with specific dates. Because financial transactions can be made effective as of a different date, past records of exchange rates must be stored and available. The following is what the EXCHANGE_RATE table looks like:
SQL> desc exchange_rate NAME TYPE —————————— ———————————— FROM_CURR VARCHAR2(3) TO_CURR VARCHAR2(3) START_DATE DATE END_DATE DATE RATE NUMBER
Online Setup Listing
connect sys/oracle@localhost:1522/ANL2 as sysdba create tablespace latest_ts datafile ‘C:\ORACLE\ORADATA\ANL2\LATEST_TS_01.DBF’ size 111M autoextend on next 1m; create tablespace midterm_ts datafile ‘C:\ORACLE\ORADATA\ANL2\MIDTERM_TS_01.DBF’ size 111M autoextend on next 1m; grant create session, create table to forex identified by forex / alter user forex quota unlimited on latest_ts / alter user forex quota unlimited on midterm_ts / connect forex/forex@localhost:1522/ANL2 create table exchange_rate ( from_curr varchar2(3), to_curr varchar2(3), start_date date, end_date date, rate number ) tablespace latest_ts / declare l_rand number; begin for d in 1..365 loop l_rand := dbms_random.value(1,2); insert into exchange_rate values (‘USD’,’GBP’,sysdate-d-1,sysdate-d,l_rand); insert into exchange_rate values (‘USD’,’CAD’,sysdate-d-1,sysdate-d,l_rand); insert into exchange_rate values (‘USD’,’EUR’,sysdate-d-1,sysdate-d,l_rand); end loop; commit; end; /
The first step in using Automatic Data Optimization, John explains, is to enable a new feature in Oracle Database 12c called Heat Map. Simply speaking, it shows the popularity or “hotness” of a segment such as a table or a partition, and it is used by Automatic Data Optimization to decide which segments are candidates for action. If the segment is accessed frequently, it’s considered hot and therefore may not be a candidate for some actions, such as relocation to lower-cost and less-efficient storage.
John enables the Heat Map feature by executing the following command as a DBA user:
alter system set heat_map = on scope=both;
To check the heat map of a specific segment, he looks into the DBA_HEAT_MAP_SEGMENT view. The time stamps of the last activity on the table—UPDATE, SELECT, full table scan, and reference constraint lookup—are recorded in this view. He describes the columns of the view for his listeners:
Enabling Automatic Data Optimization
Before starting the Automatic Data Optimization demonstration, John checks a few things, including the presence of segments in the tablespaces, using the following SQL statement:
select partition_name, tablespace_name from user_segments where segment_name = 'EXCHANGE_RATE'; PARTITION TABLESPACE ————————— —————————— Y13M07 LATEST_TS … output truncated … Y12M07 LATEST_TS
Code Listing 1: Checking for free space
select t.tablespace_name, 100*sum(f.bytes)/sum(t.bytes) free_pct from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) t, (select tablespace_name, sum(bytes) bytes from dba_free_Space group by tablespace_name) f where f.tablespace_name = t.tablespace_name group by t.tablespace_name; TABLESPACE FREE_PCT ——————————— ————————— LATEST_TS 30.00 MIDTERM_TS 99.33
Next, John enables Automatic Data Optimization for the EXCHANGE_RATE table, by executing the following SQL command as the user FOREX (who owns the table):
alter table exchange_rate ilm add policy tier to midterm_ts;
As time passes, Acme DBAs will add more partitions to the table to hold new data, and Listing 2 shows the SQL statements they will use to add the partitions for October, November, and December 2013, named Y13M10, Y13M11, and Y13M12, respectively. To simulate the storage volume growth for that time, John inserts some rows that will go into these partitions, as shown in Listing 2.
Code Listing 2: Adding new partitions
alter table exchange_rate add partition y13m10 values less than (to_date('11/01/2013','mm/dd/yyyy')) tablespace latest_ts; alter table exchange_rate add partition y13m11 values less than (to_date('12/01/2013','mm/dd/yyyy')) tablespace latest_ts; alter table exchange_rate add partition y13m12 values less than (to_date('01/01/2014','mm/dd/yyyy')) tablespace latest_ts; insert into exchange_rate values ('USD', 'GBP', add_months(sysdate,2), add_months(sysdate,2),1); insert into exchange_rate values ('USD', 'GBP', add_months(sysdate,3), add_months(sysdate,3),1); insert into exchange_rate values ('USD', 'GBP', add_months(sysdate,4), add_months(sysdate,4),1); commit;
TABLESPACE FREE_PCT —————————— ————————— LATEST_TS 8.67 MIDTERM_TS 99.33
select partition_name, tablespace_name from user_segments where segment_name = 'EXCHANGE_RATE'; PARTITION TABLESPACE ————————— —————————— Y13M11 LATEST_TS … output truncated … Y12M08 LATEST_TS Y13M07 MIDTERM_TS Y13M06 MIDTERM_TS Y12M12 MIDTERM_TS Y12M07 MIDTERM_TS
Checking the Heat Map
Jill observes that only a few partitions—not all—were relocated to the spillover tablespace. The segments for relocation, John explains, are based on the heat map of the segments, described earlier. The less recently the segment is accessed, the greater the chance that it will be relocated. John shows everyone the heat map of the EXCHANGE_RATE table, by using the SQL statement shown in Listing 3.
Code Listing 3: Checking the last access time for a table
select subobject_name “Part Name”, to_char(segment_write_time,'mm/dd/yy hh24:mi:ss') write_time, to_char(segment_read_time,'mm/dd/yy hh24:mi:ss') read_time, to_char(full_scan,'mm/dd/yy hh24:mi:ss') fts_time from dba_heat_map_segment where owner = 'FOREX' and object_name = 'EXCHANGE_RATE' order by full_scan desc; Part Name WRITE_TIME READ_TIME FTS_TIME ————————— —————————————————— ————————————————— ————————————————— Y13M03 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y12M09 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y13M04 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y12M11 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y13M10 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y12M08 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y13M09 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y13M02 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y13M05 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y13M01 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y12M10 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y13M11 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 19:48:44 Y13M07 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 00:57:12 Y12M07 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 00:57:12 Y12M12 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 00:57:12 Y13M06 07/14/13 12:23:01 07/14/13 12:24:34 07/15/13 00:57:12
Exactly, John confirms. When Automatic Data Optimization had to choose the least recently accessed segment in the LATEST_TS tablespace, it looked at the heat map of the objects in that tablespace and selected these partitions, because they were the least recently accessed.
The group—now suitably impressed—wants to know more about managing the ILM policies. “How can I check the threshold at which Automatic Data Optimization kicks in?” asks Jill. To see that and other parameters ILM uses, John looks at the DBA_ILMPARAMETERS view:
select * from dba_ilmparameters; NAME VALUE ——————————————————— ————— ENABLED 1 JOB LIMIT 10 EXECUTION MODE 3 EXECUTION INTERVAL 15 TBS PERCENT USED 85 TBS PERCENT FREE 25
“Suppose I want to change the 85 percent used to consider the tablespace full,” Jill muses. “Can I change it to, say, 90 percent?” Of course, John answers, and executes the following SQL to change the parameter to 90 percent:
begin dbms_ilm_admin.customize_ilm ( dbms_ilm_admin.tbs_percent_used, 90); end; /
Someone else has another question: “After a period of time, we would like to know what tables are under ILM policies and how the second tier of storage has been defined. Is there a way to get that information?” “Yes, there is,” John assures. He executes the SQL statements shown in Listing 4 to get that information. From the output, he demonstrates that the policy named P21 is defined on the table EXCHANGE_RATE, with the spillover tablespace being MIDTERM_TS. All the partitions of the table inherit the policy from the table (as shown in the INHERITED_FROM column). The last row in the result shows that the table itself is under the policy but is not inherited.
Code Listing 4: Identifying ILM data movement policies
select policy_name, action_type, scope, tier_tablespace, condition_days from user_ILMDataMovementPolicies; POLICY_NAME ACTION_TYPE SCOPE TIER_TABLESPACE ——————————— ——————————— —————— ——————————————— P21 STORAGE SEGMENT MIDTERM_TS select * from user_ILMObjects where object_name = 'EXCHANGE_RATE'; POLICY_NAME SUBOBJECT_NAME OBJECT_TYPE INHERITED_FROM ENA ——————————— —————————————— ——————————————— —————————————— ———— P21 Y12M01 TABLE PARTITION TABLE YES P21 Y12M02 TABLE PARTITION TABLE YES P21 Y12M03 TABLE PARTITION TABLE YES … output truncated … P21 TABLE POLICY NOT INHERITED YES
Code Listing 5: Checking ILM tasks
select task_id, task_owner, to_char(start_time, 'mm/dd/yy hh24:mi:ss') start_time, to_char(completion_time, 'mm/dd/yy hh24:mi:ss') completion_time from dba_ilmtasks where task_owner != 'SYS' TASK_ID TASK_OWNER START_TIME COMPLETION_TIME —————————— —————————— ————————————————— ————————————————— 3 FOREX 07/15/13 10:41:37 07/15/13 10:41:43 -- Find the task details select job_name, job_state, to_char(start_time, 'mm/dd/yy hh24:mi:ss') start_time, to_char(completion_time, 'mm/dd/yy hh24:mi:ss') completion_time from dba_ilmresults where task_id = 3; JOB_NAME JOB_STATE START_TIME COMPLETION_TIME ———————— —————————————————————— ————————————————— ————————————————— ILMJOB18 COMPLETED SUCCESSFULLY 07/13/13 10:41:41 07/13/13 10:41:43
Arup Nanda (email@example.com) has been an Oracle DBA since 1993, handling all aspects of database administration, from performance tuning to security and disaster recovery. He was Oracle Magazine’s DBA of the Year in 2003 and received an Oracle Excellence Award for Technologist of the Year in 2012.