Weekend Sale Limited Time 70% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: xmas50

Oracle 1z0-084 - Oracle Database 19c: Performance Management and Tuning

Page: 1 / 2
Total 55 questions

You use SQL Tuning Advisor to tune a given SQL statement.

The analysis eventually results in the implementation of a SQL Profile.

You then generate the new SQL Profile plan and enforce it using a SQL Plan Baseline but forget to disable the SQL Profile and a few days later you find out that the SQL Profile is generating a new execution plan.

Which two statements are true?

A.

The existence of two concurrent plan stability methods generates a child cursor for every execution.

B.

The SQL Profiles as well as SQL Plan Baseline are implemented using hints, so they both generate the same plan.

C.

The execution plan is the one enforced by the SQL Profile.

D.

The execution plan is the one enforced by the SQL Plan Baseline.

E.

The SQL Plan Baseline must be accepted in order to be used for the execution plan.

F.

The conflict between the two plan stability methods results in an error.

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

A.

STATISTICS_LEVEL=ALL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC+TUNING

B.

STATISTICS_LEVEL=BASIC and CONTROL_MANAGEMENT_PACK ACCESS=DIAGOSTIC

C.

STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS-DIAGOSTIC*TUNING

D.

STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC

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

A.

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

B.

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

C.

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

D.

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

E.

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

F.

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

Accessing the SALES tables causes excessive db file sequential read wait events.

Examine this AWR except:

Now, examine these attributes displayed by querying dba_tables:

Finally, examine these parameter settings:

Which two must both be used to reduce these excessive waits?

A.

Partition the SALES table.

B.

Increase PCTFREE for the SALES table.

C.

Re-create the SALES table.

D.

Compress the SALES table.

E.

Coalesce all sales table indexes.

Which two statements are true about disabling Automatic Shared Memory Management (ASMM)?

A.

All auto-tuned SGA components are reset to their original user-defined values.

B.

All SGA components excluding fixed SGA and other internal allocations are readjusted immediately after disabling ASMM.

C.

Both SGA_TARGET and SGA_MAX_SIZE must be set to zero.

D.

All SGA components retain their current sizes at the time of disabling.

E.

The SGA size remains unaffected after disabling ASMM.

F.

It requires a database instance restart to take effect.

Which two Oracle Database features use database services?

A.

Oracle Automatic Reoptimization

B.

Database Resource Manager

C.

Oracle SQL Performance Management

D.

Oracle SQL Tuning Advisor

E.

Oracle Scheduler

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

A.

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

B.

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

C.

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.

D.

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

E.

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

Which procedure gathers statistics that are always used in the generation of any execution plan?

A.

DBMS_STATS.GATHER_DICTIONARY_STATS

B.

DBMS_STATS.GATHER_FIXED_ OBJECTS_STATS

C.

DBMS_STATS.GATHER_DATABASE_STATS

D.

DBMS_STATS.GATHER_SYSTEM_STATS

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

A.

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

B.

result_cache_max_size is set dynamically to 0.

C.

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

D.

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.

E.

Decreasing the value set for RESULT_CACHE_REMOTE_EXPIRATION.

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

A.

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

B.

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

C.

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.

D.

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.