Oracle Optimization 001

Official Document: http://docs.oracle.com/cd/E11882_01/server.112/e41573/technique.htm#PFGRF94138

1. ADDM will give the optimization advice

2. How to find system bottlenecks:

Step1. Is the system's response time acceptable when a single user or system load is low?

If unacceptable, there may be problems with system data, indexing, etc.

Step2. Is the CPU fully utilized?

If the CPU is already fully utilized by Oracle, you need to troubleshoot several SQL that is the most CPU-intensive. If SQL is not a problem, you need to upgrade your device.

Step2. Wait_events Tuning

The most likely performance problem in a 3.oracle system

3.1 Bad connection management

3.2 Bad uses cursors and the shared pool do not use binding variables, causing system hard parsing too much

3.3 Bad SQL SQL statement consumes a lot of system resources

3.4 Use of nonstandard initialization parameters using non-standard initialization parameters

3.5 Getting Database I/O wrong

3.6 Online Redo Log setup problems the on-line redo log file is too small, the system checkpoint will constantly load pressure on the buffer cache and system IO, which can cause the archive to be too large to be timely.

3.7 Serialization of data blocks in the buffer cache due to lack of free lists, free list groups, transaction slots ( INITRANS ), or shortage of rollback segments.

Use ASSM to solve problems

3.8 Long Full table scans scan

3.9 High amounts of recursive ( SYS ) SQL SYS user invocation of a large number of recursive functions,

3.10 Deployment and migration errors a performance issue caused by the loss of table statistics, index loss, and so on during deployment and environment migration

　　

Oracle Optimization 001