首页 » ORACLE 9i-23c » 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
如果你找到问题权威原因说法，请通知我
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。