Parallel Processing
Parallel Processing should be used under this two conditions
1) When plenty of free resource is available . The aim of PX is to reduce the respnose time by distributing the work done by single process
2) It can used for SQL statements that take more than a dozen seconds to execute serially
If PX is commonly used for many SQL statements,the degree of parallelism should be set at the table or index level.
If it is used only for specific batches or reports . It is better to enable it at the session level or through hints
Insert statements with values clause cannot be parallelized
DML --> Insert,update,delete & merge can be executed in parallel when
1) Table has a trigger
2) A table has either a foreign key constraint refering itself
3) an object column is modified
4) a clustered or temporary table is modified
An actual distribution for a SQL statement , you can use the dynamic performance view v$pq_tqstat.
Information provided for current session and last SQL statements
Direct path insert gives better performance as it generates minimum undo . In fact undo is generated for only space management operation . For Ex :-- To increase the high watermark and to add new extent to the segment and not for the rows contained in the blocks that are inserted by direct-path .
The purpose of minimal logging is t
\'o minimize the redo generation
You can set minimual logging by setting parameter nologging at the table or partition level
Mimimial loggging is supported for direct path loads and some DDL Statements
Fetching numerous row at a time is called row prefetching
Row prefetching is enabled by JDBC drive by default
Parallel Processing should be used under this two conditions
1) When plenty of free resource is available . The aim of PX is to reduce the respnose time by distributing the work done by single process
2) It can used for SQL statements that take more than a dozen seconds to execute serially
If PX is commonly used for many SQL statements,the degree of parallelism should be set at the table or index level.
If it is used only for specific batches or reports . It is better to enable it at the session level or through hints
Insert statements with values clause cannot be parallelized
DML --> Insert,update,delete & merge can be executed in parallel when
1) Table has a trigger
2) A table has either a foreign key constraint refering itself
3) an object column is modified
4) a clustered or temporary table is modified
An actual distribution for a SQL statement , you can use the dynamic performance view v$pq_tqstat.
Information provided for current session and last SQL statements
Direct path insert gives better performance as it generates minimum undo . In fact undo is generated for only space management operation . For Ex :-- To increase the high watermark and to add new extent to the segment and not for the rows contained in the blocks that are inserted by direct-path .
The purpose of minimal logging is t
\'o minimize the redo generation
You can set minimual logging by setting parameter nologging at the table or partition level
Mimimial loggging is supported for direct path loads and some DDL Statements
Fetching numerous row at a time is called row prefetching
Row prefetching is enabled by JDBC drive by default
No comments:
Post a Comment