Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

109

积分

0

好友

9

主题
1#
发表于 2012-4-15 21:08:23 | 查看: 11459| 回复: 16
现有一个数据库,建库时临时表空间(数据库默认临时表空间)为100多M,用了一段时间以后,空间自动扩展为4G,显示利用率为99%,重启后仍然如此,请问通过哪些步骤确认这一临时表空间的使用情况是否正确,如果增长过快,通过什么 方面可以解决?
2#
发表于 2012-4-15 21:15:47
运行以下脚本 并贴出输出内容:
  1. REM tablespace report

  2. set linesize 200

  3. select a.tablespace_name,
  4.        round(a.bytes_alloc / 1024 / 1024) megs_alloc,
  5.        round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
  6.        round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
  7.        round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
  8.        100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
  9.        round(maxbytes / 1048576) Max
  10.   from (select f.tablespace_name,
  11.                sum(f.bytes) bytes_alloc,
  12.                sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
  13.           from dba_data_files f
  14.          group by tablespace_name) a,
  15.        (select f.tablespace_name, sum(f.bytes) bytes_free
  16.           from dba_free_space f
  17.          group by tablespace_name) b
  18. where a.tablespace_name = b.tablespace_name(+)
  19. union all
  20. select h.tablespace_name,
  21.        round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
  22.        round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  23.              1048576) megs_free,
  24.        round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
  25.        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  26.              sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
  27.        100 -
  28.        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  29.              sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
  30.        round(sum(f.maxbytes) / 1048576) max
  31.   from sys.v_$TEMP_SPACE_HEADER h,
  32.        sys.v_$Temp_extent_pool  p,
  33.        dba_temp_files           f
  34. where p.file_id(+) = h.file_id
  35.    and p.tablespace_name(+) = h.tablespace_name
  36.    and f.file_id = h.file_id
  37.    and f.tablespace_name = h.tablespace_name
  38. group by h.tablespace_name
  39. ORDER BY 1
  40. /

  41.     SELECT d.tablespace_name "Name",
  42.                 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
  43.                 TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
  44.                 TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
  45.                 TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
  46.                 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
  47.            FROM sys.dba_tablespaces d,
  48.                 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
  49.                 (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
  50.           WHERE d.tablespace_name = a.tablespace_name(+)
  51.             AND d.tablespace_name = t.tablespace_name(+)
  52.             AND d.extent_management like 'LOCAL'
  53.             AND d.contents like 'TEMPORARY'
  54. /
复制代码

回复 只看该作者 道具 举报

3#
发表于 2012-4-15 21:42:46
好的,多谢,等我有机会登录到那个数据库收集了日志,再请教!

回复 只看该作者 道具 举报

4#
发表于 2012-4-16 11:11:36
另外,请问第一段查询语句能不能用在其他的表空间上?能不能大致告诉我一下,两段查询语句的作用,

回复 只看该作者 道具 举报

5#
发表于 2012-4-16 12:14:41
"第一段查询语句能不能用在其他的表空间上?"

可以

两段查询语句的作用,

生成 详细的 表空间 报告

回复 只看该作者 道具 举报

6#
发表于 2012-4-18 20:23:15
那如果这两个语句用在其他的表空间上,是不是需要修改某些字段?
要改  AND d.contents like 'TEMPORARY' 么?

回复 只看该作者 道具 举报

7#
发表于 2012-4-18 21:13:46
不需要 , 建议你实际测试一下 会了解地更清楚一些

回复 只看该作者 道具 举报

8#
发表于 2012-4-18 22:22:43
好的,非常感谢maclean!

回复 只看该作者 道具 举报

9#
发表于 2012-4-19 09:45:53
今天在我自己的测试数据库中运行了一下,第一段语句运行结果显示
TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SYSAUX                                  250            3          247            1           99        32768
SYSTEM                                  480            7          473             1         99         32768   
TEMP                                          24             24            0            100            0      32768
TESTTABSPA                            200           8         192           4          96         200
UNDOTBS1                               645         636           9          99           1       32768
USERS                                    5           5           0          91           9       32768

6 rows selected.

说明这一段查看是针对数据库中所有表空间的,而第二段查询的结果如下


Name                           Size (M)        HWM (M)       HWM %
------------------------------ --------------- ------------- -------
Using (M)     Using %
------------- -------
TEMP                                    24.000        23.000   95.83
.000    0.00

说明这个只是针对临时表空间的,要想让第二段查询也针对所有表空间,该怎么改呢?
另外,有没有关于 MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX,HWM这些概念的详细介绍?

回复 只看该作者 道具 举报

10#
发表于 2012-4-19 12:59:41
maclean你好,今天在业务环境中运行了一下,
第一段SQL语句运行结果如下

TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX

------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
GKDB                                                      5300              70          5230           1          99         32768
GKNEWDB                                           5000            3638        1362          73          27        5000
SYSAUX                                                  500                40            460             8           92        32768  
SYSTEM                                                  600               75              525           12         88        32768
TEMP                                                       3790            3731          59          98           2           32768  
UNDOTBS1                                           780              765           15           98              2          32768     
USERS                                                       18                 12              5             70             30         32768

第二段语句运行结果
Name                           Size (M)        HWM (M)       HWM %
------------------------------ --------------- ------------- -------
Using (M)     Using %
------------- -------
TEMP                                 3,790.000      3789.000   99.97
       58.000    1.53
以上运行结果说明了什么问题?temp表空间和GKDB表空间使用是否正常?

[ 本帖最后由 chunchun2012 于 2012-4-19 13:04 编辑 ]

回复 只看该作者 道具 举报

11#
发表于 2012-4-19 13:00:33
第二段语句本身 就是专门针对 TEMPORARY tablespace 的 。

MEGS_ALLOC   ==》表空间已分配的空间大小 MB数
MEGS_FREE     ==》 表空间上空闲的空间大小 MB数
MEGS_USED    ==》 表空间上已使用的空间大小 MB数
PCT_FREE       ==》 表空间的空闲空间比例
PCT_USED      ==》 表空间上已使用空间的比例 
MAX     ==>   表空间目前情况下允许扩展到的 最大尺寸


HWM (M)   ==》 临时表空间Temporary tablespace被使用到过的高水位

回复 只看该作者 道具 举报

12#
发表于 2012-4-19 13:19:02
maclean你好,今天在业务环境中运行了一下,
第一段SQL语句运行结果如下

TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX

------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
GKDB                                                      5300              70          5230           1          99         32768
GKNEWDB                                           5000            3638        1362          73          27        5000
SYSAUX                                                  500                40            460             8           92        32768  
SYSTEM                                                  600               75              525           12         88        32768
TEMP                                                       3790            3731          59          98           2           32768  
UNDOTBS1                                           780              765           15           98              2          32768     
USERS                                                       18                 12              5             70             30         32768

第二段语句运行结果
Name                           Size (M)        HWM (M)       HWM %
------------------------------ --------------- ------------- -------
Using (M)     Using %
------------- -------
TEMP                                 3,790.000      3789.000   99.97
       58.000    1.53
以上运行结果说明了什么问题?temp表空间和GKDB表空间使用是否正常?

回复 只看该作者 道具 举报

13#
发表于 2012-4-23 15:22:36
我们这里的临时表空间有更严重的问题,请mac老大给看看。
SQL> select t.TABLESPACE_NAME, t.RELATIVE_FNO, (t.BYTES_USED+t.BYTES_FREE)/(1024*1024) total,t.BYTES_FREE/(1024*1024) free from V$TEMP_SPACE_HEADER T ;

TABLESPACE_NAME                RELATIVE_FNO      TOTAL       FREE               
------------------------------ ------------ ---------- ----------               
TEMP                                      3 32767.9844          0               
TEMP                                      4 32767.9844          0               
TEMP                                      5 32767.9844          0               
TEMP                                      2 32767.9844          0               

SQL> SELECT t.TABLESPACE_NAME name,t.FILE_ID, t.BYTES_CACHED/(1024*1024) cached,t.BYTES_USED/(1024*1024) used from V$TEMP_EXTENT_POOL t ;

NAME                              FILE_ID     CACHED       USED                 
------------------------------ ---------- ---------- ----------                 
TEMP                                    2          0          0                 
TEMP                                    3      32767          3                 
TEMP                                    4      32767          0                 
TEMP                                    5      32767          2                 

SQL> select * from v$version;

BANNER                                                                          
----------------------------------------------------------------               
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production               
PL/SQL Release 9.2.0.8.0 - Production                                          
CORE        9.2.0.8.0        Production                                                      
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production               
NLSRTL Version 9.2.0.8.0 - Production   
针对file_id=2的文件,
V$TEMP_SPACE_HEADER 中BYTES_FREE=0 可是V$TEMP_EXTENT_POOL 中BYTES_CACHED=0 。
我在resize时提示“ora-03297”;
mac老大给解释下这两个值的区别呗!

回复 只看该作者 道具 举报

14#
发表于 2012-4-23 15:24:03
因为涉及公司业务系统,所以没执行mac老大脚本中其它表空间的信息。若是确实需要的话,我以别的方式提供。

回复 只看该作者 道具 举报

15#
发表于 2012-4-23 18:50:56

回复 14# 的帖子

请给出以上2个脚本的输出

对于TEMPORARY TABLESPACE 其实可以不必要去resize, 完全可以重建一个新的 TEMP TABLESPACE,并 切换到该新的TEMP Tablespace上;  原temp tbs可以drop掉, 也可以在下一次startup时再drop。

回复 只看该作者 道具 举报

16#
发表于 2012-4-24 13:50:38
V$TEMPSEG_USAGE  看下什么在用吧

回复 只看该作者 道具 举报

17#
发表于 2012-4-25 10:29:44
按照mac的建议,我把temp重建了。

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-25 00:40 , Processed in 0.052788 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569