暂无图片
暂无图片
4
暂无图片
暂无图片
暂无图片

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

原创 Lucifer三思而后行 2021-11-15
1337

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

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

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

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

create index <indexname> on <table>(<columns>) parallel 4; alter index <indexname> rebuild parallel 4;

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

alter index <indexname> noparallel;

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

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

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

该 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;

📢 注意: 如果查询出 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;

📢 注意: 如果查询出 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;

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

本文的脚本来自于 MOS:

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

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论