Re: dbms_stats hint?

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 26 Jan 2010 11:31:35 -0800 (PST)
Message-ID: <d22234b6-58fa-4238-81d3-87d577d564f3_at_21g2000yqj.googlegroups.com>



On Jan 25, 5:24 pm, Chuck <chuckh1958_nos..._at_gmail.com> wrote:
> While tracking down an ora-1652 error today I noticed two very similar
> statements that were running just prior to the error. They contained a
> dbms_stats hint and looked something like the one below. What is the
> dbms_stats hint and can I safely assume it was the stats job that
> generated these statements? BTW these statements have blown up a 4g temp
> tablespace a couple of times now. Why would dbms_stats need so much temp
> space for an insert into what's probably a GTT? Any way to prevent this
> seeing this is probably coming from an internal stats job?
>
> insert /*+ append */ into sys.ora_temp_1_ds_1662 select /*+
> no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact
> use_weak_name_resl dynamic_sampling(0) no_monitoring
> */"MESSAGELOBID","MESSAGEID","NETWORKORIGINALMESSAGE","KEYALIAS" from
> "PROTECT"."MESSAGELOB" sample ( 18.6510224151) t

Back a few releases Oracle support had said that the analyze command could take 4 times the table size to generate a full compute. What dbms_stats options were in effect and how large was the table that statistics were being generated for?

What other tasks that require temp tablespace were also running? This specific SQL may not be responsible or at least not responsible by itself.

You may wish to compare the allocated size of your temp tablespace to the larger indexes and result set sizes you know your applications will pull to be sure you do in fact have enough temp space allocated.

HTH -- Mark D Powell -- Received on Tue Jan 26 2010 - 13:31:35 CST

Original text of this message