Home » RDBMS Server » Server Administration » Temp Tablespace Issue somehow fixed SQL advisor (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
Temp Tablespace Issue somehow fixed SQL advisor [message #681825] Wed, 02 September 2020 13:16 Go to next message
wtolentino
Messages: 320
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 #681827 is a reply to message #681825] Wed, 02 September 2020 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 67478
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

An example: HASH/MERGE JOIN requires temp space, NESTED LOOP not.

Re: Temp Tablespace Issue somehow fixed SQL advisor [message #681828 is a reply to message #681825] Wed, 02 September 2020 14:48 Go to previous messageGo to next message
EdStevens
Messages: 1267
Registered: September 2013
Senior Member
It 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.
Re: Temp Tablespace Issue somehow fixed SQL advisor [message #681840 is a reply to message #681827] Thu, 03 September 2020 12:20 Go to previous messageGo to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
Michel Cadot wrote on Wed, 02 September 2020 15:47

An example: HASH/MERGE JOIN requires temp space, NESTED LOOP not.

thank you Michael i learned something new. yes the SQL plan says that it has several HASH JOINs.
Re: Temp Tablespace Issue somehow fixed SQL advisor [message #681841 is a reply to message #681828] Thu, 03 September 2020 12:22 Go to previous message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
EdStevens wrote on Wed, 02 September 2020 15:48
It 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.
Previous Topic: Empty Datafile
Next Topic: TEMPORARY TABLESPACE UTILIZATION TREND
Goto Forum:
  


Current Time: Wed Oct 28 07:19:27 CDT 2020