Oracle DBA Question: Download Questions PDF
Process you follow to start looking into Performance issue at database level
(If the application is running very slow, at what points do you need to go about the database in
order to improve the performance?)
Answer:
► Run a TOP command in Unix to see CPU usage (identify CPU killer processes)
► Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and memory usage and possible blocking
► Run STATSPACK report to identify:
1. TOP 5 WAIT EVENTS
2. RESOURCE intensive SQL statements
► See if STATISTICS on affected tables needs to be re-generated
► IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and see whether new index or use of HINT brings the cost of SQL down.
► Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and memory usage and possible blocking
► Run STATSPACK report to identify:
1. TOP 5 WAIT EVENTS
2. RESOURCE intensive SQL statements
► See if STATISTICS on affected tables needs to be re-generated
► IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and see whether new index or use of HINT brings the cost of SQL down.
Download Oracle DBA Interview Questions And Answers
PDF
| Previous Question | Next Question |
| How do you install Statspack? | Explain below wait events in STATSPACK report DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE? |