Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 81:

  Examine the initializing parameters:

  

  An index exists on the column used in the WHERE of a query. You execute the query for the first time today and notice that the query is not using the index. The CUSTOMERS table has 55000 rows. View the exhibit and examine the query and its execution plan.

  

  What can be the two reasons for full table scan?

  A. The value of the OPTIMIZER_INDEX_COST_ADJ parameter is set to a low value.
  B. The blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
  C. The statistics for the CUSTOMERS table and the indexes stale.
  D. The OPTIMIZER_MODE parameter is set to ALL_ROWS.
  E. Histogram statistics for CUST_CITY_ID are missing.
  F. Average number of rows per block for the CUSTOMERS table is low.
  C. The statistics for the CUSTOMERS table and the indexes stale.
  E. Histogram statistics for CUST_CITY_ID are missing.

  ---

  C: Old statistics could cause this problem. "Histograms are feature in CBO and it helps to optimizer to determine how data are skewed(distributed) with in the column. Histogram is good to create for the column which are included in the WHERE clause where the column is highly skewed. Histogram helps to

  optimizer to decide whether to use an index or full-table scan or help the optimizer " determine the fastest table join order.

• Question 82:

  You have created some materialized views to improve the performance of several queries.

  Which four must be true to enable sessions to benefit from improved response time made possible by these materialized views?

  A. Query rewrite must be enabled for the sessions.
  B. Bitmap indexes must exist on all the columns involved in the join operations for the defining query of the MVIEWs.
  C. All or part of the query results must be obtainable from one or more MVIEWs.
  D. Bitmap join indexes must exist on all the columns involved in the join operations.
  E. Session users must have query rewrite privilege.
  F. The MVIEWs must be enabled for query rewrite.
  G. All or part of the query results must be obtainable from one MVIEW.
  A. Query rewrite must be enabled for the sessions.
  B. Bitmap indexes must exist on all the columns involved in the join operations for the defining query of the MVIEWs.
  C. All or part of the query results must be obtainable from one or more MVIEWs.
  F. The MVIEWs must be enabled for query rewrite.

  ---

  A: For a given user's session, ALTER SESSION can be used to disable or enable query rewrite for that session only.

  B: Bitmap indexes on the join columns would improve performance.

  C (not G) : One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or

  more materialized views that are defined on the detail tables.

  F:

  *

  A materialized view is only eligible for query rewrite if the ENABLE QUERY REWRITE clause has been specified, either initially when the materialized view was first created or subsequently with an ALTER MATERIALIZED VIEW statement.

  *

  Enabling or disabling query rewrite:

  by the CREATE or ALTER statement for individual materialized views by the initialization parameter QUERY_REWRITE_ENABLED by the REWRITE and NOREWRITE hints in SQL statements

  Note:

  *

  A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are

  continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.

• Question 83:

  Examine the parallelism parameters for you instance.

  

  Now examine the DSS_PLAN with parallel statement directives:

  

  Which two are true about the DSS_PLAN resource manager plan?

  A. URGENT_GROUPS sessions will always be dequeued before sessions from other groups.
  B. OTHER_GROUPS sessions are queued for maximum of six minutes.
  C. ETL_GROUP sessions can collectively consume 64 parallel execution servers before queuing starts for this consumer group.
  D. An ETL_GRP sessions will be switched to URGENT_GROUPS if the session requests more than eight parallel executions servers.
  E. URGENT_GROUP sessions will not be queued if 64 parallel execution servers are busy because their PARALLEL_TARGET_PERCENTAGE is not specified.
  A. URGENT_GROUPS sessions will always be dequeued before sessions from other groups.
  B. OTHER_GROUPS sessions are queued for maximum of six minutes.

  ---

  B: PARALLEL_QUEUE_TIMEOUT Parallel Queue Timeout

  When you use parallel statement queuing, if the database does not have sufficient resources to execute a parallel statement, the statement is queued until the required resources become available. However, there is a chance that a parallel

  statement may be waiting in the parallel statement queue for longer than is desired. You can prevent such scenarios by specifying the maximum time a parallel statement can wait in the parallel statement queue.

  The PARALLEL_QUEUE_TIMEOUT directive attribute enables you to specify the maximum time, in seconds, that a parallel statement can wait in the parallel statement queue before it is timed out. The PARALLEL_QUEUE_TIMEOUT

  attribute can be set for each consumer group.

  Incorrect:

  Not C: ETL_GROUP PARALLEL_TARGET_PERCENTAGE is 50%. So ETL_GROUP can only consume 32 servers.

  Note:

  *

  If you want more per-workload management, you must use the following directive attributes: / MGMT_Pn Management attributes control how a parallel statement is selected from the parallel statement queue for execution. You can prioritize the parallel statements of one consumer group over another by setting a higher value for the management

  attributes of that group. / PARALLEL_TARGET_PERCENTAGE / PARALLEL_QUEUE_TIMEOUT / PARALLEL_DEGREE_LIMIT_P1

  *

  PARALLEL_DEGREE_LIMIT_P1 Degree of Parallelism Limit

  You can limit the maximum degree of parallelism for any operation within a consumer group. The degree of parallelism is the number of parallel execution servers that are associated with a single operation. Use the PARALLEL_DEGREE_LIMIT_P1 directive attribute to specify the degree of parallelism for a consumer group.

  The degree of parallelism limit applies to one operation within a consumer group; it does not limit the total degree of parallelism across all operations within the consumer group. However, you can combine both the PARALLEL_DEGREE_LIMIT_P1 and the PARALLEL_TARGET_PERCENTAGE directive attributes to achieve the desired control.

  * PARALLEL_TARGET_PERCENTAGE Parallel Target Percentage

  It is possible for a single consumer group to launch enough parallel statements to use all the available parallel servers. If this happens, when a high-priority parallel statement from a different consumer group is run, no parallel servers are available to allocate to this group. You can avoid such a scenario by limiting the number of parallel servers that can be used by a particular consumer group.

  Use the PARALLEL_TARGET_PERCENTAGE directive attribute to specify the maximum percentage of the parallel server pool that a particular consumer group can use. The number of parallel servers used by a particular consumer group is counted as the sum of the parallel servers used by all sessions in that consumer group.

• Question 84:

  You are administering a database that supports an OLTP application. To set statistics preferences, you issued the following command:

  SQL > DBMS_STATS.SET_GLOBAL_PREFS (`ESTIMATE_PERCENT', `9');

  What will be the effect of executing this procedure?

  A. It will influence the gathering of statistics for a table based on the value specified for ESTIMATE_PERCENT provided on table preferences for the same table exist.
  B. It will influence dynamic sampling for a query to estimate the statistics based on ESTIMATE_PERCENT.
  C. The automatic statistics gathering job running in the maintenance window will use global preferences unless table preferences for the same table exist.
  D. New objects created will use global preference even if table preferences are specified.
  C. The automatic statistics gathering job running in the maintenance window will use global preferences unless table preferences for the same table exist.

  ---

  Note:

  *

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

  *

  The SET_GLOBAL_PREFS procedure e is used to set the global statistics preferences.

  *

  ESTIMATE_PERCENT - The value determines the percentage of rows to estimate. The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.

• Question 85:

  View the code sequence:

  

  Examine the Exhibit to view the execution plan.

  

  Which two statements are true about the query execution?

  A. The optimizer joins specified tables in the order as they appear in the FROM clause.
  B. The CUSTOMERS and ORDERS tables are joined first and the resultant is then joined with rows returned by the ORDER_ITEMS table.
  C. The CUSTOMERS and ORDER_ITEMS tables are joined first the resultant is then joined with rows by the ORDERS table.
  D. The optimizer has chosen hash join as an access method as the OPTIMIZER_MODE parameter is set to FIRST_ROWS.
  C. The CUSTOMERS and ORDER_ITEMS tables are joined first the resultant is then joined with rows by the ORDERS table.
  D. The optimizer has chosen hash join as an access method as the OPTIMIZER_MODE parameter is set to FIRST_ROWS.

  ---

  The first executed join is in line 6. The second executed join is in line 1.

  Incorrect:

  A: Line 7 and 8 are executed first.

• Question 86:

  Which three statements are true the Automatic Tuning Optimizer (ATO)?

  A. It identifies the objects with stale or missing statistics and gathers statistics automatically.
  B. It investigates the effect of new or modified indexes on the access paths for a workload and recommends running that statistics through the SQL Access Advisor.
  C. It recommends a SQL profile to help create a better execution plan.
  D. It picks up resource-intensive SQL statements from the ADDM and recommends the use of materialized views to improve query performance.
  E. It identifies the syntactic, semantic, or design problems with structure of SQL statements leading to poor performance and suggests restricting the statements.
  F. It identifies resource-intensive SQL statements, runs them through the SQL Tuning Advisor, and implements the recommendations automatically.
  A. It identifies the objects with stale or missing statistics and gathers statistics automatically.
  D. It picks up resource-intensive SQL statements from the ADDM and recommends the use of materialized views to improve query performance.
  F. It identifies resource-intensive SQL statements, runs them through the SQL Tuning Advisor, and implements the recommendations automatically.

  ---

  Under tuning mode, the optimizer can take several minutes to tune a single statement. It is both time and resource intensive to invoke Automatic Tuning Optimizer every time a query must be hard-parsed. Automatic Tuning Optimizer is meant for complex and high-load SQL statements that have nontrivial impact on the database.

  Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL state- ments that are good candidates for SQL tuning. The automatic SQL tuning feature also automati- cally identifies problematic SQL statements and implements tuning recommendations during system maintenance windows as an automated maintenance task.

  The Automatic Tuning Optimizer performs the following types of tuning analysis:

  Statistics Analysis SQL Profiling Access Path Analysis SQL Structure Analysis Alternative Plan Analysis

  Note:

  * Oracle Database uses the optimizer to generate the execution plans for submitted SQL state- ments. The optimizer operates in the following modes:

  Normal mode The optimizer compiles the SQL and generates an execution plan. The normal mode generates a reasonable plan for the vast majority of SQL statements. Under normal mode, the optimizer op- erates with very strict time constraints, usually a fraction of a second.

  Tuning mode

  The optimizer performs additional analysis to check whether it can further improve the plan pro- duced in normal mode. The optimizer output is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly better plan. When running in tuning mode, the optimizer is known as the Automatic Tuning Optimizer.

• Question 87:

  A new application module is deployed on middle tier and is connecting to your database. You want to monitor the performance of the SQL statements generated from the application. To accomplish this, identify the required steps in the correct order from the steps given below:

  1.

  Use DBNMS_APPLICATION_INFO to set the name of the module

  2.

  Use DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE to enable statistics gathering for the module.

  3.

  Use DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE to enable tracing for the service

  4.

  Use the trcsess utility to consolidate the trace files generated.

  5.

  Use the tkprof utility to convert the trace files into formatted output.

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

  ---

  Note:

  *

  Before tracing can be enabled, the environment must first be configured to enable gathering of statistics.

  *

  (gather statistics): DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE Enables statistic gathering for a given combination of Service Name, MODULE and ACTION

  *

  DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE

  Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified.

  dbms_monitor.serv_mod_act_trace_enable(

  service_name IN VARCHAR2,

  module_name IN VARCHAR2 DEFAULT ANY_MODULE,

  action_name IN VARCHAR2 DEFAULT ANY_ACTION,

  waits IN BOOLEAN DEFAULT TRUE,

  binds IN BOOLEAN DEFAULT FALSE,

  instance_name IN VARCHAR2 DEFAULT NULL,

  plan_stat IN VARCHAR2 DEFAULT NULL);

  SELECT instance_name

  FROM gv$instance;

  exec dbms_monitor.serv_mod_act_trace_enable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, TRUE, TRUE, 'orabase');

  exec dbms_monitor.serv_mod_act_trace_disable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, 'orabase');

  *

  When solving tuning problems, session traces are very useful and offer vital information. Traces are simple and straightforward for dedicated server sessions, but for shared server sessions, many processes are involved. 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. 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.

  *

  Once the trace files have been consolidated (with trcsess), tkprof can be run against the consolidated trace file for reporting purposes.

• Question 88:

  Examine the initialization parameters for a database an OLTP overload.

  

  What is the effect of changing the value of the parameter?

  A. It influences the optimizer to always use the value of the parameter?
  B. It influences the optimizer to use indexes instead of full table scans as the estimated cost of the using index is reduced.
  C. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.
  D. It influenced the optimizer to use bitmap indexes as the estimated cost conversion from bimap is rowed us reduced.
  B. It influences the optimizer to use indexes instead of full table scans as the estimated cost of the using index is reduced.

  ---

  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.

• Question 89:

  Examine the Exhibit to view the structure of and indexes for the EMPLOYEES and DEPARTMENTS tables:

  

  EXAMINE the SQL statement and its execution plan:

  

  Which two statements are correct regarding the execution plan?

  A. Step 2 is performing nested operation on JOB_ID column of the JOBS table, which is the driven table and the EMPLOYEES table is the driven table.
  B. In step 2 for every row returned by the JOBS table matching rows from the EMPLOYEES table are accessed.
  C. Step 1 is performing nested loop operation on the DEPARTMENT_ID column of the DEPARTMENTS table, which is the driven table and results returned by step 2 in the driving resultset.
  D. The performance of the query can be improved by creating bitmap index on the JOB_ID column of the EMPLOYEES table.
  E. The performance of the query can be improved by creating bitmapped index on the DEPARTMENT_ID column of the EMPLOYEES table.
  B. In step 2 for every row returned by the JOBS table matching rows from the EMPLOYEES table are accessed.
  E. The performance of the query can be improved by creating bitmapped index on the DEPARTMENT_ID column of the EMPLOYEES table.

  ---

  As per exhibit:

  B, not A, Not C: First is line 5 executed, followed by line 4, followed by line 3.

  Step 2 is line 4.

  E: The Department_ID column has lower cardinality compared to the JOB_ID column, so it is better suited for a bitmapped index.

  Note:

  *

  Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table.

  At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.

  *

  The real benefit of bitmapped indexing occurs when one table includes multiple bitmapped indexes. Each individual column may have low cardinality. The creation of multiple bitmapped indexes provides a very powerful method for rapidly answering difficult SQL queries.

  *

  Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table.

  At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.

• Question 90:

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

  ---

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