Oracle Point, Oracle Life.

Slow Running SQL results in Oracle performance degradation

Author: ChunPei Feng & R. Wang

KeyWords: Histograms, Execution Plan, DBMS_STATS, CBO Optimizer, hidden init parameters

Environment: Oracle 9iR2 and Unix, production database and standby database

Circumstance:

In the morning, routine daily database checking shows that the database has an unusual heavy load. As DBA, definitely, the first checking step is to monitor the top OS processes with command TOP or PRSTAT, which offer an ongoing look at processor activity in real time. In this case, however, a list of the most CPU-intensive processes on the system does not tell us anything special which might particularly cause the database performance degradation.

Next, information fetching about TOP SQL and long-running SQL also fail to figure out the possible reason of this performance problem.

Also, the team of application development confirms that no change has been made at the application level. And, application log doesn’t show exception on heavy jobs and excessive user logon.

According to the information above, it can be concluded that the corrupt database performance is caused by issues relating to the database server.

Steps to diagnose:

1. Check and Compare Historical Statspack Reports

So far, no one is able to tell which job attributes to performance degradation because hundreds of processes, which reside on tens of Unix servers, make DBAs difficult to track process by process. Here, the more feasible action is to recur to Statspack, which provides a great deal of performance information about an Oracle database. By keeping historical Statspack reports, it makes possible to compare current Statspack report to the one in last week. The report, generated at peak period (9:00AM - 10:00AM), is sampled to compare to one of report created in last week at same period.

Upon comparison, the instant finding is that CPU time is increased by 1,200 (2341 vs. 1175) seconds. Usually, the significant increase on CPU time very likely attribute to the following two scenarios:

• More jobs loaded
• The execution plan of SQLs is changed

For complete article of this, see Case_SQL_slow.pdf

Popularity: 9% [?]