Transcript 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
– Sort space usage by session
– Sort space usage by statement
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
 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
 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
 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
 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
 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
 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?
 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
 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
 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
 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
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
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




When statement failed
Statement text
Session attributes (module, action, etc.)
Other information not very important to us:
– Call stack trace
– Dumps
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
 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
 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
 Available from v$ views:
– Temporary segments
– Sort space usage by session
– Sort space usage by statement
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
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
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
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
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
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
 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
 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
 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
 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
Roger Schrag
Database Specialists, Inc.
www.dbspecialists.com