This article examines a high‑volume Oracle query scenario, explains why using multiple IN clauses on millions of rows leads to costly full‑table scans, and presents three alternative strategies—including a single‑SQL join, UNION ALL view, and temporary‑table approaches—along with practical examples and performance considerations.
This guide answers common Oracle backup questions, covering planning for 50‑TB databases, speed calculations, backup window design, RMAN versus Data Pump, multi‑user and RAC scenarios, catalog usage, performance troubleshooting, and practical SQL queries for monitoring backup status.
This comprehensive guide covers Oracle SQL tuning techniques, execution‑plan inspection, optimizer choices, high‑resource SQL detection, session tracing, index fundamentals, backup classifications, recovery procedures, system monitoring, and performance diagnostics, providing practical knowledge for DBA interviews and daily work.
This guide explains Oracle's TX row lock mechanism, shows how to identify blocking sessions, locate the exact locked rows using system views and ROWID functions, and provides practical steps to prevent and mitigate TX lock waiting issues.
This article walks through the internal structures of MySQL, Oracle, and PostgreSQL, detailing their logical and storage layers, key modules, process interactions, memory components, and how these elements cooperate to handle client requests and ensure data integrity.
This guide presents seventeen practical Oracle SQL optimization tips—including table order selection, WHERE clause ordering, avoiding SELECT *, reducing round‑trips, using DECODE, consolidating queries, eliminating duplicates, preferring TRUNCATE, leveraging COMMIT, replacing HAVING with WHERE, minimizing sub‑queries, employing internal functions, using aliases, favoring EXISTS over IN, detecting inefficient SQL, and proper index usage—to help developers write faster, more efficient database queries.
This article explains the classification of Oracle data dictionary objects, demonstrates how to query static and dynamic dictionary tables and views, lists common view families and permission views, and provides numerous SQL examples for inspecting users, tables, indexes, sequences, constraints, and PL/SQL objects.
This article explains why conventional indexes waste space and perform poorly on highly skewed columns, introduces a decode‑based function index that excludes high‑frequency values, details the experimental setup with millions of rows, compares index size and query performance, and outlines the method's limitations.
An Oracle SQL query with five EXISTS permission checks runs dramatically slower when only a few permissions are configured, and the author investigates the root cause using execution plans, SQL Monitor data, and step‑by‑step testing, ultimately revealing how execution counts and row returns drive the unexpected performance degradation.
This article explains the most common Oracle wait events—including buffer busy waits, buffer latch, control file parallel write/read, various DB file reads and writes, latch free, and library cache lock—detailing their causes, parameters, and how to query them for performance analysis.
The November 2017 DB‑Engines ranking shows Oracle, MySQL and Microsoft SQL Server still dominate the top three, while Splunk and HBase swap places, PostgreSQL climbs to fourth with a 54.1% year‑over‑year gain, and the list is based on five popularity factors such as search engine queries, job postings, and Stack Overflow activity.
This guide presents thirty‑four practical Oracle SQL optimization techniques—from choosing the most efficient table order and rewriting WHERE clauses to leveraging indexes, avoiding SELECT *, using DECODE, and replacing costly operations—helping developers dramatically improve query speed and resource usage.
A customer’s Oracle 11g RAC experienced chronic high CPU usage; deep AWR analysis revealed that massive parse time was attributed to mutex waits and a hidden hard‑parse failure caused by a missing column, which triggered Bug 16175381 and forced process spin, ultimately resolved by correcting the faulty SQL.
This article provides a detailed comparison of Oracle RAC and MySQL MHA high‑availability solutions, covering architecture overviews, network requirements, failover methods, configuration examples, disaster‑recovery options, performance trade‑offs, and practical considerations for choosing the right approach.
Facing a last‑minute performance crisis on a major release, the author dissected eight SQL statements, merged four permission‑related queries using Oracle's RANK function, and reduced the responsibility‑lookup time from five seconds to under one second, ensuring the system met its strict latency target.
This article explains how Oracle's logical structures—blocks, segments, tablespaces, and rowids—affect SQL performance, presents detailed experiments on block capacity, row migration, segment handling, tablespace behavior, and provides practical optimization guidelines based on observed logical read reductions.
This article walks through diagnosing a sluggish Oracle search query with many optional filters, removing unnecessary joins and function calls, applying conditional MyBatis joins, and creating composite indexes to dramatically lower the execution plan cost from 13902 to under 100.
The article details a step‑by‑step recovery of an Oracle database where the SYSAUX01.DBF file was corrupted, using DBV detection, attempted repairs, analysis of export/Data Pump failures, and low‑level file parsing to fully restore the ZXFG user's data.
This guide provides a comprehensive collection of Oracle SQL commands covering connection, query execution, variable handling, formatting, tablespace creation, modification, and external table operations, presented step‑by‑step for database administrators and developers.
Oracle provides several powerful recovery mechanisms—logical backups with the import tool, physical file restoration with incomplete recovery, LogMiner analysis of redo logs, and Flashback Query—each illustrated through a scenario where a mistaken update to the TFUNDASSET table is corrected using step‑by‑step procedures and commands.
The article reviews Oracle's high‑availability and sharding advances in 18c, including RAC Sharding 2.0 and Active Data Guard features, and examines MySQL 8.0's major improvements such as the removal of MyISAM, data‑dictionary optimizations, self‑tuning, enhanced security, and new performance capabilities, while also comparing MySQL with PostgreSQL.
This guide explains Oracle datafile concepts, how to query absolute and relative file numbers, create and add files to tablespaces, modify file size with autoextend or resize, change file availability, rename or move files, and safely delete files while observing key restrictions.
A senior SQL specialist recounts a high‑pressure production incident where a seemingly trivial INSERT statement caused a timeout, analyzes the flawed database design, compares alternative architectures, and argues for handling bulk data processing directly in the database.
Oracle announced the transfer of Java EE to the Eclipse Foundation, outlining the motivations, expected benefits for the community, and how the transition will affect Java EE licensing, implementation, and future innovation in enterprise development.
Oracle announced that Java will adopt a strict six‑month release cadence, with the next version slated for March 2018, shifting from the previous two‑year cycle and ensuring future releases in March and September without delays for unfinished features.
This article recounts a real‑world Oracle SQL performance case where a slow WM_CONCAT‑based query took over 30 minutes, the root cause was identified, and a rewritten SQL using window functions and proper aggregation reduced execution time to under three seconds.
During an external penetration test I discovered an Oracle Advanced Support service, reverse‑engineered its JavaScript endpoints, crafted GET and POST requests to create and execute named SQL statements, and ultimately extracted database version, user information, and password hashes, highlighting a critical web‑application flaw.
Learn practical techniques to reduce unnecessary I/O and CPU consumption in SQL queries, including narrowing scan ranges, using partition filters, optimizing joins, leveraging hints, bind variables, and proper use of MERGE, UPDATE, EXISTS/IN, pagination, temporary tables, and other performance‑tuning strategies.
This article explains Oracle's architecture—including the SGA, Shared Pool, Data Buffer, and Log Buffer—shows how each component influences SQL parsing, bind variables, result caching, direct‑path inserts, batch commits and logging, and provides step‑by‑step experiments and practical techniques for diagnosing and tuning performance bottlenecks.
This article teaches how to read and manipulate Oracle execution plans using hints, SQL rewrites, design features, and statistics tricks, providing step‑by‑step examples, common pitfalls, and practical scripts to achieve predictable performance without costly statistics collection.
This article explains Oracle's system structure, the physical files used by RAC and ASM, their I/O patterns for OLTP and OLAP workloads, and provides detailed recommendations for ASM diskgroup design, redundancy, AU sizing, RAID choices, and SSD optimization for read and write intensive database operations.
This article explains how Oracle's parallel processing feature can accelerate data‑intensive queries by configuring session, table, or hint‑based parallelism, shows the degree‑of‑parallelism formula, and presents experimental results that compare execution times under different settings.
This article explains the fundamentals of Oracle SQL execution plans, how to read and interpret them, the role of statistics in plan selection, methods for gathering statistics, various techniques for obtaining execution plans, and practical tips for identifying inefficient SQL statements.
This article explains what an Oracle SQL execution plan is, how statistics guide the optimizer, various ways to retrieve plans, the classification of plan types, and practical tips for spotting and fixing inefficient plans using real‑world examples and code snippets.
This article explains what raw (裸) devices are, how to identify and use them with Oracle on Unix, their performance advantages, when they should be employed, and practical tips for configuration, backup, and related storage considerations.
This article explains how to leverage the Linux perf tool to profile Oracle database workloads, covering basic commands, probe-based tracing of Oracle internal functions, generating various flame graphs, and practical Q&A for integrating perf into performance monitoring workflows.
This guide compares SQL Server and Oracle across ten key areas—including data types, date functions, default constraints, string concatenation, identity columns, conditional statements, CASE syntax, triggers, stored procedures, and user creation—providing concrete code examples for each difference.
The article shares a DBA's real‑world experiences—from a risky MySQL restart that exposed hidden issues, through practical learning advice and MySQL technical nuances, to a detailed Oracle migration case—offering actionable insights for database professionals seeking robust data management and performance optimization.
This article presents a comprehensive analysis of horizontal database sharding, detailing the design decisions, partitioning dimensions, routing, pagination handling, lookup mapping, and deployment steps based on a real‑world implementation at a large e‑commerce platform, offering practical guidance for scaling order databases.
This article explains why SQL tuning often wastes hours, identifies common inefficiencies, and provides a step‑by‑step, one‑click method—including ready‑to‑run scripts—to collect full database performance data and detailed SQL information, dramatically speeding up problem resolution.
This guide explains how to use SRVCTL to view the status of Oracle RAC databases, instances, services, node applications, and ASM instances, retrieve configuration details, start and stop the cluster, and perform backup and restore operations with concrete command examples.
This article provides a comprehensive guide to Oracle performance tools—including AWR, ASH, ADDM, AWRDD, and AWRSQRPT—explaining how to obtain and interpret their reports, key metrics to monitor, and real‑world case studies that illustrate effective database optimization for both simple and complex scenarios.
This article explains Oracle database performance tuning by introducing the five key performance reports—AWR, ASH, ADDM, AWRDD, and AWRSQRPT—detailing how to obtain them, what metrics to focus on, and how to apply them in real‑world case studies for both simple and complex optimization scenarios.
This guide explains Oracle SEQUENCE objects—how they generate numeric IDs, the full CREATE SEQUENCE syntax, practical usage with NEXTVAL and CURRVAL, alteration options, querying via data‑dictionary views, and safe removal—complete with runnable SQL examples.
This article compares SQL Server, Oracle, and MySQL, outlining each system’s key advantages such as scalability, platform support, performance, and cost, while also detailing their main drawbacks including limited openness, hardware demands, security complexities, and feature gaps, helping readers choose the most suitable database.
This article walks through a real‑world Oracle SQL tuning case, showing how to dissect a 12‑hour execution plan, fix cardinality and statistics issues, eliminate costly Filter operations, rewrite queries for efficient HASH JOINs, and leverage tools like 10053 and SQLT to dramatically improve performance.
This article walks through a real‑world Oracle pagination query that took over five seconds for just fifteen rows, analyzes why ORDER BY caused the slowdown, and demonstrates a step‑by‑step rewrite using a WITH clause and index‑driven access that reduces execution time to under one second.
This article explains how the DRIVING_SITE hint can reduce network traffic in Oracle distributed queries by pushing small tables to the remote site, demonstrates setup steps, compares execution times with and without the hint, and provides concrete PL/SQL scripts for performance testing.
This guide explains how a missing automatic mount for the Oracle data partition caused command failures after a Linux reboot, and shows step‑by‑step how to use UUIDs in /etc/fstab to ensure reliable mounting and restore Oracle ASM functionality.
This article provides a comprehensive guide to Oracle Listener, covering its core functions, how to operate it via lsnrctl commands on Windows, detailed configuration of listener.ora for static and dynamic registration, and best practices for ensuring high availability and load balancing.
A developer added a seemingly harmless filter to an Oracle SQL statement, causing the optimizer to switch the driving table and join method, which inflated execution time from 2 seconds to 90 seconds, prompting a deep dive into execution plans, indexing, and hint techniques.
This article explains why exporting a schema with Oracle Data Pump can leave a table's primary‑key sequence behind the actual data, demonstrates the problem with a live test, and provides a step‑by‑step fix by resetting the sequence to the maximum key value.
This article compiles a DBAplus online session that presents a systematic SQL‑optimization methodology, rewrite tricks, common pitfalls, and a series of concrete Oracle examples—including CASE WHEN, ROWNUM pagination, hint usage, column selection, function‑call reduction, trigger avoidance, and ROWID access—plus a detailed Q&A that clarifies practical tuning questions.
Oracle character sets define how byte data maps to symbols, with NLS_LANG controlling client language, territory, and charset; this guide explains the components, naming conventions, differences between database and client sets, export/import conversion processes, and methods to view or modify character sets safely.
This article compiles 38 practical Oracle SQL tuning techniques—from ordering tables and rewriting WHERE clauses to using indexes, avoiding SELECT *, leveraging DECODE, replacing HAVING with WHERE, and preferring TRUNCATE over DELETE—each illustrated with clear examples to help developers write faster, more efficient queries.
This article explains Oracle instance recovery, detailing when it is needed, how checkpoints and SCNs are recorded during clean and abnormal shutdowns, and the step‑by‑step forward‑roll and rollback process performed by SMON to restore database consistency after a crash.
This guide outlines practical principles for creating Oracle indexes, compares B‑Tree and bitmap index types, explains their creation syntax, highlights performance characteristics, and lists common situations where indexes may be ignored or unsuitable.
The article details the discovery of a local file inclusion (LFI) flaw in Oracle Responsys, explains how crafted requests exploit the _ri_ parameter to read arbitrary files, highlights the impact on major companies like Facebook and LinkedIn, and describes the responsible disclosure that led to a rapid patch.
This article explains eight typical situations in which Oracle SQL statements cannot use indexes, illustrates each case with concrete examples and step‑by‑step rewrites, and offers practical solutions—including predicate adjustments, function indexes, and query‑transformation fixes—to improve database performance.
This article presents two comprehensive Shell scripts—one to validate Oracle RMAN backup sets and another to perform a full backup‑restore experiment—detailing SSH trust configuration, source information collection, log transfer, directory preparation, and step‑by‑step RMAN restore commands, all illustrated with practical screenshots and a GitHub repository link.
This guide details the step‑by‑step process of moving a Windows‑based Oracle 11g RAC cluster to a Linux environment, focusing on building DataGuard between a single‑instance source and a RAC target, adjusting IPs, updating parameter files, handling tnsnames.ora, password files, RMAN backups, and finalizing the standby configuration.
This article explains eight common reasons why Oracle SQL statements fail to use indexes, provides concrete rewrite techniques, shows how optimizer bugs can block index usage, and outlines practical steps for enforcing SQL development standards through automated audit tools.
This guide walks through creating an Oracle 12cR2 container database with CREATE DATABASE, explains OMF vs non‑OMF options, details the catcdb.sql/perl changes, resolves common Perl module errors, compares 10g/11g syntax differences, and provides practical fixes such as blocksize adjustments.
When migrating applications from Oracle to MySQL, many teams encounter severe performance problems such as CPU saturation, high logical and physical reads, and slow queries, and this article outlines the root causes—including lack of parallelism, limited join algorithms, IN‑list limits, subquery inefficiencies, missing materialized views, and unsupported function indexes—and provides concrete remediation strategies for each issue.
This article explains how Oracle's bind variable peeking influences execution plan selection, demonstrates the performance differences between full table scans and index scans with skewed data distributions, and provides practical methods—including 10046 events, v$sql_bind_capture, and AWR views—to capture and analyze bind variable values for better SQL tuning.
The article explains why a seemingly harmless delete statement in an Oracle RAC+DG environment can become extremely slow, covering bind variable peek, execution‑plan changes, histogram effects, AWR/ASH analysis, rolling invalidation, and clustering factor, and shows how to diagnose and mitigate each issue.
This article explains how Oracle uses various lock types—including DML, DDL, and internal locks—to protect shared resources, details the lock manager workflow, describes the three lock components and lock modes, and shows practical SQL examples of transaction handling and blocking detection.
This article outlines practical steps DBAs can take to prevent data leakage and loss, covering user and profile management, privilege minimization, audit logging, vulnerability patching, high‑availability solutions like RAC and Data Guard, and effective backup and recovery drills.
This article explains how vulnerabilities, backdoors, and various Oracle rootkit techniques—ranging from simple package tricks to OS‑level and memory‑level attacks—allow attackers to maintain persistent, hidden access to databases, and it offers concrete detection and mitigation strategies.
This article shares practical experiences and step‑by‑step guidance for migrating databases from Oracle to MySQL, covering pre‑migration preparation, target selection, data‑object migration tools such as SQL LOAD, Python scripts, Oracle GoldenGate, MySQL Migration Toolkit and Kettle, handling of views, triggers, stored procedures, data validation techniques, and key MySQL performance parameters.
This article explains InnoDB’s double‑write buffer in MySQL, compares its design and recovery handling with Oracle’s redo and control‑file mechanisms, discusses partial‑write issues, checkpoint strategies, performance impacts on SSDs, and provides practical commands and configuration tips for DBAs.
This comprehensive guide explains Oracle database failure types, the DBA's role in ensuring availability, and detailed recovery techniques—including flashback, instance recovery, checkpoint handling, redo log management, and configuration of fast recovery areas—to minimize downtime and data loss.
After migrating a core system from AIX to Linux and upgrading Oracle from 11.2.0.3 to 11.2.0.4, a critical SQL that previously ran in milliseconds began taking hundreds of seconds, prompting a detailed investigation of execution plans, CBO limitations, and three successive rewrite strategies that restored performance.
This article explains how to manage data conversion errors during Oracle data loading by using the new VALIDATE_CONVERSION function to identify problematic rows and the enhanced CAST (and TO_…) functions with ON CONVERSION ERROR defaults, providing step‑by‑step examples, SQL scripts, and practical tips for clean inserts.
This article explains how to use Oracle's driving_site hint to minimize network traffic in distributed DBLINK queries, demonstrates a banking case where execution time drops from over eight seconds to under one second, and provides step‑by‑step view‑based solutions for DML optimization.
This article explains Oracle's dynamic sampling feature, outlines typical scenarios such as temporary tables and complex filters, presents real CRM case studies with misestimated cardinalities, demonstrates step‑by‑step solutions using DBMS_STATS commands, and shows how 12c enhancements dramatically improve estimation accuracy for complex queries.
When moving data from Oracle to MySQL, Chinese characters may appear as garbled text because the server uses a latin1 charset; this guide explains three levels of fixes—table, database, and server—by configuring UTF‑8 settings and verifying the changes.
This article explains why frequent small commits are slower than a single bulk SQL operation, demonstrates how to rewrite PL/SQL procedures to use bulk inserts, adjust UNDO settings, and enable parallel execution, achieving up to six‑fold performance gains on large data loads.
This article presents a comprehensive guide to XTTS, an Oracle table‑space transfer tool that extends TTS with cross‑platform support and incremental backup, comparing it with Data Pump and GoldenGate, detailing step‑by‑step procedures, performance optimizations, real‑world case studies, and a pre‑migration checklist.
The article recounts a troubleshooting session where a 12c Oracle standby database failed to connect via TNS, presenting the ORA-12514 error, analyzing listener and tnsnames configurations, uncovering an outdated 11g ORACLE_HOME, and detailing the steps to switch homes and restart the listener to resolve the issue.
An Oracle 11g instance on AIX 6100‑07 unexpectedly restarted due to a memory‑management bug triggered by the numperm_global=1 setting, which caused massive paging despite ample free memory; the article analyzes logs, OS metrics, and offers four remediation options including parameter changes and OS patches.
This article compares how to create tables and perform upsert (insert‑or‑update) operations in MS SQL and Oracle, presenting five distinct Oracle techniques—including implicit cursor, exception handling, dual table, no_data_found, and MERGE—each with complete PL/SQL code samples.
An in‑depth walkthrough of Oracle 12c R2 Sharding, covering its core concepts, architecture, environment setup across three virtual machines, detailed GSM and shard catalog configuration commands, test model, results, limitations, and troubleshooting tips for real‑world OLTP deployments.
The article explains Oracle 12c Release 2's launch, decodes its version numbering, highlights new capabilities such as Global Service Manager, In‑Memory, Multitenant and Sharding, compares parameter growth across releases, and offers practical upgrade timelines and key parameter recommendations.
This guide explains how to transform an ordinary Oracle table larger than 2 GB into a partitioned table using the online redefinition method (DBMS_REDEFINITION), detailing each step from feasibility checks to final cleanup with example PL/SQL code.
This article walks through common traps in Oracle's Cost‑Based Optimizer (CBO), explains why query transformations, FILTER operations, and inaccurate statistics can cripple performance, and provides practical solutions—including NULL‑AWARE anti‑joins, hint usage, SQLT tooling, and SQL rewrites—to dramatically speed up problematic queries.
The article examines a high‑volume Oracle query scenario, outlines its inefficiencies when using multiple IN clauses, and compares three optimization approaches—including a single‑SQL join, UNION ALL views, and temporary tables—while providing detailed SQL examples, performance considerations, and practical recommendations.
This article examines the surprising behavior of Oracle when adding NOT NULL columns with default NULL values, explains how different Oracle versions handle the constraint, demonstrates test cases, and clarifies the underlying data‑dictionary mechanism that leads to unexpected query results.
This comprehensive guide explains every major SQL join type—including CROSS, INNER, OUTER, FULL, SEMI, ANTI, LATERAL, and MULTISET—detailing their logical behavior, practical use‑cases, alternative syntaxes, performance implications, and visual examples across PostgreSQL, Oracle, and SQL Server.
Facing a tight September release deadline, the author tackled two critical Oracle SQL performance issues by analyzing execution plans, replacing a costly view, applying driver‑table hints, removing unnecessary CASTs and a random‑number function, ultimately cutting query time from 11 seconds to under 2 seconds.
Oracle TimesTen In-Memory Database, acquired by Oracle, offers a memory-optimized relational database with shared libraries, checkpointing, logging, and advanced replication options such as synchronous and asynchronous data replication, cache integration, and broad OS/CPU compatibility, positioning it as a high‑performance alternative to traditional RDBMS.
This article explains how to configure client‑side and server‑side Connect Time Load Balancing for Oracle RAC across versions, details the role of SCAN, local_listener and remote_listener settings, describes Runtime Connection Load Balancing with FAN and LBA events, and shows how to tune TCP timeout parameters.
This article explains Oracle Flashback technology—from basic flashback queries and version queries to transaction-level recovery—detailing required undo tablespace settings, essential parameters, SQL syntax, and step‑by‑step examples that demonstrate how to restore erroneous DML operations efficiently.
This guide presents comprehensive Oracle database performance optimization techniques—including data structure design, application architecture, SQL tuning, memory and CPU adjustments, I/O balancing, and essential tools—providing actionable steps and example queries for DBAs to improve system responsiveness and efficiency.
A developer encountered a painfully slow SELECT on an Oracle test server, discovered that an uncommitted INSERT left 852 data blocks allocated, causing massive redo size and logical reads, and learned how committing the transaction instantly restores normal query performance.
The article recounts a real‑world Oracle SQL case where a REGEXP_SUBSTR‑based IN subquery caused the optimizer to abandon an index range scan, explores why predicate pushdown fails with CONNECT BY recursion, documents attempted hints, rewrites, and the ultimate lessons for SQL performance tuning.
This article outlines common Oracle migration pitfalls—including architectural constraints, resource impacts, application coordination issues, and diverse database problems—drawn from real‑world projects, and offers practical guidance to help DBAs anticipate and mitigate these challenges during system cut‑overs, upgrades, and consolidations.
This guide walks through simulating a catastrophic Oracle database failure, performing incremental RMAN backups, verifying backup completeness, and executing a full restore and recovery—including spfile, controlfile, datafiles, and archived logs—so you can confidently recover a database after total data loss.
This guide explains how to calculate and interpret key Oracle hit ratios—including library cache, shared pool, buffer cache, data buffer, sort memory, and PGA—provides exact SQL statements for each metric, and offers tuning thresholds and actions to improve database performance.
A production Oracle RAC database on IBM Power8 experienced a severe hang due to incomplete checkpoints caused by a bug in index handling, and the detailed analysis, root‑cause identification, and three‑step remediation—including index rebuild, application isolation, and a hidden parameter tweak—ultimately restored full service.
