No question at this time
DBA Top 10
1 M. Cadot 7000
2 B. Vroman 3000
3 P. Wisse 1300
4 P. Tsongayinwe 1100
5 J. PĂ©ran 1000
6 A. Kavsek 600
7 D. Walgude 400
7 J. Schnackenberg 400
9 J. Alcroft 200
9 B. M 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48717
Total active users1324
Act. users last 24h1
Act. users last hour0
Registered user hits last week17
Registered user hits last month178
Go up

performance issues
Next thread: maximum number of open files per process
Prev thread: Help with a SQL query

Message Score Author Date
Hi Team We are experiencing performance issues ...... Phuti Tsongayinwe Jan 18, 2023, 11:56
3 things hurt me in your post: * There are no t...... Michel Cadot Jan 18, 2023, 12:43
Thanks, Michel My apologies...we have 11.1 tran...... Phuti Tsongayinwe Jan 18, 2023, 12:49
I think the queries might be slow. Thanks P... Phuti Tsongayinwe Jan 18, 2023, 12:50
My remarks about rollbacks (why half of the tran...... Michel Cadot Jan 18, 2023, 12:53
Thanks, Michel I will have to check with the de...... Phuti Tsongayinwe Jan 18, 2023, 12:58

Follow up by mail Click here


Subject: performance issues
Author: Phuti Tsongayinwe, South Africa
Date: Jan 18, 2023, 11:56, 21 days ago
Os info: Oracle Solaris 11.4 SPARC.
Oracle info: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Message: Hi Team

We are experiencing performance issues in our environment.

We have 5 database instances and I checked AWR reports for each database instance but I found this one bit weird.


Host Name Platform CPUs Cores Sockets Memory (GB)
hostname Solaris[tm] OE (64-bit) 32 4 2 192.00



Snap Id Snap Time Sessions Cursors/Session CDB
Begin Snap: 44991 18-Jan-23 08:01:11 132 3.8 YES
End Snap: 44992 18-Jan-23 09:00:25 151 4.6 YES
Elapsed: 59.24 (mins)
DB Time: 1,374.29 (mins)



Load Profile

Per Second Per Transaction Per Exec Per Call
DB Time(s): 23.2 2.1 0.05 2.05
DB CPU(s): 6.9 0.6 0.02 0.61
Background CPU(s): 0.4 0.0 0.00 0.00
Redo size (bytes): 44,152.2 3,968.9
Logical read (blocks): 102,103.2 9,178.2
Block changes: 1,219.6 109.6
Physical read (blocks): 3,982.7 358.0
Physical write (blocks): 463.1 41.6
Read IO requests: 104.5 9.4
Write IO requests: 35.0 3.2
Read IO (MB): 31.1 2.8
Write IO (MB): 3.6 0.3
IM scan rows: 0.0 0.0
Session Logical Read IM:
User calls: 11.3 1.0
Parses (SQL): 81.5 7.3
Hard parses (SQL): 0.7 0.1
SQL Work Area (MB): 12.9 1.2
Logons: 0.8 0.1
Executes (SQL): 425.2 38.2
Rollbacks: 5.0 0.5
Transactions:




Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU 24.4K 29.6
SQL*Net message from dblink 3,407,344 7776.7 2.28 9.4 Network
direct path write temp 20,473 6646 324.62 8.1 User I/O
TCP Socket (KGAS) 379,767 6632.8 17.47 8.0 Network
direct path read temp 90,518 5554.4 61.36 6.7 User I/O
db file sequential read 109,510 3640.5 33.24 4.4 User I/O
log file sync 1,152 1976.7 1715.86 2.4 Commit
db file scattered read 28,060 1429.5 50.95 1.7 User I/O
read by other session 3,774 535.8 141.98 .6 User I/O
SQL*Net more data from dblink 1,564,287 409.7 0.26 .5 Network



What might be the issue here?

Please assist...

Thanks in advance...
P





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

Subject: Re: performance issues
Author: Michel Cadot, France
Date: Jan 18, 2023, 12:43, 21 days ago
Message:
3 things hurt me in your post:
* There are no transaction figures (last line of Load Profile)
* There are 5 rollbacks per second (that is work is done and undone 5 times per second)
* There are 81.5 parses per second (that is you ask Oracle to analyze 81.5 SQL per second, normally applications use the same queries (as the Hard parses count show) they don't need to ask Oracle to analyze them several times).

What performances problem?
Is this slow queries? global database or server performances?...

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: Jan 18, 2023, 12:49, 21 days ago
Message: Thanks, Michel

My apologies...we have 11.1 transactions per second.

We have global database/server performance issues.

Now, this database instance is shared with 5 other databases on the server.

Its processes are consistently hogging the CPU at the expense of other processes.

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: Jan 18, 2023, 12:50, 21 days ago
Message: I think the queries might be slow.

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: Jan 18, 2023, 12:53, 21 days ago
Message:
My remarks about rollbacks (why half of the transactions ends with "undone"?) and parses still apply, both are cpu intensive activities.

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: Jan 18, 2023, 12:58, 21 days ago
Message: Thanks, Michel

I will have to check with the developer about the rollbacks.

Thanks
P

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