1Z0-117 | What Breathing 1Z0-117 pdf Is?

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

Answer: E,F

Explanation: * Parallel (Manual): The optimizer is forced to use the parallel settings of the objects in the statement.

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

behavior:

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

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 system with parallel server processes.

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

Q52. See the table below:

All parallel execution servers are available and sessions use default settings for parallelism.

Which three are true about parallel execution in your instance?

A. Parallel execution occurs when estimated serial execution time exceeds the minimum time threshold.

B. Parallel execution occurs for all DML statements.

C. Parallel execution occurs for those statements that access tables with dictionary DOP defined.

D. Parallel execution occurs for those statements that access tables with no dictionary DOP defined.

E. Parallel execution occurs for all DDL statements.

Answer: A,C,D

Explanation: A (not B, Not E): PARALLEL_MIN_TIME_THRESHOLD : Oracle 11gR2 will

ascertain if the query’s estimated execution time is likely to run longer than the acceptable

value (in seconds) for pARALLEL_MIN_TIME_THRESHOLD and, if sufficient resources for

parallel execution exist right now, it will allow the query to execute; otherwise, it will delay

its execution until sufficient resources exist. This helps prevent a single parallel query from

consuming excessive resources at the cost of other non-parallelizable operations. The

default of this parameter is 10 seconds.

C, D: 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.

If there were insufficient parallel servers to satisfy the requested DOP, one of three things

could occur:

/ The SQL would be run at a reduced DOP (be downgraded)

/ The SQL would run in serial mode (be serialized)

/ If PARALLEL_MIN_PERCENT was specified and less than the nominated percentage of

the DOP was achievable, then the the SQL statement might terminate with "ORA-12827:

insufficient parallel query slaves available".

Note:

PARALLEL_DEGREE_POLICY. It can have 3 values : MANUAL, LIMITED and AUTO

MANUAL - This is the default. Disables Auto DOP, statement queuing and in-memory parallel execution. It reverts the behavior of parallel execution to what it was previous to Oracle Database 11g, Release 2 (11.2).

Oracle supports parallel processing for a wide range of operations, including queries, DDL and DML:

Queries that involve table or index range scans.

Bulk insert, update or delete operations.

Table and index creation.

Oracle's parallel execution framework enables you to either explicitly chose - or even enforce - a specific degree of parallelism (DOP) or to rely on Oracle to control it.

Three modes are available to request a DOP :

default fixed adaptive

The DOP is determined in the following priority order:

hint session table and limited by the Oracle Database Resource Manager (DBRM) settings.

Reference: AUTOMATIC DEGREE OF PARALLELISM (DOP) IN ORACLE 11G R2

Q53. Examine the following command:

Which query transformation technique is used by the optimizer in this case?

A. View merging

B. Filter push-down

C. Predicate pushing

D. Predicate move-around

Answer: C

Explanation: In predicate pushing, the optimizer "pushes" the relevant predicates from the containing query block into the view query block. For views that are not merged, this technique improves the subplan of the unmerged view because the database can use the pushed-in predicates to access indexes or to use as filters.

For example, suppose you create a view that references two employee tables. The view is defined with a compound query that uses the UNION set operator, as follows:

CREATE VIEW all_employees_vw AS ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees ) UNION ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers ); You then query the view as follows:

SELECT last_name FROM all_employees_vw WHERE department_id = 50; Because the view is a compound query, the optimizer cannot merge the view's query into the accessing query block. Instead, the optimizer can transform the accessing statement by pushing its predicate, the WHERE clause condition department_id=50, into the view's compound query. The equivalent transformed query is as follows:

SELECT last_name FROM ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees WHERE department_id=50 UNION SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers WHERE department_id=50 );

Reference: Oracle Database Performance Tuning Guide, Predicate Pushing

Q54. Which statement is true about an automatic SQL task?

A. It will attempt to tune the currently running SQL statements that are highly resource intensive.

B. It will automatically implement new SQL profiles for the statements that have existing SQL profiles.

C. It will attempt to tune all-long-running queries that have existing SQL profiles.

D. It will automatically implement SQL profiles if a three-fold benefit can be achieved and automatic profile implementation is enabled.

E. It will tune all the top SQL statements from AWR irrespective of the time it takes to complete the task in a maintenance window.

Answer: D

Explanation: Optionally, implements the SQL profiles provided they meet the criteria of threefold performance improvement

The database considers other factors when deciding whether to implement the SQL profile. For example, the database does not implement a profile when the objects referenced in the statement have stale optimizer statistics. SQL profiles that have been implemented automatically show type is AUTO in the DBA_SQL_PROFILES view. If the database uses SQL plan management, and if a SQL plan baseline exists for the SQL statement, then the database adds a new plan baseline when creating the SQL profile. As a result, the optimizer uses the new plan immediately after profile creation.

Incorrect:

E: Oracle Database automatically runs SQL Tuning Advisor on selected high-load SQL

statements from the Automatic Workload Repository (AWR) that qualify as tuning

candidates. This task, called Automatic SQL Tuning, runs in the default maintenance

windows on a nightly basis. By default, automatic SQL tuning runs for at most one hour.

Note:

After automatic SQL tuning begins, the database performs the following steps:

. Identifies SQL candidates in the AWR for tuning Oracle Database analyzes statistics in AWR and generates a list of potential SQL statements that are eligible for tuning. These statements include repeating high-load statements that have a significant impact on the database. The database tunes only SQL statements that have an execution plan with a high potential for improvement. The database ignores recursive SQL and statements that have been tuned recently (in the last month), parallel queries, DML, DDL, and SQL statements with performance problems caused by concurrency issues. The database orders the SQL statements that are selected as candidates based on their performance impact. The database calculates the impact by summing the CPU time and the I/O times in AWR for the selected statement in the past week.

. Tunes each SQL statement individually by calling SQL Tuning Advisor During the tuning process, the database considers and reports all recommendation types, but it can implement only SQL profiles automatically.

. Tests SQL profiles by executing the SQL statement

. Optionally, implements the SQL profiles provided they meet the criteria of threefold performance improvement.The database considers other factors when deciding whether to implement the SQL profile. For example, the database does not implement a profile when the objects referenced in the statement have stale optimizer statistics. SQL profiles that have been implemented automatically show type is AUTO in the DBA_SQL_PROFILES view.If the database uses SQL plan management, and if a SQL plan baseline exists for the SQL statement, then the database adds a new plan baseline when creating the SQL profile. As a result, the optimizer uses the new plan immediately after profile creation.

Reference: Oracle Database Performance Tuning Guide, Automatic SQL Tuning

Q55. Examine the Exhibit. Given two sets of parallel execution processes, SS1 and SS2, which is true?

A. Each process SS1 reads some of the rows from the CUSTOMERS table and sends all the rows it reads to each process in SS2.

B. Each process in SS1 reads all the rows from the CUSTOMERS table and distributes the rows evenly among the processes in SS2.

C. Each process in SS1 reads some of the rows from the SALES table and sends all the rows it reads to each process in SS2.

D. Each process in SS1 reads all the rows from the SALES table and distributes the rows evenly among the processes in SS2.

E. Each process in SS1 reads some of the rows from the SALES table and distributes the rows evenly among the processes in SS2.

F. Each process in the SS1 reads some of the rows from the CUSTOMERS table and distributes the rows evenly among the processes in SS2.

Answer: D

Explanation:

Note:

The execution starts with line 16 (accessing the SALES table), followed by line 15.

PX BLOCKITERATOR The PX BLOCK ITERATOR row source represents the splitting up of the table EMP2 into pieces so as to divide the scan workload between the parallel scan slaves. The PX SEND and PX RECEIVE row sources represent the pipe that connects the two slave sets as rows flow up from the parallel scan, get repartitioned through the HASHtable queue, and then read by and aggregated on the top slave set.

Q56. You enabled auto degree of parallelism (DOP) for your instance. Examine the query:

Which two are true about the execution of this query?

A. Dictionary DOP will be used, if present, on the tables referred in the query.

B. DOP is calculated if the calculated DOP is 1.

C. DOP is calculated automatically.

D. Calculated DOP will always by 2 or more.

E. The statement will execute with auto DOP only when PARALLEL_DEGREE_POLICY is set to AUTO.

Answer: A,C

Explanation: * PARALLEL (AUTO): The database computes the degree of parallelism (C), which can be 1 or greater (not D). If the computed degree of parallelism is 1, then the statement runs serially.

You can use the PARALLEL hint to force parallelism. It takes an optional parameter: the DOP at which the statement should run. In addition, theNO_PARALLEL hint overrides a PARALLEL parameter in the DDL that created or altered the table.

The following example illustrates computing the DOP the statement should use:

SELECT /*+ parallel(auto) */ ename, dname FROM emp e, dept d

WHERE e.deptno=d.deptno;

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database automatically decides if a statement should execute in parallel or not and what DOP it should use. Oracle Database also determines if the statement can be executed immediately or if it is queued until more system resources are available. Finally, Oracle Database decides if the statement can take advantage of the aggregated cluster memory or not.

Q57. Examine the exhibit.

Which two are true concerning the execution plan?

A. No partition-wise join is used

B. A full partition-wise join is used

C. A partial partition-wise join is used

D. The SALES table is composite partitioned

Answer: B,D

Explanation: * The following example shows the execution plan for the full partition-wise

join with the sales table range partitioned by time_id, and subpartitioned by hash on cust_id.

| 0 | SELECT STATEMENT | | | | | |

| 1 | PX COORDINATOR | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10001 | | | P->S | QC (RAND) |

| 6 | PX SEND HASH | :TQ10000 | | | P->P | HASH |

| 8 | PX PARTITION HASH ALL | | 1 | 16 | PCWC | |

| 10 | TABLE ACCESS FULL | CUSTOMERS | 1 | 16 | PCWP | |

| 11 | PX PARTITION RANGE ITERATOR| | 8 | 9 | PCWC | |

|* 12 | TABLE ACCESS FULL | SALES | 113 | 144 | PCWP | |

Predicate Information (identified by operation id):

3 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*)),0))>100)

9 - access("S"."CUST_ID"="C"."CUST_ID")

12 - filter("S"."TIME_ID"<=TO_DATE(' 1999-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

AND

"S"."TIME_ID">=TO_DATE(' 1999-07-01

00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

* Full partition-wise joins can occur if two tables that are co-partitioned on the same key are joined in a query. The tables can be co-partitioned at the partition level, or at the subpartition level, or at a combination of partition and subpartition levels. Reference partitioning is an easy way to guarantee co-partitioning. Full partition-wise joins can be executed in serial and in parallel.

Reference: Oracle Database VLDB and Partitioning Guide, Full Partition-Wise Joins: Composite - Single-Level

Q58. Examine the statements being executed for the first time:

Steps followed by a SQL statement during parsing:

Search for a similar statement in the shared pool.

Search for an identical statement in the shared pool.

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

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.

Perform hard parsing.

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

Q59. View the code sequence:

Which three statements are true about the execution of this query?

A. The optimizer uses an index join as an access path.

B. The optimizer uses a B* tree access path for the EMPLOYEES table.

C. The optimizer uses a bitmap access path for the EMPLOYEES table.

D. The optimizer performs bitmap conversion from row IDs.

E. The optimizer performs bitmap conversion to row IDs of the combined maps.

F. The optimizer performs bitmap conversion from the row IDs of the combined maps.

Answer: C,D,E

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

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

1Z0-117 | What Breathing 1Z0-117 pdf Is?

View the entire exam for free: 1Z0-117 Exam Questions List

Related 1Z0-117 posts: