Re: dbms_stats hint?

"Chuck" <chuckh1958_nospam_at_gmail.com> wrote in message news:hjl5mq$qd9$1_at_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