sec_case_sensitive_logon – > This initialisation(init) parameter enables or disabled password case sensitivity in the database. when sec_case_sensitive_logon is TRUE – -> t he database use login passwords are case sensitive. when sec_case_sensitive_logon is FALSE – -> means database use login passwords are case insensitive. DEFAULT VALUE IS TRUE . Lets see […]

DEFERRED_SEGMENT_CREATION parameter  specifies how segments will be created for the table and its objects like index.   DEFERRED_SEGMENT_CREATION=TRUE     —–>> segments for tables and their dependent objects (indexes) will not be created until the first row is inserted into the table. DEFERRED_SEGMENT_CREATION=FALSE  ——->> As soon as table/index is created, segments will be created (even […]

LOG_CHECKPOINTS_TO_ALERT  parameter indicates whether to log the checkpoint information in the alert log or not. LOG_CHECKPOINTS_TO_ALERT – > FALSE( défault value) SQL> show parameter LOG_CHECKPOINTS_TO_ALERT NAME TYPE VALUE ———————————— ———– —————————— log_checkpoints_to_alert boolean FALSE SQL> alter system checkpoint; System altered. ALERT LOG: Tue Feb 26 03:35:55 2019 Thread 1 advanced to log sequence 6066 (LGWR […]

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 […]

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 […]

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 […]

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. $ ps -ef | grep lreg oracle 3536 105780 0 17:21 pts/5 00:00:00 grep lreg oracle 95761 1 0 Oct26 […]

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 […]

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 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: SQL> show […]