10201—关于hash join的一点疑问

显示全部楼层 · 2017-3-27 08:04:23

最近在详读JL 的《cost based oracle fundamental》中hash join的一章，在10201版本下也作了一些测试。
有个问题想请教下， JL在他的书中讲到在9i下可以通过10053看到probe passes的值，随即可大概得出hash join的cost，但我在10201版本下的10053文件中却找不到有关 probe passes的值，相关的只有"cost per ptn" ，那怎样才能确定一次hash join大概需要多少次probe passes呢？

千问 · 2017-3-27 08:04:23

Check for the word "ppasses".
Yong Huang

千问 · 2017-3-27 08:04:23

以下是我截取的10201下 10053中关于hash join的部分内容：
HA Join
Outer table:
resc: 106.29card 5000.00bytes: 527deg: 1resp: 106.29
Inner table: T_BAlias: B
resc: 106.31card: 4000.80bytes: 30deg: 1resp: 106.31
using dmeth: 2#groups: 1
Cost per ptn: 136.73#ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 349.32Resp: 349.32[multiMatchCost=0.00]
HA cost: 349.32
resc: 349.32 resc_io: 348.00 resc_cpu: 17524353
resp: 349.32 resp_io: 348.00 resp_cpu: 17524353
Best:: JoinMethod: Hash
Cost: 349.32Degree: 1Resp: 349.32Card: 4000.80Bytes: 557
可以确定的是，本次hash join不是optimal pass，至于是onepass 还是multipass， pass了多少次从哪里可以看出？
ppasses的值也没有看到啊？
另外，从cost per ptn的值是否可以得到些类似信息？

千问 · 2017-3-27 08:04:23

I don't know the answer. Anybody knows? Please speak up.
Looks like ppasses is not in 10gR2 10053 trace. The trace file looks quite different from even 10gR1 for which here's an example:
http://www.dbmonster.com/Uwe/For ... O-cost-in-oracle10g
I just tested it. But my test is small, with all data in memory.
Yong Huang
[ 本帖最后由 Yong Huang 于 2011-6-13 11:31 编辑 ]

千问 · 2017-3-27 08:04:23

Thanks! Yong Huang
还想请问一下，如果在10gR2的10053中得不到ppasses的值，那么是否可以通过v$sql_workarea中的LAST_EXECUTION字段得到？
也就是说，v$sql_workarea中的LAST_EXECUTION字段的值是否与10053中的ppasses的值相等？

千问 · 2017-3-27 08:04:23

> v$sql_workarea中的LAST_EXECUTION字段的值是否与10053中的ppasses的值相等？
Sounds like a good idea. But 10053 doesn't require actually running the SQL. The v$ view is populated after the SQL is run. If you explain plan, and the plan output has the column TempSpc, at least you know some temp space will be used.
After the SQL is run, you can either check v$sql_workarea or run dbms_xplan.display_cursor passing memstats or allstats to the format argument to see the optimal, one-pass, or multi-pass usage of temp space.
Yong Huang

千问 · 2017-3-27 08:04:23

Thanks as always,Yong Huang.
"But 10053 doesn't require actually running the SQL. The v$ view is populated after the SQL is run"
So is it true that the values in v$ view are always more convincing than in 10053?
And here is another issue I think I might need your comment:
I did some tests on hash join cases these days, if I used "set autot trace exp" before the sql was run, I found that the plan output had the column "TempSpc", so I thought this was a onepass execution at least(it can not finish its job within the pga);but when I checked the LAST_EXECUTION column in v$sql_workarea after the sql run, it said "optimal" and the corresponding LAST_TEMPSEG_SIZE column had no value.I was confused with that.
In this case, who I should believe?The "autot trace exp" output or the v$sql_workarea?
Thanks in advance!
[ 本帖最后由 sprite731 于 2011-6-16 22:47 编辑 ]

千问 · 2017-3-27 08:04:23

> The "autot trace exp" output or the v$sql_workarea?
The latter. What "explain plan" (or "autot trace exp") does is separate, not the actual run.
I think Jonathan Lewis explains this in great details in his book. Some stats are not even visible in any v$ view. You have to use one or two hash join related events to see the actual stats.
Yong Huang

千问 · 2017-3-27 08:04:23

OK,thanks