tag:blogger.com,1999:blog-14654018.post428046148426621320..comments2023-07-18T12:55:13.959+01:00Comments on The /*+Go-Faster*/ Oracle Blog: Global Temporary Tables Share Statistics Across SessionsDavid Kurtzhttp://www.blogger.com/profile/00468908370233805717noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-14654018.post-15191707606385981282010-10-05T18:44:35.145+01:002010-10-05T18:44:35.145+01:00Thank you so much for sharing this precious inform...Thank you so much for sharing this precious information with us.Unknownhttps://www.blogger.com/profile/17645884622051282583noreply@blogger.comtag:blogger.com,1999:blog-14654018.post-69824542433037760602009-11-01T20:10:42.880+00:002009-11-01T20:10:42.880+00:00Coskan, my apologies. This is a copy/paste error....Coskan, my apologies. This is a copy/paste error. The execution plan should be a full scan, I reran the test.<br /><br />During my testing, I had flushed the shared pool between queries so that I did not aggregate statistics across executions. This forced Oracle to re-parse the SQL, and the plan changed. When I ran the test in SQL*Plus with SET AUTOTRACE ON, the <i>explain plan for</i> issued by SQL*plus had the effect of forcing the database to re-parse the statement each time. However, you are right, unless Oracle is forced to re-parse the SQL statement, you will still get the same execution plan in both sessions.David Kurtzhttps://www.blogger.com/profile/00924323960047469300noreply@blogger.comtag:blogger.com,1999:blog-14654018.post-4291657473502748292009-10-21T10:22:44.788+01:002009-10-21T10:22:44.788+01:00David,
You said after you delete the stats you s...David, <br /><br />You said after you delete the stats you start to see full table scan but your example is still index scan<br /><br />Another thing is (at least on 11.1.0.6) I dont see a plan change across different sessions. No matter which dynamic sampling level I run the statement with If there is already a parsed one (ie If session 1 is parsed it with FULL SCAN ) the second session picks the same plan. (reverse is also true if second session parses first and get INDEX scan)<br /><br />What I observe is that dynamic sampling does not solve problem Oracle does not generate a child cursor.Coskan Gundogarhttps://www.blogger.com/profile/11570869033287689498noreply@blogger.com