No question at this time
DBA Top 10
1 M. Cadot 7200
2 B. Vroman 3100
3 J. PĂ©ran 2100
4 A. Kavsek 1400
5 P. Tsongayinwe 1300
6 P. Wisse 600
7 D. Walgude 400
7 J. Schnackenberg 400
9 J. Alcroft 200
10 A. Hudspith 100
10 D. Johnson 100
10 B. B 100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48684
Total active users1329
Act. users last 24h2
Act. users last hour0
Registered user hits last week44
Registered user hits last month235
Go up

performance issues
Next thread: backup database plus archivelog
Prev thread: Oracle High Availability Service Target is Showing Down in OEM

Message Score Author Date
Hi Team, We are experiencing performance issues...... Phuti Tsongayinwe Jul 07, 2022, 12:34
Below is the Time Model Statistics <pre> Time ...... Phuti Tsongayinwe Jul 07, 2022, 13:21
>>> <i> We are experiencing performance issues o...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Jul 07, 2022, 15:29
Thanks Michel, My users are reporting poor perf...... Phuti Tsongayinwe Jul 07, 2022, 18:28
Maybe the problem is in the application. They e...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Jul 07, 2022, 20:30
And about 2200 SQL statements per transaction. ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Jul 07, 2022, 20:32
Thanks Michel, I need to check that info with t...... Phuti Tsongayinwe Jul 07, 2022, 20:46
Hi Team, I forgot to mention that we have 3 app...... Phuti Tsongayinwe Jul 08, 2022, 10:01
The AWR report gives you the most expensive quer...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Jul 08, 2022, 11:22
Thanks Michel... Phuti Tsongayinwe Jul 12, 2022, 16:20
Hi Team, As an update, I logged an SR with the ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Phuti Tsongayinwe Jul 21, 2022, 13:22
Thanks, we'll keep following this thread. Reg...... Michel Cadot Jul 21, 2022, 16:55
As feedback: Oracle has identified this as a bu...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Phuti Tsongayinwe Aug 16, 2022, 11:28
Thanks you for your feedback. Regards Michel...... Michel Cadot Aug 16, 2022, 13:25

Follow up by mail Click here


Subject: performance issues
Author: Phuti Tsongayinwe, South Africa
Date: Jul 07, 2022, 12:34, 41 days ago
Os info: Oracle Solaris 11.4 SPARC.
Oracle info: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.13.0.0.0
Message: Hi Team,

We are experiencing performance issues on our prod DB.

Now, I pulled the AWR report to have a look.


Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 22630 07-Jul-22 10:27:50 100 80.8
End Snap: 22631 07-Jul-22 11:28:07 107 72.4
Elapsed: 60.28 (mins)
DB Time: 380.96 (mins)



Load Profile

Per Second Per Transaction Per Exec Per Call
DB Time(s): 6.3 4.5 0.00 0.00
DB CPU(s): 5.9 4.2 0.00 0.00
Background CPU(s): 0.0 0.0 0.00 0.00
Redo size (bytes): 35,974.7 25,648.1
Logical read (blocks): 276,808.6 197,350.1
Block changes: 246.4 175.7
Physical read (blocks): 4,203.0 2,996.5
Physical write (blocks): 73.7 52.6
Read IO requests: 1,390.7 991.5
Write IO requests: 2.5 1.8
Read IO (MB): 32.8 23.4
Write IO (MB): 0.6 0.4
IM scan rows: 0.0 0.0
Session Logical Read IM: 0.0 0.0
User calls: 4,589.4 3,272.0
Parses (SQL): 70.2 50.1
Hard parses (SQL): 3.4 2.4
SQL Work Area (MB): 9.8 7.0
Logons: 1.7 1.2
User logons: 0.1 0.1
Executes (SQL): 3,067.6 2,187.0
Rollbacks: 0.0 0.0
Transactions: 1.4



Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Avg Wait % DB time Wait Class
DB CPU 21.4K 93.5
db file sequential read 4,582,827 1302.5 284.22us 5.7 User I/O
SQL*Net more data to client 1,990,298 156.9 78.82us .7 Network
PGA memory operation 3,386,157 124.6 36.79us .5 Other
direct path read 35,914 114.7 3.19ms .5 User I/O
read by other session 375,667 80.9 215.25us .4 User I/O
db file scattered read 35,692 52 1.46ms .2 User I/O
db file parallel read 57,499 46 799.93us .2 User I/O
SQL*Net message to client 16,480,229 29.5 1.79us .1 Network
latch: shared pool 2,327 18.3 7.87ms .1 Concurrency


Please help...

I would appreciate if someone can also assist.

Thanks in advance...

Thanks
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Phuti Tsongayinwe, South Africa
Date: Jul 07, 2022, 13:21, 41 days ago
Message: Below is the Time Model Statistics


Time Model Statistics

Statistic Name Time (s) % of DB Time % of Total CPU Time
DB CPU 21,374.63 93.51 100.08
sql execute elapsed time 20,614.32 90.19
parse time elapsed 1,261.07 5.52
hard parse elapsed time 493.44 2.16
hard parse (sharing criteria) elapsed time 193.49 0.85
connection management call elapsed time 129.21 0.57
PL/SQL execution elapsed time 12.88 0.06
repeated bind elapsed time 3.86 0.02
PL/SQL compilation elapsed time 0.29 0.00
DB time 22,857.36
total CPU time 21,357.96


Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Michel Cadot, France
Date: Jul 07, 2022, 15:29, 41 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
>>> We are experiencing performance issues on our prod DB

What performances problem?
From your AWR abstract your Db runs smoothly.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Phuti Tsongayinwe, South Africa
Date: Jul 07, 2022, 18:28, 41 days ago
Message: Thanks Michel,

My users are reporting poor performance on the application.

So, I thought let me start with the DB.

Thank you so much for the reply.

Much appreciated !!!

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Michel Cadot, France
Date: Jul 07, 2022, 20:30, 41 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
Maybe the problem is in the application.
They execute 3,000 SQL statements per second, is this expected?

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Michel Cadot, France
Date: Jul 07, 2022, 20:32, 41 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
And about 2200 SQL statements per transaction.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Phuti Tsongayinwe, South Africa
Date: Jul 07, 2022, 20:46, 41 days ago
Message: Thanks Michel,

I need to check that info with the application team.

Thanks for the analysis.

I would update the thread.

Thanks
Phuti
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Phuti Tsongayinwe, South Africa
Date: Jul 08, 2022, 10:01, 40 days ago
Message: Hi Team,

I forgot to mention that we have 3 applications that are being managed through schemas on that database but these applications are interrelated.

So I am not sure if the SQL statements are from one schema or its a combinations of all the schemas.

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Michel Cadot, France
Date: Jul 08, 2022, 11:22, 40 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
The AWR report gives you the most expensive queries depending on different resources (CPU, elapsed, IO, nb of executions...).

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Phuti Tsongayinwe, South Africa
Date: Jul 12, 2022, 16:20, 36 days ago
Message: Thanks Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Phuti Tsongayinwe, South Africa
Date: Jul 21, 2022, 13:22, 27 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Team,

As an update, I logged an SR with the Oracle Product Team, as such they are busy reviewing the issue.

I will share the outcome as soon we have found the issue.

Thanks
P
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Michel Cadot, France
Date: Jul 21, 2022, 16:55, 27 days ago
Message:
Thanks, we'll keep following this thread.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Phuti Tsongayinwe, South Africa
Date: Aug 16, 2022, 11:28, 1 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: As feedback:

Oracle has identified this as a bug in the ADF code as such they have recommended that we apply the ADF Bundle Patch 33697227 to remediate this.

Thanks
P
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Michel Cadot, France
Date: Aug 16, 2022, 13:25, 1 days ago
Message:
Thanks you for your feedback.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here