Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

2011/03/28

Combine Partitioning, Advanced Compression and TotalRecall features in Oracle Database 11g

Using Partitioning together, Advanced Compression and TotalRecall features can reduce the workload and, thus, work more efficiently.

Partitioning provides several ways of partitioning a table into multiple subtables and define multiple criteria partition from a Partition Key.

Advanced Compression provides multiple compression features, including OLTP compression. This feature is widely explained and tested in this same blog. Follow this link if you want to explore it deeper: http://studentsatoracle.blogspot.com/2010/12/advanced-compression-in-oracle-database_03.html.

Last, but no least, TotalRecall creates a flashback file to record activity on the table, so the user can see the value that had a record of that table on a certain day and time. In addition, the user can define the time of data retention and the quota of disk space used for it.

We'll test a "worst-case" scenario using all three features on a test table. We will create a table partitioned in two subtables, each one of them having a different compression type. Also, we'll set a flashback file so we can query the values of the table for a year.

But, we'll just focus on the most efficient way of combining the three features, we won't check any metrics like execution times or compression rate.

First we must do some thinking. Partitions of a table must be defined at the create table command while compression can be either defined at the creation of the table or later with the alter table command. We can alter a partitioned table, but due to limitations of partitioning, we can't alter the compression type of a subtable. So, we'll have to define the partitions and the compression type when we create the table.

So, let's create our test table:


create table atest ( c1 number(5), c2 varchar2(5), c3 varchar2(5), primary key (c1) validate)
partition by range(c1) (
partition atest_p1 values less than(6) compress,
partition atest_p2 values less than(maxvalue) compress for oltp
);


With this setting, we split the newly created table test in two subtables and the criteria for the partition is the range of values at the column c1. Any item with c1 lower than 6 will go to the subtable atest_p1, while the rest will go to atest_p2. In addition, we define the compression type for the subtables. Subtable atest_p1 has a basic compression, while atest_p2 has OLTP compression.

Then we fill it a little with some silly loop:


insert into atest
select rownum, (rownum*2)+2, (rownum*3)+3
from dual connect by rownum <= 10;


Here comes the funny part. Once partitions, compression types and data are set, it's time to test the TotalRecall feature. First of all, we will need to create a new Tablespace: 


create tablespace flasharch;



Now, we'll create and set the flashback file:



create flashback archive atest_fla tablespace flasharch quota 1M retention 1 year;


Once we create the flashback, we have to alter our test table to "link" them:


alter table atest flashback archive atest_fla;


And that's all there is to it. Now, let's give it a shot.

Make a simple update, changing the values inside our test table:


update atest set c2=c2+c3+12;


Then, execute a query refering to a moment before the update. To make things more difficult, let's ask only for the values of subtable atest_p2 (change the value inside the brackets for the time before the update if testing):


select * from atest partition (atest_p2) as of timestamp to_timestamp('DD-MMM-YY hh.mm.ss');


Keep in mind that once we alter the table to the flashback, we won't be able to perform some operations on that table, mostly alter table. We'll have to drop the flashback if we want to perform major changes to the "flashbacked" table. So, while this method gives all the benefits of the three features, also has the disadvantage of making the table somewhat static and unalterable. This means there must be some previous planning, because dismantling the settings may end in loss of information. Knowing all that, the advantages of using various powerful features integrated in the database are obvious.

Thanks for reading and feel free to leave any comments or questions.

2011/02/04

How to delete files older than X days in Windows Batch

In this entry I will explain how to delete files in a Windows environment depending on its age.

This task is rather easy in Unix but in Windows (DOS) it gets quite more complicated.

Now imagine you have some folders storing all the backups the system does, and now you would like to clean those folders so they just have the last ones.

I searched a lot and seems that with normal Batch is almost impossible to do it, and after trying like 3 or 4 different options I found this one to be the best overall.

First off, as I already said, with normal batch this task is so complicated, that is why we will use and extra tool called Robocopy. This tool is included in the Windows Server 2003 Resource Kit Tools which you can download from here:

http://download.microsoft.com/download/8/e/c/8ec3a7d8-05b4-440a-a71e-ca3ee25fe057/rktools.exe

Robocopy is a powerful copy command with lots of useful options.

Then idea is quite simple:

- We will create a temporal folder.
- We will move the files we want to delete to that folder.
- Finally we will delete the folder created with the files in it.

Now on to the code.

I will just put the code commenting each line, instead of writing what it does in a not-so-easy-to-understand paragraph.

* * * * *

@ECHO OFF
ECHO # Starting...
ECHO.

REM Setting the log file name
SET day=%date:~0,2%
SET mon=%date:~3,2%
SET year=%date:~6,4%
SET logfile_name=clearing_log_%day%-%mon%-%year%

REM Writing the first line in the log including the time
SET time=%time:~0,5%
ECHO # Clearing.bat launched at %time% > %logfile_name%.txt
ECHO. >> %logfile_name%.txt

REM Program Start

REM variables - here just write the paths of the folders you want to clear
SET folder_1_path="C:\Documents and Settings\user\Desktop\Folder_1"
SET folder_2_path="C:\Documents and Settings\user\Desktop\Folder_2"

REM this next variable will be the path of the temporal folder (you can write whatever path you want as it will be deleted)
SET path_temp_folder="C:\Documents and Settings\user\Desktop\Justin Bieber"

REM first we will check if the folders specified in the path variables exists
IF EXIST %folder_1_path% (
GOTO folder_1_exists
) ELSE (
ECHO [ERROR] The folder 1 does not exist.
ECHO [ERROR] The folder 1 does not exist. >> %logfile_name%.txt
GOTO final
)

:folder_1_exists
REM now we check if the second folder exists
IF EXIST %folder_2_path% (
GOTO folder_2_exists
) ELSE (
ECHO [ERROR] The folder 2 does not exist.
ECHO [ERROR] The folder 2 does not exist. >> %logfile_name%.txt
GOTO final
)

:folder_2_exists
REM now we will delete the files in the first folder

REM first we create the temporal folder
MKDIR %path_temp_folder%

REM now we move the files older than X (in this case 7) days to that folder
ROBOCOPY %folder_1_path% %path_temp_folder% /move /minage:7 >nul
REM ROBOCOPY syntax = source destination [file [file]…] [options]

REM and finally as stated we delete the temporal folder
RMDIR %path_temp_folder% /s /q

ECHO Folder 1 cleaned.
ECHO Folder 1 cleaned. >> %logfile_name%.txt

GOTO clear_1_done

:clear_1_done
REM now we will delete the files in the second folder

REM first we create the temporal folder
MKDIR %path_temp_folder%

REM now we move the files older than X (in this second case 3) days to that folder
ROBOCOPY %folder_2_path% %path_temp_folder% /move /minage:3 >nul
REM ROBOCOPY syntax = source destination [file [file]…] [options]

REM and finally as stated we delete the temporal folder
RMDIR %path_temp_folder% /s /q

ECHO Folder 2 cleaned.
ECHO Folder 2 cleaned. >> %logfile_name%.txt

GOTO final

:final
ECHO.
ECHO # Done.
ECHO. >> %logfile_name%.txt
ECHO # Done. >> %logfile_name%.txt
ECHO # Log file = %logfile_name%.txt
ECHO # Press any key to exit.
PAUSE>NUL

* * * * *

That's it.
Just create and new .bat file, copy the code above, modify it (paths, older days...etc) to match your goals and voilà ;)

Hope it helps.

2010/12/03

Advanced Compression in Oracle Database 11g

Oracle database release 11g introduced a new type of compression called ”Advanced Compression”. This option provides multiple features including OLTP table compression.


So, let’s test out this new feature.


First of all, we are going to create 3 new tables:


nocomp – table without compression

basiccomp – basic compression

oltpcomp – advanced compression


create table nocomp (a int, b char(30), c date);

create table basiccomp (a int, b char(30), c date) compress;

create table oltpcomp (a int, b char(30), c date) compress for oltp;


Now we check its creation.

select table_name,compression,compress_for from user_tables where table_name in ('NOCOMP','BASICCOMP', 'OLTPCOMP');


TABLE_NAME COMPRESS COMPRESS_FOR

NOCOMP DISABLED

BASICCOMP ENABLED BASIC

OLTPCOMP ENABLED OLTP


Next we add 500.000 rows with some kind of repetition so we can use the compression.


Previously to the inserts, we execute the followings commands so we can start checking the times for each operation plus some other execution information:


set timing on

set autotr on


Now the insertions:


insert into nocomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


insert into basiccomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


insert into oltpcomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


Result Times:

nocomp = 11.29 s

basiccomp = 13.57 s

oltpcomp = 22.36 s


As we can see, insertions with OLTP compression are slower.


Also, we can see that the table with OLTP compression uses a lot more redo than the other two.


Redo size = 28M8 / 28M7 / 95M1


But, let’s see how the pure data compression go.


select segment_name,round(bytes/1024/1024,2) Mb from user_segments where segment_name in (‘NOCOMP’,’BASICCOMP’,’OLTPCOMP’);


SEGMENT_NAME MB

----------------------------------

NOCOMP 27

BASICCOMP 24

OLTPCOMP 8


As we can see the OLTP compression almost compressed the data by 1/3.


OLTP Compression is slower and uses more redo but its compression is quite impressive.


Next we are going to do the same experiments but this time with direct inserts.


insert /*+ APPEND */ into nocomp select rownum , chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


insert /*+ APPEND */ into basiccomp select rownum, chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


insert /*+ APPEND */ into oltpcomp select rownum ,chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;


select segment_name,blocks,round(bytes/1024/1024,2) Mb from user_segments where segment_name in (‘NOCOMP’,’BASICCOMP’,’OLTPCOMP’);


Results for Time and Space:

nocomp = 11.23 s / 24 MB

basiccomp = 11.40 s / 7 MB

oltpcomp = 11.32 s / 7 MB


With direct inserts, the times for the insertions are almost the same, but the two tables with some kind of compression use equally 1/3 of the space.



Finally, we are going to test how the different tables behave respect update and select operations.


First though, we increase the number of inserted rows from 500.000 to 2 milions.


(delete the tables and create new ones)


insert into nocomp select rownum , chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 2000000;


insert into basiccomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 2000000;


insert into oltpcomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 2000000;


Times for the insertions:

nocomp = 48 s

basiccomp = 51s

oltpcomp = 1 m 25 s


select segment_name,blocks,round(bytes/1024/1024,2) Mb from user_segments where segment_name in (‘NOCOMP’,’BASICCOMP’,’OLTPCOMP’);


Space used per table:

nocomp = 112 MB

basiccomp = 104 MB

oltpcomp = 31 MB


Once checek the time and space, we focus on the selects and updates:


select a from nocomp where b='A' and a>200000 and a<300000;

select a from basiccomp where b='A' and a>200000 and a<300000;

select a from oltpcomp where b='A' and a>200000 and a<300000;


Times:

nocomp = 22.28 s

basiccomp = 22.67 s

oltpcomp = 21.64 s


update nocomp set b='C' where b='B' and rownum <= 200000;

update basiccomp set b='C' where b='B' and rownum <= 200000;

update oltpcomp set b='C' where b='B' and rownum <= 200000;


Times:

nocomp = 5 s

basiccomp = 10 s

oltpcomp = 1 m 10 s


As we can see, both the inserts and especially the updates are slower with oltp compression but on the other hand, the selects take the same time and it uses much less space.


That's it. Thanks for reading.


(We might test LOBs compression in the near future.)