Top 10 common Oracle errors

 

1. Poor connection management
Each time an application interacts with a database, the connection is opened and closed. This problem is often related to stateless middleware in the application server. It affects performance by more than two orders of magnitude and is completely unscalable.

 

2. poor use of cursors and sharing pools
The opened cursor result set is not used during repeated parsing. If no variable is bound, all SQL statements must be hard parsed, which has an order of magnitude impact on performance and cannot be scaled at all. Variable binding should be used to open and execute multiple cursors. The dynamic SQL statements generated by the application are also suspicious.

 

3. Bad SQL
Poor SQL statements require more resources than normal applications. This may be a query of the decision support system that has been running for more than 24 hours, or an online application that has been running for more than one minute. SQL statements that consume a large amount of system resources should be checked as problematic statements. ADDM's high-load SQL and SQL adjustment consultants can be used to improve performance recommendations.

 

4. Use non-standard initialization parameters
These suggested or hypothetical Parameters Based on errors may have been set. Most systems only need to set basic parameters to obtain acceptable performance. Under certain circumstances, parameters related to the spin_count of the plug-in and the optimization features not documented may cause a large number of problems, which may need to be investigated carefully.
Similarly, the optimization parameters set in the initialization file may overwrite the execution plan that has been proven to be good. For this reason, pattern, pattern statistics, and optimization settings should be managed together as a group to ensure performance consistency.

 

5. Get the wrong database I/O
Many sites deploy their databases on a disk. In addition, some sites are allocated the wrong number of disks because they only configure Disks Based on the disk space, regardless of the I/O bandwidth.

 

6. redo log configuration issues
Many sites use too few and too small to redo logs. Too small redo logs cause consecutive system checkpoints, resulting in high-speed cache of the buffer and high load of the I/O system. If there are too few redo logs, archiving cannot continue and the database will wait for the archiving process to complete.

 

7. Data blocks in the buffer cache are serialized due to lack of idle lists, idle list groups, transaction slots, or rollback segments.
This situation is usually prone to insert-intensive applications. Applications with more than 8 K blocks are added, or applications with a large number of active users but few rollback segments are involved. Use automatic segment space management (assm) and automatic rollback segment management to solve this problem.

 

8. Long Full table Scan
A long full table scan for large data volumes or interactive online operations indicates poor transaction design, missing indexes, or non-optimized SQL statements. A long full table scan is I/O intensive and not scalable.

 

9. A large number of recursive (system) SQL statements
The sys user executes a large number of recursive SQL statements to indicate that space management activities have occurred, such as the allocation interval. This is not scalable and affects the user's corresponding time. Use local tablespace management to reduce recursive SQL statements caused by interval allocation. The recursive SQL statement executed under another user ID may be an SQL statement or PL/SQL statement.

 

10. Deployment and migration errors
In many cases, an application uses too many resources because the tables in the mode are not successfully migrated from the development environment or the old system. Such examples include missing indexes or incorrect statistics. These errors may lead to non-optimized execution plans and poor User Interaction performance. When migrating an application with known performance, use the statistical information of the dbms_stats package output mode to maintain the stability of the execution plan.