1Z0-064 Online Practice Questions and Answers

Examine the partial TKPROF output for an SQL statement: Which two inferences can definitely be made from this output? (Choose two.)

A. Array fetch operations were not performed for this query.

B. No hard parse was performed for this query.

C. The number of logical I/Os is almost equal to the number of physical I/Os.

D. Another transaction held a shared lock on the table, thereby causing a significant delay.

E. An uncommitted transaction made a series of updates to the NAME_ID column just before the execution of this query.

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

You execute this query twice in a session:

Then you query V$SQL_SHARED_CURSOR for details about child cursors as shown.

Which two statements are true? (Choose two.)

A. No statistics were collected during the first execution of the query.

B. A subsequent execution of the query in this session is likely to undergo a soft parse.

C. The second execution of the query was hard parsed because the estimated cardinality was inaccurate.

D. A subsequent execution of the query in this session will undergo a hard parse.

E. The second execution of the query was hard parsed because extended statistics were collected after the first execution of the query.

Which three statements are true about using Real-Time Database Operations? (Choose three.)

A. The STATISTICS_LEVEL initialization parameter must be set to ALL to enable automatic SQL monitoring for all long-running queries.

B. The CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter must be set to DIAGNOSTIC+TUNUNG to use Real-Time Database Operations.

C. The STATISTICS_LEVEL initialization parameter can be set to TYPICAL or ALL to enable Real-Time Database Operations.

D. Real-Time Database Operations can be enabled only at the system level.

E. Real-Time Database Operations can be created by using the DBMS_MONITOR or DBMS_SESSION packages.

F. Database operation monitoring starts automatically when a database operation consumes at least five seconds of the CPU or I/O time in a single execution.

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 want to capture the performance of your database during the last ten days of the first quarter of the current financial year, so that you can compare this performance against the remaining quarter ends of the current financial year.

Which method should you use? (Choose the best answer.)

A. Create a static baseline that can be used with AWR compare reports.

B. Create a new moving window baseline and enable adaptive thresholds for relevant metrics.

C. Use a repeating baseline template to create and drop baselines based on a repeating time schedule and set adaptive thresholds at a high significance level.

D. Use fixed baseline templates to create a new moving window baseline and set relevant warning alerts that are computed as a percentage multiple of the maximum value observed for the data in the moving window baseline.

Your database supports a DSS system.

Examine the parameter values configured in your database:

sga_max_size = 480M

sga_target = 480M

pga_aggregate_target = 160M

The CUSTOMERS table contains 8,000 rows. The CUST_ID column is the primary key and the

COUNTRY_ID column contains only three possible values: 1111, 2222, and 3333.

You execute the commands:

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(`SH',`CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> CREATE INDEX COUNTRY_IDX ON CUSTOMERS (COUNTRY_ID);

Index created.

You then perform several INSERT, UPDATE, and DELETE operations, significantly altering the data in the

table.

View the Exhibit to examine the query and its execution plan.

Which three options would improve the performance of the query? (Choose three.)

A. creating a bitmap index on the COUNTRY_ID column

B. regathering statistics on the CUSTOMERS table

C. creating a histogram on the COUNTRY_ID column

D. increasing the size of the PGA

E. creating a SQL profile

F. creating a KEEP cache

Your database supports an online transaction processing (OLTP) workload. The database uses ASM storage. One of the ASM disks goes offline because of hardware failure. When the disk is replaced and then added back to the diskgroup, database performance is affected by rebalance operations.

Which two actions would you recommend to lower the impact of rebalance operations on the performance of the database? (Choose two.)

A. Increase the number of ASMB processes.

B. Decrease the value of the ASM_POWER_LIMIT parameter.

C. Set the DISK_REPAIR_TIME disk attribute to a lower value.

D. Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.

E. Set the DISK_REPAIR_TIME disk attribute to a higher value.

Examine the Load Profile section of an AWR report: Which two inferences can you derive from the details in this section? (Choose two.)

A. The values for Redo size and Block changes imply that only updates were performed by transactions.

B. The values for Parses (SQL) and Hard parses (SQL) imply that cursor sharing occurred quite often.

C. The values for DB Time and DB CPU imply that the database had a high proportion of idle time during the specified snapshot interval.

D. The values for SQL Work Area and User calls imply that only sort-based operations were performed.

E. The values for Logical reads and Physical reads imply that the number of disk reads per second was less than the total number of DB block reads and consistent gets per second.

Which three statements are true about the TEST EXECUTE method of the SQL Performance Analyzer?

A. The SQL statements in the STS are compared only on elapsed time for the pre-change and post-change trials.

B. The execution statistics generated for SQL statements are analyzed to check whether they have improved or regressed.

C. An execution plan and statistics are generated for SQL statements only in the pre-change trial.

D. The SQL statements in the SQL Tuning Set (STS) are executed at least twice for both pre-change and post-change trials.

E. An execution plan and statistics are computes for each SQL statement in the STS.