Oracle Oracle Certifications 1Z0-117 Questions & Answers

• Question 61:

  Exhibit

  

  A table has three distinct values in its ID column. In the ID column, values 10 and 20 have more than 20000 rows each and value 30 has only five rows. The

  statistics for the schema have been updated recently.

  The CURSOR_SHARING parameter is set to EXACT.

  The query was executed recently and the cursor for the query is bind aware. Examine the exhibits to view the commands executed.

  You plan to execute the same query with a value of 30 for the bind variable V_ID.

  Which statement is true in this scenario?

  A. The same execution plan will always be used irrespective of the value in the bind variable.

  B. A new execution plan will be generated depending on the access pattern and the bind value.

  C. Adaptive cursor sharing will ensure that a new cursor is generated for each distinct value in the bind variable.

  D. Adaptive cursor sharing will happen only if you use the literal values instead of bind variables in the query.

  Correct Answer: C

  Note:

  *

  CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

  *

  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.

  *

  Values:

  *

  FORCE

  Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

  *

  SIMILAR

  Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or

  the degree to which the plan is optimized.

  *

  EXACT

  Only allows statements with identical text to share the same cursor.

• Question 62:

  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.

  Correct Answer: CEF

  * 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

• Question 63:

  You execute the following query for the first time:

  

  Examine the SQL statement processing steps:

  1.

  The value of the variable SAL is obtained to run the query.

  2.

  The syntax of the query is checked

  3.

  A parse tree for the query is generated

  4.

  Semantics for the query are checked

  5.

  The required rows are fetched

  6.

  The SQL is executed to produce the required result.

  Which is the correct order of execution of the above query?

  A. 1, 2 3, 4, 5, 6

  B. 1, 4, 2, 3, 6, 5

  C. 2, 4, 1, 3, 6, 5

  D. 2, 3, 1, 4, 6, 5

  Correct Answer: C

  Step 1: Syntax check (2)

  Step 2: Semantic check (4)

  Step 3: Shared pool check

  Step 4: Optimization

  Step 5: Row Source Optimization (generation of query plan) (3)

  Etc.

  Note:

  

  Incorrect:

  Not A: First execute then fetch rows.

  Not B: Check of syntax is before check of semantics.

  Not D: Parse tree is after semantics.

• Question 64:

  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.

  Correct Answer: ABDE

  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.

• Question 65:

  Examine the statements being executed for the first time:

  

  Steps followed by a SQL statement during parsing:

  1.

  Search for a similar statement in the shared pool.

  2.

  Search for an identical statement in the shared pool.

  3.

  Search the SQL area of identical statement already in the shared pool.

  4.

  Proceed through the remaining steps of the parse phase to ensure that the execution plan of the existing statements is applicable to the view statement.

  5.

  Perform hard parsing.

  6.

  Share the SQL area of the similar statement already in the shared pool.

  Identify the required steps in the correct sequence used by the third query.

  A. 5, 1, 3, 4

  B. 2, 4, 3

  C. 5, 2, 3, 4

  D. 1, 4, 3

  E. Only 5

  F. 2, 5

  Correct Answer: F

  2) before 5).

  Note:

  * When application code is run, Oracle attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the statement does exist in the library cache and it can be shared, then Oracle reuses the existing code. This is known as a soft parse, or a library cache hit. If Oracle is unable to use existing code, then a new executable version of the application code must be built. This is known as a hard parse, or a library cache miss.

  Reference: Oracle Database Performance Tuning Guide, SQL Sharing Criteria

• Question 66:

  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 by using different services and tracing is enabled for the services. You want to view the detailed tracing setting for particular service.

  What would you use to view the tracing information?

  A. DBMS_SERVICE package

  B. DBMS_MONITOR package

  C. DBA_ENABLED_TRACES view

  D. Trcsess and tkprof

  Correct Answer: C

  displays information about enabled SQL traces. DBA_ENABLED_TRACES

  Incorrect:

  A: The DBMS_SERVICE package lets you create, delete, activate, and deactivate services for a single instance.

  B: The DBMS_MONITOR package let you use PL/SQL for controlling additional tracing and statistics gathering.

  Reference: Oracle Database Reference, DBA_ENABLED_TRACES

• Question 67:

  Examine the parallel parameters for your instance.

  

  All parallel execution servers are available and the session use default parallelism settings. In which two cases will the degree of parallelism be automatically calculated?

  A. Statements accessing tables whom dictionary DOP is 2 or more

  B. Statements accessing tables whose dictionary DOP is DEFAULT

  C. Statements that are estimated to execute for more than 10 seconds serially

  D. Statements accessing tables with any setting for Dictionary DOP

  E. Statements with parallel hints

  Correct Answer: BE

  PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.

  Syntax PARALLEL_DEGREE_POLICY = { MANUAL | LIMITED | AUTO }

  LIMITED is used here:

  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 DEFAULT degree of parallelism using the PARALLEL clause. Statements that do not access any tables or indexes decorated with the

  DEFAULT degree of parallelism will retain the MANUAL behavior.

  Note:

  *

  In earlier versions of the Oracle Database, we had to determine the DOP more or less manually, either with a parallel hint or by setting a parallel degree with alter table. There was an automatic computation of the DOP available for the objects with dictionary DOP of default, derived from the simple formula CPU_COUNT

  *

  PARALLEL_THREADS_PER_CPU.

• Question 68:

  Examine the Exhibit and view the structure of an indexes for the EMPLOYEES table.

  

  Which two actions might improve the performance of the query?

  A. Use the ALL_ROWS hint in the query.

  B. Collect the histogram statistics for the EMPLOYEE_ID column.

  C. Decrease the value for the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.

  D. Decrease the index on the EMPLOYEE_ID if not being used.

  E. Set the OPTIMIZER_MODE parameter to ALL_ROWS.

  Correct Answer: AE

  A: The ALL_ROWS hint instructs the optimizer to optimize a statement block with a goal of best throughput, which is minimum total resource consumption.

  E: optimizer_mode=all_rows - This optimizer mode favors full-table scans (especially parallel full-table-scans) in cases where the server resources will be minimized. The all_rows mode is generally used during batch-oriented processing and for data warehouses where the goal is to minimize server resource consumption.

• Question 69:

  How can you reduce fragmentation of an index without affecting the current transactions that are using the index?

  A. Use the ANALYZE INDEX . . . command

  B. Use the ALTER INDEX . . . VALIDATE STRUCTURE command

  C. Us the ALTER INDEX . . . REBUILD ONLINE command

  D. Use the ALTER INDEX . . . DEALLOCATE UNUSED command

  Correct Answer: D

  Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the index and make the freed space available for other segments in the tablespace.

  If index is range-partitioned or hash-partitioned, then Oracle Database deallocates unused space from each index partition. If index is a local index on a composite-partitioned table, then Oracle Database deallocates unused space from each index subpartition.

  Reference: Oracle Database SQL Language Reference 11g, alter index

• Question 70:

  Examine the Following Query and execution plan:

  

  Which query transformation technique is used by the optimizer?

  A. Filter push down

  B. Subquery factoring

  C. Subquery unnesting

  D. Predicate pushing

  Correct Answer: D

  Note:

  * In the execution plan BX, note the keyword 'VIEW PUSHED PREDICATE' indicates that the view has undergone the join predicate pushdown transformation.