Oralce 检查表和索引的并行度 DOP 脚本

数据库的并行度使用需要很谨慎，很容易造成数据库运行缓慢以及严重的等待。

比较常见的由于 并行度 设置错误导致的等待
事件
：

• PX Deq Credit: send blkd
• PX Deq Credit: need buffer

错误的并行度设置往往可能是由于在创建索引或者重建索引时
开启
并行度创建，后来忘记关闭导致！

create index <indexname> on <table>（<columns>） parallel 4;
alter index <indexname> rebuild parallel 4;

AI写代码sql

* 1
* 2

✅ 使用并行度设置后的正确操作：

alter index <indexname> noparallel;

AI写代码sql

* 1

当我们遇到这样的等待事件很严重时，可以使用下方脚本快速查看是否存在不正确的并行度设置！

📢 注意： 以下
脚本
已经过内部测试，但是，不保证它对您有用。确保在使用前在测试环境中运行它。

该 SQL 查询当前数据库主机 CPU 数以及每个 CPU 默认的并行度：

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' );

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9

该 SQL 检查当前数据库中所有用户中存在不同并行度的 表：

set pagesize1000
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;

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22
* 23
* 24
* 25
* 26

📢 注意： 如果查询出 Parallel 列的值为 Serial 就证明并行度都是 1，为正常。

该 SQL 检查当前数据库中所有用户中存在不同并行度的 索引：

set pagesize1000
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;

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22

📢 注意： 如果查询出 Parallel 列的值为 Serial 就证明并行度都是 1，为正常。

该 SQL 检查具有不同 DOP 的索引的表：

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;

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19

📢 注意：查询结果为空代表没有不同 DOP 的索引的表，正常。

本文的脚本来自于 MOS：

Script to Report the Degree of Parallelism DOP on Tables and Indexes (Doc ID 270837.1)