RE: Why does a hard parse access data in the table in the query?

This is highly dependent on release level. When you get to a release that has GTT private session stats and allow (default) collection on direct inserts, you get pretty doggone good (and session perishable but exact for the session) stats (so at crossing into that release you may find it beneficial to REMOVE DBMS_STATS on GTTs from batch processing scripts that may currently be there for your GTTs.)  

It is also then possible to truly be "GLOBAL" and leave your GTTs defined rather than creating them fresh with some sequence number in the name to avoid pollution of the stats by other sessions.  

This is (finally) the resolution of the argument from circa 1991 that went something like this: 1) We want things that behave the way your "cache" temporary objects behave like when you create indexes or do order by and group by on the fly that are persistent only for the session in flight. 2) Yeah, but tying up the data dictionary locks is expensive, so we'll give that to you by a persistent definition with session dependent data 3)Okay that works for rule based parsing just fine, but you want us to move to CBO 4) Well it's an improvement, isn't it? 5) Yeah, but not what we really asked for, which should be very simple (we think), 6) Yeah, but that dictionary problem., 7) What do you expect, you only have one latch on the dictionary.. 8) Besides, if we don't build the objects in the dictionary we have to re-write the parser to get plans, and we don't want to special case them like index "cache" objects, ..99) hey, we've got plenty of latches now, why didn't we do private stats all the time?  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala
Sent: Saturday, February 16, 2019 9:15 PM To: oracle-l_at_freelists.org
Subject: Re: Why does a hard parse access data in the table in the query?  

Hi Mark!

Well, that is a deeply philosophical question. The data in the GTT usually has very short duration, either until transaction commits or until the session ends. Any durable statistics may be of very questionable validity because of the frequent data changes. The only case in which statistics on a GTT MAY help is if the data in the GTT always follows the same pattern. Personally, I do prefer dynamic sampling for GTT's over permanent statistics collected using DBMS_STATS.

Regards

On 2/8/19 1:25 PM, Powell, Mark wrote:  

If statistics exist on the GTT I do not think dynamic sampling is invoked. I find the best way to deal with GTT's is to load them with representative data in a session, generate statistics, and lock the statistics.  

Mark Powell

Database Administration

(313) 592-5148  

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l