UPDATE: dbWatch Control Center now has additional advanced performance tools. Read about these the updated blog: “Analyzing Oracle performance with the new performance tools in dbWatch Control Center”.
Performance tuning in Oracle is a repetitive process of searching for and identifying bottlenecks in your Oracle database. After you identify the culprit of the performance issue, you can fix it by optimizing the problem query or adding indexes.
It’s easier to change from being a firefighting to preventing fires. As a responsible and proactive Oracle database administrator, you should put in place “alerts and notifications” to keep track of the key performance metrics you should focus on.
What are the key performance metrics needed in Oracle?
Here is a list of performance statistics that you should track:
1. CPU Usage/Load
The image below shows the query we executed in dbWatch Control Centers’ SQL worksheet and your database’s top CPU usage per session.
As an example, below is the dbWatch Control Center monitoring module, where you can select the CPU usage performance job to find out if there are any unusual spikes in your CPU usage throughout the day or week.
You can configure this job to send you an alarm if a certain threshold has been reached. Proactive monitoring keeps alerts in place and is essential to your Oracle database.
2. Long-Running Queries
To find long-running queries executing in your Oracle database, you can execute the following command:
3. User memory usage rate
To identify the top memory usage rate per session, execute the command below:
dbWatch Control Center has a job monitoring feature for your memory usage, also called the “Top user memory usage” job.
This report helps you determine if there is an abnormal memory usage spike throughout the working day. It also shows information about the specific session ID, memory usage, and the SQL statement that is being executed. A report such as this is beneficial when performing your query optimization, as you know where to start fixing the root cause of your performance issues.
4. Blocking Sessions
Long-running queries can cause blocking sessions in our databases, especially, if a query from one session is trying to access the resource of an object in another session that is being locked for too long because of an operation such as updating of table data. Execute this query to find out if any blocking session is happening in your database:
In the monitoring module, you have a job named Blocking detector, where you will find more details about the blocking session along with the username and type of lock – the blocker and the waiter to kill the session.
This job is also configurable so that you can set the max threshold of the blocking session duration. If the duration threshold has been reached, you will get notified through email or SMS.
5. Wait Statistics
One of the most critical performance statistics is your Oracle “Wait Statistics” job. As an Oracle DBA, you need to identify if there is any performance degradation while you check for the wait statistics, as it gives information for slow response time in your system.
This report shows you a detailed chart of your different Oracle “wait” types so that you will see if there is a sudden unusual spike in your wait.
With dbWatch Control Center, you can now keep track of Oracle database performance and analyze how your Oracle database is performing.
Understand more about database management, try dbWatch today.
