How to improve oracle database performance tuning

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