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

admin管理员组

文章数量:1530518

2024年1月11日发(作者：)

知也无涯

Oracle RAC 11g r2

查询太慢

Oracle RAC 11g r2

查询太慢

Problem Descripti on

Redhat 5

双机

测试1:双实例，ASM磁盘组包含3个磁盘(SAN。在其中一个实例中执行：

SELECT c ount || ':' || RD || @ || ,

, pti on, '

FROM names a, l names b, cou nt c

WHERE nameid = nameid

AND =

AND upper(co unt) = USER

ORDER BY nameid;

第一次查询，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

1. Please gen erate 10046 trace for your sql:

SQL>c onnect username/password

SQL>alter session set timed_statistics = true;

SQL>alter sessi on set statistics_level=all;

SQL>alter sessi on set max_dump_file_size = un limited;

SQL>alter sessi on set eve nts '10046 trace n ame con text forever, level 12';

SQL>vR un your SQL here;>

SQL>alter sessi on set eve nts '10046 trace n amec on text off;

2. Format your 10046 trace file:

$tkprof

例如

生成的文件应该是在您的udump路径下面。

寻找UDUM路径，请参考

SQL> show parameter user_dump_dest

之后，format您的文件

$cd /u01/OracleAPP/oracle/admi n/R1020/udump

$ls -ltr

$tkprof r1020_ora_

3.

请提交您10046 trace

以及tkprof

输出文件

Dear customer,

目前来看，您问题表中遇到了并行的配置。为了进一步诊断，请执 行以下动作，并提供输出结果：

ACTION PLAN

请分别在测试2:在其中一台主机上创建基于

ASM磁盘组的实例 以及

测试4：在PC的VMware虚拟机里面单实例查询 的测试环境中执行以下动作

SQL> show parameter parallel』in _servers

SQL> select table_ name,degree from dba_tables where table_ name='dll

names';

SQL> select table_ name,degree from dba_tables where table_ name='operdll

names';

SQL> select table_ name,degree from dba_tables where table_

name='operacco un t';

并请提供以上测试2，

4环境的数据库alert日志位于bdump下 面

SQL> show parameter backgro un d_dump_dest

The alert .log is n amed as alert_.log.

Name ===ODM Data Collectio n ===

SELECT c ount || ':' || RD || @ || , , pti on, '

FROM dll names a, operdll names b, operacco unt c

WHERE nameid = nameid

AND =

AND upper(co unt) = USER

ORDER BY nameid

call count cpu elapsed disk query curre nt 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 duri ng parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 5

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 TABLEACCESSFULL OPERACCOU(NcrT=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 INDEXUNIQUESCANPK_DLLNAMEI(Dcr=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

*********************************************************************

***********

Elapsed times in elude wait ing on follow ing eve nts:

Event waited on Times Max. Wait Total Waited

--------------------------------------- Waited ----------

SQL*Net message to clie nt 3 0.00 0.00

SQL*Net message from clie nt 3 59.90 59.90 rdbms ipc reply 2 0.00 0.00

os thread startup 96 0.15 10.46

PX Deq: Joi n 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: Sig nal 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 - conten tion 2 0.00 0.00

FileName mytestas1_ora_

FileComme nt

Dear customer,

感谢您的配合。

目前来看，您问题表中遇到了并行的配置。为了进一步诊断，请执 行以下动作，并提供输出结果：

ACTION PLAN

请分别在测试2:在其中一台主机上创建基于

ASM磁盘组的实例 以及

测试4：在PC的VMware虚拟机里面单实例查询

的测试环境中执行以下动作

SQL> show parameter parallel_m in _servers

SQL> select table_ name,degree from dba_tables where table_ name='dll

names';

SQL> select table_ name,degree from dba_tables where table_ name='operdll

names';

SQL> select table_ name,degree from dba_tables where table_

name='operacco un t';

并请提供以上测试2,

4环境的数据库alert日志位于bdump下 面

SQL> show parameter backgro un d_dump_dest

The alert .log is n amed as alert_.log.

测试2实例的输出：

SQL> show parameter parallel_m in _servers

NAME TYPE VALUE parallel_m in _servers in teger 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_m in _servers

SQL>

NAME TYPE VALUE

parallel』in _servers in teger 0

SQL> select table_ name,degree from dba_tables where

table_name='DLLNAMES' and own er='HCS2000'; TABLE_NAME DEGREE

DLLNAMES 1

SQL> select table_ name,degree from dba_tables where

table_name='OPERDLLNAMES' and own er='HCS2000'; TABLE_NAME

DEGREE

OPERDLLNAMES DEFAULT

SQL> select table_ name,degree from dba_tables where

table_name='OPERACCOUNT' and own er='HCS2000'; TABLE_NAME

DEGREE

OPERACCOUNT DEFAULT

Dear customer,

感谢您的更新。

从您当前的设置来看，应该很大可能与您当前

RAC服务器的多颗

CPL数量有关

TABLE_NAME DEGREE

OPERDLLNAMES DEFAULT

您的DEGREE!默认值，该默认值的算法为 假设CPU数目为16, —般

show parameter PARALLEL_THREADS_PER_CPU NAME TYPE VALUE

parallel_threads_per_cpu in teger 2

show parameter cpu

NAME TYPE VALUE

cpu_co unt in teger 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 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

NAME TYPE VALUE cpu_co unt in teger 1

parallel_threads_per_cpu in teger 2

测试环境2:

SQL> show parameter cpu

NAME TYPE VALUE cpu_co unt in teger 24

parallel_threads_per_cpu in teger 2

resource_ma nager_cpu_allocati on in teger 24

SQL>

SQL> ALTER SYSTEM SET parallel_min_servers = 144 SCOPE=BOTH;

ALTER SYSTEM SET parallel_min_servers = 144 SCOPE=BOTH

*

ERROR at li ne 1:

ORA-O2O97: parameter cannot be modified because specified

value is inv alid

ORA-12811: PARALLEL_MIN_SERVERS must be less than or equal to

PARALLEL_MAX_SERVERS, 135

SQL> show parameter parallel_m in _servers

NAME TYPE VALUE parallel_m in _servers in teger 0

SQL> show parameter PARALLEL_MAX_SERVERS

NAME TYPE VALUE parallel_max_servers in teger 135

SQL> ALTER SYSTEM SET parallel_min_servers = 135 SCOPE=BOTH;

System altered.

SQL> show parameter parallel』in _servers

NAME TYPE VALUE

parallel』in _servers in teger 135

设置完之后，连续测试5次，分别用时3.7s ,3.7s ,0.4s, 0.4s, 0.7s

过5分钟再测，用时3.4s。

还是比较慢。

测试环境4：

SQL> show parameter cpu

NAME TYPE VALUE cpu_co unt in teger 1

parallel_threads_per_cpu in teger 2

Dear customer,

我们从您提供的信息中发现，您的比较是基于

91的单机环境，是 没有使用并行的。如果您的业务都是基于

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结果更新到SR上。

已经执行了

ALTER SYSTEM SET parallel_min_servers = 0 SCOPE=BOTH;

ALTER SYSTEM SET parallel_max_servers = 0 SCOPE=BOTH;

再次测试，查询用时为0.01秒，可以接受。trace文件就不上传了。

请问parallel_max_servers

设置为0后，系统的24个CPL是不是

同时只能有1个们该查询操作（而且只有一个线程）？

抛开应用其它部分，单就这个select语句而言，如何修改该select

语句或做其它设置，从而充分利用多个cpu多线程查询（如果表中数

据很多的话，肯定是多个cpu并行查询速度更快）？

Dear customer,

感谢您的配合。

目前从您应用的等待来看，您是遇到了并发高的负影响 您的SQL在不启用并发的情况下应该会执行的很好。

：如果您希望在打开并发设置前提下，单独调整问题表，您可以在问 题表上执行

打开并发

ALTER SYSTEM SET parallel_min_servers = 96 SCOPE=BOTH;

ALTER SYSTEM SET parallel_max_servers = 135 SCOPE=BOTH;

ALTER TABLE dll names NOPARALLEL;

ALTER TABLE operdll names NOPARALLEL;

ALTER TABLE operacc ount NOPARALLEL;

ALTER TABLE dll names NOPARALLEL;

ALTER TABLE operdll names NOPARALLEL;

ALTER TABLE operacc ount NOPARALLEL;

或者

屏蔽服务器的并发

SQL> ALTER SYSTEM SET parallel_min_servers = 0 SCOPE=BOTH;

ALTER SYSTEM SET parallel_max_servers = 0 SCOPE=BOTH;

ORACLE RAC性能 标签

本文标签： 查询问题并发并行实例