1Z0-117 Online Practice Questions and Answers

Correct Answer: ACDG

C: PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than PARALLEL_SERVER_TARGET.

By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes.

D: Note: ORA-12827: insufficient parallel query slaves available Cause: PARALLEL_MIN_PERCENT parameter was specified and fewer than minimum slaves were acquired Action: either re-execute query with lower PARALLEL_MIN_PERCENT or wait until some running queries are completed, thus freeing up slaves

A, G: PARALLEL_MIN_TIME_THRESHOLD specifies the minimum execution time a statement should have before the statement is considered for automatic degree of parallelism. By default, this is set to 30 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

Correct Answer: E

The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on the entire system. The Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements which are good candidates for SQL tuning.

Note:

* When SQL statements are executed by the Oracle database, the query optimizer is used to generate the execution plans of the SQL statements. The query

optimizer operates in two modes:

a normal mode and a tuning mode.

In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.

In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be improved further. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When running in the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer.

Reference: Oracle Database Performance Tuning Guide, Automatic SQL Tuning

Correct Answer: AD

INSERT Creates a SQL script that stores the trace file statistics in the database. TKPROF creates this script with the name filename3. This script creates a table and inserts a row of statistics for each traced SQL statement into the table.

Correct Answer: C

SERV_MOD_ACT_TRACE_ENABLE

serv_mod_act_trace_enable and serv_mod_act_trace_disable, which enables and disables trace for given service_name, module and action.

For example for a given service name you can trace all session started from SQL*Plus. Module and action in your own created application can be set using

dbms_application_info set_module and set_action procedures.

serv_mod_act_trace_enable fills sys table wri$_tracing_enabled and view dba_enabled_traces on top of this table as follows:

SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'orcl', module_name=>'SQL*Plus')

PL/SQL procedure successfully completed.

SQL> select * from sys.wri$_tracing_enabled;

TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 INSTANCE_NAME

FLAGS

---------- ---------- ------------- ------------- ------------- ----- 4 orcl SQL*Plus 8

SQL> select * from dba_enabled_traces;

TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS

INSTANCE_NAME

-------------- ---------- ------------- ------------- ----- ----- ------------- SERVICE_MODULE orcl SQL*Plus TRUE FALSE

Correct Answer: A

Note:

*

When the SQL Tuning Advisor recommends that a SQL Profile be used, you should accept the SQL Profile that is recommended. In cases where the SQL Tuning Advisor recommends that an index and a SQL Profile be used, both should be used. You can use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database.

*

When tuning SQL statements with the SQL Tuning Advisor, if the advisor finds a tuned plan and verifies its performance to be better than a plan chosen from the corresponding SQL plan baseline, it makes a recommendation to accept a SQL profile. When the SQL profile is accepted, the tuned plan is added to the corresponding SQL plan baseline.

*

If SQL plan management is used and there is already an existing plan baseline for the SQL statement, a new plan baseline will be added when a SQL profile is created.

*

SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

*

SQL plan baseline is fixed if it contains at least one enabled plan whose FIXED attribute is set to YES.

*

ACCEPT_SQL_PROFILE Procedure and Function

This procedure creates a SQL Profile recommended by the SQL Tuning Advisor. The SQL text is normalized for matching purposes though it is stored in the data dictionary in de-normalized form for readability.

Correct Answer: B

OPTIMIZER_INDEX_COST_ADJ

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly--that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

Correct Answer: C

displays information about enabled SQL traces. DBA_ENABLED_TRACES

Incorrect:

A: The DBMS_SERVICE package lets you create, delete, activate, and deactivate services for a single instance.

B: The DBMS_MONITOR package let you use PL/SQL for controlling additional tracing and statistics gathering.

Reference: Oracle Database Reference, DBA_ENABLED_TRACES

Correct Answer: F

2) before 5).

Note:

* When application code is run, Oracle attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the statement does exist in the library cache and it can be shared, then Oracle reuses the existing code. This is known as a soft parse, or a library cache hit. If Oracle is unable to use existing code, then a new executable version of the application code must be built. This is known as a hard parse, or a library cache miss.

Reference: Oracle Database Performance Tuning Guide, SQL Sharing Criteria

Correct Answer: CD

C: To adopt an alternative plan regardless of whether SQL Tuning Advisor recommends it, call DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE. You can use this procedure to create a SQL plan baseline on any existing reproducible plan.

D: LOAD_PLANS_FROM_SQLSET Function

This function loads plans stored in a SQL tuning set (STS) into SQL plan baselines. The plans loaded from STS are not verified for performance but added as

accepted plans to existing or new SQL plan baselines. This procedure can be used to seed SQL management base with new SQL plan baselines.

Note:

* While tuning a SQL statement, SQL Tuning Advisor searches real-time and historical performance data for alternative execution plans for the statement. If plans

other than the original plan exist, then SQL Tuning Advisor reports an alternative plan finding.

SQL Tuning Advisor validates the alternative execution plans and notes any plans that are not reproducible. When reproducible alternative plans are found, you

can create a SQL plan baseline to instruct the optimizer to choose these plans in the future.

Incorrect:

Not A: ALTER_SQL_PLAN_BASELINE Function

This function changes an attribute of a single plan or all plans associated with a SQL statement using the attribute name/value format.

Usage Notes

When a single plan is specified, one of various statuses, or plan name, or description can be altered. When all plans for a SQL statement are specified, one of

various statuses, or description can be altered. This function can be called numerous times, each time setting a different plan attribute of same plan(s) or different

plan(s).

Reference: Oracle Database Performance Tuning Guide, Alternative Plan Analysis

Correct Answer: AB

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

EXACT

Only allows statements with identical text to share the same cursor.

Note:

EXACT-This is the default setting. With this value in place, the query is not rewritten to use bind variables.

With CURSOR_SHARING=EXACT (the default), every unique SQL statement executed will create a new entry in V$SQL, it will be hard-parsed, and an execution plan will be created just for it. There can be hundreds or thousands of very similar queries in the shared pool that differ only in the literals used in the SQL statement itself. This implies that the database is forced to hard-parse virtually every query, which, in turn, not only consumes a lot of CPU cycles but also leads to decreased scalability.

The database just cannot hard-parse hundreds or thousands of SQL statements concurrently-- the application ends up waiting for the shared pool to become available. One of the major scalability inhibitors in the database is not using bind variables. That was the motivation behind adding CURSOR_SHARING=FORCE .

Correct Answer: CD

If two lines are indented equally, then the top line is normally executed first. Here the line 3 and line 4 are indented equally, so line 3 ( TABLE ACCESS FULL EMP) with EMP being the driving table as the ordered hint requests that the tables listed in the FROM clause of a SQL state- ment be joined in the order specified, with the first table in the FROM clause specifying the driving table.

Correct Answer: D

As its name suggests, the query result cache is used to store the results of SQL queries for re-use in subsequent executions. By caching the results of queries,

Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset in the first place (for example, sorting/

aggregation, physical I/O, joins etc). The cache results themselves are available across the instance (i.e. for use by sessions other than the one that first executed

the query) and are maintained by Oracle in a dedicated area of memory. Unlike our homegrown solutions using associative arrays or global temporary tables, the

query result cache is completely transparent to our applications. It is also maintained for consistency automatically, unlike our own caching programs.

Note:

RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan.

Values:

MANUAL

The ResultCache operator is added only when the query is annotated (that is, hints).

FORCE

The ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).

For the FORCE setting, if the statement contains a NO_RESULT_CACHE hint, then the hint takes precedence over the parameter setting.

Incorrect:

A, B: CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

Values:

FORCE

Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

SIMILAR Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

EXACT

Only allows statements with identical text to share the same cursor.

C: The Oracle library cache is a component of the System Global Area (SGA) shared pool. Similarly to other Oracle cache structures, the point of the library cache is to reduce work and therefore to improve performance by caching the result of parsing and optimizing SQL or PL/SQL so that subsequent executions of the same SQL or PL/SQL require fewer preparatory steps to deliver a query result.

Correct Answer: AD

A: OPTIMIZER_USE_SQL_PLAN_BASELINES enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is found in SQL Management B ase, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.

D: EVOLVE_SQL_PLAN_BASELINE Function

This function evolves SQL plan baselines associated with one or more SQL statements. A SQL plan baseline is evolved when one or more of its non-accepted plans is changed to an accepted plan or plans. If interrogated by the user (parameter verify = 'YES'), the execution performance of each non-accepted plan is compared against the performance of a plan chosen from the associated SQL plan baseline. If the non-accepted plan performance is found to be better than SQL plan baseline performance, the non-accepted plan is changed to an accepted plan provided such action is permitted by the user (parameter commit = 'YES').

Incorrect:

B: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES enables or disables the automatic recognition of repeatable SQL statements, as well as the generation of SQL plan baselines for such statements.

C: ALTER_SQL_PLAN_BASELINE Function

This function changes an attribute of a single plan or all plans associated with a SQL statement using the attribute name/value format.

Correct Answer: B

Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoration. Statistics can be restored using RESTORE procedures of DBMS_STATS package. These procedures use a time stamp as an argument and restore statistics as of that time stamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics.

Reference: Oracle Database Performance Tuning Guide, Restoring Previous Versions of Statistics

Correct Answer: C

You can use an STS as input to SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other user-specified input parameters.

Note:

A SQL tuning set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could

include a user priority ranking. You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the shared SQL area, or

customized SQL provided by the user. An STS includes:

A set of SQL statements

Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment

Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the

number of complete executions, optimizer cost, and the command type Associated execution plans and row source statistics for each SQL statement (optional). Reference: Oracle Database Performance Tuning Guide, Managing SQL Tuning Sets