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

On Jan 25, 5:24=A0pm, Chuck <chuckh1958_nos...@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 --

"Chuck" <chuckh1958_nospam@gmail.com> wrote in message 
news:hjl5mq$qd9$1@news.eternal-september.org...
> 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


This statement does come from dbms_stats; it's part of the job
of gathering histograms on a table.  Since each histogram requires
Oracle to run a complex analytic query, Oracle creates a copy of
the sample data - i.e.restricted to a subset of the rows and columns -
rather than running the analytic query against the full table for each
column.

In your case Oracle is using an 18.6% sample of the table, and plans
to collect histograms only on the four columns listed.  So you could
work out (roughly) the size of the temporary table needed.  (average
column length x num_rows + 12 x sample size for each column) plus
about 10%.

Once the temporary table is created, it's possible that the query with
the analytic function (with all its sorting) has a further large-scale 
impact
on the temporary tablespace.

The dbms_stats hint isn't really a hint - it's just a label that the 
developers
have put into the code to make it easier to identify the fact that the
statement was generated by dbms_stats.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com