A well-planned methodology is the key to success in performance tuning. Without a proper diagnosis of the issue any troubleshooting step taken will further delay the resolution. Example, a DBA can not just blindly start tracing/tuning a SQL/Sessions when all symptoms on careful analysis points towards issues with Disk/Memory/Network etc.
Also remember that most common performance gains are attained through SQL/Code/application tuning and increasing hardware resources should always be your last resort. Developers play an important role in writing applications with effective SQL statements so it is important that the DBA and Developers should work together when facing a database/application issue.
When performance issues are reported to a DBA, the first step is to get as much information as possible that is related to the issue. You can ask below example preliminary questions to users/developer to collect the first level of information..
- What operations/program are executed?
- Is it Oracle seeded or custom program?
- How much time it used to take earlier?
- Is the run time increased over time or you are seeing sudden increase in run time?
- Was there any recent code change/migration?
- Is it always slow or for 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 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.