- 18 Nov, 2019
- 0 Comments
- 2 Mins Read
Oracle Database Performance tuning tips
In this blog I will try to explain the common Oracle database performance tuning tips and tricks.
- Check is the full table scan running
This is really important to check in the queries running through full table scan or index scan.
If the query runs with the full table scan then definitely it will take long time to get the result. It is always recommended to use the index along with the table scan.
Tables can mainly for when we use where clause along with the sql queries.
- Top Activities
In the database performance is going suddenly down then it is always good idea to check the top activities running on the database.
In order to check stop activities on data bases we can use oem monitoring tool for the database or simply we can use the queries and then that queries on command line interface.
oem is a monitoring tool which is mainly useful for the monitoring of the database is and it is also useful to check the performance of database.
- User session activities
Database can have multiple sessions the session can be active, inactive or idle session.
We need to check what station is exactly what doing for that we can use the v$session as the view.
- Gather states
After doing the lot of deletion on the database there is a chances that the index will be get changed it is always recommended to use for gather statistics to make the Optimizer up to date.
While the sql query is getting executed Optimizer needs to create the plan and it has to choose the best plan among the different plan.
- Reports AWR, ADDM, ASH
If you are planning to to check for the performance of database in Oracle.it also comes with reports like awr, addm, ash . oracle database report will help you to figure out the problems in the database and it will also tell you the utilisation of the current databases.
- Increase resources
If the database size is too large to handle the queries based upon the resources like memory,disk , CPU then it is recommended to increase the resources of the databases.
Increasing the hardware resources will improve the performance of the database.
- Database growth
As the database is in production Environment Day by the size of database will go increase and the number of users will get increase.
Depending upon the database growth we need to take care of the hardware resources and along with that it is always good to monitor the database growth. the database growth can be monitored through oem very easily which is again a monitoring tool