Oracle 1Z0-084 Database Performance Tuning Guide

What Does the Oracle 1Z0-084 Exam Cover?

Oracle 1Z0-084, “Oracle Database 19c Performance Management and Tuning,” is a 55-question, 90-minute multiple-choice exam requiring 60 percent to pass, priced at $245 USD. It spans sixteen topic areas covering diagnostics, AWR-based tools, SQL identification and tuning, optimiser influence, memory tuning, Real Application Testing, and In-Memory features.

No published weightings

Oracle does not publish weightings for this exam, which changes how you plan. With sixteen areas across 55 questions, most topics account for only three or four questions – so breadth beats depth, and a topic you skipped entirely is a real risk at a 60 percent threshold.

Thinking in clusters

The sixteen areas group naturally into five clusters, and thinking in clusters is far more useful than working through a flat list:

• Diagnostics and instrumentation – tuning methods, Statspack, log and trace files, metrics, alerts, baselines
• AWR-based analysis – AWR reports, ASH, ADDM, application monitoring
• SQL tuning – identifying problem statements, influencing the optimiser, reducing operation cost, SQL performance management
• Memory tuning – shared pool, buffer cache, PGA, automatic memory management
• Advanced features – Real Application Testing, In-Memory

Pacing and question style

Roughly 98 seconds per question means the exam rewards recognition over derivation. You should be able to look at a described symptom and know which tool investigates it. Oracle’s Database Tuning Guide is the authoritative reference for the whole syllabus.

Which Tuning Methodology Does the Exam Assume?

1Z0-084 assumes a top-down, evidence-led methodology: measure where time is actually being spent, address the largest contributor, then measure again. The exam consistently rewards diagnosis before action and penalises the instinct to apply a known fix to an undiagnosed symptom.

Wait event analysis

Wait event analysis is the foundation. Oracle instruments where sessions spend time, and the resulting wait events tell you what the database was waiting on rather than what you assume it was waiting on. A system that appears slow may be waiting on I/O, on a latch, on a lock held by another session, or on nothing at all because it is CPU-bound – and each demands a completely different response.

Wait classes to memorise

The classification worth memorising is the wait class: user I/O, system I/O, concurrency, configuration, commit, application, and network. Classifying a wait narrows the investigation immediately, and questions often present a wait profile and ask what it indicates.

Diagnosing scope

The second principle is scope. A problem affecting one SQL statement is not the same as one affecting an entire instance, and the tools differ accordingly – SQL-level tools for the former, AWR and ADDM for the latter. Choosing an instance-wide tool to diagnose a single slow query, or vice versa, is a reliable distractor.

Statspack, the older alternative

Statspack appears in the syllabus as the older alternative to AWR. Know that it provides similar snapshot-based analysis without requiring the Diagnostics Pack licence, which is why it persists in environments that are not licensed for AWR.

How Do AWR, ASH, and ADDM Differ?

AWR, ASH, and ADDM are related but answer different questions. AWR stores periodic snapshots of cumulative statistics; ASH samples active session state every second; ADDM analyses AWR data automatically and reports findings with recommendations. Knowing which to reach for is among the most examinable skills in the syllabus.

AWR: the historical baseline

AWR is the historical baseline. Snapshots are taken on a schedule and an AWR report compares two of them, showing what changed over that interval – top wait events, top SQL, load profile, and instance efficiency. It answers “what was happening between 2pm and 3pm” but cannot resolve finer detail than the snapshot interval.

ASH: sampling active sessions

ASH fills exactly that gap. By sampling active sessions every second it captures short-lived problems that a snapshot comparison averages away – the four-minute spike inside a one-hour AWR window. When a question describes a brief, transient stall, ASH is almost always the answer.

ADDM: automated diagnosis

ADDM is the automated layer. It reads AWR data, identifies the largest contributors to database time, and issues findings with recommendations and an estimated benefit. The exam expects you to know that ADDM findings are ranked by impact and that its recommendations are advisory rather than obligatory.

The practical diagnostic sequence

The practical sequence – and a defensible answer to many scenario questions – is ADDM first for a fast overview, AWR for detail across an interval, and ASH when the problem is too short-lived for either.

What Role Do Metrics, Alerts, and Baselines Play?

Metrics, alerts, and baselines provide the reference point that makes performance judgements meaningful. Without a baseline, no metric can be called good or bad – the exam repeatedly tests the idea that performance is relative to normal, not to an absolute number.

Moving versus fixed baselines

A moving window baseline captures typical activity over a rolling period, while a fixed baseline preserves a specific interval you deliberately chose – a known-good period, or a peak processing window worth comparing against later. Understand that a baseline’s purpose is comparison, so preserving a representative period is what makes it useful.

Adaptive thresholds

Adaptive thresholds follow directly. Rather than alerting when a metric crosses a fixed value, adaptive thresholds derive alert levels from baseline statistics, so an alert fires when behaviour is abnormal for that workload at that time. This handles the common case where 2am batch activity and 2pm interactive load have entirely different normal ranges.

Retention and baseline settings

Know also that AWR retains snapshots for a configurable period and that baselines are excluded from automatic purging. That detail explains why a preserved baseline remains available for comparison long after ordinary snapshots have aged out.

How Do You Identify Problem SQL Statements?

Identifying problem SQL means finding the statements consuming the most resource, not the ones that feel slow. The exam tests ranking by resource consumption, reading execution plans, and distinguishing a statement that is individually expensive from one that is cheap but executed enormously often.

Resource use versus elapsed time

That distinction is the domain’s central insight. A query taking ten seconds and running once an hour costs less than a query taking fifty milliseconds and running a million times. AWR’s top SQL sections rank by total resource consumed rather than by per-execution cost, which is why a seemingly trivial statement can dominate a report.

Reading execution plans

Execution plan reading is the core technical skill. You need to interpret access paths – full table scan versus index scan and their variants – join methods, and cardinality estimates. The single most valuable diagnostic habit is comparing estimated rows against actual rows: a large discrepancy means the optimiser is working from bad information, and that misestimate is usually the real problem rather than the plan it produced.

Current versus actual plans

Know the difference between the plan the optimiser would choose now and the plan actually used at execution, since statistics or bind values may have changed since. Questions frequently hinge on that gap.

What Does It Mean to Influence the Optimizer?

Influencing the optimiser means changing the information or constraints it works from so it makes better choices – through statistics, hints, SQL profiles, and plan baselines. The exam strongly prefers approaches that improve the optimiser’s information over those that override its judgement.

Statistics first

Statistics come first and matter most. The cost-based optimiser chooses plans using object statistics, and stale or missing statistics produce poor plans no matter what else is configured. Understand what column statistics, histograms, and extended statistics contribute – histograms in particular matter for skewed data, where an average-based estimate badly misrepresents a specific value.

Hints: the blunt instrument

Hints are the blunt instrument. They force a decision, which fixes today’s problem and freezes the plan against tomorrow’s better option as data volumes change. The exam’s position is that a hint is a last resort after statistics and structure have been addressed.

SQL plan management

SQL plan management is the more sophisticated middle ground. A plan baseline records accepted plans for a statement so the optimiser will not use a new plan until it has been verified as at least as good. That directly addresses plan regression – the case where an upgrade or statistics refresh silently degrades a previously fine query. Recognising plan baselines as the answer to regression risk is a reliable exam point. The SQL Tuning Guide covers baselines and profiles in detail.

Which Techniques Reduce the Cost of SQL Operations?

Reducing SQL cost covers access path selection, join optimisation, sorting, partitioning, and result caching. The unifying principle is reducing the volume of data touched and the work performed per row – and the exam expects reasoning from that principle rather than a checklist.

Access path choice

Access path choice is the most examinable topic and is more nuanced than intuition suggests. An index is not automatically better than a full table scan: retrieving a large fraction of a table via an index means many single-block reads plus table access per row, which is frequently slower than one efficient multi-block scan. The examinable variable is selectivity – indexes win when a small proportion of rows is returned.

Join method selection

Join method selection follows similar logic. Nested loops suit small driving row sources with an indexed inner side; hash joins suit large unindexed joins; sort-merge suits pre-sorted inputs and non-equality conditions. Questions describe data volumes and expect you to match the method.

Sorting and PGA spills

Sorting deserves attention because it is expensive and often avoidable. Sorts spilling from PGA memory to temporary segments are dramatically slower, which links this topic directly to PGA tuning. Partitioning reduces cost through pruning – a filter on the partition key lets the optimiser skip partitions entirely – and result caching eliminates repeated work for queries against stable data.

How Are the Shared Pool, Buffer Cache, and PGA Tuned?

Memory tuning covers three structures with distinct purposes. The shared pool caches parsed SQL and dictionary information, the buffer cache holds data blocks read from disk, and the PGA provides per-session working memory for sorts and hash operations. Each has its own symptoms and its own fix.

Shared pool: hard parsing

Shared pool problems usually appear as excessive hard parsing. Every hard parse consumes CPU and latches, and the classic cause is literal values in SQL producing a distinct statement per execution rather than one reusable statement with bind variables. The correct answer to that scenario is bind variables, not a larger shared pool – enlarging the pool caches more unshareable statements without reducing parsing.

Buffer cache tuning

Buffer cache tuning targets physical reads. A low hit ratio means blocks are being read from disk repeatedly, though the exam is careful here: a high hit ratio does not prove health, because an inefficient query performing enormous logical reads can show an excellent ratio while performing terribly. Ratios are a starting point, never a conclusion.

PGA tuning

PGA tuning is about keeping sorts and hash joins in memory. Operations that exceed available PGA spill to temporary tablespace, and the resulting disk activity is far slower. Understand the optimal, one-pass, and multi-pass distinction – multi-pass operations indicate serious memory shortage.

Automatic memory management ties them together by letting Oracle distribute memory between components dynamically. Know what the automatic parameters govern and that manual sizing remains available where a workload has known characteristics that automation handles poorly.

Where Do Real Application Testing and In-Memory Fit?

Real Application Testing and In-Memory are the syllabus’s advanced features. RAT validates the performance impact of a change before production; In-Memory accelerates analytic queries through a columnar memory format. Both are conceptually examined rather than tested at configuration depth.

Database Replay and SPA

Real Application Testing has two components with different purposes. Database Replay captures real production workload and replays it against a changed system, testing the whole concurrent workload including contention. SQL Performance Analyzer works at statement level, comparing execution before and after a change to identify regressions specifically.

When to use each

The examinable distinction: use SPA when you need to know which statements a change would regress, and Database Replay when you need to know how the system behaves as a whole under realistic concurrency. Together they address the fundamental problem that a change tested on synthetic data may behave entirely differently on production workload.

Database In-Memory

In-Memory stores selected tables in a columnar format in memory alongside the normal row format. The dual-format architecture is the concept to hold: analytic queries scanning few columns across many rows benefit enormously from columnar access, while transactional queries retrieving whole rows continue using the row store. Know that population is selective – you choose which objects to place in memory – and that it accelerates queries rather than changing results. The Database In-Memory Guide covers population and query behaviour.

Who Should Pursue the 1Z0-084 Credential?

1Z0-084 suits practising Oracle DBAs responsible for performance, database engineers supporting large transactional or analytic systems, and consultants who are called in when a system is slow. It is a specialist credential assuming existing administration experience rather than an entry point.

Where the value concentrates

The value is concentrated in a specific professional situation: being the person who can explain why a system is slow with evidence rather than opinion. Performance problems attract confident guesses, and a DBA who can produce an AWR report, point to the dominant wait event, and show the plan change that caused it occupies a different position from one who suggests adding an index.

For engineers from other platforms

For engineers moving from other database platforms, the exam maps familiar concepts onto Oracle’s specific instrumentation. The underlying ideas – bad estimates cause bad plans, memory pressure causes spilling, ratios mislead – transfer from any mature database. What is Oracle-specific is the toolset, and that is precisely what the syllabus covers.

Where it fits in Oracle’s tracks

Within Oracle’s certification structure this sits alongside the administration track as a specialisation rather than a prerequisite step. Practitioners working across Oracle’s broader portfolio often pair it with cloud and development credentials such as the 1Z0-1127-24 OCI path or the 1Z0-909 MySQL developer route, depending on where their estate sits. Current paths are listed on Oracle’s certification portal.

How Should You Prepare for 1Z0-084?

Eight to ten weeks at six to eight hours per week suits practising DBAs. Effective 1Z0-084 preparation requires a database you can deliberately make slow, because the exam presents symptoms and asks for causes – a pattern you learn fastest by creating the symptoms yourself.

1. Weeks one to two – methodology and diagnostics. Learn the wait event model and wait classes. Generate load, then read the resulting wait profile and predict the bottleneck before confirming it.
2. Weeks three to four – AWR tooling. Generate AWR reports across intervals and learn to read them top-down. Create a short spike and confirm that ASH catches what AWR averaged away. Run ADDM and evaluate its findings critically.
3. Weeks five to six – SQL tuning. Read execution plans daily, always comparing estimated to actual rows. Delete statistics to see the damage, gather them to see the recovery, then create a plan baseline and observe how it blocks a new plan.
4. Week seven – memory tuning. Cause hard parsing with literals and fix it with binds. Undersize the PGA to force a multi-pass sort, then resize and compare.
5. Weeks eight to ten – advanced features and review. Explore SQL Performance Analyzer and In-Memory population, then move to timed practice across all sixteen areas.

Audit your coverage explicitly

Because no weightings are published, audit your coverage explicitly before booking. List all sixteen areas, rate your confidence in each, and spend remaining time on the weakest – with only three or four questions per area, two blind spots can consume the entire margin above 60 percent. Timed work through the 1Z0-084 practice exam questions exposes which areas you have been avoiding.

Frequently Asked Questions

How many questions are on the 1Z0-084 exam?

The exam contains 55 multiple-choice questions to be completed in 90 minutes, allowing roughly 98 seconds per question. The pace suits recognition rather than lengthy derivation.

What is the passing score for 1Z0-084?

You need 60 percent, which means approximately 33 of the 55 questions correct. There is no penalty for incorrect answers, so answer everything.

How much does the Oracle Performance Tuning exam cost?

The exam fee is $245 USD, varying by region. Oracle periodically offers discounted vouchers through its learning subscriptions.

Are there prerequisites for 1Z0-084?

No formal prerequisites apply, but the exam assumes working Oracle Database administration experience. It is a specialisation rather than an entry-level credential.

What is the difference between AWR and ASH?

AWR takes periodic snapshots of cumulative statistics and compares two points in time. ASH samples active sessions every second, so it captures short-lived spikes that an AWR interval would average away.

When should I use a hint instead of gathering statistics?

Rarely. The exam favours improving the optimiser’s information through accurate statistics over forcing a decision with a hint. Hints freeze a plan and prevent adaptation as data changes, so they are a last resort.

Does a high buffer cache hit ratio mean the database is healthy?

No. An inefficient query performing enormous logical reads can produce an excellent hit ratio while performing terribly. Ratios are a starting point for investigation, never a conclusion.

What causes excessive hard parsing?

Most commonly, literal values in SQL producing a distinct statement per execution instead of one reusable statement. The fix is bind variables, not a larger shared pool, which would simply cache more unshareable statements.

What is a SQL plan baseline?

A recorded set of accepted execution plans for a statement. The optimiser will not use a new plan until it has been verified as at least as good, which protects against plan regression after upgrades or statistics changes.

How long should I study for 1Z0-084?

Eight to ten weeks at six to eight hours per week is realistic for practising DBAs. Because no domain weightings are published, weight your time toward whichever of the sixteen areas you rate weakest.

Conclusion

Oracle 1Z0-084 measures diagnostic discipline more than tuning knowledge. Its sixteen areas all serve one loop: instrument the system, identify where time is actually spent, address the largest contributor, and measure again. Candidates who arrive with a catalogue of fixes but no method struggle, because the exam consistently asks what you would investigate rather than what you would change.

Three ideas carry disproportionate weight. Wait events tell you what the database was actually waiting on. Estimated-versus-actual row counts reveal when the optimiser is working from bad information. And ratios describe activity without proving health.

Prepare on a database you are free to break. Delete statistics and watch the plans degrade, flood the shared pool with literals, starve the PGA until sorts spill. The exam describes exactly those symptoms – and having produced them yourself is the difference between recognising the cause and guessing at it.