Why Study with PlanetCert?
The Latest Questions
Practice questions and exam topics aligned with the current exam objectives.
Detailed Explanations
Go beyond the answer. Master the material with comprehensive learning and professional explanations for every concept.
AI-Powered Insights
Personalized preparation guidance that adapts to your performance and identifies weak spots automatically.
Exam Information
Official specifications published by Oracle
Exam Format
Registration
Validity
Skills this practice test covers
- Diagnose a slow database from DB Time, wait events and dynamic performance views.
- Read AWR, ADDM, ASH and Statspack reports to find where database time goes.
- Find problem SQL with execution plans, SQL Trace and the optimizer trace.
- Stabilize SQL with optimizer statistics, the SQL Tuning Advisor and SQL plan baselines.
- Resolve shared pool, buffer cache and PGA problems, and configure ASMM or AMM.
- Compare performance before and after a change with SQL Performance Analyzer and Database Replay.
Explore related topics
1Z0-084 exam content
16 domains · 31 topics · 180 of 307 questions are written for 1Z0-084
Basic Tuning Methods and Diagnostics6 questions
This area sets the method used in every other one: tune in a planned order and measure first. Candidates read the time model and wait events, and diagnose problems with V$ views.
Skills tested
- Follow a tuning methodology from a measured goal to a verified fix.
- Use the time model to see how DB Time splits between CPU work and waits.
- Tell idle waits from non-idle waits such as db file sequential read.
- Diagnose a performance problem with dynamic performance (V$) views.
Questions by topic
- Diagnosing performance problems using V$ views6
Using Statspack12 questions
Statspack collects performance snapshots and reports on the change between two of them. Candidates install and configure it, then use its reports to diagnose problems.
Skills tested
- Install Statspack and schedule its snapshots.
- Diagnose a slowdown from a Statspack report that covers the problem period.
Questions by topic
- Installing and configuring Statspack6
- Diagnosing performance problem using Statspack6
Using Log and Trace Files to Monitor Performance
The alert log and trace files record events that metrics can miss. Candidates use both to monitor performance and to follow up errors.
Skills tested
- Find the alert log and trace files in the ADR.
- Spot alert log entries that point to a performance problem.
- Use a session trace file to see what one session did.
Using Metrics, Alerts and Baselines
This area is about early warning. Candidates set metric thresholds that raise alerts, and keep AWR baselines that show what normal performance looks like.
Skills tested
- Set warning and critical thresholds with DBMS_SERVER_ALERT.
- Create fixed baselines and baseline templates in AWR.
- Compare current metrics with a baseline to judge whether a change hurt performance.
Using AWR-Based Tools10 questions
AWR stores regular performance snapshots, and its tools turn them into findings. Candidates use ADDM, ASH reports and the automated maintenance tasks.
Skills tested
- Read ADDM findings and recommendations for a range of snapshots.
- Use an ASH report to find the sessions and SQL behind a short spike.
- Manage the automated maintenance tasks and their maintenance windows.
Questions by topic
- Diagnosing performance issues using ADDM5
- Diagnosing performance issues using ASH reports5
Performing Oracle Database Application Monitoring10 questions
Some problems belong to one application or one batch job. Candidates monitor database operations in real time and use services to measure performance per workload.
Skills tested
- Watch a long-running database operation with Real-Time Database Operation Monitoring.
- Mark a composite operation with DBMS_SQL_MONITOR so it can be monitored as one unit.
- Configure services so statistics can be read for each application.
Questions by topic
- Implementing Real-Time Database Operation Monitoring5
- Configuring and using services to monitor database Application performance5
Identifying Problem SQL Statements22 questions
This area moves from the instance to single statements. Candidates follow the phases of SQL processing, read execution plans and traces, and monitor adaptive plans and SQL plan directives.
Skills tested
- Explain the parse, execute and fetch phases of a SQL statement.
- Read an execution plan with EXPLAIN PLAN and DBMS_XPLAN.
- Find a poorly performing statement in SQL Trace output formatted with TKPROF.
- Monitor adaptive plans, automatic reoptimization and SQL plan directives.
Questions by topic
- Monitoring Adaptive / Dynamic Execution plans7
- Using formatted output of SQL Trace or the optimizer trace to identify poorly performing SQL statements5
- Interpreting execution plans5
- Monitoring Automatic Reoptimization and SQL Plan Directives5
Influencing the Optimizer12 questions
The optimizer chooses a plan from statistics and settings. Candidates explain how statistics change its choices and set the parameters that influence it.
Skills tested
- Explain how column statistics and histograms change the optimizer's row estimates.
- Set optimizer parameters for the system or a session, and judge their effect.
- Use a 10053 trace to see why the optimizer chose a plan.
Questions by topic
- Configuring parameters to influence the optimizer7
- Explain how statistics influence the optimizer5
Reducing the Cost of SQL Operations17 questions
The way data is stored changes how much work a statement does. Candidates shrink segments, manage row chaining and migration, choose index and table options, and configure compression.
Skills tested
- Compact a segment and lower its high water mark with ALTER TABLE SHRINK SPACE.
- Find chained and migrated rows and remove the cause.
- Choose index and table options, such as index-organized tables (IOTs), for a workload.
- Configure basic or advanced table compression.
Questions by topic
- Configuring Index and Table Performance Options7
- Configuring table compression5
- Shrinking Segments5
Using Real Application Testing6 questions
Real Application Testing shows the effect of a change before it reaches production. Candidates use SQL Performance Analyzer for single statements and Database Replay for a whole workload.
Skills tested
- Compare SQL performance before and after a change with SQL Performance Analyzer.
- Capture a production workload and replay it on a test system with Database Replay.
Questions by topic
- Use Database Replay to check impact of system change on performance6
SQL Performance Management19 questions
This area keeps SQL performance stable over time. Candidates manage optimizer statistics, use the Optimizer Statistics Advisor and the SQL tuning advisors, and control plans with SQL Plan Management.
Skills tested
- Gather and manage optimizer statistics with DBMS_STATS.
- Act on the findings of the Optimizer Statistics Advisor.
- Run the SQL Tuning Advisor and the SQL Access Advisor on problem statements.
- Load, accept and evolve SQL plan baselines with DBMS_SPM.
Questions by topic
- Use SQL Plan Management to tune SQL statements6
- Using the SQL Access and SQL Tuning advisors to Tune SQL statements7
- Managing optimizer statistics6
Tuning the Shared Pool10 questions
The shared pool holds parsed SQL and dictionary data. Candidates diagnose shared pool problems, such as too many hard parses, and manage the result cache.
Skills tested
- Trace parsing problems in the library cache to their cause.
- Diagnose dictionary cache and shared pool sizing problems.
- Manage and tune the result cache with DBMS_RESULT_CACHE.
Questions by topic
- Diagnosing and resolving performance problems related to the shared pool5
- Managing and Tuning the Result Cache5
Tuning the Buffer Cache26 questions
The buffer cache keeps data blocks in memory. Candidates fix buffer cache problems, diagnose database I/O, and configure big table caching and Database Smart Flash Cache.
Skills tested
- Diagnose buffer busy waits and free buffer waits.
- Read I/O statistics to diagnose database I/O issues.
- Configure big table caching with DB_BIG_TABLE_CACHE_PERCENT_TARGET.
- Configure Flash Cache with DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE.
Questions by topic
- Diagnosing and resolving performance problems related to the buffer cache6
- Diagnose database I/O issues8
- Configuring Big Table Caching7
- Configuring Flash Cache5
Tuning the PGA6 questions
The PGA holds private memory for work such as sorts and hash joins. Candidates resolve PGA problems and the temporary tablespace issues that follow when that work spills to disk.
Skills tested
- Diagnose PGA memory problems for sorts and hash joins.
- Resolve temporary tablespace problems caused by large sorts.
Questions by topic
- Diagnosing and resolving performance issues related to PGA6
Using Automatic Memory Management12 questions
Oracle can size its memory areas itself. Candidates configure Automatic Shared Memory Management for the SGA, and Automatic Memory Management for the SGA and PGA together.
Skills tested
- Configure ASMM with SGA_TARGET.
- Configure AMM with MEMORY_TARGET and MEMORY_MAX_TARGET.
Questions by topic
- Configuring Automatic Memory Management6
- Configuring Automatic Shared Memory Management6
Using In-Memory Features12 questions
The In-Memory Column Store keeps copies of tables in a columnar format for fast analytic queries. Candidates configure it and use it with other features, such as partitioning and RAC.
Skills tested
- Size the In-Memory Column Store with INMEMORY_SIZE.
- Mark tables and columns for it with the INMEMORY clause.
- Use In-Memory with partitioning and with RAC.
Questions by topic
- Configure and Use in memory columnn store to improve SQL performance6
- Use in memnory column store with other database features.6
Requirements
- No formal prerequisites: Oracle lists recommended experience, not a required exam.
- Recommended: experience administering Oracle Database 19c and a good grasp of its architecture.
- Recommended: working knowledge of SQL and PL/SQL, execution plans and optimizer statistics.
- Recommended: the administration skills of Oracle Database Administration I and Oracle Database Administration II.
About the 1Z0-084 exam
1Z0-084 checks that a database administrator can apply a tuning method to Oracle Database 19c: measure where time goes, find the cause, and fix the instance or the SQL. Its topic areas run from wait events, Statspack and AWR-based tools to the optimizer, SQL Plan Management, the memory caches and In-Memory features. Oracle expects deep knowledge of the database architecture, daily administration, the advisors, and SQL execution plans and statistics. Passing earns the Oracle Certified Professional, Oracle Database 19c: Performance Management and Tuning certification.
The PlanetCert practice test gives the most questions to Tuning the Buffer Cache, Identifying Problem SQL Statements and SQL Performance Management. The explanations link each symptom to a tool, for example free buffer waits to the buffer cache, or a changed plan to SQL Plan Management. Flashcard Mode suits the many views, packages and parameters, such as DBMS_XPLAN, DBMS_SPM and MEMORY_TARGET. Brainy AI Assistant can explain an execution plan line by line, or say which wait event points to an I/O problem.
How to earn the certification
- Earns
- Oracle Certified Professional, Oracle Database 19c: Performance Management and TuningProfessional
Oracle sets no expiry date for this certification.
- Before
- 1z0-083 Oracle Database Administration II
The second 19c administration exam, which goes deeper into running the database.
- 1z0-083 Oracle Database Administration II
How to prepare for 1Z0-084
- 1
Set up a 19c tuning lab
Install Oracle Database 19c in a virtual machine, load a sample schema, and take AWR snapshots around a test workload. Every later step uses this lab.
- 2
Read reports from DB Time down
Generate an AWR report and a Statspack report for the same period. Start at DB Time and the top wait events, then follow them to the SQL and segments behind them.
- 3
Starve each memory area in turn
Shrink the shared pool, the buffer cache and the PGA one at a time and watch which waits appear. Then let ASMM or AMM size them. Buffer cache tuning has the most practice questions.
- 4
Trace and pin one slow query
Run SQL Trace and a 10053 trace on a slow statement, read its plan with DBMS_XPLAN, and fix the plan with a SQL plan baseline. Then test the change with SQL Performance Analyzer.
- 5
Drill each area, then time it
Work through the 1Z0-084 practice test one topic area at a time in Study Mode, then take a full run in Timed Mode. Ask Brainy AI Assistant about any view, package or wait event still unclear.
Start a timed 1Z0-084run
Oracle's preparation for this exam is the learning path Earn the Oracle Database 19c Perf Mgmt & Tuning Professional Credential in Oracle MyLearn, together with hands-on lab time.
Who 1Z0-084 is for
- Senior DBAs who own the performance of busy Oracle Database 19c systems.
- Performance specialists who are called in when queries or batch jobs slow down.
- Database architects who plan memory, storage and SQL design with performance in mind.
- Consultants who review Oracle databases with AWR, ADDM and ASH reports.
Typical job titles: Database administrator · Senior DBA · Performance tuning specialist · Database architect · Database consultant
1Z0-084 glossary
18 terms from the exam objectives and the vendor's official material, each defined in a sentence.
- Active Session History (ASH)
- Frequent samples of the active sessions, used to see what sessions were doing or waiting on during a short problem.
- Adaptive plans
- Execution plans that can change a join method while the statement runs, based on the number of rows actually seen.
- ADDM
- Automatic Database Diagnostic Monitor: the advisor that analyzes AWR snapshots and reports the main causes of lost database time, with recommendations.
- Automatic Memory Management
- Oracle sizing the SGA and the PGA together from one target, which MEMORY_TARGET sets.
- Automatic Workload Repository (AWR)
- The built-in store of regular performance snapshots that ADDM, AWR reports and baselines all read from.
- Big table caching
- A setting that lets full scans of large tables use part of the buffer cache; DB_BIG_TABLE_CACHE_PERCENT_TARGET sets the size of that part.
- Database Replay
- A Real Application Testing tool that captures a production workload and replays it on a test system to show the effect of a change.
- Database Smart Flash Cache
- An extension of the buffer cache on flash storage, which holds clean blocks that no longer fit in memory.
- DB Time
- The total time that user sessions spent in database calls, on the CPU or in non-idle waits. It is the main measure of the time model.
- High water mark
- The boundary between used and never-used blocks in a segment. Full scans read up to it, so shrinking a segment can make them faster.
- Histograms
- Column statistics that describe unevenly spread values, so the optimizer can estimate rows for common and rare values better.
- In-Memory Column Store
- An area of the SGA that keeps copies of tables in a columnar format, which speeds up analytic queries.
- Result Cache
- An area of the shared pool that stores the results of queries or functions, so repeated calls can skip the work.
- SQL Performance Analyzer
- A Real Application Testing tool that runs a set of SQL statements before and after a change and compares their performance.
- SQL Plan Directives
- Notes the optimizer keeps after a misestimate, so that next time it collects extra information, such as dynamic statistics.
- SQL Plan Management
- A feature that keeps accepted plans as SQL plan baselines, so a new plan is used only after it is verified to perform well.
- Statspack
- A snapshot-based reporting tool that the DBA installs; it compares two points in time, much like an AWR report.
- Wait events
- Named reasons why a session had to wait, such as db file sequential read for a single-block read.