Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 41:

  Examine the utilization parameters for an instance:

  

  You notice that despite having an index on the column used in the where clause, queries use full table scans with highly selective filters. What are two possible reasons for the optimizer to use full table scans instead of index unique scans and index range scans?

  A. The OPTIMIZER_MODE parameter is set to ALL_ROWS.
  B. The clustering factor for the indexes is high.
  C. The number of leaf blocks for the indexes is high.
  D. The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to 100.
  E. The blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
  D. The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to 100.
  E. The blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.

  ---

  D: OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly--that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

  The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

  E: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation. g release 2, the default value of this parameter is a value that As of Oracle Database 10 corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.

  Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value.

  Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.

  Note:

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

• Question 42:

  While tuning a SQL statement, the SQL Tuning Advisor finds an existing SQL profile for a statement that has stale statistics. Automatic optimizer statistics is enabled for the database. What does the optimizer do in this situation?

  A. Updates the existing SQL profiles for which the statistics are stale.
  B. Makes the statistics information available to GATHER_DATABASE_STATS_JOB_PROC
  C. Starts the statistics collection process by running GATHER_STATS_JOB
  D. Writes a warning message in the alert log file
  B. Makes the statistics information available to GATHER_DATABASE_STATS_JOB_PROC

  ---

  Automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. This internal procedure operates similarly to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that GATHER_DATABASE_STATS_JOB_PROCprioritizes database objects that require statistics, so that objects that most need updated statistics are processed first, before the maintenance window closes.

  Note:

  *

  The optimizer relies on object statistics to generate execution plans. If these statistics are stale or missing, then the optimizer does not have the necessary information it needs and can generate poor execution plans. The Automatic Tuning

  Optimizer checks each query object for missing or stale statistics, and produces two types of output:

  / Recommendations to gather relevant statistics for objects with stale or no statistics

  Because optimizer statistics are automatically collected and refreshed, this problem occurs only when automatic optimizer statistics collection is disabled. See "Managing Automatic Optimizer Statistics Collection".

  / Auxiliary statistics for objects with no statistics, and statistic adjustment factor for objects with stale statistics

  The database stores this auxiliary information in an object called a SQL profile.

  *

  Oracle recommends that you enable automatic optimizer statistics collection. In this case, the database automatically collects optimizer statistics for tables with absent or stale statistics. If fresh statistics are required for a table, then the database collects them both for the table and associated indexes.

  Automatic collection eliminates many manual tasks associated with managing the optimizer. It also significantly reduces the risks of generating poor execution plans because of missing or stale statistics.

  Automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. This internal procedure operates similarly to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that GATHER_DATABASE_STATS_JOB_PROC prioritizes database objects that require statistics, so that objects that most need updated statistics are processed first, before the maintenance window closes. Reference: Oracle Database Performance Tuning Guide, Managing Automatic Optimizer Statistics Collection

• Question 43:

  Which three statements are true about histograms?

  A. They capture the distribution of different values in an index for better selectivity estimates.
  B. They can be used only with indexed columns.
  C. They provide metadata about distribution of and occurrences of values in a table column.
  D. They provide improved selectivity estimates in the presence of data skew, resulting in execution plans with uniform distribution.
  E. They help the optimizer in deciding whether to use an index or a full table scan.
  F. They help the optimizer to determine the fastest table join order.
  C. They provide metadata about distribution of and occurrences of values in a table column.
  E. They help the optimizer in deciding whether to use an index or a full table scan.
  F. They help the optimizer to determine the fastest table join order.

  ---

  C: A histogram is a frequency distribution (metadata) that describes the distribution of data values within a table.

  E: It's well established that histograms are very useful for helping the optimizer choose between a full-scan and and index-scan.

  F: Histograms may help the Oracle optimizer in deciding whether to use an index vs. a full-table scan (where index values are skewed) or help the optimizer determine the fastest table join order. For determining the best table join order, the

  WHERE clause of the query can be inspected along with the execution plan for the original query. If the cardinality of the table is too-high, then histograms on the most selective column in the WHERE clause will tip-off the optimizer and

  change the table join order.

  Note:

  * The Oracle Query Optimizer uses histograms to predict better query plans. The ANALYZE command or DBMS_STATS package can be used to compute these histograms.

  Incorrect:

  B: Histograms are NOT just for indexed columns.

  Adding a histogram to an un-indexed column that is used in

  a where clause can improve performance.

  D: Histograms Opportunities

  Any column used in a where clause with skewed data

  Columns that are not queried all the time

  Reduced overhead for insert, update, delete

• Question 44:

  You are administering a database supporting an OLTP application. The application runs a series of extremely similar queries the MYSALES table where the value of CUST_ID changes. Examine Exhibit1 to view the query and its execution plan.

  

  Examine Exhibit 2 to view the structure and indexes for the MYSALES table. The MYSALES table has 4 million records.

  

  Data in the CUST_ID column is highly skewed.

  Examine the parameters set for the instance:

  

  Which action would you like to make the query use the best plan for the selectivity?

  A. Decrease the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.
  B. Us the /*+ INDEX(CUST_ID_IDX) */ hint in the query.
  C. Drop the existing B* -tree index and re-create it as a bitmapped index on the CUST_ID column.
  D. Collect histogram statistics for the CUST_ID column and use a bind variable instead of literal values.
  D. Collect histogram statistics for the CUST_ID column and use a bind variable instead of literal values.

  ---

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

  A column histogram should only be created when we have data skew exists or is suspected.

• Question 45:

  Examine the following command:

  

  Which query transformation technique is used by the optimizer in this case?

  A. View merging
  B. Filter push-down
  C. Predicate pushing
  D. Predicate move-around
  C. Predicate pushing

  ---

  In predicate pushing, the optimizer "pushes" the relevant predicates from the containing query block into the view query block. For views that are not merged, this technique improves the subplan of the unmerged view because the database

  can use the pushed-in predicates to access indexes or to use as filters.

  For example, suppose you create a view that references two employee tables. The view is defined with a compound query that uses the UNION set operator, as follows:

  CREATE VIEW all_employees_vw AS

  ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees ) UNION

  ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers ); You then query the view as follows:

  SELECT last_name

  FROM all_employees_vw

  WHERE department_id = 50;

  Because the view is a compound query, the optimizer cannot merge the view's query into the accessing query block. Instead, the optimizer can transform the accessing statement by pushing its predicate, the WHERE clause condition

  department_id=50, into the view's compound query.

  The equivalent transformed query is as follows:

  SELECT last_name

  FROM ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees WHERE department_id=50

  UNION

  SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers WHERE department_id=50 );

  Reference: Oracle Database Performance Tuning Guide, Predicate Pushing

• Question 46:

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

  ---

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

  View Exhibit1 and examine the structure and indexes for the MYSALES table.

  

  The application uses the MYSALES table to insert sales record. But this table is also extensively used for generating sales reports. The PROD_ID and CUST_ID columns are frequently used in the WHERE clause of the queries. These columns have few distinct values relative to the total number of rows in the table. The MYSALES table has 4.5 million rows.

  View exhibit 2 and examine one of the queries and its autotrace output.

  

  Which two methods can examine one of the queries and its autotrace output?

  A. Drop the current standard balanced B* Tree indexes on the CUST_ID and PROD_ID columns and re-create as bitmapped indexes.
  B. Use the INDEX_COMBINE hint in the query.
  C. Create a composite index involving the CUST_ID and PROD_ID columns.
  D. Rebuild the index to rearrange the index blocks to have more rows per block by decreasing the value for PCTFRE attribute.
  E. Collect histogram statistics for the CUST_ID and PROD_ID columns.
  B. Use the INDEX_COMBINE hint in the query.
  C. Create a composite index involving the CUST_ID and PROD_ID columns.

  ---

  B: The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for the combination of multiple indexes, because it is a more versatile hint.

  C: Combining the CUST_ID and PROD_ID columns into an composite index would improve performance.

• Question 48:

  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 using different services. You enabled the statistics gathering for the service by using the following command:

  SQL > EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE (`APPS1', NULL, NULL);

  Which two statements are true regarding statistics gathered for APPS1 service?

  A. The statistics are gathered for all the modules and actions within the service.
  B. The statistics are collected at the session level for all sessions connected using the service.
  C. The statistics are aggregated and stored in the V$SERV_MOD_ACT_STATS view.
  D. The statistics are gathered for all the modules using the service only when DBMS_APPLICATION_INFO.SET_MODULE is executed to register with the service.
  E. Statistics gathering is enabled only for the subsequent sessions using the service.
  F. The statistics are gathered for all the applications using the service only when DBMS_APPLICATION_INFO.SET_ACTION is executed to register with the service.
  A. The statistics are gathered for all the modules and actions within the service.
  C. The statistics are aggregated and stored in the V$SERV_MOD_ACT_STATS view.

  ---

  SERV_MOD_ACT_STAT_ENABLE Procedure

  This procedure enables statistic gathering for a given combination of Service Name, MODULE and ACTION. Calling this procedure enables statistic gathering for a hierarchical combination of Service name, MODULE name, and ACTION name on all instances for the same database. Statistics are accessible by means of the V$SERV_MOD_ACT_STATS view.

  Note:

  * Syntax

  DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(

  service_name IN VARCHAR2,

  module_name IN VARCHAR2,

  action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);

  Parameter, Description

  service_name

  Name of the service for which statistic aggregation is enabled

  module_name

  Name of the MODULE. An additional qualifier for the service. It is a required parameter.

  action_name

  Name of the ACTION. An additional qualifier for the Service and MODULE name. Omitting the parameter (or supplying ALL_ACTIONS constant) means enabling aggregation for all Actions for a given Server/Module combination. In this case,

  statistics are aggregated on the module level.

  Reference: Oracle Database PL/SQL Packages and Types Reference 11g, SERV_MOD_ACT_STAT_ENABLE Procedure

• Question 49:

  In Your Database, The Cursor_Shareing Parameter is set to EXACT. In the Employees table, the data is significantly skewed in the DEPTNO column. The value 10 is found in 97% of rows.

  Examine the following command and out put.

  

  Which three statements are correct?

  A. The DEPTNO column will become bind aware once histogram statistics are collected.
  B. The value for the bind variable will considered by the optimizer to determine the execution plan.
  C. The same execution plan will always be used irrespective of the bind variable value.
  D. The instance collects statistics and based on the pattern of executions creates a histogram on the column containing the bind value.
  E. Bind peeking will take place only for the first execution of the statement and subsequent execution will use the same plan.
  A. The DEPTNO column will become bind aware once histogram statistics are collected.
  B. The value for the bind variable will considered by the optimizer to determine the execution plan.
  D. The instance collects statistics and based on the pattern of executions creates a histogram on the column containing the bind value.

  ---

  *

  We here see that the cursor is marked as bind sensitive (IS_BIND_SEN is Y).

  *

  In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value.

  A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend * on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to

  determine if a different plan for different bind values is called for.

  *

  (B, not C): A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind

  values, to determine if a different plan for different bind values is called for.

  Note: Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.

  Reference: Why are there more cursors in 11g for my query containing bind variables?

• Question 50:

  You are administering a database supporting an OLTP workload where the users perform frequent queries for fetching a new rows as possible, involving join operations on recently inserted data. In addition at night, a few DSS queries are also performed. Examine the initialization parameters for the instance:

  

  Which two options would you use for the optimizer?

  A. Set the OPTIMIZER_MODE initialization parameter to FIRST_ROWS_n.
  B. Add the hint ALL_ROWS in the DOS queries.
  C. Set the OPTIMIZER_INDEX_CACHING initialization parameter to 0.
  D. Add a hint INDEX_COMBINE in all DSS queries.
  E. Set the OPTIMIZER_INDEX_COST_ADJ initialization parameter to 100.
  A. Set the OPTIMIZER_MODE initialization parameter to FIRST_ROWS_n.
  E. Set the OPTIMIZER_INDEX_COST_ADJ initialization parameter to 100.

  ---

  The last appended rows are more likely to be found quickly with FIRST_ROWS_n.

  E: Make it not to prioritize index instead if table scan. OPTIMIZER_INDEX_COST_ADJ

  OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly--that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

  The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.