Posts

Oracle SQL Optimization Tricks: Table Elimination and OR-Expansion Explained

-
Oracle optimizer's job is to find the most efficient way to execute SQL statements and provide the requested data to the client. We all know it is a complex code and gets improved as part of every product release. Today Oracle Optimizer is very evolved and has very rich useful features in-built in it (adaptive plans, dynamic statistics etc) Below is a basic optimization technique ,which may be there from 8i/9i, may be not relevant today , but still posting as sometimes below things may come up in some execution plan which you are checking. Table discard/elimination ========================= The query involves 2 tables - students and teachers , with student table having a FK relationship with the teachers table. CREATE TABLE STUDENTS ( STUDENT_ID NUMBER(10) NOT NULL, NAME VARCHAR2(50 BYTE) NOT NULL, TEACHER_ID NUMBER(10) ) ALTER TABLE STUDENTS ADD ( FOREIGN KEY (TEACHER_ID) REFERENCES TEACHERS (TEACHER_ID) ENABLE VALI...
Post a Comment
Read more

Installation and Configuration of ZDM(Zero Down-Time Migration).

-
Image
In this blog we will only concentrate on installation and configuration of ZDM. Pre-requisites: 1. Zero Downtime Migration service host should be a dedicated system, No GI/Oracle Home installed on the server. 2. Zero Downtime Migration software requires a standalone Linux host running on any one of the following platforms:      Oracle Linux 7, Oracle Linux 8, or Red Hat Enterprise Linux 8. Configuration of ZDM Server: a. For this POC we will install ZDM Service on a EC2 instance in AWS. For creation      of instance we used terraform.  OS used for this demo was "Red Hat Enterprise Linux release 9.6 (Plow)" (As this   was under free tier).  Instance type was t2.micro (As this was under free tier).   Storage requirement :    Minimum 500MB of swap space is required.    Minimum 100GB of additional space is required for downloading ZDM installer/binaries and logs.   b. After instance creation, below rpm...
Post a Comment
Read more

Oracle Zero Downtime Migration(Oracle ZDM) - Overview

-
Image
Oracle ZDM is a software solution which allows you to directly and seamlessly migrate your on-premises Oracle Databases to and between any Oracle-owned infrastructure, including Exadata Database Machine On-Premises, Exadata Cloud at Customer (ExaC@C), and Oracle Cloud Infrastructure. Oracle Zero Downtime Migration (ZDM) is designed using Oracle's best practices for high availability. It uses tools like Golden-Gate and Data Guard to keep your database available during migration, and relies on technologies such as RMAN, Data Pump, and database links to move data efficiently and safely — all with minimal downtime. Oracle ZDM offers four different migration methods: 1. Logical Online Migration using Golden-Gate.     In  logical online migration approach ZDM leverages Oracle Data Pump for the initial export and Oracle Golden-Gate for continuous data replication. Oracle Golden-Gate plays a critical role by capturing and applying real-time changes from the source to the tar...
1 comment
Read more

Row Locking and ITL in Oracle.

-
Image
Locking is very important aspect in a database. It is must to regulate access to (shared) resource so that one process/user doesn't change it while other is modifying the same. If we question how database may be locking rows/data, we usually may think there may be a process/manager which must be doing the task internally. The manager process maintains the information which process id wants/has to lock particular rows etc. Seems perfect for simple logical explanation, However if we consider this for OLTP systems, we can easily understand that such a single point of control may cause bottleneck. All processes competing for locks(acquire or release) on resources via a single manager will ensure that the application/workload cant scale at all. One of the major reason for Oracle being the top database is the locking mechanism which it implements. There is no such concept as lock manger etc. Oracle places the lock related information in the actual data block itself. Ex:  when a row in a ...
Post a Comment
Read more