I'm looking for ways to improve my database performance for heavy analytical queries and found that my temp tablespace has 1M extents (default for uniform tablespace). With my usual temp tablespace usage far above 1GB per query (top queries with heavy joins/sorts/groups can consume up to 100 GB of temp tablespace) 1M extents looks too low for me. Is it worth increasing extent size for performance?
1 Answer
Unless you have some evidence from AWR/ ASH that allocating extents is a particularly time-consuming part of your query runtime, this is probably the sort of micro-optimization that you'd consider if you were trying to squeeze the last fraction of a percent of performance out of the system.
Are you at the point where you're reasonably confident that you've gone as far as you can with pre-aggregating data for your typical query workload with materialized views, you've built OLAP cubes, etc.? Unless the query that is consuming 100 GB of TEMP space is based on some user doing some one-off analytics that is aggregating by something that no other queries want to aggregate by, my guess would be that there is a lot more potential for large-scale optimization that will produce much greater returns.
