LoginSignup
23
28

More than 5 years have passed since last update.

@na0AaooQ(青木 直之)in株式会社LIFULL

Oracle 11g 各表領域(テーブルスペース)の使用サイズと空き容量 + TEMP表領域のディスクソート回数を表示するスクリプト

Last updated at Posted at 2015-01-23

はじめに

Oracle 11gの表領域(テーブルスペース)一覧の使用サイズや空き容量 + TEMP表領域のディスクソート発生回数を表示するスクリプトについて記載致します。

概要

Linuxサーバ上に構築したOracle 11gサーバ(11.1.0.7.2)の運用管理をしております。24時間365日稼動し続けるサービスの本番環境Oracleデータベースサーバを運用している為、データベースの負荷、セッション情報、ロックやラッチの発生状況、SQLコストの変化など様々な情報を確認しております。

Oracleサーバのリソース使用状況確認の一部として、Oracleサーバ上に格納している各表領域の使用サイズや空き容量、TEMP表領域のディスクソート発生回数を確認したいという事があるかと思います。

後述のOracle表領域一覧と使用サイズ + TEMP表領域のディスクソート発生回数を表示するスクリプトを作成し、定期的にスクリプトを実行して各表領域の使用サイズをグラフ化したり、手動でスクリプトを実行してOracle 11gサーバの状態を確認しております。

Oracle Enterprise Managerでも各表領域の使用サイズや空き容量は確認出来ますが、確認方法の一つとして、ご参考になりましたら幸いです。

スクリプトを実行している環境

・Oracle 11g (11.1.0.7.2)

Oracle表領域一覧と使用サイズ + TEMP表領域のディスクソート発生回数を表示するスクリプト

Oracle 11gサーバの任意のディレクトリに、以下のようなスクリプトを作成します。
変数の値はサンプル用に修正しております。

[oracle@example-oracle-11g-active ~]$ vi show_oracle_tablespace.sh
show_oracle_tablespace.sh
#!/bin/sh

export NLS_DATE_FORMAT="YYYY/MM/DD HH24:MI:SS"

SQL_TEXT="/任意のディレクトリ/show_oracle_tablespace.sql"

if [ -r "${SQL_TEXT}" ] && [ -f "sqlplus" ] ; then

        sqlplus / as sysdba @${SQL_TEXT}

fi

スクリプトに実行権限を設定します。

[oracle@example-oracle-11g-active ~]$ chmod 755 show_oracle_tablespace.sh

スクリプト内で実行するSQLファイルを作成します。
SQLファイルにはchmod 754のような実行権限付与は不要です。

[oracle@example-oracle-11g-active ~]$ vi /任意のディレクトリ/show_oracle_tablespace.sql
show_oracle_tablespace.sql
set echo on
set colsep |
set pages 1000
set lines 1000

column TEMP_SIZE(MB)    format a15
column TEMP_HWM(MB)     format a15
column TEMP_HWM(%)      format a15
column TEMP_USED(MB)    format a15
column TEMP_USING(%)    format a15

----- TEMP TABLESPACE USED
SELECT
       d.tablespace_name,
       round(現サイズ) "SIZE(MB)",
       round(最大使用量) "USED(MB)",
       round(((最大使用量/現サイズ))*100) "USE(%)",
       round(現サイズ-最大使用量) "AVAIL(MB)"
FROM
       (SELECT tablespace_name, SUM(bytes)/(1024*1024) "現サイズ" FROM dba_temp_files GROUP BY tablespace_name) d,
       (SELECT tablespace_name, SUM(bytes_used)/(1024*1024) "最大使用量" FROM v$temp_extent_pool GROUP BY tablespace_name) f,
       dba_tablespaces t
WHERE
       (d.tablespace_name = f.tablespace_name) AND (d.tablespace_name = t.tablespace_name )
ORDER BY
       d.tablespace_name
;

----- TEMP TABLESPACE HWM
SELECT
       d.tablespace_name,
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "TEMP_SIZE(MB)",
       TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "TEMP_HWM(MB)",
       TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "TEMP_HWM(%)" ,
       TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "TEMP_USED(MB)",
       TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "TEMP_USING(%)"
FROM
       sys.dba_tablespaces d,
       (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
       (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = t.tablespace_name(+)
       AND d.extent_management like 'LOCAL'
       AND d.contents like 'TEMPORARY'
;

----- DISK SORT INCIDENCE
col "SORT_INCIDENCE(%)" format 990.99
SELECT
        tablespace_name, ( used_blocks / total_blocks ) * 100 "SORT_INCIDENCE(%)"
FROM
        v$sort_segment
WHERE
        tablespace_name = 'TEMP' OR tablespace_name = 'TEMP_EXAMPLE1' OR tablespace_name = 'TEMP_EXAMPLE2'
ORDER BY
        tablespace_name
;

----- DISK SORT COUNT
SELECT
        name, value "SORT_FREQUENCY"
FROM
        v$sysstat
WHERE
        name like '%sort%'
ORDER BY
        name
;

----- TABLESPACE USED
SELECT
        d.tablespace_name,
        現サイズ "SIZE(MB)",
        round(現サイズ-空き容量) "USED(MB)",
        round((1 - (空き容量/現サイズ))*100) "USE(%)",
        空き容量 "AVAIL(MB)"
FROM
        (SELECT tablespace_name, round(SUM(bytes)/(1024*1024)) "現サイズ" FROM dba_data_files GROUP BY tablespace_name) d,
        (SELECT tablespace_name, round(SUM(bytes)/(1024*1024)) "空き容量" FROM dba_free_space GROUP BY tablespace_name) f
WHERE
        d.tablespace_name = f.tablespace_name
ORDER BY
        d.tablespace_name
;

exit;

SQLファイルのパーミッションには実行権限は不要なので、パーミッションを644にしておきます。

[oracle@example-oracle-11g-active ~]$ chmod 644 /任意のディレクトリ/show_oracle_tablespace.sql

Oracle 11gサーバでのスクリプト実行例

Oralce 11gサーバにoracleユーザでログインして、先ほど作成したスクリプトを実行します。以下のようにOracleサーバに格納している表領域一覧や使用サイズ、TEMP表領域のディスクソート発生回数を表示出来ます。

なお、以下に記載しているスクリプト実行結果の表領域名やサイズ等はサンプル用に修正しております。

[oracle@example-oracle-11g-active ~]$ ./show_oracle_tablespace.sh
SQL> set colsep |
SQL> set pages 1000
SQL> set lines 1000
SQL>
SQL> column TEMP_SIZE(MB)       format a15
SQL> column TEMP_HWM(MB)        format a15
SQL> column TEMP_HWM(%) format a15
SQL> column TEMP_USED(MB)       format a15
SQL> column TEMP_USING(%)       format a15
SQL>
SQL> ----- TEMP TABLESPACE USED
SQL> SELECT
  2         d.tablespace_name,
  3         round(現サイズ) "SIZE(MB)",
  4         round(最大使用量) "USED(MB)",
  5         round(((最大使用量/現サイズ))*100) "USE(%)",
  6         round(現サイズ-最大使用量) "AVAIL(MB)"
  7  FROM
  8         (SELECT tablespace_name, SUM(bytes)/(1024*1024) "現サイズ" FROM dba_temp_files GROUP BY tablespace_name) d,
  9         (SELECT tablespace_name, SUM(bytes_used)/(1024*1024) "最大使用量" FROM v$temp_extent_pool GROUP BY tablespace_name) f,
 10         dba_tablespaces t
 11  WHERE
 12         (d.tablespace_name = f.tablespace_name) AND (d.tablespace_name = t.tablespace_name )
 13  ORDER BY
 14         d.tablespace_name
 15  ;

TABLESPACE_NAME               |  SIZE(MB)|  USED(MB)|    USE(%)| AVAIL(MB)
------------------------------|----------|----------|----------|----------
TEMP                          |      5040|         0|         0|      5040
TEMP_EXAMPLE1                 |     12000|         0|         0|     12000
TEMP_EXAMPLE2                 |       200|         0|         0|       200

SQL>
SQL> ----- TEMP TABLESPACE HWM
SQL> SELECT
  2         d.tablespace_name,
  3         TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "TEMP_SIZE(MB)",
  4         TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "TEMP_HWM(MB)",
  5         TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "TEMP_HWM(%)" ,
  6         TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "TEMP_USED(MB)",
  7         TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "TEMP_USING(%)"
  8  FROM
  9         sys.dba_tablespaces d,
 10         (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
 11         (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
 12  WHERE d.tablespace_name = a.tablespace_name(+)
 13         AND d.tablespace_name = t.tablespace_name(+)
 14         AND d.extent_management like 'LOCAL'
 15         AND d.contents like 'TEMPORARY'
 16  ;

TABLESPACE_NAME               |TEMP_SIZE(MB)  |TEMP_HWM(MB)   |TEMP_HWM(%)    |TEMP_USED(MB)  |TEMP_USING(%)
------------------------------|---------------|---------------|---------------|---------------|---------------
TEMP                          |      5,040.000|     2519.000  |  49.98        |         .000  |   0.00
TEMP_EXAMPLE1                 |     12,000.000|    11931.000  |  99.43        |         .000  |   0.00
TEMP_EXAMPLE2                 |        200.000|       72.000  |  36.00        |         .000  |   0.00

SQL>
SQL> ----- DISK SORT INCIDENCE
SQL> col "SORT_INCIDENCE(%)" format 990.99
SQL> SELECT
  2          tablespace_name, ( used_blocks / total_blocks ) * 100 "SORT_INCIDENCE(%)"
  3  FROM
  4          v$sort_segment
  5  WHERE
  6          tablespace_name = 'TEMP' OR tablespace_name = 'TEMP_EXAMPLE1' OR tablespace_name = 'TEMP_EXAMPLE2'
  7  ORDER BY
  8          tablespace_name
  9  ;

TABLESPACE_NAME                |SORT_INCIDENCE(%)
-------------------------------|-----------------
TEMP                           |             0.00
TEMP_EXAMPLE1                  |             0.00
TEMP_EXAMPLE2                  |             0.00

SQL>
SQL> ----- DISK SORT COUNT
SQL> SELECT
  2          name, value "SORT_FREQUENCY"
  3  FROM
  4          v$sysstat
  5  WHERE
  6          name like '%sort%'
  7  ORDER BY
  8          name
  9  ;

NAME                                                            |SORT_FREQUENCY
----------------------------------------------------------------|--------------
sorts (disk)                                                    |           190
sorts (memory)                                                  |    1881695505
sorts (rows)                                                    |    1.5694E+11

SQL>
SQL> ----- TABLESPACE USED
SQL> SELECT
  2          d.tablespace_name,
  3          現サイズ "SIZE(MB)",
  4          round(現サイズ-空き容量) "USED(MB)",
  5          round((1 - (空き容量/現サイズ))*100) "USE(%)",
  6          空き容量 "AVAIL(MB)"
  7  FROM
  8          (SELECT tablespace_name, round(SUM(bytes)/(1024*1024)) "現サイズ" FROM dba_data_files GROUP BY tablespace_name) d,
  9          (SELECT tablespace_name, round(SUM(bytes)/(1024*1024)) "空き容量" FROM dba_free_space GROUP BY tablespace_name) f
 10  WHERE
 11          d.tablespace_name = f.tablespace_name
 12  ORDER BY
 13          d.tablespace_name
 14  ;

TABLESPACE_NAME               |  SIZE(MB)|  USED(MB)|    USE(%)| AVAIL(MB)
------------------------------|----------|----------|----------|----------
INDX_EXAMPLE1                 |     15900|      9800|        62|      6100
INDX_EXAMPLE2                 |      3600|      2700|        75|       900
　　(省略)
SYSAUX                        |      2048|      1677|        82|       371
SYSTEM                        |       500|       459|        92|        41
TOOLS                         |       500|       154|        31|       346
TS_EXAMPLE1                   |     12000|      8200|        68|      3800
TS_EXAMPLE1                   |      3600|      2700|        75|       900
　　(省略)
UNDOTBS2                      |     46820|     11305|        24|     35515
USERS                         |        10|         1|        10|         9

XXX rows selected.

SQL>
SQL> exit;
[oracle@example-oracle-11g-active ~]$

Oracleデータベースの運用管理をしていく上で、様々な情報を確認する必要があるかと思いますが、各表領域のデータ使用量、ディスクソートのようなメモリに収まりきらずディスクI/Oを伴う処理が発生していないかどうかも確認するようにしておくと良いかと思います。ご参考になりましたら幸いです。

以上になります。

23
28
4

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
23
28