Database-Wiki

DBMS_PARALLEL_EXECUTE in oracle PL/SQL

From oracle 11gr2, DBMS_PARALLEL_EXECUTE package can be used for updating large tables in parallel using chunk option. Basically, it does two steps. 1. Divides the table data into small chunks. 2. Apply DML change on each chunk parallely. This method improves the performance the large DML operations, without consuming too much of rollback segment. Lets […]

WHAT IS SQL PROFILE IN ORACLE

You might have heard of SQL profile and SQL baselines in performance tuning chapters. Lets today go through details about sql profile. What is SQL profile: SQL profile is a collection of additional statistical information stored in the data dictionary that helps the optimizer to generate the best plan for the query. SQL profile is […]

_use_adaptive_log_file_sync parameter in oracle

The parameter _use_adaptive_log_file_sync was introduced in 11gR2 and controls whether adaptive switching between post/wait and polling is enabled. DEFAULT VALUE of this parameter is TRUE. i.e according to oracle algorithm and internal statistics, the adaptive switching will happen between post/wait and polling method. There are 2 methods by which LGWR and foreground processes can communicate […]

LREG Background Process in oracle

Prior to 12c, PMON process used to handle the instance registration with listner, From oracle 12c, a new background process called lreg takes care of this instance registration, Which reduces the load from pmon process.

Basically, LREG process does below things. 1. Registers instance information with the listener. 2. It is a critical background […]

_optimizer_ignore_hint

Sometimes application queries come with the specific hint, which may impact your database performance. And it is difficult to find and remove these hints from each query. Oracle provided an undocumented hidden parameter, _optimizer_ignore_hint. If this parameter is set to true, Then it will ignore the hints mentioned in the SQL queries. DEFAULT VALUE OF […]

oracleasm utility for ASMLIB

ASMlib driver is required on  Linux operating systems, to enable a disk readable by ASM. Without ASMLIB, disks can’t be used at asm disks. Below are the steps for configuring ASMlib and marking disks as ASM.   Download Asmlib: You can download these rpm packages directly from OTN: http://www.oracle.com/technetwork/server-storage/linux/downloads/index-088143.html Else, if your system is registered […]

resumable_timeout parameter in oracle database

resumable_timeout is an initialization parameter introduced in Oracle 9i.  This parameter defines the duration in seconds, for which the statements will be suspended if there is no space available in the tablespace for that operation. Once we add space to that tablespace, those transactions will resume their operation. Let’s see the below example:

The […]

Skip to toolbar