Database Administration (MOSC)

MOSC Banner

How does SHARED Global Temporary Tables influence the Optimizer in 12.1.0.2?

edited Aug 12, 2019 3:37AM in Database Administration (MOSC) 16 commentsAnswered

Hi,

After an upgrade from 11g to 12.1.0.2. some complex sql queries started to perform poorly.  Upon further investigation it was determine that a small number

of sql queries  used Global Temporary Tables.   To address this problem the only action made was to set them to SHARED instead of SESSION.

Statistics are NOT gathered on these tables.

The database has the dynamic sampling parameter set to 2  and the sql statements do have this hint dynamic_sampling(de 2) .  As far I am

aware the behavior shouldn't differ based on GTTs.

So my question is just academic and the need to understand how this simple change improved the situation. Can anyone explain?

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