Oracle Temporary Tablespace SQLステートメントの実装

2022-01-18 08:52:32

テンポラリーテーブルスペースの概念

一時表領域は、データベースのソート操作を管理し、一時表、中間ソート結果およびその他の一時オブジェクトを格納するために、ORACLEがSORTを使用する必要があるときに、PGA sort_area_sizeサイズが十分ではない場合、データはソートのための一時表領域に配置されます使用します。データベースのいくつかの操作のように。CREATE INDEX、ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、UNION ALL、INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN、など、データベースのいくつかの操作のように、一時テーブル空間を使用することがあります。操作が完了すると、システムは自動的に一時テーブルスペース内の一時オブジェクトをクリーンアップし、一時セグメントを自動的に解放します。ここでの解放は、無料で再利用可能と表示されるだけで、実際には実質的に占有しているディスクスペースは解放されていない。これが、テンポラリ表領域が増え続けることがある理由です。

一時テーブルスペースには、大規模ソート（小規模ソートはRAMで直接行い、大規模ソートはディスクソートが必要）の中間結果やハッシュが格納される。永続的なデータファイルではなく、一時的なファイルで構成されている点が永続的な表領域と異なる。一時表領域は永続的なタイプのオブジェクトを保存しないので、バックアップされないし、する必要もない。さらに、一時ファイルに対する操作では、アンドゥのログは生成されますが、レドゥのログは生成されません。

一時表領域を作成したり、一時表領域に一時データファイルを追加したりする場合、一時データファイルが大きくても、その処理はかなり高速に行われます。これは、ORACLEの一時データファイルが特別なクラスのデータファイルであるためです。スパースファイルは、一時表領域ファイルが作成されるときに、ファイルのヘッダーと最後のブロックにのみ書き込まれます。そのスペースは、遅延ベースで割り当てられます。これが、一時表領域を作成したり、一時表領域にデータファイルを追加するのが非常に高速である理由です。

また、一時表領域はNOLOGGINGモードであり、永久型付けされたオブジェクトを保持しないため、データベースが破損した場合でも、リカバリを行うことで一時表領域が不要になります。

Oracleデータベースの一時テーブルスペースに関連するSQL文は、以下のようにまとめられています。

Oracle一時テーブルスペースは、データファイルを作成し、追加します。

--create temporary tablespace tempdata
create temporary tablespace tempdata tempfile '/oradata/orcl/tempdata01.dbf' size 30g autoextend off;
--add temporary tablespace data file
alter tablespace tempdata add tempfile '/oradata/orcl/tempdata02.dbf' size 30g autoextend off;
--delete the temporary tablespace data file
alter tablespace tempdata drop tempfile '/oradata/orcl/tempdata02.dbf' including datafiles;
--resize temp tablespace datafiles
alter database tempfile '/oradata/orcl/tempdata01.dbf' resize 2G;
--set auto-scaling
alter database tempfile '/oradata/orcl/tempdata01.dbf' autoextend on;
-- switch default temporary tablespace
alter database default temporary tablespace tempdata;
--delete the temporary tablespace
drop tablespace temp including contents and datafiles cascade constraints;
-- shrink the temporary tablespace
alter tablespace temp shrink space keep 8G;
alter tablespace temp shrink tempfile '/oradata/orcl/tempdata01.dbf';

現在のデフォルトのテンポラリーテーブルスペースを表示する。

SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

一時テーブルスペースの使用率を照会します。

select df.tablespace_name "Tablespace",
       df.totalspace "Total(MB)",
       nvl(FS.UsedSpace, 0) "Used(MB)",
       (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
       round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
        FROM dba_TEMP_files
        GROUP BY tablespace_name) df,
       (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace
        FROM gV$temp_extent_pool
        GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+)

一時テーブルスペースに対応する一時ファイルの使用量を表示する。

SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,
    BYTES_USED/1024/1024/1024 AS TABLESAPCE_USED ,
    BYTES_FREE/1024/1024/1024 AS TABLESAPCE_FREE
FROM V$TEMP_SPACE_HEADER
ORDER BY 1 DESC;

を使用して、ライブテンポラリー表領域のsql_idとsidを照会します。

set linesize 260 pagesize 1000
col machine for a40
col program for a40
SELECT se.username,
       sid,
       serial#,
       se.sql_id
       machine,
       program,
       tablespace,
       segtype,
       (su.BLOCKS*8/1024/1024) GB
  FROM v$session se, v$sort_usage su
 WHERE se.saddr = su.session_addr
 order by su.BLOCKS desc;

 
/* Note that the sql_id query here should use the sql_id of the v$session view, not the sql_id of the v$sort_usage view, which is inaccurate */

使用したSQL_IDのテンポラリテーブルスペースの履歴を照会します。

select a.SQL_ID,
       a.SAMPLE_TIME,
       a.program,
       sum(trunc(a.TEMP_SPACE_ALLOCATED / 1024 / 1024)) MB
  from v$active_session_history a
 where TEMP_SPACE_ALLOCATED is not null 
 and sample_time between
 to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
 to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
 group by a.sql_id,a.SAMPLE_TIME,a.PROGRAM
 order by 2 asc,4 desc;

Oracleの一時的なテーブルスペースのSQL文の実装に関するこの記事は、これに導入され、より関連するOracleの一時的なテーブルスペースの文の内容は、スクリプトの家の前の記事を検索してくださいまたは次の関連記事を閲覧し続けるあなたは、将来的に多くのスクリプト家をサポートします願っています！この記事では、Oracleの一時的なテーブルスペースの文の実装は、このように導入されています。