How to Invalid Partition Indexes

Indexes will become invalid for various reason. When indexes are invalid your query may not perform well and sometime it will lead into your operation fail during few DML. So it is DBA responsibility periodically check and rebuild during off business hours.

1. How to find the Invalid Indexes
Indexes can be from normal tables or partitioned/subpartitioned tables respectively we will have NONPARTITIONED INDEXES and PARTITIONED INDEXES/SUBPARTITIONED INDEXES

We have three different views to check for respective Indexes.
1.1 DBA_INDEXES
1.2 DBA_IND_PARTITIONS
1.3 DBA_IND_SUBPARTITIONS

You can use the STATUS of these views to find the invalid Indexes. Status with VALID, USABLE and N/A is good.

you have have to write simple SQL using the all three with proper subtitle and run it periodically.

——Snip of Invalid Index SQL – Start——–

set linesize 200
set pagesize 200
col owner format a20
col index_owner format a20
col index_name format a30
col parition_name format a30
col subpartition_name format a30
col status format a10

prompt —– Invalid NONPARTITIONED Indexes
select owner,index_name,status from dba_indexes where
status not in (‘VALID’,’USABLE’,’N/A’);
prompt —– Invalid PARTITIONED Indexes
select index_owner,index_name,parition_name,status from
dba_indexes where status not in (‘VALID’,’USABLE’,’N/A’);
prompt —– Invalid SUBPARTITIONED Indexes
select index_owner,index_name,parition_name,subpartition_name,status
from dba_indexes where status not in (‘VALID’,’USABLE’,’N/A’);

——Snip of Invalid Index SQL – End——–

2. How to fix the Invalid Indexes:

Once invalid indexes are identified we have to rebuild

We can use REBUILD to rebuild the indexes. We can use below keywords appropriately to rebuild indexes and safer manner

2.1 PARALLEL
2.2 NOLOGGING
2.3 ONLINE

Note: Please be careful while doing rebuilding indexes will make some current operation on hold/abort so better you check before doing rebuild operation and I would recommend you to do it during off business hours until unless you need it as emergency. If need to be done during business hours please make sure users aware that you are doing it.

NONPARTITIONED indexes rebuild:

ALTER INDEX <OWNER>.<INDEX_NAME> REBUILD NOLOGGING;

PARTITIONED indexes rebuild:

ALTER INDEX <OWNER>.<INDEX_NAME> REBUILD PARTITION <PARTITION_NAME> PARALLEL NOLOGGING;

How to Advance Compression 12c

1. What is Advance Compression:

As you already know in oracle compression started playing major role these days on saving cost and improving performance.

Oracle Advanced Compression option help to improve performance while reducing storage costs. It allows significantly reduce overall database storage footprint by enabling compression for all types of data –relational (table), unstructured (file), index, network and backup data.
Oracle Advanced Compression option are designed to improve performance for all components of your IT infrastructure, including memory, network bandwidth and storage.

2. Benefits of Advanced Row Compression

1. Reduce the storage space consumption by a factor of 2x to 4x by using Advanced Row Compression.
2. 2.5x faster on table scan compare with uncompressed data
3. Significant advantage is to read compressed blocks (data and indexes) directly, in memory, without uncompressing the blocks. This helps improve performance due to the reduction in I/O, and the reduction in system calls related to the I/O operations. Further, the buffer cache becomes more
efficient by storing more data without having to add memory.

3. Minimal Performance Overhead

1. A newly initialized block remains uncompressed until data in the block reaches an internally controlled threshold. When a transaction causes the data in the block to reach this threshold, all contents of the block are compressed. Subsequently, as more data is added to the block and the threshold is again reached, the entire block is recompressed to achieve the highest level of compression. This process repeats until Oracle determines that the block can no longer benefit from further compression. Only the transaction that performs the compression of the block will experience the slight compression overhead – the majority of DML transactions on compressed blocks will have the exact same performance as they would with uncompressed blocks.
2. Less than 3% cpu overhead on compressed data

4. Advanced Row Compression Implementation:

4.1 For new tables and partitions, enabling Advanced Row Compression is easy: simply CREATE the table or partition and specify “ROW
STORE COMPRESS ADVANCED”
Example:
CREATE TABLE emp (emp_id NUMBER, first_name VARCHAR2(128), last_name VARCHAR2(128)) ROW STORE COMPRESS ADVANCED;

4.2 For existing tables and partitions, there are a number of recommended approaches to enabling Advanced Row Compression:
4.2.1. ALTER TABLE … ROW STORE COMPRESS ADVANCED
Enable Advanced Row Compression for all future DML — however, the existing data in
the table will remain uncompressed.

4.2.2. Online Redefinition (DBMS_REDEFINITION)
Enable Advanced Row Compression for future DML and also compress existing data.
DBMS_REDEFINITION keeps the table online for both read/write activity during the migration. Run DBMS_REDEFINITION in parallel for best performance.
There is no interruption in the use of the indexes during, or after.
The only exception is when online redefinition is used for redefining a partition — any global indexes are invalidated and need to be rebuilt

4.2.3. ALTER TABLE … MOVE ROW STORE COMPRESS ADVANCED
Enable Advanced Row Compression for future DML and also compress existing data.
While the table is being moved it is online for read activity but has an exclusive (X)
lock – so all DML will be blocked until the move command completes. Run ALTER
TABLE…MOVE in parallel for best performance.
ALTER TABLE… MOVE will invalidate any indexes on the partition or table; those indexes will
need to be rebuilt after the ALTER TABLE… MOVE.

4.2.4. ALTER TABLE … MOVE TABLE/PARTITION/SUBPARTITION … ONLINE
Enable Advanced Row Compression for future DML and also compress existing data. ALTER TABLE … MOVE TABLE/PARTITION/SUBPARTITION … ONLINE allows
DML operations to continue to run uninterrupted on the table/partition/subpartition that is being
moved. Indexes are maintained during the move operation, so a manual index rebuild is not
required. New in 12.2 on Oracle Cloud, move tables online as well as partitions/subpartitions.

5. Advanced Index Compression
OLTP Databases will have more indexes to support the multitude of access paths for OLTP applications, this can cause indexes to contribute a greater share to overall storage of a database.

New in 12.2 on Oracle Cloud, the “HIGH” level of Advanced Index Compression provides significant space savings while also improving performance for queries that are executed using indexes. High compression offers the following advantages over LOW compression:
Gives higher compression ratios in most cases.
Employs more complex compression algorithms than advanced low compression.
Stores data in a compression unit, which is a special on-disk format.

The following is an example of enabling Advanced Index Compression (high level):

CREATE INDEX hr.emp_mndp_ix ON hr.employees(manager_id, department_id) COMPRESS ADVANCED HIGH;

Restriction on Index Compression:

Bitmap indexes can’t be compressed.
Partitioned indexes can’t be compressed. From 11g you can do compress partition index .

6. Here I tried to implement Index compression using Oracle Lab:

6.1 Create a Table with 6 columns type of varchar
CREATE TABLE test (
ENAME VARCHAR2(75),
EADD1 VARCHAR2(75),
EADD2 VARCHAR2(75),
EADD3 VARCHAR2(75),
EADD4 VARCHAR2(75),
CITY VARCHAR2(75)
);
Table created.

INSERT /*+ APPEND */ INTO test
SELECT RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(TO_CHAR(level),75, ‘X’)
FROM dual
CONNECT BY level <= 10000;

COMMIT;
10000 rows created.
col owner format a10
col segment_name format a15
col segment_type format a15
select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST TABLE 6291456

6.2 Created Non-Unique Index without compression and checked the size of an Index

SQL> CREATE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY);

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;

OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 6291456
MUDHALVAN TEST TABLE 6291456

SQL>

6.3 Created Non-Unique Index with compression and checked the size of an Index

SQL> CREATE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY) COMPRESS 5;

Index created.

SQL>EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;

OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 2097152
MUDHALVAN TEST TABLE 6291456

SQL>

Note : You can see the size of an index is reduced more than 2.5x

6.4 Created Unique Index without compression and checked the size of an Index

SQL> CREATE UNIQUE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY);

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;

OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 6291456
MUDHALVAN TEST TABLE 6291456

SQL>

6.5 Created Unique Index with compression and checked the size of an Index

SQL> CREATE UNIQUE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY) COMPRESS 5;

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;

OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 2097152
MUDHALVAN TEST TABLE 6291456

SQL>

6.6 Created Unique Index with compression advanced and checked the size of an Index

SQL> CREATE UNIQUE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY) COMPRESS ADVANCED LOW;

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;

OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 2097152
MUDHALVAN TEST TABLE 6291456

SQL>

How to Import Oracle 12C Dump into 11g Database

What is the scenario:
Exported the database using 12c expdp. Now we have to import into 11g Rel 2 database.

What Command you used:
impdp system/system directory=backup dumpfile=test12c.dmp logfile=test12.log schemas=test version=11.2

What Error message you get:
ORA-39142 incompatible version

Solution:
impdp system/system directory=backup dumpfile=test12c.dmp logfile=test12.log schemas=test version=latest

Note: You can also do expdp again with version=11.2 and do direct impdp on target database.

How to resmgr cpu quantum wait

Issue:
When the DB Performance issue is reported, After checking found the server load is very low, CPU/IO 90+ percent free.
But many sessiones waiting on event “resmgr:cpu quantum”. From AWR report we can see “resmgr:cpu quantum” is the TOP one consuming 100% DB time

What/Why resmgr:cpu quantum :

“resmgr:cpu quantum” means a session is waiting in a resource queue, wait until resource manager assign CPU pieces to it.

The wait event resmgr:cpu quantum is a normal wait event used by the Oracle Resource Manager to control CPU distribution. The resmgr:cpu quantum only occurs when the resource manager is enabled and the resource manage is “throttling” CPU consumption.

This is due to DEFAULT_MAINTENANCE_PLAN. From 11g onwards every weekday window has a pre-defined Resource Plan called DEFAULT_MAINTENANCE_PLAN, which will become active once the related window opens. In 10gR2, DEFAULT_MAINTENANCE_PLAN is associated with WEEKNIGHT_WINDOW and WEEKEND_WINDOW.

You can confirm this using the below SQL
SQL> select name,value from v$parameter where name in (‘resource_manager_plan’,’resource_limit’)’

SQL> show parameter resource

SQL> select window_name,resource_plan,last_start_date,duration,enabled from dba_scheduler_windows;

Solution:

Note 392037.1 – Database ‘Hangs’. Sessions Wait for ‘resmgr:cpu quantum’
Note 1339803.1 Recommended Patches for CPU Resource Manager
Set the maintenance window to other time where CPU resource is available maximum
During peak time clear the resource_manager_plan to none
alter system set resource_manager_plan=” scope=both;

You can disable resource plan for specific days by following commands

execute dbms_scheduler.set_attribute(‘MONDAY_WINDOW’,’RESOURCE_PLAN’,”);
Note: The above command will disable resource plan for Monday only

How to DBMS_SYSTEM Package

DBMS_SYSTEM package contains a number of procedures that can be useful. Oracle clearly state that these procedures are not supported so use at your own risk.

The package DBMS_SYSTEM is installed by running the script
$ORACLE_HOME/rdbms/admin/dbmsutil.sql
This script is internally called by
$ORACLE_HOME/rdbms/admin/catproc.sql
By default only the SYS user can execute procedures and functions in this package.

To allow other users to execute this package, run the following commands as SYS
GRANT EXECUTE ON dbms_system TO PUBLIC;
CREATE PUBLIC SYNONYM dbms_system FOR dbms_system;

Commonly used procedures or may required for DBA

ksdwrt
SET_SQL_TRACE_IN_SESSION
SET_EV
READ_EV

ksdwrt:

Used to write messages to the alertlog and/or trace files.

EXEC DBMS_System.ksdwrt(n, message);
EXEC DBMS_System.ksdwrt(2, ‘My Test Alertlog Message’);
Where the value of “n” indicates the destination.

1 – Write to trace file.
2 – Write to alertlog.
3 – Write to both.

Example:
To write a message to the trace file use
EXECUTE dbms_system.ksdwrt (1, ‘Output written to trace file’);
To write a message to the alert log use
EXECUTE dbms_system.ksdwrt (2, ‘Output written to alert log’);
To write a message to both the trace file and the alert log use
EXECUTE dbms_system.ksdwrt (3, ‘Output written to both trace file and alert log’);

SET_SQL_TRACE_IN_SESSION:

Used to set trace on or off in another users session.

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid, serial#, true );
EXEC DBMS_SYSTEM.set_sql_trace_in_session(31, 97, true );

This is the equivalent to enabling event 10046 level 1.

For example to enable trace in a session with SID 9, use:
EXECUTE dbms_system.set_sql_trace_in_session (9, 29, true);
To disable trace in the same session use:
EXECUTE dbms_system.set_sql_trace_in_session (9, 29, false);
The SID and serial# for the session can be obtained from V$SESSION e.g.:
DECLARE
l_sid NUMBER;
l_serial NUMBER;
BEGIN
SELECT sid, serial#
INTO l_sid, l_serial
FROM v$session
WHERE username = ‘User1’;

dbms_system.set_sql_trace_in_session (l_sid, l_serial, TRUE);
END;
/

SET_EV:

Used to set trace on for a specific event.

EXEC DBMS_SYSTEM.set_ev(sid, serial#, event, level, name);
EXEC DBMS_SYSTEM.set_ev(31, 97, 10046, 4, ”);

Where level indicates the following levels of trace.

1 – Standard SQL_TRACE functionality.
4 – As level 1 plus tracing of bind variables.
8 – As level 1 plus wait events.
12 – As level 1 plus bind variables and wait events.

READ_EV:

Used to check if a specific event is currently being traced.

EXEC DBMS_SYSTEM.read_ev(event, output);

If output = 1 the event is being traced.