Oracle DBA SQL Tuning With Real Time Scenarios

Install Oracle Linux 8.2.0 in VirtualBox, create a VM with 8 GB RAM and 300 GB disk, and use GUI to configure root 30 GB and 4 GB swap.

Configure PuTTY to securely connect to the virtual machine, enable ssh and X11 forwarding, adjust network settings, save the session, and log in as root.

Configure Oracle prerequisites for installing Oracle database software, fix ethernet adapter issues, set host name and IP, create Oracle user and directories, disable firewall and SELinux, and prepare startup scripts.

Transfer Oracle database software zip from Windows to Linux VM via WinSCP, unzip it in the Oracle home, and run the installer to install Oracle Database 19 enterprise edition.

Install Oracle database from the Oracle home using the Run Installer, configure fast recovery area and archiving, optionally enable pdb and sample schemas, and set environment variables to complete setup.

Create generic sample tables for testing SQL tuning, including dropping and recreating xtbl, ytbl, and ztbl, building sequences and indexes, inserting test data, and gathering statistics.

Explore Oracle database architecture from client to server, covering the shared pool, library cache, soft versus hard parses, buffer and undo, read consistency, and result cache for SQL tuning.

Discover how SQL is processed from the shared pool through syntax and semantic checks, then optimized into the best execution plan using the query transformer and buffer cache.

Explore how the shared pool handles sql hashing, and the creation of parent and child cursors, and how soft and hard passes and optimizer mode changes influence execution plan sharing.

Understand when to cache a table from a data file in the buffer cache. Consider undo blocks and direct path read with PGA in caching decisions.

Explore the evolution of Oracle database features by version, from SQL basics to advanced tuning tools, helping DBAs diagnose performance issues and select the right parameters.

Identify SQL tuning factors such as stale statistics, plan changes, and optimizer parameters; assess workload and parallelism using ash, edm, traces, and sql monitor to optimize performance.

Clarify DBA roles and responsibilities before tuning to set clear boundaries. Assess baseline and measurable, achievable, and relevant goals for SQL performance, then iteratively troubleshoot using reports and execution plans.

Explore how Oracle parses sql, generates sql id and plan hash values, and demonstrates buffer cache and its behavior with buffer gets, discretes, and io during repeated executions.

Understand how Oracle treats slight query differences, causing new SQL IDs and hash values, and how the shared pool and buffer cache influence execution and performance.

Learn how SQL response time depends on network, jdbc, listener, and memory caches, and how DBAs use the cis metric view and sql server response time to monitor baseline deviations.

Learn how to generate and read Oracle execution plans using explain plan, autotrace, and dbms_xplan, understand estimated vs actual plans, plan tables, and runtime statistics.

Enable and disable performance data collection by adjusting statistics level and timed statistics; use typical for most statistics and all for plan statistics during SQL tuning.

Learn to read Oracle execution plans by applying simple rules: indentation, right-to-left, and top-down order to identify which steps execute first, such as table access and index scans.

Explore how to interpret complex execution plans using right-to-left, top-down, and indentation guides, supported by diagrams to map parent-child steps and hash join operations.

Explore hands-on execution plan analysis by simulating queries and reading plans with top-down/right-left and tree methods, learning to interpret stars as filter or access and deduce the execution order.

Learn how Oracle assigns query blocks to each SQL statement, generates runtime execution plans with display cursor, and uses signatures, sql IDs, and hints to map to profiles and baselines.

Learn how selectivity, the fraction of rows matching a predicate, and cardinality, the estimated rows returned, drive Oracle's execution plans, choosing index scans or full table scans based on statistics.

Learn to generate a SQL health check report to diagnose slow SQL by analyzing execution plans, statistics including histograms, 153 trace data, and SQL Tuning Advisor recommendations.

Discover how the SQL tuning advisor, part of the tuning pack, generates recommendations like index creation, execution plan changes, or query rewrites.

This lecture introduces the sql monitor report as a tool to identify where time is spent in execution plans, with cpu, io, and buffer gets details in html or text.