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…
As the name suggests, Index Full Scans are Index Scans where the entire index is scanned in order. Following are the situations where optimizer chooses Index Full Scans:- A predicate references a column in the index. This column need not be the leading column. No predicate is specified and all columns in the table and…
Optimizer chooses Index Range Scans for selective queries which have bounded as well as unbounded data. Thus for every predicate clause having >,>=,<,<= condition on an indexed column, Oracle optimizer would go for Index Range Scans unless fetching large amount of data or unselective query which favours FTS. Following are the situations where optimizer chooses…
Index Unique Scans returns at most 1 rowid, this means that whenever the condition in WHERE clause guarantees that only one row would be returned Oracle optimizer chooses Index Unique Scans. To summarize this, Index Unique Scan access path is used when all columns of a unique (B-tree) index or an index created as a…
Rowid is an internal representation of the storage location of data. The rowid of a row specifies the data file and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row because it specifies the…
This is another post in the series where we would be sharing the brief about Access Paths chosen by the optimizer and the situations where it would prefer to choose that Access Path. In Direct Path Read, the server process reads the data block from disk directly into process-private memory i.e. PGA bypassing the SGA.…
This post is to describe all the clauses available with Oracle datapump export (expdp). This article assumes that the reader has basic knowledge of database administration. If not gone through yet please go through the articles Oracle datapump Architecture & Internals so that you have basic architecture understanding of datapump. Oracle 12c datapump have following clauses, which…
Oracle Database Internal Mechanism 