1z0-054 Oracle Database 11g Performance Tuning Practice Exam

Sample Questions:

You are working on an online transaction processing (OLTP) system. You detected free buffer waits events for your database instance running in a machine that has multiple CPUs. You increased the database buffer cache size as the first step. After a few hours of work on the database, further investigation shows that the same event is being recorded. What would be your next step to avoid this event in future?

Decrease the value of the DBWR_IO_SLAVES parameter.

Set the USE_INDIRECT_DATA_BUFFERS parameter to TRUE.

Increase the value of the DB_WRITER_PROCESSES parameter.

Increase the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter.

After running SQL Performance Analyzer (SPA), you observe a few regressed SQL statements in the SPA output. Identify the two actions that you would suggest for these regressed SQL statements. (Choose two.)

Running SQL Access Advisor

Adding them to SQL Plan Baseline

Submitting them to SQL Tuning Advisor

Running Automatic Database Diagnostic Monitor (ADDM)

Examine the following block that executes a SQL Performance Analyzer task: begin dbms_sqlpa.execute_analysis_task(task_name => ‘SPA_SKD4’, execution_type => ‘TEST EXECUTE’,

execution_name => ‘before’); end; Which two statements are true about the execution? (Choose two.)

It generates execution plans and execution statistics.

It executes only the query part of the DML statements.

The test plans are generated and added to plan baseline.

The test plans are executed, but not generated if they are available in the plan baseline.

Examine the following block that executes a SQL Performance Analyzer task: begin dbms_sqlpa.execute_analysis_task(task_name => ‘SPA_SKD4’, execution_type => ‘TEST EXECUTE’,

execution_name => ‘before’); end; Which two statements are true about the execution? (Choose two.)

It generates execution plans and execution statistics.

It executes only the query part of the DML statements.

The test plans are generated and added to plan baseline.

The test plans are executed, but not generated if they are available in the plan baseline.

Identify the two direct sources from where SQL plans can be loaded into the SQL plan baselines. (Choose two.)

Cursor cache

Stored outline

SQL Tuning Set

Automatic Workload Repository (AWR) snapshots

You receive a recommendation for a SQL statement through the automatic SQL tuning process that suggests implementing a SQL profile. You implement the SQL profile. The tables associated with the SQL statement grow, and indexes are created and dropped on these tables. What would happen to the SQL profile that is created for the SQL statement?

It becomes outdated immediately after the tables change.

It is maintained automatically along with the changes in the tables.

The profile will be used for the changed plans of the SQL statement.

The plan for the SQL statement is frozen until it is regenerated by the SQL Tuning Advisor.

Who this course is for:

• Those who want to get knowledge on Certifications & Check your Skill Test while attend this Practice Test