Oracle-RAC-11g-r2性能调优---解决查询慢问题

精品文档 1欢迎下载 知也无涯知也无涯 OracleOracle RACRAC 11g11g r2r2 查询太慢查询太慢 Oracle RAC 11g r2 查询太慢 Problem Description Redhat 5 双机 测试 1 双实例 ASM 磁盘组包含 3 个磁盘 SAN 在其中一个实例中执行 SELECT c operaccount c PASSWORD a PATH a dll a description 1 gif FROM hcs2000 dllnames a hcs2000 operdllnames b hcs2000 operaccount c WHERE a dllnameid b dllnameid AND b operid c operid AND upper c operaccount USER ORDER BY a dllnameid 第一次查询 25 秒 第二次查询 3 秒 第三次查询 1 6 秒 过 10 分钟后查 询 26 秒 测试 2 在其中一台主机上创建基于 ASM 磁盘组的单个实例 第一次查询 14 秒 第二次查询 3 秒 第三次查询 0 7 秒 第四次查询 3 5 秒 测试 3 在其中一台主机上创建基于文件系统的单个实例 第一次查询 5 秒 第二次查询 2 2 秒 第三次查询 2 1 秒 测试 4 在 PC 的 VMware 虚拟机里面单实例查询 只需 0 001 秒或 0 秒 测试 1 中的查询太慢了 请问怎么查看问题原因 如何调优 Dear customer 请您执行以下动作 如果可以 请在您提到的 4 个场景下都生成以下文件 并请添加 您的说明后 作为附件更新到 SR 上 ACTION PLAN 精品文档 2欢迎下载 1 Please generate 10046 trace for your sql SQL connect username password SQL alter session set timed statistics true SQL alter session set statistics level all SQL alter session set max dump file size unlimited SQL alter session set events 10046 trace name context forever level 12 SQL SQL alter session set events 10046 trace name context off 2 Format your 10046 trace file tkprof 例如 生成的文件应该是在您的 udump 路径下面 寻找 UDUMP 路径 请参考 SQL show parameter user dump dest 之后 format 您的文件 cd u01 OracleAPP oracle admin R1020 udump ls ltr tkprof r1020 ora 9638 trc 9638 output 3 请提交您 10046 trace 以及 tkprof 输出文件 9638 output Dear customer 目前来看 您问题表中遇到了并行的配置 为了进一步诊断 请 执行以下动作 并提供输出结果 ACTION PLAN 请分别在测试 2 在其中一台主机上创建基于 ASM 磁盘组的实例 以及 测试 4 在 PC 的 VMware 虚拟机里面单实例查询 的测试环境中执行以下动作 精品文档 3欢迎下载 SQL show parameter parallel min servers SQL select table name degree from dba tables where table name dllnames SQL select table name degree from dba tables where table name operdllnames SQL select table name degree from dba tables where table name operaccount 并请提供以上测试 2 4 环境的数据库 alert 日志 位于 bdump 下 面 SQL show parameter background dump dest The alert log is named as alert log Name ODM Data Collection SELECT c operaccount c PASSWORD a PATH a dll a description 1 gif FROM dllnames a operdllnames b operaccount c WHERE a dllnameid b dllnameid AND b operid c operid AND upper c operaccount USER ORDER BY a dllnameid call count cpu elapsed disk query current rows Parse 1 0 00 0 00 0 0 0 0 Execute 1 0 01 11 14 0 3 0 0 Fetch 2 0 03 2 24 0 0 0 1 total 4 0 05 13 39 0 3 0 1 Misses in library cache during parse 1 Optimizer mode ALL ROWS Parsing user id 5 精品文档 4欢迎下载 Rows Row Source Operation 1 PX COORDINATOR cr 3 pr 0 pw 0 time 0 us 0 PX SEND QC ORDER TQ10001 cr 0 pr 0 pw 0 time 0 us cost 3 size 68 card 1 0 SORT ORDER BY cr 0 pr 0 pw 0 time 0 us cost 3 size 68 card 1 0 PX RECEIVE cr 0 pr 0 pw 0 time 0 us 0 PX SEND RANGE TQ10000 cr 0 pr 0 pw 0 time 0 us 0 NESTED LOOPS cr 0 pr 0 pw 0 time 0 us 0 NESTED LOOPS cr 0 pr 0 pw 0 time 0 us cost 2 size 68 card 1 0 NESTED LOOPS cr 0 pr 0 pw 0 time 0 us cost 2 size 19 card 1 0 PX BLOCK ITERATOR cr 0 pr 0 pw 0 time 0 us 0 TABLE ACCESS FULL OPERACCOUNT cr 0 pr 0 pw 0 time 0 us cost 2 size 11 card 1 0 INDEX FULL SCAN OPERDLLNAMESINDEX cr 0 pr 0 pw 0 time 0 us cost 1 size 16 card 2 object id 73471 0 INDEX UNIQUE SCAN PK DLLNAMEID cr 0 pr 0 pw 0 time 0 us cost 0 size 0 card 1 object id 73465 0 TABLE ACCESS BY INDEX ROWID DLLNAMES cr 0 pr 0 pw 0 time 0 us cost 1 size 49 card 1 Elapsed times include waiting on following events Event waited on Times Max Wait Total Waited Waited rdbms ipc reply 2 0 00 0 00 os thread startup 96 0 15 10 46 PX Deq Join ACK 78 0 25 0 39 latch free 10 0 00 0 01 latch parallel query alloc buffer 1 0 00 0 00 PX Deq Parse Reply 66 0 04 0 21 SQL Net message to client 2 0 00 0 00 PX Deq Execute Reply 132 0 01 0 15 PX Deq Credit send blkd 15 1 98 2 03 SQL Net message from client 2 0 00 0 00 PX Deq Signal ACK RSG 70 0 00 0 01 latch call allocation 4 0 00 0 01 PX Deq Slave Session Stats 2 0 00 0 00 enq PS contention 2 0 00 0 00 精品文档 5欢迎下载 Elapsed times include waiting on following events Event waited on Times Max Wait Total Waited Waited SQL Net message to client 3 0 00 0 00 SQL Net message from client 3 59 90 59 90 rdbms ipc reply 2 0 00 0 00 os thread startup 96 0 15 10 46 PX Deq Join ACK 78 0 25 0 39 latch free 10 0 00 0 01 latch parallel query alloc buffer 1 0 00 0 00 PX Deq Parse Reply 66 0 04 0 21 PX Deq Execute Reply 132 0 01 0 15 PX Deq Credit send blkd 15 1 98 2 03 PX Deq Signal ACK RSG 70 0 00 0 01 latch call allocation 4 0 00 0 01 PX Deq Slave Session Stats 2 0 00 0 00 enq PS contention 2 0 00 0 00 FileName mytestas1 ora 4262 trc output FileComment Dear customer 感谢您的配合 目前来看 您问题表中遇到了并行的配置 为了进一步诊断 请 执行以下动作 并提供输出结果 ACTION PLAN 请分别在测试 2 在其中一台主机上创建基于 ASM 磁盘组的实例 以及 测试 4 在 PC 的 VMware 虚拟机里面单实例查询 的测试环境中执行以下动作 SQL show parameter parallel min servers SQL select table name degree from dba tables where table name dllnames SQL select table name degree from dba tables where table name operdllnames SQL select table name degree from dba tables where 精品文档 6欢迎下载 table name operaccount 并请提供以上测试 2 4 环境的数据库 alert 日志 位于 bdump 下 面 SQL show parameter background dump dest The alert log is named as alert log 测试 2 实例的输出 SQL show parameter parallel min servers NAME TYPE VALUE parallel min servers integer 0 SQL select table name degree from dba tables where table name DLLNAMES TABLE NAME DEGREE DLLNAMES 1 SQL select table name degree from dba tables where table name OPERDLLNAMES TABLE NAME DEGREE OPERDLLNAMES DEFAULT SQL select table name degree from dba tables where table name OPERACCOUNT TABLE NAME DEGREE OPERACCOUNT DEFAULT 测试 4 实例的输出 SQL show parameter parallel min servers SQL NAME TYPE VALUE parallel min servers integer 0 SQL select table name degree from dba tables where 精品文档 7欢迎下载 table name DLLNAMES and owner HCS2000 TABLE NAME DEGREE DLLNAMES 1 SQL select table name degree from dba tables where table name OPERDLLNAMES and owner HCS2000 TABLE NAME DEGREE OPERDLLNAMES DEFAULT SQL select table name degree from dba tables where table name OPERACCOUNT and owner HCS2000 TABLE NAME DEGREE OPERACCOUNT DEFAULT Dear customer 感谢您的更新 从您当前的设置来看 应该很大可能与您当前 RAC 服务器的多颗 CPU 数量有关 TABLE NAME DEGREE OPERDLLNAMES DEFAULT 您的 DEGREE 是默认值 该默认值的算法为 假设 CPU 数目为 16 一般 show parameter PARALLEL THREADS PER CPU NAME TYPE VALUE parallel threads per cpu integer 2 精品文档 8欢迎下载 show parameter cpu NAME TYPE VALUE cpu count integer 16 那么 以下对于并行的默认值设置即为 Threads CPU 3 parallel threads per cpu default DOP CPU Threads CPU 加入之前 CPU 16 parallel threads per cpu 2 default DOP 3x 2 x 16 96 ACTION PLAN 请您提供您当前两个环境的 show parameter PARALLEL THREADS PER CPU show parameter cpu 或者您可以直接执行 对于单机 ALTER SYSTEM SET parallel min servers 96 SCOPE BOTH 对于 RAC 执行 ALTER SYSTEM SET parallel min servers 96 SCOPE BOTH SID ORCL1 ALTER SYSTEM SET parallel min servers 96 SCOPE BOTH SID ORCL2 之后重新测试您的 SQL 测试环境 2 SQL show parameter cpu NAME TYPE VALUE cpu count integer 24 parallel threads per cpu integer 2 精品文档 9欢迎下载 resource manager cpu allocation integer 24 SQL SQL ALTER SYSTEM SET parallel min servers 144 SCOPE BOTH ALTER SYSTEM SET parallel min servers 144 SCOPE BOTH ERROR at line 1 ORA 02097 parameter cannot be modified because specified value is invalid ORA 12811 PARALLEL MIN SERVERS must be less than or equal to PARALLEL MAX SERVERS 135 SQL show parameter parallel min servers NAME TYPE VALUE parallel min servers integer 0 SQL show parameter PARALLEL MAX SERVERS NAME TYPE VALUE parallel max servers integer 135 SQL ALTER SYSTEM SET parallel min servers 135 SCOPE BOTH System altered SQL show parameter parallel min servers NAME TYPE VALUE parallel min servers integer 135 设置完之后 连续测试 5 次 分别用时 3 7s 3 7s 0 4s 0 4s 0 7s 过 5 分钟再测 用时 3 4s 还是比较慢 测试环境 4 SQL show parameter cpu 精品文档 10欢迎下载 NAME TYPE VALUE cpu count integer 1 parallel threads per cpu integer 2 测试环境 2 SQL show parameter cpu NAME TYPE VALUE cpu count integer 24 parallel threads per cpu integer 2 resource manager cpu allocation integer 24 SQL SQL ALTER SYSTEM SET parallel min servers 144 SCOPE BOTH ALTER SYSTEM SET parallel min servers 144 SCOPE BOTH ERROR at line 1 ORA 02097 parameter cannot be modified because specified value is invalid ORA 12811 PARALLEL MIN SERVERS must be less than or equal to PARALLEL MAX SERVERS 135 SQL show parameter parallel min servers NAME TYPE VALUE parallel min servers integer 0 SQL show parameter PARALLEL MAX SERVERS NAME TYPE VALUE 精品文档 11欢迎下载 parallel max servers integer 135 SQL ALTER SYSTEM SET parallel min servers 135 SCOPE BOTH System altered SQL show parameter parallel min servers NAME TYPE VALUE parallel min servers integer 135 设置完之后 连续测试 5 次 分别用时 3 7s 3 7s 0 4s 0 4s 0 7s 过 5 分钟再测 用时 3 4s 还是比较慢 测试环境 4 SQL show parameter cpu NAME TYPE VALUE cpu count integer 1 parallel threads per cpu integer 2 Dear customer 我们从您提供的信息中发现 您的比较是基于 9I 的单机环境 是 没有使用并行的 如果您的业务都是基于 9I 单机开发 建议您将 parallel max servers 设置为 0 之后再次测试 SQL ALTER SYSTEM SET parallel min servers 0 SCOPE BOTH ALTER SYSTEM SET parallel max servers 0 SCOPE BOTH 之后 请将您新测试的 10046