Document 2337875.1.pdf - 墨天轮文档

Document 2337875.1.pdf

未来

5页

0次

2023-12-27

10墨值下载

12/14/23, 10:00 AM

Document 2337875.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=19km3be1j5_52&id=2337875.1

1/5

SQL Statements Run Slower Waiting on "PGA memory operation" and "Acknowledge over PGA

limit" After Migrate from 12.1 to 12.2 (Doc ID 2337875.1)

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later

Oracle Database Exadata Cloud Machine - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Oracle Database Backup Service - Version N/A and later

Information in this document applies to any platform.

SYMPTOMS

Performance of customer's application SQL statements degraded after migrated from 12.1.0.2 environment to 12.2.0.1

environment.　From following 10046 events trace files, in 12.2.0.1, there are far more waiting events of "PGA memory

operation" and "acknowledge over PGA limit". There is especially more time spent on "acknowledge over PGA limit".

Furthermore both 12.1.0.2 and 12.2.0.1 environments had a lot of waiting events　"direct path read" and "direct path

read temp".

In 12.1.0.2:

call count cpu elapsed disk query current rows

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

Parse 1 0.76 0.68 0 40 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 6.56 7.16 84598 85108 5 3

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

total 4 7.32 7.85 84598 85148 5 3

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

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

SQL*Net message to client 2 0.00 0.00

Disk file operations I/O 8 0.00 0.00

direct path read 381 0.03 0.49

direct path write temp 2 0.00 0.00

direct path read temp 377 0.00 0.06

SQL*Net message from client 2 11.19 11.19

In 12.2.0.1:　

call count cpu elapsed disk query current rows

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

Parse 1 1.95 3.97 0 15 0 0

12/14/23, 10:00 AM

Document 2337875.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=19km3be1j5_52&id=2337875.1

2/5

Execute 1 0.00 0.08 0 0 0 0

Fetch 2 6.18 14.13 60055 79304 28 3

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

total 4 8.14 18.20 60055 79319 28 3

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

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

PGA memory operation 1926 0.00 0.07

acknowledge over PGA limit 536 0.02 6.11

SQL*Net message to client 2 0.00 0.00

Disk file operations I/O 8 0.00 0.01

direct path read 93 0.00 0.25

direct path write temp 128 0.04 1.02

direct path read temp 135 0.01 0.48

SQL*Net message from client 2 10.10 10.11

No significant long elapsed time of all Row Source Operation steps:

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

3 3 3 TABLE ACCESS BY INDEX ROWID <TABLE NAME> (cr=11 pr=0 pw=0 time=3 us cost=3 size=18

card=1)

3 3 3 INDEX UNIQUE SCAN <INDEX NAME> (cr=8 pr=0 pw=0 time=3 us cost=2 size=0 card=1)

(object id 302050)

2 2 2 TABLE ACCESS BY INDEX ROWID <TABLE NAME>(cr=34 pr=0 pw=0 time=3 us cost=1 size=7

card=1)

2 2 2 INDEX UNIQUE SCAN <INDEX NAME> (cr=32 pr=0 pw=0 time=3 us cost=0 size=0 card=1)

(object id 301973)

3 3 3 SORT AGGREGATE (cr=30 pr=0 pw=0 time=3 us)

2 2 2 NESTED LOOPS (cr=30 pr=0 pw=0 time=3 us cost=14 size=34 card=1)

2 2 2 NESTED LOOPS (cr=28 pr=0 pw=0 time=3 us cost=14 size=34 card=1)

... ...

47 47 47 VIEW <VIEW_NAME> (cr=60280 pr=60055 pw=7090 time=1 us cost=29080

size=15826944 card=989184)

47 47 47 HASH GROUP BY (cr=60280 pr=60055 pw=7090 time=1 us cost=29080 size=39567360

card=989184)

47 47 47 JOIN FILTER USE :BF0005 (cr=60280 pr=60055 pw=7090 time=1 us cost=29080

size=39763600 card=994090)

994091 994091 994091 MERGE JOIN (cr=60280 pr=60055 pw=7090 time=1 us cost=29080

size=39763600 card=994090)

994091 994091 994091 SORT JOIN (cr=7311 pr=0 pw=0 time=1 us cost=7901 size=18887729

card=994091)

994091 994091 994091 TABLE ACCESS FULL <TABLE NAME> (cr=7311 pr=0 pw=0 time=1 us

cost=2017 size=18887729 card=994091)

994091 994091 994091 SORT JOIN (cr=52969 pr=60055 pw=7090 time=994091 us cost=21179

size=21506835 card=1024135)

1024227 1024227 1024227 TABLE ACCESS FULL <TABLE NAME> (cr=52969 pr=52965 pw=0 time=1

us cost=14538 size=21506835 card=1024135)

SQL performance has not been improved, after alter parameter pga_aggregate_limit to 0, which made waiting events

"acknowledge over PGA limit" disappear.　From the following 10046 events trace file, almost all time is spent on CPU

during Fetch phase. Wait events "PGA memory operation" still happens frequently　although it seems not to take time.

And, there is still no significant long elapsed time of all Row Source Operation steps.

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

3 3 3 TABLE ACCESS BY INDEX ROWID <TABLE NAME> (cr=11 pr=0 pw=0 time=3 us cost=3 size=18

card=1)

3 3 3 INDEX UNIQUE SCAN <INDEX NAME> (cr=8 pr=0 pw=0 time=3 us cost=2 size=0 card=1)

12/14/23, 10:00 AM

Document 2337875.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=19km3be1j5_52&id=2337875.1

1/5

SQL Statements Run Slower Waiting on "PGA memory operation" and "Acknowledge over PGA

limit" After Migrate from 12.1 to 12.2 (Doc ID 2337875.1)

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later

Oracle Database Exadata Cloud Machine - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Oracle Database Backup Service - Version N/A and later

Information in this document applies to any platform.

SYMPTOMS

Performance of customer's application SQL statements degraded after migrated from 12.1.0.2 environment to 12.2.0.1

environment.　From following 10046 events trace files, in 12.2.0.1, there are far more waiting events of "PGA memory

operation" and "acknowledge over PGA limit". There is especially more time spent on "acknowledge over PGA limit".

Furthermore both 12.1.0.2 and 12.2.0.1 environments had a lot of waiting events　"direct path read" and "direct path

read temp".

In 12.1.0.2:

call count cpu elapsed disk query current rows

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

Parse 1 0.76 0.68 0 40 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 6.56 7.16 84598 85108 5 3

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

total 4 7.32 7.85 84598 85148 5 3

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

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

SQL*Net message to client 2 0.00 0.00

Disk file operations I/O 8 0.00 0.00

direct path read 381 0.03 0.49

direct path write temp 2 0.00 0.00

direct path read temp 377 0.00 0.06

SQL*Net message from client 2 11.19 11.19

In 12.2.0.1:　

call count cpu elapsed disk query current rows

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

Parse 1 1.95 3.97 0 15 0 0

12/14/23, 10:00 AM

Document 2337875.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=19km3be1j5_52&id=2337875.1

2/5

Execute 1 0.00 0.08 0 0 0 0

Fetch 2 6.18 14.13 60055 79304 28 3

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

total 4 8.14 18.20 60055 79319 28 3

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

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

PGA memory operation 1926 0.00 0.07

acknowledge over PGA limit 536 0.02 6.11

SQL*Net message to client 2 0.00 0.00

Disk file operations I/O 8 0.00 0.01

direct path read 93 0.00 0.25

direct path write temp 128 0.04 1.02

direct path read temp 135 0.01 0.48

SQL*Net message from client 2 10.10 10.11

No significant long elapsed time of all Row Source Operation steps:

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

3 3 3 TABLE ACCESS BY INDEX ROWID <TABLE NAME> (cr=11 pr=0 pw=0 time=3 us cost=3 size=18

card=1)

3 3 3 INDEX UNIQUE SCAN <INDEX NAME> (cr=8 pr=0 pw=0 time=3 us cost=2 size=0 card=1)

(object id 302050)

2 2 2 TABLE ACCESS BY INDEX ROWID <TABLE NAME>(cr=34 pr=0 pw=0 time=3 us cost=1 size=7

card=1)

2 2 2 INDEX UNIQUE SCAN <INDEX NAME> (cr=32 pr=0 pw=0 time=3 us cost=0 size=0 card=1)

(object id 301973)

3 3 3 SORT AGGREGATE (cr=30 pr=0 pw=0 time=3 us)

2 2 2 NESTED LOOPS (cr=30 pr=0 pw=0 time=3 us cost=14 size=34 card=1)

2 2 2 NESTED LOOPS (cr=28 pr=0 pw=0 time=3 us cost=14 size=34 card=1)

... ...

47 47 47 VIEW <VIEW_NAME> (cr=60280 pr=60055 pw=7090 time=1 us cost=29080

size=15826944 card=989184)

47 47 47 HASH GROUP BY (cr=60280 pr=60055 pw=7090 time=1 us cost=29080 size=39567360

card=989184)

47 47 47 JOIN FILTER USE :BF0005 (cr=60280 pr=60055 pw=7090 time=1 us cost=29080

size=39763600 card=994090)

994091 994091 994091 MERGE JOIN (cr=60280 pr=60055 pw=7090 time=1 us cost=29080

size=39763600 card=994090)

994091 994091 994091 SORT JOIN (cr=7311 pr=0 pw=0 time=1 us cost=7901 size=18887729

card=994091)

994091 994091 994091 TABLE ACCESS FULL <TABLE NAME> (cr=7311 pr=0 pw=0 time=1 us

cost=2017 size=18887729 card=994091)

994091 994091 994091 SORT JOIN (cr=52969 pr=60055 pw=7090 time=994091 us cost=21179

size=21506835 card=1024135)

1024227 1024227 1024227 TABLE ACCESS FULL <TABLE NAME> (cr=52969 pr=52965 pw=0 time=1

us cost=14538 size=21506835 card=1024135)

SQL performance has not been improved, after alter parameter pga_aggregate_limit to 0, which made waiting events

"acknowledge over PGA limit" disappear.　From the following 10046 events trace file, almost all time is spent on CPU

during Fetch phase. Wait events "PGA memory operation" still happens frequently　although it seems not to take time.

And, there is still no significant long elapsed time of all Row Source Operation steps.

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

3 3 3 TABLE ACCESS BY INDEX ROWID <TABLE NAME> (cr=11 pr=0 pw=0 time=3 us cost=3 size=18

card=1)

3 3 3 INDEX UNIQUE SCAN <INDEX NAME> (cr=8 pr=0 pw=0 time=3 us cost=2 size=0 card=1)

of 5

10墨值下载

mos

关注

评论