NTech IncSnowflake Partner

Home / Oracle DBA / Performance tuning

Oracle performance tuning

Oracle performance tuning consultants who find the real bottleneck

For teams with slow Oracle screens, overnight jobs that overrun, or queries that regressed after an upgrade or patch. NTech's Oracle tuning is led by a senior Oracle DBA with 20+ years in production. We analyze AWR, ASH and ADDM reports, wait events and execution plans, fix the top SQL and configuration problems, and hand you a ranked findings report with the measured baseline and results, as a one-off health check or ongoing through remote DBA support.

Quick answer

What do Oracle performance tuning consultants do?

Oracle performance tuning consultants find and fix the causes of a slow database using evidence: AWR, ASH and ADDM reports (or Statspack without the Diagnostics Pack), wait-event analysis and execution plans. Typical fixes are SQL rewrites, fresh optimizer statistics, better indexes, SQL plan baselines for regressed plans, and memory, redo or I/O changes, each measured before and after.

Last updated · NTech Inc

Who does the work: NTech's Oracle practice is led by a senior Oracle DBA with 20+ years supporting Oracle databases in production. That person reviews the evidence and the recommended fixes. Engineers in India support the analysis on a shift that overlaps your working day.

What we look at

Where Oracle performance problems come from

SQL and execution plans

Top SQL by elapsed time and logical reads, plan changes, bad join orders and missing or unused indexes.

Statistics

Stale or missing optimizer statistics, histograms, extended statistics and the gathering schedule.

Waits and contention

Lock waits, latch and mutex contention, hot blocks, sequence caching and RAC global cache waits.

Memory

SGA and PGA sizing, buffer cache efficiency and sort or hash spills to temp.

I/O and storage

Redo log sizing, I/O latency by file and ASM disk group balance.

Application patterns

Row-by-row processing, missing bind variables and chatty calls that tuning the database alone can't fix.

Method

How an Oracle tuning engagement runs

  1. Define the problem and the baseline

    Agree which screens, reports or jobs are slow, since when, and what "fast enough" means for the business. Pick AWR snapshot ranges (or Statspack snapshots) that cover a good period and a bad one.

  2. Check licensing, then collect evidence

    Confirm whether the Diagnostics and Tuning Packs are licensed before touching AWR, ASH or SQL Monitor. Collect AWR, ASH and ADDM reports or Statspack, SQL Monitor reports, OS CPU and I/O statistics, the alert log and the parameter file. Read-only access is enough for this step.

  3. Analyze DB time and wait events

    Break DB time into CPU and wait classes, identify the top wait events, and use ASH to tie them to sessions, SQL and objects. Check ADDM findings against the raw data rather than accepting them as given.

  4. Analyze the top SQL and its plans

    Rank SQL by elapsed time, CPU and logical reads. Use DBMS_XPLAN and AWR plan history to compare current and past plans, and check estimated against actual row counts to find where the optimizer is wrong.

  5. Fix and test

    Fresh or corrected statistics, new or changed indexes, SQL rewrites, SQL plan baselines through SQL Plan Management, parameter, memory or redo changes. Each fix is tested outside production first.

  6. Apply, measure and report

    Apply through your change process with a rollback step, measure the same workload again, and compare the results with the baseline in the findings report.

Diagnosis

Symptom, evidence and typical fix

Most Oracle performance problems fall into a small number of patterns. This table shows how we connect what users see to the evidence in the database and to the fix that usually follows.

SymptomEvidence we look forTypical fix
Query suddenly slow after a statistics job, patch or upgradeNew plan hash value in AWR plan history; estimated versus actual rows far apartCorrect statistics, then pin the known good plan with a SQL plan baseline
Screens slow, db file sequential read dominatesTop SQL doing many single-block index reads; nested loops over large row countsBetter index or join method so fewer rows are visited
Reports slow, db file scattered read or direct path readFull scans of large tables in top SQL by physical readsSelective indexes, partition pruning, or accept scans for batch work
Commits slow, application waits on saveslog file sync high; redo write latency in AWR; commit rate per secondBatch commits, faster redo storage, right-size redo logs
Users block each otherenq: TX - row lock contention; blocking sessions in ASHShorter transactions, application change to the locking pattern
High CPU with many sessions parsingHigh hard parse rate; library cache and cursor: pin S waitsBind variables in the application; cursor sharing as a stopgap
Inserts slow under concurrencybuffer busy waits, enq: SQ or RAC gc waits on the same blocksLarger sequence cache, hash-partitioned or reverse-key indexes
Heavy temp usage, slow sorts and hash joinsMulti-pass work areas; temp space in SQL MonitorPGA sizing and plan fixes
CPU saturated, nothing obvious waitingDB time mostly CPU; top SQL with very high logical reads per executionTune the top SQL before considering more cores

Tools

Evidence, not guesses, and no license surprises

Several Oracle tuning tools are separately licensed. We check what you own first, so a tuning review never creates a license problem.

Tool or methodWhat it showsLicensing
AWR, ASH, ADDMWorkload history, sampled session activity, automated findingsDiagnostics Pack, Enterprise Edition
SQL Monitor, SQL Tuning Advisor, SQL profilesLive plan execution, tuning recommendations, plan correctionsTuning Pack (requires the Diagnostics Pack)
SQL Plan ManagementAccepted plans per statement, protection from plan regressionsIncluded with Enterprise Edition
SQL Performance Analyzer, Database ReplayBefore-and-after comparison of a workload across a changeReal Application Testing option
Statspack, DBMS_XPLAN, dynamic performance viewsWorkload snapshots, execution plans, waits and statisticsIncluded

New to tuning? Read our database optimization guide.

Deliverables

What an Oracle health check delivers

A health check is a structured review of performance, configuration, backups, patch level, space and security. It ends with a report you can act on, whether or not we do the work.

Health check deliverables

  • Executive summary: the three to five issues that matter most
  • Top problem SQL with execution plans, root cause and recommended fix
  • Wait-event and DB time analysis for the agreed periods
  • Optimizer statistics review and a recommended gathering approach
  • Index review: missing, unused and duplicate indexes
  • Memory, redo, temp and I/O configuration findings
  • Initialization parameter review against your version and workload
  • Backup, recovery, patch level and security findings
  • Ranked list of fixes with effort, risk and expected effect
  • Measurements against the baseline for the changes applied

Engagement options

One-off health check or ongoing tuning

One-off health checkFixed-scope tuning projectOngoing via remote DBA
ScopeReview and findings reportFix a named problem, such as a batch window or a regressed releaseTuning as part of monthly Oracle administration
Best forA second opinion, or a database nobody has reviewed in a whileA known, urgent performance problemSystems where workload and code keep changing
Who applies fixesYour team, or us as a follow-onUs, through your change processUs, as part of normal change windows
Commercial modelFixed scopeFixed scope, by milestoneMonthly, with up to 24/7 coverage

The India team works a typical shift of 14:00 to 23:00 IST, overlapping 09:00 to 13:30 US Eastern, so questions are answered the same day. See remote Oracle DBA services and engagement models.

How to choose

When to tune, when to buy hardware, when to upgrade

Faster hardware and new versions are sometimes the right answer, but they are expensive ways to fix a bad execution plan. Oracle Enterprise Edition is usually licensed per processor core, so adding cores can raise license costs as well as hardware costs. This is the order we work in.

What the evidence showsDo this firstWhy
A few SQL statements account for most DB timeTune the SQLThe cheapest and fastest fix, and it often removes the need for hardware
CPU is saturated after the top SQL is tuned, and workload has grownAdd CPU, with the license impact costedThe database is doing necessary work; more capacity is justified
Redo or data file latency is high at moderate I/O volumeFix storageSlow storage hurts every query and commit, and SQL tuning can't fix it
Running an out-of-support release, or hitting a known optimizer bugPlan an upgrade, capturing plans firstBaselines captured before the upgrade protect against plan regressions after it
Row-by-row processing or no bind variablesChange the applicationDatabase settings can only partly compensate for the access pattern
Oracle license cost is the real problemAssess Oracle to PostgreSQL migrationTuning lowers resource use; it doesn't remove license fees

Case study

Query and index tuning at scale

Enterprise SaaS

Multi-tenant platform re-architecture

Duration
10 months
Team
7 senior engineers

A fast-growing B2B SaaS provider hit performance and scaling limits in its multi-tenant platform. The work started the way a tuning engagement does, with profiling and evidence, and combined structural changes with query and index tuning.

Approach

  • Architecture review and performance profiling
  • New data partitioning for tenant isolation
  • Caching layer and database query optimization
  • Monitoring and alerting

Outcomes

  • Faster API response times across endpoints
  • Lower database costs from query and index tuning
  • Capacity to support enterprise clients

Client names are withheld; full versions are available under NDA. See all case studies.

Contact us

Request an Oracle health check

Tell us what is slow and since when. A senior Oracle DBA will reply with what we would look at first.

  • A senior engineer reads every message
  • Reply within one business day
  • No obligation, and your details are used only to reply

Prefer the full form? Go to the contact page.

FAQ

Questions buyers ask us

What is an Oracle database health check?

A structured review of performance, configuration, backups, patch level, space and security, ending with a ranked list of risks and fixes with effort and expected effect.

Who does the tuning work?

NTech's Oracle work is led by a senior Oracle DBA with 20+ years supporting Oracle databases in production, who reviews the evidence and the recommended fixes. Engineers in India support the analysis on a shift that overlaps your working day.

Do we need the Diagnostics and Tuning Packs for performance tuning?

AWR, ASH and ADDM require the Diagnostics Pack, and SQL Monitor and SQL Tuning Advisor require the Tuning Pack, on Enterprise Edition. Without them, tuning uses Statspack, dictionary views and manual plan analysis.

What is the most common cause of Oracle performance problems?

A small number of SQL statements with poor execution plans, usually caused by missing or stale statistics, missing indexes or plan changes after an upgrade or patch.

How quickly can performance improve?

Fixes to a few problem SQL statements often bring the largest gains and can be applied quickly. Structural issues such as storage or application design take longer.

Why did performance change after an upgrade or patch?

Usually execution plans changed because of optimizer changes or new statistics. Comparing plans before and after and fixing the regressed statements resolves most cases.

What is a SQL plan baseline?

A SQL plan baseline is an Oracle SQL Plan Management feature that stores accepted execution plans for a statement, so the optimizer keeps using a known good plan after statistics, patches or upgrades change.

Should we buy faster hardware instead of tuning?

Tune first unless the evidence shows the database is doing necessary work at full capacity. Oracle Enterprise Edition is usually licensed per processor core, so extra cores can raise license costs as well as hardware costs.

Can you tune without changing production directly?

Yes. Evidence collection needs only read-only access, AWR data can be exported for offline analysis, and every fix is tested outside production and applied through your change process with a rollback step.

Next step

Tell us what you need to build or who you need to hire.

A 30-minute call with a senior architect. You leave with a scoped plan or a role profile, whether or not you work with us.