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

秒或

0

秒。< /p>

测试

1

中的查询太慢了，请问怎么查看问题原因，如何调优？

Dear customer,

请您执行以下动作：

如果可以，请在 您提到的

4

个场景下都生成以下文件，并请添加您

的说明后，作为附件更新到

SR

上：

ACTION PLAN

-----------------------

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

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_

3.

请提交您

10046 trace

以及

tkprof

输出文件

Dear customer,

目 前来看，您问题表中遇到了并行的配置。为了进一步诊断，请执

行以下动作，并提供输出 结果：

ACTION PLAN

-----------------------

请分别在 测试

2

：在其中一台主机上创建基于

A SM

磁盘组的实例

以及

测试

4

：在

PC

的

V Mware

虚拟机里面单实例查询

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

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

< /p>

并请提供以上测试

2

，

< br> 4

环境的数据库

alert

日志

位于

b dump

下

面

SQL> show parameter background_dump_dest

The is named as alert_.log.

Name

--------

=== ODM Data Collection ===

SELECT count || ':' || RD || '@' || ,

, ption, ''

FROM dllnames a, operdllnames b, operaccount c

WHERE eid = eid

AND =

AND upper(count) = USER

ORDER BY eid

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

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

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

***********

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_

FileComment

----------------------

Dear customer,

感谢您的配合。

< br>目前来看，您问题表中遇到了并行的配置。为了进一步诊断，请执

行以下动作，并 提供输出结果：

ACTION PLAN

-----------------------

请分别在测试

2

：在其中一台主机上创建基于

ASM

磁盘组的实例

以及

测试

4

：在

PC

的

V Mware

虚拟机里面单实例查询

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

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

日志

位于

b dump

下

面

SQL> show parameter background_dump_dest

The 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