1Z0-117 | Downloadable Oracle 1Z0-117 dumps
Q11. Which two statements about In-Memory Parallel Execution are true?
A. It can be configured using the Database Resource Manager.
B. It increases the number of duplicate block images in the global buffer cache.
C. It requires setting PARALLEL_DEGREE_POLICY to LIMITED.
D. Objects selected for In-Memory Parallel Execution have blocks mapped to specific RAC instances.
E. It requires setting PARALLEL_DEGREE_POLICY to AUTO
F. Objects selected for In-Memory Parallel Execution must be partitioned tables or indexes.
Answer: D,E
Explanation:
D, E: In-Memory Parallel Execution
When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database decides if an object that is accessed using parallel execution would benefit from being cached in the SGA (also called the buffer cache). The decision to cache an object is based on a well-defined set of heuristics including the size of the object and frequency on which it is accessed. In an Oracle RAC environment, Oracle Database maps pieces of the object into each of the buffer caches on the active instances. By creating this mapping, Oracle Database automatically knows which buffer cache to access to find different parts or pieces of the object. Using this information, Oracle Database prevents multiple instances from reading the same information from disk over and over again, thus maximizing the amount of memory that can cache objects. If the size of the object is larger than the size of the buffer cache (single instance) or the size of the buffer cache multiplied by the number of active instances in an Oracle RAC cluster, then the object is read using direct-path reads.
E: PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.
AUTO Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
Incorrect:
C: LIMITED Enables automatic degree of parallelism for some statements but statement queuing and in-memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated explicitly with the PARALLEL clause. Tables and indexes that have a degree of parallelism specified will use that degree of parallelism.
Reference: Oracle Database VLDB and Partitioning Guide 11g, How Parallel Execution Works
Q12. 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.
Answer: B,C
Explanation:
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.
Q13. Refer to the Exhibit. Execution plan:
What must be the correct order of steps that the optimizer executes based on the ID column the execution plan?
A. 3, 5, 4, 6, 7
B. 3, 5, 4, 7, 6
C. 3, 4, 5, 7, 6
D. 4, 5, 3, 7, 6
Answer: B
Q14. 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.
Answer: C
Explanation: 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
Q15. Examine the exhibit to view the query and its execution plan?
What two statements are true?
A. The HASH GROUP BY operation is the consumer of the HASH operation.
B. The HASH operation is the consumer of the HASH GROUP BY operation.
C. The HASH GROUP BY operation is the consumer of the TABLE ACCESS FULL operation for the CUSTOMER table.
D. The HASH GROUP BY operation is consumer of the TABLE ACCESS FULL operation for the SALES table.
E. The SALES table scan is a producer for the HASH JOIN operation.
Answer: A,E
Explanation: A, not C, not D: Line 3, HASH GROUP BY, consumes line 6 (HASH JOIN BUFFERED).
E: Line 14, TABLE ACCESS FULL (Sales), is one of the two producers for line 6 (HASH JOIN).
Q16. Examine the execution plan:
Which two are true regarding the execution plan?
A. The CUSTOMERS table is hash partitioned.
B. The SALES table is hash partitioned.
C. The CUSTOMERS table is scanned first and selected partitions from the SALES table are scanned based on the BLOOM Filter created during the scan of the CUSTOMERS table.
D. The SALES table is scanned first and selected partitions from the CUSTOMERS table are scanned based on the Bloom Filter created during the scan of the SALES table.
E. Both the CUSTOMERS and SALES tables are scanned simultaneously and rows from the CUSTOMERS table are joined to row of the SALES table.
F. The CUSTOMERS table is range partitioned.
Answer: B,C
Explanation: B: As per line 14 and 15.
C: As per exhibit line 13 is execute before line 15.
Incorrect:
Not E: As per line 13 and 15 – they are not executed simultaneously.
Q17. Examine the following anonymous PL/SQL code block of code:
Which two are true concerning the use of this code?
A. The user executing the anonymous PL/SQL code must have the CREATE JOB system privilege.
B. ALTER SESSION ENABLE PARALLEL DML must be executed in the session prior to executing the anonymous PL/SQL code.
C. All chunks are committed together once all tasks updating all chunks are finished.
D. The user executing the anonymous PL/SQL code requires execute privilege on the DBMS_JOB package.
E. The user executing the anonymous PL/SQL code requires privilege on the DBMS_SCHEDULER package.
F. Each chunk will be committed independently as soon as the task updating that chunk is finished.
Answer: A,E
Explanation: A (not D, not E):
To use DBMS_PARALLEL_EXECUTE to run tasks in parallel, your schema will need the
CREATE JOB system privilege.
E (not C): DBMS_PARALLEL_EXECUTE now provides the ability to break up a large table
according to a variety of criteria, from ROWID ranges to key values and user-defined
methods. You can then run a SQL statement or a PL/SQL block against these different
“chunks” of the table in parallel, using the database scheduler to manage the processes
running in the background. Error logging, automatic retries, and commits are integrated into
the processing of these chunks.
Note:
*
The DBMS_PARALLEL_EXECUTE package allows a workload associated with a base table to be broken down into smaller chunks which can be run in parallel. This process involves several distinct stages. 1.Create a task 2.Split the workload into chunks CREATE_CHUNKS_BY_ROWID CREATE_CHUNKS_BY_NUMBER_COL CREATE_CHUNKS_BY_SQL 3.Run the task RUN_TASK User-defined framework Task control 4.Check the task status 5.Drop the task
*
The workload is associated with a base table, which can be split into subsets or chunks of rows. There are three methods of splitting the workload into chunks.
CREATE_CHUNKS_BY_ROWID CREATE_CHUNKS_BY_NUMBER_COL CREATE_CHUNKS_BY_SQL The chunks associated with a task can be dropped using the DROP_CHUNKS procedure.
*
CREATE_CHUNKS_BY_ROWID The CREATE_CHUNKS_BY_ROWID procedure splits the data by rowid into chunks specified by the CHUNK_SIZE parameter. If the BY_ROW parameter is set to TRUE, the CHUNK_SIZE refers to the number of rows, otherwise it refers to the number of blocks.
Reference: TECHNOLOGY: PL/SQL Practices, On Working in Parallel
Q18. 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.
Answer: A,C
Explanation: 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
Q19. Which two types of SQL statements will benefit from dynamic sampling?
A. SQL statements that are executed parallel
B. SQL statement that use a complex predicate expression when extended statistics are not available.
C. SQL statements that are resource-intensive and have the current statistics
D. SQL statements with highly selective filters on column that has missing index statistics
E. Short-running SQL statements
Answer: A,B
Explanation: A: he optimizer decides whether to use dynamic statistics based on several factors. For example, the database uses automatic dynamic statistics when the SQL statement uses parallel execution.
B: One scenario where DS is used is when the statement contains a complex predicate expression and extended statistics are not available. Extended statistics were introduced in Oracle Database 11g Release 1 with the goal to help the optimizer get good quality cardinality estimates for complex predicate expressions.
D: DS It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan.
Reference: When the Optimizer Uses Dynamic Statistics
Q20. 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.
Answer: A,B
Explanation: http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52asktom-1735913.html
* 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).