ADO policy evaluation
and enforcement
While the ADO environment is self-managing and does its actions in
maintenance windows, a couple of packages are useful to the administrator who
wishes to do an ad hoc inspection or wants to tweak the ADO environment.
Procedure call
|
Useful for
|
dbms_ilm_admin.CUSTOMIZE_ILM
|
Customization
of ILM policy evaluation to adjust and policies are evaluated.
|
dbms_ilm.PREVIEW_ILM
|
Evaluation
of an ILM policy to see if its triggering conditions are met.
|
Dbms_ilm.EXECUTE_ILM
|
To
execute an ADO
task.
|
Example of ILM (storage tiering):
A storage
tiering policy specifies that a table’s data will be migrated to a named
tablespace, when the amount of free space in the containing tablespace goes
down below a threshold (due to data growth).
We will create
a test table and attach an ILM policy to it, pump it with data, and observe how
ILM treats it in response to its growth.
About configuring the ILM
environment for testing
The
threshold at which an ILM action is launched in response to free space
reduction can be customized, using the DBMS_ILM_ADMIN package
(dbms_ilm_admin.customize_ilm).
We will
configure the ILM environment such that ILM policy that we define will be
triggered as soon as the amount of free space in the tablespace becomes less
than or equal to 95% (meaning 5% or more of the tablespace’s available space is
already used up).
SQL> begin
2
dbms_ilm_admin.customize_ilm(parameter => dbms_ilm_admin.TBS_PERCENT_USED,value
=> 5);
3
dbms_ilm_admin.customize_ilm(parameter =>
dbms_ilm_admin.TBS_PERCENT_FREE,value =>
95);
4 end;
5 /
PL/SQL procedure successfully
completed
This sets
up the ilm threshold to an suitably low value so that the ilm policies and
actions trigger without having to pump a lot of data into the test table.
Further, we
will use a test tablespace called users, which have only 5 MB of data and
cannot auto extend.
Creating a test table and
populating it with bulk data:
create table employees (
emp_id number, name varchar(20),
join_date date, mgr number,
dept_id number)
tablespace users;
Inserting about 20,000 records in it,
to use up about all the space in the users tablespace.
SQL> insert into employees
2 select rownum, 'emp' ||
ltrim(to_char(rownum,'0999999')) , sysdate, 123, 100 from dual
3 connect by level<=20000
4 ;
20000 rows inserted
SQL> commit;
Commit complete
Then it is
necessary to attach a storage-tiering policy to the table. The policy will
specify that the table’s rows must be moved to a different tablespace named
LOW_COST_STORE (when the tablespace usage reaches the thresholds set up for ilm
as mentioned above).
SQL> alter table employees
2 ilm add policy
3 tier to low_cost_store;
Table altered.
Now, it is
time for the database to evaluate the policy, which we will do (manually in
this case) with this script (as the scott user). Normally the database would
evaluate the policies in the maintenance windows.
The script
example below, invokes the dbms_ilm.preview_ilm
procedure, which will evaluate all the ADO
policies in the current schema, and creates a task to enforce it (but in the
inactive state). The user may execute the created task immediately with a
script (or else it would execute in a maintenance window).
SQL> set serveroutput on
SQL>
SQL> declare
2 v_task_id number:=0;
3
BEGIN
4 dbms_ilm.preview_ilm(task_id =>
v_task_id,ilm_scope => dbms_ilm.SCOPE_SCHEMA);
5 dbms_output.put_line('task id = ' ||
v_task_id);
6 if v_task_id is null then
7 dbms_output.put_line('task id is null');
8 end if;
9
END;
10
/
task id =
task id is null
PL/SQL procedure successfully completed
However,
the task id that it is supposed to return to the caller is NULL (I do not know
why).
So I query
the user_ilmtasks view to see the task that it created, and I retrieve its id
from there (task id = 128).
SQL> select task_id,
policy_name, object_owner, object_name, selected_for_execution
from user_ilmevaluationdetails order by task_id desc ;
2
TASK_ID POLICY_NAM
OBJECT_OWNER OBJECT_NAME SELECTED_FOR_EXECUTION
------- ----------
--------------- --------------- ------------------------------
128 P203 SCOTT EMPLOYEES SELECTED FOR EXECUTION
SQL>
Then query the user_ilmtasks to examine the task status
(INACTIVE)
SQL> select task_id, state,
to_char(creation_time,'dd-mon-yyyy hh24:mi:ss') creation_time
from user_ilmtasks where task_id=128;
2
TASK_ID STATE CREATION_TIME
------- ---------
--------------------
128 INACTIVE 27-nov-2014 13:17:52
Finally we
can execute the ilm task using a script. The dbms_ilm.execute_ilm_task executes
a previously evaluated ADO
task. In the example below, I supply the previously retrieved task id to the
dbms_ilm.execute_task procedure.
SQL> declare
v_task_id number:=128;
begin
dbms_ilm.execute_ilm_task(task_id
=> v_task_id,
execution_mode =>
dbms_ilm.ILM_EXECUTION_ONLINE,
execution_schedule =>
dbms_ilm.SCHEDULE_IMMEDIATE);
end;
2 3
4 5 6
7 8 /
PL/SQL procedure successfully completed.
The
results of the task execution are available in the user_ilmresults
SQL> select task_id, job_name, job_state,
to_char(start_time,'dd-mon-yyyy hh24:mi:ss') start_time,
to_char(completion_time,'dd-mon-yyyy hh24:mi:ss') completion_time
from user_ilmresults where task_id=128;
2 3 4
TASK_ID JOB_NAME
JOB_STATE
START_TIME
COMPLETION_TIME
------- --------------- ------------------------- --------------------
--------------------
128 ILMJOB318 COMPLETED SUCCESSFULLY 27-nov-2014 13:19:29 27-nov-2014 13:19:29
After the
task completed, we can see that the table employees (which was originally in
the USERS tablespace) is now contained in the LOW_COST_STORE tablespace.
SQL> select
tablespace_name from user_tables where table_name='EMPLOYEES';
TABLESPACE_NAME
------------------------------
LOW_COST_STORE
This was
an example of storage tiering where the database has moved the table data to a
different class of storage (presumably low-cost, bulk storage) in response to a
space pressure condition.
Example of ILM (compression
tiering):
In case of
compression tiering, specific rows of data (with different ages) are compressed
in-place, when policy-specified conditions are satisfied, like a certain amount
of time has passed since those rows were accessed or modified. Older rarely
accessed rows can be compressed to a greater degree to save space, whereas
newer rows may not be compressed so that they can be accessed without having to
de-compress them.
An example
of a compression tiering policy would typicially specify at the row level, for
example
Alter
table employees
ilm add policy
row store compress advanced row
after 10 days of no modification;
This is a
row level ado policy, which means the policy will continue to apply to future
rows so that they may be compressed when they become eligible. Further, as per
the Oracle 12c obe page, for a table with a row level policy, compression can
take place only if all the rows in a block satisfy the policy criteria.
On the
other hand, a segment level policy applies to the entire segment, and will
become automatically disabled after it executes at least once. Segment level
compression tiering policies are meant for partitioned tables.
Demonstrating
below a row level compression tiering policy
Re-creating
the same employee table…
SQL>
SQL> drop table employees;
Table dropped
SQL> create table employees (
2 emp_id number, name varchar(20),
3 join_date date, mgr number, dept_id number)
4 /
Table created
SQL>
SQL> insert /*+
APPEND */into employees
2 select rownum, 'emp' ||
ltrim(to_char(rownum,'0999999')) , sysdate, 123, 100 from dual
3 connect by level<=20000;
20000 rows inserted
SQL> commit;
Commit complete
SQL>
Attaching
the row level policy to the table…
SQL>
SQL> Alter table employees
2 Ilm add policy
3 Row store compress advanced
4 row after 10 days of no modification;
Table altered
Flushing the heat map metadata to disk :
SQL>
SQL>
begin
2
dbms_ilm.flush_all_segments;
3
end;
4 /
PL/SQL
procedure successfully completed.
Evaluating
the ILM policy…
SQL> set
serveroutput on
SQL>
SQL> declare
2
v_task_id number:=0;
3
BEGIN
4
dbms_ilm.preview_ilm(task_id => v_task_id,ilm_scope =>
dbms_ilm.SCOPE_SCHEMA);
5
dbms_output.put_line('task id = ' || v_task_id);
6
if v_task_id is null then
7
dbms_output.put_line('task id is null');
8
end if;
9 END;
10 /
task id =
task id is null
PL/SQL procedure
successfully completed
Retrieving
the task id…
SQL>
SQL> select task_id, policy_name, object_owner,
object_name, selected_for_execution
2 from user_ilmevaluationdetails order by
task_id desc ;
TASK_ID
POLICY_NAME
OBJECT_OWNER
OBJECT_NAME
SELECTED_FOR_EXECUTION
----------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------
164 P305
SCOTT
EMPLOYEES
SELECTED FOR EXECUTION
Executing
the ilm task with id=164
SQL> declare
2 v_task_id number:=164;
3 begin
4 dbms_ilm.execute_ilm_task(task_id =>
v_task_id,
5 execution_mode =>
dbms_ilm.ILM_EXECUTION_ONLINE,
6 execution_schedule =>
dbms_ilm.SCHEDULE_IMMEDIATE);
7 end;
8 /
PL/SQL procedure successfully completed
Gathering
optimizer stats (to collect metadata into user_tables)
SQL> BEGIN
2
DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMPLOYEES');
3 END;
4 /
PL/SQL procedure successfully completed
Analyzing
the compression stats for the table’s rows… (as per Oracle
docs, compression_type=4096 is basic compression, compression_type=1 is No
compression ref DBMS_COMPRESSION).
I am not
getting advanced compression; and do not know the reason at this time, but
compression tiering is demonstrated here.
SQL> select compression_type, COUNT(*)
2 from (select
dbms_compression.get_compression_type(ownname => 'SCOTT',
3
tabname => 'EMPLOYEES',
4 row_id => t.rowid) compression_type
5 from scott.employees t)
6 GROUP BY COMPRESSION_TYPE
7 /
COMPRESSION_TYPE
COUNT(*)
---------------- ----------
1 56
4096 19944
About 56 rows
are non-compressed, whereas about 19,944 rows are advanced-compressed.
If more
rows were to be added, they’d be stored uncompressed,
until the ADO
policy kicks in again.
Adding a
few more rows…
insert into
employees
select rownum, 'emp' || ltrim(to_char(rownum,'0999999')) , sysdate, 123, 100 from dual
connect by level<=500;
…
Checking
the compression statistics again…
SQL> select compression_type, COUNT(*)
from (select
dbms_compression.get_compression_type(ownname => 'SCOTT',
tabname => 'EMPLOYEES',
row_id => t.rowid)
compression_type
from
scott.employees t)
GROUP BY
COMPRESSION_TYPE 2 3
4 5 6 ;
COMPRESSION_TYPE
COUNT(*)
---------------- ----------
1 556
4096 19944
Again
this table has few rows uncompressed whereas others are compressed.
