This section lists the most common mistakes found in Oracle systems. By following the Oracle performance improvement methodology, you should be able to avoid these mistakes altogether. If you find these mistakes in your system, then re-engineer the application where the performance effort is worthwhile.
1. Bad Connection Management
The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. It has over two orders of magnitude impact on performance, and is totally unscalable.
2. Bad Use of Cursors and the Shared Pool
Not using cursors results in repeated parses. If bind variables are not used, then there is hard parsing of all SQL statements. This has an order of magnitude impact in performance, and it is totally unscalable. Use cursors with bind variables that open the cursor and execute it many times. Be suspicious of applications generating dynamic SQL.
3. Bad SQL
Bad SQL is SQL that uses more resources than appropriate for the application requirement. This can be a decision support systems (DSS) query that runs for more than 24 hours or a query from an online application that takes more than a minute. SQL that consumes significant system resources should be investigated for potential improvement. ADDM identifies high load SQL and the SQL tuning advisor can be used to provide recommendations for improvement.
4. Use of Nonstandard Initialization Parameters
These might have been implemented based on poor advice or incorrect assumptions. Most systems will give acceptable performance using only the set of basic parameters. In particular, parameters associated with SPIN_COUNT on latches and undocumented optimizer features can cause a great deal of problems that can require considerable investigation.
Likewise, optimizer parameters set in the initialization parameter file can override proven optimal execution plans. For these reasons, schemas, schema statistics, and optimizer settings should be managed together as a group to ensure consistency of performance.
5. Getting Database I/O Wrong
Many sites lay out their databases poorly over the available disks. Other sites specify the number of disks incorrectly, because they configure disks by disk space and not I/O bandwidth.
6. Redo Log Setup Problems
Many sites run with too few redo logs that are too small. Small redo logs cause system checkpoints to continuously put a high load on the buffer cache and I/O system. If there are too few redo logs, then the archive cannot keep up, and the database will wait for the archive process to catch up.
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.
This is particularly common on INSERT-heavy applications, in applications that have raised the block size above 8K, or in applications with large numbers of active users and few rollback segments. Use automatic segment-space management (ASSM) and automatic undo management to solve this problem.
8. Long Full Table Scans
Long full table scans for high-volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long table scans, by nature, are I/O intensive and unscalable.
9. High Amounts of Recursive (SYS) SQL
Large amounts of recursive SQL executed by SYS could indicate space management activities, such as extent allocations, taking place. This is unscalable and impacts user response time. Use locally managed tablespaces to reduce recursive SQL due to extent allocation. Recursive SQL executed under another user ID is probably SQL and PL/SQL, and this is not a problem.
10. Deployment and Migration Errors
In many cases, an application uses too many resources because the schema owning the tables has not been successfully migrated from the development environment or from an older implementation. Examples of this are missing indexes or incorrect statistics. These errors can lead to sub-optimal execution plans and poor interactive user performance. When migrating applications of known performance, export the schema statistics to maintain plan stability using the DBMS_STATS package.
Although these errors are not directly detected by ADDM, ADDM highlights the resulting high load SQL.
Showing posts with label Guideline. Show all posts
Showing posts with label Guideline. Show all posts
Increase Concurrency By Indexing Foreign Key
Concurrency Control, Indexes, and Foreign Keys
Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behavior depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.
No Index on the Foreign Key
In the following circumstances, the database acquires a table lock on the child table:
In above diagram, an unindexed foreign key column in the child table causes the deletion of row 3 in the parent to acquire a share table lock on the child table. This lock enables other transactions to query but not update the table. For example, phone numbers in employees cannot be updated while the departments row is being deleted. The table lock releases immediately after the DML on the departments table completes. If multiple rows are affected, then the lock is obtained and released once for each row.
Note: DML on a child table does not acquire a table lock on the parent table.
Index on the Foreign Key
If a foreign key column in the child table is indexed, then DML on the parent table acquires a table lock on the parent table. This lock prevents transactions from acquiring exclusive table locks, but does not prevent DML on the parent or the child table while the DML on the parent table occurs. This situation is preferable if updates or deletions occur on the parent table while updates occur on the child table.
Bellow diagram shows a scenario in which the foreign key column in the child table is indexed. The parent table is departments and the child table is employees. A session updates row 3 in departments. The DML on departments does not prevent updates to employees, although updates and deletions of rows in departments must wait for row-level locks on the indexes of employees to clear.
If the child table specifies ON DELETE CASCADE, then deletions from the parent table can result in deletions from the child table. For example, a deletion of a record from departments can cause the deletion of records from employees for employees in the deleted department. In this case, waiting and locking rules are the same as if you deleted rows from the child table after deleting rows from the parent table.
Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behavior depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.
No Index on the Foreign Key
In the following circumstances, the database acquires a table lock on the child table:
- No index exists on the foreign key column of the child table. For example, assume that hr.departments table is a parent of hr.employees, which contains the unindexed foreign key department_id.
- A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges data into the parent table. Inserts into the parent table do not acquire table locks on the child table. For example, a database session deletes row 3 from the departments table.
In above diagram, an unindexed foreign key column in the child table causes the deletion of row 3 in the parent to acquire a share table lock on the child table. This lock enables other transactions to query but not update the table. For example, phone numbers in employees cannot be updated while the departments row is being deleted. The table lock releases immediately after the DML on the departments table completes. If multiple rows are affected, then the lock is obtained and released once for each row.
Note: DML on a child table does not acquire a table lock on the parent table.
Index on the Foreign Key
If a foreign key column in the child table is indexed, then DML on the parent table acquires a table lock on the parent table. This lock prevents transactions from acquiring exclusive table locks, but does not prevent DML on the parent or the child table while the DML on the parent table occurs. This situation is preferable if updates or deletions occur on the parent table while updates occur on the child table.
Bellow diagram shows a scenario in which the foreign key column in the child table is indexed. The parent table is departments and the child table is employees. A session updates row 3 in departments. The DML on departments does not prevent updates to employees, although updates and deletions of rows in departments must wait for row-level locks on the indexes of employees to clear.
If the child table specifies ON DELETE CASCADE, then deletions from the parent table can result in deletions from the child table. For example, a deletion of a record from departments can cause the deletion of records from employees for employees in the deleted department. In this case, waiting and locking rules are the same as if you deleted rows from the child table after deleting rows from the parent table.
Guidelines for Managing Tablespaces
Guidelines for Managing Tablespaces
Before working with tablespaces of an Oracle Database, familiarize yourself with the guidelines provided in the following sections:
1. Using Multiple Tablespaces
Using multiple tablespaces allows you more flexibility in performing database operations. When a database has multiple tablespaces, you can:
Separate user data from data dictionary data to reduce I/O contention.
Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
Store the datafiles of different tablespaces on different disk drives to reduce I/O contention.
Take individual tablespaces offline while others remain online, providing better overall availability.
Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage.
Back up individual tablespaces.
Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently. Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles.
Review your data in light of these factors and decide how many tablespaces you need for your database design.
2. Assigning Tablespace Quotas to Users
Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object segment.
Before working with tablespaces of an Oracle Database, familiarize yourself with the guidelines provided in the following sections:
1. Using Multiple Tablespaces
Using multiple tablespaces allows you more flexibility in performing database operations. When a database has multiple tablespaces, you can:
Separate user data from data dictionary data to reduce I/O contention.
Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
Store the datafiles of different tablespaces on different disk drives to reduce I/O contention.
Take individual tablespaces offline while others remain online, providing better overall availability.
Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage.
Back up individual tablespaces.
Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently. Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles.
Review your data in light of these factors and decide how many tablespaces you need for your database design.
2. Assigning Tablespace Quotas to Users
Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object segment.
Subscribe to:
Posts (Atom)