Analyzing Basic Performance Issues

Analyzing Basic Performance Issues 

Whenever any performance issue it is important that DBA and Developers should work together when facing a database/application issue.

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

You can ask below questions to users/developer to collect the first level of information..

 1. What operations/program are executed?

 2. Is it Oracle seeded or custom program?

 3. How much time it used to take earlier?

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

 5. Was there any recent code change/migration?

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

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

 8. How much data is getting processed?

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

 10. Does the problem happens on both their test and production systems?

Asking above kind of questions will help you in deciding what part of system you should target.

-Target the whole system

-Target a single session

-Target a single SQL statement

 

Your goal should be to answer below three questions:

Where is time spent?

You need to identify where the time is getting consumed. Is it the code or network latency or disk I/O bottlenecks or the CPU/Memory crunch

How is time spent?

You need to find out how the time is spent and how much time is spend in each layer – database, application, network, disk etc .

How to reduce the time spent?

Finally, based on above information see where the major chunk of time is spent and how to reduce it.

 --> High level steps for System level performance issues

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 network is issue.

Verify if any other resource intensive processes are running on 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.

 

--> High level steps for Session level performance issues

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.

 

--> High level steps for SQL level performance issues

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.