Timeline for Why does the query cost change so much and how to prevent it
Current License: CC BY-SA 3.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 7, 2020 at 22:23 | history | edited | CommunityBot |
added [query-performance] to 2412 questions - Shog9 (Id=1924)
|
|
| Oct 6, 2012 at 10:39 | answer | added | Chris Saxon | timeline score: 1 | |
| Oct 3, 2012 at 20:59 | comment | added | filippo | @ChrisSaxon Small has about 100 rows, medium nears 1k, huge is ~400 millions. | |
| Oct 3, 2012 at 20:15 | comment | added | Chris Saxon | How many rows are actually in the small and huge tables? You're getting (widely) different cardinality estimates for the two in the two plans, which would explain why the plans are different | |
| Oct 3, 2012 at 16:12 | answer | added | Vincent Malgrat | timeline score: 2 | |
| Oct 3, 2012 at 14:46 | comment | added | filippo |
@VincentMalgrat I am not a dba, but I gave it a show based on what I read here dba-oracle.com/art_dbazine_oracle10g_dynamic_sampling_hint.htm it didn't work, but I might have applied it wrong (sorry, I can't really take the time to experiment all that much now). The hint I used was /*+ dynamic_sampling(my_id 10) */. thanks
|
|
| Oct 3, 2012 at 13:57 | history | edited | filippo | CC BY-SA 3.0 |
Added explain plans for the situations mentioned in the description.
|
| Oct 3, 2012 at 13:50 | comment | added | filippo | @ChrisSaxon Sure, There you have it. I renamed the tables to make sense with the topic, hope I haven't messed it up. | |
| Oct 3, 2012 at 9:09 | comment | added | Vincent Malgrat |
Your statistics (if they are up-to-date) must confuse the CBO, have you tried the DYNAMIC_SAMPLING hint?
|
|
| Oct 3, 2012 at 8:00 | comment | added | Chris Saxon | Can you post explain plans comparing the good plan vs. the bad one please? | |
| Oct 3, 2012 at 3:57 | answer | added | Brian Efting | timeline score: 0 | |
| Oct 2, 2012 at 21:49 | history | edited | filippo | CC BY-SA 3.0 |
added 1 characters in body
|
| Oct 2, 2012 at 21:45 | comment | added | filippo |
@Phil The stats are fine, but thanks. And, there isn't any reason to not join.. I actually did try that, thats what I meant with "I have tried to isolate the "small query" in a with clause, tried to use an join instead a sub query", sorry if it was ambiguous.
|
|
| Oct 2, 2012 at 21:40 | comment | added | Philᵀᴹ | Are the stats up to date? Is there a reason why you can't add small_table to the where (along with the other bits in the IN clause) and just do a straight join with the main table? | |
| Oct 2, 2012 at 21:38 | history | asked | filippo | CC BY-SA 3.0 |