tag:blogger.com,1999:blog-5124641802818980374.post2163863887482563122..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: PLAN_HASH_VALUE - How equal (and stable?) are your execution plans - part 2Unknownnoreply@blogger.comBlogger6125tag:blogger.com,1999:blog-5124641802818980374.post-70735311696988470602009-09-04T00:26:43.093+02:002009-09-04T00:26:43.093+02:00Kyle,
thanks for stopping by.
Yes, the script s...Kyle,<br /><br />thanks for stopping by. <br /><br />Yes, the script should be handy for those rare situations where you get significant performance differences due to such subtle changes not covered by the traditional PLAN_HASH_VALUE.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-68755495069794210322009-09-04T00:03:11.299+02:002009-09-04T00:03:11.299+02:00Nice script. Definitely something I've been wa...Nice script. Definitely something I've been wanting to look into, ie looking for plan changes which include not only the plan but the order of predicate evaluation.Kyle Haileyhttps://www.blogger.com/profile/13586511268045480856noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-33586276517377423462009-08-13T23:54:51.849+02:002009-08-13T23:54:51.849+02:00Kerry,
very interesting... Thanks for sharing the...Kerry,<br /><br />very interesting... Thanks for sharing the finding.<br /><br />That's basically the most obvious threat: The same plan but different filter / access predicates leading to significant differences in runtime.<br /><br />The custom hash value calculated as shown here should have revealed that. The final script of the post would mark such statements in the shared pool.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-9728192991948269632009-08-13T23:10:28.457+02:002009-08-13T23:10:28.457+02:00Ah ha! I ran across the very scenario you were tal...Ah ha! I ran across the very scenario you were talking about today. Two sql statements varied in the number of bind variables in an in-list. I was experimenting with forcing an index skip scan, and low and behold - I get two statements with the same plan_hash_value but very different behavior - due to one using the column in the in-list as a filter only and the other using is as an access path via the skip scan on the index. The optimizer obviously knows the difference as the calculated cost is way different. (by the way, I ctl-C'ed out of the long running one or it would run for several hours. I'll try pasting some supporting data here. Hopefully the formatting won't be too bad.<br /><br /><br />------------------------------------------------------------------------------------------------------------------------------------------------------<br />SQL_ID af1rum3tw4vf7, child number 0<br />-------------------------------------<br />select /* test 6f7y6x3abtub0_in 3 */ /*+ index_ss("DFKKOP" "DFKKOP~5")<br />gather_plan_statistics */ "MANDT" , "OPBEL" , "OPUPW" , "OPUPK" , "OPUPZ" ,<br />"BUKRS" , "GSBER" , "BUPLA" , "SEGMENT" , "AUGST" , "GPART" , "VTREF" , "VTRE2"<br /><br />Plan hash value: 3440925338<br /><br />----------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />----------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 11 (100)| |<br />|* 1 | TABLE ACCESS BY INDEX ROWID| DFKKOP | 2 | 850 | 11 (0)| 00:00:01 |<br />|* 2 | INDEX SKIP SCAN | DFKKOP~5 | 61 | | 4 (0)| 00:00:01 |<br />----------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 1 - filter("FAEDN"<=:A2)<br /> 2 - access("VTREF"=:A3)<br /> filter(("VTREF"=:A3 AND "AUGST"<>:A1 AND "MANDT"||''=:A0))<br /><br /><br />------------------------------------------------------------------------------------------------------------------------------------------------------<br />SQL_ID b4s8s2m83h2cp, child number 0<br />-------------------------------------<br />select /* test 6f7y6x3abtub0_in 4 */ /*+ index_ss("DFKKOP" "DFKKOP~5")<br />gather_plan_statistics */ "MANDT" , "OPBEL" , "OPUPW" , "OPUPK" , "OPUPZ" ,<br />"BUKRS" , "GSBER" , "BUPLA" , "SEGMENT" , "AUGST" , "GPART" , "VTREF" , "VTRE2"<br /><br />Plan hash value: 3440925338<br /><br />----------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />----------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 5710K(100)| |<br />|* 1 | TABLE ACCESS BY INDEX ROWID| DFKKOP | 8 | 3400 | 5710K (1)| 14:40:19 |<br />|* 2 | INDEX SKIP SCAN | DFKKOP~5 | 51M| | 109K (1)| 00:16:52 |<br />----------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 1 - filter("FAEDN"<=:A2)<br /> 2 - filter(("AUGST"<>:A1 AND INTERNAL_FUNCTION("VTREF") AND "MANDT"||''=:A0))Kerry Osbornehttps://www.blogger.com/profile/18129210709237272590noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-54287087627826385512009-08-02T13:50:32.168+02:002009-08-02T13:50:32.168+02:00Kerry,
it was something that interested me in gen...Kerry,<br /><br />it was something that interested me in general: What is the PLAN_HASH_VALUE based upon.<br /><br />Then I came across a description of an issue where different filter and access predicates lead to significant differences in runtime but the PLAN_HASH_VALUE of the two statements was the same (since those predicates are not used for the calculation).<br /><br />Therefore I decided to complete my investigations I started some time ago.<br /><br />The description and scripts provided should help in such circumstances to get a better understanding if there are subtle differences in the plans that might make a significant difference.<br /><br />Although I admit it usually should only happen rarely that the PLAN_HASH_VALUE is the same but the statements perform differently.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-80352072227640993462009-08-02T04:29:00.480+02:002009-08-02T04:29:00.480+02:00Very interesting Randolf,
I'll have to play...Very interesting Randolf,<br /><br /> I'll have to play around with this idea a bit. I'm interested as to what caused you to start thinking about including the other available info in a plan hash value. Did you run into a situation where something changed but was not reflected by a change in the plan hash value?<br /><br />KerryKerry Osbornehttps://www.blogger.com/profile/18129210709237272590noreply@blogger.com