September 2006

The Specialist
Database News You Can Use
A monthly newsletter for Oracle usersSeptember 2006
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: More about Temporary Segments

You’re invited to submit your Oracle-related questions to us at askdbspecialists09@dbspecialists.com. Please include your name and telephone number in your email.

This month’s question comes to us from Greg in Denver: We are getting the “ORA-01652: unable to extend temp segment by 1250 in tablespace TEMP” error on our database. We’ve used the event-setting technique explained in the July 2006 edition of The Specialist to identify SQL statements that fail with the ORA-01652 error. However, the statements that are failing don’t seem like they would use a lot of temp space. Is there a way to identify SQL statements that use a lot of temp space?

Danny Chow of the Database Specialists team responds: You may find that a query that fails with an ORA-01652 error does not itself use a lot of temp space. There could be other active statements using a lot of temp space and elbowing out queries that need just a little temp space. It is not difficult to determine how the database’s temp space is being used; a few queries against the v$ views will give you all the information you need to troubleshoot the problem on your system.

There are two important v$ views for understanding how temporary space is being used in the database. The v$sort_segment view describes general information about each sort segment, such as size and number of blocks currently in use. (This view only provides information about temporary segments located in temporary tablespaces.)

The v$sort_usage view describes how the space within one temporary segment is being used on a session and statement level. Typically, an instance will have only one sort segment per temporary tablespace, but the space within the segment can be used by multiple statements running in different sessions. Note that v$tempseg_usage is a synonym for v$sort_usage.

The following queries can be use to determine temp space usage on the database. Note that minor adaptation will be required for Oracle 8i databases because the dba_tablespaces view does not have a block_size column in Oracle 8i:

--
-- Listing of temp segments.
--
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;

--
-- Temp segment usage per session.
--
SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
         P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         COUNT(*) statements
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,
         P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

--
-- Temp segment usage per statement.
--
SELECT  S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
        T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
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;

Sample output from the queries above is as follows:

--
-- Listing of temp segments.
--
TABLESPACE     MB_TOTAL  MB_USED  MB_FREE
-------------- -------- -------- --------
TEMP              150.0      1.0    149.0

--
-- Temp segment usage per session.
--
SID_SERIAL USERNAME  OSUSER SPID MODULE    PROGRAM          MB_USED TABLESPACE STATEMENTS
---------- --------- ------ ---- --------- ---------------- ------- ---------- ----------
145,1      TEST_USER test   1373 test_prog oracle@test (TNS     1.0 TEMP                1

--
-- Temp segment usage per statement.
--
SID_SERIAL USERNAME  HASH_VALUE SQL_TEXT                                 MB_USED TABLESP
---------- --------- ---------- ---------------------------------------- ------- -------
145,1      TEST_USER 3641290170 SELECT * FROM MYTEST_MESSAGES NM WHERE N     1.0 TEMP
                                M.AWAITING_SENDING = 'y' AND NOT EXISTS
                                ( SELECT 1 FROM MYTEST_MESSAGE_GROUPS NM
                                G WHERE NMG.MESSAGE_GROUP_ID = NM.MESSAG
                                E_GROUP_ID AND NMG.INCOMPLETE = 'y' ) OR
                                DER BY NM.NOTIFY_MESSAGE_ID

As you can see from the sample output, queries against v$sort_segment and v$sort_usage enable the DBA to identify exactly which statements are using temp space, and how much. Often the query that fails with an ORA-01652 error will not be the one consuming excessive amounts of temp space. The queries above will help you find the statements using lots of temp space on your system.

Oracle Security: Passwords

Security should be a priority for every Oracle DBA, right up there with backup and recovery planning, uptime, and performance management. Here’s a web page from Red Database Security that contains a collection of useful information on how Oracle uses passwords. It includes:

  • Information on the algorithms Oracle uses for password management and some of their pitfalls.
  • A listing of places where Oracle passwords might accidentally show up in clear text which may assist a hacker.
  • A technique for changing a user’s password temporarily so that a DBA can log into an account without knowing the user’s password.
  • A survey of some Oracle password cracking programs, many of which are free.

A RAC Survival Kit

Whether you are looking into Real Application Clusters for the first time or have a production RAC environment, you might want to know about Alejandro Vargas’ RAC Survival Kit: Troubleshooting and Information. An Oracle employee, Vargas uses his blog to walk you through various scenarios and points out a number of references relating to problems you may encounter with RAC. He breaks it down into categories like configuration, internal errors, backup and recovery, performance issues, and hangs. It helps to have an Oracle Metalink account to view some of Vargas’ references.

You can find Vargas’ RAC survival kit at http://blogs.oracle.com/AlejandroVargas/stories/storyReader$15. On this page you’ll find quick links to the RAC certification matrix, listings of patch requirements for RAC, RAC setup steps, material about RAC concepts and design, and much more.

The Sys Admin Perspective on Oracle

The Sys Admin Journal is CMP Media’s journal for Unix and Linux systems administrators, and the publication occasionally includes articles relating to Oracle technology. Most recently, there was an article by Mark Bole entitled: A Unix Perspective on Oracle Archive Redo Log Files. “One specific feature of Oracle has been in place from the beginning—the archived redo log file, which is arguably the single most important component of a production application for the SA and DBA to jointly manage and protect,” writes Bole, who reviews archived redo log files from an SA perspective. Bole notes that the lines between the role of the systems administrator and the DBA continue to blur, and he includes a checklist for these two roles to review together to improve support. You can read this article at http://www.samag.com/documents/s=9797/sam0507a/0507a.htm.

Next on the list of helpful articles in the Sys Admin Journal is John Ouellette’s article: Restoring Your Confidence in Oracle Backups. “Wouldn’t it be great to be able to rely on your backups and have full confidence that they will work properly during a disaster or a routine restore operation? Unfortunately, making assumptions about your restores can lead to delays in projects, wasted time, and ultimately data loss.”

Ouellette gives some great examples on “The Power of Testing” and more at http://www.samag.com/documents/s=10067/sam0605a/0605a.htm

 

Leave a Reply

Your email address will not be published. Required fields are marked *