[Q31-Q54] Try 1z1-084 Free Now! Real Exam Question Answers Updated [Jan 31, 2026]

NO.31 You manage a 19c database with default optimizer settings.
This statement is used extensively as subquery in the application queries:
SELECT city_id FROM sh2.sales WHERE city_id=:Bl
You notice the performance of these queries is often poor and, therefore, execute:
SELECT city_id,COUNT(*) FROM sh2.sales GROUP BY city_id;
Examine the results:

There is no index on the CITY_ID column.
Which two options improve the performance?

Generate frequency histograms on the CITY__ID column.

Create an index on the CITY IP column.

Use a SQL Profile to enforce the appropriate plan.

Force the subquery to use dynamic sampling.

Activate the adaptive plans.

NO.32 Which three statements are true about tuning dimensions and details of v$sys_time_model and DB time?

Statspack cannot account for high CPU time when CPU TIME is a Top 10 event in DB time. When CPU time is high, SQL tuning may improve performance.

Systems in which CPU time is dominant need more tuning that those in which WAIT TIME is dominant.

The proportion of WAIT TIME to CPU TIME always increases with increased system load.

When WAIT TIME is high, instance tuning may improve performance.

Parse Time Elapsed accounts for successful soft and hard parse operations only.

DB Time accounts for all time used by background processes and user sessions.

NO.33 This error occurred more than four hours ago in the database:
ORA-04036 PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
You want to know which process and query were at fault.
Which two views should you use for this purpose?

DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_SQLSTAT

DBA_HIST_SQLTEXT

DBA_HIST_PGASTAT

DBA_HIST_PROCESS_MEM_SUMMARY

NO.34 Examine this statement and output:

Which two situations can trigger this error?

The user lacks the required privileges to execute the DBMS WORKLOAD CAPTURE package or the directory.

There is a file in the capture directory.

The syntax is incomplete.

The capture directory is part of the root file system.

The instance is unable to access the capture directory.

NO.35 Which two Oracle Database features use database services?

Oracle Automatic Reoptimization

Database Resource Manager

Oracle SQL Performance Management

Oracle SQL Tuning Advisor

Oracle Scheduler

NO.36 You need to transport performance data from a Standard Edition to an Enterprise Edition database. What is the recommended method to do this?

Export the data by using expdp from Statspack and import it by using $ORACLE_HOME/rdbms/admin
/awrload into the AWR repository.

Export the data by using expdp from the ftatspack repository and import it by using impdp into the AWR repository.

Export the data by using the expdp utility and parameter file spuexp.par from the Statspack repository and import it by using impdp into Export the data by using expdp from the Statspack repository and import it by using impdp into the AWR repository.

Export the data by using the exp utility and parameter file spuexp.par from the Statspack repository and import it by using imp into a dedicated Statspack schema on the destination.

NO.37 The CURS0R_SHARING and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameters are set to default. The top five wait events in an awr report are due to a large number of hard parses because of several almost identical SQL statements.
Which two actions could reduce the number of hard parses?

Create the KEEP cache and cache tables accessed by the SQL statements.

Create the RECYCLE cache and cache tables accessed by the SQL statements.

Increase the size of the library cache.

Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE.

Set the CURSOR_SHARING parameter to FORCE.

NO.38 Which two options are part of a Soft Parse operation?

SQL Row Source Generation

SQL Optimization

Semantic Check

Shared Pool Memory Allocation

Syntax Check

NO.39 Which two statements are true about the use and monitoring of Buffer Cache Hit ratios and their value in tuning Database I/O performance?

The performance of workloads that primarily generate full table scans and fast full index scans are always affected by the cache hit ratio.

A 99% cache hit ratio can be observed for database instances which have very poor I/O performance.

The buffer cache advisory view v$db_cache_advice provides advice on cache hit ratios appropriate for the instance workload.

Both the RECYCLE and KEEP buffer caches should always have a very high cache hit ratio.

A 60% cache hit ratio can be observed for database instances which have very good I/O performance.

NO.40 You need to transport performance data from a Standard Edition to an Enterprise Edition database. What is the recommended method to do this?

Export the data by using expdp from Statspack and import it by using
$ORACLE_HOME/rdbms/admin/awrload into the AWRrepository.

Export the data by using expdp from the ftatspack repository and import it by using impdp into the AWR repository.

Export the data by using the expdp utility and parameter file spuexp.par from the Statspack repository and import it by using impdp into Export the data by using expdp from the Statspack repository and import it by using impdp into the AWR repository.

Export the data by using the exp utility and parameter file spuexp.par from the Statspack repository and import it by using imp into a dedicated Statspack schema on the destination.

NO.41 Which two statements are true about session wait information contained in v$session or v$session_wait?

Rows for sessions displaying WAITED UNKNOWN TIME in the STATE column indicate that the session is still waiting.

Rows for sessions that are currently waiting have a wait time of 0.

Rows for sessions that are not waiting might contain the actual wait time for the last event for which they waited.

Rows for sessions that are currently waiting have their wait time incremented every microsecond.

Rows for sessions that are not waiting always contain the total wait time since the session started.

NO.42 Which two statements are true about space usage in temporary tablespaces?

When a global temporary table instantiation is too large to fit in memory, space is allocated in a temporary tablespace.

Temporary tablespaces setting Includes quotas to limit temporary space used by a session for that Temporary tablespace.

A sort will fail if a sort to disk requires more disk space and no additional extent can be found/allocated in/for the sort segment.

When a session consumes all temporary tablespace storage, then the session would hang until the temporary space used by that session is cleared.

Lack of temporary tablespace space for sort operations can be prevented by using temporary tablespace groups.

NO.43 Which two statements are true about Data Pump import for objects that used the in Memory (IM) column store in their source database?

It always gives preference to the IM column store clause defined at the tablespace level over table-level definitions.

It must always transports existing INMEMORY attributes.

Its INMEM0RY_CLAUSE of the Data Pump Export allows modifications to IM column store clause of a table with existing INMEMORY setting.

Its TRANSFORM clause can be used to add the INMEMORV clause to exported tables that lack them.

It ignores the IM column store clause of the exporting objects.

It can generates the INMEMORY clause that matches the table settings at export time.

NO.44 Which two actions can cause invalidation or loss of one or more results in the SQL Query Result Cache?

One or more results were aged out of the result cache.

result_cache_max_size is set dynamically to 0.

A request was made by the RCBG background of a non-RAC database.

A request was made by the RCBG background process in a physical standby database that is opened read only and whose primary has a result cache.

Decreasing the value set for RESULT_CACHE_REMOTE_EXPIRATION.

NO.45 Which three statements are true about server-generated alerts?

They are notifications from the Oracle Database Server of an existing or impending problem.

They provide notifications but never any suggestions for correcting the identified problems.

They are logged in the alert log.

They can be viewed only from the Cloud Control Database home page.

Their threshold settings can be modified by using DBMS_SERVER_ALERT.

They may contain suggestions for correcting the identified problems.

Server-generated alerts in Oracle Database are designed to notify DBAs and other administrators about issues within the database environment. These alerts can be triggered by a variety of conditions, including threshold-based metrics and specific events such as ORA- error messages. Here’s how these options align with the statements provided:
* A (True):Server-generated alerts are indeed notifications from the Oracle Database Server that highlight existing or impending issues. These alerts are part of Oracle’s proactive management capabilities, designed to inform administrators about potential problems before they escalate.
* C (True):These alerts are logged in the alert log of the Oracle Database. The alert log is a crucial diagnostic tool that records major events and changes in the database, including server-generated alerts.
This log is often the first place DBAs look when troubleshooting database issues.
* F (True):Server-generated alerts may include suggestions for correcting identified problems. Oracle Database often provides actionable advice within these alerts to assist in resolving issues more efficiently. These suggestions can range from adjusting configuration parameters to performing specific maintenance tasks.
Options B, D, and E do not accurately describe server-generated alerts:
* B (False):While the statement might have been true in some contexts, Oracle’s server-generated alerts often include corrective suggestions, making this statement incorrect.
* D (False):Server-generated alerts can be viewed from various interfaces, not just the Cloud Control Database home page. They are accessible through Enterprise Manager, SQL Developer, and directly within the database alert log, among other tools.
* E (False):While it’s true that threshold settings for some alerts can be modified, the method specified, usingDBMS_SERVER_ALERT, is not correct. Threshold settings are typically adjusted through Enterprise Manager or by modifying specific initialization parameters directly.
References:
* Oracle Database Documentation:Oracle Database 19c: Performance Management and Tuning
* Oracle Base: Alert Log and Trace Files
* Oracle Support:Understanding and Managing Server-Generated Alerts

NO.46 You execute the following:
EXECUTE DBMS_AuTO_TASK_ADMIN.DISABLE;
Which advisor remains enabled?

Automatic SQL Tuning

SQL Plan Management Evolve Advisor

Optimizer Statistics Advisor

Automatic Optimizer Statistics Collection

Automatic Segment Advisor

NO.47 You are informed that the RMAN session that is performing the database duplication is much slower than usual. You want to know the approximate time when the rman operation will be completed.
Which view has this information?

V$SESSION

V$SESSTAT

V$RMAN_BACKUP_JOB_DETAILS

V$SESSION_LONGOPS

NO.48 Database performance degraded between 23:15 and 23:30 for the last three nights. The awr snapshot interval is one hour. The AODM report contains nothing about this performance problem.
With which tool can you further analyze this problem?

SQL Performance Analyzer

AWR Compare Periods report

SQL Tuning Advisor

Active Session History report

NO.49 Which two statements are true about space usage in temporary tablespaces?

When a global temporary table instantiation is too large to fit in memory, space is allocated in a temporary tablespace.

Temporary tablespaces setting Includes quotas to limit temporary space used by a session for that Temporary tablespace.

A sort will fail if a sort to disk requires more disk space and no additional extent can be found/allocated in/for the sort segment.

When a session consumes all temporary tablespace storage, then the session would hang until the temporary space used by that session is cleared.

Lack of temporary tablespace space for sort operations can be prevented by using temporary tablespace groups.

NO.50 Examine this statement and output:

Which three statements are true?

Both 9822 and 8779 sessions are waiting for operating system resources.

Session 8779 may be waiting due to a network problem.

Session 9857 waited 1354 seconds for another process, which was also waiting for a transaction to end.

Session 9857 is not waiting.

Session 8779 may be waiting for a user or application response.

Session 9822 will always stop waiting if the session that owns the TX enqueue issues a COMMIT statement as session 9822 is the first session in the transaction queue.

NO.51 A database instance is suffering poor I/O performance on two frequently accessed large tables.
No Big Table caching occurs in the database.
Examine these parameter settings:

Which are two actions either one of which will allow Big Table caching to occur?

Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to at least 50

Setting DB_KEEP_CACHE_SIZE to at least 50M

Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to at least 25

Increasing DB_CACHESIZE to 1 G

Setting PARALLEL_DEGREE_POLICYADAPTIVE

Setting PARALLEL_DEGREE_POLICYAUTO

NO.52 A Standard Edition production database has performance problems for two hours on the same day each week.
Which tool must you use to diagnose the problem?

SQL Performance Analyzer

AWR Compare Periods report

Database Replay

Statspack report

NO.53 What are the least elevated values of statistics_level and C0NTR0LJ4ANAGEMENT_PACK_ACCESS that allow the usage of Monitoring of Database Operations?

STATISTICS_LEVEL=ALL and
CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC+TUNING

STATISTICS_LEVEL=BASIC and CONTROL_MANAGEMENT_PACK ACCESS=DIAGOSTIC

STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS-
DIAGOSTIC*TUNING

STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC

NO.54 You must write a statement that returns the ten most recent sales. Examine this statement:

Users complain that the query executes too slowly. Examine the statement’s current execution plan:

What must you do to reduce the execution time and why?

Create an index on SALES.TIME_ID to force the return of rows in the order specified by the ORDER BY clause.

Replace the FETCH FIRST clause with ROWNUM to enable the use of an index on SALES.

Collect a new set of statistics on PRODUCT, CUSTOMERS, and SALES because the current stats are inaccurate.

Enable Adaptive Plans so that Oracle can change the Join method as well as the Join order for this query.

Create an index on SALES.CUST_ID to force an INDEX RANGE SCAN on this index followed by a NESTED LOOP join between CUSTOMERS and SALES.

Leave a Reply Cancel reply