Skip navigation.

Hemant K Chitale

Syndicate content
I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
follow me on twitter : @HemantKChitale
Updated: 4 hours 22 min ago

FBDA -- 3 : Support for TRUNCATEs

Mon, 2016-04-04 09:13
One of the points in the previous post was how ADD and DROP Column commands are supported in FBDA.

Let's look at support for TRUNCATEs.

(Just to prove that I am not using the Undo tablespace for the AS OF query, I drop the Undo tablespace) :

SQL> startup
ORACLE instance started.

Total System Global Area 402653184 bytes
Fixed Size 2924928 bytes
Variable Size 260050560 bytes
Database Buffers 134217728 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/NONCDB/PARAMETERFILE/spf
ile.267.896483727
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> create undo tablespace undotbs2;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTBS2';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 402653184 bytes
Fixed Size 2924928 bytes
Variable Size 260050560 bytes
Database Buffers 134217728 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 402653184 bytes
Fixed Size 2924928 bytes
Variable Size 260050560 bytes
Database Buffers 134217728 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>


I now TRUNCATE my test table and then run an AS OF query against it.

SQL> connect hemant/hemant
Connected.
SQL> select count(*) from test_fbda;

COUNT(*)
----------
1000

SQL> truncate table test_fbda;

Table truncated.

SQL> select count(*) from test_fbda;

COUNT(*)
----------
0

SQL> select count(*) from test_fbda as of timestamp (sysdate-1);

COUNT(*)
----------
1000

SQL>


The AS OF query was satisfied by the FBDA History table  (and not from Undo).

However, because of yesterday's test of ADD and DROP columns (see yesterday's post), the table structure doesn't match AS OF yesterday.  So, I work around the difference by naming the columns.

SQL> insert into test_fbda select * from test_fbda as of timestamp (sysdate-1);
insert into test_fbda select * from test_fbda as of timestamp (sysdate-1)
*
ERROR at line 1:
ORA-00913: too many values


SQL>
SQL> desc test_fbda
Name Null? Type
----------------------------------------- -------- ----------------------------
ID_COLUMN NUMBER
DATA_COLUMN VARCHAR2(15)
DATE_INSERTED DATE

SQL> insert into test_fbda
2 select id_column, data_column, date_inserted
3 from test_fbda as of timestamp (sysdate-1);

1000 rows created.

SQL> commit;

Commit complete.

SQL>


Thus, when my query matches the table structure, I can use the data from the previous version of the query.

Note how I did NOT use the SYS_FBA_HIST_93250 table as I'd shown in my previous posts.  The History table is NOT to be accessed directly but I demonstrated queries against it it to show the underlying architecture in FBDA.
.
.
.



Categories: DBA Blogs

FBDA -- 2 : FBDA Archive Table Structure

Sun, 2016-04-03 10:10
Following up on my earlier post, I look at the FBDA Archive Tables.

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 3 23:26:27 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Sat Apr 02 2016 23:32:30 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
TEST_FBDA
SYS_FBA_DDL_COLMAP_93250
SYS_FBA_HIST_93250
SYS_FBA_TCRV_93250

SQL> desc test_fbda
Name Null? Type
----------------------------------------- -------- ----------------------------
ID_COLUMN NUMBER
DATA_COLUMN VARCHAR2(15)
DATE_INSERTED DATE

SQL>
SQL> desc sys_fba_hist_93250
Name Null? Type
----------------------------------------- -------- ----------------------------
RID VARCHAR2(4000)
STARTSCN NUMBER
ENDSCN NUMBER
XID RAW(8)
OPERATION VARCHAR2(1)
ID_COLUMN NUMBER
DATA_COLUMN VARCHAR2(15)
DATE_INSERTED DATE

SQL> desc sys_fba_ddl_colmap_93250
Name Null? Type
----------------------------------------- -------- ----------------------------
STARTSCN NUMBER
ENDSCN NUMBER
XID RAW(8)
OPERATION VARCHAR2(1)
COLUMN_NAME VARCHAR2(255)
TYPE VARCHAR2(255)
HISTORICAL_COLUMN_NAME VARCHAR2(255)

SQL> desc sys_fba_tcrv_93250
Name Null? Type
----------------------------------------- -------- ----------------------------
RID VARCHAR2(4000)
STARTSCN NUMBER
ENDSCN NUMBER
XID RAW(8)
OP VARCHAR2(1)

SQL>


The HIST table is the History table for my active table. It adds columns that track Rowid, Start SCB and End SCN for a range of rows that are copied into the History Table, Transaction Identifier, Operation and then the actual columns of the active table.
The DDL_COLMAP table seems to track Column Mappings.  See example below.
The TCRV table seems to be tracking Transactions ?

Let's try some DDL to ADD and DROP columns to the active table.

SQL> alter table test_fbda add (new_col_1 varchar2(5));

Table altered.

SQL> desc test_fbda
Name Null? Type
----------------------------------------- -------- ----------------------------
ID_COLUMN NUMBER
DATA_COLUMN VARCHAR2(15)
DATE_INSERTED DATE
NEW_COL_1 VARCHAR2(5)

SQL> desc sys_fba_93250
ERROR:
ORA-04043: object sys_fba_93250 does not exist


SQL> desc sys_fba_hist_93250
Name Null? Type
----------------------------------------- -------- ----------------------------
RID VARCHAR2(4000)
STARTSCN NUMBER
ENDSCN NUMBER
XID RAW(8)
OPERATION VARCHAR2(1)
ID_COLUMN NUMBER
DATA_COLUMN VARCHAR2(15)
DATE_INSERTED DATE
NEW_COL_1 VARCHAR2(5)

SQL>
SQL> select * from sys_fba_ddl_colmap_93250
2 /

STARTSCN ENDSCN XID O
---------- ---------- ---------------- -
COLUMN_NAME
--------------------------------------------------------------------------------
TYPE
--------------------------------------------------------------------------------
HISTORICAL_COLUMN_NAME
--------------------------------------------------------------------------------
1697151
ID_COLUMN
NUMBER
ID_COLUMN

1697151
DATA_COLUMN
VARCHAR2(15)
DATA_COLUMN

1697151
DATE_INSERTED
DATE
DATE_INSERTED

1728713
NEW_COL_1
VARCHAR2(5)
NEW_COL_1


SQL>


The new column added to the active table is also now reflected in the History Table.  The DDL_COLMAP shows the effective start of each column (notice the STARTSCN mapped to the COLUMN_NAME)

Let's set some values in new column and see if they appear in the History Table.

SQL> update test_fbda set new_col_1 = 'New'
2 where id_column < 6;

5 rows updated.

SQL> commit;

Commit complete.

SQL> select id_column, new_col_1, scn_to_timestamp(startscn), scn_to_timestamp(endscn)
2 from sys_fba_hist_93250
3 where id_column < 6
4 order by 1,3;

ID_COLUMN NEW_C
---------- -----
SCN_TO_TIMESTAMP(STARTSCN)
---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ENDSCN)
---------------------------------------------------------------------------
1
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

2
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

3
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

4
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

5
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM


SQL>


What rows are copied into the History Table are *prior* image rows (copied from the Undo Area).  The STARTSCN and ENDSCN are of *yesterday* (02-April).

Let me DROP the new column.

SQL> alter table test_fbda drop (new_col_1);

Table altered.

SQL> desc test_fbda
Name Null? Type
----------------------------------------- -------- ----------------------------
ID_COLUMN NUMBER
DATA_COLUMN VARCHAR2(15)
DATE_INSERTED DATE

SQL> desc sys_fba_hist_93250;
Name Null? Type
----------------------------------------- -------- ----------------------------
RID VARCHAR2(4000)
STARTSCN NUMBER
ENDSCN NUMBER
XID RAW(8)
OPERATION VARCHAR2(1)
ID_COLUMN NUMBER
DATA_COLUMN VARCHAR2(15)
DATE_INSERTED DATE
D_1729869_NEW_COL_1 VARCHAR2(5)

SQL>
SQL> select * from sys_fba_ddl_colmap_93250;

STARTSCN ENDSCN XID O
---------- ---------- ---------------- -
COLUMN_NAME
--------------------------------------------------------------------------------
TYPE
--------------------------------------------------------------------------------
HISTORICAL_COLUMN_NAME
--------------------------------------------------------------------------------
1697151
ID_COLUMN
NUMBER
ID_COLUMN

1697151
DATA_COLUMN
VARCHAR2(15)
DATA_COLUMN

1697151
DATE_INSERTED
DATE
DATE_INSERTED

1728713 1729869
D_1729869_NEW_COL_1
VARCHAR2(5)
NEW_COL_1


SQL>


The dropped column is no longer in the active table  and has been renamed in the History table.  (The data in the column has to be preserved but the column is renamed).  Notice how the DDL_COLMAP table now shows an ENDSCN for this column, with the new (renamed)  column as in the History table.  The column name seems to include the SCN (ENDSCN ?)

Let's confirm what data is now present in the History table  (remember : Our earlier query showed the pre-update image for this column).

SQL> select id_column, D_1729869_NEW_COL_1, scn_to_timestamp(startscn), scn_to_timestamp(endscn)
2 from sys_fba_hist_93250
3 where (id_column < 6 OR D_1729869_NEW_COL_1 is not null)
4 order by 1,3;

ID_COLUMN D_172
---------- -----
SCN_TO_TIMESTAMP(STARTSCN)
---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ENDSCN)
---------------------------------------------------------------------------
1
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

1
02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM

1 New
03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM

2
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

2
02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM

2 New
03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM

3
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

3
02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM

3 New
03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM

4
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

4
02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM

4 New
03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM

5
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

5
02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM

5 New
03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM


15 rows selected.

SQL>
SQL> select scn_to_timestamp(1729869) from dual;

SCN_TO_TIMESTAMP(1729869)
---------------------------------------------------------------------------
03-APR-16 11.45.27.000000000 PM

SQL>


Why do we now have 3 rows in the History table for each row in the Active Table ?  Take ID_COLUMN=1.  The first row -- for the time range 02-Apr 11:32pm to 02-Apr 11:46pm--  is as of yesterday, the same row we saw in the History table after the update in the active table.  The second row is the representation to preserve the row for the time rang 02-Apr 11:46pm to 03-Apr 11:41pm to support AS OF queries upto the time of the UPDATE.  The third row for the time range 03-Apr 11:41pm to 03-Apr 11:45pm is to present the UPDATEd value ('New') in the column upto the last transaction updating it before the column was dropped at 03-Apr 11:45:27pm.

Thus, Oracle maintains multiple versions of the same row, including versions for DROPped columns, in the History Table.

Note :  The History Table is not supposed to be directly queried in the manner I have shown here.  The proper query against the active table would be an AS OF query which is automatically rewritten / redirected to "hit" the History table when necessary.

What about the third table table -- the TCRV table ?

SQL> l
1 select scn_to_timestamp(startscn), op , count(*)
2 from sys_fba_tcrv_93250
3 group by scn_to_timestamp(startscn), op
4* order by 2,1
SQL> /

SCN_TO_TIMESTAMP(STARTSCN) O
--------------------------------------------------------------------------- -
COUNT(*)
----------
03-APR-16 11.45.24.000000000 PM U
1000


SQL>
SQL> select count(distinct(rid)) from sys_fba_tcrv_93250;

COUNT(DISTINCT(RID))
--------------------
1000

SQL>



It shows 1000 rows has having been UPDATEd ? (Does OP='U' mean 'UPDATE). We do know that ADD and DROP column are changes to the table.  But are they UPDATEs ?

Next post : Continuing with DML operations (more rows, some updates).  We'll see if we can decipher anything rom the TCRV table as well. Changed to showing support for TRUNCATEs.
.
.
.



Categories: DBA Blogs

FBDA -- 1 : Testing Flashback Data Archive in 12c (NonCDB)

Sat, 2016-04-02 09:53
Note : At the bottom of this post, you'll find links to more (subsequent) posts on this topic.

Some testing I'd done with Flashback Data Archive (henceforth called FBDA in this and subsequent posts, if any) in 11.2.0.4 left me with uncertainty about the automatic purging of data beyond the Retention Period.  I might return to testing 11.2.0.4, but here I shall begin testing in 12.1.0.2  (NonCDB).

Setting up FBDA :

[oracle@ora12102 ~]$ sqlplus system/oracle

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 2 23:23:53 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Sat Apr 02 2016 23:20:47 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create tablespace fbda ;

Tablespace created.

SQL> create flashback archive fbda tablespace fbda retention 3 day;

Flashback archive created.

SQL> create tablespace hemant;

Tablespace created.

SQL> create user hemant identified by hemant
2 default tablespace hemant;

User created.

SQL> grant create table to hemant;

Grant succeeded.

SQL> grant create session to hemant;

Grant succeeded.

SQL> alter user hemant quota unlimited on hemant;

User altered.

SQL> alter user hemant quota unlimited on fbda;

User altered.

SQL> grant flashback archive administer to hemant;

Grant succeeded.

SQL> grant flashback archive on fbda to hemant;

Grant succeeded.

SQL>
SQL> connect / as sysdba
Connected.
SQL> grant execute on dbms_flashback_archive to hemant;

Grant succeeded.

SQL>
SQL> connect hemant/hemant
Connected.
SQL> create table test_fbda (id_column number, data_column varchar2(15), date_inserted date) tablespace hemant;

Table created.

SQL> alter table test_fbda flashback archive fbda;

Table altered.

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
TEST_FBDA

SQL>


Note the Flashback Archive history table corresponding to TEST_FBDA doesn't get created immediately.

SQL> connect hemant/hemant
Connected.
SQL> insert into test_fbda
2 select rownum , to_char(rownum), trunc(sysdate)
3 from dual connect by level < 1001;

1000 rows created.

SQL> commit;

Commit complete.

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
TEST_FBDA

SQL> select count(*) from test_fbda;

COUNT(*)
----------
1000

SQL> select flashback_archive_name, retention_in_days, status
2 from user_flashback_archive;

FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
RETENTION_IN_DAYS STATUS
----------------- -------
FBDA
3


SQL> select table_name, flashback_archive_name, archive_table_name, status
2 from user_flashback_archive_tables;

TABLE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- -------------
TEST_FBDA
FBDA
SYS_FBA_HIST_93250 ENABLED


SQL>
SQL> !sleep 300

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
TEST_FBDA
SYS_FBA_DDL_COLMAP_93250
SYS_FBA_HIST_93250
SYS_FBA_TCRV_93250

SQL>
SQL> select object_id
2 from user_objects
3 where object_name = 'TEST_FBDA'
4 and object_type = 'TABLE'
5
SQL> /

OBJECT_ID
----------
93250

SQL>


So, it took some time for the flashback archive history table (identified on the basis of the OBJECT_ID) to appear.  The background fbda process seems to run (wakeup) every 5minutes although it may wake up more frequently if there is more activity in the database.

SQL> select trunc(date_inserted), count(*)
2 from test_fbda
3 group by trunc(date_inserted)
4 /

TRUNC(DAT COUNT(*)
--------- ----------
02-APR-16 1000

SQL> select trunc(date_inserted), count(*)
2 from sys_fba_hist_93250
3 group by trunc(date_inserted)
4 /

no rows selected

SQL> update test_fbda
2 set data_column = data_column
3 where rownum < 451;

450 rows updated.

SQL> commit;

Commit complete.

SQL> select trunc(date_inserted), count(*)
2 from sys_fba_hist_93250
3 group by trunc(date_inserted)
4 /

no rows selected

SQL>
SQL> !sleep 180

SQL> select trunc(date_inserted), count(*)
2 from sys_fba_hist_93250
3 group by trunc(date_inserted)
4 /

TRUNC(DAT COUNT(*)
--------- ----------
02-APR-16 450

SQL>
SQL> select scn_to_timestamp(startscn), scn_to_timestamp(endscn), date_inserted, count(*)
2 from sys_fba_hist_93250
3 group by scn_to_timestamp(startscn), scn_to_timestamp(endscn), date_inserted
4 order by 1;

SCN_TO_TIMESTAMP(STARTSCN)
---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ENDSCN)
---------------------------------------------------------------------------
DATE_INSE COUNT(*)
--------- ----------
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM
02-APR-16 450


SQL>


Notice that not all 1000 rows got copied to the FBDA.  Only the 450 rows that I updated were copied in.  They are tracked by SCN-Timestamp.  (The "DATE_INSERTED" column is my own date column, Oracle wouldn't be using that column to track DML dates for rows as the values in that column are controlled by me -- the application or developer, not Oracle).

Note :  The History Table is not supposed to be directly queried in the manner I have shown here.

Tomorrow :  More Rows, and some DDLs as well.

Post 2 here.  It covers some of the architectural components and support for ADD / DROP column DDL.

Post 3 here.  It shows support for TRUNCATEs.

Post 4 here.  On Partitions and Indexes.

Post 5 here.  On (Auto)Purging.

Post 6 here.  On Bug Notes

Post 7 here.  Maintainig Partitioned Source Table
.
.
.


Categories: DBA Blogs

Now an OCP 12c

Sun, 2016-03-27 09:59
I have upgraded my Oracle Database Certified Professional status from 11g to 12c, having passed the 1Z0-060 Exam last Monday.

This is the list of material I used :

1.  Documentation :  Oracle 12c Database Documentation.  Not just the New Features Guide but a large number of pages spread throughout the documentation.  I went through *selected* chapters / pages of the Administrator's Guide, Reference Guide, PLSQL Package Guide, Developers Guide and Backup and Recovery Guide

2.  Books :
a.  Oracle Database 12c New Features  by Robert G. Freeman  (Oracle Press)
b.  OCP Upgrade to Oracle Database 12c Exam Guide  by Sam R. Alapati  (Oracle Press)
c.  Study Guide for 1Z0-060 Upgrade to Oracle Database 12c  by Matthew Morris (Self-Published as Oracle Certification Prep)

3.  FlashCards
a.  FlashCards with Test Software for 1Z0-060 at https://www.selftestsoftware.com
b.  FlashCards OCPFlash1Z0-060 on Google PlayStore  by Matthew Morris

4.  Practice Tests
a.  Practice Tests and Test Software for 1Z0-060 at https://www.selftestsoftware.com
b.  Oracle Certification Prep Practice Test for 1Z0-060  at http://www.oraclecertificationprep.com

5.  Links to other Resources :  http://www.oraclecertificationprep.com and  www.oracle-base.com


Note : This does NOT mean that I didn't have practice environments.  The books alone aren't sufficient.   I created three practice environments :
a.  Fresh Install of Oracle Linux, RDBMS 12.1.0.1 and creation NonCDB database on FileSystem
b. Donwloaded Oracle Virtual Box PreBuilt VM with 12.1.0.2 and MultiTenant Database
c. Fresh Install of Oracle Linux, creation of disks for ASM, install of Grid Infrastructure and RDBMS 12.1.0.2 and creation of NonCDB database on ASM
.
.
.

Categories: DBA Blogs

Compression -- 5 : OLTP Compression

Sat, 2016-03-26 09:03
Taking the test case from my first Blog Post on compression,  is there any way to support OLTP ?
As demonstrated in the first Blog Post and here, once the table undergoes DML, its size changes.

PDB1@ORCL> select segment_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name in ('SOURCE_DATA','COMPRESSED_1')
4 /

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1 TABLE 12
SOURCE_DATA TABLE 49

PDB1@ORCL>
PDB1@ORCL> update compressed_1
2 set owner=owner;

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
2 from user_segments
3 where segment_name = 'COMPRESSED_1';

BYTES/1048576
-------------
22

PDB1@ORCL>
PDB1@ORCL> update compressed_1
2 set owner=substr(owner||owner,1,30);

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
2 from user_segments
3 where segment_name = 'COMPRESSED_1';

BYTES/1048576
-------------
58

PDB1@ORCL>

So, I setup another table that supports OLTP compression.  This requires the Advanced Compression licence.  Make sure that you have purchased the licence !

PDB1@ORCL> create table compressed_2 tablespace hemant COMPRESS FOR OLTP
2 as select * from source_data where 1=2;

Table created.

PDB1@ORCL> insert /*+ APPEND */ into compressed_2
2 select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
2 from user_segments
3 where segment_name = 'COMPRESSED_2';

BYTES/1048576
-------------
13

PDB1@ORCL> update compressed_2
2 set owner=owner;

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
2 from user_segments
3 where segment_name = 'COMPRESSED_2';

BYTES/1048576
-------------
13

PDB1@ORCL> update compressed_2
2 set owner=substr(owner||owner,1,30);

364496 rows updated.

PDB1@ORCL> select bytes/1048576
2 from user_segments
3 where segment_name = 'COMPRESSED_2';

BYTES/1048576
-------------
29

PDB1@ORCL>
PDB1@ORCL> select table_name, pct_free, compression, compress_for
2 from user_tables
3 where table_name like 'COMPRESS%'
4 order by 1;

TABLE_NAME PCT_FREE COMPRESS
------------------------------ ---------- --------
COMPRESS_FOR
------------------------------
COMPRESSED_1 0 ENABLED
BASIC

COMPRESSED_2 10 ENABLED
ADVANCED


PDB1@ORCL>


Note the initial size of COMPRESSED_2 is slightly large because it starts with PCT_FREE=10.
The UPDATE does seem to take longer to run.  COMPRESS FOR OLTP preserves PCT_FREE at 10. But it does handle UPDATEs better than BASIC Compression. However, if you have no subsequent UPDATEs to the data, BASIC Compression (which does not need an additional licence when running Enterprise Edition) will suffice.

I urge you to *TEST* COMPRESS FOR OLTP with the different types of UPDATE operations that you actually have in your live database before you choose to implement it.

In 12c, COMPRESS FOR OLTP is called ROW STORE COMPRESS ADVANCED.  I presented the 11g style COMPRESS FOR OLTP syntax so that you could use it in 11g.
.
.
.


Categories: DBA Blogs

Compression -- 4 : RMAN (BASIC) Compression

Sat, 2016-03-19 06:34
BASIC Compression in RMAN is free with the Enterprise Edition. Advanced Compression with a specified algorithm requires the Advanced Compression Option Licence.

RMAN> backup as compressed backupset database root;

Starting backup at 19-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9fxmvhl3_.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9fxn0vgg_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9fxn0t8s_.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAR-16
channel ORA_DISK_1: finished piece 1 at 19-MAR-16
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T081217_cgtjk2ms_.bkp tag=TAG20160319T081217 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:16
Finished backup at 19-MAR-16

Starting Control File and SPFILE Autobackup at 19-MAR-16
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_03_19/o1_mf_s_906884075_cgtjofh1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-MAR-16

RMAN> list backup;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 385.78M DISK 00:02:09 19-MAR-16
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20160319T081217
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T081217_cgtjk2ms_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 3034354 19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf
3 Full 3034354 19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9fxmvhl3_.dbf
4 Full 3034354 19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9fxn0vgg_.dbf
6 Full 3034354 19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9fxn0t8s_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 17.20M DISK 00:00:03 19-MAR-16
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20160319T081435
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_03_19/o1_mf_s_906884075_cgtjofh1_.bkp
SPFILE Included: Modification time: 19-MAR-16
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 3034519 Ckp time: 19-MAR-16

RMAN>
CDB$ROOT@ORCL> l
1 select status, command_id, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(end_time,'DD-MON HH24:MI') End_At,
2 trunc(input_bytes/1048576) Input_MB, trunc(output_bytes/1048576) Output_MB, input_type
3 from v$rman_backup_job_details
4* order by start_time
CDB$ROOT@ORCL>
CDB$ROOT@ORCL> /

STATUS COMMAND_ID START_AT END_AT INPUT_MB OUTPUT_MB
------------------- ------------------------- ----------------- ------------- ---------- ----------
INPUT_TYPE
-------------
COMPLETED 2016-03-19T08:12:02 19-MAR 08:12 19-MAR 08:14 1807 402
DB FULL


CDB$ROOT@ORCL>

Compare the INPUT_MB and OUTPUT_MB to see the compression achieved.

Default compression can also be configured with

RMAN> show compression algorithm;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

RMAN>
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored

RMAN>
RMAN> backup datafile 1;

Starting backup at 19-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAR-16
channel ORA_DISK_1: finished piece 1 at 19-MAR-16
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T082704_cgtkdrdh_.bkp tag=TAG20160319T082704 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 19-MAR-16

Starting Control File and SPFILE Autobackup at 19-MAR-16
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_03_19/o1_mf_s_906884879_cgtkgk5d_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-MAR-16

RMAN> list backup of datafile 1;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 385.78M DISK 00:02:09 19-MAR-16
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20160319T081217
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T081217_cgtjk2ms_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 3034354 19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 207.82M DISK 00:00:55 19-MAR-16
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20160319T082704
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T082704_cgtkdrdh_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 3035302 19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf

RMAN>
CDB$ROOT@ORCL> l
1 select status, command_id, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(end_time,'DD-MON HH24:MI') End_At,
2 trunc(input_bytes/1048576) Input_MB, trunc(output_bytes/1048576) Output_MB, input_type
3 from v$rman_backup_job_details
4* order by start_time
CDB$ROOT@ORCL> /

STATUS COMMAND_ID START_AT END_AT INPUT_MB OUTPUT_MB
------------------- ------------------------- ----------------- ----------------- --------- ---------
INPUT_TYPE
-------------
COMPLETED 2016-03-19T08:12:02 19-MAR 08:12 19-MAR 08:14 1807 402
DB FULL

COMPLETED 2016-03-19T08:18:29 19-MAR 08:27 19-MAR 08:28 803 225
DATAFILE FULL


CDB$ROOT@ORCL>
CDB$ROOT@ORCL> !du -sh /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T082704_cgtkdrdh_.bkp
208M /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T082704_cgtkdrdh_.bkp

CDB$ROOT@ORCL>


Note the slight discrepancy in reported backuppiece size.  The LIST BACKUP shows it as 207.82MB,  the query on v$rman_backup_job_details dividing the output_bytes by 1048576 shows the size as 225MB (truncating to units) while the OS level "du -sh" command shows it as 208MB.
.
.
.

Categories: DBA Blogs

Compression -- 3 : Index (Key) Compression

Sun, 2016-03-13 03:34
Unlike Table Compression that uses deduplication of column values, Index Compression is based on the keys.  Key Compression is also called Prefix Compression.

This relies on repeated leading key values being eliminated.  Thus, for example, if the leading column of the composite index has frequently repeated values and because an Index is always an organised (sorted) structure, we find the repeated values appearing as if "sequentially".  Key Compression can eliminate the repeated values.

Thus, it becomes obvious that Index Key Compression is usable for
a.  A Composite Index of 2 or more columns
b.  Repeated appearances of values in the *leading* key columns
c.  Compression defined for a maximum of n-1 columns  (where n is the number of columns in the index).  That is, the last column cannot be compressed.
Note that a Non-Unique Index automatically has the ROWID appended to it, so Key Compression can be applied to all the columns defined.

Let's look at a few examples.

Starting with creating a fairly large table (that is a multiplied copy of DBA_OBJECTS)

PDB1@ORCL> create table target_data as select * from source_data where 1=2;

Table created.

PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL>
PDB1@ORCL> desc target_data
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)

PDB1@ORCL>


What composite index is a good candidate for Key Compression ?
*Not* an Index that begins with OBJECT_ID as that is a Unique value.

Let's compare two indexes (compressed and non-compressed) on (OWNER, OBJECT_TYPE, OBJECT_NAME).

PDB1@ORCL> create index target_data_ndx_1_comp on
2 target_data (owner, object_type, object_name) compress 2;

Index created.

PDB1@ORCL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_1_COMP');

PL/SQL procedure successfully completed.

PDB1@ORCL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_1_COMP'
4 /

LEAF_BLOCKS
-----------
5629

PDB1@ORCL>


PDB1@ORCL> drop index target_data_ndx_1_comp
2 /

Index dropped.

PDB1@ORCL> create index target_data_ndx_2_nocomp on
2 target_data (owner, object_type, object_name) ;

Index created.

PDB1@ORCL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_2_NOCOMP');

PL/SQL procedure successfully completed.

PDB1@ORCL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_2_NOCOMP'
4 /

LEAF_BLOCKS
-----------
7608

PDB1@ORCL>


Note the "compress 2" specification for the first index.  That is an instruction to compress based on the leading 2 columns.
Thus, the compressed index is 5,629 blocks but the normal, non-compressed index is 7,608 blocks.  We make a gain of 26% in the index size.

Why did I choose OWNER, OBJECT_TYPE as the leading columns ?  Because I expected a high level of repetition on these column names.


Note : I have not explored Advanced Index Compression available in 12.1.0.2
Advanced Index Compression tested in 12.1.0.2
.
.

Categories: DBA Blogs

COMPRESSION -- 2 : Compressed Table Partitions

Sun, 2016-03-06 09:38
A Partitioned Table can choose to have a mix of COMPRESS and NOCOMPRESS Partitions.

As in this case where the first 3 partitions are defined as COMPRESS and the last as NOCOMPRESS :  :

[oracle@localhost Hemant]$ sqlplus hemant/hemant@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 6 23:20:19 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Sun Mar 06 2016 23:19:11 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @create_SALESHIST
SQL> spool create_SALESHIST
SQL>
SQL> drop table SALESHIST;

Table dropped.

SQL>
SQL> alter session set "_partition_large_extents"=FALSE;

Session altered.

SQL>
SQL> create table SALESHIST
2 (
3 PROD_ID NUMBER NOT NULL ,
4 CUST_ID NUMBER NOT NULL ,
5 TIME_ID DATE NOT NULL ,
6 CHANNEL_ID NUMBER NOT NULL ,
7 PROMO_ID NUMBER NOT NULL ,
8 QUANTITY_SOLD NUMBER(10,2) NOT NULL ,
9 AMOUNT_SOLD NUMBER(10,2) NOT NULL
10 )
11 NOCOMPRESS LOGGING
12 TABLESPACE USERS
13 PARTITION BY RANGE (TIME_ID)
14 (
15 PARTITION SALES_1998
16 VALUES LESS THAN (TO_DATE('1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
17 'NLS_CALENDAR=GREGORIAN'))
18 SEGMENT CREATION IMMEDIATE
19 COMPRESS BASIC NOLOGGING
20 TABLESPACE SALES_1998 ,
21 PARTITION SALES_1999
22 VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
23 'NLS_CALENDAR=GREGORIAN'))
24 SEGMENT CREATION IMMEDIATE
25 COMPRESS BASIC NOLOGGING
26 TABLESPACE SALES_1999 ,
27 PARTITION SALES_2000
28 VALUES LESS THAN (TO_DATE('2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
29 'NLS_CALENDAR=GREGORIAN'))
30 SEGMENT CREATION IMMEDIATE
31 COMPRESS BASIC NOLOGGING
32 TABLESPACE SALES_2000 ,
33 PARTITION SALES_2001
34 VALUES LESS THAN (TO_DATE('2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
35 'NLS_CALENDAR=GREGORIAN'))
36 SEGMENT CREATION IMMEDIATE
37 NOCOMPRESS NOLOGGING
38 TABLESPACE SALES_2001 )
39 /

Table created.

SQL>
SQL> spool off
SQL>
SQL> col partition_name format a30
SQL> select partition_name, compression, compress_for
2 from user_tab_partitions
3 where table_name = 'SALESHIST'
4 order by partition_position;

PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
SALES_1998 ENABLED BASIC
SALES_1999 ENABLED BASIC
SALES_2000 ENABLED BASIC
SALES_2001 DISABLED

SQL>
SQL> select compression, compress_for
2 from user_tables
3 where table_name = 'SALESHIST';

COMPRESS COMPRESS_FOR
-------- ------------------------------


SQL>


The Compression attributes actually apply to the Segments so the Partition Segments have the definition but the Table, being segmentless, does not show the definition.

Note that I am still demonstrating BASIC Compression. So the compression is applied only on Direct Path INSERT.  As in :

SQL> insert /*+ APPEND */ into saleshist select * from sh.sales;

918843 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select partition_name, tablespace_name, bytes/1024
2 from user_segments
3 where segment_name = 'SALESHIST'
4 and segment_type = 'TABLE PARTITION'
5 order by 1;

PARTITION_NAME TABLESPACE_NAME BYTES/1024
------------------------------ ------------------------------ ----------
SALES_1998 SALES_1998 3072
SALES_1999 SALES_1999 4096
SALES_2000 SALES_2000 4096
SALES_2001 SALES_2001 11264

SQL>


This demonstrates that it is possible to
a) Have some Partitions defined as COMPRESSed and others as NOCOMPRESSed
b) Create different Partitions in different Tablespaces

Before the advent of (and still without using) the 12c ILM features, this was and is a method to manage historical data with compression and moving or placing data (i.e. the respective tablespace datafile(s)) in low-cost storage as desired.

Note : For the purposes of this demo, I used the parameter "_partition_large_extents"=FALSE.  Oracle's default Partition size since 11.2.0.2 has been 8MB for Table Partitions and that would have been excessively large for this demo.  If you are using Hybrid Columnar Compression and/or Exadata, Oracle advises against this.
.
.
.

Categories: DBA Blogs

Compression -- 1b : (more on) BASIC Table Compression

Sun, 2016-02-28 04:38
In the previous blog, I demonstrated creating an empty table with BASIC Compression and then populating it.

What if you have a pre-existing table with data that you want to compress ?

Here I start with a table that already has rows but doesn't have compression configured.

PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name = 'REGULAR_1';

TABLESPACE_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
HEMANT TABLE 49

PDB1@ORCL> select pct_free, blocks, num_rows, blocks*8192/1048576
2 from user_tables
3 where table_name = 'REGULAR_1';

PCT_FREE BLOCKS NUM_ROWS BLOCKS*8192/1048576
---------- ---------- ---------- -------------------
10 6224 364496 48.625

PDB1@ORCL>


I then proceed to rebuild it as a COMPRESSed table.

PDB1@ORCL> alter table regular_1 move compress;

Table altered.

PDB1@ORCL> exec dbms_stats.gather_table_stats('','REGULAR_1');

PL/SQL procedure successfully completed.

PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name = 'REGULAR_1';

TABLESPACE_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
HEMANT TABLE 12

PDB1@ORCL> select pct_free, blocks, num_rows, blocks*8192/1048576
2 from user_tables
3 where table_name = 'REGULAR_1';

PCT_FREE BLOCKS NUM_ROWS BLOCKS*8192/1048576
---------- ---------- ---------- -------------------
0 1448 364496 11.3125

PDB1@ORCL>


Note how not only did the table size shrink to less than 12MB, the PCT_FREE also got  reset to 0 !
.
.
.

Categories: DBA Blogs

Compression -- 1 : BASIC Table Compression

Sun, 2016-02-21 08:30
Now, starting a new series of blog posts on Compression.

Beginning with  BASIC Table Compression.

Basic Table Compression was introduced in 9i Release 2 (9.2.0).  It is free with the Enterprise Edition.  Basic Compression works only with Direct Path (Bulk Load) INSERTs.  It does not apply to "normal" INSERT and UPDATE operations.
Basic Compression is actually "de-duplication".  It identifies values that are repeated within the same database block and replaces them with tokens mapped to the list of values.   Note the section that I have underlined.  De-duplication does not span database blocks.

Here is a first demo of Basic Compression using INSERT /*+ APPEND */  (for Direct Path Insert).

I start with a table that is a multiplied copy of DBA_OBJECTS, named as SOURCE_DATA.

PDB1@ORCL> show user
USER is "HEMANT"
PDB1@ORCL> select count(*) from source_data;

COUNT(*)
----------
364496

PDB1@ORCL>
PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name = 'SOURCE_DATA';

TABLESPACE_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
HEMANT TABLE 49

PDB1@ORCL>


I then create table to hold compressed data.  Note (let me repeat again : Basic Compression works only with DIRECT PATH INSERT).

PDB1@ORCL> create table compressed_1 tablespace hemant compress as select * from source_data where 1=2;

Table created.

PDB1@ORCL> insert /*+ APPEND */ into compressed_1
2 select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL>
PDB1@ORCL> col segment_name format a30
PDB1@ORCL> select segment_name, segment_type, bytes/1048576
2 from user_Segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1 TABLE 12
SOURCE_DATA TABLE 49

PDB1@ORCL>


So, a 49MB table is compressed down to 12MB.  What if I UPDATE these rows ?

PDB1@ORCL> update compressed_1
2 set owner = owner;

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select segment_name, segment_type, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1 TABLE 22
SOURCE_DATA TABLE 49

PDB1@ORCL>


Updating only a single column without changing the length of the data in that column has increased the size of the table.

Let me continue the UPDATE experiment further, without increasing the length of data in any columns.

PDB1@ORCL> update compressed_1
2 set owner = owner, object_name = object_name, subobject_name = subobject_name, object_id = object_id,
3 data_object_id = data_object_id, object_type = object_type, created = created
4 /

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select segment_name, segment_type, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1 TABLE 22
SOURCE_DATA TABLE 49

PDB1@ORCL>


No further degradation ? (unless my UPDATE expands the size of data in one or more column(s)).

Note a difference between the two tables :

PDB1@ORCL> select table_name, compression, compress_for, pct_free
2 from user_tables;

TABLE_NAME COMPRESS COMPRESS_FOR PCT_FREE
------------------------------ -------- ------------------------------ ----------
COMPRESSED_1 ENABLED BASIC 0
SOURCE_DATA DISABLED 10

PDB1@ORCL>


The Compressed table is created with PCT_FREE=10.  PCT_FREE=0  (Note that this can also have an impact when you issue UPDATEs subsequently, UPDATEs that increase the size of data in one or more columns).

UPDATE : In 12c the syntax for BASIC Compression is "ROW STORE COMPRESS BASIC". Simply specifying "COMPRESS" or "COMPRESS BASIC" is supported for backward compatibility.

.
.
.

Categories: DBA Blogs

RMAN : Unused Block Compression and Null Block Compression

Fri, 2016-02-19 08:56
To quote the 11.2 documentation,  "Although it is referred to as block compression, it might be helpful to think of block compression as block skipping".  11.2 differentiates between Unused Block Compression and Null Block Compression.
UPDATE 28-Feb-16 : Please note the list of 5 pre-requisites for Unused Block Compression.

In response to a community thread, here is a quick demo to see both Null Block Compression and Unused Block Compression.

I start with a 100MB tablespace.

[oracle@ora11204 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 19 22:37:47 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace hemant;

Tablespace created.

SQL> alter user hemant quota unlimited on hemant;

User altered.

SQL> select file_name, bytes/1048576 from dba_data_files
2 where tablespace_name = 'HEMANT';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
100


SQL>


First I take a backup of this empty tablespace.

RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp tag=TAG20160219T224255 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904257776_cdgbhkcx_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>

This 100MB datafile backed up to 1.03MB is Null Blocm compression.

I next load data into it and  then take a backup.

[oracle@ora11204 Desktop]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 19 22:45:12 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table hkc_all_obj tablespace hemant
2 as select * from dba_objects;

Table created.

SQL> insert into hkc_all_obj select * from hkc_all_obj;

28105 rows created.

SQL> /

56210 rows created.

SQL> /

112420 rows created.

SQL> /

224840 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
HKC_ALL_OBJ
49


SQL>
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp tag=TAG20160219T224721 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258049_cdgbr179_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 49.67M DISK 00:00:04 19-FEB-16
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224721
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807559 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>


So, I now have 49.67MB backup of the datafile in the tablespace. What happens if I drop all objects (in this case only the 1 table) in that tablespace, with RECYLEBIN ?

SQL> show parameter recycle

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on
SQL> drop table hkc_all_obj;

Table dropped.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
2 from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
TS_NAME SPACE SPACE*8192/1048576
------------------------------ ---------- ------------------
BIN$LCGWc71HDSzgUwEAAH/27g==$0 HKC_ALL_OBJ
HEMANT 6272 49


SQL>
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp tag=TAG20160219T225023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258224_cdgbxjg4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 49.67M DISK 00:00:04 19-FEB-16
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224721
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807559 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 49.67M DISK 00:00:00 19-FEB-16
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20160219T225023
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807909 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>


The latest backup (BackupSet 29) is still 49.67MB although, technically, the tablespace has no Tables/Indexes.  It does have something in the RECYCLEBIN.

Let me purge the RECYCLEBIN and then run a fresh backup.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
2 from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
TS_NAME SPACE SPACE*8192/1048576
------------------------------ ---------- ------------------
BIN$LCGWc71HDSzgUwEAAH/27g==$0 HKC_ALL_OBJ
HEMANT 6272 49


SQL> purge hkc_all_obj;
purge hkc_all_obj
*
ERROR at line 1:
ORA-38302: invalid PURGE option


SQL> purge table hkc_all_obj;

Table purged.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
2 from user_recyclebin;

no rows selected

SQL>
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225323_cdgc33tc_.bkp tag=TAG20160219T225323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258404_cdgc351n_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 1.03M DISK 00:00:01 19-FEB-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224255
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4806951 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 49.67M DISK 00:00:04 19-FEB-16
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20160219T224721
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807559 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 49.67M DISK 00:00:00 19-FEB-16
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20160219T225023
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4807909 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31 Full 1.03M DISK 00:00:00 19-FEB-16
BP Key: 31 Status: AVAILABLE Compressed: NO Tag: TAG20160219T225323
Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225323_cdgc33tc_.bkp
List of Datafiles in backup set 31
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4808966 19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN>
[oracle@ora11204 Desktop]$ ls -l /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
-rw-rw----. 1 oracle oracle 104865792 Feb 19 22:53 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
[oracle@ora11204 Desktop]$


Aha ! BackupSet 31 is now 1.03MB only.  So, Unused Block Compression has kicked in !  The blocks that belonged to that table are yet formatted and in prior versions that did not have Unused Block Compression would still be backed up even though the table has been dropped.
(Note : I did not shrink the datafile, it is still 100MB).


UPDATE 28-Feb-16 :  Testing for TRUNCATE

SQL> select segment_name, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME BYTES/1048576
--------------------------------------------------------------------------------- -------------
HKC_ALL_OBJ 49

SQL>
RMAN> backup tablespace hemant;

Starting backup at 28-FEB-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf
channel ORA_DISK_1: starting piece 1 at 28-FEB-16
channel ORA_DISK_1: finished piece 1 at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp tag=TAG20160228T190655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 28-FEB-16

Starting Control File and SPFILE Autobackup at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2016_02_28/o1_mf_s_905022445_cf5o7lyq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-16

RMAN> list backup of tablespace hemant completed after "trunc(sysdate)";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 49.55M DISK 00:00:22 28-FEB-16
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20160228T190655
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4776386 28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

RMAN>
SQL> show parameter recyclebin;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> truncate table hkc_all_obj;

Table truncated.

SQL> select segment_name, bytes/1048576
2 from user_segments
3 where tablespace_name = 'HEMANT';

SEGMENT_NAME BYTES/1048576
--------------------------------------------------------------------------------- -------------
HKC_ALL_OBJ .0625

SQL>
RMAN> backup tablespace hemant;

Starting backup at 28-FEB-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf
channel ORA_DISK_1: starting piece 1 at 28-FEB-16
channel ORA_DISK_1: finished piece 1 at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190946_cf5octqc_.bkp tag=TAG20160228T190946 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 28-FEB-16

Starting Control File and SPFILE Autobackup at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2016_02_28/o1_mf_s_905022589_cf5oczwq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-16

RMAN> list backup of tablespace hemant completed after "trunc(sysdate)";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 49.55M DISK 00:00:22 28-FEB-16
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20160228T190655
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4776386 28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.53M DISK 00:00:01 28-FEB-16
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20160228T190946
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190946_cf5octqc_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 4776656 28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

RMAN>


The TRUNCATE did reduce the size of the table and the backup !
.
.
.

Categories: DBA Blogs

Trace Files -- 12 : Tracing a Particular Process

Sun, 2016-02-14 02:59
Unlike tracing for particular SQL statements, you can also trace by PID  (Oracle PID) or Server Process ID (SPID).

SQL> select s.sid, p.pid
2 from v$session s join v$process p
3 on (s.paddr=p.addr)
4 and s.username = 'HEMANT';

SID PID
---------- ----------
19 22

SQL>
SQL> alter system set events 'sql_trace {process: orapid=22}';

System altered.

SQL>
SQL> select s.sid, p.pid, p.spid
2 from v$session s join v$process p
3 on (s.paddr=p.addr)
4 and s.username = 'HR';

SID PID SPID
---------- ---------- ------------------------
14 26 3207

SQL> alter system set events 'sql_trace {process:3207}';

System altered.

SQL>


Tracing for the processes is disabled with :

SQL> alter system set events 'sql_trace {process: orapid=22} off';

System altered.

SQL> alter system set events 'sql_trace {process:3207} off';

System altered.

SQL>


Tracing seems to get disabled after the next SQL, not immediately.

Remember : This is SQL Tracing, not Optimizer Tracing.
.
.
.

Categories: DBA Blogs