检查degree >1 的
select substr(owner,1,15) Owner , ltrim(degree) Degree,
ltrim(instances) Instances,
count(*) “Num Tables” , ‘Parallel’
from dba_tables
where ( trim(degree) > ‘1’ )
and table_name not like ‘ET$%’
group by owner, degree , instances
order by owner;
–select *from dba_tables where ( trim(degree) > ‘1’ )
select substr(owner,1,15) Owner ,
substr(trim(degree),1,7) Degree ,
substr(trim(instances),1,9) Instances ,
count(*) “Num Indexes”,
‘Parallel’
from dba_indexes
where ( trim(degree) > ‘1’ ) or
( trim(instances) != ‘1’ and trim(instances) != ‘0’ )
group by owner, degree , instances
order by owner;
–select * from dba_indexes where ( trim(degree) > ‘1’ ) or ( trim(instances) != ‘1’ and trim(instances) != ‘0’ )
alter index xxx noparallel change default to 1
—————– degree default
For index maintenance (online rebuild). The session level degree of parallelism was altered to a higher value.
Alter session force parallel DDL parallel 12;
Alter session force parallel Query parallel 12;
Alter session force parallel DML parallel 12;
ALTER INDEX index_name rebuild ONLINE;
QUESTION : After the above maintenance, the index DOP is reflecting 12 instead of the default.
Is this an expected behavior ?
CHANGES
CAUSE
This is expected behavior.
SOLUTION
FORCE Clause
FORCE forces parallel execution of subsequent statements in the session. If no parallel clause or hint is specified, then a default degree of parallelism is used. This clause overrides any parallel_clause specified in subsequent statements in the session but is overridden by a parallel hint.
DML: Provided no parallel DML restrictions are violated, subsequent DML statements in the session are executed with the default degree of parallelism, unless a degree is specified in this clause.
DDL: Subsequent DDL statements in the session are executed with the default degree of parallelism, unless a degree is specified in this clause. Resulting database objects will have associated with them the prevailing degree of parallelism. >>>>>>>>>>>>>>
Specifying FORCE DDL automatically causes all tables created in this session to be created with a default level of parallelism. The effect is the same as if you had specified the parallel_clause (with the default degree) in the CREATE TABLE statement.
QUERY: Subsequent queries are executed with the default degree of parallelism, unless a degree is specified in this clause.
——————–
This article has been written to explain the formula to compute the new default value for the Database parameter PARALLEL_MAX_SERVERS in 11.2.0.2 and above.
DETAILS
With 11.2.0.2 & above, there is a new method to compute the default for PARALLEL_MAX_SERVERS.
In the Oracle Rdbms Reference Guide we find:
parallel_max_servers = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5
In the formula, the value assigned to concurrent_parallel_users running at the default degree of parallelism on an
instance is dependent on the memory management setting.
– If automatic memory management is disabled (manual mode), then the value of concurrent_parallel_users is 1.
– If PGA automatic memory management is enabled, then the value of concurrent_parallel_users is 2.
– If global memory management or SGA memory target is used in addition to PGA automatic memory management,
then the value of concurrent_parallel_users is 4.
The value is capped by processes -15 (this is true for versions prior 11.2.0.2 as well).
As example we have the following values
parallel_threads_per_cpu = 2
cpu_count = 4
pga_aggregate_target = 500M
sga_target = 900M
processes = 150
parallel_max_servers = 2 * 4 * 4 * 5 = 160
parallel_max_servers = min( 150-15 , 160 ) = 135
So with these values we get a default of 135 for parallel_max_servers.
Note if the parallel_max_servers is reduced due to value of processes, then you see similar to the following in alert log (e.g. at instance start up):
Mon May 06 18:43:06 2013 Adjusting the default value of parameter parallel_max_servers from 160 to 135 due to the value of parameter processes (150) Starting ORACLE instance (normal)
——check degree
Provide script for a DBA to check the degree of parallelism on tables and indexes.
SOLUTION
Requirements
Any tool that can execute SQL in the database. A simple one is SQLPlus.
Configuring
No configuration needed, other than remove the column formatting lines if not executed in SQLPlus.
Instructions
The scripts can be run with copy and paste after connected to the database as a user who has select access on the queried objects.
Script
Check Script
————-
col name format a30
col value format a20
Rem How many CPU does the system have?
Rem Default degree of parallelism is
Rem Default = parallel_threads_per_cpu * cpu_count
Rem ————————————————-;
select substr(name,1,30) Name , substr(value,1,5) Value
from v$parameter
where name in (‘parallel_threads_per_cpu’ , ‘cpu_count’ );
col owner format a30
col degree format a10
col instances format a10
Rem Normally DOP := degree * Instances
Rem See the following Note for the exact formula.
Rem Note:260845.1 Old and new Syntax for setting Degree of Parallelism
Rem How many tables a user have with different DOPs
Rem ——————————————————-;
select * from (
select substr(owner,1,15) Owner , ltrim(degree) Degree,
ltrim(instances) Instances,
count(*) “Num Tables” , ‘Parallel’
from all_tables
where ( trim(degree) != ‘1’ and trim(degree) != ‘0’ ) or
( trim(instances) != ‘1’ and trim(instances) != ‘0’ )
group by owner, degree , instances
union
select substr(owner,1,15) owner , ‘1’ , ‘1’ ,
count(*) , ‘Serial’
from all_tables
where ( trim(degree) = ‘1’ or trim(degree) = ‘0’ ) and
( trim(instances) = ‘1’ or trim(instances) = ‘0’ )
group by owner
)
order by owner;
Rem How many indexes a user have with different DOPs
Rem —————————————————;
select * from (
select substr(owner,1,15) Owner ,
substr(trim(degree),1,7) Degree ,
substr(trim(instances),1,9) Instances ,
count(*) “Num Indexes”,
‘Parallel’
from all_indexes
where ( trim(degree) != ‘1’ and trim(degree) != ‘0’ ) or
( trim(instances) != ‘1’ and trim(instances) != ‘0’ )
group by owner, degree , instances
union
select substr(owner,1,15) owner , ‘1’ , ‘1’ ,
count(*) , ‘Serial’
from all_indexes
where ( trim(degree) = ‘1’ or trim(degree) = ‘0’ ) and
( trim(instances) = ‘1’ or trim(instances) = ‘0’ )
group by owner
)
order by owner;
col table_name format a35
col index_name format a35
Rem Tables that have Indexes with not the same DOP
Rem !!!!! This command can take some time to execute !!!
Rem —————————————————;
set lines 150
select substr(t.owner,1,15) Owner ,
t.table_name ,
substr(trim(t.degree),1,7) Degree ,
substr(trim(t.instances),1,9) Instances,
i.index_name ,
substr(trim(i.degree),1,7) Degree ,
substr(trim(i.instances),1,9) Instances
from all_indexes i,
all_tables t
where ( trim(i.degree) != trim(t.degree) or
trim(i.instances) != trim(t.instances) ) and
i.owner = t.owner and
i.table_name = t.table_name;
Sample Output
NAME VALUE
—————————— ——————–
cpu_count 2
parallel_threads_per_cpu 2
OWNER DEGREE INSTANCES Num Tables ‘PARALLEL’
—————————— ———- ———- ———- ————
APEX_030200 1 1 360 Serial
APEX_040000 1 1 426 Serial
APEX_WS1 1 1 18 Serial
APPQOSSYS 1 1 4 Serial
CTXSYS 1 1 49 Serial
DWHBW 8 1 1 Parallel
DWH_DM DEFAULT DEFAULT 1 Parallel
… OWNER DEGREE INSTANCES Num Indexes ‘PARALLEL’
—————————— ———- ———- ———– ———–
APEX_030200 1 1 946 Serial
APEX_040000 1 1 1177 Serial
APEX_WS1 1 1 28 Serial
CTXSYS 1 1 59 Serial
DWHBW 1 1 20 Serial
DWH_DM DEFAULT DEFAULT 1 Parallel … OWNER TABLE_NAME DEGREE INSTANCES INDEX_NAME DEGREE INSTANCES
—————————— ———————————– ———- ———- ———————————– ———- ———-
OWBSYS CMPFCOCLASSES 1 1 IDX_FCOUOID DEFAULT DEFAULT
OWBSYS CMPFCOCLASSES 1 1 IDX_FCOCLASSNAMEELEMID DEFAULT DEFAULT
OWBSYS CMPFCOCLASSES 1 1 IDX_FCOOWNINGFOLDER DEFAULT DEFAULT
OWBSYS CMPFCOCLASSES 1 1 IDX_FCONAME DEFAULT DEFAULT
——————instance default————-
This notes explain the differences for setting of the DOP ( Degree of Parallelism )
on a object between old and new syntax.
SCOPE
DBA’s , Developer’s and Engineers.
DETAILS
Before 8.1 we used the syntax
parallel(object, degree, instances)
to define the degree of parallelism. In 8.1 we changed the syntax to
parallel(object, degree )
There was a documentation bug until 10g that the documentation
only used the old syntax.
For the backward compatibilty we convert internally the old into the
next syntax.
When we use the old syntax, a value for instances, does not mean that
we restrict slaves only on this instances.
1.) DOP’s on Tables and Indexes
——————————–
We use the Matrix above to convert table /index setting’s into the next syntax:
Degree Instances new Oracle DOP
——— ——— ——————
1 1 1
x Default x
x 1 x
1 Default Default
Default y y
1 y y
Default 1 Default
x y x*y
Default Default Default
Example:
create table test … parallel (degree 2 instances 3);
is the same as
create table test … parallel 6;
2.) DOP’s setting via Hints
=======都强制了ALTER SESSION 还是并行，这个instance defalut的值是什么呢？？？
GOAL
What is the reason for which a statement is executed in parallel even so all the conditions of having this in serial are met?
The degree for all the tables involved is set to 1
and
parallel_degree_limit CPU
parallel_degree_policy MANUAL
Much more even when setting:
ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL QUERY;
the statement is still executed in parallel.
SOLUTION
Parallelism was triggered by the fact that instances was set to default for one of the tables even if the degree was set to 1.
SQL> select owner, table_name, degree, instances from dba_tables where table_name=’LMCMPHQ’;
OWNER TABLE_NAME DEGREE INSTANCES
—————————— —————————— ———- —————————————-
TEST LMTEST 1 DEFAULT
This is expected behaviour as explained into “Old and New Syntax for Setting Degree of Parallelism (Doc ID 260845.1)” when degree is 1 and instances is DEFAULT then the DOP is DEFAULT.
————————-
Query executes with DEFAULT PX sessions when Degree=1 on object and no parallel hint is present
CAUSE
When a query shows parallel execution plan even if degree=1 and no hint used, check the value of “INSTANCES” from DBA_TABLES or DBA_INDEXES. A value of DEFAULT for INSTANCES will make the query use DEFAULT degree
e.g.
SQL> create table table_objects as select * from dba_objects union select * from dba_objects union select * from dba_objects union select * from dba_objects union select * from dba_objects union select * from dba_objects union select * from dba_objects; Table created.
SQL> create index table_objects_idx on TABLE_OBJECTS(OBJECT_TYPE,object_name) parallel(DEGREE 1 INSTANCES DEFAULT);
Index created.
SQL> explain plan for select /*+ index_ffs(table_objects,table_objects_idx) */ count(distinct object_name) from table_objects where OBJECT_TYPE='TABLE';
Explained.
SQL> select * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2868850136
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 175 (6)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 66 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 66 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 66 | | | Q1,01 | PCWP | |
| 5 | VIEW | VW_DAG_0 | 2387 | 153K| 175 (6)| 00:00:01 | Q1,01 | PCWP | |
| 6 | HASH GROUP BY | | 2387 | 184K| 175 (6)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 2387 | 184K| 175 (6)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 2387 | 184K| 175 (6)| 00:00:01 | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 2387 | 184K| 175 (6)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 2387 | 184K| 166 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 11 | INDEX FAST FULL SCAN| TABLE_OBJECTS_IDX | 2387 | 184K| 166 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - filter("OBJECT_TYPE"='TABLE')SQL> select index_name,degree,instances from dba_indexes where index_name='TABLE_OBJECTS_IDX';
INDEX_NAME DEGREE INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
TABLE_OBJECTS_IDX 1 DEFAULT
SQL> alter index table_objects_idx noparallel;
Index altered.
SQL> select index_name,degree,instances from dba_indexes where index_name='TABLE_OBJECTS_IDX';
INDEX_NAME DEGREE INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
TABLE_OBJECTS_IDX 1 1
SQL> explain plan for select /*+ index_ffs(table_objects,table_objects_idx) */ count(distinct object_name) from table_objects where OBJECT_TYPE='TABLE';
Explained.
SQL> select * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1073692753
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 168 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| 2 | VIEW | VW_DAG_0 | 2387 | 153K| 168 (2)| 00:00:01 |
| 3 | HASH GROUP BY | | 2387 | 184K| 168 (2)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| TABLE_OBJECTS_IDX | 2387 | 184K| 166 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("OBJECT_TYPE"='TABLE')As observed making the object to NoParallel makes the INSTANCES value to 1 or just to make INSTANCES=1 you can issue : alter index table_objects_idx PARALLEL(INSTANCES 1) ;
SOLUTION
When an object is created with PARALLEL and have INSTANCES=DEFAULT, it will make query to spawn DEFAULT degree. From 11gR2 onwards it is recommended not to use INSTANCES variable in PARALLEL clause
