Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 111:

  Examine the Exhibit.

  

  Which two options are true about the execution plan and the set of statements?

  A. The query uses a partial partition-wise join.
  B. The degree of parallelism is limited to the number of partitions in the EMP_RANGE_DID table.
  C. The DEPT table id dynamically distributed based on the partition keys of the EMP_RANGE_DID table.
  D. The server process serially scans the entire DEPT table for each range partition on the EMP_RANGE_DID table.
  E. The query uses a full partition-wise join.
  A. The query uses a partial partition-wise join.
  D. The server process serially scans the entire DEPT table for each range partition on the EMP_RANGE_DID table.

• Question 112:

  You need to upgrade you Oracle Database 10g to 11g. You want to ensure that the same SQL plans that are currently in use in the 10g database are used in the upgraded database initially, but new, better plans are allowed subsequently.

  Steps to accomplish the task:

  1.

  Set the OPTIMIZER_USE_SQL_BASELINE and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE.

  2.

  Bulk load the SQL Management Base as part of an upgrade using an STS containing the plans captured in Oracle Database 10g.

  3.

  Evolve the plan baseline using the DBMS_SPM.EVOLVE_PLAN_BASELINE procedure.

  4.

  Fix the plan baseline using the DBMS_SPM.ALTER_SQL_PLANBASELINE procedure.

  5.

  Accept new, better plans using the DBMS_SPM.ALTER_SQL_PLAN_BASELINE procedure and manually load them to the existing baseline.

  6.

  Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE.

  Identify the required steps.

  A. 1, 3, 4, 5
  B. 1, 6, 3, 4, 5
  C. 1, 2, 3, 5
  D. 1, 2, 3, 4
  E. 1, 6, 3
  F. 1 and 2
  F. 1 and 2

  ---

  *

  (1) OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES In Oracle Database 11g a new feature called SQL Plan Management (SPM) has been introduced to guarantees any plan changes that do occur lead to better performance. When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to TRUE (default FALSE) Oracle will automatically capture a SQL plan baseline for every repeatable SQL statement on the system. The execution plan found at parse time will be added to the SQL plan baseline as an accepted plan.

  *

  (2) Once you have completed the software upgrade, but before you restart the applications and allow users back on the system, you should populate SQL Plan Management (SPM) with the 10g execution plans you captured before the upgrade. Seeding SPM with the 10g execution plans ensures that the application will continue to use the same execution plans you had before the upgrade. Any new execution plans found in Oracle Database 11g will be recorded in the plan history for that statement but they will not be used. When you are ready you can evolve or verify the new plans and only implement those that perform better than the 10g plan.

  Incorrect:

  Not (3): DBMS_SPM.EVOLVE_PLAN_BASELINE is not used to evolve new plans.

  DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE should be used:

  It is possible to evolve a SQL statement's execution plan using Oracle Enterprise Manager or by running the command-line function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. U

  Note:

  * SQL plan management (SPM) ensures that runtime performance will never degrade due to the change of an execution plan. To guarantee this, only accepted (trusted) execution plans will be used; any plan will be tracked and evaluated at a

  later point in time and only accepted as verified if the new plan performs better than an accepted plan. SQL Plan Management has three main components:

  1.

  SQL plan baseline capture:

  Create SQL plan baselines that represents accepted execution plans for all relevant SQL statements. The SQL plan baselines are stored in a plan history inside the SQL Management Base in the SYSAUX tablespace.

  2.

  SQL plan baseline selection

  Ensure that only accepted execution plans are used for statements with a SQL plan baseline and track all new execution plans in the history for a statement as unaccepted plan. The plan history consists of accepted and unaccepted plans. An

  unaccepted plan can be unverified (newly found but not verified) or rejected (verified but not found to performant).

  3.

  SQL plan baseline evolution

  Evaluate all unverified execution plans for a given statement in the plan history to become either accepted or rejected

• Question 113:

  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
  B. A full partition-wise join is used
  D. The SALES table is composite partitioned

  ---

  * 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 114:

  You identified some DSS queries that perform expensive join and aggregation operations.

  The queries access historical data from noncurrent partition of the fact tables.

  What three actions could you perform to improve the response time of the queries without modifying the SQL statements?

  A. Set the QUERY_REWRITE_ENABLED to TRUE at the session level.
  B. Create an STS for the statements, run SQL Tuning Advisor for the STS, and implement any generated recommendations for materialized views.
  C. Set QUERY_REWRITE_ENABLED to TRUE at the instance level.
  D. Create an STS for the statements, run SQL Access Advisor for the STS, and implement any generated recommendations for materialized views.
  E. Set QUERY_REWRITE_INTEGRITY to ENFORCED at the instance level.
  B. Create an STS for the statements, run SQL Tuning Advisor for the STS, and implement any generated recommendations for materialized views.
  C. Set QUERY_REWRITE_ENABLED to TRUE at the instance level.
  D. Create an STS for the statements, run SQL Access Advisor for the STS, and implement any generated recommendations for materialized views.

  ---

  A: * QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database. Values:

  false

  Oracle does not use rewrite.

  true

  Oracle costs the query with rewrite and without rewrite and chooses the method with the lower cost.

  force

  Oracle always uses rewrite and does not evaluate the cost before doing so. Use force when you know that the query will always benefit from rewrite and when reduction in compile time is important.

  To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.

  C: You can use SQL Tuning Advisor to tune one or more SQL statements

  D: Using the SQL Access Advisor Wizard or API, you can do the following:

  Recommend materialized views and indexes based on collected or hypothetical workload information.

  Manage workloads.

  Mark, update, and remove recommendations.

  Note:

  *

  STS SQL tuning set.

  *

  A SQL Tuning Set is a database object that includes one or more SQL statements and their execution statistics and execution context. You can use the set as an input source for various advisors, such as SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Analyzer.

  Incorrect:

  E: QUERY_REWRITE_INTEGRITY determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on unenforced relationships.

  Values:

  enforced

  Oracle enforces and guarantees consistency and integrity.

  trusted Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle. stale_tolerated

  Oracle allows rewrites using unenforced relationships. Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data

• Question 115:

  Examine the parallelism parameter for your instance:

  

  parallel_servers_target

  Now examine the resource plan containing parallel statement directives:

  Consumer Group resource plan containing parallel statement directives:

  

  Which two are true about parallel statement queuing when this plan is active?

  A. Urgent_group sessions collectively can consume up to 64 parallel execution servers before queuing starts for this consumer group.
  B. ETL_GROUP sessions can collectively consume up to 64 parallel execution servers before the queuing starts for this consumer.
  C. A single OTHER_GROUPS session will execute serially once it is queued for six minutes.
  D. A single ETL_GROUP session can consume up to eight parallel execution servers.
  E. A single ETL_GROUP session can consume up to 32 parallel execution servers.
  F. A single OTHER_GROUPS session will execute in parallel once it is queued for six minutes.
  A. Urgent_group sessions collectively can consume up to 64 parallel execution servers before queuing starts for this consumer group.
  D. A single ETL_GROUP session can consume up to eight parallel execution servers.

  ---

  (http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN13466)

• Question 116:

  Examine the query and its execution plan:

  

  Which statement is true regarding the execution plan?

  A. This query first fetches rows from the CUSTOMERS table that satisfy the conditions, and then the join return NULL from the CUSTOMER_ID column when it does not find any corresponding rows in the ORDERS table.
  B. The query fetches rows from CUSTOMERS and ORDERS table simultaneously, and filters the rows that satisfy the conditions from the resultset.
  C. The query first fetches rows from the ORDERS table that satisfy the conditions, and then the join returns NULL form CUSTOMER_ID column when it does not find any corresponding rows in the CUSTOMERS table.
  D. The query first joins rows from the CUSTOMERS and ORDERS tables and returns NULL for the ORDERS table columns when it does not find any corresponding rows in the ORDERS table, and then fetches the rows that satisfy the conditions from the result set.
  A. This query first fetches rows from the CUSTOMERS table that satisfy the conditions, and then the join return NULL from the CUSTOMER_ID column when it does not find any corresponding rows in the ORDERS table.

• Question 117:

  How can you reduce fragmentation of an index without affecting the current transactions that are using the index?

  A. Use the ANALYZE INDEX . . . command
  B. Use the ALTER INDEX . . . VALIDATE STRUCTURE command
  C. Us the ALTER INDEX . . . REBUILD ONLINE command
  D. Use the ALTER INDEX . . . DEALLOCATE UNUSED command
  D. Use the ALTER INDEX . . . DEALLOCATE UNUSED command

  ---

  Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the index and make the freed space available for other segments in the tablespace.

  If index is range-partitioned or hash-partitioned, then Oracle Database deallocates unused space from each index partition. If index is a local index on a composite-partitioned table, then Oracle Database deallocates unused space from each index subpartition.

  Reference: Oracle Database SQL Language Reference 11g, alter index

• Question 118:

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

  ---

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

  Which two are the fastest methods for fetching a single row from a table based on an equality predicate?

  A. Fast full index scan on an index created for a column with unique key
  B. Index unique scan on an created for a column with unique key
  C. Row fetch from a single table hash cluster
  D. Index range scan on an index created from a column with primary key
  E. Row fetch from a table using rowid
  C. Row fetch from a single table hash cluster
  E. Row fetch from a table using rowid

  ---

  A scan is slower than a row fetch (from hash value or rowid).

• Question 120:

  See the table below: All execution servers are currently available and the sessions use defaults for all parallel settings.

  

  In which two cases will statements execute in parallel?

  A. When parallel hints are used but only if estimated serial execution takes more than 10 seconds.
  B. When parallelism is defined at the statement level.
  C. When the degree of parallelism is explicitly defined in the data dictionary for tables and indexes accessed by a query.
  D. Parallel DDL statements but only if estimated serial DDL execution time is greater than 10 seconds.
  E. When the degree of parallelism is explicitly defined for tables and indexes but only if estimated serial execution takes more than 10 seconds.
  B. When parallelism is defined at the statement level.
  C. When the degree of parallelism is explicitly defined in the data dictionary for tables and indexes accessed by a query.

  ---

  Incorrect:

  A, D, E: When PARALLEL_MIN_TIME_THRESHOLD is set to AUTO the PARALLEL_MIN_TIME_THRESHOLD is set to 30, not to 10. See note below.

  Note:

  * parallel_min_time_threshold

  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.