通过测试用例了解Oracle Temp表空间具体使用情况

2020-09-10 00:00:00 视图查询执行笛卡尔空间

Oracle Temp表空间具体使用信息分析

1 查询临时表空间大小

SQL> select tablespace_name,file_name,autoextensible,bytes/1024/1024,maxbytes/1024/1024 from dba_temp_files;

TABLESPACE_NAME FILE_NAME AUT BYTES/1024/1024 MAXBYTES/1024/1024

-------------------- ------------------------------------------------------------ --- --------------- ------------------

TEMP +ASMVG1/orcl/tempfile/temp.262.1005600277 YES 20 32767.9844

2.测试环境

sqlplus / as sysdba

create user myoracle identified by oracle;

grant connect,resouce to myoracle;

create table test_objs as select * from dba_objects;

exit

sqlplus myorace/oracle

select a.* ,b.* from test_objs a,test_objs b order by 1,2;

新开一会话

sqlplus / as sysdba

SQL> select username,sql_id,tablespace,segtype,extents from v$tempseg_usage;

SQL> /

USERNAME SQL_ID TABLESPACE SEGTYPE EXTENTS

------------------------------ ------------- ------------------------------- --------- ----------

MYORACLE 0a003svjmcz8t TEMP SORT 50

SQL> /

USERNAME SQL_ID TABLESPACE SEGTYPE EXTENTS

------------------------------ ------------- ------------------------------- --------- ----------

MYORACLE 0a003svjmcz8t TEMP SORT 59

使用关联查询可以确定正在消耗TEMP空间的sql详细信息

SQL> SELECT a.username, a.sid, a.serial#, a.osuser,a.schemaname,a.program,a.type,b.tablespace,b.sql_id, to_char(trunc((b.blocks * d.value) / 1024 /1024)) || ' MB' size

2 , b.segtype , c.sql_text

3 FROM v$session a, v$tempseg_usage b, v$sqlarea c

4 , (select value from v$parameter where name = 'db_block_size') d

5 WHERE a.saddr = b.session_addr

6 AND c.address= a.sql_address

7 AND c.hash_value = a.sql_hash_value

8 ORDER BY b.tablespace, b.blocks;

no rows selected

SQL> /

USERNAME SID SERIAL# OSUSER SCHEMANAME PROGRAM TYPE TABLESPACE SQL_ID SIZ SEGTYPE SQL_TEXT

------------------------------ ---------- ---------- ------------------------------ ------------------------------ ------------------------------------------------ ---------- ------------------------------- ------------- ------------------------------------------- --------- ------------------------------------------------------------

MYORACLE 60 11 oracle MYORACLE sqlplus@rac1 (TNS V1-V3) USER TEMP dyk4dprp70d74 216 MB SORT select a.* ,b.* from test_objs a,test_objs b order by 1,2

SQL> /

USERNAME SID SERIAL# OSUSER SCHEMANAME PROGRAM TYPE TABLESPACE SQL_ID SIZ SEGTYPE SQL_TEXT

MYORACLE 60 11 oracle MYORACLE sqlplus@rac1 (TNS V1-V3) USER TEMP dpgnh97fq0yhu 226 MB SORT select a.* ,b.* from test_objs a,test_objs b order by 1,2

此时从视图v$tempseg_usage;可以清楚看出临时段的使用情况。如果该SQL使用了大量的temp空间必然执行很慢，可以查看其执行计划分析SQL语句和执行计划作出优化处理。

select * from table(dbms_xplan.display_cursor('dyk4dprp70d74'));

select * from table(dbms_xplan.display_cursor('dpgnh97fq0yhu'));

SQL> select * from table(dbms_xplan.display_cursor('dpgnh97fq0yhu'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID dpgnh97fq0yhu, child number 0

-------------------------------------

select a.* ,b.* from test_objs a,test_objs b order by 1,2

Plan hash value: 3670659157

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | | 29M(100) | |

| 1 | SORT ORDER BY | | 241M | 93G | 102G | 29M (1) | 98:51:41 |

| 2 | MERGE JOIN CARTESIAN| | 241M | 93G | | 838K (2) | 02:47:39 |

| 3 | TABLE ACCESS FULL | TEST_OBJS | 15549 | 3143K | | 56 (2) | 00:00:01 |

| 4 | BUFFER SORT | | 15549 | 3143K| | 29M (1)| 98:51:40 |

| 5 | TABLE ACCESS FULL | TEST_OBJS | 15549 | 3143K | | 54 (2) | 00:00:01 |

-------------------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

21 rows selected.

分析执行计划，全表扫描，笛卡尔积，笛卡尔结果集再一次做排序，都及其耗费CPU资源和I/O资源

可以进一步从ASH分析

SQL> select sql_id,SQL_PLAN_LINE_ID,SQL_PLAN_OPERATION,event,count(*) from v$active_session_history where sql_id='dpgnh97fq0yhu' group by sql_id,SQL_PLAN_LINE_ID,SQL_PLAN_OPERATION,event

SQL_ID SQL_PLAN_LINE_ID SQL_PLAN_OPERATION EVENT COUNT(*)

------------- ---------------- ------------------------------ ---------------------------------------- ----------

dpgnh97fq0yhu 4 BUFFER 2

dpgnh97fq0yhu 0 SELECT STATEMENT Disk file operations I/O 1

dpgnh97fq0yhu 0 SELECT STATEMENT control file sequential read 3

dpgnh97fq0yhu 0 SELECT STATEMENT DFS lock handle 19

dpgnh97fq0yhu 0 SELECT STATEMENT enq: CF - contention 3

dpgnh97fq0yhu 1 SORT 16

dpgnh97fq0yhu 0 SELECT STATEMENT 3

7 rows selected.

如果当前没有SQL在使用Temp空间，则如下两个视图没有数据呈现

SQL> select * from v$tempseg_usage;

no rows selected

SQL> select * from v$sort_usage;

no rows selected

一旦TEMP空间被使用，则如下两个查询确定TEMP空间被使用了多少

SQL> select tablespace_name,file_name,autoextensible,bytes/1024/1024,maxbytes/1024/1024 from dba_temp_files

TABLESPACE_NAME FILE_NAME AUT BYTES/1024/1024 MAXBYTES/1024/1024

------------------------------ -------------------------------------------------- --- --------------- ------------------

TEMP +ASMVG1/orcl/tempfile/temp.262.1005600277 YES 407 32767.9844

SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

------------------------------- ------------ ----------- -----------

TEMP 51456 0 51456

SQL> select 51456*8/1024 from dual;

51456*8/1024

------------

402

如果SQL正在使用TEMP空间，则查询v$sort_segment 会显示USED_BLOCKS，此时FREE_BLOCKS

SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

------------------------------- ------------ ----------- -----------

TEMP 56960 56960

一旦占用TEMP表空间的SQL结束，则视图v$sort_segment信息显示如下所示

SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

------------------------------- ------------ ----------- -----------

TEMP 0 62336

此时TOTAL_BLOCKS和FREE_BLOCKS相同，说明此时的TEMP表空间可以继续给其他SQL使用。

通过v$temp_space_header查询临时表空间已经使用过的大空间，这个视图是化的。

SQL> select TABLESPACE_NAME,sum(BYTES_USED)/1024/1024,sum(BYTES_FREE)/1024/1024 from v$temp_space_header group by TABLESPACE_NAME;

TABLESPACE_NAME SUM(BYTES_USED)/1024/1024 SUM(BYTES_FREE)/1024/1024

------------------------------ ------------------------- -------------------------

TEMP 492

可以看到执行计划的步骤1耗时多，这也是该SQL慢的核心原因

一旦SQL语句结束，则该视图不再有数据呈现。

相关文章