OraFAQ Forum: Performance Tuning » In-database archival

Home » RDBMS Server » Performance Tuning » In-database archival (Oracle 12C)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

In-database archival [message #658037]

Thu, 01 December 2016 03:35

srinivas.k2005
Messages: 402
Registered: August 2006

Senior Member

Hello,

I have come across the concept "in database archiving oracle 12c". Does it really helps to improve the performance of the queries? Please let me know the limitations.

I tested locally and it has not improved anything in the size the tables and nor it has helped to improve the performance of the queries.

Please advise.

Regards,
SRK

Report message to a moderator

Re: In-database archival [message #658038 is a reply to message #658037]

Thu, 01 December 2016 03:38

John Watson
Messages: 8804
Registered: January 2010
Location: Global Village

Senior Member

This is not a performance feature. It just adds a filter to all your queries, which (if you have not pre-pended the column to your indexes) may slow things down. For example:

orclz>
orclz> set autot on exp
orclz> select count(*) from emp;

  COUNT(*)
----------
        14


Execution Plan
----------------------------------------------------------
Plan hash value: 1006289799

------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_EMP |    14 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

orclz> alter table emp row archival;

Table altered.

orclz> select count(*) from emp;

  COUNT(*)
----------
        14


Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  2002 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |  2002 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |    14 | 28028 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMP"."ORA_ARCHIVE_STATE"='0')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

orclz>

Report message to a moderator

Re: In-database archival [message #658043 is a reply to message #658038]

Thu, 01 December 2016 05:11

srinivas.k2005
Messages: 402
Registered: August 2006

Senior Member

Thanks a lot John:)

Report message to a moderator

Previous Topic:	java.sql.SQLTimeoutException: ORA-01013
Next Topic:	Materlized View

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 23 10:38:14 CDT 2023