Home » RDBMS Server » Performance Tuning » Application slow down (Database 11.2.0.3.0)
Application slow down [message #647241] Sun, 24 January 2016 02:34 Go to next message
samiyabulushi
Messages: 2
Registered: January 2016
Location: Oman
Junior Member
Dear,

Need you help

In my production environment users inform the slow down in application. I need to find out DB performance functioning normal or not;
I have run ADDM/AWR to generate the report;

the report shows:
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
------------------------------ ------------------- ---------------
1 "User I/O" wait Class 0 | 17.4 0
2 Hard Parse 0 | 16.83 0
3 Commits and Rollbacks 0 | 11.48 2
4 Session Connect and Disconnect 0 | 3.33 1


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Findings and Recommendations
----------------------------



Finding 3: Commits and Rollbacks
Impact is 0 active sessions, 11.48% of total activity.
------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 11.48% of total activity.
-----------------------------------------------------------------
Action
Investigate application logic for possible reduction in the number of
COMMIT operations by increasing the size of transactions.
Rationale
The application was performing 15 transactions per minute with an
average redo size of 9334 bytes per transaction.

Recommendation 2: Host Configuration
Estimated benefit is 0 active sessions, 11.48% of total activity.
-----------------------------------------------------------------
Action
Investigate the possibility of improving the performance of I/O to the
online redo log files.
Rationale
The average size of writes to the online redo log files was 5 K and the
average time per write was 7 milliseconds.
Rationale
The total I/O throughput on redo log files was 0 K per second for reads
and 2.5 K per second for writes.
Rationale
The redo log I/O throughput was divided as follows: 0% by RMAN and
recovery, 100% by Log Writer, 0% by Archiver, 0% by Streams AQ and 0% by
all other activity.

Symptoms That Led to the Finding:
---------------------------------
Wait class "Commit" was consuming significant database time.
Impact is 0 active sessions, 11.48% of total activity.


Finding 4: Session Connect and Disconnect
Impact is 0 active sessions, 3.33% of total activity.
-----------------------------------------------------
Session connect and disconnect calls were consuming significant database time.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 3.33% of total activity.
----------------------------------------------------------------
Action
Investigate application logic for possible reduction of connect and
disconnect calls. For example, you might use a connection pool scheme in
the middle tier.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional Information
----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.

The database's maintenance windows were active during 54% of the analysis
period.







The report show recommendation in commits/rollback

I some one direct me how to solve this:
Re: Application slow down [message #647242 is a reply to message #647241] Sun, 24 January 2016 03:05 Go to previous messageGo to next message
John Watson
Messages: 7624
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Your report shows that over a nine hour period, you had only 694 seconds of actual work. Your database is doing hardly any work at all.

If your users are complaining about something, you need to find the SQL statement (or PL/SQL, or whatever) that is under-performing, and tune it.

Be sure to read the forum guide before you post the details of the statement(s) here.
Re: Application slow down [message #647243 is a reply to message #647242] Sun, 24 January 2016 03:10 Go to previous message
samiyabulushi
Messages: 2
Registered: January 2016
Location: Oman
Junior Member
Thanks John for guide. I'll go through it
Previous Topic: Understanding oracle sql explain plan
Next Topic: Wich index is used. Confusion in SQL-trace
Goto Forum:
  


Current Time: Tue Oct 23 10:52:55 CDT 2018