1 / 31

What You Can Do When Your Database Runs out of Temp Space

What You Can Do When Your Database Runs out of Temp Space. Roger Schrag Database Specialists, Inc. www.dbspecialists.com. Today's Session. Oracle sorting basics Identifying SQL statements that fail Diagnostic event 1652 Monitoring temporary space usage Temporary segments

evers
Download Presentation

What You Can Do When Your Database Runs out of Temp Space

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. What You Can Do When Your Database Runs out of Temp Space Roger Schrag Database Specialists, Inc. www.dbspecialists.com

  2. Today's Session • Oracle sorting basics • Identifying SQL statements that fail • Diagnostic event 1652 • Monitoring temporary space usage • Temporary segments • Sort space usage by session • Sort space usage by statement

  3. White Paper • Contains all of the material we will discuss today and more. • Code samples and sample output are easier to read. • Easier to cut and paste the code for testing on your system. • Download from: www.dbspecialists.com/presentations

  4. Oracle Sorting Basics • Sorts required for, among others: • Most index builds • Many ORDER BY and GROUP BY clauses • Operations that use memory like sorts do include: • Hashes • Bitmap merges • Global temporary table instantiations • Small sorts are performed in memory. • Bigger sorts require disk space for storing partial results.

  5. Memory Limits for Sorts • When workarea_size_policy = AUTO: • pga_aggregate_target specifies how much memory can be used by all sessions • Oracle decides how to allocate available memory to individual sessions • When workarea_size_policy = MANUAL: • sort_area_size, hash_area_size, etc. • When memory limit reached: • Partial results written to a temporary segment in a temporary tablespace

  6. Temporary Tablespaces • Each database user has a designated temporary tablespace. • A group of temporary tablespaces can be designated for a user in Oracle 10g. • Users don’t need quota on temporary tablespaces. • In fact, such quotas are ignored. • See dba_users and dba_tablespace_groups.

  7. Sort Segments • A shorter way of saying “temporary segments in temporary tablespaces” • Owned by SYS • Just one sort segment per temp tablespace • Multiple sorts in multiple sessions can share one sort segment • Temp tablespaces can only hold sort segments • Oracle internals are optimized for this fact

  8. Sort Segment Space • A statement can require multiple sorts. • A database session can have multiple statements active at once. • Sort segment blocks get an “unused” status when their contents are no longer needed. • Sort segment doesn’t shrink.

  9. Running Out of Space • A sort will fail if: • No unused blocks in sort segment, and • No space in the temporary tablespace for sort segment to allocate an additional extent • When a sort fails for lack of space: • User sees: “ORA-1652: unable to extend temp segment” • Message written to alert log also • Not all ORA-1652s are sort space issues: • Example: ALTER TABLE…MOVE

  10. Space: Do We Care? • Sort space is a shared resource • Quotas can’t limit one user’s demands • One bad query can chew up everybody’s temporary space: • An extra table in the FROM clause… • A missing join condition…

  11. Identifying Statements that Failed Due to Lack of Temp Space • ORA-1652 in alert log tells you when a statement failed: • Tablespace name is identified • Statement text is not provided • Diagnostic event can be set for ORA-1652: • Oracle writes statement to trace file • Low overhead: Trace only written when ORA-1652 error occurs

  12. Setting the Diagnostic Event • To set diagnostic event for ORA-1652 in your session: ALTER SESSION SET EVENTS '1652 trace name errorstack'; • To set in all sessions instance-wide: ALTER SYSTEM SET EVENTS '1652 trace name errorstack'; • To deactivate: ALTER SESSION SET EVENTS '1652 trace name context off'; ALTER SYSTEM SET EVENTS '1652 trace name context off';

  13. Tracing an ORA-1652 • When an ORA-1652 error happens in a session with the diagnostic event set, Oracle writes a trace file to the user_dump_dest directory. • Alert log indicates trace file written: Tue Jan 2 17:21:14 2007 Errors in file /u01/app/oracle/admin/rpkprod/udump/rpkprod_ora_10847.trc: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

  14. Sample Trace File Oracle Database 10g Release 10.2.0.2.0 - 64bit Production ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_2 System name: SunOS Node name: rpk Release: 5.8 Version: Generic_108528-27 Machine: sun4u Instance name: rpkprod Redo thread mounted by this instance: 1 Oracle process number: 18 Unix process pid: 10847, image: oracle@rpk (TNS V1-V3) *** ACTION NAME:() 2007-01-02 17:21:14.871 *** MODULE NAME:(SQL*Plus) 2007-01-02 17:21:14.871 *** SERVICE NAME:(SYS$USERS) 2007-01-02 17:21:14.871 *** SESSION ID:(130.13512) 2007-01-02 17:21:14.871 *** 2007-01-02 17:21:14.871

  15. Sample Trace File 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" ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) --------------- -------- -------------------- ------------------------- ksedmp()+632 CALL ksedst()+0 000000017 ? FFFFFFFF7FFF8D8C ? 000000000 ? FFFFFFFF7FFF8900 ? 00000000A ? 00000000C ? ksddoa()+276 PTR_CALL 0000000000000000 000000001 ? 000000000 ? 000104B5A ? 000104800 ? 000104800 ? 104B5AF5C ?

  16. Trace File Contents • When statement failed • Statement text • Session attributes (module, action, etc.) • Other information not very important to us: • Call stack trace • Dumps

  17. Keep in Mind! • Statement captured in trace file failed due to lack of space • Statement may or may not be the cause of temp space shortage on system • Consider: • Query 1 consumes 99% of temp space on database and succeeds • Query 2 fails when trying to allocate a small amount of temp space

  18. Disclaimer • The diagnostic event facility has been present in Oracle for many years. • Some diagnostic events are widely known and commonly used. • Example: 10046 for extended SQL trace • Various Metalink documents show how to set a diagnostic event to write a trace file when a specific Oracle error occurs. • Example: Metalink document 217274.1 shows how to set a diagnostic event to write a trace file when an ORA-942 error occurs.

  19. Disclaimer • Don’t set diagnostic events unless you know what they do. • Some diagnostic events alter database behavior in unpredictable ways. • The ORA-1652 diagnostic event shown here appears safe because it seems functionally equivalent to diagnostic events prescribed by Oracle Support in Metalink documents.

  20. Monitoring Temporary Space Usage Available from v$ views: Temporary segments Sort space usage by session Sort space usage by statement

  21. Monitoring Temporary Segments One row for each sort segment in the database: SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;

  22. Temporary Segments TABLESPACE MB_TOTAL MB_USED MB_FREE ------------------------------- ---------- ---------- ---------- TEMP 10000 9 9991 • TEMP has one sort segment • TEMP is 10,000 Mb in size • 9 Mb of the sort segment is in use • 9,991 Mb is available: • Unused blocks in sort segment and/or • Unallocated extents in TEMP

  23. Monitoring Sort Space by Session One row for each session using sort segment space: SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial;

  24. Sort Space by Session SID_SERIAL USERNAME OSUSER SPID MODULE PROGRAM MB_USED TABLESPACE SORT_OPS ---------- -------- ------ ---- ------ --------- ------- ---------- -------- 33,16998 RPK_APP rpk 3061 inv httpd@db1 9 TEMP 2 • Session 33 with serial number 16998 has two active sorts to disk • Using 9 Mb of sort segment space in TEMP • Database user is RPK_APP • Oracle server process has PID 3061

  25. Monitoring Sort Space by Statement Rows for each statement using sort segment space: SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.sqladdr address, Q.hash_value, Q.sql_text FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name ORDER BY S.sid;

  26. Sort Space By Statement SID_SERIAL USERNAME MB_USED TABLESPACE ADDRESS HASH_VALUE ---------- -------- ------- ---------- ---------------- ---------- SQL_TEXT -------------------------------------------------------------------------------- 33,16998 RPK_APP 8 TEMP 000000038865B058 3641290170 SELECT * FROM NOTIFY_MESSAGES NM WHERE NM.AWAITING_SENDING = 'y' AND NOT EXISTS ( SELECT 1 FROM NOTIFY_MESSAGE_GROUPS NMG WHERE NMG.MESSAGE_GROUP_ID = NM.MESSAG E_GROUP_ID AND NMG.INCOMPLETE = 'y' ) ORDER BY NM.NOTIFY_MESSAGE_ID 33,16998 RPK_APP 1 TEMP 00000003839FFE20 1874671316 select * from rpk_stat where sample_group_id = :b1 order by stat#, seq# • Session 33 with serial number 16998 has two statements with active sorts to disk. • One statement is using 8 Mb of sort segment space, and the other is using 1 Mb. • Hash values and addresses in the shared SQL area are provided.

  27. Wrapping Up • When a sort is too big to fit in memory, Oracle allocates space in a sort segment. • Temporary space is a resource shared by database users. • One user performing an unreasonably large sort can impact other users. • It is easy to detect when a statement has failed due to lack of sort space.

  28. What We’ve Learned • By setting a simple diagnostic event it is easy to see the exact text of each statement that fails due to lack of sort space. • By querying v$ views it is easy to monitor sort space usage in real time. • Oracle DBAs can use these techniques to diagnose temporary tablespace problems and monitor sorting activity in a proactive way. • These tactics can be helpful for addressing both chronic and intermittent shortages of temporary space.

  29. White Paper • Contains all of the material we discussed today and more. • Code samples and sample output are easier to read. • Easier to cut and paste the code for testing on your system. • Download from: www.dbspecialists.com/presentations

  30. About Database Specialists • Database Specialists, Inc. provides Oracle database consulting in Solaris, Linux, HP-UX, AIX, and Windows environments. • Our DBA Pro offering and Database Rx™ tools provide remote database support and 24/7 coverage at an attractive price point. • We specialize in short term projects including upgrades, performance tuning and health checks. • Our Oracle DBAs each have a minimum of 10 years of Oracle experience with a focus on Oracle technology, mission-critical production support and RAC environments. • Database Specialists is US-based. Database Specialists helps you increase uptime, improve performance, minimize risk, and reduce costs

  31. What You Can Do When Your Database Runs Out of Temp Space Roger Schrag Database Specialists, Inc. www.dbspecialists.com

More Related