1Z0-064 Online Practice Questions and Answers

You want to capture AWR data to monitor performance variation every Monday between 9:00 AM and

12:00 PM for three months and automatically remove the older AWR data every fortnight.

How would you achieve this? (Choose the best answer.)

A. Create AWR baselines.

B. Create SQL plan baselines.

C. Create repeating baseline templates.

D. Create database services and make sure that user connections use them to connect to the database instance.

E. Create a single baseline template.

Examine the partial AWR report taken for a time period of 60 minutes: Which two inferences can you draw from this report? (Choose two.)

A. The database user calls are issuing frequent explicit commits.

B. The CPUs are busy executing server processes and background processes for a considerable amount of CPU time.

C. The database user calls are spending most of their time in I/O for single block reads.

D. The database user calls are spending most of their time waiting for sessions that are in more important consumer groups.

Examine the query and its output:

Which two inferences can be definitely derived from this output? (Choose two.)

A. The db file scattered read event has occurred 104 times in this session for file# 12.

B. The session has completed performing a full table scan.

C. The SQL statements in this session are performing excessive disk reads.

D. The multiblock factor is 8 for this I/O but it could vary for the other I/O events.

Examine the query and its output:

Which two statements are true? (Choose two.)

A. Session 2832 had to wait 2029 seconds for a message to arrive because of a network bottleneck.

B. Session 4208 is either idle or experiencing poor response time due to a network or resource bottleneck on the client process.

C. Session 3346 is in wait state because it wants to lock a row in a block in which other sessions have already locked rows, and there is no free ITL slot available in this block.

D. Session 3346 is in wait state because either it is waiting to update a row that is locked by another session or another session is trying to insert the same key value in a UNIQUE index.

E. Session 4208 is definitely idle and should be killed to free network resources.

Examine the parameters set for your database instance: You upgrade your database to Oracle Database 12c. The database supports a mixed workload and works with different workloads at different times. You notice in an ADDM report that the shared pool is inadequately sized. You resize the shared pool by decreasing the sizes of other pools, which results in inadequate sizes for other pools. You want to automate the sizing of SGA components.

Which two actions should you perform? (Choose two.)

A. Set the SGA_TARGET parameter equal to SGA_MAX_SIZE.

B. Set the SGA_TARGET parameter to the sum of DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE.

C. Set the MEMORY_MAX_TARGET parameter to the sum of DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE.

D. Set DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE to their minimum required values.

E. Set the PGA_AGGREGATE_TARGET parameter to 0 and the SGA_TARGET parameter to 1.5G.

Examine the initialization parameters set for a database instance:

The database supports an OLTP workload. Applications connect to the instance using shared server connections and perform small, random I/Os. All the data files are on the same disk. You notice free buffer wait events for sessions in the database instance.

To solve the problem, you increase the size of the buffer cache. But after some time, you notice sessions waiting again on free buffer waits.

What will you recommend to alleviate the issue? (Choose the best answer.)

A. Run the I/O calibration tool.

B. Configure the database instance to make asynchronous I/O available to DBWR.

C. Spread the data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/O subsystem.

D. Configure dedicated server connections for the applications.

Your database supports an OLTP workload during the day and batch processing at night. You want to monitor performance metrics to detect any degradation of performance in both types of workloads over a time period of 30 days.

Examine this list of possible steps:

1.

Create a fixed baseline.

2.

Create a baseline template.

3.

Create a new moving window baseline.

4.

Increase the retention period default value to 30 days.

5.

Increase the size of the existing moving window baseline to 30 days.

6.

Create warning and critical alerts for the relevant metrics.

7.

Enable adaptive thresholds to detect the workload patterns and specify a high-significance-level threshold type.

8.

Enable adaptive thresholds to detect the workload patterns and set different threshold values as a percentage of the maximum value.

Which option represents the required steps in the correct order? (Choose the best answer.)

A. 5, 7

B. 2, 4, 3

C. 3, 4, 8

D. 4, 5, 7

E. 5, 1, 6, 8

To investigate the slow response time of queries on the TRANS table, you gathered the table statistics and executed the query:

The table is stored in a tablespace that has Automatic Segment Space Management (ASSM) enabled. The tablespace is created with a standard block size of 8192 bytes.

Which three can be reasons for the slow response time of the queries? (Choose three.)

A. Row size is too large to fit into a single block during insert operations.

B. Row moves from one data block to another data block because the row grows too large to fit in the original block.

C. The table is subject to frequent insert, update, and delete DML activity leading to sparsely populated blocks.

D. The value of PCTUSED is set to a value lower than the default, causing row chaining.

E. The value of PCTFREE is set to a value lower than the default, causing row chaining.

You upgrade your database from Oracle Database 11g to 12c. You capture execution plans for a resource-intensive SQL workload before the upgrade. After the upgrade, you load the plans into the SQL Management Base.

Examine the parameters set for the instance:

Which two statements are true?

A. A better execution plan may be found for the SQL statements and can be evolved manually.

B. New execution plans generated will always be added to the SQL plan baselines as fixed plans.

C. New execution plans cannot be evolved until execution plans from the previous version are dropped.

D. New SQL plan baselines might be generated automatically for the repeatable SQL statements.

E. New execution plans generated will always be added to the SQL plan baselines as accepted plans.

As part of an application upgrade, new objects are being added to an application schema. You want to check the effect of the new schema objects on the performance of the SQL workload generated by the application.

How would you accomplish this?

A. Capture the workload in an STS and submit to SQL Access Advisor as an input to generate recommendations for indexes and materialized views.

B. Capture the workload, set the OPTIMIZER_USE_PENDING_STATISTICS to FALSE, and then replay the workload.

C. Capture the workload in an STS and submit to SQL Tuning Advisor as an input to generate recommendations for indexes and SQL profiles.

D. Set the PUBLISH statistic preference to FALSE, gather statistics, capture the workload in a SQL Tuning Set (STS), and submit the STS to SQL Tuning Advisor.