SQL Tuning  «Prev  Next»

Lesson 2Oracle SQL Tuning Prerequisites
ObjectiveExplain the SQL skills, Oracle concepts, and practice access needed to begin SQL tuning in Oracle AI Database 26ai.

Oracle SQL Tuning Prerequisites

You are ready to begin this course when you can read a query, explain its intended result, and run it in an appropriate practice environment. You do not need to arrive as an experienced database administrator. The course develops tuning skills through observation and comparison: understand the request, examine how Oracle processes it, make a justified change, and check the result.

For Oracle AI Database 26ai, useful preparation falls into three groups: essential SQL knowledge, a small set of architecture concepts, and practical familiarity with your database client. Basic PL/SQL and operating-system knowledge help with later exercises, but advanced PL/SQL programming and UNIX administration are not universal entry requirements. Certification is not required either.

This lesson helps you identify gaps before they interrupt an experiment. It also separates application concepts from storage and administrative concepts. Knowing which schema owns a table, for example, answers a different question from knowing which tablespace stores its data. Keeping those distinctions clear makes later execution-plan analysis easier.

Essential SQL knowledge

Start with correctness. A faster query is not an improvement if it returns different customers, loses rows, introduces duplicates, or changes the meaning of an aggregate. Before comparing alternatives, describe the result in ordinary language. State which rows qualify, which columns are required, how duplicates should be treated, and whether the application requires an explicit ordering.

You should be comfortable reading the following constructs, even if you occasionally consult syntax documentation:

Consider a request for customers who placed orders during a particular month. Ask whether the output requires one row per customer or one row per order. These are different requests. A join that repeats a customer's name for every order may be correct for an order report and incorrect for a customer mailing list. Tuning begins after this ambiguity has been resolved.

Also review NULL behavior and datatypes. A missing value requires different reasoning from a known zero or a known string. When comparing a date column with a supplied value, identify the intended datatype and conversion rules. Do not assume that a display format describes how the database stores the value. These details help you recognize changes that appear harmless but alter a query's meaning.

An index is another concept to understand rather than prescribe automatically. Its presence does not guarantee faster execution, and it is not a substitute for an explicit ordering requirement. Later lessons examine access paths and the work they perform. At this stage, be able to identify the table and index objects involved without assuming that every full scan is a mistake.

If selection, joins, and aggregates are unfamiliar, review the Basic Structured Query Language course first. Use that course for SQL foundations. Oracle architecture, PL/SQL package calls, and diagnostic access are separate areas of preparation, not skills that a basic SQL lesson necessarily covers.

Recognize transactions and state-changing operations

Know the difference between inspecting data and changing it. Statements such as INSERT, UPDATE, and DELETE modify rows. COMMIT and ROLLBACK control transaction outcomes. Object-definition commands belong to another category, and you must not assume that every action can be undone by issuing ROLLBACK. Oracle DDL normally involves implicit commits.

For a tuning exercise, establish what the script is allowed to change before running it. A script may begin with a harmless query and later create an index or collect statistics. Read the complete script, including package calls. A familiar-looking command name is not enough to determine its effects. Ask for a practice copy when the intended experiment could affect a shared application.

Client settings matter as well. Learn whether your client commits automatically and how it handles disconnected sessions or failed statements. An experiment involving data changes should have an agreed initial state and a way to restore the practice data. Without that preparation, the second run may use different data from the first, making the comparison misleading.

Useful PL/SQL, client, and operating-system skills

Basic PL/SQL helps you recognize a block, a procedure call, and a package-qualified name. Later lessons may refer to packages such as DBMS_XPLAN or DBMS_STATS. You do not need to write a substantial application before studying them, but you should be able to distinguish a package call from an ordinary query and consult its documented parameters.

Keep four kinds of instructions separate: SQL statements, PL/SQL blocks, client commands, and operating-system commands. For example, a client command that saves output to a file is not a SQL statement sent to the database. A script delimiter accepted by SQL*Plus is not necessarily text that belongs in an application driver's SQL string. Use the execution mode intended by your client.

Choose a suitable SQL client already available in your environment. Practice opening a connection, running a selected statement, running a script, viewing errors, and saving output. Record the client and its relevant settings with your experiments. If a script fails, retain the complete error message and the statement that produced it rather than describing the problem only as a failed connection or a broken query.

Operating-system familiarity is useful for paths, scripts, and files. UNIX expertise is not required merely because the database server runs on a UNIX-like operating system. A learner can connect from an approved Windows client without administering the server. Access to server-side trace files is a separate permission and workflow issue; a path displayed in a diagnostic message may not be a file on your own computer.

Database, instance, schema, and container

Use these terms carefully when describing your environment. A database includes persistent storage; an instance provides the running memory and processes that access it. In a single-instance arrangement the distinction can be easy to overlook. Oracle Real Application Clusters can involve multiple instances accessing a database, so do not turn the common single-instance example into a universal rule.

A schema identifies a collection of objects owned by a database user. A tablespace concerns storage placement. Connecting as a user does not mean that all accessible objects belong to that user's schema, and a schema is not simply another name for a tablespace. When an example uses an unqualified table name, first establish which object that name resolves to in your session.

Oracle 26ai uses the multitenant architecture. Know the service and pluggable database, or PDB, intended for your practice account. A PDB contains schemas and their objects; it is not a separate operating-system instance. Application exercises should use the designated practice PDB rather than treating the CDB root as a general-purpose workspace. Oracle's CDB and PDB overview explains this relationship.

These distinctions have immediate diagnostic value. If an expected table is missing, verify the connection and object owner before changing the SQL. If two learners obtain different results, compare their service, schema, data, and session setup. A difference in environment can explain the observation without requiring an optimizer problem.

A compact storage refresher

The logical storage hierarchy is blocks, extents, segments, and tablespaces. An extent comprises blocks; a segment comprises extents and belongs to one tablespace. A segment's extents can occupy different datafiles within that tablespace, but an individual extent cannot span files.

A simple heap table can use one data segment. Partitioning, separate LOB storage, and deferred segment creation make “every table immediately has exactly one segment” an unreliable rule. For tuning, distinguish the logical table from the storage structures accessed by a particular operation.

Permanent tablespaces use datafiles; temporary tablespaces use tempfiles. Smallfile tablespaces can have multiple files, while a bigfile tablespace has one. Autoextend remains subject to limits and available capacity. Adding a file is therefore not a universal solution to growth.

SYSTEM and SYSAUX have internal roles. Use the assigned application tablespace for practice objects; do not assume a tablespace named USERS exists everywhere. Oracle's logical storage documentation supplies further detail. Storage administration is not an exercise in this lesson.

Metadata access and diagnostic permissions

The data dictionary describes database objects and their properties. SYS owns its base tables and user-accessible views; SYSTEM is an administrative account, not a co-owner of those base tables. Query supported views rather than modifying dictionary base tables directly.

The common view prefixes express different scopes: USER_ generally describes your own objects, ALL_ describes objects accessible to you, and DBA_ supplies broader information requiring appropriate privileges. Container scope also matters. An empty list of your tables does not establish that the entire database contains no tables. See Oracle's dictionary overview.

Metadata, optimizer statistics, and runtime measurements answer different questions. A column definition describes an object. Optimizer statistics help estimate data characteristics. Execution measurements describe observed work. Do not mistake an estimate for a measurement merely because both appear in a diagnostic report.

Later, DBMS_XPLAN.DISPLAY_CURSOR requires access to documented diagnostic views. Ask the administrator for the access needed by the exercise rather than treating SYSDBA as a prerequisite. Oracle documents these requirements in the DBMS_XPLAN security model. Optional advisor facilities also have environment and licensing requirements; they are not required just to begin this course.

Read-only checks for your practice connection

The following examples inspect session context and table metadata. They are illustrative statements, not recorded results from a tested course database. Run them only through your approved connection. They do not create sample objects, change storage, or grant diagnostic privileges.

SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user,
       SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS current_schema,
       SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name
FROM dual;

SELECT table_name, tablespace_name
FROM user_tables
ORDER BY table_name;

The first query distinguishes the session user, current schema, and container. CURRENT_SCHEMA affects name resolution; it does not itself grant additional privileges. Compare the returned values with the environment assigned to you. Oracle's SYS_CONTEXT reference describes the session attributes.

The second query lists tables owned by the current user. A newly provisioned practice account may return no rows. Tablespace metadata can also be null for some table organizations, including partitioned-table entries; that alone is not evidence of corruption. If an exercise expects sample tables, confirm that its setup has actually been provided. The HR schema is optional and should not be assumed installed, accessible, or unlocked.

Prepare to make repeatable comparisons

Keep a small experiment record containing the SQL text, input values, expected result, connection identity, client settings, and observations. Decide what “faster” means for the task: a shorter wait for the first rows, less time to fetch the complete result, or better throughput across repeated requests. A client displaying its first screen of rows does not establish that the entire result has been fetched.

For example, imagine comparing two versions of a report. One client stops after displaying a small page of results while another fetches everything. Their elapsed times answer different questions. Before attributing the difference to SQL, make the retrieval behavior comparable. Also record whether the data and other activity changed between runs.

Change one meaningful factor at a time where practical. Save the original statement and avoid overwriting the only copy of an earlier observation. If a result surprises you, write down what changed and what remained constant. The purpose is to build an explanation supported by evidence, not to produce a preferred access path at any cost.

Readiness self-check

Before proceeding, work through these questions with a query you understand:

  1. Can you explain its intended rows, columns, duplicates, and ordering without referring to performance?
  2. Can you identify its joins and describe how missing values affect its predicates?
  3. Do you know the practice service, container, schema, and permitted operations?
  4. Can you distinguish an inspection query from a command that changes data, objects, or statistics?
  5. Can you save the SQL, input values, error messages, and complete output needed for a comparison?
  6. Do you know which knowledge gaps require SQL review and which require help from the environment administrator?

A gap is a preparation task, not a reason to abandon the course. Review SQL fundamentals if the result is unclear. Check connection details if objects appear missing. Request the necessary diagnostic access if an approved example cannot read a required view. Then continue to course resources. Module 2 develops SQL execution, optimizer, and plan-reading concepts from this foundation.


SEMrush Software 2 SEMrush Banner 2