Database Tuning (MOSC)

MOSC Banner

How can I reduce the TEMP usage and increase PGA usage

in Database Tuning (MOSC) 2 commentsAnswered

We have a large (26Tb) ) DW Oracle 12.2 database running on Linux (PGA is sized to 70GB) . Multiple users run SQL report joining to eight large sub-partitioned tables using four parallel threads . The SQL report runs slow and returns more than 4 million row and has 205 columns in GROUP BY clause. Last night in a four hour window the we used 3.6Tb of TEMP space.

What I have seen is that when multiple reports runs, some fail on TEMP space. Individual report SQL uses 20Gb of TEMP space. The memory is needed for hash group/join operation.

Is it possible to increase request percentage to the PGA. Lets say instead of requesting X% from PGA, can I increase it to Y%? I would like to prevent going to TEMP space. I think hash-join's constant reading from the TEMP space in chunks delays the query.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

Instructions for posting

×

1. Select a discussion category from the picklist.


2. Enter a title that clearly identifies the subject of your question.


3. In the body, insert detailed information, including Oracle product and version.


Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI).


Cloud / Fusion customers - Our Cloud community has moved! Please go to Cloud Customer Connect .


New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center