In parts 1 and 2 of this series, we discussed the history of database compression, and the new features in Oracle 12c called Heat Maps and Automatic Data Optimization (ADO). This article will discuss how these technologies can be combined.
The best way to illustrate how Heat Maps and ADO can be combined is through an example. In this example we will create an ILM policy, which will compress the ADODEMO table. The compression will be automatic and based on criteria that there have been no modifications performed on the table since the past 48 hours. In a production Online Transaction Processing (OLTP) system, we assume that dormant data can be compressed (and moved to a separate partition) to save storage and since the data is not frequently accessed, compressing the data will not impact the OLTP type performance.
In order to demonstrate this, we will need to create a procedure to accelerate the passage of time, so that the table qualifies for ADO action even though 2 days have not actually elapsed. Luckily, Oracle has a procedure in their docs that we are able to use to build a new procedure called ado_time.
Below is an example of this type of procedure.
Note: This example is not intended to be used in production and Mythics does not make claims of accuracy or intended consequences. As with anything from the internet, please test this on a sandbox system before touching a production database.
These steps (through line 22) end the creation of the example procedure.
This example uses a test user called ‘bubba’ - with DBA privileges granted.
Also in this example, we grant execute on the procedure to BUBBA, and then turn on the Heat Map option at the instance level.
After enabling heat map tracking, the next step in this example, sets the heat map tracking start time back 2 days to ensure statistics logged after this time are valid and considered by Automatic Data Optimization (ADO).
We now connect as BUBBA and populate the table with data.
The next step in this example checks the size of the uncompressed table. The results show that it is 191 MB.
The next step verifies Heat Map tracking collected statistics for the BUBBA.ADODEMO table.
After verifying Heat Map tracking statistics, we confirm that the table is not currently compressed.
The next step in this example adds a compression policy on BUBBA.ADODEMO table.
We verify that the policy has been added.
The next step in the example looks at the Policies.
Now we will verify that the new policy will allow the data to be moved.
The use case for this example is to see the difference in size over time. Instead of waiting a number of days, we will use the previously created ado_time procedure to roll the clock forward two days.
At this stage we need to flush the In Memory Heat Map data to the persistent tables and views on disk.
A rough and dirty way to do this is to bounce the database rather than wait for the MMON background process to kick in and do the job. Log back in as SYS and bounce the instance.
After restarting the database, and logging back in as bubba, we now see that the Heat Map; statistics are showing that the table was last accessed on March 20th, “two” days ago.
Normally the ADO related jobs are run in the maintenance window. Rather than wait for this window to occur, we trigger it manually via the DBMS_ILM.EXECUTE_ILM procedure called from this PL/SQL block.
We can now query the USER_ILMTASKS view and see that the ADO job has been started and the ILM policy we have defined has been earmarked for execution.
Let’s get the details for task_id 12,
In the next step of the example, we can now see that the table has been compressed and the ADO job has been completed.
We can also check what compression was used on the table.
So for the purpose of this data compression example, we see the results here. After the compression, the table has been reduced in size from 192 MB to 49 MB!
To clean up, we can remove the ILM policy on the table if required.
In this example, we used Automatic Data Optimization to automatically reduce the cold data from 192MB to 40MB, a reduction of over four times! Best of all, this is a simple automatic process, allowing complex tables to be compressed and partitioned with a simple rule. For more information about how ADO can help your database, please reach out to your Mythics Account Manager.
Erik Benner, Enterprise Architect, Mythics Inc.