ALTER SESSION FORCE PARALLEL DDL PARALLEL 1
ALTER SESSION FORCE PARALLEL DML PARALLEL 1
系统参数（ALTER SYSTEM）有没有类似设置？或者可否让指定用户登陆时自动执行上述命令或者可实现相同功能的命令？
搜了半天帮助文档也没找到类似内容，各位高人有没有解决方法呢？
7 个解决方案
#1
可以在写SQL时用hint 强制来使用并行。
HINT 提示修改
parallel(table,4) 并行度为4
parallel(table) 如果使用parallel 但未指定并行度，则DOP要通过初始化参数CPU_count 和Parallel_THREADS_PER_CPU计算得到，
并行度为4的程序，最多可以分配或创建9个并行执行服务器来满足这个事务操作，所以并行操作速度有很大提高，但对CPU占用比较多
并行操作增加了事务操作的性能，但会连续的记录重做日志，并且造成瓶颈，所以可以使用nologging 模式来避免瓶颈
sql> alter table table_name NOLOGGING;
sql> select /*+ parallel(table,4)*/ count(*) from table;
并行处理服务器（Parallel Execution Servers）
并行处理服务器默认是开启的。因为PARALLEL_MAX_SERVERS参数的默认值是大于0的；
在SESSION级别修改并行处理
1 关闭并行处理
ALTER SESSION DISABLE PARALLEL DML|DDL|QUERY;
如，关闭DDL的并行处理
ALTER SESSION DISABLE PARALLEL DDL;
2 开启并行处理
ALTER SESSION ENABLE PARALLEL DML|DDL|QUERY;
如，开启DML的并行处理
ALTER SESSION ENABLE PARALLEL DML;
SQL语句强制并行处理
ALTER SESSION FORCE PARALLEL DML|DDL|QUERY;
如：强制并行DDL，并行度为5
ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;
注：强制并行处理的优先级比SQL语句中HINT的优先级要低
#2
做个标记。。
#3
这些办法我都知道的
我就是希望用户登陆时可以自动执行类似这种语句，ALTER SESSION FORCE PARALLEL DDL PARALLEL 1
或者在有系统参数可以起相同作用的
有没有办法呢？
我就是希望用户登陆时可以自动执行类似这种语句，ALTER SESSION FORCE PARALLEL DDL PARALLEL 1
或者在有系统参数可以起相同作用的
有没有办法呢？
#4
PARALLEL_MAX_SERVERSProperty Description
Parameter type Integer
Default value Derived from the values of CPU_COUNT, PARALLEL_THREADS_PER_CPU, and PGA_AGGREGATE_TARGET
Modifiable ALTER SYSTEM
Range of values 0 to 3599
Real Application Clusters Multiple instances must have the same value.
Note:
This parameter applies to parallel execution in exclusive mode as well as in a Real Application Clusters environment.
PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle increases the number of processes from the number created at instance startup up to this value.
If you set this parameter too low, some queries may not have a parallel execution process available to them during query processing. If you set it too high, memory resource shortages may occur during peak periods, which can degrade performance.
Parameter type Integer
Default value Derived from the values of CPU_COUNT, PARALLEL_THREADS_PER_CPU, and PGA_AGGREGATE_TARGET
Modifiable ALTER SYSTEM
Range of values 0 to 3599
Real Application Clusters Multiple instances must have the same value.
Note:
This parameter applies to parallel execution in exclusive mode as well as in a Real Application Clusters environment.
PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle increases the number of processes from the number created at instance startup up to this value.
If you set this parameter too low, some queries may not have a parallel execution process available to them during query processing. If you set it too high, memory resource shortages may occur during peak periods, which can degrade performance.
#5
在文档中有这个参数PARALLEL_MAX_SERVERS
不过是设置最大并行数的
呵呵 好像还是没有解决楼主的需求
这个我也不太了解
楼主可以看看在用户的概要文件PROFILE里能不能设置
我现在没环境 不能帮你看了
不过是设置最大并行数的
呵呵 好像还是没有解决楼主的需求
这个我也不太了解
楼主可以看看在用户的概要文件PROFILE里能不能设置
我现在没环境 不能帮你看了
#6
解决了，可以用Trigger，谢谢大家捧场。
CREATE OR REPLACE TRIGGER tri_aftlogon_session_options
AFTER LOGON ON DATABASE
DECLARE
dbusername VARCHAR2(30);
cmdstr VARCHAR2(64);
BEGIN
cmdstr := 'ALTER SESSION DISABLE PARALLEL DDL';
dbusername := SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY');
IF upper(dbusername) = 'TESTDBUSER'
then
EXECUTE IMMEDIATE cmdstr;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE OR REPLACE TRIGGER tri_aftlogon_session_options
AFTER LOGON ON DATABASE
DECLARE
dbusername VARCHAR2(30);
cmdstr VARCHAR2(64);
BEGIN
cmdstr := 'ALTER SESSION DISABLE PARALLEL DDL';
dbusername := SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY');
IF upper(dbusername) = 'TESTDBUSER'
then
EXECUTE IMMEDIATE cmdstr;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
#7
show parameter parallel 看下先
#1
可以在写SQL时用hint 强制来使用并行。
HINT 提示修改
parallel(table,4) 并行度为4
parallel(table) 如果使用parallel 但未指定并行度，则DOP要通过初始化参数CPU_count 和Parallel_THREADS_PER_CPU计算得到，
并行度为4的程序，最多可以分配或创建9个并行执行服务器来满足这个事务操作，所以并行操作速度有很大提高，但对CPU占用比较多
并行操作增加了事务操作的性能，但会连续的记录重做日志，并且造成瓶颈，所以可以使用nologging 模式来避免瓶颈
sql> alter table table_name NOLOGGING;
sql> select /*+ parallel(table,4)*/ count(*) from table;
并行处理服务器（Parallel Execution Servers）
并行处理服务器默认是开启的。因为PARALLEL_MAX_SERVERS参数的默认值是大于0的；
在SESSION级别修改并行处理
1 关闭并行处理
ALTER SESSION DISABLE PARALLEL DML|DDL|QUERY;
如，关闭DDL的并行处理
ALTER SESSION DISABLE PARALLEL DDL;
2 开启并行处理
ALTER SESSION ENABLE PARALLEL DML|DDL|QUERY;
如，开启DML的并行处理
ALTER SESSION ENABLE PARALLEL DML;
SQL语句强制并行处理
ALTER SESSION FORCE PARALLEL DML|DDL|QUERY;
如：强制并行DDL，并行度为5
ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;
注：强制并行处理的优先级比SQL语句中HINT的优先级要低
#2
做个标记。。
#3
这些办法我都知道的
我就是希望用户登陆时可以自动执行类似这种语句，ALTER SESSION FORCE PARALLEL DDL PARALLEL 1
或者在有系统参数可以起相同作用的
有没有办法呢？
我就是希望用户登陆时可以自动执行类似这种语句，ALTER SESSION FORCE PARALLEL DDL PARALLEL 1
或者在有系统参数可以起相同作用的
有没有办法呢？
#4
PARALLEL_MAX_SERVERSProperty Description
Parameter type Integer
Default value Derived from the values of CPU_COUNT, PARALLEL_THREADS_PER_CPU, and PGA_AGGREGATE_TARGET
Modifiable ALTER SYSTEM
Range of values 0 to 3599
Real Application Clusters Multiple instances must have the same value.
Note:
This parameter applies to parallel execution in exclusive mode as well as in a Real Application Clusters environment.
PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle increases the number of processes from the number created at instance startup up to this value.
If you set this parameter too low, some queries may not have a parallel execution process available to them during query processing. If you set it too high, memory resource shortages may occur during peak periods, which can degrade performance.
Parameter type Integer
Default value Derived from the values of CPU_COUNT, PARALLEL_THREADS_PER_CPU, and PGA_AGGREGATE_TARGET
Modifiable ALTER SYSTEM
Range of values 0 to 3599
Real Application Clusters Multiple instances must have the same value.
Note:
This parameter applies to parallel execution in exclusive mode as well as in a Real Application Clusters environment.
PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle increases the number of processes from the number created at instance startup up to this value.
If you set this parameter too low, some queries may not have a parallel execution process available to them during query processing. If you set it too high, memory resource shortages may occur during peak periods, which can degrade performance.
#5
在文档中有这个参数PARALLEL_MAX_SERVERS
不过是设置最大并行数的
呵呵 好像还是没有解决楼主的需求
这个我也不太了解
楼主可以看看在用户的概要文件PROFILE里能不能设置
我现在没环境 不能帮你看了
不过是设置最大并行数的
呵呵 好像还是没有解决楼主的需求
这个我也不太了解
楼主可以看看在用户的概要文件PROFILE里能不能设置
我现在没环境 不能帮你看了
#6
解决了，可以用Trigger，谢谢大家捧场。
CREATE OR REPLACE TRIGGER tri_aftlogon_session_options
AFTER LOGON ON DATABASE
DECLARE
dbusername VARCHAR2(30);
cmdstr VARCHAR2(64);
BEGIN
cmdstr := 'ALTER SESSION DISABLE PARALLEL DDL';
dbusername := SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY');
IF upper(dbusername) = 'TESTDBUSER'
then
EXECUTE IMMEDIATE cmdstr;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE OR REPLACE TRIGGER tri_aftlogon_session_options
AFTER LOGON ON DATABASE
DECLARE
dbusername VARCHAR2(30);
cmdstr VARCHAR2(64);
BEGIN
cmdstr := 'ALTER SESSION DISABLE PARALLEL DDL';
dbusername := SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY');
IF upper(dbusername) = 'TESTDBUSER'
then
EXECUTE IMMEDIATE cmdstr;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
#7
show parameter parallel 看下先