Parallel execution dramatically reduces response time for data-intensive operations on large databases typically associated with decision support systems (DSS) and data warehouses. You can also implement parallel execution on certain types of online transaction processing (OLTP) and hybrid systems. Parallel execution improves processing for:
You can also use parallel execution to access object types within an Oracle database. For example, you can use parallel execution to access large objects (LOBs). Parallel execution benefits systems with all of the following characteristics:
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.
When to Implement Parallel Execution
The benefits of parallel execution can be seen in DSS and data warehousing 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 see any benefit from being executed in parallel.
When Not to Implement Parallel Execution
Parallel execution is not normally useful for:
Operations That Can Be Parallelized
You can use parallel execution for any of the following:
Some examples are table scans, index full scans, and partitioned index range scans.
Some examples are nested loop, sort merge, hash, and star transformation.
Some examples are CREATE TABLE AS SELECT, CREATE INDEX, REBUILD INDEX, REBUILD INDEX PARTITION, and MOVE/SPLIT/COALESCE PARTITION.
You can normally 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 parallelized 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 run with a degree of parallelism equal to the number of partitions.
Parallel operations require accurate statistics to perform optimally.
Some examples are INSERT AS SELECT, updates, deletes, and MERGE operations.
Parallel DML (parallel insert, update, merge, and delete) 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 normally use parallel DML where you use regular DML.
Although data manipulation language (DML) normally includes queries, the term parallel DML refers only to inserts, updates, upserts and deletes done in parallel.
Some examples are GROUP BY, NOT IN, SELECT DISTINCT, UNION, UNION ALL, CUBE, and ROLLUP, as well as aggregate and table functions.
You can parallelize queries and subqueries in SELECT statements, as well as the query portions of DDL statements and DML statements (INSERT, UPDATE, DELETE, and MERGE).
You can parallelize the use of SQL*Loader, where large amounts of data are routinely encountered. To speed up your loads, you can use a parallel direct-path load as in the following example:
sqlldr USERID=SCOTT/TIGER CONTROL=LOAD2.CTL DIRECT=TRUE PARALLEL=TRUE
sqlldr USERID=SCOTT/TIGER CONTROL=LOAD3.CTL DIRECT=TRUE PARALLEL=TRUE
You can also use a parameter file to achieve the same thing.
An important point to remember is that indexes are not maintained during a parallel load.
Data Warehousing Related Interview Questions
|Informatica Interview Questions||Data Warehousing Interview Questions|
|Networking Interview Questions||System Administration Interview Questions|
|Hadoop Interview Questions||MYSQL DBA Interview Questions|
|Data modeling Interview Questions||Hadoop Administration Interview Questions|
|Apache Flume Interview Questions||Informatica Admin Interview Questions|
Data Warehousing Tutorial
Data Warehousing Concepts
Physical Design In Data Warehouses
Hardware And I/o Considerations In Data Warehouses
Parallelism And Partitioning In Data Warehouses
Basic Materialized Views
Advanced Materialized Views
Overview Of Extraction, Transformation, And Loading
Extraction In Data Warehouses
Transportation In Data Warehouses
Loading And Transformation
Maintaining The Data Warehouse
Change Data Capture
Schema Modeling Techniques
Sql For Aggregation In Data Warehouses
Sql For Analysis And Reporting
Sql For Modeling
Olap And Data Mining
Using Parallel Execution
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.