How DBA determines and handle the database when temporary tablespace running out of space. Here,we have two techniques to find how space in temporaray tablespace is being used :
1.) Direct Oracle to log every statement that fails for lack of temporary space.
2.) A set of queries to run at any time to capture in real time how temporary space is currently being used on a per-session or per-statement basis.
Identifying SQL Statements that Fail Due to Lack of Temporary Space :
It is helpful that Oracle logs ORA-1652 errors to the instance alert log as it informs a DBA that there is a space issue. The error message includes the name of the tablespace in which the lack of space occurred, and a DBA can use this information to determine if the problem is related to sort segments in a temporary tablespace or if there is a different kind of space allocation problem.
Unfortunately, Oracle does not identify the text of the SQL statement that failed. However, Oracle does have a diagnostic event mechanism that can be used to give us more information whenever an ORA-1652 error occurs by causing Oracle server processes to write to a trace file. This trace file will contain a wealth of information,including the exact text of the SQL statement that was being processed at the time that the ORA-1652 error occurred.
We can set a diagnostic event for the ORA-1652 error in our individual database session with the following statement:
SQL> alter session set events '1652 trace name errorstack';
We can also set diagnostic events in another session (without affecting all sessions instance-wide) by using the “oradebug event” command in SQL*Plus.We can deactivate the ORA-1652 diagnostic event or remove all diagnostic event settings from the server parameter file with statements such as the following:
SQL> alter session set events '1652 trace name context off';
If a SQL statement fails due to lack of space in the temporary tablespace and the ORA-1652 diagnostic event has been activated, then the Oracle server process that encountered the error will write a trace file to the directory specified by the user_dump_dest instance parameter.
*** ACTION NAME:() 2011-09-17 17:21:14.871
*** MODULE NAME:(SQL*Plus) 2011-09-17 17:21:14.871
*** SERVICE NAME:(SYS$USERS) 2011-09-17 17:21:14.871
*** SESSION ID:(130.13512) 2011-09-17 17:21:14.871
*** 2011-09-17 17:21:14.871
ksedmp: internal or fatal error
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Current SQL statement for this session:
SELECT "A1"."INVOICE_ID", "A1"."INVOICE_NUMBER", "A1"."INVOICE_DAT
E", "A1"."CUSTOMER_ID", "A1"."CUSTOMER_NAME", "A1"."INVOICE_AMOUNT",
"A1"."PAYMENT_TERMS", "A1"."OPEN_STATUS", "A1"."GL_DATE", "A1"."ITE
M_COUNT", "A1"."PAYMENTS_TOTAL"
FROM "INVOICE_SUMMARY_VIEW" "A1"
ORDER BY "A1"."CUSTOMER_NAME", "A1"."INVOICE_NUMBER"
From the trace file we can clearly see the full text of the SQL statement that failed. It is important to note that the statements captured in trace files with this method may not themselves be the cause of space issues in the temporary tablespace. For example, one query could run successfully and consume 99.9% of the temporary tablespace due to a Cartesian product, while a second query fails when trying to allocate just a small amount of sort space. The second query is the one that will get captured in a trace file, while the first query is more likely to be the root cause of the problem.
No comments:
Post a Comment