Oracle Oracle Certifications 1Z0-117 Questions & Answers

• Question 91:

  A database supports three applications: CRM, ERP, and ACC. These applications connect to the database by using three different services: CRM_SRV for the

  CRM application, ERP_SRV for the ERP application, and ACC_SRV for the ACC application.

  You enable tracing for the ACC_SRV service by issuing the following command:

  SQL> EXECUTE DBMS for the ACC_SRV service by issuing the following command:

  SQL> EXECUITIVE DBMS_MONITOR. SERV_MOD_ACT_TRACE_ENABLE

  (service_name => `ACC_SRV', waits => TRUE, binds = > FALSE, instance_name = > `inst1');

  Which statement is true?

  A. All trace information for the service connection to inst1 will be stored in a single trace file.

  B. A trace file is not created because the module name is not specified.

  C. A single trace file is created for each session that uses the ACC_SRV service.

  D. Only those SQL statements that are identified with the ACC_SRV service executed on the inst1 instance are recorded in trace files.

  E. All trace information for the ACC_SRV service connected to inst1 is stored in multiple trace files, which can be consolidated by using the tkprof utility.

  Correct Answer: C

  SERV_MOD_ACT_TRACE_ENABLE

  serv_mod_act_trace_enable and serv_mod_act_trace_disable, which enables and disables trace for given service_name, module and action.

  For example for a given service name you can trace all session started from SQL*Plus. Module and action in your own created application can be set using

  dbms_application_info set_module and set_action procedures.

  serv_mod_act_trace_enable fills sys table wri$_tracing_enabled and view dba_enabled_traces on top of this table as follows:

  SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'orcl', module_name=>'SQL*Plus')

  PL/SQL procedure successfully completed.

  SQL> select * from sys.wri$_tracing_enabled;

  TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 INSTANCE_NAME

  FLAGS

  ---------- ---------- ------------- ------------- ------------- ----- 4 orcl SQL*Plus 8

  SQL> select * from dba_enabled_traces;

  TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS

  INSTANCE_NAME

  -------------- ---------- ------------- ------------- ----- ----- ------------- SERVICE_MODULE orcl SQL*Plus TRUE FALSE

• Question 92:

  Examine the exhibit.

  

  Which is true based on the information obtainable from the execution plan?

  A. A full partition-wise join performed between the EMPLOYEES and DEPARTMENTS tables.

  B. A full table scan on the DEPARTMENTS table performed serially by the query coordinator.

  C. A full table scan on the DEPARTMENTS table is performed serially by a single parallel execution server process.

  D. A partial partition-wise join performed between the EMPLOYEES and DEPARTMENTS tables.

  E. A full table scan on the EMPLOYEES table is done in parallel.

  Correct Answer: E

  PX BLOCK ITERATOR This operation is typically the first step in a parallel pipeline. The BLOCK ITERATOR breaks up the table into chunks that are processed by

  each of the parallel servers involved.

  Incorrect:

  B, C: The scan on the Departsments table is done in parallel.

  Note:

  * As per exhibit: Line 7 is run first, followed by line 6.

  *

  Example with same structure of execution plan:

  

  Here's how to read the plan:

  1.

  The first thing done is at line 9 an index fast full scan on SYS.OBJ$.I_OBJ1 index. This is done in parallel, as indicated from the "PX SEND" line above.

  2.

  In line 8, we're doing a "PX SEND BROADCAST" operation. When joining tables in parallel, Oracle can choose to either broadcast results (rows) from one operation to apply to the other table scan, or it can choose PX SEND HASH. In this case, our CBO determined that a BROADCOAST was appropriate because the results from the OBJ$ table were much lower than the MYOBJ table

  3.

  Line 7, the PX RECEIVE step, is basically the consumer of the broadcasted rows in step 8

  4.

  Line 6 is an in-memory BUFFER SORT of the rows returned from the index scan on OBJ$

  5.

  Lines 11 and 10, respectively, indicate the full scan and PX BOCK ITERATOR operation for the granules involved in the 8 PQ servers

  6.

  In line 5, Oracle is doing a hash join on the resulting rows from the parallel scans on MYOBJ and OBJ$

  7.

  Line 4 is a per-PQ server sort of data from the joind PQ servers

  8.

  Line 3 is the consumer QC that holds the result of the each of the PQ servers

  9.

  Line 2 is the PX Coordinator (QC) collecting, or consuming the rows of the joined data

  10.

  Line 1 is the final SORT AGGREGATE line that performs the grouping function

• Question 93:

  You enable auto degree of parallelism (DOP) for your database instance.

  Examine the following query:

  

  Which two are true about the execution of statement?

  A. Dictionary DOP for the objects accessed by the query is used to determine the statement DOP.

  B. Auto DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.

  C. Dictionary DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.

  D. The statement will be queued if insufficient parallel execution slaves are available to satisfy the statements DOP.

  E. The statement will be queued if the number of busy parallel execution servers exceeds PARALLEL_SERVERS_TARGET.

  F. The statements may execute serially.

  Correct Answer: AF

  *

  Parallel (Manual): The optimizer is forced to use the parallel settings of the objects in the state- ment.

  *

  MANUAL - This is the default. Disables Auto DOP (not B), statement queuing (not D, Not E) and in-memory parallel execution. It reverts the behavior of parallel

  execution to what it was pre- vious to Oracle Database 11g, Release 2 (11.2).

  *

  PARELLEL (MANUAL)

  You can use the PARALLEL hint to force parallelism. It takes an optional parameter: the DOP at which the statement should run.

  The following example forces the statement to use Oracle Database 11g Release 1 (11.1) behav- ior:

  SELECT /*+ parallel(manual) */ ename, dname FROM emp e, dept d WHERE e.deptno=d.deptno;

  *

  PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARAL- LEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require paral- lel execution, if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than PARALLEL_SERVER_TARGET.

  By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the sys- tem with parallel server processes.

  Note that all serial (non-parallel) statements will execute immediately even if statement queuing has been activated.

• Question 94:

  You are logged in as the HR user and you execute the following procedure:

  SQL > exec DBMS_STATS.SET_TABLE_PREFS (`HR', `EMPLOYEES', `PUBLISH', `FALSE'); SQL> exec DBMS_STATS.GATHER_TABLE_STATS (`HR', `EMPLOYEES');

  Which statement is true about the newly gathered statistics?

  A. They are temporary and purged when the session exits.

  B. They are used by the optimizer for all sessions.

  C. They are locked and cannot be overwritten.

  D. They are marked as pending and stored in the pending statistics table.

  Correct Answer: D

  In previous database versions, new optimizer statistics were automatically published when they were gathered. In 11g this is still the default action, but you now have the option of keeping the newly gathered statistics in a pending state until you choose to publish them.

  The DBMS_STATS.GET_PREFS function allows you to check the 'PUBLISH' attribute to see if statistics are automatically published. The default value of TRUE means they are automatically published, while FALSE indicates they are held in a pending state.

  Reference: Statistics Collection Enhancements in Oracle Database 11g Release 1, Pending Statistics

• Question 95:

  You are administering database that supports an OLTP workloads. Most of the queries use an in- dex range scan or index unique scan as access methods.

  Which three scenarios can prevent the index access being used by the queries?

  A. When highly selective filters is applied on an indexed column of a table with sparsely popu- lated blocks.

  B. When the rows are filtered with an IS NULL operator on the column with a unique key de- fined

  C. When the histogram statistics are not collected for the columns used in where clause.

  D. When a highly selective filter is applied on the indexed column and the index has very low value for clustering factor.

  E. When the statistics for the table are not current.

  Correct Answer: ABE

  A: Low clustering factor promotes good performance.

  The clustering_factor measures how synchronized an index is with the data in a table. A table with a high clustering factor is out-of-sequence with the rows and

  large index range scans will consume lots of I/O. Conversely, an index with a low clustering_factor is closely aligned with the table and related rows reside

  together of each data block, making indexes very desirable for optimal access.

  Note:

  *

  Oracle SQL not using an index is a common complaint, and it's often because the optimizer thinks that a full-scan is cheaper than index access. Oracle not using an index can be due to:

  *

  (E) Bad/incomplete statistics Make sure to re-analyze the table and index with dbms_stats to ensure that the optimizer has good metadata.

  *

  Wrong optimizer_mode The first_rows optimizer mode is to minimize response time, and it is more likely to use an index than the default all_rows mode.

  *

  Bugs See these important notes on optimizer changes in 10g that cause Oracle not to use an index.

  *

  Cost adjustment In some cases, the optimizer will still not use an index, and you must de- crease optimizer_index_cost_adj.

• Question 96:

  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.

  Correct Answer: AD

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

• Question 97:

  View the 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 are frequently used in WHERE clause of the queries. These columns have few distinct values relative to the total number of rows in the table.

  View exhibit 2 and examine one of the queries and its auto trace output. What should you do to improve the performance of the query?

  A. Use the INDEX_COMBINE hint in the query.

  B. Create composite index involving the CUST_ID and PROD_ID columns.

  C. Gather histograms statistics for the CUST_ID and PROD_ID columns.

  D. Gather index statistics for the MYSALES_PRODID_IDX and MYSALES_CUSTID_IDX indexes.

  Correct Answer: D

  Note:

  *

  Statistics quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

  *

  INDEX_COMBINE Forces a bitmap index access path on tab. Primarily this hint just tells Oracle to use the bitmap indexes on table tab. Otherwise Oracle will choose the best combination of indexes it can think of based on the statistics. If it is ignoring a bitmap index that you think would be helpful, you may specify that index plus all of the others taht you want to be used. Note that this does not force the use of those indexes, Oracle will still make cost based choices.

  *

  Histograms Opportunities Any column used in a where clause with skewed data Histograms are NOT just for indexed columns.

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

• Question 98:

  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.

  Correct Answer: C

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

  Tracing has been enabled for the HR user. You execute the following command to check the contents of the orcl_25052.trc trace file, which was generated during tracing:

  

  Which two statements are correct about the execution of the command?

  A. SCRIPT.SQL stores the statistics for all traced SWL statements.

  B. Execution plans for SQL statements are stored in TEMP_PLAN_TABLE and can be queried by the user.

  C. SQL statements in the output files are stored in the order of elapsed time.

  D. TKPROF use TEMP_PLAN_TABLE in the HR schema as a temporary plan table.

  E. Recursive SQL statements are included in the output file.

  Correct Answer: AD

  INSERT Creates a SQL script that stores the trace file statistics in the database. TKPROF creates this script with the name filename3. This script creates a table and inserts a row of statistics for each traced SQL statement into the table.

• Question 100:

  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.

  Correct Answer: DE

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