SNAPSHOT TOO OLD ERROR
While setting a high value for the UNDO_RETENTION parameter can potentially minimize the possibility of receiving "snapshot too old" errors, it doesn't guarantee that the database won't overwrite older undo data that may be needed by a running transaction. You can move long-running batch jobs to a separate time interval when other programs aren't running in the database, to avoid these errors.
Regardless, while you can minimize the occurrence of "snapshot too old" errors with these approaches, you can't completely eliminate such errors without specifying the guaranteed undo retention feature. Oracle will keep new DML statements from executing when you set up guaranteed undo retention.
The "guarantee" part of the undo retention guarantee is real—Oracle will certainly retain undo at least for the time you specify and will never overwrite any of the unexpired undo extents that contain the undo required to satisfy the undo retention period.
TEMPORARY TABLESPACE
All database sessions share the temporary tablespace, the session that runs the large sort operation could potentially result in other sessions receiving errors due to lack of room in that tablespace. Once the temporary tablespace fills up, all SQL statements that seek to use the temporary tablespace will fail with the ORA-1652: unable to extend temp segment error.
Once a large operation uses its share of the PGA memory, Oracle will write intermediary results to disk in the temporary tablespace. These types of operations are called one-pass or multi-pass operations, and since they are performed on disk, they are much slower than an operation performed entirely in the PGA.
RESOLVING THE "UNABLE TO EXTEND TEMP SEGMENT" ERROR
Oracle starts the creation of the new index by putting the new index structure into a temporary segment in the tablespace you specify (INDX_01 in our example) for the index. The reason is that if your index creation process fails, Oracle (to be more specific, the SMON process) will remove the temporary segment from the tablespace you specified for creating the new index. Once the index is successfully created (or rebuilt), Oracle converts the temporary segment into a permanent segment within the INDX_01 tablespace. However, as long as Oracle is still creating the index, the database deems it a temporary segment and thus when an index creation fails, the database issues the ORA-01652 error, which is also the error code for an "out of space" error for a temporary tablespace.
When you create an index, the creation process uses two different temporary segments. One temporary segment in the TEMP tablespace is used to sort the index data. Another temporary segment in the permanent tablespace holds the index while it is being created. After creating the index, Oracle changes the temporary segment in the index's tablespace into a permanent segment. The same is the case when you create a table with the CREATE TABLE…AS SELECT (CTAS) option.
RESOLVING OPEN CURSORS ISSUE
If the application layer never closes the ref cursors created by the PL/SQL code, the database will simply hang on to the server resources for the used cursors. You must fix the application logic so it closes the cursors—the problem isn't really in the database.
If you're using a Java application deployed on an application server such as the Oracle WebLogic Server, the WebLogic Server's JDBC connection pools provide open database connections for applications. Any prepared statements in each of these connections will use a cursor. Multiple application server instances and multiple JDBC connection pools will mean that the database needs to support all the cursors. If multiple requests share the same session ID, the open cursor problem may be due to implicit cursors. The only solution then is to close the connection after each request.
While setting a high value for the UNDO_RETENTION parameter can potentially minimize the possibility of receiving "snapshot too old" errors, it doesn't guarantee that the database won't overwrite older undo data that may be needed by a running transaction. You can move long-running batch jobs to a separate time interval when other programs aren't running in the database, to avoid these errors.
Regardless, while you can minimize the occurrence of "snapshot too old" errors with these approaches, you can't completely eliminate such errors without specifying the guaranteed undo retention feature. Oracle will keep new DML statements from executing when you set up guaranteed undo retention.
The "guarantee" part of the undo retention guarantee is real—Oracle will certainly retain undo at least for the time you specify and will never overwrite any of the unexpired undo extents that contain the undo required to satisfy the undo retention period.
TEMPORARY TABLESPACE
All database sessions share the temporary tablespace, the session that runs the large sort operation could potentially result in other sessions receiving errors due to lack of room in that tablespace. Once the temporary tablespace fills up, all SQL statements that seek to use the temporary tablespace will fail with the ORA-1652: unable to extend temp segment error.
Once a large operation uses its share of the PGA memory, Oracle will write intermediary results to disk in the temporary tablespace. These types of operations are called one-pass or multi-pass operations, and since they are performed on disk, they are much slower than an operation performed entirely in the PGA.
RESOLVING THE "UNABLE TO EXTEND TEMP SEGMENT" ERROR
Oracle starts the creation of the new index by putting the new index structure into a temporary segment in the tablespace you specify (INDX_01 in our example) for the index. The reason is that if your index creation process fails, Oracle (to be more specific, the SMON process) will remove the temporary segment from the tablespace you specified for creating the new index. Once the index is successfully created (or rebuilt), Oracle converts the temporary segment into a permanent segment within the INDX_01 tablespace. However, as long as Oracle is still creating the index, the database deems it a temporary segment and thus when an index creation fails, the database issues the ORA-01652 error, which is also the error code for an "out of space" error for a temporary tablespace.
When you create an index, the creation process uses two different temporary segments. One temporary segment in the TEMP tablespace is used to sort the index data. Another temporary segment in the permanent tablespace holds the index while it is being created. After creating the index, Oracle changes the temporary segment in the index's tablespace into a permanent segment. The same is the case when you create a table with the CREATE TABLE…AS SELECT (CTAS) option.
RESOLVING OPEN CURSORS ISSUE
If the application layer never closes the ref cursors created by the PL/SQL code, the database will simply hang on to the server resources for the used cursors. You must fix the application logic so it closes the cursors—the problem isn't really in the database.
If you're using a Java application deployed on an application server such as the Oracle WebLogic Server, the WebLogic Server's JDBC connection pools provide open database connections for applications. Any prepared statements in each of these connections will use a cursor. Multiple application server instances and multiple JDBC connection pools will mean that the database needs to support all the cursors. If multiple requests share the same session ID, the open cursor problem may be due to implicit cursors. The only solution then is to close the connection after each request.
No comments:
Post a Comment