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.

When DBAs report a performance issue, the first step is to get as much information as possible that is related to the issue.

We can ask some questions to users/developers to collect the first level of information.

What operations/programs are executed?

Is it an Oracle seeded or custom program?

How much time did it used to take earlier?

Is the run time increased over time or you are seeing a sudden increase in run time?

Was there any recent code change/migration?

Is it always slow or for a certain time of the day only?

Is it slow for all parameters or for some specific parameters?

How much data is getting processed?

What is the frequency of job execution? Was there any change in frequency?

Does the problem happen on both their test and production systems?

After getting answers , it will help in deciding what part of the system you should target.

Target the whole system

Target a single session

Target a single SQL statement

Use vmstat top/prstat to identify system wide CPU/Memory consumption.

Use iostat to verify if disks are the bottleneck

Use netstat/tnsping etc to verify if the network is issue.

Verify if any other resource intensive processes are running on the server.

Verify filesystem space.

Check alert logs, application logs, traces etc.

Check database locks

Generate AWR reports to see what is eating up resources.

Check if increasing application/database memory/redo/undo/temp/SGA will help.

Find the Top SQLs executing under session.

Apply SQL optimization techniques on top SQLs.

Verify locking at session level

Generating AWR/ASH for that duration may help in providing useful information.

Apply trace, Generate TKPROF file and analyze for top resource consuming SQL.

Avoid full table scans on large tables

Possible indexes on columns contained in the WHERE clause

Use AWR/ASH reports to get collective information

Also use SQLTRPT/SQLT

Verify is statistics are current

Verify if indexes are valid and are adequate.

Verify is parallelism/materialized views/Baselines/SQL Profiles will help

Monitor V$SESSION_LONGOPS to detect long running operations

Decide on using Hints if those are helping.

Table partitioning can be thought of as an option based on kind and size of tables.

1) The various operations(eg:joins) to be performed during the query

2) The order in which the operations are performed

3) The algorithm to be used for performing each operation

4) The best way to retrieve data from disk or memory

5) The best way to pass data from one operation to another during the query

Oracle provides several tools/utilities to assist with performance monitoring and tuning like …

ADDM (Automated Database Diagnostics Monitor)

AWR - Tuning Pack

TKProf

Oracle Enterprise Manager-Tuning Pack

When a user is experiencing performance issues in an Oracle database, a database administrator typically follows a systematic approach to diagnose and resolve the problem. Here are the steps an Oracle DBA might take:

1. **Gather Information:**

- Communicate with the user to understand the specific performance issue and its impact on operations.

- Collect information such as the SQL statements causing slowness, the affected tables or indexes, recent changes to the database or application, and any error messages or alerts.

2. **Review System Metrics:**

- Monitor system metrics using tools like Oracle Enterprise Manager (OEM), Oracle Cloud Console, or third-party monitoring tools.

- Check CPU utilization, memory usage, disk I/O rates, network traffic, and other performance-related metrics to identify any bottlenecks.

3. **Check Database Health:**

- Verify the health of the database instance, including the status of background processes (e.g., PMON, SMON), the availability of tablespaces, and the size of redo logs and undo tablespaces.

- Check for any locks, waits, or contention issues that might be impacting performance.

4. **Analyze SQL Queries:**

- Use SQL tuning tools such as SQL Tuning Advisor, SQL Access Advisor, or Explain Plan to analyze the execution plans of slow SQL statements.

- Identify inefficient query patterns, missing indexes, unnecessary joins, or suboptimal SQL coding that can be optimized for better performance.

5. **Optimize Indexes and Statistics:**

- Review and optimize indexes to ensure they are being used effectively by the SQL queries.

- Refresh table and index statistics using the DBMS_STATS package to provide accurate information to the query optimizer.

6. **Review Configuration Settings:**

- Check database configuration settings such as memory allocation (SGA, PGA), optimizer parameters, redo log configuration, and resource manager settings.

- Adjust configuration settings based on best practices and workload requirements to improve performance.

7. **Tune Database Parameters:**

- Tune Oracle database parameters (e.g., buffer cache size, shared pool size, log buffer size) based on workload characteristics and performance metrics.

- Implement recommended parameter changes to optimize database performance.

8. **Implement Database Tuning Techniques:**

- Apply database tuning techniques such as partitioning, materialized views, compression, and caching to improve query performance and reduce resource consumption.

9. **Monitor and Test:**

- Monitor the impact of changes and optimizations on database performance.

- Conduct performance testing and benchmarking to validate improvements and identify any remaining issues.

10. **Document and Communicate:**

- Document the steps taken, optimizations applied, and performance improvements achieved.

- Communicate with stakeholders, including users and management, to provide updates on the performance issue resolution and ongoing performance monitoring efforts.