1Z0-117 | The Secret of Oracle 1Z0-117 examcollection
Q41. You have enabled DML by issuing: ALTER session ENABLE PARALLEL DML;
The PARELLEL_DEGREE_POLICY initialization parameter is set to AUTO.
Which two options true about DML statements for which parallel execution is requested?
A. Statements for which PDML is requested will execute serially estimated time is less than the time specified by the PARALLEL_MIN_THRESHOLD parameter.
B. Statements for which PDML is requested will be queued if the number of busy parallel execution servers greater than PARALLEL_MIN_SERVERS parameter.
C. Statements for which PDML is requested will always execute in parallel if estimated execution in parallel if estimated execution time is greater than the time specified by the PARELLEL_MIN_TIME_THRESHOLD parameter.
D. Statements for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_SERVERS_TARGET parameter.
E. Statement for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_DEGREE_LIMIT parameter.
Answer: C,D
Explanation: C: 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.
D: PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel 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.
Note:
*
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.
*
PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution processes for the instance. This value is the number of parallel execution processes Oracle creates when the instance is started.
Reference: Oracle Database Reference; PARALLEL_SERVERS_TARGET
Q42. You plan to bulk load data using INSERT /*+PARALLEL*/ INTO . . . . SELECT FROM statements.
Which four types of operations can execute in parallel on tables that have no bitmapped indexes or materialized views defined on term?
A. Direct path insert of a million rows into a partitioned, index-organized table containing one million rows.
B. Direct path insert of a million rows into a partitioned, index-organized table containing 10 million rows.
C. Direct path insert of a million rows into a nonpartitioned, index-organized table containing one million rows.
D. Direct path insert of a million rows into a nonpartitioned, heap-organized table containing 10 million rows.
E. Direct path insert of a million rows into a nonpartitioned, heap-organized table containing one million rows.
Answer: A,B,D,E
Explanation: Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.
Q43. Which two are the fastest methods for fetching a single row from a table based on an equality predicate?
A. Fast full index scan on an index created for a column with unique key
B. Index unique scan on an created for a column with unique key
C. Row fetch from a single table hash cluster
D. Index range scan on an index created from a column with primary key
E. Row fetch from a table using rowid
Answer: C,E
Explanation: A scan is slower than a row fetch (from hash value or rowid).
Q44. You execute the following query:
Which statement is true about the usage of these hints in the query?
A. The optimizer pushes the join predicate into the inline view.
B. The optimizer evaluates the subquery as a first and then filters out rows.
C. The optimizer performs a join operation first and then filters out the rows.
D. The hint will have no effect because one of the join resultsets is an inline view.
Answer: A
Explanation: The PUSH_PRED hint forces pushing of a join predicate into the view.
For example:
SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *
FROM employees e,
(SELECT manager_id
FROM employees
) v
WHERE e.manager_id = v.manager_id(+)
AND e.employee_id = 100;
When the PUSH_PRED hint is used without an argument, it should be placed in the view
query block. When PUSH_PRED is used with the view name as an argument, it should be
placed in the surrounding query.
Reference: Oracle Database Performance Tuning Guide, PUSH_PRED
Q45. 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.
Answer: A,D
Explanation:
(http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN13466)
Q46. One of your databases supports a mixed workload.
When monitoring SQL performance, you detect many direct paths reads full table scans.
What are the two possible causes?
A. Histograms statistics not available
B. Highly selective filter on indexed columns
C. Too many sort operations performed by queries
D. Indexes not built on filter columns
E. Too many similar type of queries getting executed with cursor sharing disabled
Answer: B,D
Explanation:
Note:
* The direct path read Oracle metric occurs during Direct Path operations when the data is asynchronously read from the database files into the PGA instead of into the SGA data buffer. Direct reads occur under these conditions:
-
When reading from the TEMP tablespace (a sort operation)
-
When reading a parallel full-table scan (parallel query factotum (slave) processes)
-Reading a LOB segment
* The optimizer uses a full table scan in any of the following cases:
-Lack of Index
-Large Amount of Data
-Small Table
-High Degree of Parallelism
Q47. Which three options are true about parallel queries when PARALLEL_DEGREE_POLICY is set to MANUAL and the session is using the default settings for parallel query, DDL, and DML?
A. A subquery in a parallel DML is parallelized only if it includes a parallel hint.
B. The number of parallel execution servers requested for a cursor is based on the greatest degree of parallelism associated with any object accessed by the cursor.
C. A SELECT statement can be executed in parallel only if no scalar subqueries are contained in the SELECT list.
D. In a CREATE TABLE . . . AS SELECT (CTAS) statement, SELECT is parallelized only if create TABLE is parallelized.
E. In an INSERT INTO . . . SELECT FROM statement, INSERT is parallelized if select is parallelized.
F. Single row inserts are never executed is parallel.
Answer: C,E,F
Explanation:
* Decision to Parallelize
A SELECT statement can be parallelized only if the following conditions are satisfied:
/ The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the schema objects referred to in the query have a PARALLEL declaration associated with them.
/ At least one of the tables specified in the query requires one of the following:
A full table scan An index range scan spanning multiple partitions
/ (C) No scalar subqueries are in the SELECT list.
*
By default, the system only uses parallel execution when a parallel degree has been explicitly set on an object or if a parallel hint is specified in the SQL statement.
*
CREATE TABLE ... AS SELECT in Parallel
Parallel execution lets you parallelize the query and create operations of creating a table as a subquery from another table or set of tables. This can be extremely useful in the creation of summary or rollup tables.
Clustered tables cannot be created and populated in parallel.
* PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.
MANUAL
Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution. This reverts the behavior of parallel execution to what it was prior to Oracle Database 11g Release 2 (11.2). This is the default.
Incorrect:
A:
*
For parallel DML (INSERT, UPDATE, MERGE, and DELETE), the reference object that determines the DOP (degree of parallelism) is the table being modified by and insert, update, or delete operation. Parallel DML also adds some limits to the DOP to prevent deadlock. If the parallel DML statement includes a subquery, the subquery's DOP is the same as the DML operation.
*
For parallel DDL, the reference object that determines the DOP is the table, index, or partition being created, rebuilt, split, or moved. If the parallel DDL statement includes a subquery, the subquery's DOP is the same as the DDL operation.
D: The CREATE TABLE ... AS SELECT statement contains two parts: a CREATE part (DDL) and a SELECT part (query). Oracle Database can parallelize both parts of the statement.
The query part of a CREATE TABLE ... AS SELECT statement can be parallelized only if the following conditions are satisfied:
. The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the CREATE part of the statement has a PARALLEL clause specification or the schema objects referred to in the query have a PARALLEL declaration associated with them.
. At least one of the tables specified in the query requires one of the following: a full table scan or an index range scan spanning multiple partitions.
Reference: Oracle Database VLDB and Partitioning Guide, Using Parallel Execution
Q48. In your database, the CURSOR_SHARING parameter is set to FORCE.
A user issues the following SQL statement:
Select * from SH.CUSTOMERS where REIGN=’NORTH’
Which two statements are correct?
A. The literal value ‘NORTH’ is replaced by a system-generated bind variable.
B. Bind peeking will not happen and subsequent executions of the statement with different literal values will use the same plan.
C. Adaptive cursor sharing happens only if there is a histogram in the REIGN column of the CUSTOMERS table.
D. Adaptive cursor sharing happens irrespective of whether there is a histogram in the REIGN column of the CUSTOMERS table.
Answer: A,D
Explanation:
https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_c ursor_sharing_similar_and_force
Q49. 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.
Answer: D,E
Explanation:
http://docs.oracle.com/cd/E11882_01/server.112/e41573/advisor.htm#PFGRF94911
Q50. 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.
Answer: E
Explanation: PX BLOCK ITERATORThis 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 table3. Line 7, the PX RECEIVE step, is basically the consumer of the broadcasted rows in step 84. 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 servers6. 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 servers8. Line 3 is the consumer QC that holds the result of the each of the PQ servers9. Line 2 is the PX Coordinator (QC) collecting, or consuming the rows of the joined data10. Line 1 is the final SORT AGGREGATE line that performs the grouping function