1z0-054 | 10 Tips For Avant-garde 1z0-054 practice test


Q31. You enabled tracing for a session by executing the following command:

SQL>SET AUTOTRACE TRACEONLY;

You issued a SQL query in the same session. Which two statements are true? (Choose two.)

A. It generates only a report on the execution path used by the SQL optimizer.

B. Trace for the statement would be created without displaying the query output.

C. It generates only the statement execution statistics without executing the query.

D. It generates a report on the execution path used by the SQL optimizer and the statement execution statistics.

Answer: BD

Q32. View Exhibit1 and examine a portion of Active Session History(ASH) report.

View Exhibit2 to examine the tablespaces.

Which action would you suggest after analyzing the output?

A. Increase the log buffer size.

B. Rebuild indexes on SPCT table.

C. Decreasing the number of free lists in segment SPCT.

D. Move SPCT table to a different tablespace that is locally managed with automatic segment space management.

Answer: D

Q33. There are 500 users of an online transaction processing (OLTP) application that resides in your database. The system had been maintaining an average response rate of 10 seconds as specified by the Service-Level Agreement (SLA).This morning you were informed that for the past four days the average response time has gone up to 15-20 seconds. No changes have been made to the hardware or networking setup.

What is the first thing that you should do to diagnose the cause of the slow response time?

A. Determine whether it is a memory configuration issue.

B. Determine whether the slow response is wait bound or CPU bound.

C. Determine whether the application is performing excessive I/O due to poor access path choice.

D. Determine whether new tablespaces have been created on the disks that are being used by the existing tablespaces.

Answer: B

Q34. The Service-Level Agreement for the production database includes a clause that the database down time should not be more than 15 minutes.

Which two details would you examine in the alert log to diagnose whether your database meets this requirement? (Choose two.)

A. log switch frequency

B. data file recovery time

C. instance recovery time

D. deadlock and timeout errors

E. the FAST_START_MTTR_TARGET parameter setting

Answer: CE

Q35. You work as a database administrator to manage an online transaction processing (OLTP) system.The application code was recently tuned for optimum performance and users acknowledged the performance improvement. However, the users are now again complaining that the application response time has gone down considerably. You used a few queries to investigate the problem and a couple of important queries are shown in the Exhibit. View the Exhibit and analyze the output shown.

Which actions would improve the performance? (Choose all that apply.)

Exhibit:

A. increasing the number database writers

B. decreasing the number of database writers

C. increasing the size of the database buffer cache

D. decreasing the size of the database buffer cache

E. decreasing the value set to the FAST_START_MTTR_TARGET parameter

Answer: AC

Q36. While configuring the snapshot data capture for your database, you have specified the snapshot level setting as 6.

Which statistics would be captured by the Statspack report? (Choose all that apply.)

A. resource limit

B. memory statistics

C. parent and child latches

D. segment-level statistics

E. optimizer execution plans

Answer: ABE

Q37. You received complaints from users about slow response time in some parts of the application. On investigation, you find that this happens whenever the application runs any query against the PROD_DET table that contains detailed information about all the products. You gathered the table statistics and executed the following statements to find more information about this table:

SQL> SELECT chain_cnt,

2 round(chain_cnt/num_rows*100,2) pct_chained,

3 avg_row_len, pct_free, pct_used

4 FROM user_tables

5 WHERE table_name = 'PROD_DET'

CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED

----------         -----------         -----------    ----------   ----------

4789                           100                  3691               10              40

Which methods can you use to improve the performance? (Choose all that apply.)

A. online table redefinition

B. export and import the table

C. decrease the PCTFREE value

D. move the table using the ALTER TABLE command

Answer: ABD

Q38. You are managing an online transaction processing (OLTP) system. Many users complain about the slow response time. You investigated and found that in the Top 5 events, direct path read accounts for 73.2% of the total wait time.

Which two are the possible reasons for the high direct path read time? (Choose two.)

A. The I/O subsystem is slow.

B. The SQL statements are using high CPU.

C. The SQL statement has a long parsing time.

D. The SQL statements are performing a large number of physical and logical reads.

Answer: AD

Q39. View the Exhibit exhibit1 to examine the series of SQL commands and parameter settings.

View the Exhibit exhibit2 to examine the plans available in the SQL plan baseline.

The first plan (in red) is created when OPTIMIZER_MODE is set to ALL_ROWS and the second plan (in blue) is created when OPTIMIZER_MODE is set to FIRST_ROWS.

Which SQL plan baseline would be used if the SQL query in exhibit1 is executed again when the value of OPTIMIZER_MODE is set to FIRST_ROWS?

A. the second plan, because it is a fixed plan

B. the first plan, because it is an accepted plan

C. the second plan, because it is the latest generated plan in FIRST_ROW mode

D. A new plan, because the second plan in FIRST_ROW mode is not an accepted plan

Answer: B

Q40. You are working as a DBA in ABC Corp. You are working on online transaction processing (OLTP) system. The database uses ASM storage. One of the ASM disk goes offline because of hardware failure. When the disk is added back again after the fix, because the database is active, rebalance operations impact the I/O subsystem.

Which two recommendations would you give to lower the impact on the I/O subsystem? (Choose two.)

A. Increase the number of ASMB processes.

B. Increase the number of DBWR_IO_SLAVES.

C. Decrease the value for the ASM_POWER_LIMIT parameter.

D. Increase the value for the ASM_POWER_LIMIT parameter.

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

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

Answer: CF