Database Tuning (MOSC)

MOSC Banner

Should we have stats gathered on permanent tables that are used as TEMP?

edited May 8, 2012 3:10PM in Database Tuning (MOSC) 6 commentsAnswered
We have several packaged applications where they create work/temp tables to stage the work before writing to a permanent table. IMHO, they should have been created as GLOBAL TEMPORARY tables because the program never COMMITs rows.  This causes performance problems sometimes. Our gather stats job gathers stats on these tables as having zero rows then the optimizer makes a really bad plan.

We have seen that deleting stats and locking stats so they can not be gathered can significantly speed things up. Right now, I am looking at an explain plan with a merge join cartesian. As soon as a I delete stats on the two "temp" tables the plan becomes reasonable.  The vendors are completely useless in advising us on this.

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

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center