Updated Feb 05, 2025 Verified 1z1-084 dumps Q&As – 100% Pass [Q34-Q53]

5/5 - (1 vote)

Updated Feb 05, 2025 Verified 1z1-084 dumps Q&As – 100% Pass

New 2025 Latest Questions 1z1-084 Dumps – Use Updated Oracle Exam

Q34. 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.

Q35. Database performance has degraded recently.
index range scan operations on index ix_sales_time_id are slower due to an increase in buffer gets on sales table blocks.
Examine these attributes displayed by querying DBA_TABLES:

Now, examine these attributes displayed by querying DBA_INDEXES:

Which action will reduce the excessive buffer gets?

Re-create the SALES table sorted in order of index IX_SALES_TIME_ID.

Re-create index IX_SALES_TIME_ID using ADVANCED COMPRESSION.

Re-create the SALES table using the columns in IX_SALES_TIME_ID as the hash partitioning key.

Partition index IX_SALES_TIME_ID using hash partitioning.

Q36. You need to collect and aggregate statistics for the ACCTG service and PAYROLL module, and execute:

Where do you find the output of this command?

By viewing V$SERV_MOD_ACT_STATS

In $ORACLE_BASE/diag/rdbms/<db unique name>/<instance name>/trace

By viewing V$SERVICE_STATS

In the current working directory

Q37. Examine these commands, which execute successfully:

Which statement is true?

AD DM is enabled for all pluggable databases.

ADDM, AWR, and ASH reports can be purged automatically.

AWR- snapshots in all pluggable databases will be purged automatically after every 60 mins.

AWR snapshots can be purged manually in pluggable databases.

Q38. 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.

Q39. Examine these statements and output:

What parameter change activates the generation and use of SQL Plan Directives7

optimizer_features_enable=12.2.0.1

optimizer_capture_sql_plan_baselines_TRUE

optimizer_dynamic_sampling=11

optimizer_adaptive_plans=TRUE

optimizer_adaptive_statistics = TRUE

Q40. 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

Q41. A database supporting a mixed workload is hosted on a server with 64 CPUs.
A large number of free buffer waits and buffer busy waits occur affecting performance.
The buffer cache size was then increased but after a few hours, the same wait events occur more often than before the change.
Examine these parameter settings:

Which two actions can help reduce the number of these waits7

increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT to 128

Increasing the size of MEMORYTARGET

setting dbwr_io_slaves to 64

increasing the value of DBWRITERPROCESSES to 64,

reducing the values of DB_FILE_MULTILOCK_READ_COUNT to 64

Q42. 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

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

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

A 60% cache hit ratio can be observed for database instances which have very good 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.

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.

Q44. 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.

Q45. Which three statements are true about using the in Memory (IM) column store?

It does not improve performance for queries using cached results of function evaluations on columns from the same table.

It does not improve performance for queries that use join groups on columns from different tables.

It can improve OLTP workload performance by avoiding the use of indexes.

It does not improve performance for queries using user-defined virtual column results.

It does not require all database data to fit in memory to improve query performance.

It improves performance for queries joining several tables using bloom filter joins.

Q46. Which two types of performance problems are reported by ADDM for PDBS?

I/O capacity limits

Excessive checkpoint writes

SGA sizing issues

Top SQL statements

User I/O waits

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

Syntax Check

SQL Row Source Generation

SQL Optimization

Shared Pool Memory Allocation

Semantic Check

Q48. 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.

Q49. Examine this output of a query of VSPGA_TAPGET_ADVICE:

Which statements is true’

With a target of 700 MB or more, all multipass executions work areas would be eliminated.

PGAA_AGGREGATE should be set to at least 800 MB.

GGREGATE_TARGET should be set to at least 700 MB.

With a target of 800 MB or more, all one-pass execution work areas would be eliminated.

Q50. 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.

Q51. Database performance has degraded recently.
index range scan operations on index ix_sales_time_id are slower due to an increase in buffer gets on sales table blocks.
Examine these attributes displayed by querying DBA_TABLES:

Now, examine these attributes displayed by querying DBA_INDEXES:

Which action will reduce the excessive buffer gets?

Re-create the SALES table sorted in order of index IX_SALES_TIME_ID.

Re-create index IX_SALES_TIME_ID using ADVANCED COMPRESSION.

Re-create the SALES table using the columns in IX_SALES_TIME_ID as the hash partitioning key.

Partition index IX_SALES_TIME_ID using hash partitioning.

Q52. For which two actions can SQL Performance Analyzer be used to assess the impact of changes to SQL performance?

storage, network, and interconnect changes

operating system upgrades

changes to database initialization parameters

database consolidation for pluggable databases (PDBs)

operating system and hardware migrations

Q53. This error occurred more than four hours ago in the database:
ORA-04036 PGA memory used by theinstance exceedsPGA_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