Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 31:

  You are administering a database supporting an OLTP workload. A new module was added to one of the applications recently in which you notice that the SQL statements are highly resource intensive in terms of CPU, I/O and temporary space. You created a SQL Tuning Set (STS) containing all resource-intensive SQL statements. You want to analyze the entire workload captured in the STS. You plan to run the STS through the SQL Advisor.

  Which two recommendations can you get?

  A. Combing similar indexes into a single index
  B. Implementing SQL profiles for the statements
  C. Syntactic and semantic restructuring of SQL statements
  D. Dropping unused or invalid index.
  E. Creating invisible indexes for the workload
  F. Creating composite indexes for the workload
  C. Syntactic and semantic restructuring of SQL statements
  F. Creating composite indexes for the workload

  ---

  The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects , creation of new indexes (F), restructuring of the SQL statement (C), or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

  Reference: Oracle Database Performance Tuning Guide 11g , SQL Tuning Advisor

• Question 32:

  View the exhibit and examine the plans in the SQL baseline for a given statement. Which interpretation is correct?

  A. A new plan cannot be evolved because SYS_SQL_bbedc41f554c408 is accepted.
  B. Plan SYS_SQL_PLAN_bbdc741f554c408 will always be used by the optimizer for the query.
  C. A new plan must be evolved using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function before it can be used.
  D. Plan SYS_SQL_bbedc741a57b5fc2 can be used by the optimizer if the cost of the query is less than plan SYS_SQL_PLAN_bbedc741f554c408.
  E. Plan SYS_SQL_PLAN_bbedc741f554c408 will not be used until it is fixed by using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function.
  C. A new plan must be evolved using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function before it can be used.

  ---

  Note:

  *

  Evolving a SQL plan baseline is the process by which the optimizer determines if non-accepted plans in the baseline should be accepted. As mentioned previously, manually loaded plans are automatically marked as accepted, so manual

  loading forces the evolving process. When plans are loaded automatically, the baselines are evolved using the

  EVOLVE_SQL_PLAN_BASELINE function, which returns a CLOB reporting its results.

  SET LONG 10000

  SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle =>

  'SYS_SQL_7b76323ad90440b9')

  FROM dual;

  *

  Manual plan loading can be used in conjunction with, or as an alternative to automatic plan capture. The load operations are performed using the DBMS_SPM package, which allows SQL plan baselines to be loaded from SQL tuning sets or

  from specific SQL statements in the cursor cache. Manually loaded statements are flagged as accepted by default. If a SQL plan baseline is present for a SQL statement, the plan is added to the baseline, otherwise a new baseline is created.

  *

  fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.

• Question 33:

  Your database has the OLTP_SRV service configured for an OLTP application running on a middle tier. This service is used to connect to the database by using connection pools. The application has three modules. You enabled tracing at

  the service by executing the following command:

  SQL exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (`OLTP_SRV');

  What is the correct method of consolidating the trace files generated by the procedure?

  A. Use all trace files as input for the tkprof utility to consolidate the trace files for a module.
  B. Use one trace file at a time as input for the trcess utility and use tkprof utility to consolidate all the output files for a module.
  C. Use the trcess utility to consolidate all trace files into a single output file, which can then be processed by the tkprof utility.
  D. Use the tkprof utility to consolidate the trace files and create an output that can directly be used for diagnostic purposes.
  C. Use the trcess utility to consolidate all trace files into a single output file, which can then be processed by the tkprof utility.

  ---

  Note:

  * Oracle provides the trcsess command-line utility that consolidates tracing information based on specific criteria.

  The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can help you monitor applications running against the Oracle Server.

  Note: SERV_MOD_ACT_TRACE_ENABLE Procedure

  Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified

  Reference: Oracle Database Performance Tuning Guide

• Question 34:

  A database instance is configured in the shared server mode and it supports multiple applications running on a middle tier. These applications connect to the database by using different services and tracing is enabled for the services. You

  want to view the detailed tracing setting for particular service.

  What would you use to view the tracing information?

  A. DBMS_SERVICE package
  B. DBMS_MONITOR package
  C. DBA_ENABLED_TRACES view
  D. Trcsess and tkprof
  C. DBA_ENABLED_TRACES view

  ---

  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

• Question 35:

  Examine the Exhibit and view the structure of an indexes for the EMPLOYEES table.

  

  Which two actions might improve the performance of the query?

  A. Use the ALL_ROWS hint in the query.
  B. Collect the histogram statistics for the EMPLOYEE_ID column.
  C. Decrease the value for the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.
  D. Decrease the index on the EMPLOYEE_ID if not being used.
  E. Set the OPTIMIZER_MODE parameter to ALL_ROWS.
  A. Use the ALL_ROWS hint in the query.
  E. Set the OPTIMIZER_MODE parameter to ALL_ROWS.

  ---

  A: The ALL_ROWS hint instructs the optimizer to optimize a statement block with a goal of best throughput, which is minimum total resource consumption.

  E: optimizer_mode=all_rows - This optimizer mode favors full-table scans (especially parallel full-table-scans) in cases where the server resources will be minimized. The all_rows mode is generally used during batch-oriented processing and for data warehouses where the goal is to minimize server resource consumption.

• Question 36:

  You are working on a database that supports an OLTP workload. You see a large number of hard parses occurring and several almost identical SQL statements in the library cache that vary only in the literal values in the WHERE clause conditions.

  Which two methods can you use to reduce hard parsing?

  A. Replace literals with bind variables and evolve a baseline for the statement.
  B. Use the RESULT_CACHE hint in the queries.
  C. Create baselines for the almost identical SQL statement by manually loading them from the cursor cache.
  D. Set the CURSOR_SHARING parameter to SIMILAR.
  A. Replace literals with bind variables and evolve a baseline for the statement.
  D. Set the CURSOR_SHARING parameter to SIMILAR.

  ---

  A: We can reduce this Hard parsing by using bindvariables

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

  Note:

  A hard parse is when your SQL must be re-loaded into the shared pool. A hard parse is worse than a soft parse because of the overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be

  completely re-checked for syntax and semantics and an executable generated.

  Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.

  See the cursor_sharing parameter for a easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.

  Reference: Oracle Database Reference, CURSOR_SHARING

• Question 37:

  See the code fragment:

  

  You receive the following error message:

  ORA-12827: insufficient parallel query slaves available

  Which three parameter settings could you change to avoid this error?

  A. Decrease the value of PARALLEL_MIN_PERCENT
  B. Increase the value of PARALLEL_MAX_SERVERS
  C. Increase the value of PARALLEL_MIN_SERVERS
  D. Reduce the value of PARALLEL_MIN_TIME_THRESHOLF
  E. Increase the value of PARALLEL_DEGREE_LIMIT
  F. Set the PARALLEL_DEGREE_POLICY = AUTO
  G. Set the PARALLEL_DEGREE_POLICY = LIMITED
  A. Decrease the value of PARALLEL_MIN_PERCENT
  B. Increase the value of PARALLEL_MAX_SERVERS
  G. Set the PARALLEL_DEGREE_POLICY = LIMITED

  ---

  A: 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

  B: Your query doesn't run because you've told Oracle not to run it unless at least 5% of the parallel execution processes are available for your query.

  Set PARALLEL_MIN_PERCENT=0 or increase the number of parallel execution processes by increasing the PARALLEL_MAX_SERVERS parameter.

  G: PARALLEL_DEGREE_POLICY

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

  LIMITED

  Enables automatic degree of parallelism for some statements but statement queuing and in- memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated

  explicitly with the PARALLEL clause. Tables and indexes that have a degree of parallelism specified will use that degree of parallelism.

  Note:

  PARALLEL_MIN_PERCENT operates in conjunction with PARALLEL_MAX_SERVERS and PARALLEL_MIN_SERVERS. It lets you specify the minimum percentage of parallel execution processes (of the value of

  PARALLEL_MAX_SERVERS) required for parallel execution. Setting this parameter ensures that parallel operations will not execute sequentially unless adequate resources are available. The default value of 0 means that no minimum

  percentage of processes has been set.

  Consider the following settings:

  PARALLEL_MIN_PERCENT = 50

  PARALLEL_MIN_SERVERS = 5

  PARALLEL_MAX_SERVERS = 10

  If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met.

  You can use this parameter in conjunction with PARALLEL_ADAPTIVE_MULTI_USER. In a multi-user environment, an individual user or application can set PARALLEL_MIN_PERCENT to a minimum value until sufficient resources are

  available on the system and an acceptable degree of parallelism is returned.

• Question 38:

  You are logged in as the HR user and you execute the following procedure:

  SQL > exec DBMS_STATS.SET_TABLE_PREFS (`HR', `EMPLOYEES', `PUBLISH', `FALSE');

  SQL> exec DBMS_STATS.GATHER_TABLE_STATS (`HR', `EMPLOYEES');

  Which statement is true about the newly gathered statistics?

  A. They are temporary and purged when the session exits.
  B. They are used by the optimizer for all sessions.
  C. They are locked and cannot be overwritten.
  D. They are marked as pending and stored in the pending statistics table.
  D. They are marked as pending and stored in the pending statistics table.

  ---

  In previous database versions, new optimizer statistics were automatically published when they were gathered. In 11g this is still the default action, but you now have the option of keeping the newly gathered statistics in a pending state until you choose to publish them.

  The DBMS_STATS.GET_PREFS function allows you to check the 'PUBLISH' attribute to see if statistics are automatically published. The default value of TRUE means they are automatically published, while FALSE indicates they are held in a pending state.

  Reference: Statistics Collection Enhancements in Oracle Database 11g Release 1, Pending Statistics

• Question 39:

  Examine the exhibit to view the query and its execution plan?

  

  What two statements are true?

  A. The HASH GROUP BY operation is the consumer of the HASH operation.
  B. The HASH operation is the consumer of the HASH GROUP BY operation.
  C. The HASH GROUP BY operation is the consumer of the TABLE ACCESS FULL operation for the CUSTOMER table.
  D. The HASH GROUP BY operation is consumer of the TABLE ACCESS FULL operation for the SALES table.
  E. The SALES table scan is a producer for the HASH JOIN operation.
  A. The HASH GROUP BY operation is the consumer of the HASH operation.
  E. The SALES table scan is a producer for the HASH JOIN operation.

  ---

  A, not C, not D: Line 3, HASH GROUP BY, consumes line 6 (HASH JOIN BUFFERED).

  E: Line 14, TABLE ACCESS FULL (Sales), is one of the two producers for line 6 (HASH JOIN).

• Question 40:

  Examine the execution plan: Which two are true regarding the execution plan?

  

  A. The CUSTOMERS table is hash partitioned.
  B. The SALES table is hash partitioned.
  C. The CUSTOMERS table is scanned first and selected partitions from the SALES table are scanned based on the BLOOM Filter created during the scan of the CUSTOMERS table.
  D. The SALES table is scanned first and selected partitions from the CUSTOMERS table are scanned based on the Bloom Filter created during the scan of the SALES table.
  E. Both the CUSTOMERS and SALES tables are scanned simultaneously and rows from the CUSTOMERS table are joined to row of the SALES table.
  F. The CUSTOMERS table is range partitioned.
  B. The SALES table is hash partitioned.
  C. The CUSTOMERS table is scanned first and selected partitions from the SALES table are scanned based on the BLOOM Filter created during the scan of the CUSTOMERS table.

  ---

  B: As per line 14 and 15.

  C: As per exhibit line 13 is execute before line 15.

  Incorrect:

  Not E: As per line 13 and 15 they are not executed simultaneously.