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.…
There are several requests to a DBA for changing the Database Name & DBID of a database, suppose user wants to clone one his env. (say PROD01) to make it a dev or test instance, so he clones the VM but this would give a database with the same Database Name as that of the…
Dead connection detection (DCD) is a Oracle net feature which is primarily intended for environments where clients power down their systems or client machine crashes unexpectedly without gracefully closing the connections from the Oracle database. In cases where client machine abruptly crashes with properly disconnection the sessions from Oracle databases the resources locked by such…
This is a quick post to troubleshoot ORA-12519 TNS:no appropriate service handler found & ORA-12516 TNS:listener could not find available handler with matching protocol stack. Most common reasons for the ORA-12516 and ORA-12519 errors is the configured maximum number of PROCESSES and SESSIONS limitation being reached. When this occurs, the service handlers for the TNS listener become “Blocked”…
This is a quick post to resolve ORA-28040 & ORA-03134, specially if you have recently upgraded to 12c. ORA-28040: No matching authentication protocol error. ORA-03134: Connections to this server version are no longer supported error. Actually from 12c the default setting for allowed logon version is 11, that means by default you can only connect…
This is a quick article on managing Voting Disk, the voting disk internals are explained in Voting Disk Internals post. From 11g release 2, voting disks can be stored on either on ASM diskgroups or on cluster file systems, following are the steps to add/delete/move a voting disk:- How to add a Voting Disk When voting disk…
Oracle Database Internal Mechanism 