[Guaranteed] 1Z0-117 Oracle actual test 11-20 (Nov 2021)

Examcollection enjoys a high reputation within the IT industry for every one of the certification exam practice materials this offers. There is a team associated with specialized experts whore devoted for you to compiling and revising the Oracle Oracle exam dumps. All of them have got rich experience in compiling the Oracle 1Z0-117 exam questions and answers. Your questions are technical, unique, and correct; the answers tend to be detailed, right, and precise. Equally the Oracle practice questions and answers are verified many times prior to publishing.

2021 Nov 1Z0-117 real exam

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

Answer: F 

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

Q12. An application user complains about statement execution taking longer than usual. You find that the query uses a bind variable in the WHERE clause as follows: 

You want to view the execution plan of the query that takes into account the value in the bind variable PCAT. 

Which two methods can you use to view the required execution plan? 

A. Use the DBMS_XPLAN.DISPLAY function to view the execution plan. 

B. Identify the SQL_ID for the statementsand use DBMS_XPLAN.DISPLAY_CURSOR for that SQL_ID to view the execution plan. 

C. Identify the SQL_ID for the statement and fetch the execution plan PLAN_TABLE. 

D. View the execution plan for the statement from V$SQL_PLAN. 

E. Execute the statement with different bind values and set AUTOTRACE enabled for session. 

Answer: B,D 

Explanation: D: V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache. 

B: The DBMS_XPLAN package supplies five table functions: 

DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle 

DISPLAY - to format and display the contents of a plan table. 

DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR. 

DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor. 

DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set. 

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

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

Answer: C,E 

Explanation: 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." 

Q15. Which three tasks are performed by the parallel execution coordinator process? 

A. Allocating parallel execution processes from the parallel execution server pool. 

B. Determining the parallel execution method for each operation in the execution plan. 

C. Managing the data flow between the producers and consumers during inter-operation parallelism. 

D. Any serial processing that is part of the execution plan. 

E. Determining the desired number of parallel execution processes 

F. Managing the data flow between the producers and consumers during intra-operation parallelism. 

Answer: A,B,C 

Explanation: A: When executing a parallel operation, the parallel execution coordinator obtains parallel execution servers from the pool and assigns them to the operation. If necessary, Oracle Database can create additional parallel execution servers for the operation. These parallel execution servers remain with the operation throughout execution. After the statement has been processed completely, the parallel execution servers return to the pool. 

B: 

*

 The parallel execution coordinator examines each operation in a SQL statement's execution plan then determines the way in which the rows operated on by the operation must be divided or redistributed among the parallel execution servers. 

*

 After the optimizer determines the execution plan of a statement, the parallel execution coordinator determines the parallel execution method for each operation in the plan. 

Note: 

* Oracle Database can process a parallel operation with fewer than the requested number of processes. If all parallel execution servers in the pool are occupied and the maximum number of parallel execution servers has been started, the parallel execution coordinator switches to serial processing. 

Reference: Oracle Database VLDB and Partitioning Guide 11g, How Parallel Execution Works 

Renovate 1Z0-117 free practice exam:

Q16. Which two statements are true about index full scans? 

A. An index fast full scan multi block I/O to read the index structure in its entirely. 

B. Index nodes are not retrieved in the index order, and there fore the nodes are not in sequence. 

C. An index fast full scan reads the index block by block. 

D. An index fast full scan reads the whole index from the lowest value to the higher value. 

Answer: A,B 

Explanation: A: To speed table and index block access, Oracle uses the db_file_multiblock_read_count parameter (which defaults to 8) to aid in getting full-table scan and full-index scan data blocks into the data buffer cache as fast as possible. 

B: The index nodes are not retrieved in index order, the rows will not be sequenced. 

Note: there are some requirements for Oracle to invoke the fast full-index scan. . All of the columns required must be specified in the index. That is, all columns in the select and where clauses must exist in the index. . The query returns more than 10 percent of the rows within the index. This 10 percent figure depends on the degree of multi-block reads and the degree of parallelism. . You are counting the number of rows in a table that meet a specific criterion. The fast full-index scan is almost always used for count(*) operations. 

Reference: index fast full scan tips 

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

Answer: B 

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

Q18. Examine the Exhibit 1 to view the structure of and indexes for EMPLOYEES and DEPARTMENTS tables. 

Which three statements are true regarding the execution plan? 

A. The view operator collects all rows from a query block before they can be processed but higher operations in the plan. 

B. The in-line query in the select list is processed as a view and then joined. 

C. The optimizer pushes the equality predicate into the view to satisfy the join condition. 

D. The optimizer chooses sort-merge join because sorting is required for the join equality predicate. 

E. The optimizer chooses sort-merge join as a join method because an equality predicate is used for joining the tables. 

Answer: A,B,C 

Explanation: 

Incorrect: 

Not D, not E: 

Sort Merge joins are used for UN-Equality and also there is no SORT clause in the SQL. 

Note: The optimizer may choose a sort merge join over a hash join for joining large amounts of data when any of the following conditions is true: 

*

 The join condition between two tables is not an equijoin, that is, uses an inequality condition such as <, <=, >, or >=. 

*

 Because of sorts required by other operations, the optimizer finds it cheaper to use a sort merge. 

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

Answer: D 

Explanation: 

https://blogs.oracle.com/optimizer/entry/understanding_dbms_statsset__prefs_procedures 

Q20. You are administering a database supporting an OLTP workload where the users perform frequent queries for fetching a new rows as possible, involving join operations on recently inserted data. In addition at night, a few DSS queries are also performed. Examine the initialization parameters for the instance: 

Which two options would you use for the optimizer? 

A. Set the OPTIMIZER_MODE initialization parameter to FIRST_ROWS_n. 

B. Add the hint ALL_ROWS in the DOS queries. 

C. Set the OPTIMIZER_INDEX_CACHING initialization parameter to 0. 

D. Add a hint INDEX_COMBINE in all DSS queries. 

E. Set the OPTIMIZER_INDEX_COST_ADJ initialization parameter to 100. 

Answer: A,E 

Explanation: The last appended rows are more likely to be found quickly with FIRST_ROWS_n. 

E: Make it not to prioritize index instead if table scan. 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.