Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 91:

  Which statement is true about an automatic SQL task?

  A. It will attempt to tune the currently running SQL statements that are highly resource intensive.
  B. It will automatically implement new SQL profiles for the statements that have existing SQL profiles.
  C. It will attempt to tune all-long-running queries that have existing SQL profiles.
  D. It will automatically implement SQL profiles if a three-fold benefit can be achieved and automatic profile implementation is enabled.
  E. It will tune all the top SQL statements from AWR irrespective of the time it takes to complete the task in a maintenance window.
  D. It will automatically implement SQL profiles if a three-fold benefit can be achieved and automatic profile implementation is enabled.

  ---

  Optionally, implements the SQL profiles provided they meet the criteria of threefold performance improvement

  The database considers other factors when deciding whether to implement the SQL profile. For example, the database does not implement a profile when the objects referenced in the statement have stale optimizer statistics. SQL profiles that have been implemented automatically show type is AUTO in the DBA_SQL_PROFILES view. If the database uses SQL plan management, and if a SQL plan baseline exists for the SQL statement, then the database adds a new plan baseline when creating the SQL profile. As a result, the optimizer uses the new plan immediately after profile creation.

  Incorrect:

  E: Oracle Database automatically runs SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates. This task, called Automatic SQL Tuning, runs in the default

  maintenance windows on a nightly basis. By default, automatic SQL tuning runs for at most one hour.

  Note:

  After automatic SQL tuning begins, the database performs the following steps:

  1.

  Identifies SQL candidates in the AWR for tuning

  Oracle Database analyzes statistics in AWR and generates a list of potential SQL statements that are eligible for tuning. These statements include repeating high- load statements that have a significant impact on the database.

  The database tunes only SQL statements that have an execution plan with a high potential for improvement. The database ignores recursive SQL and statements that have been tuned recently (in the last month), parallel queries, DML, DDL,

  and SQL statements with performance problems caused by concurrency issues.

  The database orders the SQL statements that are selected as candidates based on their performance impact. The database calculates the impact by summing the CPU time and the I/O times in AWR for the selected statement in the past

  week.

  2.

  Tunes each SQL statement individually by calling SQL Tuning Advisor

  During the tuning process, the database considers and reports all

  recommendation types, but it can implement only SQL profiles automatically.

  3.

  Tests SQL profiles by executing the SQL statement

  4.

  Optionally, implements the SQL profiles provided they meet the criteria of threefold performance improvement. The database considers other factors when deciding whether to implement the SQL profile. For example, the database does not

  implement a profile when the objects referenced in the statement have stale optimizer statistics. SQL profiles that have been implemented automatically show type is AUTO in the DBA_SQL_PROFILES view.If the database uses SQL plan

  management, and if a SQL plan baseline exists for the SQL statement, then the database adds a new plan baseline when creating the SQL profile. As a result, the optimizer uses the new plan immediately after profile creation.

  Reference: Oracle Database Performance Tuning Guide, Automatic SQL Tuning

• Question 92:

  Examine the Exhibit 1 to view the structure of and indexes for EMPLOYEES and DEPARTMENTS tables.

  

  Which three statements are true regarding the execution plan?

  A. The view operator collects all rows from a query block before they can be processed but higher operations in the plan.
  B. The in-line query in the select list is processed as a view and then joined.
  C. The optimizer pushes the equality predicate into the view to satisfy the join condition.
  D. The optimizer chooses sort-merge join because sorting is required for the join equality predicate.
  E. The optimizer chooses sort-merge join as a join method because an equality predicate is used for joining the tables.
  A. The view operator collects all rows from a query block before they can be processed but higher operations in the plan.
  B. The in-line query in the select list is processed as a view and then joined.
  C. The optimizer pushes the equality predicate into the view to satisfy the join condition.

  ---

  Incorrect:

  Not D, not E:

  Sort Merge joins are used for UN-Equality and also there is no SORT clause in the SQL. Note: The optimizer may choose a sort merge join over a hash join for joining large amounts of data when any of the following conditions is true:

  *

  The join condition between two tables is not an equijoin, that is, uses an inequality condition such as <, <=, >, or >=.

  *

  Because of sorts required by other operations, the optimizer finds it cheaper to use a sort merge.

• Question 93:

  

  View the exhibit and examine the query and its execution plan from the PLAN_TABLE. Which statement is true about the execution?

  A. The row with the ID column having the value 0 is the first step execution plan.
  B. Rows are fetched from the indexes on the PRODUCTS table and from the SALES table using full table scan simultaneously, and then hashed into memory.
  C. Rows are fetched from the SALES table, and then a hash join operator joins with rows fetched from indexes on the PRODUCTS table.
  D. All the partitions of the SALES table are read in parallel.
  C. Rows are fetched from the SALES table, and then a hash join operator joins with rows fetched from indexes on the PRODUCTS table.

• Question 94:

  You enabled auto degree of parallelism (DOP) for your instance.

  Examine the query:

  

  Which two are true about the execution of this query?

  A. Dictionary DOP will be used, if present, on the tables referred in the query.
  B. DOP is calculated if the calculated DOP is 1.
  C. DOP is calculated automatically.
  D. Calculated DOP will always by 2 or more.
  E. The statement will execute with auto DOP only when PARALLEL_DEGREE_POLICY is set to AUTO.
  A. Dictionary DOP will be used, if present, on the tables referred in the query.
  C. DOP is calculated automatically.

  ---

  *

  PARALLEL (AUTO): The database computes the degree of parallelism (C), which can be 1 or greater (not D). If the computed degree of parallelism is 1, then the statement runs serially.

  *

  You can use the PARALLEL hint to force parallelism. It takes an optional parameter: the DOP at which the statement should run. In addition, theNO_PARALLEL hint overrides a PARALLEL parameter in the DDL that created or altered the table.

  The following example illustrates computing the DOP the statement should use: SELECT /*+ parallel(auto) */ ename, dname FROM emp e, dept d

  WHERE e.deptno=d.deptno;

  * When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database auto- matically decides if a statement should execute in parallel or not and what DOP it should use. Oracle Database also determines if the statement can be executed immediately or if it is queued until more system resources are available. Finally, Oracle Database decides if the statement can take advantage of the aggregated cluster memory or not.

• Question 95:

  You are administering a database, where an application frequently executes identical SQL statements with the same syntax. How will you optimize the query results without retrieving data blocks from the storage?

  A. By setting the CURSOR_SHARING parameter to FORCE.
  B. By using the bind variables and setting the CURSOR_SHARING parameter to EXACT.
  C. By using the CACHE hint to pin the queries in the library cache
  D. By ensuring that RESULT_CACHE_MODE parameter is set to MANUAL and using the RESULT_CACHE hint in the queries.
  E. By creating a SQL plan baseline for the identical statements.
  D. By ensuring that RESULT_CACHE_MODE parameter is set to MANUAL and using the RESULT_CACHE hint in the queries.

  ---

  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.

• Question 96:

  You enable auto degree of parallelism (DOP) for your database instance. Examine the following query:

  

  Which two are true about the execution of statement?

  A. Dictionary DOP for the objects accessed by the query is used to determine the statement DOP.
  B. Auto DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.
  C. Dictionary DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.
  D. The statement will be queued if insufficient parallel execution slaves are available to satisfy the statements DOP.
  E. The statement will be queued if the number of busy parallel execution servers exceeds PARALLEL_SERVERS_TARGET.
  F. The statements may execute serially.
  A. Dictionary DOP for the objects accessed by the query is used to determine the statement DOP.
  F. The statements may execute serially.

  ---

  *

  Parallel (Manual): The optimizer is forced to use the parallel settings of the objects in the state- ment.

  *

  MANUAL - This is the default. Disables Auto DOP (not B), statement queuing (not D, Not E) and in-memory parallel execution. It reverts the behavior of parallel execution to what it was pre- vious to Oracle Database 11g, Release 2 (11.2).

  *

  PARELLEL (MANUAL)

  You can use the PARALLEL hint to force parallelism. It takes an optional parameter: the DOP at which the statement should run. The following example forces the statement to use Oracle Database 11g Release 1 (11.1) behav- ior:

  SELECT /*+ parallel(manual) */ ename, dname FROM emp e, dept d

  WHERE e.deptno=d.deptno;

  *

  PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARAL- LEL_DEGREE_POLICY is set to AUTO, Oracle will

  queue SQL statements that require paral- lel 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 sys- tem with parallel server processes.

  Note that all serial (non-parallel) statements will execute immediately even if statement queuing has been activated.

• Question 97:

  One of your databases supports a mixed workload.

  When monitoring SQL performance, you detect many direct paths reads full table scans. What are the two possible causes?

  A. Histograms statistics not available
  B. Highly selective filter on indexed columns
  C. Too many sort operations performed by queries
  D. Indexes not built on filter columns
  E. Too many similar type of queries getting executed with cursor sharing disabled
  B. Highly selective filter on indexed columns
  D. Indexes not built on filter columns

  ---

  Note:

  * The direct path read Oracle metric occurs during Direct Path operations when the data is asynchronously read from the database files into the PGA instead of into the SGA data buffer. Direct reads occur under these conditions:

  -When reading from the TEMP tablespace (a sort operation)

  - When reading a parallel full-table scan (parallel query factotum (slave) processes)

  -Reading a LOB segment

  * The optimizer uses a full table scan in any of the following cases:

  -Lack of Index

  -Large Amount of Data

  -Small Table

  -High Degree of Parallelism

• Question 98:

  An application supplied by a new vendor is being deployed and the SQL statements have plan baselines provided by the supplier. The plans have been loaded from a SQL tuning set. You require the optimizer to use these baselines, but allow better plans to used, should any be created.

  Which two tasks would you perform to achieve this?

  A. Set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.
  B. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE.
  C. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to fix the plans.
  D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to fix the new plans.
  E. Use the DBMS_SPM.ALTER_SQL_BASELINE function to accept new plans.
  A. Set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.
  D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to fix the new plans.

  ---

  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.

• Question 99:

  How can you analyze an existing trace file to list the almost resource-intensive statements, aggregation of statistics, and to either exclude recursive call details?

  A. By using the DBMS_TRACE package
  B. By using the EXPLAIN PLAN command
  C. By enabling the SQL_TRACE parameter for the session
  D. By using the TKPROF utility
  E. By using the TRCSESS utility
  D. By using the TKPROF utility

  ---

  D: You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file. TKPROF can also:

  *

  Create a SQL script that stores the statistics in the database

  *

  Determine the execution plans of SQL statements TKPROF reports each statement executed with the resources it has consumed, the number of times it was called, and the number of rows which it processed. This information lets you easily locate those statements that are using the greatest resource. With experience or with baselines available, you can assess whether the resources used are reasonable given the work done.

  Incorrect:

  A: DBMS_TRACE provides subprograms to start and stop PL/SQL tracing in a session. Oracle collects the trace data as the program executes and writes it to database tables.

  A typical session involves:

  Starting PL/SQL tracing in session (DBMS_TRACE.SET_PLSQL_TRACE).

  Running an application to be traced.

  Stopping PL/SQL tracing in session (DBMS_TRACE.CLEAR_PLSQL_TRACE).

  E: The trcsess utility consolidates trace output from selected trace files based on several criteria: Session Id Client Id Service name Action name Module name After trcsess merges the trace information into a single output file, the output file could be processed by TKPROF Reference: Oracle Database Performance Tuning Guide, Understanding TKPROF

• Question 100:

  You have enabled DML by issuing: ALTER session ENABLE PARALLEL DML;

  The PARELLEL_DEGREE_POLICY initialization parameter is set to AUTO.

  Which two options true about DML statements for which parallel execution is requested?

  A. Statements for which PDML is requested will execute serially estimated time is less than the time specified by the PARALLEL_MIN_THRESHOLD parameter.
  B. Statements for which PDML is requested will be queued if the number of busy parallel execution servers greater than PARALLEL_MIN_SERVERS parameter.
  C. Statements for which PDML is requested will always execute in parallel if estimated execution in parallel if estimated execution time is greater than the time specified by the PARELLEL_MIN_TIME_THRESHOLD parameter.
  D. Statements for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_SERVERS_TARGET parameter.
  E. Statement for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_DEGREE_LIMIT parameter.
  C. Statements for which PDML is requested will always execute in parallel if estimated execution in parallel if estimated execution time is greater than the time specified by the PARELLEL_MIN_TIME_THRESHOLD parameter.
  D. Statements for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_SERVERS_TARGET parameter.

  ---

  C: 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.

  D: 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.

  Note:

  *

  PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.

  AUTO

  Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.

  *

  PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution processes for the instance. This value is the number of parallel execution processes Oracle creates when the instance is started.

  Reference: Oracle Database Reference; PARALLEL_SERVERS_TARGET