分区表与索引的管理

取自：

Oracle® Database
VLDB and Partitioning Guide
11g Release 2 (11.2)
E25523-01

Global Range Partitioned Indexes

由于最高的分区有maxvalue分区边界，所以

Maintenance of Global Partitioned Indexes
By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE

These indexes can be maintained by appending the clause UPDATE INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:
■The index remains available and online throughout the operation. Hence no other applications are affected by this operation.
■The index does not have to be rebuilt after the operation.
Figure 2–7 offers a graphical view of global partitioned indexes.
Note:
This feature is supported only for heap-organized tables.


perform pruning:

1.
Static pruning occurs at compile-time, with the information about the partitions accessed beforehand.
Dynamic pruning occurs at run-time, meaning that the exact partitions to be accessed by a statement are not known beforehand

SQL statements that benefit from static pruning perform better than statements that benefit from dynamic pruning

■Partition pruning occurs using the result of a subquery.
■The optimizer rewrites the query with a star transformation and pruning occurs after the star transformation.
■The most efficient execution plan is a nested loop.
These three cases result in the use of dynamic pruning.

■Dynamic Pruning with Bind Variables
■Dynamic Pruning with Subqueries
■Dynamic Pruning with Star Transformation
■Dynamic Pruning with Nested Loop Joins

2.Notes:
2.1 Data Type Conversions:
To get the maximum performance benefit from partition pruning, you should avoid constructs that require the database to convert the data type you specify.


2.2 Function Calls:
There are several cases when the optimizer cannot perform pruning.
One common reasons is when an operator is used on top of a partitioning column.
This could be an explicit operator (for example, a function) or even an implicit operator
introduced by Oracle as part of the necessary data type conversion for executing the statement

If your queries commonly use function calls, then consider using a virtual column and virtual column partitioning to benefit from partition pruning in these cases

2.3 Collection Tables:


Partition-Wise Joins
■Full Partition-Wise Joins
■Partial Partition-Wise Joins


Full Partition-Wise Joins: Single-Level - Single-Level

Full Partition-Wise Joins: Composite - Single-Level
Composite - single-level partitioning is effective because it enables you to combine pruning on one dimension with a full partition-wise join on another dimension.

Full Partition-Wise Joins: Composite - Composite
You can get full partition-wise joins on all combinations of partition and subpartition partitions:
    partition - partition, partition - subpartition, subpartition - partition, and subpartition - subpartition.
    
    

Partial Partition-Wise Joins:
■Partial Partition-Wise Joins: Single-Level Partitioning
■Partial Partition-Wise Joins: Composite



Index Partitioning:
The rules for partitioning indexes are similar to those for tables:
■An index can be partitioned unless:
–The index is a cluster index.
–The index is defined on a clustered table.
■You can mix partitioned and nonpartitioned indexes with partitioned and nonpartitioned tables:
–A partitioned table can have partitioned or nonpartitioned indexes.
–A nonpartitioned table can have partitioned or nonpartitioned indexes.
■Bitmap indexes on nonpartitioned tables cannot be partitioned.
■A bitmap index on a partitioned table must be a local index.
However, partitioned indexes are more complicated than partitioned tables because there are three types of partitioned indexes:
■Local prefixed
■Local nonprefixed
■Global prefixed
Oracle Database supports all three types. However, there are some restrictions. For example, a key cannot be an expression
  when creating a local unique index on a partitioned table.    
 

Local Prefixed Indexes:
A local index is prefixed if it is partitioned on a left prefix of the index columns and the subpartioning key is included in the index key.
   Local prefixed indexes can be unique or nonunique.

Local Nonprefixed Indexes:
A local index is nonprefixed if it is not partitioned on a left prefix of the index columns or if the index key does not include the subpartitioning key.
     You cannot have a unique local nonprefixed index unless the partitioning key is a subset of the index key.   


Global Partitioned Indexes:


Prefixed and Nonprefixed Global Partitioned Indexes:
A global partitioned index is prefixed if it is partitioned on a left prefix of the index columns.
A global partitioned index is nonprefixed if it is not partitioned on a left prefix of the index columns.
Oracle does not support global nonprefixed partitioned indexes.

Global prefixed partitioned indexes can be unique or nonunique. Nonpartitioned indexes are treated as global prefixed nonpartitioned indexes.

Management of Global Partitioned Indexes:
Global partitioned indexes are harder to manage than local indexes because of the following
■When the data in an underlying table partition is moved or removed (SPLIT, MOVE, DROP, or TRUNCATE), all partitions of a global index are affected.
   Consequently global indexes do not support partition independence.
■When an underlying table partition or subpartition is recovered to a point in time, all corresponding entries in a global index must be recovered to
   the same point in time. Because these entries may be scattered across all partitions or subpartitions of the index, mixed with entries for other
    partitions or subpartitions that are not being recovered, there is no way to accomplish this except by re-creating the entire global index.




Summary of Partitioned Index Types
Table 3–1 summarizes the types of partitioned indexes that Oracle supports. The key points are:
■If an index is local, then it is equipartitioned with the underlying table. Otherwise, it is global.
■A prefixed index is partitioned on a left prefix of the index columns. Otherwise, it is nonprefixed.



Performance Implications of Prefixed and Nonprefixed Indexes:
It is more expensive to probe into a nonprefixed index than to probe into a prefixed index. If an index is prefixed (either local or global) and Oracle is
   presented with a predicate involving the index columns, then partition pruning can restrict application of the predicate to a subset of the index partitions.
   

ALTER TABLE sales MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998 COMPRESS;

ALTER TABLE sales MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998 COMPRESS FOR ARCHIVE LOW;

ALTER TABLE sales MODIFY PARTITION sales_q1_1998 REBUILD UNUSABLE LOCAL INDEXES;

Partitioning for Availability, Manageability, and Performance 3-29 You can also include the UPDATE INDEXES clause in the MOVE statement in order for the entire operation to be completed automatically without any negative effect on users accessing the table.

ALTER TABLE sales MERGE PARTITIONS sales_q1_1998, sales_q2_1998 INTO PARTITION sales_1_1998 TABLESPACE ts_arch_1_1998 COMPRESS FOR OLTP UPDATE INDEXES;




CREATE TABLE orders
(
order_id   NUMBER(12),
order_date  date,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
PARTITION BY RANGE(order_date)
(
PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY') ),
PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
);


ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix;
ALTER INDEX sales_area_ix REBUILD PARTITION feb99_ix;
ALTER INDEX sales_area_ix REBUILD PARTITION mar99_ix;
...
ALTER INDEX sales_area_ix REBUILD PARTITION dec99_ix;


DELETE FROM sales partition (dec98);
ALTER TABLE sales DROP PARTITION dec98;


ALTER TABLE sales DROP PARTITION dec98 UPDATE INDEXES


DELETE FROM sales partition (dec94);
ALTER TABLE sales DROP PARTITION dec94;



ALTER INDEX npr DROP PARTITION P1;
ALTER INDEX npr REBUILD PARTITION P2;


If the partition being moved contains any data, then indexes may be marked UNUSABLE according to the following table:


CREATE TABLE print_media
( product_id NUMBER(6) primary key
, ad_textdocs_ntab textdoc_tab
)
NESTED TABLE ad_textdocs_ntab STORE AS equi_nestedtab
( (document_typ NOT NULL)
STORAGE (INITIAL 8M)
)
PARTITION BY RANGE (product_id)
(
PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20)
);

-- Insert into base table
INSERT INTO print_media VALUES (1,textdoc_tab(textdoc_typ('xx'), textdoc_typ('yy')));
INSERT INTO print_media VALUES (11,textdoc_tab(textdoc_typ('aa'), textdoc_typ('bb')));
COMMIT;
-- Insert into nested table
INSERT INTO TABLE
(SELECT p.ad_textdocs_ntab FROM print_media p WHERE p.product_id = 11) VALUES ('cc');
SELECT * FROM print_media;

CREATE TABLE print_media2
( product_id NUMBER(6)
, ad_textdocs_ntab textdoc_tab
)
NESTED TABLE ad_textdocs_ntab STORE AS equi_nestedtab2
( (document_typ NOT NULL)
  STORAGE (INITIAL 8M)
)
PARTITION BY RANGE (product_id)
(
PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20)
);

EXEC dbms_redefinition.start_redef_table('qn', 'print_media', 'print_media2');

DECLARE
error_count pls_integer := 0;
BEGIN
dbms_redefinition.copy_table_dependents('qn', 'print_media', 'print_media2',
0, true, false, true, false,
error_count);
dbms_output.put_line('errors := ' || to_char(error_count));
END;

EXEC dbms_redefinition.finish_redef_table('qn', 'print_media', 'print_media2');


INDEX ... REBUILD PARTITION/SUBPARTITION
This statement rebuilds an index partition or subpartition unconditionally.
■ALTER TABLE ... MODIFY PARTITION/SUBPARTITION ... REBUILD UNUSABLE LOCAL INDEXES
This statement finds all of the unusable indexes for the given table partition or subpartition and rebuilds them. It only rebuilds an index partition if it has
 been marked UNUSABLE.

ALTER TABLE scubagear MODIFY PARTITION p1 REBUILD UNUSABLE LOCAL INDEXES;

Renaming a Table Partition/Subpartition/Index Partitions
ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks;
ALTER TABLE ... RENAME SUBPARTITION
ALTER INDEX ... RENAME PARTITION
ALTER INDEX scuba RENAME SUBPARTITION sys_subp3254 TO bcd_types;


ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD

DELETE FROM sales PARTITION (dec98);
ALTER TABLE sales TRUNCATE PARTITION dec98;

ALTER TABLE sales TRUNCATE PARTITION dec98 UPDATE INDEXES;

ALTER TABLE diving TRUNCATE SUBPARTITION us_locations DROP STORAGE;




CREATE SMALLFILE TABLESPACE old_orders DATAFILE 'old_orders'
SIZE 15M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;


CREATE TABLE cdrs
( id NUMBER
, cust_id NUMBER
, from_number VARCHAR2(20)
, to_number VARCHAR2(20)
, date_of_call DATE
, distance VARCHAR2(1)
, call_duration_in_s NUMBER(4)
) PARTITION BY RANGE(date_of_call)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY HASH(cust_id)
SUBPARTITIONS 16
(PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2005','dd-MON-yyyy')))
PARALLEL;


Partition maintenance operations are most easily performed on local indexes. Local indexes do not invalidate a global index when partition management takes place.
 Use INCLUDING INDEXES in the PEL statement to exchange the local indexes with the equivalent indexes on the separate table so that no index partitions get
  invalidated. For PEL, you can update global indexes as part of the load. Use the UPDATE GLOBAL INDEXES extension to the PEL statement. If an index requires
   updating, then the PEL takes much longer.


If your materialized views and underlying tables use comparable partitioning strategies, then PEL can be an extremely powerful way to keep materialized views up-to-date manually. For example, if both your underlying table and your materialized view use range partitioning, then you can consider PEL to keep your underlying table and materialized view up-to-date. The total data refresh scenario would work as follows:
■Create tables to enable PEL against the tables and materialized views.
■Load data into the tables, build the indexes, and implement any constraints.
■Update the base tables using PEL.
■Update the materialized views using PEL.
■Execute ALTER MATERIALIZED VIEW CONSIDER FRESH for every materialized view you updated using this strategy.


ALTER TABLE sales DROP PARTITION sales_1995 UPDATE GLOBAL INDEXES PARALLEL;

The first time you make a compressed partition part of an existing, fully uncompressed partitioned table, you must either drop all existing bitmap indexes or mark them UNUSABLE before adding a compressed partition.

ALTER SESSION enable parallel ddl;
ALTER TABLE sales
MOVE PARTITION sales_1995
COMPRESS FOR OLTP
PARALLEL NOLOGGING;




CREATE UNIQUE INDEX orders_pk
ON orders(order_id)
GLOBAL PARTITION BY HASH (order_id)
( PARTITION p1 TABLESPACE Q1_ORDERS
, PARTITION p2 TABLESPACE Q2_ORDERS
, PARTITION p3 TABLESPACE Q3_ORDERS
, PARTITION p4 TABLESPACE Q4_ORDERS
) NOLOGGING;


ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY (order_id) USING INDEX;



As with tables containing local indexes, the affected partitions are locked to prevent DML operations against the affected table partitions. However, unlike the index maintenance for local indexes, any global index is still fully available for DML operations and does not affect the online availability of the OLTP system.


Conceptually and technically, the index maintenance for global indexes for a partition maintenance operation is comparable to the index maintenance that would become necessary for a semantically identical DML operation.


The following example drops all data older than January 2006 from the orders table. Note that as part of the drop statement, an UPDATE GLOBAL INDEXES statement is executed, so that the global index remains usable throughout the maintenance operation. Any local index partitions are dropped as part of this operation.
ALTER TABLE orders DROP PARTITION p_before_jan_2006 UPDATE GLOBAL INDEXES

UPDATE INDEXES clause ensures that all indexes remain usable throughout and after the operation, without additional rebuilds.
ALTER TABLE orders
MERGE PARTITIONS p_2006_jan,p_2006_feb
INTO PARTITION p_before_mar_2006 COMPRESS
TABLESPACE ts_low_cost
UPDATE INDEXES;


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


Automatic Parallel Degree Polic:
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.
The following is a summary of parallel statement processing when parallel degree policy is set to automatic.
1.A SQL statement is issued.
2.The statement is parsed and the optimizer determines the execution plan.
3.The threshold limit specified by the PARALLEL_MIN_TIME_THRESHOLD initialization parameter is checked.
a.If the execution time is less than the threshold limit, the SQL statement is run serially.
b.If the execution time is greater than the threshold limit, the statement is run in parallel based on the DOP that the optimizer calculates.



e.g:
alter system set PARALLEL_DEGREE_POLICY='AUTO';

automatic DOP: skipped because of IO calibrate statistics are missing


select * from v$io_calibration_status;

select name,asynch_io from v$datafile f,v$iostat_file i
    where f.file#=i.file_no
   and (filetype_name='Data File' or filetype_name='Temp File');

 
 set serveroutput on;

 DECLARE
    lat INTEGER;
    iops INTEGER;
    mbps INTEGER;
  BEGIN
  --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
    DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
    DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
    dbms_output.put_line('max_mbps = ' || mbps);
  end;


PARALLEL_MIN_TIME_THRESHOLD is the second initialization parameter that controls automatic DOP. It specifies the minimum execution time a statement should have
 before the statement is considered for automatic DOP. By default, this is 10 seconds. The optimizer first calculates a serial execution plan for the SQL statement;
  if the estimated execution elapsed time is greater than PARALLEL_MIN_TIME_THRESHOLD (10 seconds), the statement becomes a candidate for automatic DOP

Controlling Automatic Degree of Parallelism
There are two initialization parameters that control automatic DOP, PARALLEL_DEGREE_POLICY and PARALLEL_MIN_TIME_THRESHOLD. They are also described in "Automatic Parallel Degree Policy" on page 8-10 and "Controlling Automatic DOP, Parallel Statement Queuing, and In-Memory Parallel Execution" on page 8-13. There are also two hints you can use to control parallelism.


ALTER SESSION SET parallel_degree_policy = limited;
ALTER TABLE emp parallel (degree default);


hint:
PARALLEL/NO_PARALLEL

The following example illustrates forcing the statement to be executed in parallel:
SELECT /*+parallel */ ename, dname FROM emp e, dept d WHERE e.deptno=d.deptno;

The following example illustrates forcing the statement to be executed in parallel with a degree of 10:
SELECT /*+ parallel(10) */ ename, dname FROM emp e, dept d WHERE e.deptno=d.deptno;

The following example illustrates forcing the statement to be executed serially:
SELECT /*+ no_parallel */ ename, dname FROM emp e, dept d WHERE e.deptno=d.deptno;

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;

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;


If the size of the object is larger than the size of the buffer cache (single instance) or the size of the buffer cache multiplied by the number of
active instances in an Oracle RAC cluster, then the object is read using direct-path reads.



■NO_STATEMENT_QUEUING
When PARALLEL_DEGREE_POLICY is set to AUTO, this hint enables a statement to bypass the parallel statement queue. For example:
SELECT /*+ NO_STATEMENT_QUEUING */ emp.last_name, dpt.department_name
FROM employees emp, departments dpt
WHERE emp.department_id = dpt.department_id;


STATEMENT_QUEUING
When PARALLEL_DEGREE_POLICY is not set to AUTO, this hint enables a statement to be delayed and to only run when parallel processes are available to run at the requested DOP. For example:
SELECT /*+ STATEMENT_QUEUING */ emp.last_name, dpt.department_name
FROM employees emp, departments dpt
WHERE emp.department_id = dpt.department_id;



To limit inter-node parallel execution, you can control parallel execution in an Oracle RAC environment using the PARALLEL_FORCE_LOCAL initialization parameter.
 By setting this parameter to TRUE, the parallel server processes can only execute on the same Oracle RAC node where the SQL statement was started.

Use the NOLOGGING clause of the CREATE TABLE, CREATE INDEX, ALTER TABLE, and ALTER INDEX statements to disable undo and redo log generation


Decision to Parallelize (Query Part)
The query part of a CREATE TABLE ... AS SELECT statement can be parallelized only if the following conditions are satisfied:
■The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the CREATE part of the statement has a PARALLEL clause specification or the schema objects referred to in the query have a PARALLEL declaration associated with them.
■At least one table specified in the query requires either a full table scan or an index range scan spanning multiple partitions



When parallel DML is disabled, no DML is executed in parallel even if the PARALLEL hint is used.

When parallel DML is enabled in a session, all DML statements in this session are considered for parallel execution. However, even if parallel DML is enabled, the DML operation may still execute serially if there are no parallel hints or no tables with a parallel attribute or if restrictions on parallel operations are violated.


Functions in Parallel Queries
In a SELECT statement or a subquery in a DML or DDL statement, a user-written function may be executed in parallel in any of the following cases:
■If it has been declared with the PARALLEL_ENABLE keyword
■If it is declared in a package or type and has a PRAGMA RESTRICT_REFERENCES clause that indicates all of WNDS, RNPS, and WNPS
■If it is declared with CREATE FUNCTION and the system can analyze the body of the PL/SQL code and determine that the code neither writes to the database nor reads or modifies package variables
Other parts of a query or subquery can sometimes execute in parallel even if a given function execution must remain serial.


Parameters Establishing Resource Limits for Parallel Operations
You can set initialization parameters to determine resource limits. The parameters that establish resource limits are discussed in the following topics:
■PARALLEL_FORCE_LOCAL
■PARALLEL_MAX_SERVERS
■PARALLEL_MIN_PERCENT
■PARALLEL_MIN_SERVERS
■PARALLEL_MIN_TIME_THRESHOLD
■PARALLEL_SERVERS_TARGET
■SHARED_POOL_SIZE
■Computing Additional Memory Requirements for Message Buffers
■Adjusting Memory After Processing Begins




By default, Oracle Database allocates parallel execution buffers from the shared pool.
If Oracle Database displays the following error on startup, you should reduce the value for SHARED_POOL_SIZE low enough so your database starts:
ORA-27102: out of memory
SVR4 Error: 12: Not enough space
After reducing the value of SHARED_POOL_SIZE, you might see the error:
ORA-04031: unable to allocate 16084 bytes of shared memory
("SHARED pool","unknown object","SHARED pool heap","PX msg pool")
If so, execute the following query to determine why Oracle Database could not allocate the 16,084 bytes:
SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='SHARED POOL'
GROUP BY ROLLUP (NAME);
Your output should resemble the following:
NAME SUM(BYTES)
-------------------------- ----------
PX msg pool 1474572
free memory 562132
2036704

The best way to resolve this problem might be to choose a different join method; a nested loop join might be the best option. Alternatively, if one join table is small relative to the other, a BROADCAST distribution method can be hinted using PQ_DISTRIBUTE hint. Note that the optimizer considers the BROADCAST distribution method, but requires OPTIMIZER_FEATURES_ENABLE set to 9.0.2 or higher.


The V$PQ_TQSTAT view provides a detailed report of message traffic at the table queue level. V$PQ_TQSTAT data is valid only when queried from a session that is executing parallel SQL statements. A table queue is the pipeline between query server groups, between the parallel execution coordinator and a query server group, or between a query server group and the coordinator. The table queues are represented explicitly in the operation column by PX SEND <partitioning type> (for example, PX SEND HASH) and PX RECEIVE. For backward compatibility, the row labels of PARALLEL_TO_PARALLEL, SERIAL_TO_PARALLEL, or PARALLEL_TO_SERIAL continue to have the same semantics as previous releases and can be used as before to deduce the table queue allocation. In addition, the top of the parallel execution plan is marked by a new node with operation PX COORDINATOR.

V$PQ_TQSTAT has a row for each query server process that it reads from or writes to in each table queue. A table queue connecting 10 consumer processes to 10 producer processes has 20 rows in the view. Total the bytes column and group by TQ_ID, and the table queue identifier, to obtain the total number of bytes sent through each table queue. Compare this to the optimizer estimates; large variations might indicate a need to analyze the data using a larger sample.


The processes shown in the output from the previous example using
GV$PX_SESSION collaborate to complete the same task. The next example shows the execution of a join query to determine the progress of these processes in terms of physical reads. Use this query to track any specific statistic:
SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
NAME "Stat Name", VALUE
FROM GV$PX_SESSTAT A, V$STATNAME B
WHERE A.STATISTIC# = B.STATISTIC# AND NAME LIKE 'PHYSICAL READS'
AND VALUE > 0 ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;
Your output should resemble the following:
QCSID SID Inst Group Set Stat Name VALUE
------ ----- ------ ------ ------ ------------------ ----------
9 9 1 physical reads 3863
9 7 1 1 1 physical reads 2
9 21 1 1 1 physical reads 2


Consider data skew. If a join key involves excessive data skew, a hash join may require some parallel query servers to work more than others. Consider using a hint to cause a BROADCAST distribution method if the optimizer did not choose it. Note that the optimizer considers the BROADCAST distribution method only if the OPTIMIZER_FEATURES_ENABLE is set



The default logging attribute is LOGGING. However, if you have put the database in NOARCHIVELOG mode, by issuing ALTER DATABASE NOARCHIVELOG, then all operations that can be done without logging do not generate logs, regardless of the specified logging attribute.


Note that automatic DOP only parallelizes the DML part of a SQL statement if and only if parallel DML is enabled or forced.


conventional insert operation. For example, you can use /*+ noappend parallel */ with the SQL INSERT statement to perform a parallel conventional insert.
SQL> INSERT /*+ NOAPPEND PARALLEL */ INTO sales_hist SELECT * FROM sales;


In the following example, RMAN backs up all database files that have not been backed up in the last 7 days first, runs for 4 hours, and reads the blocks as fast as possible.
BACKUP DATABASE NOT BACKED UP SINCE 'sysdate - 7'
PARTIAL DURATION 4:00 MINIMIZE TIME;