Thursday, February 18, 2021

Index Monitoring in Oracle Database

Index Monitoring

In Oracle 12.2 this functionality has been replaced by Index Usage Tracking.

The database maintains all indexes defined against a table regardless of their usage. Index maintenance can cause significant amounts of CPU and I/O usage, which can be detrimental to performance in a write-intensive system. With this in mind, it makes sense to identify and remove any indexes that are not being used as they are a pointless drain on resources. Index monitoring allows unused indexes to be identified accurately, removing the risks associated with dropping useful indexes.

It is important to make sure that index monitoring is performed over a representative time period. If you only monitor indexes during specific time frames you may incorrectly highlight indexes as being unused. The safest method is to use a monitoring period which spans the whole lifecycle of your application, including any OLTP and batch operations. In a Decision Support System (DSS) this may involve a period of weeks or months, while OLTP systems typically have shorter cycles.

ALTER INDEX

Index monitoring is started and stopped using the ALTER INDEX syntax shown below.

ALTER INDEX my_index_i MONITORING USAGE;
ALTER INDEX my_index_i NOMONITORING USAGE;

If you are using a version prior to Oracle 12.1, information about the index usage can be displayed using the V$OBJECT_USAGE view.

SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   v$object_usage
WHERE  index_name = 'MY_INDEX_I'
ORDER BY index_name;

The V$OBJECT_USAGE view does not contain an OWNER column so you must to log on as the object owner to see the usage data.

From Oracle 12.1 onward the V$OBJECT_USAGE view has been deprecated in favour of the {DBA|USER]}_OBJECT_USAGE views. The structure is the same, but the DBA_OBJECT_USAGE view includes an OWNER column.

SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   dba_object_usage
WHERE  index_name = 'MY_INDEX_I'
AND    owner = 'TEST'
ORDER BY index_name;

Useful Scripts

The following scripts may come in handy when setting up and reviewing index monitoring:

Foreign Key Indexes

To quote the Oracle Database Concepts manual:

"You should almost always index foreign keys. The only exception is when the matching unique or primary key is never updated or deleted."

When a foreign key is unindexed, DML on the parent primary key results in a share row exclusive table lock (or share-subexclusive table lock, SSX) on the child table, preventing DML from other transactions against the child table. If the DML affects several rows in the parent table, the lock on the child table is obtained and released immediately for each row in turn. Despite the speed of the lock-release process, this can cause significant amounts of contention on the child table during periods of heavy update/delete activity on the parent table.

When a foreign key is indexed, DML on the parent primary key results in a row share table lock (or subshare table lock, SS) on the child table. This type of lock prevents other transactions from issuing whole table locks on the child table, but does not block DML on either the parent or the child table. Only the rows relating to the parent primary key are locked in the child table.

This issue is mitigated somewhat in later releases, but it is still worth considering, and above all testing.

Limitations

Index monitoring is only a guideline and does not list all indexes used by the system. Prior to Oracle 12.2, index monitoring is based on the indexes selected for the execution plan during the parsing phase of optimisation. Once the statement runs, other indexes for recursive SQL, like constraint checking, will also be referenced, but may not be included in the object usage views. Use index monitoring as a guide line, not an absolute.

In Oracle 12.2 the mechanism behind index monitoring has been revamped, changing it from parse time to runtime, so the chances of an index that is used not making it into the object usage views is a lot lower.

As mentioned previously, the monitoring is only as good as the sample period you observe. Imagine you are checking usage for 11 months, decide to drop some indexes, then the following month you run some yearly reports which rely on those indexes. Once again, use index monitoring as a guide line, not an absolute.

https://blog.zeddba.com/2018/07/17/index-monitoring-in-oracle-database/

Monday, February 8, 2021

How to Use In-Database Archiving

Why In-Database Archiving

The simple goal of in-database archiving is to assign values to the data based on business rules of whether the data is current or marked as non-active. As the value of the data lowers over time, the related data can be moved to less costly storage. The challenges are:

Databases grow; rarely do they get smaller
Data growth rates are accelerating
The old data may need to be instantly available in the database at a future time

In-database archiving is a possible solution.

Setting Up Row Archival

Use in-database archiving to distinguish active rows from non-active within the same table. It is also possible to compress the non-active rows as they are moved. Compression requires the Advanced Compression option.

To enable row archival, add the hidden column, ora_archive_state:

CREATE TABLE emp
(empno NUMBER(7),
fullname VARCHAR2(40),
job VARCHAR2(9),
mgr NUMBER(7))
ROW ARCHIVAL; — adds the ora_archive_state column

An existing table can be altered for In-Database Archiving:

ALTER TABLE emp ROW ARCHIVAL;

The row archival attribute indicates whether a row is active or non-active. By default, when a row is inserted, it is set as active, and the ora_archive_state value is ‘0’. After a period of time, the row may be less frequently accessed and is rarely updated, but still considered as active. It needs to be retained for records retention and/or compliance purposes. It is, therefore, considered to be in a non-active state. The value ‘1’ – or any value other than ‘0’ – in the ora_archive_state column would reflect the non-active state. Update ora_archive_state to ‘1’ for row archiving. ACTIVE is the default value.

Using Row Archival

View ora_archive_state column:

When it is determined that the rows should not be active, set the rows to the archive state. This could be done with a batch job scheduled to run during the maintenance window.

UPDATE emp
SET ora_archive_state = 1
WHERE empno < 100;

Set rows back to active state if needed for active use:

UPDATE emp
SET ora_archive_state = 0;

Setting Session Visibility

To view active rows only:

View all rows:

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

To return to active rows only:

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

Disabling Row Archival

When row archival is disabled, the ora_archive_state column is automatically removed. To disable row archival:

ALTER TABLE emp NO ROW ARCHIVAL;

In my next blog, I will be discussing Temporal History.

ora_archive_state Tips

Starting in Oracle 12c, Oracle provides a new feature that allow you to "logically delete" a row in a table without physically removing the row. This effectively makes deleted rows "invisible" to all SQL and DML, but they can be revealed at any time, providing a sort of "instant" rollback method.

To use ora_archive_state as an alternative to deleting rows, you need the following settings and parameters:

1. Create the table with the row archival clause

create table mytab (col1 number, col2 char(200)) row archival;

2. Now that the table is marked as row archival, you have two methods for removing rows, a permanent solution with the standard delete DML, plus the new syntax where you set ora_archive_state to a non-zero value:

update mytab set ora_archive_state=2 where col2='FRED';

3. To make "invisible rows" visible again, you simply set the rows ora_archive_state to zero:

update mytab set ora_archive_state=0 where col2='FRED';

To see rows that are marked as deleted you can select the pseudo-column ora_archive_state from a table, just as-if it were a real table column. This is a binary pseudo-column, and a "1" value indicates a logically deleted table row.

select id, col1, col2, ora_archive_state from mytab;

Removing row invisibility

Below we see a case where we set the row archival visibility parameter to "all" thereby allowing us to see all of the rows that have been logically deleted:

alter session set row archival visibility = all;

select col1, col2, ora_archive_state from mytab;

We can then turn-on row invisibility back on by changing row archival visibility = "active":

alter session set row archival visibility = all;

We also see two new data dictionary columns in dba_tab_cols, hidden_column (an indicator) and user_generated:

select
   table_name,
   column_name,
   hidden_column,
   user_generated
from
   dba_tab_cols
where
   table_name='MYTAB';