首页 » ORACLE » sqlplus autotrace sql Statistics value全部为0

sqlplus autotrace sql Statistics value全部为0

上周在配置OGG分离查询、DML/DDL事务,后在source schema运行发现要10秒的查询sql,到target schema查询10分钟未出结果,在sqlplus 中查看autotrace 的信息时发现了个怪现象,如下

SQL> l
  1  SELECT
  2                                  DISTINCT t4.subject_id, t4.score_activity_date
  3                              FROM icme_project_score t4,
  4                                   icme_org t5,
  5                                   icme_org t6
  6                             WHERE     t4.is_valid != 1
  7                                   AND t5.org_id = 37000537
  8                                   AND t6.org_code LIKE t5.org_code || '%'
  9                                   AND t4.give_org_id = t6.org_id
 10                                   AND t4.SCORE_ACTIVITY_DATE >=
 11                                          TO_DATE ('2012-01-01', 'yyyy-mm-dd')
 12                                   AND t4.SCORE_ACTIVITY_DATE <  13                                            TO_DATE ('2012-10-12', 'yyyy-mm-dd')  14*                                         + 1 SQL> /

62 rows selected.

Elapsed: 00:00:13.40

Execution Plan
----------------------------------------------------------
Plan hash value: 3554107662

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |   185K|    15M|       | 87514   (2)| 00:17:31 |       |       |
|   1 |  HASH UNIQUE                   |                    |   185K|    15M|    18M| 87514   (2)| 00:17:31 |       |       |
|*  2 |   HASH JOIN                    |                    |   185K|    15M|       | 83711   (2)| 00:16:45 |       |       |
|   3 |    NESTED LOOPS                |                    |  9157 |   572K|       |   242   (1)| 00:00:03 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| ICME_ORG           |     1 |    32 |       |     2   (0)| 00:00:01 |       |       |
|*  5 |      INDEX UNIQUE SCAN         | PK_ICME_ORG        |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|*  6 |     INDEX FAST FULL SCAN       | UK_ICME_ORGCODE    |  9157 |   286K|       |   240   (1)| 00:00:03 |       |       |
|   7 |    PARTITION RANGE ALL         |                    |  3714K|    88M|       | 83445   (2)| 00:16:42 |     1 |    19 |
|*  8 |     TABLE ACCESS FULL          | ICME_PROJECT_SCORE |  3714K|    88M|       | 83445   (2)| 00:16:42 |     1 |    19 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T4"."GIVE_ORG_ID"="T6"."ORG_ID")
   5 - access("T5"."ORG_ID"=37000537)
   6 - filter("T6"."ORG_CODE" LIKE "T5"."ORG_CODE"||'%')
   8 - filter("T4"."SCORE_ACTIVITY_DATE">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T4"."SCORE_ACTIVITY_DATE"1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         62  rows processed

环境如下
source db version: 10201
target db version: 10205
golden gate version:11r2

system parameters:

statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE

1,首先对表的统计信息进行收集
发现问题依旧
2,发现target schema 查看autotrace时是dba角色,revoke dba,grant PLUSTRACE
3, 在target replicat 的ogg配置中增加MAPEXCLUDE icme4.PLAN_TABLE,排除plan_table
4, delete plan_table
再重新执行刚才的sql

Execution Plan
----------------------------------------------------------
Plan hash value: 3554107662

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |   185K|    15M|       | 87514   (2)| 00:17:31 |       |       |
|   1 |  HASH UNIQUE                   |                    |   185K|    15M|    18M| 87514   (2)| 00:17:31 |       |       |
|*  2 |   HASH JOIN                    |                    |   185K|    15M|       | 83711   (2)| 00:16:45 |       |       |
|   3 |    NESTED LOOPS                |                    |  9157 |   572K|       |   242   (1)| 00:00:03 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| ICME_ORG           |     1 |    32 |       |     2   (0)| 00:00:01 |       |       |
|*  5 |      INDEX UNIQUE SCAN         | PK_ICME_ORG        |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|*  6 |     INDEX FAST FULL SCAN       | UK_ICME_ORGCODE    |  9157 |   286K|       |   240   (1)| 00:00:03 |       |       |
|   7 |    PARTITION RANGE ALL         |                    |  3714K|    88M|       | 83445   (2)| 00:16:42 |     1 |    19 |
|*  8 |     TABLE ACCESS FULL          | ICME_PROJECT_SCORE |  3714K|    88M|       | 83445   (2)| 00:16:42 |     1 |    19 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T4"."GIVE_ORG_ID"="T6"."ORG_ID")
   5 - access("T5"."ORG_ID"=37000537)
   6 - filter("T6"."ORG_CODE" LIKE "T5"."ORG_CODE"||'%')
   8 - filter("T4"."SCORE_ACTIVITY_DATE">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T4"."SCORE_ACTIVITY_DATE"1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     377795  consistent gets
          0  physical reads
          0  redo size
       2432  bytes sent via SQL*Net to client
        536  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         62  rows processed

发现统计信息出来了,后来再重新还原刚才的role,去掉ogg的配置都没能还原那个现象,调整了一个系统参数运行时间也和source schema的时间相差几秒了。总结排查的方法是table/index statistics, parameter statistics_level, check role,ogg MAPEXCLUDE icme4.PLAN_TABLE

如果你找到问题权威原因说法,请通知我

打赏

对不起,这篇文章暂时关闭评论。