Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 21:

  Which two statements are true about the trcsess utility?

  A. It merges multiple trace files and produces a formatted output file.
  B. It merges multiple trace files from a particular session into one single trace file.
  C. It produces multiple files only for DBA sessions, which can be consolidated into one formatted file using the tkprof utility.
  D. It produces multiple files for a service, which can be consolidated into one formatted file using the tkprof utility.
  E. It merges files pertaining to a user session scattered across different processes in a shared server configuration.
  A. It merges multiple trace files and produces a formatted output file.
  B. It merges multiple trace files from a particular session into one single trace file.

  ---

  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.

  Note:

  *

  trcsess is useful for consolidating the tracing of a particular session for performance or debugging purposes. Tracing a specific session is usually not a problem in the dedicated server model as a single dedicated process serves a session during its lifetime. You can see the trace information for the session from the trace file belonging to the dedicated server serving it. However, in a shared server configuration a user session is serviced by different processes from time to time. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This makes it difficult to get a complete picture of the life cycle of a session.

  Reference: Oracle Database Performance Tuning Guide 11g , Using the trcsess Utility

  *

  Now there is a new tool, a command line utility called trcsess to help read the trace files. The trcsess command-line utility consolidates trace information from selected trace files, based on specified criteria. The criteria include session id, client id, service name, action name and module name.

• Question 22:

  You want to run SQL Tuning Advisor statements that are not captured by ADDM, AWR, and are not in the library cache. What is the prerequisite?

  A. Enable SQL plan management
  B. Create a SQL plan baseline for each query
  C. Create a SQL Tuning Set (STS) containing the SQL statements
  D. Gather statistics for objects used in the application
  C. Create a SQL Tuning Set (STS) containing the SQL statements

  ---

  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

• Question 23:

  You executed the following statements:

  

  Which two statements are true about the query execution?

  A. The execution plan is generated and fetched from the library cache.
  B. The query executes and displays the execution plan and statistics.
  C. The query executes and inserts the execution plan in PLAN_TABLE.
  D. The query executes and execution plan is stored in the library cache and can be viewed using v$SQL_PLAN.
  E. The query will always use the plan displayed by the AUTOTRACE output.
  B. The query executes and displays the execution plan and statistics.
  D. The query executes and execution plan is stored in the library cache and can be viewed using v$SQL_PLAN.

  ---

  B: set autotrace traceonly: Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.

  Note:

  / Autotrace

  Autotrace can be configured to run the SQL and gives a plan and statistics afterwards or just give you an explain plan without executing the query. To achieve this use the following:

  *

  Explain only

  set autotrace traceonly explain

  *

  Execute with stats and explain plan

  set autotrace on explain stat (with data returned by query)

  or

  autotrace traceo expl stat (without data returned by query)

  *

  To make the output from an autotrace more readable

  col plan_plus_exp format a100

  *

  Turn off autotrace

  set autotrace off

  / V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache. http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3054.htm#REFRN30250

• Question 24:

  See the table below:

  

  All parallel execution servers are available and sessions use default settings for parallelism. Which three are true about parallel execution in your instance?

  A. Parallel execution occurs when estimated serial execution time exceeds the minimum time threshold.
  B. Parallel execution occurs for all DML statements.
  C. Parallel execution occurs for those statements that access tables with dictionary DOP defined.
  D. Parallel execution occurs for those statements that access tables with no dictionary DOP defined.
  E. Parallel execution occurs for all DDL statements.
  A. Parallel execution occurs when estimated serial execution time exceeds the minimum time threshold.
  C. Parallel execution occurs for those statements that access tables with dictionary DOP defined.
  D. Parallel execution occurs for those statements that access tables with no dictionary DOP defined.

  ---

  A (not B, Not E): PARALLEL_MIN_TIME_THRESHOLD : Oracle 11gR2 will ascertain if the query's estimated execution time is likely to run longer than the acceptable value (in seconds) for pARALLEL_MIN_TIME_THRESHOLD and, if

  sufficient resources for parallel execution exist right now, it will allow the query to execute; otherwise, it will delay its execution until sufficient resources exist. This helps prevent a single parallel query from consuming excessive resources at

  the cost of other non-parallelizable operations. The default of this parameter is 10 seconds. C, D: In earlier versions of the Oracle Database, we had to determine the DOP more or less manually, either with a parallel hint or by setting a

  parallel degree with alter table. There was an automatic computation of the DOP available for the objects with dictionary DOP of default, derived from the simple formula CPU_COUNT * PARALLEL_THREADS_PER_CPU. If there were

  insufficient parallel servers to satisfy the requested DOP, one of three things could occur:

  / The SQL would be run at a reduced DOP (be downgraded)

  / The SQL would run in serial mode (be serialized)

  / If PARALLEL_MIN_PERCENT was specified and less than the nominated percentage of the DOP was achievable, then the the SQL statement might terminate with "ORA-12827:

  insufficient parallel query slaves available".

  Note:

  *

  PARALLEL_DEGREE_POLICY. It can have 3 values : MANUAL, LIMITED and AUTO

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

  *

  Oracle supports parallel processing for a wide range of operations, including queries, DDL and DML:

  ?Queries that involve table or index range scans.

  ?Bulk insert, update or delete operations.

  ?Table and index creation.

  *

  Oracle's parallel execution framework enables you to either explicitly chose - or even enforce - a specific degree of parallelism (DOP) or to rely on Oracle to control it.

  *

  Three modes are available to request a DOP :

  default fixed adaptive

  *

  The DOP is determined in the following priority order: hint session table and limited by the Oracle Database Resource Manager (DBRM) settings.

  Reference: AUTOMATIC DEGREE OF PARALLELISM (DOP) IN ORACLE 11G R2

• Question 25:

  You executed the following statement:

  

  Which three statements are true about EXPLAIN PLAN?

  A. The execution plan is saved in PLAN_TABLE without executing the query.
  B. The execution plan for the query is generated and displayed immediately as the output.
  C. The execution plan generated may not necessarily be the execution plan used during query execution.
  D. The execution plan is saved in DBA_HIST_SQL_PLAN without executing the query.
  E. The execution plan generated can be viewed using the DBMS_XPLAIN.DISPLAY function.
  F. The execution plan generated can be fetched from the library cache by using the DBMS_XPLAIN.DISPLAY function.
  A. The execution plan is saved in PLAN_TABLE without executing the query.
  C. The execution plan generated may not necessarily be the execution plan used during query execution.
  E. The execution plan generated can be viewed using the DBMS_XPLAIN.DISPLAY function.

  ---

  *

  (A, not D): The explain plan process stores data in the PLAN_TABLE.

  *

  EXPLAIN PLAN

  The EXPLAIN PLAN method doesn't require the query to be run (A), greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE.

  E: Use the DBMS_XPLAN.DISPLAY function to display the execution plan.

  * The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views.

  Note:

  *

  First the query must be explained.

  SQL> EXPLAIN PLAN FOR

  2 SELECT *

  3 FROM emp e, dept d

  4 WHERE e.deptno = d.deptno

  5 AND e.ename = 'SMITH';

  Explained.

  SQL>

  Then the execution plan displayed. (not B)

  SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

  Plan Table

  -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | | | | | | | NESTED LOOPS | | | | | | |

  | TABLE ACCESS FULL |EMP | | | | | |

  | TABLE ACCESS BY INDEX RO|DEPT | | | | | |

  | INDEX UNIQUE SCAN |PK_DEPT | | | | | |

  8 rows selected.

  SQL>

  For parallel queries use the "utlxplp.sql" script instead of "utlxpls.sql".

• Question 26:

  You ran a high load SQL statement that used an index through the SQL Tuning Advisor and accepted its recommendation for SQL profile creation. Subsequently you noticed that there has been a 2% growth in number of rows in the tables

  used by the SQL statement and database statistics have also been refreshed.

  How does this impact the created SQL profile?

  A. It becomes invalid and no longer used the optimizer.
  B. It remains valid and ensures that the optimizer always use the execution plan that was created before the changes happened.
  C. It remains and allows the optimizer to pick a different plan required.
  D. It becomes invalid and a new SQL profile is created for the statement by the auto tuning task.
  C. It remains and allows the optimizer to pick a different plan required.

• Question 27:

  Examine the parallelism parameters for your instance:

  

  Examine the Exhibit to view the query and its explain plan output.

  

  All sessions use default parallelism settings.

  What two steps could you take to make the query execute in parallel?

  A. Add a parallel hint.
  B. Decrease the value of PARALLEL_MIN_TIMETHRESHOLD.
  C. Increase the value of PARALLEL_MIN_SERVERS.
  D. Increase the value of PARALLEL_MAX_SERVERS.
  E. Decrease the value of PARALLEL_MIN_PERCENT.
  A. Add a parallel hint.
  B. Decrease the value of PARALLEL_MIN_TIMETHRESHOLD.

  ---

  A: You can rely on hints to set the degree of parallelism, but these can be hard to set correctly.

  B:

  Decision tree for query parallelization

  

• Question 28:

  Which statement is true about the usage of the STAR_TRANSFORMATION hint in a query?

  A. The optimizer always uses a plan in which the transformation is used.
  B. The optimizer uses transformation only if the cost is less than a query executing without transformation.
  C. The optimizer always generates subqueries to transform a query.
  D. The optimizer always uses bitmap indexes on the primary key column for any dimension table to transform a query.
  B. The optimizer uses transformation only if the cost is less than a query executing without transformation.

  ---

  (http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50508) says that "Even if the hint is specified, there is no guarantee that the transformation will take place".

• Question 29:

  Which statement is true about an SQL plan baselines that are fixed?

  A. New plans are added automatically by the optimizer to the baseline and are automatically evolved.
  B. New, better plans are added automatically as a fixed plan baseline.
  C. New plan can be manually loaded to the baseline from the cursor cache or a SQL tuning set.
  D. New plans can be added as fixed plans to the baseline by using the SQL Tuning Advisor to generate a SQL profile and by accepting the SQL profile.
  D. New plans can be added as fixed plans to the baseline by using the SQL Tuning Advisor to generate a SQL profile and by accepting the SQL profile.

  ---

  When a SQL statement with a fixed SQL plan baseline is tuned using the SQL Tuning Advisor, a SQL profile recommendation has special meaning. When the SQL profile is accepted, the tuned plan is added to the fixed SQL plan baseline as

  a non-fixed plan. However, as described above, the optimizer will not use the tuned plan as long as a reproducible fixed plan is present. Therefore, the benefit of SQL tuning may not be realized. To enable the use of the tuned plan, manually

  alter the tuned plan to a fixed plan by setting its FIXED attribute to YES.

  Note:

  It is also possible to influence the optimizer's choice of plan when it is selecting a plan from a SQL plan baseline. SQL plan baselines can be marked as fixed. Fixed SQL plan baselines indicate to the optimizer that they are preferred. If the

  optimizer is costing SQL plan baselines and one of the plans is fixed, the optimizer will only cost the fixed plan and go with that if it is reproducible.

  If the fixed plan(s) are not reproducible the optimizer will go back and cost the remaining SQL plan baselines and select the one with the lowest cost. Note that costing a plan is nowhere near as expensive as a hard parse. The optimizer is not

  looking at all possible access methods but at one specific access path.

  Reference: Oracle Database Performance Tuning Guide 11g, Using Fixed SQL Plan Baselines Reference: SQL Plan Management in Oracle Database 11g

• Question 30:

  Examine the Exhibit to view the structure of an indexes for the SALES table.

  

  The SALES table has 4594215 rows. The CUST_ID column has 2079 distinct values.

  What would you do to influence the optimizer for better selectivity?

  A. Drop bitmap index and create balanced B*Tree index on the CUST_ID column.
  B. Create a height-balanced histogram for the CUST_ID column.
  C. Gather statistics for the indexes on the SALES table.
  D. Use the ALL_ROWS hint in the query.
  D. Use the ALL_ROWS hint in the query.

  ---

  OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

  Values:

  FIRST_ROWS_N - The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).

  FIRST_ROWS - The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

  ALL_ROWS - The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).