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

The purpose of Oracle temporary tablespace is to store some temporary buffer data and sort the intermediate results of the query.

Temporary tablespace is that with the release of business, the system will automatically clean up temporary objects in the temporary tablespace and automatically release temporary segments. The release here is only marked as idle and can be reused, but the actual disk space is not really released. This is why temporary tablespaces sometimes grow.

The following actions take up temporary table space

Create, rebuild, union, insert (append), minus, Order by, group by, Distinc, analyze, Sort-merge joins, Hash join

The wait events associated with temporary tablespaces are as follows:

Directpath write temp

Direct path read temp

Enq:TS-contention

1. Use temporary tablespaces:

1. When querying, too many tables are used in the join table query, and there are the following join methods: Sort-mergejoins and Hash join.

There are minus, Order by, group by and Distinc in the 2.DML statement, which has a large amount of query data.

3. Some fields of the query are not indexed, and oracle copies all the data to the temporary tablespace

Second, the processing method of the database performance affected by the temporary table space

1. Positioning problem

View temporary tablespace usage

Selectt1. "Tablespace"Tablespace"

T1. "Total (G)"Total (G)"

Nvl (T2. "Used (G)", 0) "Used (G)"

T1. "Total (G)"-nvl (T2. "Used (G)", 0) "Free (G)"

From (selecttablespace_name "Tablespace"

To_char ((sum / 1024 / 1024 / 1024)), '99999990.900') "Total (G)"

Fromdba_temp_files group by tablespace_name

Union

Selecttablespace_name "Tablespace"

To_char ((sum / 1024 / 1024 / 1024)), '99999990.900') "Total (G)"

Fromdba_data_files

Wheretablespace_name like 'TEMP%'

Group by tablespace_name) T1

(selecttablespace, round (sum (blocks) * 8 / 1024) "Used (G)" from

V$sort_usage group by tablespace) T2

Wheret1. "Tablespace" = t2.tablespace (+)

Find out the statements that consume sql

Select sql_id,count (*) from v$session whereevent like 'direct path% temp' group by sql_id desc

You can use the following sql statement to locate the statement that consumes temporary table space for a certain period of time:

Select sql_id, count (*) fromdba_hist_active_sess_historywhere sample_time > = to_timestamp ('2017-8-01 16 fromdba_hist_active_sess_historywhere sample_time 0015,' yyyy-mm-ddhh34:mi:ss') and sample_time selectts#, name from sys.ts$

TS# NAME

-

0 SYSTEM

1 UNDOTBS1

2 SYSAUX

3 TEMP

4 USERS

5 UNDOTBS2

2. Perform cleanup operation

SQL > altersession set events' immediate trace name DROP_SEGMENTS level 4'

Description: TS# of temp tablespace is 3, So TS#+ 1 = 4.

Third, the method of optimizing temporary tablespace

Business layer optimization:

1. Optimized statement

Such as group by and order by fields

Eliminate the heavy use of temporary tablespaces by indexing these fields.

two。 Rewrite poor sql statements.

Database layer optimization

1. Consider using temporary tablespace groups

Benefits of using temporary tablespace groups:

Prevent a temporary tablespace from running out of space.

Different temporary table spaces can be used when the same user connects multiple session at the same time.

Multiple temporary tablespaces can be used in parallel operations

two。 Set reasonable PGA to reduce the use of temporary tablespaces

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.