Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 71:

  Which two types of column filtering may benefit from partition pruning?

  A. Equally operates on range-partitioned tables.
  B. In-list operators on system-partitioned tables
  C. Equality operators on system-partitioned tables
  D. Operators on range-partitioned tables
  E. Greater than operators on hash-partitioned tables
  A. Equally operates on range-partitioned tables.
  D. Operators on range-partitioned tables

  ---

  The query optimizer can perform pruning whenever a WHERE condition can be reduced to either one of the following two cases:

  partition_column = constant

  partition_column IN (constant1, constant2, ..., constantN)

  In the first case, the optimizer simply evaluates the partitioning expression for the value given, determines which partition contains that value, and scans only this partition. In many cases, the equal sign can be replaced with another arithmetic comparison, including <, >, <=, >=, and <>. Some queries using BETWEEN in the WHERE clause can also take advantage of partition pruning.

  Note:

  *

  The core concept behind partition pruning is relatively simple, and can be described as "Do not scan partitions where there can be no matching values".

  When the optimizer can make use of partition pruning in performing a query, execution of the query can be an order of magnitude faster than the same query against a nonpartitioned table containing the same column definitions and data.

  *

  Example:

  Suppose that you have a partitioned table t1 defined by this statement:

  CREATE TABLE t1 (

  fname VARCHAR(50) NOT NULL,

  lname VARCHAR(50) NOT NULL,

  region_code TINYINT UNSIGNED NOT NULL,

  dob DATE NOT NULL

  )

  PARTITION BY RANGE( region_code ) (

  PARTITION p0 VALUES LESS THAN (64),

  PARTITION p1 VALUES LESS THAN (128),

  PARTITION p2 VALUES LESS THAN (192),

  PARTITION p3 VALUES LESS THAN MAXVALUE

  );

  Consider the case where you wish to obtain results from a query such as this one:

  SELECT fname, lname, region_code, dob

  FROM t1

  WHERE region_code > 125 AND region_code < 130;

  p0 or

  It is easy to see that none of the rows which ought to be returned will be in either of the partitions p3; that is, we need to search only in partitions p1 and p2 to find matching rows. By doing so, it is possible to expend much less time and effort

  in finding matching rows than would be required to scan all partitions "cutting away" of unneeded partitions is known as pruning.

  in the table. This

• Question 72:

  Which two statements are true about index full scans?

  A. An index fast full scan multi block I/O to read the index structure in its entirely.
  B. Index nodes are not retrieved in the index order, and there fore the nodes are not in sequence.
  C. An index fast full scan reads the index block by block.
  D. An index fast full scan reads the whole index from the lowest value to the higher value.
  A. An index fast full scan multi block I/O to read the index structure in its entirely.
  B. Index nodes are not retrieved in the index order, and there fore the nodes are not in sequence.

  ---

  A: To speed table and index block access, Oracle uses the db_file_multiblock_read_count parameter (which defaults to 8) to aid in getting full-table scan and full- index scan data blocks into the data buffer cache as fast as possible.

  B: The index nodes are not retrieved in index order, the rows will not be sequenced.

  Note:

  there are some requirements for Oracle to invoke the fast full-index scan.

  All of the columns required must be specified in the index. That is, all columns in the select and where clauses must exist in the index.

  The query returns more than 10 percent of the rows within the index. This 10 percent figure depends on the degree of multi-block reads and the degree of parallelism.

  You are counting the number of rows in a table that meet a specific criterion. The fast full-index scan is almost always used for count(*) operations.

  Reference: index fast full scan tips

• Question 73:

  Examine Exhibit1 to view the query and its AUTOTRACE output.

  

  Which two statements are true about tracing?

  A. The displayed plan will be stored in PLAN_TABLE.
  B. Subsequent execution of this statement will use the displayed plan that is stored in v$SQL.
  C. The displayed plan may not necessarily be used by the optimizer.
  D. The query will not fetch any rows; it will display only the execution plan and statistics.
  E. The execution plan generated can be viewed from v$SQLAREA.
  A. The displayed plan will be stored in PLAN_TABLE.
  D. The query will not fetch any rows; it will display only the execution plan and statistics.

  ---

  The PLAN_TABLE is automatically created as a public synonym to a global temporary table. This temporary table holds the output of EXPLAIN PLAN statements for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans

• Question 74:

  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.
  E. Unlock and gather statistics for the table after inserts are done and lock them again.

  ---

  *

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

  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.
  B. Use dynamically sampling hint for the queries on frequently updated tables.
  C. Create histogram for the columns used frequently in the WHERE clause.

  ---

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

  Examine the statements being executed for the first time:

  

  Steps followed by a SQL statement during parsing:

  1.

  Search for a similar statement in the shared pool.

  2.

  Search for an identical statement in the shared pool.

  3.

  Search the SQL area of identical statement already in the shared pool.

  4.

  Proceed through the remaining steps of the parse phase to ensure that the execution plan of the existing statements is applicable to the view statement.

  5.

  Perform hard parsing.

  6.

  Share the SQL area of the similar statement already in the shared pool.

  Identify the required steps in the correct sequence used by the third query.

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

  ---

  2) before 5).

  Note:

  * When application code is run, Oracle attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the statement does exist in the library cache and it can be shared, then Oracle reuses the existing code. This is known as a soft parse, or a library cache hit. If Oracle is unable to use existing code, then a new executable version of the application code must be built. This is known as a hard parse, or a library cache miss.

  Reference: Oracle Database Performance Tuning Guide, SQL Sharing Criteria

• Question 77:

  Examine the Exhibit1 to view the structure of an indexes for the EMPLOYEES table.

  

  Examine the query:

  SQL> SELECT * FROM employees WHERE employees_id IN (7876, 7900, 7902);

  EMPLOYEE_ID is a primary key in the EMPLOYEES table that has 50000 rows.

  Which statement is true regarding the execution of the query?

  A. The query uses an index skip scan on the EMP_EMP_ID_PK index to fetch the rows.
  B. The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using an index range scan on the EMP_EMP_ID_PK index.
  C. The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using a fast full index scan on the EMP_EMP_ID_PK index.
  D. The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using an index unique scan on the EMP_EMP_ID_PK index.
  E. The query uses a fast full index scan on the EMP_EMP_ID_PK index fetch the rows.
  B. The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using an index range scan on the EMP_EMP_ID_PK index.

  ---

  How the CBO Evaluates IN-List Iterators

  The IN-list iterator is used when a query contains an IN clause with values. The execution plan is identical to what would result for a statement with an equality clause instead of IN except for one additional step. That extra step occurs when the IN-list iterator feeds the equality clause with unique values from the IN-list.

  Both of the statements in Example 2-1 and Example 2-1 are equivalent and produce the same plan.

  Example 2-1 IN-List Iterators Initial Statement

  SELECT header_id, line_id, revenue_amount FROM so_lines_all WHERE header_id IN (1011,1012,1013);

  SELECT header_id, line_id, revenue_amount FROM so_lines_all WHERE header_id = 1011 OR header_id = 1012 OR header_id = 1013;

  Plan

  SELECT STATEMENT INLIST ITERATOR TABLE ACCESS BY INDEX ROWID SO_LINES_ALL INDEX RANGE SCAN SO_LINES_N1

  Reference: Database Performance Tuning Guide and Reference

• Question 78:

  View the exhibit and examine the findings provided by the SQL Tuning Advisor for SELECT Statement.

  

  A SWL plan baseline already exists for the execution plan.

  What two methods can you use to ensure that an alternate plan becomes an accepted plan?

  A. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function.
  B. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function.
  C. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function and run the DBMS_STATS to manually refresh stale statistics.
  D. Use the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function.
  C. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function and run the DBMS_STATS to manually refresh stale statistics.
  D. Use the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function.

  ---

  C: To adopt an alternative plan regardless of whether SQL Tuning Advisor recommends it, call DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE. You can use this procedure to create a SQL plan baseline on any existing reproducible

  plan.

  D: LOAD_PLANS_FROM_SQLSET Function

  This function loads plans stored in a SQL tuning set (STS) into SQL plan baselines. The plans loaded from STS are not verified for performance but added as accepted plans to existing or new SQL plan baselines. This procedure can be used

  to seed SQL management base with new SQL plan baselines.

  Note:

  * While tuning a SQL statement, SQL Tuning Advisor searches real-time and historical performance data for alternative execution plans for the statement. If plans other than the original plan exist, then SQL Tuning Advisor reports an

  alternative plan finding.

  SQL Tuning Advisor validates the alternative execution plans and notes any plans that are not reproducible. When reproducible alternative plans are found, you can create a SQL plan baseline to instruct the optimizer to choose these plans in

  the future.

  Incorrect:

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

  Usage Notes

  When a single plan is specified, one of various statuses, or plan name, or description can be altered. When all plans for a SQL statement are specified, one of various statuses, or description can be altered. This function can be called

  numerous times, each time setting a different plan attribute of same plan(s) or different plan(s).

  Reference: Oracle Database Performance Tuning Guide, Alternative Plan Analysis

• Question 79:

  An application issues many expensive join aggregations type queries.

  Examine the Exhibit to view the queries.

  

  Which two could help improve the performance of these SQL statements without changing application code?

  A. Create B*-Tree indexes on the join columns.
  B. Create a materialized view with query rewrite enabled for the first statement and nested MVIEWs for the other statements.
  C. Collect histogram statistics on columns for which aggregating functions are performed.
  D. Create an STS for these queries and use SQL Access Advisor, which may generate advice about MVIEWs.
  E. Create an STS for these queries and use SQL Performance Analyzer, which may generate advice about MVIEWs.
  B. Create a materialized view with query rewrite enabled for the first statement and nested MVIEWs for the other statements.
  D. Create an STS for these queries and use SQL Access Advisor, which may generate advice about MVIEWs.

  ---

  B: Materialized views and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries.

  D:

  *

  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.

  *

  SQL Access Advisor:

  Materialized views and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries. The SQL Access Advisor helps you achieve your performance goals by recommending the proper

  set of materialized views, materialized view logs, and indexes for a given workload. Understanding and using these structures is essential when optimizing SQL as they can result in significant performance improvements in data retrieval. The

  advantages, however, do not come without a cost. Creation and maintenance of these objects can be time consuming, and space requirements can be significant.

  The SQL Access Advisor recommends bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. B-tree indexes are most commonly used in a data warehouse to index unique or near-unique keys.

  Note:

  *

  Conventional wisdom holds that bitmap indexes are most appropriate for columns having low distinct values--such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not completely accurate, however. In reality, a bitmap index is always advisable for systems in which data is not frequently updated by many concurrent systems. A bitmap index on a column with 100-percent unique values (a column candidate for primary key) is as efficient as a B-tree index.

  *

  By default, the Oracle creates a b_tree index. In a b-tree, you walk the branches until you get to the node that has the data you want to use. In the classic b-tree structure, there are branches from the top that lead to leaf nodes that contain the data.

  Incorrect: not E: SQL Performance Analyzer enables you to assess the performance impact of any system change resulting in changes to SQL execution plans and performance characteristics. Examples of common system changes for which you can use SQL Performance Analyzer include: Database upgrade Configuration changes to the operating system, hardware, or database Database initialization parameter changes Schema changes, for example, adding new indexes or materialized views Gathering optimizer statistics Validating SQL tuning actions, for example, creating SQL profiles or implementing partitioning

• Question 80:

  Which three factors does the estimator depend on for overall cost estimation of a given execution plan?

  A. Cardinality
  B. Sort area size
  C. OPTIMIZER_FEATURE_ENABLE parameter
  D. NOT NULL_FEATURE_ENABLE parameter
  E. NOT NULL constraint on a unique key column
  F. Library cache size
  G. The units of work such as disk input/output, CPU usage, and memory used in an operation
  A. Cardinality
  C. OPTIMIZER_FEATURE_ENABLE parameter
  G. The units of work such as disk input/output, CPU usage, and memory used in an operation

  ---

  C: OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.

  Note: The estimator determines the overall cost of a given execution plan. The estimator generates three different types of measures to achieve this goal:

  *

  Selectivity

  This measure represents a fraction of rows from a row set. The selectivity is tied to a query predicate, such as last_name='Smith', or a combination of predicates.

  *

  Cardinality

  This measure represents the number of rows in a row set. 1,

  *

  Cost

  This measure represents units of work or resource used. The query optimizer uses disk I/O, CPU usage, and memory usage as units of work.

  If statistics are available, then the estimator uses them to compute the measures. The statistics improve the degree of accuracy of the measures.