1Z0-064 Online Practice Questions and Answers

You are administering a database that supports a DSS workload. Automatic Shared Memory Management is enabled for the database instance. Users issue queries to perform large soft operations and complain about degraded performance of the queries. On investigation, you notice that the queries are performing multipass work area executions and the I/O contention on one of the temporary tablespaces is very high.

Which two can be possible resolutions for this issue? (Choose two.)

A. Increase the size of the large pool.

B. Increase the value of the PGA_AGGREGATE_TARGET parameter.

C. Create a temporary tablespace group and assign it to users.

D. Increase the value of the PGA_AGGREGATE_LIMIT parameter.

E. Create another temporary tablespace and assign it to users.

F. Enable temporary undo.

Examine an extract from a PGA Memory Advisory for your database: Which two inferences are correct? (Choose two.)

A. Automatic management of PGA memory is disabled.

B. The current PGA size requires the use of a temporary tablespace for sorting operations.

C. The current PGA size is sufficient and does not require the memory manager to allocate more memory.

D. PGA size should be increased at least four times its current size for significant improvement in performance and disk space management.

For which two requirements can you always use the V$ACTIVE_SESSION_HISTORY view? (Choose two.)

A. to investigate intermittent performance problems in a session, only when the problem lasted less than five minutes in the last twelve hours

B. to find the exact number of executions of a specific query in a session in the last two minutes

C. to identify which module in an application was being executed in a session

D. to identify a scheduler job that is not responding

E. to find the amount of Program Global Architecture (PGA) memory being currently consumed by a session

You plan to upgrade your production database from Oracle Database 11g to 12c and also to introduce new objects to the database. You also want to upgrade the hardware. You have already created a test system with the upgrades to be made to the production database. As part of the testing, you want to:

analyze and compare the overall database workload with concurrency and transaction characteristics find SQL statements that might get regressed because of the upgrade analyze execution plans for SQL statements for which performance might get regressed analyze the impact of new schema objects on database performance

Which two tools would you recommend to achieve the objective? (Choose two.)

A. Database Replay

B. SQL Tuning Advisor

C. SQL Access Advisor

D. Automatic Database Diagnostic Monitor (ADDM) compare periods report

E. SQL Performance Analyzer

F. Automatic Workload Repository (AWR) compare periods report

You have been asked to use table compression for two large tables. Given are the details of the tables:

The TRANS_DET table:

The table is used by an OLTP application.

High volume insert and update operations are performed on the table.

The table is frequently queried using index range scans.

The TRANS_HISTORY table:

The table is used by a DSS application.

High volume bulk loads are performed on the table.

The table is used to store archival data on which large table full-table scans (FTS) are performed.

Which row store compression would you recommend for these tables with minimal overhead on

performance? (Choose the best answer.)

A. basic table compression for both the tables

B. advanced row compression for both the tables

C. basic table compression for the TRANS_HISTORY table and advanced row compression for the TRANS_DET table

D. basic table compression for the TRANS_DET table and advanced row compression for the TRANS_HISTORY table

E. warehouse compression for the TRANS_DET table and archive compression for the TRANS_HISTORY table

You are administering a database that supports an OLTP workload. RESULT_CACHE_MODE is set to the default value and a result cache is configured for the instance. Multiple sessions execute syntactically similar queries without dblinks, containing functions and expressions, on tables with no DML activity. Some users complain about poor performance of these queries.

You investigate and find that the queries are frequently performing physical I/O, even though the results fetched by the queries are similar.

Which two actions do you recommend to overcome the problem affecting these queries? (Choose two.)

A. Set the RESULT_CAHCE_MODE parameter to FORCE for the instance.

B. Use the result cache hint in the queries.

C. Use bind variables for similar queries instead of literals.

D. Set the RESULT_CACHE_REMOTE_EXPIRATION parameter to a nonzero value.

E. Configure the KEEP pool and cache the queried tables used in the KEEP pool.

You are administering a database that supports a mixed workload. The CURSOR_SHARING parameter is set to the default value. While analyzing the latest Automatic Workload Repository (AWR) report, you find a large number of cursor: pin S wait on X, cursor: pin X wait on S, and library cache mutex waits in the Top 10 foreground events section. Examine the Instance Efficiency Percentages section in the AWR report:

Which three statements are true in this scenario? (Choose three.)

A. Sessions are waiting for mutexes in share mode on cursors but other sessions are holding the mutexes in exclusive mode.

B. The CPU is spending more time in finding cursors in the library cache.

C. Cursors are not getting shared, resulting in a large number of hard parses.

D. Sessions are waiting for mutexes in exclusive mode on cursors but other sessions are holding the mutexes in share mode.

E. The buffers required by queries are not found in the buffer cache, thereby increasing expensive disk I/

O.

A parallel SQL statement is taking a long time to execute. You want to verify how the server processes are dividing work.

How should you determine this?

A. by using Real-Time ADDM

B. by using services

C. by using SQL Tuning Advisor

D. by using ASH

E. by using Real-Time SQL monitoring

Examine the parameters set for your database instance:

To investigate the slow response time of queries on the TRANS table, you gather table and execute the query:

The table is stored in a tablespace with Automatic Segment Space Management (ASSM), and some rows of the TRANS table are migrated and chained.

Which two actions would you recommend to improve query response time?

A. Reorganize the TRANS table online by using the DBMS_REDEFINITION package.

B. Create a bigger non-standard blocksize tablespace and move the TRANS table to that tablespace.

C. Move the TRANS table to a tablespace with manual segment space management with a lower value set for the PCTUSED attribute.

D. Move the TRANS table to a tablespace with manual segment space management with a higher value set for the PCTFREE attribute.

Your database supports multiple applications. The applications run on the middle tier and use connection for connecting to the database.

You notice that the sessions created by the applications are competing for resources. You want to statistically measure the workload and set priorities.

What action must you perform to achieve this?

A. Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE procedure to trace the services.

B. Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SESSION_TRACE_ENABLE procedure to trace the services.

C. Create services for the applications and set the relative priority of services within an instance by mapping the services directly to consumer groups.

D. Create services for the applications and set a relative priority by assigning them to application users.