11gR2升级之后,v$session和v$process查询慢的猜测

最近升级11gR2之后,发现有几套库查询v$session和v$process异常的缓慢,执行大概要30-40秒。而另外一套库则不存在这样的情况。通过对比两套库的执行计划,发现有很大的区别。如下所示:

A库
============
Plan Table
============
----------------------------------------------------+-----------------------------------+
| Id  | Operation                  | Name           | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT           |                |       |       |     3 |           |
| 1   |  NESTED LOOPS              |                |     1 |   116 |     3 |  00:00:01 |
| 2   |   NESTED LOOPS             |                |     1 |    86 |     3 |  00:00:01 |
| 3   |    NESTED LOOPS            |                |   285 |   15K |     3 |  00:00:01 |
| 4   |     FIXED TABLE FULL       | X$KSUPR        |     4 |    88 |     3 |  00:00:01 |
| 5   |     FIXED TABLE FULL       | X$KSLWT        |    73 |  2409 |     0 |           |
| 6   |    FIXED TABLE FIXED INDEX | X$KSUSE (ind:1)|     1 |    31 |     0 |           |
| 7   |   FIXED TABLE FIXED INDEX  | X$KSLED (ind:2)|     1 |    30 |     0 |           |
----------------------------------------------------+-----------------------------------+

B库
============
Plan Table
============
-----------------------------------------------------+-----------------------------------+
| Id  | Operation                   | Name           | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT            |                |       |       |     5 |           |
| 1   |  NESTED LOOPS               |                |   116 |   14K |     5 |  00:00:01 |
| 2   |   HASH JOIN                 |                |   116 |   11K |     5 |  00:00:01 |
| 3   |    NESTED LOOPS             |                |   116 |  8352 |     1 |  00:00:01 |
| 4   |     FIXED TABLE FULL        | X$KSLWT        |  2281 |   80K |     1 |  00:00:01 |
| 5   |     FIXED TABLE FIXED INDEX | X$KSUSE (ind:1)|     1 |    36 |     0 |           |
| 6   |    FIXED TABLE FULL         | X$KSUPR        |   225 |  5850 |     3 |  00:00:01 |
| 7   |   FIXED TABLE FIXED INDEX   | X$KSLED (ind:2)|     1 |    30 |     0 |           |
-----------------------------------------------------+-----------------------------------+

可以看到走的慢的执行计划是做了三次nested loop,而走的较快的走了2次nested loop和1次hash join。所以在对比一下两者的统计信息。

A库
Table Stats::
  Table: X$KSLWT  Alias:  W
    #Rows: 73  #Blks:  0  AvgRowLen:  103.00  ChainCnt:  0.00
  FixedTabRowCost:  3500.00
  Column (#4): KSLWTSID(
    AvgLen: 5 NDV: 73 Nulls: 0 Density: 0.013699 Min: 1 Max: 13255
  Column (#9): KSLWTEVT(
    AvgLen: 4 NDV: 18 Nulls: 0 Density: 0.006849 Min: 1 Max: 490
    Histogram: Freq  #Bkts: 18  UncompBkts: 73  EndPtVals: 18
***********************
Table Stats::
  Table: X$KSLED  Alias:  E
    #Rows: 1152  #Blks:  0  AvgRowLen:  79.00  ChainCnt:  0.00
  FixedTabRowCost:  3500.00
  Column (#2): INDX(
    AvgLen: 4 NDV: 1152 Nulls: 0 Density: 0.000868 Min: 0 Max: 1151
***********************
Table Stats::
  Table: X$KSUSE  Alias:  S
    #Rows: 13536  #Blks:  0  AvgRowLen:  215.00  ChainCnt:  0.00
  FixedTabRowCost:  3500.00
  Column (#22): KSUSEPRO(
    AvgLen: 3 NDV: 78 Nulls: 0 Density: 0.000037 Min: 0 Max: 996921004067791917474525345029816320
    Histogram: Freq  #Bkts: 78  UncompBkts: 13536  EndPtVals: 78
  Column (#2): INDX(
    AvgLen: 5 NDV: 13536 Nulls: 0 Density: 0.000074 Min: 1 Max: 13536
***********************
Table Stats::
  Table: X$KSUPR  Alias: X$KSUPR
    #Rows: 9000  #Blks:  0  AvgRowLen:  162.00  ChainCnt:  0.00
  FixedTabRowCost:  3500.00
  Column (#1): ADDR(
    AvgLen: 9 NDV: 9000 Nulls: 0 Density: 0.000111
    
B库
Table Stats::
  Table: X$KSLWT  Alias:  W
    #Rows: 2281  #Blks:  0  AvgRowLen:  116.00  ChainCnt:  0.00
  FixedTabRowCost:  3500.00
  Column (#4): KSLWTSID(
    AvgLen: 5 NDV: 2281 Nulls: 0 Density: 0.000438 Min: 1 Max: 13304
  Column (#9): KSLWTEVT(
    AvgLen: 4 NDV: 23 Nulls: 0 Density: 0.043478 Min: 1 Max: 490
***********************
Table Stats::
  Table: X$KSLED  Alias:  E
    #Rows: 1152  #Blks:  0  AvgRowLen:  79.00  ChainCnt:  0.00
  FixedTabRowCost:  3500.00
  Column (#2): INDX(
    AvgLen: 4 NDV: 1152 Nulls: 0 Density: 0.000868 Min: 0 Max: 1151
***********************
Table Stats::
  Table: X$KSUSE  Alias:  S
    #Rows: 13536  #Blks:  0  AvgRowLen:  251.00  ChainCnt:  0.00
  FixedTabRowCost:  3500.00
  Column (#22): KSUSEPRO(
    AvgLen: 4 NDV: 2282 Nulls: 0 Density: 0.000438 Min: 0 Max: 996921005506681932261742094648868864
  Column (#2): INDX(
    AvgLen: 5 NDV: 13536 Nulls: 0 Density: 0.000074 Min: 1 Max: 13536
***********************
Table Stats::
  Table: X$KSUPR  Alias: X$KSUPR
    #Rows: 9000  #Blks:  0  AvgRowLen:  188.00  ChainCnt:  0.00
  FixedTabRowCost:  3500.00
  Column (#1): ADDR(
    AvgLen: 9 NDV: 9000 Nulls: 0 Density: 0.000111
Access path analysis for X$KSUPR
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for X$KSUPR[X$KSUPR] 
  Column (#3): INST_ID(
    AvgLen: 3 NDV: 1 Nulls: 0 Density: 1.000000 Min: 2 Max: 2

  Column (#4): KSSPAFLG(
    AvgLen: 3 NDV: 2 Nulls: 0 Density: 0.500000 Min: 0 Max: 1
  Table: X$KSUPR  Alias: X$KSUPR
    Card: Original: 9000.000000  Rounded: 225  Computed: 225.00  Non Adjusted: 225.00
  Access Path: TableScan
    Cost:  2.91  Resp: 2.91  Degree: 0
      Cost_io: 0.00  Cost_cpu: 36382500
      Resp_io: 0.00  Resp_cpu: 36382500
  Best:: AccessPath: TableScan
         Cost: 2.91  Degree: 1  Resp: 2.91  Card: 225.00  Bytes: 0

发现基表X$KSLWT的统计信息值差太多。其他都一样。那么X$KSLWT是什么表呢?我们来查询一次。

[oracle@11g admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 24 15:08:03 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(1) from X$KSLWT;

  COUNT(1)
----------
        24

SQL> select count(1) from v$session;

  COUNT(1)
----------
        24

另开一个会话,连入之后。再次查询。

SQL> select count(1) from X$KSLWT;

  COUNT(1)
----------
        25

SQL> select count(1) from v$session;

  COUNT(1)
----------
        25

从这里我们可以初步的判断出原因,在升级的时候,停掉了应用,然后收集了数据字典表的统计信息,这个时候的session只是一些数据库自带进程的。所以当时的值是73。随着升级完成之后,中间件和数据库建立了长连接。可以看到是2000多个会话。如果还是走之前的执行计划,就导致了执行缓慢。所以这类型的数据字典应该在业务开放之后收集。我们来看一下两个库该表详细统计信息情况

 A库
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.
 
SQL> select table_name,num_rows,last_analyzed from dba_tab_statistics where table_name='X$KSLWT';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
X$KSLWT                                73 2013-08-10 21:27:17

B库
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.

SQL> select table_name,num_rows,last_analyzed from dba_tab_statistics where table_name='X$KSLWT';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
X$KSLWT                              2281 2013-08-11 08:00:52

如何收集数据字典的统计信息,大家可以参考

Fixed Objects Statistics(GATHER_FIXED_OBJECTS_STATS) Considerations (Doc ID 798257.1)

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *