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

Pattern Questions:

You might be engaged on a web-based transaction processing (OLTP) system. You detected free buffer waits occasions on your database occasion working in a machine that has a number of CPUs. You elevated the database buffer cache dimension as step one. After just a few hours of labor on the database, additional investigation exhibits that the identical occasion is being recorded. What could be the next step to keep away from this occasion in future?

Lower the worth of the DBWR_IO_SLAVES parameter.

Set the USE_INDIRECT_DATA_BUFFERS parameter to TRUE.

Improve the worth of the DB_WRITER_PROCESSES parameter.

Improve the worth of the DB_FILE_MULTIBLOCK_READ_COUNT parameter.

After working SQL Performance Analyzer (SPA), you observe just a few regressed SQL statements within the SPA output. Determine the 2 actions that you’d recommend for these regressed SQL statements. (Select two.)

Working SQL Entry Advisor

Including them to SQL Plan Baseline

Submitting them to SQL Tuning Advisor

Working Computerized Database Diagnostic Monitor (ADDM)

Study the next block that executes a SQL Performance Analyzer process: start dbms_sqlpa.execute_analysis_task(task_name => ‘SPA_SKD4’, execution_type => ‘TEST EXECUTE’,

execution_name => ‘earlier than’); finish; Which two statements are true concerning the execution? (Select two.)

It generates execution plans and execution statistics.

It executes solely the question a part of the DML statements.

The take a look at plans are generated and added to plan baseline.

The take a look at plans are executed, however not generated if they’re obtainable within the plan baseline.

Study the next block that executes a SQL Performance Analyzer process: start dbms_sqlpa.execute_analysis_task(task_name => ‘SPA_SKD4’, execution_type => ‘TEST EXECUTE’,

execution_name => ‘earlier than’); finish; Which two statements are true concerning the execution? (Select two.)

It generates execution plans and execution statistics.

It executes solely the question a part of the DML statements.

The take a look at plans are generated and added to plan baseline.

The take a look at plans are executed, however not generated if they’re obtainable within the plan baseline.

Determine the 2 direct sources from the place SQL plans may be loaded into the SQL plan baselines. (Select two.)

Cursor cache

Saved define

SQL Tuning Set

Computerized Workload Repository (AWR) snapshots

You obtain a advice for a SQL assertion by means of the automated SQL tuning course of that means implementing a SQL profile. You implement the SQL profile. The tables related to the SQL assertion develop, and indexes are created and dropped on these tables. What would occur to the SQL profile that’s created for the SQL assertion?

It turns into outdated instantly after the tables change.

It’s maintained robotically together with the adjustments within the tables.

The profile might be used for the modified plans of the SQL assertion.

The plan for the SQL assertion is frozen till it’s regenerated by the SQL Tuning Advisor.