temp space

50 %
50 %
Information about temp space
Entertainment

Published on January 3, 2008

Author: Sarah

Source: authorstream.com

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:  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 White Paper:  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 Oracle Sorting Basics:  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. Memory Limits for Sorts :  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 Temporary Tablespaces:  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. Sort Segments:  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 Sort Segment Space :  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. Running Out of Space :  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 Space: Do We Care? :  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… Identifying Statements that Failed Due to Lack of Temp Space:  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 Setting the Diagnostic Event :  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'; Tracing an ORA-1652 :  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 Sample Trace File:  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 Sample Trace File:  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 ? Trace File Contents:  Trace File Contents When statement failed Statement text Session attributes (module, action, etc.) Other information not very important to us: Call stack trace Dumps Keep in Mind!:  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 Disclaimer:  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. Disclaimer:  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. Monitoring Temporary Space Usage:  Monitoring Temporary Space Usage Available from v$ views: Temporary segments Sort space usage by session Sort space usage by statement Monitoring Temporary Segments:  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; Temporary Segments:  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 Monitoring Sort Space by Session:  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; Sort Space by Session:  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 Monitoring Sort Space by Statement:  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; Sort Space By Statement:  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. Wrapping Up :  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. What We’ve Learned:  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. White Paper:  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 About Database Specialists:  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 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

Add a comment

Related presentations

Related pages

aT Tempspace 一鳴空間

... and compact living. Hong Kong people often complain about their tight living conditions and the very high cost of space, ...
Read more

What' s the Temperature of Outer Space

Some parts of space are hot! Gas between stars, as well as the solar wind, both seem to be what we call "empty space," yet they can be more than a thousand ...
Read more

Oracle und der TEMP-Tablespace - Oracle | XING

Hallo, wir haben in der Tat meistens am Wochenende Berechnugen, die dazu führen, dass der TEMP-Space auf 60 GB anwächst, während der Woche jedoch meist ...
Read more

TEMP Art Space

TEMP Art Space 57 Walker Street New York, NY 10013. TEMP is a contemporary art space in lower Manhattan. Our mission is to promote emerging artists and ...
Read more

Troubleshooting Insufficient Disk Space in tempdb

This topic provides procedures and recommendations to help you diagnose and troubleshoot problems caused by insufficient disk space in the tempdb database.
Read more

Temp Space - DJ Msfox jetzt als MP3 in top Qualität ...

Temp Space - DJ Msfox jetzt als MP3 in top Qualität herunterladen. Komplette Alben und Einzeltitel verfügbar - Amazon Music
Read more

Outer space - Wikipedia

Outer space, deep space, or just space, is the void that exists between celestial bodies, including the Earth. It is not completely empty, but consists of ...
Read more

Temp tablespace full | Oracle Community

Hi, Below is the query I am using to check the free and available space on a tablespace: SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes ...
Read more

Zentraler Informatikdienst » Temp-Space

Daten, die sich im Ordner public befinden, sind allgemein zugänglich und können z.B. von KollegInnen verwendet werden (nur Lesezugriff). Sensitive Daten ...
Read more

Temp Space - Microsoft Store

Ich stimme zu, dass diese Seite Cookies für Analysen, personalisierte Inhalte und Werbung verwendet.
Read more