Skip to main content

Posts

Showing posts from February, 2018

Oracle Foreground and Background Wait Events

What is foregraound and backgroup wait events?: Its *who* is doing the waiting that determines foreground or background. *You* run a query that does a full scan... its a *foreground* db file scattered read. *SMON* runs a query that a full scan... its a *background* db file scattered read, then there *might* be some interaction between the two, for example: *You* do a commit, its foreground wait for 'log file sync'. You've asked LGWR to write some data, so you have a foreground wait but you are waiting on a background process to complete. LGWR dispatches the I/O off to the OS and it is now waiting for the I/O to complete. So it has a background wait of "log file parallel write". You could argue that *you* are also effectively waiting on that as well, but we don't make that interpretation because it wasn't you that issued the I/O. Eventually the IO completes and LGWR is done, so its no longer waiting. Shortly thereafter, LGWR is going to let you know that t

Use case for Oracle Goldengate for replication

Use case for Oracle Goldengate to replication source: Logical replication from source to target, means SQL statement executed to propagate changes to target. Source and target database is heterogeneous, i.e. Oracle to SQL server , Oracle to MySQL. Source and target database is homogeneous i.e. Oracle to Oracle, SQL server to SQL server. Source and target database object structure is different . Rollback source transaction using Goldengate utility reverse. One column on source is mapped to different column on target. Prevent deletion of rows on target i.e. if rows are deleted from source and should not be deleted on target using Oracle Goldengate  Event Market Interface. Only part of the row need to replicated from source to target. Filtered data is replicated from source to target using SQLEXE function. Prevent modification of certain data on target when source data is modified, and log data modification using Oracle Goldengate Event Market Interface.

MySQL Transaction Isolation Level

Transaction Isolation Levels: Isolation level can be change for a single session or for all subsequent connections with the SET TRANSACTION statement. To set the server's default isolation level for all connections, use the --transaction-isolation option on the command line or in an option file. Each isolation level has different locking strategies. READ UNCOMMITTED : SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. READ COMMITT ED : First UPDATE acquires an x-lock on each row that it reads and releases those for rows that it does not modify. Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. If you use READ COMMITTED , you must use row-based binary logging . REPEATABLE READ : Default for InnoDB - the first UPDATE acquires an x-lock on each row that it reads and do

Oracle Download PSU | CPU | SPU | Bundle Patch | Patchset and Base Release

Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2) It will ask for following What would you like to download? Oracle Database Base Releases Oracle Database Patchsets Oracle Database Release Updates (RU) and Release Update Revisions (RUR) (Versions 12.2 & higher) Oracle Database PSU, SPU(CPU), Bundle Patches (Versions 12.1 & lower) OJVM RU/PSU/Bundle Patches Latest Available Microsoft Windows Patches Please select the version: Here you will select version And you will be able to see Description, PSU, SPU (CPU), GI PSU, and Bundle Patch (Windows 32 and 64 bit)

MySQL InnoDB parameters affect performance

Innodb Settings: innodb_buffer_pool_size : The size in bytes of the buffer pool , the memory area where InnoDB caches table and index data. innodb_buffer_pool_instances : The number of regions that the InnoDB buffer pool is divided into . For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency , by reducing contention as different threads read and write to cached pages. Increase up to 16 if you have large number of CPU cores. innodb_sort_buffer_size : Specifies the size of sort buffers in BYTES , used to sort data during creation of an InnoDB index . Default is 1048576. innodb_flush_method : O_DIRECT - uses O_DIRECT (or directio() on Solaris) to open the data files , and uses fsync() to flush both the data and log files . Bypass OS cache . Good choice for most IO subsystems, default - NULL innodb_flush_log_at_trx_commit : With this value, the contents of the InnoDB log buf