ITPub博客

首页 > 数据库 > Oracle > explain plan slow due to dynamic sampling

explain plan slow due to dynamic sampling

原创 Oracle 作者:opsc 时间:2009-05-11 20:32:42 0 删除 编辑
We've got a interesting problem today, a simple explain plan will take more than 1mins.[@more@]

From oracle Document:

==========================================================================================================================================================
* Level 0: Do not use dynamic sampling.
* Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
* Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
* Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
* Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
* Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
* Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
==========================================================================================================================================================

Our dynamic_sampling is 1, and our query meets level 1 case, so it will dynamic sampling 32 blocks for the parse.

The problem is why it cost so long time, even it needs to scan 32 blocks it should be faster. Do a 10046 trace, you'll get it:

There are two tables doing a join, sampling first one is quite fast, but the second took 1 mins, and we can see some wait event:

WAIT #1: nam='db file sequential read' ela= 6850 file#=74 block#=196990 blocks=1 obj#=-1 tim=5607684804274
WAIT #1: nam='db file sequential read' ela= 10074 file#=105 block#=191632 blocks=1 obj#=-1 tim=5607685209538
WAIT #1: nam='db file sequential read' ela= 7344 file#=367 block#=91659 blocks=1 obj#=-1 tim=5607687038063
WAIT #1: nam='db file sequential read' ela= 6822 file#=731 block#=203283 blocks=1 obj#=-1 tim=5607689090744

Try dump some blocks, we'll easily know that they are long raw chained blocks. And the consistent read for the long raw table is much higher.

tableA :

FETCH #1:c=280000,e=268167,p=0,cr=69,cu=0,mis=0,r=1,dep=1,og=1,tim=5609788395376
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT GROUP BY (cr=69 pr=0 pw=0 time=268168 us)'
STAT #1 id=2 cnt=36250 pid=1 pos=1 obj=54649 op='TABLE ACCESS SAMPLE A (cr=69 pr=0 pw=0 time=36396 us)'

tableB :

FETCH #1:c=62870000,e=61399688,p=1,cr=596,cu=0,mis=0,r=1,dep=1,og=1,tim=5609849805278
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT GROUP BY (cr=596 pr=1 pw=0 time=61399689 us)'
STAT #1 id=2 cnt=40 pid=1 pos=1 obj=3182 op='TABLE ACCESS SAMPLE B (cr=596 pr=1 pw=0 time=12572271 us)'

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/21084389/viewspace-1021977/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-10-21

  • 博文量
    14
  • 访问量
    41572