| Temp Tablespace Issue somehow fixed SQL advisor [message #681825] |
Wed, 02 September 2020 13:16  |
wtolentino
Messages: 422
Registered: March 2005
|
Senior Member |
|
|
we have been getting this error "ORA-01652: unable to extend temp segment by 32 in tablespace TEMP". in normal circumstances if we know that the temp tablespace is undersized we would increase it's size. we attempted to increase the size several times as temporary fix, it did not work. according to our app development team the query has been running over the years without an issue. we knew it has to do something with the query so we attempt to tune it by SQL advisor (gather stats and create the SQL profile) and that did the trick. there was no code change.
my question is what does tuning has to do with the tablespace size issue that it somehow fixed the ORA-01652. no code change and same data rows.
thanks,
warren
|
|
|
|
|
|
|
|
|
|
| Re: Temp Tablespace Issue somehow fixed SQL advisor [message #681841 is a reply to message #681828] |
Thu, 03 September 2020 12:22  |
wtolentino
Messages: 422
Registered: March 2005
|
Senior Member |
|
|
EdStevens wrote on Wed, 02 September 2020 15:48It seems rather obvious that gather stats caused/allowed the optimizer to choose a more efficient access plan. Perhaps over time the data distribution reached some tipping point in the variables that the optimizer considers.
thanks Ed that gives me some ideas how the optimizer works.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
