Students at Oracle is a blog created by internal students from Oracle to help people introducing them to Oracle products with tutorials, viewlets and guides.
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.
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:
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.
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:
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
: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
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_NAMECOMPRESS COMPRESS_FOR
NOCOMPDISABLED
BASICCOMPENABLED BASIC
OLTPCOMPENABLEDOLTP
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_NAMEMB
----------------------------------
NOCOMP27
BASICCOMP24
OLTPCOMP8
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.)