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

From: Powell, Mark <mark.powell2_at_dxc.com>
Date: Fri, 8 Feb 2019 18:25:20 +0000
Message-ID: <DM6PR01MB39143D82E6B538F106B153ADCE690_at_DM6PR01MB3914.prod.exchangelabs.com>

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

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Mladen Gogala <gogala.mladen_at_gmail.com> Sent: Thursday, February 7, 2019 7:36 PM To: oracle-l_at_freelists.org
Subject: Re: Why does a hard parse access data in the table in the query?

Norman, if you use global temporary tables, the dynamic sampling will gather statistics on them each and every time, even in Oracle 11G. There is a whole philosophy around GGT's in 12c, but I still prefer the good, old dynamic sampling.

Regards

On 2/7/19 9:06 AM, Norman Dunbar wrote:
> On 07/02/2019 10:19, Norman Dunbar wrote:
>> I get the feeling I should know this, but it seems to have aged out
>> of the cache!
>
> Thanks to everyone who said "dynamic sampling" because that's what it
> appears to be. I haven't traced it yet, but:
>
> * It was a hard parse;
> * Optimizer_dynamic_Sampling = 2 (the default)
>
> Unfortunately, the stats on this database are gathered very
> frequently. So the default says to "gather dynamically when one or
> more of the tables has no stats" means that I shouldn't be seeing
> dynamic sampling.
>
> Maybe, because stats go stale pretty quickly on some tables, it's
> taking "stale" to mean "none"? I wonder.
>
> Anyway, thanks again, you have been most helpful and hopefully, this
> won't age out of cache too soon! :o)
>
>
> Cheers,
> Norm.
>
>

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

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



DXC Technology Company - Headquarters: 1775 Tysons Boulevard, Tysons, Virginia 22102, USA.
DXC Technology Company -- This message is transmitted to you by or on behalf of DXC Technology Company or one of its affiliates. It is intended exclusively for the addressee. The substance of this message, along with any attachments, may contain proprietary, confidential or privileged information or information that is otherwise legally exempt from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate any part of this message. If you have received this message in error, please destroy and delete all copies and notify the sender by return e-mail. Regardless of content, this e-mail shall not operate to bind DXC Technology Company or any of its affiliates to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose. --.

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

Received on Fri Feb 08 2019 - 19:25:20 CET