Shulou(Shulou.com)06/01 Report--

On the methods of eliminating the surge of temp ts

People often ask the question of the surge of temp tablespaces and how to recycle temporary tablespaces. Due to different versions, there are obviously a variety of methods, but these methods show that they are palliative rather than permanent cures. Only by deeply understanding the reasons for the rapid increase of temp tablespaces can we fundamentally solve the temp ts problem.

What is the operation that uses temp ts?

-Index creation or re-creation.

-ORDER BY or GROUP BY

-DISTINCT operation.

-UNION & INTERSECT & MINUS

-Sort-Merge joins.

-Analyze operation

-some anomalies will cause temp to soar

Therefore, in dealing with the above operations, dba needs to pay more attention to the use of temp, v$sort_segment dictionary can record the more detailed use of temp, and v$sort_usage will tell us who is doing what.

Sql > select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment

TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

TEMP 1 63872 30464 33408

SQL > select username,session_addr,sqladdr,sqlhash from v$sort_usage

USERNAME SESSION_ADDR SQLADDR SQLHASH

CYBERCAFE C0000000D7EF99E8 C0000000E1BFE970 4053158416

Then through the multi-table join, we can find out the more detailed operation:

SQL > select se.username,se.sid,su.extents,su.blocks*to_number (rtrim (p.value)) as Space,tablespace,segtype,sql_text from v$sort_usage su,v$parameter pr é cor session se,v$sql s where p.nameplate block block size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid

USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE

--

SQL_TEXT

CYBERCAFE 42 238 249561088 TEMP SORT

Select 1 from sys.streams$_prepare_ddl p where ((p.global_flag=1 and: 1 is null) or (p.global_flag=0 and p.usrid=:2)) and rownum=1

This example should be caused by some exceptions. In fact, in most cases, the sort will end almost within a few seconds. If the SQL is captured within a few seconds of the sort operation, you should be lucky, that is, you know that a certain SQL will have a sort operation. When you want to capture them, you will find that they have already finished sort. After sorting, the sort segment will be cleared by smon. But a lot of time, we will encounter a situation where the temporary segment is not released and the temp table space is almost full. What should we do?

The recommended methods on metalink are collected as follows

-- restart the instance

When the instance is restarted, the smon process will release the temporary segment. However, in many cases, our library does not allow down.

So there are not many opportunities for this method to be used, but it is still very useful if your instance is restarted in the sort segment.

If it is not released, this situation needs to be treated with caution.

-- modify parameters (for 8i and below only)

SQL > alter tablespace temp increase 1

SQL > alter tablespace temp increase 0

-- merge fragments

SQL > alter tablespace temp coalesce

-- diagnosing events

SQL > alter session set events' immediate trace name DROP_SEGMENTS level 4'

Note: the TS# of temp tablespace is 3pm so TS#+1=4.

-- rebuilding temp

SQL > alter database tempfile'.' Drop

SQL > alter tablespace temp add tempfile'.'

It can be said that the above methods address the symptoms rather than the root causes, because the rapid growth of temp is obviously due to too much disk sort, and there are many reasons for disk sort, such as a small sort area. Of course, how big is the sort area setting? Of course, this needs to satisfy that the In-memory Sort is greater than 99%.

Instance Efficiency Percentages (Target 100%)

~

Buffer Nowait%: 100.00 Redo NoWait%: 99.99

Buffer Hit%: 99.36 In-memory Sort%: 100.00

Library Hit%: 99.87 Soft Parse%: 99.84

Execute to Parse%: 1.17 Latch Hit%: 99.96

Parse CPU to Parse Elapsd%: 92.00% Non-Parse CPU: 94.59

Allocation of sorted areas

-dedicated server assigns sort area.

The sorting area is in PGA.

-the shared server allocates sort area.

The sorting area is in UGA. UGA is allocated in shared pool.

In the previous version of 9i, sort_area_size determines the allocation of sortarea. In 9i and later versions, when workarea_size_policy and other auto, the pga_aggregate_target parameter determines that the sortarea is greater than, then the sortarea should be 5% of the total memory of pga. When workarea_size_policy and other manual, the size of the sort area is still determined by the sort_area_size.

No matter which version, if the sort area is too small and the In-memory Sort rate is low, then the temp tablespace will certainly grow very fast, and if it is opened higher, it will lead to serious memory consumption (more long connections) in the Cramp S structure. Since the smon process reclaims sort segment that is no longer in use every 5 minutes, if you do not want smon to recycle sort segment, you can write the initialization parameter file using the following two event, and then

Restart the instance so that if you have more disk ordering, it will quickly burst the disk.

Event= "10061 trace name context forever, level 10" / / No extra charge

Event= "10269 trace name context forever, level 10" / / prohibits merging of fragments

Most of the dist sort can be eliminated by setting pga or sort_area_size properly, but what about other disk sort? From the cause of sort, the disk sort caused by index / analysis / exception should be a small part, and the others should be distinct/union/group by/order by and merge sort join in select, so how do we capture these operations? Usually how to have disk sorting SQL, its logical read / physical read / sort / execution time is relatively large, so we can filter the v$sqlarea or v$sql dictionary, after long-term monitoring of the database, I believe we can find out these black sheep. So what happens when you find out the SQL that caused disk sort? Of course, it is to analyze the SQL and optimize it.

[oracle@www1 sql] $more show_sql.sh

#! / bin/bash

Sqlplus-s aaa/bbbcol sql_text format A81

Col disk_reads format 999999.99

Col bgets_per format 99999999.99

Col "ELAPSD_TIME (s)" format 9999.99

Col "cpu_time (s)" format 9999.99

Set long 99999999999

Set pagesize 9999

Select address,hash_value,disk_reads/executions disk_reads,elapsed_time/1000000/executions as "ELAPSD_TIME (s)"

Buffer_gets/executions bgets_per,executions,first_load_time as first_time,sql_text

From v$sql

Where executions > 0 and (disk_reads/executions > 500 or buffer_gets/executions > 20000) and command_type = 3

Order by 3,4

-- select s. Diskcharts readsreachs.bufferparts getspact pact s.executions bgets_per,first_load_time,st.sql_text

-- from v$sql sdirection vs. sqltextbook withdrawing newlines st

-- where s.address=st.address and s.hash_value=st.hash_value

-- and s.disk_reads > 1000 or (s.executions > 0 and s.buffer_gets/s.executions > 50000)

-- order by st.piece

Exit

!

In conclusion, how to fundamentally reduce the expansion of temp tablespaces? There are two methods:

1 set a reasonable pga or sort_area_size

2 sql caused by optimization of disk sort

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.