Oracle Oracle Certifications 1Z0-117 Questions & Answers

• Question 21:

  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.

  Correct Answer: B

  (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 22:

  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

  Correct Answer: D

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

  Examine the query:

  

  The RESULT_CACHE_MODE parameter is set to MANUAL for the database.

  Which two statements are true about the usage of the result cache?

  A. The SQL runtime environment checks whether the query result is cached in the result cache; if the result exists, the optimizer fetches the result from it.

  B. The SQL runtime environment does check for the query result in the result cache because the RESULT_CACHE_MODE parameter is set to MANUAL.

  C. The SQL runtime environment checks for the query result in the result cache only when the query is executed for the second time.

  D. If the query result does not exist in the cache and the query is executed, the result is generated as output, and also sorted in the result cache.

  Correct Answer: AD

  Note:

  *

  result_cache_mode: the result cache can be enabled in three ways: via hint, alter session or alter system. Default is MANUAL which means that we need to explicitly request caching via the RESULT_CACHE hint;

  *

  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.

  *

  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.

• Question 24:

  You are administering a database that supports an OLTP workload in which one of the applications inserts rows in a table until 12 noon every, after which multiple years perform frequent queries on the table. You want the statistics to be more representative of the table population.

  What must be done to ensure that an optimizer uses the latest statistics of the table?

  A. Set the STALE_PERCENT preference to 0.

  B. Set the OPTIMIZER_MODE parameter to ALL_ROWS.

  C. Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.

  D. Use the FIRST_ROWS_n hint in the queries.

  E. Unlock and gather statistics for the table after inserts are done and lock them again.

  Correct Answer: E

  *

  For tables that are substantially modified in batch operations, such as with bulk loads, gather statistics on these tables as part of the batch operation. Call the DBMS_STATS procedure as soon as the load operation completes.

  * Statistics for a table or schema can be locked. After statistics are locked, you can make no modifications to the statistics until the statistics have been unlocked. Locking procedures are useful in a static environment in which you want to guarantee that the statistics never change.

  The DBMS_STATS package provides two procedures for locking (LOCK_SCHEMA_STATS and LOCK_TABLE_STATS) and two procedures for unlocking statistics (UNLOCK_SCHEMA_STATS andUNLOCK_TABLE_STATS).

  Incorrect:

  A: STALE_PERCENT cannot be set to 0.

  * With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.

  STALE_PERCENT - This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default value is 10%.

  B: Optimizer_mode applies to the database, not to a specific table.

  *

  Possible values for optimizer_mode = choose/ all_rows/ first_rows/ first_rows[n]

  Important facts about ALL_ROWS

  1.

  ALL_ROWS considers both index scan and full scan and based on their contribution to the overall query, it uses them. If Selectivity of a column is low, optimizer may use index to fetch the data (for example `where employee_code=7712'), but if selectivity of column is quite high ('where deptno=10'), optimizer may consider doing Full table scan. With ALL_ROWS, optimizer has more freedom to its job at its best.

  2.

  Good for OLAP system, where work happens in batches/procedures. (While some of the report may still use FIRST_ROWS depending upon the anxiety level of report reviewers)

  3.

  Likes hash joins over nested loop for larger data sets.

  C: Optimizer dynamic sampling refers to the ability of the SQL optimizer to take a sample of rows from a table to calculate missing statistics. Dynamic sampling can be controlled with the OPTIMIZER_DYNAMIC_SAMPLING parameter or the DYNAMIC_SAMPLING hint.

  level 0 - do not use dynamic sampling

  D: First_row_n cannot be used in this way.

• Question 25:

  You are administering a database that supports a DSS workload, where in an application a set of queries use the query rewrite on materialized views. You notice that these queries are performing poorly.

  Which two actions can you make to improve the performance of theses queries?

  A. Use DBMS_MVIEW.EXPLAIN_REWRITE to analyze whether the queries are rewritten.

  B. USE DBMS_ADVISOR.QUICK_TUNE to analyze the query rewrite usage of materialized views for the entire workload.

  C. Create an STS for all the queries and use SQL performance Analyzer to generate recommendations for determining the regressed SQL statements.

  D. Create an STS for all the queries in the application and use the SQL Tuning Advisor to generate recommendations.

  E. Create an STS for all the queries in the application and use the SQL Access Advisor to generate a recommendation for optimizing materialized views for maximum query rewrite usage and fast refresh.

  Correct Answer: DE

  http://docs.oracle.com/cd/E11882_01/server.112/e41573/advisor.htm#PFGRF94911

• Question 26:

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

  

  Identify the two correct interpretations that can be made from the execution plan.

  A. The DEPT table is driving table and the EMP table join is the driven table.

  B. Rows from the DEPT table are first hashed by the join key into memory and then joined to the EMP table on the join key.

  C. The EMP table is the driving table and the DEPT table us the driven table.

  D. The rows from the DEPT table are sorted first by the join key and then hashed into memory.

  E. Rows from both the tables are sorted by the join key, but only rows from the DEPT table are hashed into memory.

  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.

• Question 27:

  You are administering a database that supports an OLTP workload. Automatic optimizer statistics collection is scheduled in the night maintenance window. Some of the tables get updated frequently during day time and you notice a performance degradation for queries using those tables due to stale statistics.

  Which two options might help to avoid the performance degradation of the queries?

  A. Set the global statistics preference STALE_PERCENT to 0.

  B. Use dynamically sampling hint for the queries on frequently updated tables.

  C. Create histogram for the columns used frequently in the WHERE clause.

  D. Gather statistics with global statistics preference NO_VALIDATE to TRUE.

  E. Set the OPTIMZER_USE_PENDING_STATISTICS parameter to TRUE.

  Correct Answer: BC

  B: Dynamic sampling first became available in Oracle9i Database Release 2. It is the ability of the cost-based optimizer (CBO) to sample the tables a query references during a hard parse, to determine better default statistics for unanalyzed segments, and to verify its "guesses." This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling.

  There are two ways to use dynamic sampling:

  The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER

  SESSION command.

  The DYNAMIC_SAMPLING query hint can be added to specific queries.

  C: A histogram is a collection of information about the distribution of values within a column.

  In some cases, the distribution of values within a column of a table will affect the optimizer's decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a full-table scan cheaper than index access.

  Histograms are also important for determinine the optimal table join order.

  Incorrect:

  A: Too much statistics would be gathered.

  Note: STALE_PERCENT - This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and

  should be regathered. The default value is 10%.

  D: In Oracle PL/SQL, the VALIDATE keyword defines the state of a constraint on a column in a table.

  E: OPTIMZER_USE_PENDING_STATISTICS specifies whether or not the optimizer uses pending statistics when compiling SQL statements.

• Question 28:

  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.

  Correct Answer: AD

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

  Examine the exhibit.

  

  Which two are true concerning the execution plan?

  A. No partition-wise join is used

  B. A full partition-wise join is used

  C. A partial partition-wise join is used

  D. The SALES table is composite partitioned

  Correct Answer: BD

  * The following example shows the execution plan for the full partition-wise join with the sales table range partitioned by time_id, and subpartitioned by hash on

  cust_id. ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop |IN-OUT| PQ Distrib |

  ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | |

  | 1 | PX COORDINATOR | | | | | |

  | 2 | PX SEND QC (RANDOM) | :TQ10001 | | | P->S | QC (RAND) |

  |* 3 | FILTER | | | | PCWC | |

  | 4 | HASH GROUP BY | | | | PCWP | |

  | 5 | PX RECEIVE | | | | PCWP | |

  | 6 | PX SEND HASH | :TQ10000 | | | P->P | HASH |

  | 7 | HASH GROUP BY | | | | PCWP | |

  | 8 | PX PARTITION HASH ALL | | 1 | 16 | PCWC | |

  |* 9 | HASH JOIN | | | | PCWP | |

  | 10 | TABLE ACCESS FULL | CUSTOMERS | 1 | 16 | PCWP | |

  | 11 | PX PARTITION RANGE ITERATOR| | 8 | 9 | PCWC | |

  |* 12 | TABLE ACCESS FULL | SALES | 113 | 144 | PCWP | |

  Predicate Information (identified by operation id):

  3 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*)),0))>100)

  9 - access("S"."CUST_ID"="C"."CUST_ID")

  12 - filter("S"."TIME_ID"<=TO_DATE(' 1999-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

  "S"."TIME_ID">=TO_DATE(' 1999-07-01

  00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  * Full partition-wise joins can occur if two tables that are co-partitioned on the same key are joined in a query. The tables can be co-partitioned at the partition level, or at the subpartition level, or at a combination of partition and subpartition levels. Reference partitioning is an easy way to guarantee co-partitioning. Full partition-wise joins can be executed in serial and in parallel.

  Reference: Oracle Database VLDB and Partitioning Guide, Full Partition-Wise Joins: Composite

  -Single-Level

• Question 30:

  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.

  Correct Answer: D

  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