eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« DBA手记:Failed Login Count带来的性能问题 | Blog首页 | Oracle Linux 6 使用 Ext4的性能体验 »

DBA手记:OEM罪几何?-空间监控的性能问题

在某金融行业用户的ERP数据库中,一个小时的采样报告,位于Elapsed Time消耗排行第二位的SQL消耗了19.41%DB Time,该SQL同样是OEM发出来的,其SQL ModuleOracle Enterprise Manager.Metric Engine,这个SQL每次执行需要245.77秒的时间,是极其缓慢的,数据库环境是Oracle Database 10g 10.2.0.4版本:

dbanb206.png

SQL的文本内容是:

insert into mgmt_db_size_gtt

  select tablespace_name, NVL(sum(bytes) / 1048576, 0) sz

    from sys.dba_free_space

   group by tablespace_name

这显然是通过dba_free_space来计算各表空间的Free空间,这个SQL同样是OEM发出的,其执行计划可以通过AWR获得:

SQL> select * from table(dbms_xplan.display_awr('4d6m2q3ngjcv9'));                                                     

insert into mgmt_db_size_gtt  select   tablespace_name,NVL(sum(bytes)/1048576, 0) sz 

from sys.dba_free_space  group by tablespace_name                                                                                               

                                                                                                                       

Plan hash value: 2413628916                                                                                            

                                                                                                                       

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

| Id  | Operation                    | Name             | Rows  | Bytes | Cost  |                                      

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

|   0 | INSERT STATEMENT             |                  |       |       |    82 |                                      

|   1 |  SORT GROUP BY               |                  |   189 |  5670 |    82 |                                      

|   2 |   VIEW                       | DBA_FREE_SPACE   |   189 |  5670 |    35 |                                      

|   3 |    UNION-ALL                 |                  |       |       |       |                                      

|   4 |     NESTED LOOPS             |                  |    68 |  2584 |     6 |                                      

|   5 |      NESTED LOOPS            |                  |    68 |  2176 |     6 |                                       

|   6 |       TABLE ACCESS FULL      | TS$              |     1 |    23 |     5 |                                      

|   7 |       TABLE ACCESS CLUSTER   | FET$             |   136 |  1224 |     1 |                                       

|   8 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     6 |       |                                      

|   9 |     NESTED LOOPS             |                  |   119 |  5355 |     6 |                                       

|  10 |      NESTED LOOPS            |                  |   119 |  4641 |     6 |                                      

|  11 |       TABLE ACCESS FULL      | TS$              |    19 |   551 |     5 |                                      

|  12 |       FIXED TABLE FIXED INDEX| X$KTFBFE (ind:1) |     6 |    60 |     1 |                                      

|  13 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     6 |       |                                      

|  14 |     NESTED LOOPS             |                  |     1 |   126 |    20 |                                      

|  15 |      NESTED LOOPS            |                  |     1 |   120 |    20 |                                      

|  16 |       NESTED LOOPS           |                  |     1 |    68 |     3 |                                      

|  17 |        TABLE ACCESS FULL     | RECYCLEBIN$      |     1 |    39 |     2 |                                      

|  18 |        TABLE ACCESS CLUSTER  | TS$              |     1 |    29 |     1 |                                      

|  19 |         INDEX UNIQUE SCAN    | I_TS#            |     1 |       |       |                                      

|  20 |       FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |   100 |  5200 |    17 |                                      

|  21 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     6 |       |                                      

|  22 |     NESTED LOOPS             |                  |     1 |    81 |     3 |                                      

|  23 |      NESTED LOOPS            |                  |     1 |    58 |     2 |                                      

|  24 |       NESTED LOOPS           |                  |     1 |    52 |     2 |                                       

|  25 |        TABLE ACCESS FULL     | RECYCLEBIN$      |     1 |    39 |     2 |                                      

|  26 |        TABLE ACCESS CLUSTER  | UET$             |     1 |    13 |       |                                       

|  27 |         INDEX UNIQUE SCAN    | I_FILE#_BLOCK#   |     1 |       |       |                                      

|  28 |       INDEX UNIQUE SCAN      | I_FILE2          |     1 |     6 |       |                                      

|  29 |      TABLE ACCESS CLUSTER    | TS$              |     1 |    23 |     1 |                                      

|  30 |       INDEX UNIQUE SCAN      | I_TS#            |     1 |       |       |                                       

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

通过执行计划可以看到,在Oracle Database 10g引入了回收站功能后,会将回收站(RECYCLEBIN$)中的空间计算为自由空间,加入到dba_free_space字典中。

如果数据库中存在大量的回收站对象,则这部分回收站空间的计算将会极为耗时,在这个数据库环境中,共有5万多个回收站对象:

SQL> select count(*) from RECYCLEBIN$;                                                                                 

                                                                                                                       

  COUNT(*)                                                                                                              

----------                                                                                                             

     51986         

清理这些回收站对象可以大幅提升这个SQL查询的性能,在OEM中禁用这个Metric监控则可以彻底去除这个SQL访问。

SQL报告中,显示了该SQL如下的详细信息:

dbanb207.png

 

$ORACLE_HOME/rdbms/admin/catspace.sql脚本中,可以找到创建DBA_FREE_SPACE视图的脚本:

create or replace view DBA_FREE_SPACE

    (TABLESPACE_NAME, FILE_ID, BLOCK_ID,BYTES, BLOCKS, RELATIVE_FNO)

as

select ts.name, fi.file#, f.block#, f.length * ts.blocksize, f.length, f.file#

from sys.ts$ ts, sys.fet$ f, sys.file$ fi

where ts.ts# = f.ts# and f.ts# = fi.ts# and f.file# = fi.relfile# and ts.bitmapped = 0

union all

select /*+ ordered use_nl(f) use_nl(fi) */  ts.name, fi.file#, f.ktfbfebno,

       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno

from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi

where ts.ts# = f.ktfbfetsn and f.ktfbfetsn = fi.ts# and f.ktfbfefno = fi.relfile#

  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

union all

select /*+ ordered use_nl(u) use_nl(fi) */ ts.name, fi.file#, u.ktfbuebno,

       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno

from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi

where ts.ts# = rb.ts# and rb.ts# = fi.ts# and u.ktfbuefno = fi.relfile#

  and u.ktfbuesegtsn = rb.ts# and u.ktfbuesegfno = rb.file# and u.ktfbuesegbno = rb.block#

  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

union all

select ts.name, fi.file#, u.block#,u.length * ts.blocksize, u.length, u.file#

from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb

where ts.ts# = u.ts# and u.ts# = fi.ts# and u.segfile# = fi.relfile#

  and u.ts# = rb.ts# and u.segfile# = rb.file#

  and u.segblock# = rb.block# and ts.bitmapped = 0

/

以上脚本中,后面两个UNION ALL查询块是Oracle 10g引入的,并且为了修正这个视图带来的BugOracle一直不停的在改进视图语句。注意视图中Hints的制定对于执行计划的强制影响。

 

我们要时刻牢记的是:Oracle引入了某个新功能之后,同时也会引入很多问题,所以在使用新功能、新特性时要加强监控,及时发现和解决可能出现的问题


历史上的今天...
    >> 2008-02-21文章:
    >> 2006-02-21文章:
           如何才能防止拉网页?
    >> 2005-02-21文章:
           自己动手,丰衣足食

无觅

By eygle on 2011-02-21 08:30 | Comments (2) | Case | 2731 |

2 Comments

曾经,我说的是曾经。
一套跑在p595满配上的10g rac系统
因为有了GC来监控,直接宕机
从此以后,所有的服务器上不准用GC


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com