Oracle Wait Events

When writing posts on performance tuning I felt that there needs to be a my-version of description of Oracle Wait Events along with Access method and Process & Memory Architecture. Thus this series is to start the Oracle Wait events which are common in any Database Environment BUT has a lot of story. db file…

db file scattered read – oracle wait event

DB File scattered read wait event occurs when an oracle process waits for a physical read of multiple oracle blocks from the disk. This wait identifies that a full scan (Full Table Scan or Index fast full scan) is occurring BUT during this read the blocks are read into memory locations that are not physically…

db file sequential reads – Oracle wait event

DB file sequential reads wait event comes under the User I/O wait class. In any busy system this is one of the most common wait event and normally appears in the top 5 wait events. In this post we’ll understand whats happening during this wait event. In simple words, Db file sequential reads wait event…

12c New Features – Invisible Columns

There can be several situations where you want to test the impact of dropping a column from a table, till 11gR2 Oracle provided an option to mark a column as UNUSED before dropping a column, and do ALTER TABLE … DROP UNUSED COLUMNS at a later time but marking a column UNUSED is a irreversible…

Move Table Online – 12cR2

Until 11gR2 to move a table to different tablespace there were two available methods, first one was ALTER TABLE .. MOVE TABLESPACE but during this the table was locked for DML, thus downtime and the second one was DBMS_REDEFINITION which will allow to create a new table on the desired tablespace and sync the data…

Move Table Partition/Sub-Partition Online – 12cR1

With 12cR1 you can now move table partitions and sub-partitions online. There are lot of reasons for which you might want to move a table partition or sub-partition such as:- Re-cluster data and reduce fragmentation. Move a partition to another tablespace. Modify create-time attributes. Store the data in compressed format using table compression. The problem…