Performance tuning is the way to make optimal uses of an oracle database system using existing resources.

We can get performance problems on databases on multiple levels like the database was completely slow and users are not able to login to it.

Might be performing a problem occurred within a certain time window, might be anyone's user process is running slow , might be some batch job is running slow. might be only one user is getting some performance issue when he is executing the query. Might be some sql session is eating high CPU like that.

So we can get multiple types of performance problems on different levels.

As a DBA , my actions will depend on the problem level.

Suppose , a user asked that his batch job was running slow between 1 to 2 pm and last week that was running fine and today It took hours to complete. So in this case, we identify the SQL that was running between the problem window through AHS, AWR and ADDM report and check, Is there a transactional lock? We check the excitation plan , is the plan the same or changed ? We check the object stats ? We check the batch job size might be possible today, the user processes more rows instead of previous.

Suppose, some oracle session is consuming a lot of CPU and due to this load average is very high . In this case we find the SQL_ID based on the user session and we start digging for information.

Suppose , database was slow between the windows, then we generate the AWR report and start to analyse the report and try to find out the solution.

If we have a diagnosis pack licence we can use several tools to get suggestions to improve sql performance and then implement the suggestion into the database.

If we don't have a diagnosis pack licence then we have to get a manual suggestion.

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.