please update top 3 position of BZU?

Introduction to Parallel Execution
Parallel execution enables the application of multiple CPU and I/O resources to the execution of a single database operation. It dramatically reduces response time for data-intensive operations on large databases typically associated with a decision support system (DSS) and data warehouses. You can also implement parallel execution on an online transaction processing (OLTP) system for batch processing or schema maintenance operations such as index creation. Parallel execution is sometimes called parallelism. Parallelism is the idea of breaking down a task so that, instead of one process doing all of the work in a query, many processes do part of the work at the same time. An example of this is when four processes combine to calculate the total sales for a year, each process handles one quarter of the year instead of a single process handling all four quarters by itself. The improvement in performance can be quite significant. Parallel execution improves processing for:

Queries requiring large table scans, joins, or partitioned index scans

Creation of large indexes

Creation of large tables (including materialized views)

Bulk insertions, updates, merges, and deletions

You can also use parallel execution to access object types within Oracle Database. For example, you can use parallel execution to access large objects (LOBs).

If the necessary parallel server processes are not available for parallel execution, a SQL statement is queued when the parallel degree policy is set to automatic. After the necessary resources become available, the SQL statement is dequeued and allowed to execute. The parallel statement queue operates as a first-in, first-out queue by default. If the query in front of the queue cannot be scheduled, none of the queries in the queue can be scheduled even if resources are available in the system to ensure that the query at the head of the queue has adequate resources. However, if you configure and set up a resource plan, then you can control the order in which parallel statements are dequeued and the number of parallel servers used by each workload or consumer group. For information, refer to “Managing Parallel Statement Queuing with Resource Manager”.

This section contains the following topics:

When to Implement Parallel Execution

When Not to Implement Parallel Execution

Fundamental Hardware Requirements

Operations That Can Use Parallel Execution

When to Implement Parallel Execution
Parallel execution benefits systems with all of the following characteristics:

Symmetric multiprocessors (SMPs), clusters, or massively parallel systems

Sufficient I/O bandwidth

Underutilized or intermittently used CPUs (for example, systems where CPU usage is typically less than 30%)

Sufficient memory to support additional memory-intensive processes, such as sorting, hashing, and I/O buffers

If your system lacks any of these characteristics, parallel execution might not significantly improve performance. In fact, parallel execution may reduce system performance on overutilized systems or systems with small I/O bandwidth.

The benefits of parallel execution can be seen in DSS and data warehouse environments. OLTP systems can also benefit from parallel execution during batch processing and during schema maintenance operations such as creation of indexes. The average simple DML or SELECT statements that characterize OLTP applications would not experience any benefit from being executed in parallel.

When Not to Implement Parallel Execution
Parallel execution is not typically useful for:

Environments in which the typical query or transaction is very short (a few seconds or less).

This includes most online transaction systems. Parallel execution is not useful in these environments because there is a cost associated with coordinating the parallel execution servers; for short transactions, the cost of this coordination may outweigh the benefits of parallelism.

Environments in which the CPU, memory, or I/O resources are heavily utilized.

Parallel execution is designed to exploit additional available hardware resources; if no such resources are available, then parallel execution does not yield any benefits and indeed may be detrimental to performance.

Fundamental Hardware Requirements
Parallel execution is designed to effectively use multiple CPUs and disks to answer queries quickly. It is very I/O intensive by nature. To achieve optimal performance, each component in the hardware configuration must be sized to sustain the same level of throughput: from the CPUs and the Host Bus Adapters (HBAs) in the compute nodes, to the switches, and on into the I/O subsystem, including the storage controllers and the physical disks. If the system is an Oracle Real Application Clusters (Oracle RAC) system, then the interconnection also has to be sized appropriately. The weakest link is going to limit the performance and scalability of operations in a configuration.

It is recommended to measure the maximum I/O performance a hardware configuration can achieve without Oracle database. This measurement can be used as a baseline for the future system performance evaluations. Remember, it is not possible for parallel execution to achieve better I/O throughput than the underlying hardware can sustain. Oracle Database provides a free calibration tool called Orion, which is designed to measure the I/O performance of a system by simulating Oracle I/O workloads. A parallel execution typically performs large random I/Os.

Operations That Can Use Parallel Execution
You can use parallel execution for any of the following:

Access methods

Some examples are table scans, index fast full scans, and partitioned index range scans.

Join methods

Some examples are nested loop, sort merge, hash, and star transformation.

DDL statements

Some examples are CREATE TABLE AS SELECT, CREATE INDEX, REBUILD INDEX, REBUILD INDEX PARTITION, and MOVE/SPLIT/COALESCE PARTITION.

You can typically use parallel DDL where you use regular DDL. There are, however, some additional details to consider when designing your database. One important restriction is that parallel DDL cannot be used on tables with object or LOB columns.

All of these DDL operations can be performed in NOLOGGING mode for either parallel or serial execution.

The CREATE TABLE statement for an index-organized table can be run with parallel execution either with or without an AS SELECT clause.

Different parallelism is used for different operations. Parallel CREATE (partitioned) TABLE AS SELECT and parallel CREATE (partitioned) INDEX statements run with a degree of parallelism (DOP) equal to the number of partitions.

DML statements

Some examples are INSERT AS SELECT, UPDATE, DELETE, and MERGE operations.

Parallel DML (parallel insert, update, merge, and delete operations) uses parallel execution mechanisms to speed up or scale up large DML operations against large database tables and indexes. You can also use INSERT … SELECT statements to insert rows into multiple tables as part of a single DML statement. You can ordinarily use parallel DML where you use regular DML.

Although data manipulation language usually includes queries, the term parallel DML refers only to inserts, updates, merges, and deletes done in parallel.

Parallel query

You can run queries and subqueries in parallel in SELECT statements, plus the query portions of DDL statements and DML statements (INSERT, UPDATE, DELETE, and MERGE).

Miscellaneous SQL operations

Some examples are GROUP BY, NOT IN, SELECT DISTINCT, UNION, UNION ALL, CUBE, and ROLLUP, plus aggregate and table functions.

SQL*Loader

You can use SQL*Loader in parallel execution where large amounts of data are routinely encountered. To speed up your load operations, you can use a parallel direct-path load as in the following example:

sqlldr CONTROL=LOAD1.CTL DIRECT=TRUE PARALLEL=TRUE
sqlldr CONTROL=LOAD2.CTL DIRECT=TRUE PARALLEL=TRUE
sqlldr CONTROL=LOAD3.CTL DIRECT=TRUE PARALLEL=TRUE
You provide your user Id and password on the command line. You can also use a parameter file to achieve the same result.

An important point to remember is that indexes are not maintained during a parallel load.

Source

@Khuram-Shahzad
Yes! You can use video link but if you want to embed video you may use following code.

<iframe width="544" height="360" src="https://www.sitename.com/embed/vL4Myo1g9v8" frameborder="0" allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>

Solution # 1

Instruction Processor Hexadecimal Code Behavioral RTL JPL R5, [26] Falcon - A 10000 101 00011010, 1000 0101 0001 1010, 851A (R[5] ≥ 0): PC ← PC+ (26-PC); STS R7, R2 (100) Falcon - E 00101 111 010, 000000000000001100100, 0010 1111 0100 0000, 0000 0000 0110 0100, 2F40 0064 M[R[2]+100] ← R [7] STACC R4, 36 Modified EAGLE 10111 100 00100100, 1011 1100 0010 0100, BC24 M[R[4] + ([email protected]<7>)©C] ← ACC C represents the constant 36 DIV R2 EAGLE 10000 010, 1000 0010, 82 R[0] ← R[0]/R[2] R[2] ← R[0]%R[2] SHIFTL R5, R2, 7 FALCON - A 01100 101 010 00111, 0110 0101 0100 0111, 6547 R[5]<15…0> ← R [2]<(15- N)…0>©([email protected]) N represents constant 7

Solution # 2

Instruction Value of Destination Operand Data Bus <15…0> Address Bus (15…0> LDACC B 55CEh 55CEh 3320h SUB R1 55B9h N/A N/A LDACC C 2015h 2015h AB0Eh ADD R2 45E0h N/A N/A STACC A 45E0h 45E0h AB10h

Table 3 Data Bus and Address Bus Contents for Modified Eagle

Calculation Steps (Instruction-By-Instruction)

• LDACC B
LDACC stands for load accumulator. In LDACC, the destination operand is accumulator and source operand is the memory location labelled as B. The memory label B points to the memory address 3320h. When this instruction is executed, the value stored at memory address 3320h will be read and loaded in Accumulator register. This address 3320h will be copied into Address Bus which will then read its contents from memory and load the contents at data bus. The operand size in Modified EAGLE is 2-byte. Therefore, the values stored at addresses 3320h and 3321h will be loaded at data bus. These values are CEh and 55h respectively. Since, Modified EAGLE employs Little endian notation hence, the 2-bye value will be read as 55CEh. The value of data bus will also be 55CEh and this will be loaded into destination operand Accumulator.
• SUB R1
SUB R1 means to subtract the value of source operand register R1 which is 0015h, from the destination operand Accumulator (ACC) register which contains 55CEh. The result of subtraction will be stored back into ACC. After subtraction, the value stored in ACC will be 55B9h. Because SUB is not a memory instruction, we are not concerned with the contents of Data Bus or Address Bus because the values we need to execute the instruction are already available in registers. Hence, Data Bus and Address Bus values will be labelled as N/A.
• LDACC C
When this instruction is executed, the value stored at memory address labelled with C is read and loaded in Accumulator register ACC. In this case, the address of the C is AB0Eh which is also the value of address bus. The operands in Modified EAGLE are 2-byte values. The contents at addresses AB0Eh and AB0Fh will be copied into data bus which will then be loaded into ACC register. These contents are 15h and 20h. Due to Little endian notation, the 2-bye value will be 2015h. The value of data bus will be 2015h and same will be loaded in destination register ACC.
• ADDR2
When ADD is executed, the value of register R2 is added to ACC register. Hence, after the execution, the ACC register will hold 45E0h. AS usual, ADD is not a memory instruction, so we are not concerned with the values of Data Bus and Address Bus and both are labelled as N/A.
• STACC A
STACC stands for Store ACC. There is one destination operand which is a memory label A. When the instruction is executed, the value of Accumulator register ACC is stored at the memory address labelled by A. The destination memory address will be AB10h. The value of ACC is 45E0h will be stored as address AB10h. However, due to Little-Endian notation, the address will be stored as E0h at memory location AB10h and then 45h at memory location AB11h.