Dear DBA-Mates,
Hope you all are doing Good and fantastic. We are
back with one of the most important questions as Oracle
Database performance Tuning which will be useful here. And
would like to share with you all.
In every interview question is asked as What to do
if Oracle Database Performance issue occur?
Below are some points which are very important and
useful to check the performance and will help to troubleshoot the Database
performance issue.
1. DB
and listener status:
Database and listener status is the basic check
confirmation.
2. Mount
points like archive destination and others:
We have check for the all-mount points and no mount
point should be 100%. And specially archive mount point which should be below
70 to 80%.
3. Alert
log and Trace file: which helps in
getting some issue related information
4. top
activity:
OEM monitoring tool (memory uses, CPU uses)
5. Blocking session and deadlock:
We can check blocking sessions and deadlock if occur any. Please click on blocking sessions and deadlock for more details.
6. User
session (active, inactive, ideal etc):
We can check the number of Inactive sessions and if
there is more number from long time then we can check details and kill them
like form sessions related sessions and other accordingly.
7. Increase
memory, CPU, resources if number of users
increased, check DB growth – We can check and compare the details from previous
to current like DB growth and user growth, so in case of DB and number of users
have increased then of course we need to increase the memory and number of CPU
to increase the performance.
8. Optimize
the query:
We can optimize the query also to improve query.
9. DB
Backup should run in non-business hours:
As we all know DB backup process uses resources which may also decrease the performance of database. So, always should run in non-hours business.
10. AWR,
ash and addm reports:
We can also take awr, ash and addm reports which
can help in analysis of the resource, sql etc... uses and can help in to
improve the database performance.
11. Wait
events and their types:
Here, we can check the what are events occurred in
AWR report which can be analysis and take action accordingly.
12. Oracle
bug:
After above checks if still now helpful then we can check for if there is any oracle bug. For this we need to raise the Oracle Support and may need to database apply patches like quarterly or security patches etc. as per suggested by oracle support.
13. Table
Scan check:
We can check if the query is scanning full table or not. Also, can check for GSS, last analyzed table/schema.
Hope this may useful, there are some more details
which will backup with some more details.
Thank you all!!!
Ora-data Team
